Module 7: Macros Docs And Lineage

A reusable revenue macro for Kiosko

Next →

Description

fact_orders.sql has calculated revenue with o.quantity * o.unit_price since module 3 — the same expression, with no change at all, across modules 4 (tests), 5 (snapshot), and 6 (incremental). This lesson extracts it into a real macro, calculate_revenue, which you're going to write with the same syntax from lesson 2 and really apply to the model. The difference from the previous lesson is that here there's no practice file to delete at the end: calculate_revenue.sql stays in the project, versioned, and fact_orders.sql really changes — with the obligation to rebuild it with --full-refresh, the flag you already know from module 6's lesson 7, so the change applies to all 40 rows, not just the most recent partition.

Connection to the module. Lesson 2 gave you a macro's minimal syntax over a practice example. This lesson applies that same syntax to the real problem the module's introduction raised: a math expression, repeated with no reuse mechanism at all, extracted to a single place. And it connects directly with module 6: changing a SELECT's logic in an already-built incremental model is, precisely, the first of the three scenarios that module's lesson 7 identified as requiring --full-refresh.

Worked example: calculate_revenue

Create the file, with the same macro syntax you already used in lesson 2:

-- macros/calculate_revenue.sql
{% macro calculate_revenue(quantity_col, price_col) -%}
    ({{ quantity_col }} * {{ price_col }})
{%- endmacro %}

Two parameters, with no default value at all: quantity_col and price_col, the names of the two columns being multiplied. Unlike discounted_price in the previous lesson, this macro doesn't need a third configurable parameter — Kiosko's revenue calculation is always "quantity times unit price," with no variation by case — so both parameters are required.

Verify it compiles correctly before touching any model, with the same --inline technique from lesson 2:

dbt compile --inline "select {{ calculate_revenue('quantity', 'unit_price') }} as revenue from {{ ref('stg_orders') }}"

What to expect.

Compiled inline node is:
select (quantity * unit_price) as revenue from "kiosko"."main"."stg_orders"

(quantity * unit_price) — exactly the same expression fact_orders.sql writes by hand today, now produced by the macro. With the macro already verified in isolation, it's time to apply it to the real model.

Modifying fact_orders.sql

Replace the inline expression with the macro invocation. This is the only change in the whole file:

-- models/marts/fact_orders.sql
{{
    config(
        materialized='incremental',
        incremental_strategy='delete+insert',
        unique_key='order_id'
    )
}}

select
    o.order_id,
    o.store_id,
    o.product_id,
    o.quantity,
    o.unit_price,
    {{ calculate_revenue('o.quantity', 'o.unit_price') }} as revenue,
    o.order_ts
from {{ ref('stg_orders') }} as o
inner join {{ ref('dim_store') }} as ds
    on o.store_id = ds.store_id
inner join {{ ref('dim_date') }} as dd
    on cast(o.order_ts as date) = dd.calendar_date

{% if is_incremental() %}
where cast(o.order_ts as date) = cast('{{ var("run_date") }}' as date)
{% endif %}

o.quantity * o.unit_price as revenue, became {{ calculate_revenue('o.quantity', 'o.unit_price') }} as revenue, — a single line, with the as revenue alias untouched. Notice the arguments carry the o. prefix ('o.quantity', not 'quantity'), because fact_orders's SELECT uses a table alias (from ... as o) — the macro knows nothing about table aliases, it simply inserts the exact text it received, so the argument has to include whatever prefix the final SQL needs.

Confirm with dbt compile what SQL this change really produces:

dbt compile --select fact_orders --full-refresh
cat target/compiled/kiosko_analytics/models/marts/fact_orders.sql

What to expect.

select
    o.order_id,
    o.store_id,
    o.product_id,
    o.quantity,
    o.unit_price,
    (o.quantity * o.unit_price) as revenue,
    o.order_ts
from "kiosko"."main"."stg_orders" as o
inner join "kiosko"."main"."dim_store" as ds
    on o.store_id = ds.store_id
inner join "kiosko"."main"."dim_date" as dd
    on cast(o.order_ts as date) = dd.calendar_date

The only visible change compared with module 6's compiled SQL is the parenthesis around o.quantity * o.unit_price — the one the macro adds in its body. Mathematically, (o.quantity * o.unit_price) and o.quantity * o.unit_price are exactly the same expression; the parenthesis doesn't change the result, it just makes explicit that the macro controls that whole portion of the calculation.

Why this run needs --full-refresh

fact_orders already exists as an incremental table since module 6 — you're not creating it from scratch, you're modifying the SELECT of a model that already has 40 rows built with the old logic. This is, precisely, the first of the three scenarios module 6's lesson 7 named: "You changed the SELECT's logic." A normal incremental run, with --vars, would only rebuild the partition for the date you pass it — leaving the other partitions with the revenue the model's previous version calculated, before the macro existed. Even though the numeric value wouldn't change in this particular case (quantity * unit_price and (quantity * unit_price) give the same number), the complete table would end up built with two different versions of the code — some rows from the old SELECT, some from the new — something no future run should silently inherit.

Run the --full-refresh you already compiled above:

dbt run --select fact_orders --full-refresh

What to expect.

Running with dbt=1.12.2
Registered adapter: duckdb=1.11.0
Found 7 models, 15 data tests, 1 snapshot, 4 sources, 502 macros

Concurrency: 4 threads (target='dev')

1 of 1 START sql incremental model main.fact_orders ............................ [RUN]
1 of 1 OK created sql incremental model main.fact_orders ....................... [OK in 0.09s]

Finished running 1 incremental model in 0 hours 0 minutes and 0.19 seconds (0.19s).

Completed successfully

Done. PASS=1 WARN=0 ERROR=0 SKIP=0 NO-OP=0 REUSED=0 TOTAL=1

Notice the header: 502 macros, one more than the 501 you'd been seeing since module 4 — the total count includes calculate_revenue, alongside the ones dbt-core ships with out of the box. This number alone already confirms dbt recognizes the new macro as part of the project, before looking at any result.

Confirming the result didn't change

The check that really matters — the same one every mini-project has required since module 3:

dbt show --inline "select count(*) as n_rows, sum(revenue) as total_revenue from {{ ref('fact_orders') }}"

What to expect.

Previewing inline node:
| n_rows | total_revenue |
| ------ | -------------- |
|     40 |         106.15 |

40 rows, 106.15 revenue — the exact same number since module 3. Extracting the calculation into a macro didn't change the result by a single cent; it only changed where the logic that produces it lives. Also confirm the complete test suite is still green — none of the fifteen data_tests module 4 declared depend on whether revenue was calculated with a macro or an inline expression, so none of them should fail:

dbt test

What to expect.

Found 7 models, 15 data tests, 1 snapshot, 4 sources, 502 macros

Finished running 15 data tests in 0 hours 0 minutes and 0.20 seconds (0.20s).

Completed successfully

Done. PASS=15 WARN=0 ERROR=0 SKIP=0 NO-OP=0 REUSED=0 TOTAL=15

Fifteen out of fifteen, with no change — the same result that closed module 6, now with revenue calculated by a macro instead of a hand-repeated expression.

Going deeper: why swapping the argument order didn't break anything, and why that's not a guarantee

calculate_revenue(quantity_col, price_col) multiplies its two arguments — and multiplication is commutative: a * b is always equal to b * a, regardless of order. Test this by invoking it with the arguments reversed:

dbt compile --inline "select {{ calculate_revenue('unit_price', 'quantity') }} as revenue_swapped, {{ calculate_revenue('quantity', 'unit_price') }} as revenue_normal from {{ ref('stg_orders') }}"

What to expect.

Compiled inline node is:
select (unit_price * quantity) as revenue_swapped, (quantity * unit_price) as revenue_normal from "kiosko"."main"."stg_orders"

Both produce exactly the same numeric value — unit_price * quantity and quantity * unit_price are mathematically identical. But notice this is a property of this specific operation, not a guarantee dbt or Jinja offers: if calculate_revenue ever grew to subtract a discount column (quantity_col * price_col - discount_col), swapping the arguments' order would no longer give the same result — subtraction isn't commutative. The underlying lesson, beyond this particular case: the parameter names (quantity_col, price_col) exist to communicate intent to whoever reads the code, not just to make the macro work — passing arguments in the right order matters, even though today, with a simple multiplication, an ordering mistake doesn't show up in any visibly different number.

Common mistakes

Forgetting --full-refresh and trusting that a normal dbt run already propagated the change. What happens: someone modifies fact_orders.sql to use calculate_revenue, runs dbt run --select fact_orders --vars '{"run_date": "..."}' (a normal incremental run, without --full-refresh), and assumes the whole table already reflects the change. Why it happens: the command finishes with PASS=1, no error at all — nothing in the output warns that only one partition got touched. How to spot it: in this particular case, with a commutative multiplication, no number would look different — this lesson's Going deeper already explained why — so the mistake stays invisible in Kiosko. The way to spot it in a real project is to ask yourself, every time you change an already-built incremental model's SELECT: does this run rebuild the whole table, or just the partition I asked for? How to fix it: any change to an incremental model's SELECT logic — not just a change that alters the numeric result, but any change in how it's calculated — requires --full-refresh at least once, to guarantee the complete table ends up built by the same code, end to end. This is, exactly, the first scenario module 6's lesson 7 already named.

Passing the arguments without the alias prefix the model needs. What happens: someone writes {{ calculate_revenue('quantity', 'unit_price') }} inside fact_orders.sql — copying the isolated-verification example, with no o. prefix — instead of {{ calculate_revenue('o.quantity', 'o.unit_price') }}. Why it happens: the dbt compile --inline check against stg_orders doesn't need any alias (there's no JOIN involved), so it's easy to copy that version without thinking about fact_orders.sql actually having one. How to spot it: if quantity and unit_price aren't ambiguous across the JOIN's tables, the model might compile anyway — but if dim_store or dim_date ever had a column with the same name, dbt would fail with an ambiguous-column error. How to fix it: any argument invoked inside a model with a table alias needs that same prefix inside the string you pass to the macro — the macro never "knows" what SQL context it's being used in, it simply inserts the text exactly as it received it.

Thinking the macro needs to know Kiosko's real column names. What happens: someone assumes calculate_revenue is, somehow, tied to quantity and unit_price as fixed names, and is surprised when the Going deeper section's invocation (calculate_revenue('unit_price', 'quantity')) also works. Why it happens: the macro's parameter names (quantity_col, price_col) look so much like fact_orders's real column names that it's easy to think they're linked. How to spot it: invoke the macro over any pair of numeric columns, even ones not named quantity or unit_price — for example, calculate_revenue('unit_cost', 'quantity') over combined stg_products and stg_orders — and confirm it compiles with no error at all, even though the result wouldn't make business sense in that case. How to fix it: a macro doesn't know any schema — it receives text, it substitutes text. That calculate_revenue is meant for quantity and unit_price is a decision made by whoever invokes it, not a restriction the macro imposes on its own.

Exercises

Exercise 1 — Reproduce the change from module 6's state. If you have access to a copy of fact_orders.sql as it was left at the end of module 6 (with o.quantity * o.unit_price inline), apply this lesson's change without looking at the worked example, and confirm with dbt run --select fact_orders --full-refresh followed by the count query that you land at exactly 40 rows and 106.15 revenue.

See solution

The complete change is a single line: o.quantity * o.unit_price as revenue, becomes {{ calculate_revenue('o.quantity', 'o.unit_price') }} as revenue,, with no other part of the file touched — not the config() block, not the JOINs, not the {% if is_incremental() %} block. If the result doesn't match, first check that macros/calculate_revenue.sql exists and that dbt ls --resource-type model (or any dbt command) reports 502 macros in its header, not 501 — a 501 count means the macro didn't end up saved in the right folder.

Exercise 2 — Simulate the scenario of a real formula change. Temporarily modify calculate_revenue to subtract a fixed 5% discount: {{ quantity_col }} * {{ price_col }} * 0.95. Run dbt run --select fact_orders --vars '{"run_date": "2026-08-09"}' (with no --full-refresh, a normal incremental run), and then the count query. Does total_revenue reflect the discount across all 40 rows, or only in some?

See solution

Only the run_date: "2026-08-09" rows (three orders, based on the per-date breakdown you already know from module 6) would reflect the 5% discount — the other 37 rows would keep the revenue the macro's previous version calculated, with no discount at all, because a normal incremental run with delete+insert only touches the partition you asked for. total_revenue drops to 105.96 — neither the original (106.15) nor what applying the discount to all 40 rows would produce, an in-between value that mixes two different versions of the calculation within the same table: concrete evidence of why this kind of change requires --full-refresh, not a partial run. Undo the change to calculate_revenue.sql (go back to {{ quantity_col }} * {{ price_col }}, with no * 0.95) and run dbt run --select fact_orders --full-refresh before continuing, to leave the project in its reference state: 40 rows, 106.15.

Exercise 3 — Argue why extracting calculate_revenue didn't change fact_orders's grain or columns. In 2-3 sentences, explain why this lesson's change is, precisely, the same kind as the one module 6 made when converting fact_orders to incremental: a change in how the model gets built, never in what it contains.

See solution

fact_orders still has exactly the same seven columns (order_id, store_id, product_id, quantity, unit_price, revenue, order_ts) and the same order-line grain it's had since module 3 — the macro doesn't add, remove, or rename any column, it only changes how the expression that calculates one of them gets written. It's the same pattern you already recognized in module 6, when fact_orders went from table to incremental with no column gained or lost: both changes modify the model's implementation — how dbt builds the table, or how a formula gets expressed inside the SELECT — never the contract that model offers to whoever queries it.

Summary and next step

In this lesson you extracted o.quantity * o.unit_price from fact_orders.sql into macros/calculate_revenue.sql, a real macro that stays versioned in the project. You verified the macro in isolation with dbt compile --inline, applied the change to the real model, and rebuilt the complete table with --full-refresh — the exact scenario module 6's lesson 7 already anticipated: a change to an incremental model's SELECT logic. You confirmed the same result as always (40 rows, 106.15 revenue, fifteen tests green) and saw, in every command's header, that the project now recognizes 502 macros instead of 501.

Before moving on you should be able to: explain why a change to an incremental model's logic requires --full-refresh, even when the numeric result doesn't visibly change; and write, from memory, the complete {{ calculate_revenue('o.quantity', 'o.unit_price') }} invocation inside a SELECT.

Lesson 4 changes the topic within the same module: instead of reusable logic, you're going to document what each model and column means across the project, including a way to reuse documentation text with the same discipline this lesson applied to SQL.

Resources

← PreviousWriting your first macroNext →Documenting models and columns