dbt Macros vs Persistent SQL UDFs in BigQuery: Choose Right

dbt Macros vs Persistent SQL UDFs in BigQuery: Which Should You Use?

The choice between dbt macros and persistent SQL UDFs in BigQuery determines whether your shared business logic is fast, traceable, and cost-efficient — or whether it quietly inflates your slot usage, breaks your DAG lineage, and becomes a maintenance liability. Macros belong in dbt when the logic is SQL-generative and transformation-scoped. Persistent UDFs belong in BigQuery when the logic must be shared across tools, executed row-by-row at query time, or invoked outside your dbt DAG entirely.

If you have landed on this page, you are probably staring at a block of complex business logic — a fee calculation, a currency conversion function, a fraud scoring expression — and trying to decide where it should live. The wrong choice is not dramatic. It does not throw an error. It just makes things quietly worse over time: a UDF that should have been a macro slows every model that calls it; a macro that should have been a UDF gets copy-pasted into five downstream models and maintained in none of them. This post gives you a clear decision framework, built from delivery experience on real projects, so you can make the right call the first time.

What Is the Actual Difference Between a dbt Macro and a Persistent SQL UDF?

A dbt macro is a Jinja-templated SQL snippet that runs at compile time. When dbt compiles your project, it renders the macro inline — the function body is copied directly into the generated SQL that BigQuery actually executes. BigQuery never sees the macro as a callable object; it sees only the expanded SQL. This means macros are transparent to BigQuery's query optimiser: partition pruning, predicate pushdown, and join reordering can all operate on the fully expanded query as normal.

A persistent SQL UDF, by contrast, is a named function object stored in a BigQuery dataset. It is created once with a CREATE FUNCTION statement and called by name in any SQL query — from dbt models, from Looker explores, from scheduled queries, from the BigQuery console, from any tool with warehouse access. BigQuery resolves and executes the UDF at query runtime, not compile time.

This is the core asymmetry: macros are a dbt abstraction that disappears before BigQuery ever runs the query; UDFs are a BigQuery object that persists in your warehouse independently of dbt.

From a practical standpoint, this has three downstream consequences that most teams do not fully appreciate until something breaks:

1. Optimiser visibility. Because a macro expands inline, BigQuery's optimiser can see through it. A persistent SQL UDF — especially a JavaScript UDF — is treated as an opaque callable. Google's own BigQuery best practices documentation notes that calling a JavaScript UDF requires spinning up a subprocess, which directly impacts query performance. SQL UDFs are better, but they are still less transparent to the optimiser than inline SQL.

2. Lineage coverage. dbt tracks references made through {{ ref() }} and {{ source() }} calls. A persistent UDF called inside a dbt model body — without a corresponding ref() or source() — is invisible to dbt's DAG. If the UDF reads from a BigQuery table internally, that dependency does not appear in your lineage graph. You can rebuild a model without knowing that an upstream table the UDF depends on has changed.

3. Cross-tool availability. A dbt macro only exists inside your dbt project. A persistent UDF lives in your BigQuery dataset and is callable from any SQL interface that has the right IAM permissions. If your BI tool, your data science team, or an external scheduled query needs to call the same logic, a UDF is the only option that works without duplicating code.

When Should You Use a dbt Macro?

Use a dbt macro when the logic is transformation-scoped — when it is about how you shape or filter data inside your dbt pipeline, and nothing outside the pipeline needs to call it.

The canonical macro use cases are:

• Generating boilerplate SQL patterns — date spine generation, unpivoting column arrays, building standard WHERE clause filters across multiple models
• Encoding conditional logic that varies by environment — different fiscal year start dates in dev vs prod, different exchange rate sources by deployment region
• Applying DRY principles to staging model patterns — a clean_currency_code() macro that standardises ISO codes and gets inlined into every staging model that touches payment data
• Wrapping dbt-specific control flow — using {{ is_incremental() }}, {{ this }}, or {{ run_started_at }} inside the logic, which have no meaning outside a dbt compile context

A macro is also the right choice when you want BigQuery's query optimiser to reason about the generated SQL. If your logic involves filtering on a partition column, you want that predicate visible to BigQuery at compile time — not hidden inside a UDF call that the optimiser cannot look through.

One pattern we see repeatedly in early-stage analytics projects: teams reach for macros when they want to share logic across tools, then wonder why their BI layer cannot call the macro directly. The macro was the right choice for the dbt pipeline; the mistake was not also creating a persistent UDF for the BI layer to use. The two are not mutually exclusive. A macro can generate the same SQL expression that a persistent UDF encapsulates — the macro handles the dbt context, the UDF handles the external context.

If you are building out your dbt project architecture and want a second opinion on where your shared business logic should live, explore how Fintel Analytics approaches this — we work with growth-stage companies globally to design and deliver dbt projects that are built correctly from the start, not refactored expensively later.

When Should You Use a Persistent SQL UDF?

Use a persistent SQL UDF when the logic must survive outside your dbt compile context — when it is called by more than one tool, when it encapsulates complex row-level computation that would be unwieldy inline, or when your organisation needs a single governed definition of a calculation that any analyst can call without copying SQL.

The strongest persistent UDF use cases are:

• Cross-tool shared logic — a payment fee calculation that your dbt models, your Holistics or Looker explores, and your finance team's ad-hoc BigQuery queries all need to call identically. The UDF is the single source of truth; it lives in a centrally governed dataset and is referenced by name everywhere.
• Complex row-level expressions — bucketing logic, scoring functions, or encoding transformations that operate on individual rows and would generate enormous inline SQL if expanded by a macro across hundreds of model references
• Logic authored outside dbt — your data science team writes a feature engineering function in SQL and wants it callable from both their notebook environment and your production dbt models. A persistent UDF in a shared dataset is the only architecture that works here.
• Encapsulating version-controlled business rules — when your compliance or finance team needs to audit exactly what formula was applied to a dataset on a given date, a persistent UDF deployed through a CI/CD pipeline gives you that traceability in a way that an inlined macro does not.

One critical nuance: when you choose a persistent SQL UDF over a JavaScript UDF, you get meaningfully better performance. Google's BigQuery documentation is explicit on this — use SQL UDFs for calculations that SQL can express cleanly, because the query optimiser can apply optimisations to SQL UDF definitions. JavaScript UDFs consume more slot resources compared to standard SQL queries, decreasing job performance. In practice, we have seen JavaScript UDFs on a high-cardinality join column add 40–60% to slot consumption on models that were otherwise well-optimised — a cost that compound across every run.

If you are already using JavaScript UDFs and suspect they are inflating your BigQuery bill, our earlier post on JavaScript UDFs in dbt BigQuery: The Slot Cost You're Missing covers how to detect and quantify the overhead.

The Hybrid Pattern Most Teams Miss

The most robust pattern — and the one that experienced teams eventually converge on — is using macros and persistent UDFs together, with each doing the job it is suited for.

Here is how this looks in practice:

Step 1 — Define the persistent SQL UDF in a governed dataset. Create a shared_functions dataset in BigQuery. Deploy your core business logic UDFs here via dbt using a custom materialisation or a post-hook CREATE OR REPLACE FUNCTION statement. Version-control the UDF definition in your dbt project like any other model.

Step 2 — Call the UDF from dbt models where row-level computation is needed. Reference it by fully qualified name: shared_functions.calculate_net_settlement(gross_amount, fee_rate, currency_code). This is clean, readable, and testable.

Step 3 — Use macros for everything that generates SQL structure. Partition filters, date range expressions, environment-specific flags, column selection patterns — these stay as macros. They expand inline and remain fully visible to the BigQuery optimiser.

Step 4 — Document the boundary explicitly in your dbt project README. The single biggest cause of logic drift we see is teams not writing down the rule. Document which dataset holds governed UDFs, who has permission to deploy to it, and the test coverage requirement before any UDF goes to production.

A pattern we see repeatedly when onboarding new clients: a payments or fintech team has encoded their interchange fee calculation as a macro that is copied into seventeen models, each with a slightly different parameter arrangement. When the fee schedule changes, six of the seventeen models get updated, three do not, and nobody notices until the month-end reconciliation fails. Moving that calculation into a governed persistent SQL UDF, deployed through a CI/CD pipeline with mandatory schema tests, eliminates that entire class of error. One of our clients described this as going from "hoping the right formula is everywhere" to "knowing it is."

For reference, the access control layer for your shared UDF dataset deserves the same attention as your dbt authorised views. See our post on dbt Authorized Views in BigQuery: Fix Broken Access Control for the IAM and dataset permission patterns that apply directly here.

How to Audit Your Current Project for Macro vs UDF Misuse

Before you can fix the pattern, you need to find the problem. Here is the audit approach we use when reviewing a new client's dbt project:

Check 1 — Find macros that are called in more than four distinct models. Run a grep across your models/ directory for every macro invocation. Any macro appearing in five or more models with identical parameters is a UDF candidate — it is already behaving like a shared function; it is just not governed like one.

grep -r "{{ my_macro_name" models/ | wc -l

Check 2 — Find JavaScript UDFs referenced in model bodies. Search for CREATE TEMP FUNCTION or direct project.dataset.function_name( calls in your model SQL. Any JavaScript UDF referenced in a hot model (one that runs on every pipeline execution against a large table) should be evaluated for conversion to a SQL UDF or an inlined macro.

Check 3 — Find logic that exists in both your dbt macros and your BI layer separately. Pull the custom SQL expressions from your Looker LookML or Holistics calculated fields and compare them against your macro library. If the same formula exists in both places with slight differences, you have a single source of truth problem — a persistent UDF resolves it.

Check 4 — Check UDF dependencies that are invisible to dbt lineage. If any of your persistent UDFs reference BigQuery tables internally, those dependencies do not appear in dbt docs generate lineage. Document them manually and consider whether restructuring — passing the data in as a parameter rather than having the UDF query a table — would give you better transparency.

Check 5 — Cost attribution by model. Use INFORMATION_SCHEMA.JOBS to pull bytes billed and slot milliseconds by job_id, joined to your dbt query labels. Models that consume disproportionate slot resources are candidates for macro-vs-UDF review — often the culprit is a JavaScript UDF in a join condition or a WHERE clause that forces a full row evaluation on every record.

Frequently Asked Questions

Q: Can I use a dbt macro and a persistent SQL UDF for the same logic?

A: Yes — and in many projects this is the right answer. Use a macro to generate the SQL inside your dbt pipeline where inline expansion is preferred by the optimiser. Use a persistent UDF in a shared BigQuery dataset for any tool outside dbt that needs to call the same logic. The two are not mutually exclusive and can be kept in sync through version-controlled deployment.

Q: Do persistent SQL UDFs show up in dbt docs lineage?

A: Not automatically. dbt lineage tracks dependencies declared through ref() and source() calls. A persistent UDF called by name inside a model body — with no ref() — is invisible to the DAG. If your UDF internally queries a BigQuery table, that upstream dependency will not appear in dbt docs generate. You need to document it manually or restructure the UDF to receive data as parameters rather than querying tables directly.

Q: Are JavaScript UDFs in BigQuery significantly more expensive than SQL UDFs?

A: Yes, materially so. BigQuery's own documentation states that JavaScript UDFs typically consume more slot resources compared to standard SQL queries. In practice this can mean 30–70% additional slot consumption on models that call JavaScript UDFs at row level across large tables. SQL UDFs are always preferable when the logic can be expressed in standard SQL, because the query optimiser can apply optimisations to SQL UDF definitions.

Q: How do I deploy and version-control persistent BigQuery UDFs in dbt?

A: The most common approach is to define the CREATE OR REPLACE FUNCTION DDL in a file and invoke it via a dbt post-hook or run-operation. As of dbt v1.12+, native UDF materialisation support is available for BigQuery, which allows you to manage UDFs as first-class objects in your DAG. This gives you dependency tracking, versioning, and test coverage in the same workflow as your models.

Q: Should shared business logic always go into a persistent UDF rather than a macro?

A: No. If the logic is only ever called inside your dbt pipeline and benefits from inline SQL expansion — particularly for partition filter transparency — a macro is the right choice. Persistent UDFs add governance overhead and a deployment step; that overhead is worth it when the logic crosses tool boundaries or when you need a single, auditable definition callable from any SQL interface. If in doubt, start with a macro and promote to a UDF when a second tool needs to call the same logic.

At Fintel Analytics, we review and refactor dbt project architectures for growth-stage companies where this exact decision — macro vs UDF, inline vs governed — has compounded quietly into inflated BigQuery bills, broken lineage, and business logic that nobody fully trusts anymore. If your team is building on BigQuery and dbt and you are not confident that your shared logic is governed, performant, and consistently applied across every tool that touches it, that is a problem with a clear, deliverable fix — and it is exactly the kind of engagement we do.