Writing your first macro

Description

Before touching fact_orders.sql, it's worth learning a macro's syntax over an example that isn't part of Kiosko's project yet — that way any syntax mistake, any confusion between {{ }} and {% %}, happens on a practice file you can delete with no consequences, instead of on the model that already calculates the real revenue of 40 orders. This lesson writes discounted_price, a two-parameter macro that calculates a discounted price, verifies it with dbt compile --inline and dbt show --inline — the same two tools you already used since module 2 to inspect compiled SQL without needing to save a model — and deletes it at the end. Lesson 3 writes the real macro, calculate_revenue, on top of that same syntax foundation.

Connection to the module. Lesson 1 gave you the complete analogy — a macro is a formula you write once and use in ten documents. This lesson builds that analogy's first concrete piece: the exact syntax Jinja requires to define and use one of those formulas inside a dbt project.

An analogy: the template with a field that fills in differently case by case

Think of a business-letter template, with a [NAME] field in the greeting. The template gets written once — "Dear [NAME], thank you for your purchase..." — and gets reused for hundreds of different recipients, without rewriting the whole letter every time; the only thing that changes is what text replaces the blank field. A Jinja macro works exactly the same way: {% macro %} defines the complete template, with one or more blank fields (its arguments), and every time you invoke it with {{ }}, you tell it what to substitute into those fields for that particular case.

Two Jinja delimiters, two different jobs

Before the first example, it's worth pinning down a distinction you're going to use in every line of this module: Jinja has two kinds of braces, and mixing them up is the most common mistake for anyone just starting to write macros.

• {% ... %} — statements. Used to define structure: opening and closing a macro block ({% macro %} / {% endmacro %}), a conditional ({% if %} / {% endif %}), a loop. It produces no text by itself — it just tells Jinja "something starts or ends here."
• {{ ... }} — expressions. Used to insert a value into the output text: a variable, a macro invocation's result, a column. Everything inside {{ }} gets evaluated and its result literally replaces those braces in the compiled SQL.

You already used this distinction without naming it explicitly since module 2: {{ source('kiosko_raw', 'orders') }} is an expression (it produces text: the file's path), while module 6's {% if is_incremental() %} is a statement (it controls which block of SQL gets included, without producing any text itself). Defining a macro uses both: {% macro %} (statement, no output) to open the mold, and any {{ argument }} inside the body (expression, with output) to insert what the macro received.

Worked example: discounted_price, a practice macro

Create the file, inside the macros/ folder dbt_project.yml has already reserved since module 1:

-- macros/discounted_price.sql
{% macro discounted_price(price_col, pct=0.10) -%}
    ({{ price_col }} * (1 - {{ pct }}))
{%- endmacro %}

Read this piece by piece:

• {% macro discounted_price(price_col, pct=0.10) %} opens the definition. discounted_price is the name you're going to use to invoke it; price_col and pct are its two parameters.
• pct=0.10 — the =0.10 declares a default value. If whoever invokes the macro doesn't pass a second argument, pct automatically gets 0.10. price_col, on the other hand, has no default value — it's a required parameter; omitting it produces an error, as you're going to confirm in Common mistakes.
• ({{ price_col }} * (1 - {{ pct }})) is the body — the SQL the macro produces, with two blank fields ({{ price_col }}, {{ pct }}) Jinja substitutes with whatever it received on each invocation.
• {% endmacro %} closes the definition. Without this closing tag, dbt doesn't know where the macro's body ends — you're going to see the exact error forgetting it produces in Common mistakes.
• -%} and {%- are whitespace control: the dash before or after the percent sign tells Jinja to trim the line breaks and spaces adjacent to that tag. Without them, the compiled SQL would end up with loose line breaks around the parenthesis — functionally correct, but visually messy. You're going to confirm the exact difference in this lesson's Going deeper section.

Verifying the macro with no model at all, with dbt compile --inline

discounted_price doesn't need its own .sql model to be tested — the same --inline technique you already used since module 2 to verify source() works, unchanged, to verify a macro:

dbt compile --inline "select {{ discounted_price('4.50', 0.10) }} as sale_price"

What to expect.

Compiled inline node is:
select (4.50 * (1 - 0.1)) as sale_price

Notice a detail you're going to see again later in this same lesson: 0.10 became 0.1 — Jinja received 0.10 as a number, not as text (it's not quoted in the invocation), and when rendering it as part of the SQL, Python (the language behind Jinja) drops any float's trailing zero. The mathematical value doesn't change — 0.1 and 0.10 are the same number — but it's worth recognizing this behavior now, so you're not surprised if some number you pass unquoted shows up "trimmed" in the compiled SQL.

Also notice something structural: the compiled SQL contains not a trace of Jinja. {% macro %}, {{ }}, the name discounted_price — none of that ever reaches the warehouse. Jinja is a templating layer that gets fully resolved, on your machine, before DuckDB ever sees a single line of SQL. This is the same guarantee you already saw with is_incremental() in module 6: as far as the database engine is concerned, a macro never existed — only the plain text it produced exists.

Now actually run it, with dbt show --inline, using pct's default value (without passing it):

dbt show --inline "select {{ discounted_price('4.50') }} as default_10pct, {{ discounted_price('4.50', 0.25) }} as custom_25pct"

What to expect.

Previewing inline node:
| default_10pct | custom_25pct |
| -------------- | ------------ |
|           4.05 |        3.375 |

default_10pct uses the default 0.10 (4.50 with a 10% discount); custom_25pct passes 0.25 explicitly (4.50 with a 25% discount). The same macro, with no change to its code, produces two different results depending on what you passed it — precisely the payoff the business-letter template analogy promised.

Applying the macro over a real column, not a literal

The two examples above used '4.50', a quoted number — a SQL literal, not a column. A macro doesn't tell the two apart: what it receives as an argument is, always, text that gets substituted as-is into the compiled SQL. Confirm this by invoking it over a real stg_orders column:

dbt show --inline "select order_id, unit_price, {{ discounted_price('unit_price', 0.10) }} as sale_price from {{ ref('stg_orders') }} order by order_id" --limit 3

What to expect.

Previewing inline node:
| order_id | unit_price | sale_price |
| -------- | ---------- | ---------- |
| ORD-1001 |       0.55 |      0.495 |
| ORD-1002 |       1.20 |      1.080 |
| ORD-1003 |       0.75 |      0.675 |

'unit_price' — no quotes inside the argument, but quotes around the whole argument, because it's a Python/Jinja string that contains a column name — produces unit_price * (1 - 0.10) in the compiled SQL, the same expression as before, now applied row by row. This is, precisely, the pattern lesson 3 is going to use with calculate_revenue: a macro's arguments are almost always going to be column names, passed as quoted strings.

Going deeper: what whitespace control changes

Remove, for a moment, the whitespace-control dashes (-%}, {%-) and compile again:

-- version WITHOUT whitespace control, just for comparison
{% macro discounted_price_messy(price_col, pct=0.10) %}
    ({{ price_col }} * (1 - {{ pct }}))
{% endmacro %}

dbt compile --inline "select {{ discounted_price_messy('4.50', 0.10) }} as sale_price"

What to expect (the compiled SQL, with its real line breaks — also notice Jinja still renders 0.10 as 0.1, with no trailing zero, because internally it treats it as a floating-point number, not text):

Compiled inline node is:
select 
    (4.50 * (1 - 0.1))
 as sale_price

The SQL is still valid — DuckDB ignores extra whitespace and line breaks with no problem — but two loose line breaks are left around the parenthesis, inherited directly from how the macro's body was formatted in the .sql file. With a single macro this is just a cosmetic detail; if you concatenated several macros in a row with no whitespace control, those loose line breaks would pile up one after another. -%} at the end of the opening tag trims all the whitespace that follows; {%- at the start of the closing tag trims all the whitespace that precedes it — with both, the same compile ends up on a single clean line, as you already saw in the worked example (select (4.50 * (1 - 0.1)) as sale_price). It's a style decision, not a correctness one — but it's the one you're going to use in calculate_revenue.sql in lesson 3, the same one you already used without noticing in module 4's test_is_positive.sql.

Cleaning up the practice file

discounted_price served its purpose: demonstrating a macro's minimal syntax without touching the real project. Delete it before moving on to lesson 3, so it doesn't get confused with calculate_revenue, the macro that is going to stay versioned in the project:

rm macros/discounted_price.sql

Confirm dbt no longer recognizes it:

dbt compile --inline "select {{ discounted_price('4.50') }} as x"

What to expect.

Encountered an error:
Runtime Error
  Compilation Error in sql_operation inline_query (from remote system.sql)
    'discounted_price' is undefined. This can happen when calling a macro that does not exist. Check for typos and/or install package dependencies with "dbt deps".

This is the same error you're going to diagnose in Common mistakes if you ever invoke a macro with a misspelled name — it's worth seeing now, on a file you deleted on purpose, so you recognize it right away when it shows up by accident.

Common mistakes

Forgetting {% endmacro %}. What happens: someone writes the macro's body and keeps going with the rest of the file, without closing the block. Why it happens: unlike an {% if %}, whose {% endif %} usually gets written almost at the same time as the opening line, a macro with a long body makes it easy to lose track of the missing close. How to spot it: dbt fails to compile anything in the project, not just the broken macro, with a specific message:

Compilation Error
  Reached EOF without finding a close tag for macro (searched from line 1)

"EOF" (end of file) means Jinja reached the complete file's end without finding the {% endmacro %} it expected — everything after the opening, including the rest of the file, got "trapped" inside the unclosed definition. How to fix it: every {% macro %} needs exactly one {% endmacro %} — write both at the same time, the way you open and close a parenthesis, before filling in the body in between.

Invoking a macro with {% %} instead of {{ }}. What happens: someone, used to {% if %} and {% macro %} using percent braces, tries to invoke a macro with the same syntax: {% discounted_price('4.50') %} instead of {{ discounted_price('4.50') }}. Why it happens: both kinds of braces show up mixed together in the same file, and it's easy to lose track of which one declares structure and which one produces a value. How to spot it: the error is specific and points at the exact line:

Encountered unknown tag 'discounted_price'.
  line 1
    select {% discounted_price('5') %} as x

Jinja interprets {% discounted_price(...) %} as if you were trying to open a statement called discounted_price — something that doesn't exist — not as an invocation. How to fix it: remember the rule from the "Two Jinja delimiters" section: {% %} declares structure (produces no text), {{ }} produces a value — invoking a macro always produces a value, so it always goes inside {{ }}.

Passing a column name with no quotes, expecting it to work the same as a literal. What happens: someone writes {{ discounted_price(unit_price, 0.10) }} — with no quotes around unit_price — expecting the macro to understand it refers to a column. Why it happens: inside already-compiled SQL, unit_price with no quotes is exactly what a real column looks like, so it seems reasonable to pass it that way. How to spot it: Jinja tries to resolve unit_price as a Jinja variable (not as text), and if no variable with that name exists, it fails with an "undefined" error before even getting to compile any SQL. How to fix it: any argument that should show up as-is in the compiled SQL — whether it's a column name or a literal — gets passed in quotes inside the macro invocation, as in every example in this lesson: discounted_price('unit_price', 0.10), never discounted_price(unit_price, 0.10).

Exercises

Exercise 1 — Rebuild discounted_price from memory. Without looking at this lesson's worked example, write the macros/discounted_price.sql file again, with both parameters (price_col, pct=0.10) and whitespace control. Verify with dbt compile --inline that it produces the same SQL as the original example.

-- macros/discounted_price.sql
{% macro discounted_price(price_col, pct=0.10) -%}
    ({{ price_col }} * (1 - {{ pct }}))
{%- endmacro %}

Exercise 2 — Add a third, optional parameter. Extend discounted_price with a third parameter, round_to=2, that rounds the result to that many decimal places using SQL's round() function. Verify that discounted_price('4.567', 0.10) (without specifying round_to) rounds to 2 decimal places by default.

{% macro discounted_price(price_col, pct=0.10, round_to=2) -%}
    round({{ price_col }} * (1 - {{ pct }}), {{ round_to }})
{%- endmacro %}

dbt compile --inline "select {{ discounted_price('4.567', 0.10) }} as x"

Exercise 3 — Argue why a macro can never fail on its own in the warehouse. In 2-3 sentences, using what you saw about this lesson's compiled SQL, explain why a SQL syntax error (like a column that doesn't exist) can never be "the macro's fault" in a strict sense, even if the error showed up right after invoking it.

Summary and next step

In this lesson you wrote discounted_price, a practice macro with two parameters — one required, one with a default value — and confirmed, with dbt compile --inline and dbt show --inline, that it produces different SQL depending on what arguments it receives, with no trace of Jinja left in the final SQL. You saw the distinction between {% %} (statements, no output) and {{ }} (expressions, with output), whitespace control with dashes, and three concrete errors — missing endmacro, invocation with the wrong delimiter, unquoted argument — with the exact message each one produces.

Before moving on you should be able to: write, from memory, the minimal {% macro name(args) %} ... {% endmacro %} structure; and explain, in your own words, why a macro never shows up in the SQL the warehouse executes.

Lesson 3 uses this exact same syntax to write the module's real macro: calculate_revenue, which replaces the o.quantity * o.unit_price expression fact_orders.sql has repeated since module 3, verified against the same exact result as always — 40 rows, 106.15 total revenue.

Resources

• dbt Developer Hub — "Jinja and macros," the official syntax reference for {% macro %}/{% endmacro %}, arguments, and default values, with the canonical cents_to_dollars example that inspires discounted_price. docs.getdbt.com/docs/build/jinja-macros. In English.
• Jinja (Pallets Projects) — "Template Designer Documentation," the section on whitespace control with the - signs on tags. jinja.palletsprojects.com/en/stable/templates/#whitespace-control. In English.
• dbt Developer Hub — "dbt compile" and "dbt show," the two command references already cited since module 2, applied here to macros instead of source(). docs.getdbt.com/reference/commands/compile · docs.getdbt.com/reference/commands/show. In English.