dbt Interview Questions and Answers Part 6 - Jinja, Macros and Packages

dbt Interview Q&A · Part 6 of 10

In this post we are going to cover Jinja, macros and packages in dbt, through 7 real dbt interview questions for data engineers, asked the way they come up on a real project. The last one is a scenario: five teams copy the same macros between projects, they drift apart, and you have to share and govern them.

Jinja is the templating language inside every dbt model. A macro is a reusable piece of Jinja that dbt turns into SQL before anything runs, and a package is a set of macros, tests and models that other people wrote and you install. Interviewers ask about this because it is where clean projects stay clean, or turn into code nobody can review.

Part 6 of the Real Time dbt Interview Questions and Answers series on TechBrothersIT.

On this page

1. The short answer
2. Let's create the project and some sample data
3. Question 1: The same CASE logic is copied into ten models. How do you turn it into a macro?
4. Question 2: In dev, dbt builds models in a schema with your name in front, and a teammate is confused. How does generate_schema_name work?
5. Question 3: The model needs to filter on a value that differs per run. How do you use var and env_var?
6. Question 4: A teammate wants to install dbt_utils. How do packages, dbt deps and the package lock work?
7. Question 5: You need to grant select on every new table to the BI role. How do you use hooks or the grants config?
8. Question 6: A macro has gotten hard to read and is full of Jinja loops. When is Jinja too much?
9. Scenario 7: Five teams copy the same macros between projects and they drift apart. How do you share and govern them?
10. The full script

The short answer

Say this first

A macro is Jinja that dbt renders into SQL at compile time, so check it with dbt compile. generate_schema_name puts the target schema in front of a custom schema, var and env_var pass values in, and packages are pinned by package-lock.yml. Use the grants config for access, hooks for the rest, and keep Jinja readable.

Let's create the project and some sample data

Our project is a small shop with one seed, raw_orders, with five orders in different statuses and regions. A staging view cleans it up, and a mart table fct_orders groups the statuses. The profile has two targets: dev writes to dbt_ali and prod writes to analytics. Here are all the starting files.

dbt_project.yml

name: shop
version: '1.0'
profile: shop

models:
  shop:
    staging:
      +materialized: view
    marts:
      +materialized: table
      +schema: finance

profiles.yml

shop:
  target: dev
  outputs:
    dev:
      type: duckdb
      path: dev.duckdb
      schema: dbt_ali
      threads: 1
    prod:
      type: duckdb
      path: dev.duckdb
      schema: analytics
      threads: 1

seeds/raw_orders.csv

order_id,customer_id,status,amount,region,order_date
1,101,completed,120.00,us,2026-10-01
2,102,shipped,45.50,eu,2026-10-05
3,101,cancelled,30.00,us,2026-10-12
4,103,returned,80.00,eu,2026-10-20
5,104,placed,60.00,us,2026-10-24

models/staging/stg_orders.sql

select
    order_id,
    customer_id,
    status,
    cast(amount as decimal(10, 2)) as amount,
    region,
    cast(order_date as date) as order_date
from {{ ref('raw_orders') }}

models/marts/fct_orders.sql

select
    order_id,
    customer_id,
    status,
    amount,
    case
        when status in ('completed', 'shipped') then 'fulfilled'
        when status in ('cancelled', 'returned') then 'lost'
        else 'open'
    end as status_group
from {{ ref('stg_orders') }}

Question 1: The same CASE logic is copied into ten models. How do you turn it into a macro?

Move the CASE into a macro that takes the column as an argument, call it from every model, and check the result with dbt compile.

Let's look at the copy first. In fct_orders, a CASE groups the order statuses into fulfilled, lost and open. Ten copies of that means ten places to fix when the business adds a new status.

So we move it into macros/order_status_group.sql. The macro takes the column and returns SQL text. The model now just calls order_status_group('status'). A macro is not a function in the warehouse: dbt renders it at compile time. Let's run dbt compile -s fct_orders to see the SQL that will actually run.

macros/order_status_group.sql

{% macro order_status_group(column) -%}
    case
        when {{ column }} in ('completed', 'shipped') then 'fulfilled'
        when {{ column }} in ('cancelled', 'returned') then 'lost'
        else 'open'
    end
{%- endmacro %}

models/marts/fct_orders.sql

select
    order_id,
    customer_id,
    status,
    amount,
    {{ order_status_group('status') }} as status_group
from {{ ref('stg_orders') }}

Terminal

$ dbt compile -s fct_orders
Compiled node 'fct_orders' is:
select
    order_id,
    customer_id,
    status,
    amount,
    case
        when status in ('completed', 'shipped') then 'fulfilled'
        when status in ('cancelled', 'returned') then 'lost'
        else 'open'
    end as status_group
from "dev"."dbt_ali"."stg_orders"

Finished 'compile' successfully for target 'dev'
Processed: 1 model
Summary: 1 total | 1 success

The compiled SQL has the same CASE as before, with status filled in. Now let's build it in dev with dbt build -s +fct_orders and check the table.

Terminal

$ dbt build -s +fct_orders
 Succeeded seed  dbt_ali.raw_orders (table)
 Succeeded model dbt_ali.stg_orders (view)
 Succeeded model dbt_ali_finance.fct_orders (table)

Finished 'build' successfully for target 'dev'
Processed: 2 models | 1 seed
Summary: 3 total | 3 success

SQL - check the table (DuckDB)

select order_id, status, status_group from dbt_ali_finance.fct_orders order by order_id

Result5 rows

order_id	status	status_group
1	completed	fulfilled
2	shipped	fulfilled
3	cancelled	lost
4	returned	lost
5	placed	open

The result is unchanged: orders 1 and 2 (completed, shipped) are fulfilled, orders 3 and 4 (cancelled, returned) are lost, and order 5 (placed) is open. The dashes in {%- and -%} trim the whitespace around the tags, so the compiled SQL stays tidy.

Real project tip

If all ten models read the same table, compute the column once upstream instead of calling a macro ten times. Either way, compile before and after the change and compare the SQL.

Question 2: In dev, dbt builds models in a schema with your name in front, and a teammate is confused. How does generate_schema_name work?

dbt does not use a custom schema on its own. The default generate_schema_name macro builds the schema as the target schema, an underscore, then the custom schema.

Let's look at our setup. Every target has a schema: dev writes to dbt_ali and prod writes to analytics. The marts folder sets a custom schema, finance. In the dev build above, fct_orders landed in dbt_ali_finance. Now let's run the same build for prod with dbt build -s +fct_orders --target prod.

Terminal

$ dbt build -s +fct_orders --target prod
 Succeeded seed  analytics.raw_orders (table)
 Succeeded model analytics.stg_orders (view)
 Succeeded model analytics_finance.fct_orders (table)

Finished 'build' successfully for target 'prod'
Processed: 2 models | 1 seed
Summary: 3 total | 3 success

In prod the fact table lands in analytics_finance. That is on purpose: each developer's schemas stay behind their own prefix, so a dev run never writes into prod's schemas.

If prod must use the bare name, override the macro. We add our own generate_schema_name: in the prod target it returns the custom schema as it is, and everywhere else it calls the default. Let's build for prod again, then for dev.

macros/generate_schema_name.sql

{% macro generate_schema_name(custom_schema_name, node) -%}
  {%- if target.name == 'prod' and custom_schema_name is not none -%}
    {{ custom_schema_name | trim }}
  {%- else -%}
    {{ default__generate_schema_name(custom_schema_name, node) }}
  {%- endif -%}
{%- endmacro %}

Terminal

$ dbt build -s +fct_orders --target prod
 Succeeded seed  analytics.raw_orders (table)
 Succeeded model analytics.stg_orders (view)
 Succeeded model finance.fct_orders (table)

Finished 'build' successfully for target 'prod'
Processed: 2 models | 1 seed
Summary: 3 total | 3 success
$ dbt build -s +fct_orders
 Succeeded seed  dbt_ali.raw_orders (table)
 Succeeded model dbt_ali.stg_orders (view)
 Succeeded model dbt_ali_finance.fct_orders (table)

Finished 'build' successfully for target 'dev'
Processed: 2 models | 1 seed
Summary: 3 total | 3 success

Now prod builds finance.fct_orders, and dev still builds dbt_ali_finance.fct_orders.

dbt also ships generate_schema_name_for_env: the same in prod, but all of dev goes into your own schema. Both approaches need the prod job's target to be named prod. And v1.12 warns on a null schema.

Pitfall

Never drop the prefix in dev. Developers overwrite each other's tables, and even production's.

Question 3: The model needs to filter on a value that differs per run. How do you use var and env_var?

Use var for a value you pass to a run, and env_var for a value set per machine or per job. Give both a default.

Let's create fct_recent_orders. It filters on var("start_date", "2026-10-01"), so it still works when nobody passes a date, and on env_var("DBT_REGION", "us"). First we run dbt show -s fct_recent_orders with no vars, then again with --vars and a start date of 2026-10-15.

models/marts/fct_recent_orders.sql

select
    order_id,
    region,
    order_date,
    amount
from {{ ref('stg_orders') }}
where order_date >= '{{ var("start_date", "2026-10-01") }}'
  and region = '{{ env_var("DBT_REGION", "us") }}'

Terminal

$ dbt show -s fct_recent_orders
Query show_model_shop_fct_recent_orders
┌──────────┬────────┬────────────┬────────┐
│ order_id ┆ region ┆ order_date ┆ amount │
╞══════════╪════════╪════════════╪════════╡
│ 1        ┆ us     ┆ 2026-10-01 ┆ 120.   │
│ 3        ┆ us     ┆ 2026-10-12 ┆ 30.    │
│ 5        ┆ us     ┆ 2026-10-24 ┆ 60.    │
└──────────┴────────┴────────────┴────────┘
3 rows.
 Succeeded model dbt_ali_finance.fct_recent_orders (table)

Finished 'show' successfully for target 'dev'
$ dbt show -s fct_recent_orders --vars "{start_date: '2026-10-15'}"
Query show_model_shop_fct_recent_orders
┌──────────┬────────┬────────────┬────────┐
│ order_id ┆ region ┆ order_date ┆ amount │
╞══════════╪════════╪════════════╪════════╡
│ 5        ┆ us     ┆ 2026-10-24 ┆ 60.    │
└──────────┴────────┴────────────┴────────┘
1 rows.
 Succeeded model dbt_ali_finance.fct_recent_orders (table)

Finished 'show' successfully for target 'dev'

The first run returns the three US orders since October 1: orders 1, 3 and 5. With the start date of October 15, only order 5 is left.

Now the region. We add a .env file that sets DBT_REGION=eu and run the same show command.

.env

DBT_REGION=eu

Terminal

$ dbt show -s fct_recent_orders
Query show_model_shop_fct_recent_orders
┌──────────┬────────┬────────────┬────────┐
│ order_id ┆ region ┆ order_date ┆ amount │
╞══════════╪════════╪════════════╪════════╡
│ 2        ┆ eu     ┆ 2026-10-05 ┆ 45.5   │
│ 4        ┆ eu     ┆ 2026-10-20 ┆ 80.    │
└──────────┴────────┴────────────┴────────┘
2 rows.
 Succeeded model dbt_ali_finance.fct_recent_orders (table)

Finished 'show' successfully for target 'dev'

The model returns the two EU orders, 2 and 4. dbt v2 and v1.12 both load that file from the working folder.

When the same var is set in more than one place, this is the order that wins:

• --vars on the command line
• then vars.yml, new in v1.12
• then dbt_project.yml
• then the default in var()

Pitfall

Never put secrets in Git. Use env_var with the DBT_ENV_SECRET prefix, and only in profiles.yml or packages.yml. Give every other env var a default, or set it in every job.

Question 4: A teammate wants to install dbt_utils. How do packages, dbt deps and the package lock work?

List the package in packages.yml, run dbt deps, and commit the package-lock.yml it writes, so everyone installs the same code.

First, the file. packages.yml sits next to dbt_project.yml. dependencies.yml also works and can hold Mesh projects, but dbt v1 doesn't render Jinja in it. From the dbt Hub, it is the package name and a version range:

packages.yml or dependencies.yml (dbt Hub)

packages:
  - package: dbt-labs/dbt_utils
    version: [">=1.4.0", "<2.0.0"]

A package can also come from Git, with a revision: a tag, a branch or a commit. Only the Hub resolves duplicate dependencies between packages. The Hub isn't reachable from our demo machine, so here we use Git, at tag 1.4.1, with the lock file from the first dbt deps. Let's run dbt deps.

packages.yml

packages:
  - git: https://github.com/dbt-labs/dbt-utils.git
    revision: 1.4.1

package-lock.yml

packages:
- git: https://github.com/dbt-labs/dbt-utils.git
  name: dbt_utils
  revision: 49b932cb1f6fd0f7dfbf9048512db6415f570b20
sha1_hash: 7ad934758dfab0f5eb473219a8a484e73eb53fac

Delete the file .env.

Terminal

$ dbt deps
   Loading packages.yml
Installing packages
Installing https://github.com/dbt-labs/dbt-utils.git
 Installed dbt_utils from 49b932cb1f6fd0f7dfbf9048512db6415f570b20
 Installed 1 package

Finished 'deps' successfully

dbt installs dbt_utils from commit 49b932cb1f6fd0f7dfbf9048512db6415f570b20, the exact commit in the lock. To upgrade, change packages.yml or run dbt deps --upgrade.

Then we call the package's macros and tests with its name in front. Let's add the dbt_utils.accepted_range test with min_value: 0 on amount and run dbt build -s stg_orders.

models/staging/stg_orders.yml

models:
  - name: stg_orders
    columns:
      - name: amount
        data_tests:
          - dbt_utils.accepted_range:
              arguments:
                min_value: 0

Terminal

$ dbt build -s stg_orders
   Loading packages.yml
 Succeeded model dbt_ali.stg_orders (view)
    Passed test  dbt_utils_accepted_range_stg_orders_amount__0

Finished 'build' successfully for target 'dev'
Processed: 1 model | 1 test
Summary: 2 total | 2 success

The test dbt_utils_accepted_range_stg_orders_amount__0 passes, so no amount is negative. Two more things: dbt v2 installs missing packages on its own when a command runs, and popular dbt Labs packages like dbt_utils already work on v2.

Real project tip

Commit package-lock.yml, not the dbt_packages/ folder.

Question 5: You need to grant select on every new table to the BI role. How do you use hooks or the grants config?

Use the grants config where your warehouse supports it, and hooks for everything else.

The grants config says select for the BI role, on a folder or a model. When a model, seed or snapshot finishes building, dbt makes its grants match the config exactly. Let's add it to fct_orders and run dbt run -s fct_orders.

models/marts/_marts.yml

models:
  - name: fct_orders
    config:
      grants:
        select: ['bi_reader']

Terminal (expected error)

$ dbt run -s fct_orders
   Loading packages.yml
    Failed model dbt_ali_finance.fct_orders (table)

=================== Errors and Warnings ====================
[error] [JinjaError (dbt1501)]: Error executing materialization macro
  'dbt_duckdb.materialization_table_duckdb' for model model.shop.fct_orders: Failed to eval the
    compiled Jinja expression unknown method: map has no method named warn_once
(in run/shop/models/marts/fct_orders.sql)
(in dbt_internal_packages/dbt-duckdb/macros/materializations/table.sql:60:9)
(in dbt_internal_packages/dbt-adapters/macros/adapters/apply_grants.sql:156:31)
(in dbt_internal_packages/dbt-duckdb/macros/adapters.sql:321:7)
  --> run/shop/models/marts/fct_orders.sql

Finished 'run' with 1 error for target 'dev'
Processed: 1 model
Summary: 1 total | 1 error

DuckDB has no grant statement. dbt v2 stops with an error from the adapter's grants macro: map has no method named warn_once. dbt v1 only warns that DuckDB doesn't support grants, and builds anyway.

So here we use a hook: SQL that dbt runs before or after a model. Since DuckDB can't grant, our hook logs a row instead. First we remove the grants file and create that log table with dbt run-operation create_grant_log.

macros/create_grant_log.sql

{% macro create_grant_log() %}
  {% do run_query("create table if not exists main.grant_log
      (relation varchar, grantee varchar)") %}
{% endmacro %}

Delete the file models/marts/_marts.yml.

Terminal

$ dbt run-operation create_grant_log
   Loading packages.yml

Finished 'run-operation' successfully for target 'dev'

Now we add a post-hook on every mart model, using this for the table it just built, and run dbt build -s marts.

dbt_project.yml

name: shop
version: '1.0'
profile: shop

models:
  shop:
    staging:
      +materialized: view
    marts:
      +materialized: table
      +schema: finance
      # DuckDB has no GRANT: this hook logs what it would grant
      +post-hook:
        - "insert into main.grant_log
           values ('{{ this }}', 'bi_reader')"

Terminal

$ dbt build -s marts
   Loading packages.yml
 Succeeded model dbt_ali_finance.fct_orders (table)
 Succeeded model dbt_ali_finance.fct_recent_orders (table)

Finished 'build' successfully for target 'dev'
Processed: 2 models
Summary: 2 total | 2 success

SQL - check the table (DuckDB)

select relation, grantee from main.grant_log order by relation

Result2 rows

relation	grantee
"dev"."dbt_ali_finance"."fct_orders"	bi_reader
"dev"."dbt_ali_finance"."fct_recent_orders"	bi_reader

The log has both finance tables, fct_orders and fct_recent_orders, each with bi_reader. On Snowflake, the hook would be a real grant, but the grants config is still the right tool:

dbt_project.yml (Snowflake)

# Snowflake: a hook would work...
+post-hook: "grant select on {{ this }} to role bi_reader"
# ...but the grants config is the right tool
+grants:
  select: ['bi_reader']

The grants config is declarative: remove a role from the config, and dbt revokes it. Keep hooks for the rest, like on-run-end to grant usage on a schema.

Tip

A + in front of select adds to the inherited roles instead of replacing them.

Question 6: A macro has gotten hard to read and is full of Jinja loops. When is Jinja too much?

If a reviewer has to compile a macro to understand it, it's too much.

Let's look at order_metrics. It loops over statuses, then over measures, with an if inside, to build count and sum columns. It saves typing, and it is hard to review. We call it from customer_order_metrics and run dbt compile -s customer_order_metrics.

macros/order_metrics.sql

{% macro order_metrics(statuses, measures, with_total=true) %}
  {%- for s in statuses %}
    {%- for m in measures %}
      {%- if m == 'count' %}
    count(case when status = '{{ s }}' then 1 end)
      as {{ s }}_orders
      {%- else %}
    sum(case when status = '{{ s }}' then {{ m }} end)
      as {{ s }}_{{ m }}
      {%- endif %}
      {%- if not loop.last %},{% endif %}
    {%- endfor %}
  {%- endfor %}
  {%- if with_total %},
    sum(amount) as total_amount
  {%- endif %}
{% endmacro %}

models/marts/customer_order_metrics.sql

select
    customer_id,
    {{ order_metrics(['shipped', 'returned'], ['count', 'amount']) }}
from {{ ref('stg_orders') }}
group by customer_id

Terminal

$ dbt compile -s customer_order_metrics
   Loading packages.yml
Compiled node 'customer_order_metrics' is:
select
    customer_id,

    count(case when status = 'shipped' then 1 end)
      as shipped_orders,
    sum(case when status = 'shipped' then amount end)
      as shipped_amount
    count(case when status = 'returned' then 1 end)
      as returned_orders,
    sum(case when status = 'returned' then amount end)
      as returned_amount,
    sum(amount) as total_amount

from "dev"."dbt_ali"."stg_orders"
group by customer_id

Finished 'compile' successfully for target 'dev'
Processed: 1 model
Summary: 1 total | 1 success

The compiled SQL shows the bug: there is no comma after shipped_amount. loop.last belongs to the inner loop, so the comma between the two statuses is missing, and the warehouse would reject this query. Now the plain SQL version, six readable columns, with dbt build -s customer_order_metrics.

models/marts/customer_order_metrics.sql

select
    customer_id,
    count(*) filter (where status = 'shipped') as shipped_orders,
    sum(amount) filter (where status = 'shipped') as shipped_amount,
    count(*) filter (where status = 'returned') as returned_orders,
    sum(amount) filter (where status = 'returned') as returned_amount,
    sum(amount) as total_amount
from {{ ref('stg_orders') }}
group by customer_id

Terminal

$ dbt build -s customer_order_metrics
   Loading packages.yml
 Succeeded model dbt_ali_finance.customer_order_metrics (table)

Finished 'build' successfully for target 'dev'
Processed: 1 model
Summary: 1 total | 1 success

It builds. My rule is to use Jinja for:

• Same logic everywhere: one macro, like the status group
• Environments: target, var, env_var
• Adapters: adapter.dispatch, with a snowflake__ or default__ version

And not for:

• Business logic in loops
• Macros that build whole queries
• run_query without if execute

Keep macros small, with clear arguments, and document them in a properties file. Guard run_query with if execute: at parse time, dbt v1 gets no result.

Code review rule

Read the compiled SQL in every review.

Scenario 7: Five teams copy the same macros between projects and they drift apart. How do you share and govern them?

Copies drift because nobody owns them. Move the shared macros into one internal dbt package, with an owner, versions and CI, and have every project install it instead of a copy.

Here is the plan, step by step:

• One package, one owner: its own repo, and code owners review every change
• Semantic version tags: a changelog, and teams pin a tag, never main
• CI in the package repo: integration tests on every pull request, per warehouse and dbt version
• Breaking change: a major version, with a deprecation warning one release earlier

Each team pins a release tag in its own packages.yml and upgrades on its own schedule. For example, Finance and Marketing can pin v1.4.0 while Sales stays on v1.3.0.

packages.yml (each team)

packages:
  - git: https://github.com/acme/acme-dbt-macros.git
    revision: v1.4.0   # a release tag, never main

Native private packages, for GitHub, GitLab and Azure DevOps, also work locally in dbt v2 and v1.12.

For our demo, the package is a local folder, shared/acme_macros. Its dbt_project.yml has a name, a version and the dbt versions it supports. The model now calls acme_macros.order_status_group('status'). Let's run dbt deps and then dbt compile -s fct_orders.

shared/acme_macros/dbt_project.yml

name: acme_macros
version: '1.4.0'
require-dbt-version: ['>=1.10.0', '<3.0.0']

shared/acme_macros/macros/order_status_group.sql

{% macro order_status_group(column) -%}
    case
        when {{ column }} in ('completed', 'shipped') then 'fulfilled'
        when {{ column }} in ('cancelled', 'returned') then 'lost'
        else 'open'
    end
{%- endmacro %}

packages.yml

packages:
  - local: shared/acme_macros

package-lock.yml

packages:
- local: shared/acme_macros
  name: acme_macros
sha1_hash: 3e8f863ff5d26d3d2f1e1b66f5472956e87dbc1f

models/marts/fct_orders.sql

select
    order_id,
    customer_id,
    status,
    amount,
    {{ acme_macros.order_status_group('status') }} as status_group
from {{ ref('stg_orders') }}

Delete the file macros/order_status_group.sql.

Delete the file models/staging/stg_orders.yml.

Terminal

$ dbt deps
   Loading packages.yml
Installing packages
Installing acme_macros
 Installed acme_macros
 Installed 1 package

Finished 'deps' successfully
$ dbt compile -s fct_orders
   Loading packages.yml
Compiled node 'fct_orders' is:
select
    order_id,
    customer_id,
    status,
    amount,
    case
        when status in ('completed', 'shipped') then 'fulfilled'
        when status in ('cancelled', 'returned') then 'lost'
        else 'open'
    end as status_group
from "dev"."dbt_ali"."stg_orders"

Finished 'compile' successfully for target 'dev'
Processed: 1 model
Summary: 1 total | 1 success

dbt installs acme_macros, and the compiled SQL shows the same CASE as before. So five teams share one tested copy.

Interview tip

Say "one package, one owner, pinned tags, CI on every pull request". That covers both sharing and governance.

Key points to remember

• A macro renders SQL at compile time: check it with dbt compile
• generate_schema_name: target schema + custom schema; override for prod only
• var per run, env_var per environment, with defaults (never for secrets)
• Pin packages and commit package-lock.yml
• grants config where supported, hooks for the rest; keep Jinja readable

The full script

This Python script writes the whole project and runs every dbt command in order; run pip install dbt first.

Show the full script that builds this project and runs every step

Full script - pip install dbt, then run it with Python in an empty folder

# ========================================================================
# dbt Interview Q&A #6 - Jinja, macros and packages
# TechBrothersIT. Sample data is invented for teaching.
# Every step below was run with dbt v2 (dbt OSS 2.0.8) on DuckDB; the outputs in the video and the
# blog post are the real outputs. To run it yourself, in an empty folder:
#   pip install dbt     (dbt v2; adapters are bundled, and DuckDB needs no account or server)
#   python dbt_Interview_QA_06_Code.py
# ========================================================================

import os, subprocess

def write(path, text):
    os.makedirs(os.path.dirname(path) or ".", exist_ok=True)
    with open(path, "w", encoding="utf-8") as f:
        f.write(text.strip("\n") + "\n")

def remove(path):
    if os.path.exists(path):
        os.remove(path)

def dbt(cmd, expect_error=False):
    print("\n$ " + cmd, flush=True)
    rc = subprocess.run(cmd, shell=True).returncode
    if rc != 0 and not expect_error:
        raise SystemExit("stopped: this command was expected to succeed")

# Step 0: the project and its sample data
write('dbt_project.yml', """\
name: shop
version: '1.0'
profile: shop

models:
  shop:
    staging:
      +materialized: view
    marts:
      +materialized: table
      +schema: finance
""")
write('profiles.yml', """\
shop:
  target: dev
  outputs:
    dev:
      type: duckdb
      path: dev.duckdb
      schema: dbt_ali
      threads: 1
    prod:
      type: duckdb
      path: dev.duckdb
      schema: analytics
      threads: 1
""")
write('seeds/raw_orders.csv', """\
order_id,customer_id,status,amount,region,order_date
1,101,completed,120.00,us,2026-10-01
2,102,shipped,45.50,eu,2026-10-05
3,101,cancelled,30.00,us,2026-10-12
4,103,returned,80.00,eu,2026-10-20
5,104,placed,60.00,us,2026-10-24
""")
write('models/staging/stg_orders.sql', """\
select
    order_id,
    customer_id,
    status,
    cast(amount as decimal(10, 2)) as amount,
    region,
    cast(order_date as date) as order_date
from {{ ref('raw_orders') }}
""")
write('models/marts/fct_orders.sql', """\
select
    order_id,
    customer_id,
    status,
    amount,
    case
        when status in ('completed', 'shipped') then 'fulfilled'
        when status in ('cancelled', 'returned') then 'lost'
        else 'open'
    end as status_group
from {{ ref('stg_orders') }}
""")

# Step 1: The CASE becomes a macro; compile shows the SQL
write('macros/order_status_group.sql', """\
{% macro order_status_group(column) -%}
    case
        when {{ column }} in ('completed', 'shipped') then 'fulfilled'
        when {{ column }} in ('cancelled', 'returned') then 'lost'
        else 'open'
    end
{%- endmacro %}
""")
write('models/marts/fct_orders.sql', """\
select
    order_id,
    customer_id,
    status,
    amount,
    {{ order_status_group('status') }} as status_group
from {{ ref('stg_orders') }}
""")
dbt('dbt compile -s fct_orders')

# Step 2: Build it in dev
dbt('dbt build -s +fct_orders')

# Step 3: The same build in prod: default generate_schema_name
dbt('dbt build -s +fct_orders --target prod')

# Step 4: Override generate_schema_name: prod uses the bare custom schema
write('macros/generate_schema_name.sql', """\
{% macro generate_schema_name(custom_schema_name, node) -%}
  {%- if target.name == 'prod' and custom_schema_name is not none -%}
    {{ custom_schema_name | trim }}
  {%- else -%}
    {{ default__generate_schema_name(custom_schema_name, node) }}
  {%- endif -%}
{%- endmacro %}
""")
dbt('dbt build -s +fct_orders --target prod')
dbt('dbt build -s +fct_orders')

# Step 5: var with a default, then --vars
write('models/marts/fct_recent_orders.sql', """\
select
    order_id,
    region,
    order_date,
    amount
from {{ ref('stg_orders') }}
where order_date >= '{{ var("start_date", "2026-10-01") }}'
  and region = '{{ env_var("DBT_REGION", "us") }}'
""")
dbt('dbt show -s fct_recent_orders')
dbt('dbt show -s fct_recent_orders --vars "{start_date: \'2026-10-15\'}"')

# Step 6: env_var: a .env file sets the region
write('.env', """\
DBT_REGION=eu
""")
dbt('dbt show -s fct_recent_orders')

# Step 7: A git package: dbt_utils at tag 1.4.1, with its committed lock file
write('packages.yml', """\
packages:
  - git: https://github.com/dbt-labs/dbt-utils.git
    revision: 1.4.1
""")
write('package-lock.yml', """\
packages:
- git: https://github.com/dbt-labs/dbt-utils.git
  name: dbt_utils
  revision: 49b932cb1f6fd0f7dfbf9048512db6415f570b20
sha1_hash: 7ad934758dfab0f5eb473219a8a484e73eb53fac
""")
remove('.env')
dbt('dbt deps')

# Step 8: Use a test from the package
write('models/staging/stg_orders.yml', """\
models:
  - name: stg_orders
    columns:
      - name: amount
        data_tests:
          - dbt_utils.accepted_range:
              arguments:
                min_value: 0
""")
dbt('dbt build -s stg_orders')

# Step 9: The grants config on DuckDB
write('models/marts/_marts.yml', """\
models:
  - name: fct_orders
    config:
      grants:
        select: ['bi_reader']
""")
dbt('dbt run -s fct_orders', expect_error=True)   # EXPECTED TO FAIL: the video shows this error

# Step 10: Prepare a log table for the hook
write('macros/create_grant_log.sql', """\
{% macro create_grant_log() %}
  {% do run_query("create table if not exists main.grant_log
      (relation varchar, grantee varchar)") %}
{% endmacro %}
""")
remove('models/marts/_marts.yml')
dbt('dbt run-operation create_grant_log')

# Step 11: A post-hook on every mart model
write('dbt_project.yml', """\
name: shop
version: '1.0'
profile: shop

models:
  shop:
    staging:
      +materialized: view
    marts:
      +materialized: table
      +schema: finance
      # DuckDB has no GRANT: this hook logs what it would grant
      +post-hook:
        - "insert into main.grant_log
           values ('{{ this }}', 'bi_reader')"
""")
dbt('dbt build -s marts')

# Step 12: A macro full of loops: compile shows the bug
write('macros/order_metrics.sql', """\
{% macro order_metrics(statuses, measures, with_total=true) %}
  {%- for s in statuses %}
    {%- for m in measures %}
      {%- if m == 'count' %}
    count(case when status = '{{ s }}' then 1 end)
      as {{ s }}_orders
      {%- else %}
    sum(case when status = '{{ s }}' then {{ m }} end)
      as {{ s }}_{{ m }}
      {%- endif %}
      {%- if not loop.last %},{% endif %}
    {%- endfor %}
  {%- endfor %}
  {%- if with_total %},
    sum(amount) as total_amount
  {%- endif %}
{% endmacro %}
""")
write('models/marts/customer_order_metrics.sql', """\
select
    customer_id,
    {{ order_metrics(['shipped', 'returned'], ['count', 'amount']) }}
from {{ ref('stg_orders') }}
group by customer_id
""")
dbt('dbt compile -s customer_order_metrics')

# Step 13: The plain SQL version
write('models/marts/customer_order_metrics.sql', """\
select
    customer_id,
    count(*) filter (where status = 'shipped') as shipped_orders,
    sum(amount) filter (where status = 'shipped') as shipped_amount,
    count(*) filter (where status = 'returned') as returned_orders,
    sum(amount) filter (where status = 'returned') as returned_amount,
    sum(amount) as total_amount
from {{ ref('stg_orders') }}
group by customer_id
""")
dbt('dbt build -s customer_order_metrics')

# Step 14: The shared macro moves into an internal package
write('shared/acme_macros/dbt_project.yml', """\
name: acme_macros
version: '1.4.0'
require-dbt-version: ['>=1.10.0', '<3.0.0']
""")
write('shared/acme_macros/macros/order_status_group.sql', """\
{% macro order_status_group(column) -%}
    case
        when {{ column }} in ('completed', 'shipped') then 'fulfilled'
        when {{ column }} in ('cancelled', 'returned') then 'lost'
        else 'open'
    end
{%- endmacro %}
""")
write('packages.yml', """\
packages:
  - local: shared/acme_macros
""")
write('package-lock.yml', """\
packages:
- local: shared/acme_macros
  name: acme_macros
sha1_hash: 3e8f863ff5d26d3d2f1e1b66f5472956e87dbc1f
""")
write('models/marts/fct_orders.sql', """\
select
    order_id,
    customer_id,
    status,
    amount,
    {{ acme_macros.order_status_group('status') }} as status_group
from {{ ref('stg_orders') }}
""")
remove('macros/order_status_group.sql')
remove('models/staging/stg_orders.yml')
dbt('dbt deps')
dbt('dbt compile -s fct_orders')

Next in this series

Part 7: Sources, Seeds and Configuration

The sample data on this page is invented for teaching. Every terminal output is the real output of the dbt command above it, run with dbt v2 (dbt OSS 2.0.8) on DuckDB; run times and node counters are left out, and node lines are listed in DAG order. Part of the Real Time dbt Interview Questions and Answers series on TechBrothersIT. Features, limits and their status (GA or preview) are current as of October 2026; check the official dbt documentation (docs.getdbt.com) for anything you quote in an interview.