A Variable and a Little Jinja in Python

The {{ }} and {% %} in a dbt model are Jinja, a template language. Before any SQL runs, dbt reads the file and fills in the template: {{ … }} writes a value into the text, and {% … %} runs an if or a for. The warehouse only sees the plain SQL that comes out. ref, source and config were Jinja all along.

A variable is a setting of the project, written once under vars: in dbt_project.yml and read in any model with {{ var('name') }}. You can change it for one run without touching a file: dbt build --vars '{start_date: 2026-05-06}'. A for loop writes the lines between {% for … %} and {% endfor %} once for each item of a list. Data teams use both to keep a setting in one place and to stop copying near-identical columns by hand. For example, one column per product:

SELECT
    {% for p in ['Mug', 'Pen'] %}
    SUM(quantity) FILTER (WHERE product = '{{ p }}') AS {{ p }}_sold,
    {% endfor %}
    COUNT(*) AS orders
FROM {{ ref('stg_orders') }}

dbt writes the middle line twice, once for Mug and once for Pen. Note the quotes: {{ p }} writes only the bare text Mug, so a string or a date needs quotes around the braces.

You edit models/marts/daily_orders.sql, a table of orders per day. The project's settings are given in dbt_project.yml:

name: shop
version: "1.0"
profile: shop

vars:
  start_date: '2026-05-03'

The model reads this given staging model, models/staging/stg_orders.sql:

SELECT
    order_id,
    customer_id,
    product,
    TRY_CAST(quantity AS INTEGER) AS quantity,
    CAST(unit_price AS INTEGER) AS unit_price,
    status,
    CAST(ordered_at AS TIMESTAMP) AS ordered_at
FROM {{ source('shop', 'raw_orders') }}
QUALIFY ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY filename DESC) = 1

The check builds the project, shows the model and the SQL dbt compiled from it, then builds again with start_date set to 2026-05-06. Your code must print:

model stg_orders (view): OK
model daily_orders (table): OK
daily_orders (table), 5 rows:
day | paid_orders | cancelled_orders | orders
2026-05-03 | 9 | 2 | 11
2026-05-04 | 6 | 2 | 8
2026-05-05 | 12 | 0 | 12
2026-05-06 | 7 | 3 | 10
2026-05-07 | 9 | 2 | 11
daily_orders, compiled:
  …  (the SQL dbt made from your file, the loop and the variable filled in)
dbt build --vars '{start_date: 2026-05-06}':
model stg_orders (view): OK
model daily_orders (table): OK
daily_orders (table), 2 rows:
day | paid_orders | cancelled_orders | orders
2026-05-06 | 7 | 3 | 10
2026-05-07 | 9 | 2 | 11

At work you type that same dbt build --vars … in a terminal, and the warehouse is Snowflake or BigQuery instead of the DuckDB file.

Your task: keep the orders placed on or after the variable start_date, and add one count column per status, paid_orders and cancelled_orders, written by a for loop over ['paid', 'cancelled'].

Assignment
Follow the Coding Tutorial to filter daily_orders by the start_date variable and write its status columns with a Jinja loop.

Hint
Put quotes around {{ var('start_date') }}, as around any date in SQL. Inside the loop, {{ status }} goes both in the string you compare with and in the column's name; end the line with a comma, since COUNT(*) AS orders comes after it.