We migrated one helper. cents_to_dollars — a one-line arithmetic function nobody had touched in fourteen months. The smallest case worth migrating, and the smallest case where the difference between a macro and a UDF would have somewhere to show. The macro version compiled to (amount / 100.0) inline, the way it always had. The UDF version compiled to analytics.dbt_helpers.cents_to_dollars(amount) — a string the warehouse has to resolve at run time. Then we ran dbt test against an empty target schema and the warehouse said Unknown function CENTS_TO_DOLLARS. The test was correct. The function wasn’t there yet. That is the post.

Tristan Handy opened the original feature request in 2016. For most of the decade since, the workaround was a Claire-Carroll-vintage on-run-start hook that pre-deployed warehouse functions before the models ran. GA arrived on 10 February 2026 — three months ago. Every tutorial since has walked the YAML. None of them shows the line in target/compiled/ where a model stops containing its helper and starts referring to one.

Start with the macro. A one-line helper that divides cents by a hundred. The model file calls it, dbt compiles the macro inline, and the warehouse receives a SELECT that is, for the warehouse, indistinguishable from one a human typed.

{% macro cents_to_dollars(column_name) %}
  ({{ column_name }} / 100.0)
{% endmacro %}

And the model that calls it:

SELECT
  order_id,
  {{ cents_to_dollars('amount') }} AS amount_dollars
FROM {{ ref('raw_orders') }}

After dbt compile, the artefact in target/compiled/ is the model with the macro expanded in place. There is no function anywhere — the arithmetic sits in the SELECT:

SELECT
  order_id,
  (amount / 100.0) AS amount_dollars
FROM analytics.raw.raw_orders

The dbt 1.11 UDF version puts the same logic in functions/cents_to_dollars.sql and declares its signature in YAML:

CREATE OR REPLACE FUNCTION cents_to_dollars(amount_cents BIGINT)
RETURNS FLOAT
LANGUAGE SQL
AS $$
  amount_cents / 100.0
$$;

functions:
  - name: cents_to_dollars
    arguments:
      - name: amount_cents
        data_type: bigint
    returns:
      data_type: float

And the model body changes shape. The Jinja entry point changes too — ref() is for models, seeds, and snapshots; UDFs use {{ function('name') }}, which resolves to the fully-qualified function identifier dbt deployed:

SELECT
  order_id,
  {{ function('cents_to_dollars') }}(amount) AS amount_dollars
FROM {{ ref('raw_orders') }}

After dbt build, the compiled artefact contains the resolved function path — an object the warehouse looks up at run time:

SELECT
  order_id,
  analytics.dbt_helpers.cents_to_dollars(amount) AS amount_dollars  -- calls warehouse function
FROM analytics.raw.raw_orders

That line is the whole point of the post. Not cosmetic. The macro version is self-contained — the model carries its definition with it; nothing on the warehouse needs to exist except the source table. The UDF version contains a reference — the model relies on analytics.dbt_helpers.cents_to_dollars being present before the query is even valid. Every helper in this file produces the same answer the macro did. And the next time someone drops the function out-of-band, the model errors. The compiled SQL says it all: a macro is what you wrote; a UDF is what you reference.

A macro is text. A UDF is a row in INFORMATION_SCHEMA.FUNCTIONS. The first is what dbt compiles; the second is what dbt deploys — and the test step is the first thing that finds out.

Back to the cents_to_dollars helper we just migrated. The first thing the new compiled output told us was that dbt run — the command our CI had been calling for years — wasn’t the right command anymore. The macro version never cared. dbt run compiled the macro inline and the warehouse received a single CREATE OR REPLACE TABLE. The macro existed only as text, expanded the moment dbt sent the model SQL.

The UDF version splits that into two DDL statements in a defined order. Functions are nodes in the DAG; the model that calls {{ function('cents_to_dollars') }} depends on the function the same way a model depends on a seed. dbt build issues CREATE OR REPLACE FUNCTION first — against the schema the project targets for function resources — and only then runs the model. dbt run alone does not deploy the function. It never has. We learned that the way most teams will: by reading a stack trace.

One Jinja detail to flag while it’s in front of us, because the tutorials skip it. ref() resolves to a model, seed, or snapshot. UDFs use a different macro — {{ function('name') }}, singular, lowercase — which resolves to the fully-qualified function identifier in the target warehouse. Same dependency-edge mechanics in the DAG, different Jinja entry point. We mention it once and never again.

Then we wrote the operational difference out as a table. Not to spec the feature — to make the deploy-order shift readable at a glance, because it is the thing every existing CI job touches.

The deploy-order shift is the only thing that changes for a small project. For a large one, it changes the entire Slim CI plan the moment somebody edits a function body — which is what we tried next.

Back to the cents_to_dollars helper, the same one. We made the smallest possible edit — replaced 100.0 with 100 inside the function body — and ran the Slim CI selector dbt projects everywhere lean on without naming. The macro version of this experiment is well known: edit a macro, nothing else, and state:modified returns an empty set. Macros are recorded in manifest.json by name; a body change inside the macro does not propagate to the modified set of downstream models. Slim CI rebuilds nothing. Production silently inherits the new arithmetic on the next scheduled run. Most teams have lived with that.

The UDF version closes the gap. The same one-character edit, this time against the function file:

$ dbt ls --select state:modified --state prod-artifacts/
function.my_project.cents_to_dollars   # the UDF itself
model.my_project.orders                # downstream model that calls {{ function('cents_to_dollars') }}

Two rows. The function flagged — expected. The downstream orders model flagged too, not because its file changed, but because a node it depends on did. state:modified on UDFs propagates through the DAG the same way it does on models. A change to cents_to_dollars is, as far as Slim CI is concerned, a change to every model that invokes {{ function('cents_to_dollars') }}. Run dbt build --defer --state prod-artifacts/ and the function resolves to the state-manifest copy until the new one is deployed. The blast radius is visible, and the build plan knows the order.

The same edit, in two different resource types, produced two different CI plans. The macro version hid the change. The UDF version surfaced it. — Field note, §3

This is the property to lean on. For any helper a non-trivial number of models depend on, the change blast radius becomes visible to your CI before it becomes visible to production. The cost is the deploy-order shift from §2. The trade is worth it for any helper whose definition you would otherwise debate in a Slack thread after the fact.

Now we’re back at the moment from the dek. cents_to_dollars migrated, orders.sql rewritten to call {{ function('cents_to_dollars') }}, unit test in place. We ran dbt test against an empty target schema — the way a developer does on a fresh branch before they bother with dbt run. The test errored. The error did not say what we expected.

Unit tests in dbt run against a real adapter connection. They mock ref() — you declare rows in YAML, dbt builds a temporary CTE under the model, the model SQL runs against the CTE instead of the source table. That works for macros: the macro is text, the test executes the expanded text, and the warehouse never needs anything beyond the empty schema dbt is pointed at.

It does not work the same way for UDFs. The model now calls cents_to_dollars(amount) — a function call the warehouse resolves at execution time. If the function isn’t in the schema dbt is pointed at, the query fails. Not as an assertion failure. As a warehouse SQL error, written in the warehouse’s vocabulary:

unit_tests:
  - name: test_cents_to_dollars
    model: orders
    given:
      - input: ref('raw_orders')
        rows:
          - {order_id: 1, amount: 1000}
    expect:
      rows:
        - {order_id: 1, amount_dollars: 10.0}

Run that test against a schema where the function has not been deployed — a freshly-created CI schema, a developer who ran dbt test before dbt run, a target where someone dropped the function out-of-band — and the failure reads like this:

Failure in test test_cents_to_dollars (models/schema.yml)
  Database Error in unit_test test_cents_to_dollars
    002003 (42883): SQL compilation error:
    Unknown function CENTS_TO_DOLLARS

“Unknown function” is not how a unit test usually announces itself. The reflex on a developer machine is to assume the test rows are wrong; on CI, that the assertion changed. Neither is true. The function simply isn’t there, and a unit test cannot mock its way around the absence the way it mocks a ref().

Unit tests on UDF-bearing models require the UDF to exist in the target warehouse at test time. The macro version of the same model would have passed without a warehouse object in sight.

Once we knew the test was telling the truth, the fix took one line of CI config. Run dbt build --select "resource_type:function" before dbt test — or, simpler, replace dbt test in CI with dbt build on the whole selector and let the resource-type ordering deploy functions first. dbt run won’t do it on its own. That single command swap is the operational consequence of the migration; everything in §5 assumes it.

Everything above came from one migration on one helper. Here is the order we ran it in, so the next helper is faster than the one that produced the post. Multiply by however many helpers are worth the deploy-order shift; the cost per helper drops sharply after the first.

Catalogue. Open macros/ and list every macro that returns a SQL expression callable inside a SELECT. Helpers that return DDL fragments, materialisation overrides, and audit-pattern macros stay as macros — UDFs are first-class but they are not a replacement for the meta-programming Jinja does. The candidates are the small, expression-shaped helpers: arithmetic conversions, string cleanups, type coercions.

Author. One .sql file per UDF under functions/, containing the CREATE OR REPLACE FUNCTION body. One entry in functions/schema.yml per UDF, declaring the argument names, types, and return type. The example from §1 is the whole pattern.

Rewrite. Replace the Jinja macro call in every model with a direct SQL call to the function. The Jinja goes away; the model gets shorter; the diff in target/compiled/ is the only one that matters.

Deploy. Run dbt build on the affected selector. The function is created first; the model and its unit tests run after; the order is implicit in the DAG. On the first run, the function will not exist in production yet, so this is also the moment to confirm your CI job runs dbt build rather than dbt test in isolation — otherwise the next pull request to touch the function will surprise the test step the way §4 describes.

Verify. Two checks. First, target/compiled/<model>.sql should show the function call where the macro expansion used to be — the §1 diff, in your own project. Second, change one character inside the function body and run dbt ls --select state:modified --state prod-artifacts/ — the function and every model that calls it via {{ function('cents_to_dollars') }} should both appear. If they do, the Slim CI behaviour from §3 is wired up correctly.

A UDF is not a better macro. It is a different resource type with a different deploy story, a different CI story, and a different test story. It earns its place when its body needs to be visible to CI — not because someone wrote a tutorial about the YAML. — Field note, §5