MACROS & JINJA

YOUR FIRST MACRO

Say three different models all convert cent amounts to dollars. Instead of repeating the arithmetic, put it in a macro — one file in your macros/ directory:

-- macros/cents_to_dollars.sql
{% macro cents_to_dollars(column_name, scale=2) %}
    ({{ column_name }} / 100)::numeric(16, {{ scale }})
{% endmacro %}

Call it from a model like any function:

-- models/staging/stg_payments.sql
select
    payment_id,
    order_id,
    {{ cents_to_dollars('amount_cents') }} as amount_usd
from {{ source('app_db', 'payments') }}

Run dvt compile and look at the result in target/compiled/ — the macro call is gone, replaced by the SQL it produces:

-- target/compiled/my_project/models/staging/stg_payments.sql
select
    payment_id,
    order_id,
    (amount_cents / 100)::numeric(16, 2) as amount_usd
from "analytics"."public"."payments"

That's the whole mechanism: macros expand at compile time, before any SQL reaches a database. Change the macro once and every model that calls it picks up the change on the next run.

JINJA IN DVT IS DBT'S JINJA

DVT is a wrapper around dbt, not a fork — your project is parsed and rendered by dbt's own Jinja renderer. Everything you know from dbt works unchanged, in every model, including federated ones:

select *
from {{ ref('stg_payments') }}                          -- another model
where created_at >= '{{ var("start_date", "2024-01-01") }}'   -- project variable

{{ var() }} reads from dbt_project.yml or the command line — dvt run --vars '{start_date: 2026-01-01}' overrides the default. {{ env_var('DBT_SCHEMA') }} reads an environment variable at parse time, which is how secrets and per-environment values stay out of your SQL files.

{{ config(...) }} at the top of a model sets its materialization and options, {{ source() }} reads declared sources, and control flow — {% if %}, {% for %}, {% set %} — behaves exactly as dbt documents it. Incremental models get is_incremental() and {{ this }}, in both incremental and f_incremental flavors.

WRITING MACROS: ARGUMENTS, DEFAULTS, BRANCHING

Macro parameters work like Python function arguments: positional, named, with optional defaults. In the example above, scale=2 is a default — {{ cents_to_dollars('amount_cents', 4) }} overrides it.

Macros can branch on the target context variable, which is handy for environment-specific SQL:

-- macros/limit_in_dev.sql
{% macro limit_in_dev(row_count=1000) %}
  {% if target.name == 'dev' %}
    limit {{ row_count }}
  {% endif %}
{% endmacro %}

-- models/marts/fct_orders.sql
select * from {{ ref('stg_orders') }}
{{ limit_in_dev() }}

In development the model compiles with a limit 1000; in production the macro renders to nothing. Macros can also call other macros, take kwargs, and use everything in dbt's Jinja context — ref, source, run_query, log. Whatever a dbt macro can do, a DVT macro can do.

UTILITY MACROS: DVT RUN-OPERATION

Not every macro belongs inside a model. Grants, cleanup jobs, one-off maintenance — for those you write a macro that does something rather than returns SQL, and invoke it directly with dvt run-operation:

-- macros/grant_select.sql
{% macro grant_select(role) %}
  {% set sql %}
    grant usage on schema {{ target.schema }} to {{ role }};
    grant select on all tables in schema {{ target.schema }} to {{ role }};
  {% endset %}
  {% do run_query(sql) %}
  {{ log("Granted select on " ~ target.schema ~ " to " ~ role, info=True) }}
{% endmacro %}

$ dvt run-operation grant_select --args '{role: reporting}'
14:02:11  Granted select on analytics to reporting

--args takes a YAML dictionary mapping argument names to values. dvt run-operationhas identical semantics to dbt's — and note where it runs: run_query executes on the default target connection (or whatever --targetyou pass). That's not an accident. It's the doctrine of the next section.

THE DVT DOCTRINE: MACROS ARE TARGET-SIDE

Here is the one rule DVT adds, and it's worth internalizing: macros belong to models that run on the default target. A macro's body is SQL, and SQL is written in some engine's dialect — the ::numeric(16, 2)cast above is the default target's spelling. Standard models (view / table / incremental), snapshots, tests and analyses all execute on that target, so a macro they share is judged in one dialect: the target's.

Federated models (f_table / f_incremental) are a different world: their SQL is DuckDB dialect by contract, whatever the default target is. Calling your macro from a federated model is a project error — plainly, don't do it. A macro serving both worlds would need to be two dialects at once, and no tool can transpile it correctly for either. Keep federated SQL self-contained; keep macros with the target-side models they were written for.

The payoff of the rule is what happens when you switch the default target. Because macros are target-side, dvt flip-target-toknows exactly what they are — target-dialect SQL — and transpiles their bodies to the new engine's dialect along with your models, snapshots and tests. The {% macro %} tag, its name and its parameters come back byte-for-byte; only the SQL body translates. Our cents_to_dollars lands on SQL Server as cast(amount_cents / 100 as numeric(16, 2)) without you touching the file.

JINJA CONTEXT REFERENCE

The essentials of the rendering context, all dbt's own. This is a working subset — dbt's full context (every function, every property) is available in DVT because dbt is doing the rendering.

RUN-OPERATION REFERENCE