Re-engineer something you own
Convert a scalar SQL function into a dbt macro and prove equivalence
- Models or views call a scalar function created directly in the warehouse, so dbt cannot see the dependency and changes to it are not reviewed or tested.
- A freshly built environment breaks because the warehouse function its models call does not exist there.
- The team wants to retire a warehouse function, but its callers need proof that the SQL the dbt version generates gives the same values.
Bring a scalar warehouse function into the dbt project as a macro, switch the agreed callers to it, and prove that the SQL the macro generates matches the unchanged function and that each switched caller keeps its approved output, with its quirks reproduced and reported as follow-up work.
Use this Recipe
Use the Vibedata Recipe `scalar-sql-function-to-dbt-macro` at https://github.com/accelerate-data/vibedata-cookbooks/blob/main/recipes/scalar-sql-function-to-dbt-macro/recipe.md Read the Recipe and execute it in the context of the current Intent.
Recipe id scalar-sql-function-to-dbt-macro · Canonical definition
Verified by
What has to be observably true before this Recipe is finished.
- For every agreed input, evaluating the SQL expression generated by the macro produces the same value and warehouse type as the unchanged function at the approved precision; any difference left is reported, not claimed as equivalent.
- Each switched caller keeps its approved output contract, covering whichever of rows, columns, column order and types the approved contract counts as unchanged.
- Both sides are compared in the same approved execution context, with any session setting or warehouse state the function reads held equal, and the match holds in each approved environment and time zone where the function runs or its outputs were captured; otherwise the gap is stated.
- Switched callers take the logic from the dbt project and no longer depend on the warehouse function.
- Every function behaviour that affects a result on the agreed inputs and argument types is reproduced, including null handling and which side of a boundary a value falls on.
- Each defect found in the function is listed with the inputs it affects, the rule it appears to break and what correcting it would change, as follow-up work.
- Inputs the function treats specially that the data never contains are checked against the unchanged function or another approved oracle where one is available; otherwise the gap is stated.
- Equivalence is kept separate from cutover: callers left on the function keep it available, and it is dropped or disabled as approved only after equivalence is accepted and no caller still uses it or remains unaccounted for.
- Anything the comparison cannot prove is stated.
Recipe promptThe task specification the agent reads. Reference only — it is not what you copy.
Convert <scalar_function> into a dbt macro in the current Intent and switch <callers> to it. Inherit the Intent's repository, platform, Domain, sources and approved requirements, and resolve only the semantics they leave open. Deliver the macro and the switched callers with evidence that evaluating the SQL the macro generates gives the same value and warehouse type as the unchanged function on the agreed inputs at the approved precision, and that each switched caller keeps its approved output. Reproduce every function behaviour on the agreed inputs and argument types, including ones that look like mistakes, and report each defect as follow-up work; an output change someone wants belongs in a separate change. Handle the function as approved, and state what the comparison cannot prove.
Agent guidanceHow the agent approaches the work, and what it will not do.
Inherit the Intent's repository, platform, Domain, sources and approved requirements. Read the function's definition first: its argument and return types, overloads, implicit casts at the call, each branch and boundary, and anything it depends on besides its arguments, such as session settings, tables or other warehouse state, privileges or side effects. If it is nondeterministic (its result varies for the same arguments with context and state held equal), or its required semantics depend on side effects, privilege boundaries or state the macro cannot reproduce, stop and report it as out of scope. Find the function's callers inside and outside the dbt project, and state where callers could not be enumerated. Settle how the unchanged function's output is obtained on the agreed inputs, and keep that baseline unchanged. Write the macro to reproduce every such behaviour, including the ones that look like mistakes, and list each defect as follow-up work. Evaluate the SQL the macro generates against that baseline, in the same execution context, on the values the data holds, the inputs the function treats specially and the agreed edge inputs, then switch the agreed callers and compare each caller's output with its output before the switch in each approved environment. Handle the function as approved and state what the comparison cannot prove.
Composes
- capturing-requirements
- profiling-source-data
- generating-dbt-model
- dbt-unit-testing
- running-dbt-in-sandbox
- documenting-dbt-models
- verifying
Asks first
Semantic decisions the Intent cannot supply. Never context Studio already holds.
- If unresolved, which callers move to the macro in this work, and which, inside or outside dbt, keep calling the function?
- If unresolved, are values compared exactly or within an approved precision, and what counts as an unchanged caller output?
- If unresolved, which inputs form the comparison: the values the data holds, plus which further edge inputs, such as out-of-range values, in which environments and time zones, and, if the function can no longer run, which captured outputs serve as the baseline?
- If unresolved, for a function that reads session settings or warehouse state, which execution context and state are held equal for the comparison?
- If unresolved, which argument types must the generated SQL accept: only those the current callers pass, or every type the function accepts?
- If unresolved, must the macro work only on this platform, or also on other warehouses the project targets?
- If unresolved, is the function marked as superseded, and once equivalence is accepted and no caller still uses it or remains unaccounted for, is it kept, disabled or dropped?
Guardrails
- Do not edit the function or its captured outputs to make the comparison pass.
- Do not drop or disable the function before equivalence has been accepted, or while any caller still uses it or remains unaccounted for; proven equivalence alone does not authorize removal.
- Do not correct a function defect, such as a null placeholder or an inclusive boundary; reproduce it, report it as follow-up work, and route any wanted output change to a separate change.
- Do not treat a nondeterministic function, or one whose required semantics depend on side effects, privilege boundaries or state the macro cannot reproduce, as an ordinary scalar function; report it as out of scope.
- Do not treat matching caller outputs as proof that the generated SQL matches the function on every input; aggregates can hide a difference on individual inputs.
- Do not loosen the approved precision, or narrow the compared inputs or the approved contract, to make the comparison pass.
- Do not change the output of models outside the conversion.
- Do not extend the conversion into rewriting the callers' own logic, table-valued functions, stored procedures or porting to platforms the user has not approved.