dbt Interview Questions and Answers Part 3 - Incremental Models in Real Projects

dbt Interview Q&A · Part 3 of 10

In this post we are going to cover incremental models in real dbt projects, 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: a billion-row events table that must load daily, with backfills when a day goes wrong, and where microbatch fits.

An incremental model does not rebuild its table on every run. The first run builds it, and every run after that loads only the new or changed rows into the table that is already there. Interviewers love this topic because incremental models are where duplicates, missing late rows and silent drift show up in production.

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

The short answer

Say this first

An incremental model inserts only new or changed rows. Give it a unique_key so reruns replace rows instead of duplicating them, filter with is_incremental() and {{ this }} plus a lookback for late data, choose the strategy by how rows change, set on_schema_change on purpose, and use --full-refresh or microbatch backfills when the table drifts.

Let's create the project and some sample data

Our project starts with one seed of raw orders and a staging view, stg_orders, on top of it. We will add incremental models question by question. Here are the starting files.

dbt_project.yml
name: shop
version: '1.0'
profile: shop

models:
  shop:
    +materialized: view
seeds/raw_orders.csv
order_id,customer_id,amount,status,updated_at
1,10,100.00,placed,2026-10-01 09:00:00
2,11,50.00,placed,2026-10-01 10:00:00
3,12,75.00,placed,2026-10-02 08:00:00
models/stg_orders.sql
select
    order_id,
    customer_id,
    cast(amount as decimal(10, 2)) as amount,
    status,
    cast(updated_at as timestamp) as updated_at
from {{ ref('raw_orders') }}

There are 3 orders to start with, all placed.

Question 1: An incremental model loads duplicate rows after a rerun. What did the model miss, and how does unique_key fix it?

It missed a unique key. Without one, most adapters just append, so every row that passes the filter becomes a new row, even when that order is already in the table.

Let's build two models side by side. Both load only rows newer than the max updated_at in their table. orders_no_key has no key, and orders uses unique_key='order_id' with delete+insert. We run dbt build.

models/orders_no_key.sql
{{ config(materialized='incremental') }}

select order_id, customer_id, amount, status, updated_at
from {{ ref('stg_orders') }}
{% if is_incremental() %}
where updated_at > (select max(updated_at) from {{ this }})
{% endif %}
models/orders.sql
{{ config(
    materialized='incremental',
    unique_key='order_id',
    incremental_strategy='delete+insert'
) }}

select order_id, customer_id, amount, status, updated_at
from {{ ref('stg_orders') }}
{% if is_incremental() %}
where updated_at > (select max(updated_at) from {{ this }})
{% endif %}
Terminal
$ dbt build
 Succeeded seed  main.raw_orders (table)
 Succeeded model main.stg_orders (view)
 Succeeded model main.orders (incremental)
 Succeeded model main.orders_no_key (incremental)

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

Both build. Now order 2 changes from placed to shipped and gets a new updated_at, order 4 arrives, and we add unique and not_null tests on order_id. We run dbt build again.

models/orders.yml
models:
  - name: orders_no_key
    columns:
      - name: order_id
        data_tests:
          - unique
          - not_null
  - name: orders
    columns:
      - name: order_id
        data_tests:
          - unique
          - not_null
seeds/raw_orders.csv
order_id,customer_id,amount,status,updated_at
1,10,100.00,placed,2026-10-01 09:00:00
2,11,50.00,shipped,2026-10-03 09:00:00
3,12,75.00,placed,2026-10-02 08:00:00
4,10,20.00,placed,2026-10-03 10:00:00
Terminal (expected error)
$ dbt build
 Succeeded seed  main.raw_orders (table)
 Succeeded model main.stg_orders (view)
 Succeeded model main.orders (incremental)
 Succeeded model main.orders_no_key (incremental)
    Passed test  not_null_orders_no_key_order_id
    Passed test  not_null_orders_order_id
    Failed test  unique_orders_no_key_order_id (models/orders.yml:6:13)
    Passed test  unique_orders_order_id

====================== Test Failures =======================
Test failed (1 failed row(s)): unique_orders_no_key_order_id

Finished 'build' with 1 error for target 'dev'
Processed: 3 models | 4 tests | 1 seed
Summary: 8 total | 7 success | 1 error
SQL - check the table (DuckDB)
select order_id, status, updated_at from orders_no_key order by order_id, updated_at
Result5 rows
order_idstatusupdated_at
1placed2026-10-01 09:00
2placed2026-10-01 10:00
2shipped2026-10-03 09:00
3placed2026-10-02 08:00
4placed2026-10-03 10:00

The unique test on orders_no_key fails: the shipped version of order 2 was inserted next to the placed one, two rows for one order. With delete+insert, dbt deletes the rows whose key is in the new batch, then inserts the new versions. With merge, it updates them in place. Let's check the keyed model.

No command in this step.

SQL - check the table (DuckDB)
select order_id, status, updated_at from orders order by order_id
Result4 rows
order_idstatusupdated_at
1placed2026-10-01 09:00
2shipped2026-10-03 09:00
3placed2026-10-02 08:00
4placed2026-10-03 10:00

Four orders, one row each, with order 2 shipped.

Real project tip

The key must be truly unique and never null, in the new batch too. So always pair unique_key with unique and not_null tests on that column.

Question 2: A teammate asks what is_incremental and this do in an incremental model. How do you explain it?

is_incremental() is a macro that returns true only when three things hold, and this is the model's own table in the warehouse.

is_incremental() is true only when:

  • materialized is incremental
  • the table already exists
  • the run is not a --full-refresh run

Inside the if block, you use this to ask the table what it already has, usually the latest timestamp. Let's compile the orders model, first normally and then with --full-refresh.

Terminal
$ dbt compile -s orders
Compiled node 'orders' is:

select order_id, customer_id, amount, status, updated_at
from "dev"."main"."stg_orders"

where updated_at > (select max(updated_at) from "dev"."main"."orders")

Finished 'compile' successfully for target 'dev'
Processed: 1 model
Summary: 1 total | 1 success
$ dbt compile -s orders --full-refresh
Compiled node 'orders' is:

select order_id, customer_id, amount, status, updated_at
from "dev"."main"."stg_orders"

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

In the first compile the table exists, so the where clause is there, and this became "dev"."main"."orders". In the second, is_incremental() is false: the filter is gone, and dbt rebuilds the whole table from the select.

Use this only inside the is_incremental() block, because on the first run the table does not exist yet.

Unit test both branches

A unit test can override is_incremental to true or false, with overrides: macros: is_incremental: true.

Question 3: Late-arriving records are missing from the incremental table. How do you handle late data?

A filter on the max updated_at assumes rows arrive in order. When they do not, use a lookback window, and let the unique key make reprocessing safe.

Let's see the problem. Order 5 was updated on October 2 at 15:00, but it reaches the warehouse after order 4, from October 3 at 10:00. We run dbt build -s +orders.

seeds/raw_orders.csv
order_id,customer_id,amount,status,updated_at
1,10,100.00,placed,2026-10-01 09:00:00
2,11,50.00,shipped,2026-10-03 09:00:00
3,12,75.00,placed,2026-10-02 08:00:00
4,10,20.00,placed,2026-10-03 10:00:00
5,13,60.00,placed,2026-10-02 15:00:00
Terminal
$ dbt build -s +orders
 Succeeded seed  main.raw_orders (table)
 Succeeded model main.stg_orders (view)
 Succeeded model main.orders (incremental)
    Passed test  not_null_orders_order_id
    Passed test  unique_orders_order_id

Finished 'build' successfully for target 'dev'
Processed: 2 models | 2 tests | 1 seed
Summary: 5 total | 5 success
SQL - check the table (DuckDB)
select order_id, status, updated_at from orders order by order_id
Result4 rows
order_idstatusupdated_at
1placed2026-10-01 09:00
2shipped2026-10-03 09:00
3placed2026-10-02 08:00
4placed2026-10-03 10:00

The build succeeds, but the table still has only orders 1 to 4. Order 5 is older than the max, so the filter skips it, forever. Now we change the filter to look one day back from the max and run the model.

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

select order_id, customer_id, amount, status, updated_at
from {{ ref('stg_orders') }}
{% if is_incremental() %}
where updated_at > (
    select max(updated_at) - interval 1 day from {{ this }}
)
{% endif %}
Terminal
$ dbt run -s orders
 Succeeded model main.orders (incremental)

Finished 'run' successfully for target 'dev'
Processed: 1 model
Summary: 1 total | 1 success
SQL - check the table (DuckDB)
select order_id, status, updated_at from orders order by order_id
Result5 rows
order_idstatusupdated_at
1placed2026-10-01 09:00
2shipped2026-10-03 09:00
3placed2026-10-02 08:00
4placed2026-10-03 10:00
5placed2026-10-02 15:00

Order 5 is in the table, and there is still one row per order. The unique key is what makes that safe: reprocessed rows replace their old versions instead of duplicating.

Size the lookback from how late your data really arrives. Anything later than the window needs a backfill or a full refresh. Microbatch builds the same idea in, with its lookback config.

Better filter

If the source has a load timestamp, filter on that instead of the business date, because it only moves forward.

Question 4: A new column was added upstream and the incremental model ignored it. How does on_schema_change work?

on_schema_change decides what dbt does when the model's columns no longer match the table. The default is ignore, so the new column is dropped without a word.

The source added a channel column. We add it to the seed, to staging and to the model's select, reload the seed with dbt seed --full-refresh, and run both models.

seeds/raw_orders.csv
order_id,customer_id,amount,status,updated_at,channel
1,10,100.00,placed,2026-10-01 09:00:00,web
2,11,50.00,shipped,2026-10-03 09:00:00,store
3,12,75.00,placed,2026-10-02 08:00:00,web
4,10,20.00,placed,2026-10-03 10:00:00,web
5,13,60.00,placed,2026-10-02 15:00:00,store
models/stg_orders.sql
select
    order_id,
    customer_id,
    cast(amount as decimal(10, 2)) as amount,
    status,
    cast(updated_at as timestamp) as updated_at,
    channel
from {{ ref('raw_orders') }}
models/orders.sql
{{ config(
    materialized='incremental',
    unique_key='order_id',
    incremental_strategy='delete+insert'
) }}

select order_id, customer_id, amount, status, updated_at,
       channel
from {{ ref('stg_orders') }}
{% if is_incremental() %}
where updated_at > (
    select max(updated_at) - interval 1 day from {{ this }}
)
{% endif %}
Terminal
$ dbt seed --full-refresh
 Succeeded seed  main.raw_orders (table)

Finished 'seed' successfully for target 'dev'
Processed: 1 seed
Summary: 1 total | 1 success
$ dbt run -s stg_orders orders
 Succeeded model main.stg_orders (view)
 Succeeded model main.orders (incremental)

Finished 'run' successfully for target 'dev'
Processed: 2 models
Summary: 2 total | 2 success
SQL - check the table (DuckDB)
select * from orders order by order_id
Result5 rows
order_idcustomer_idamountstatusupdated_at
110100.00placed2026-10-01 09:00
21150.00shipped2026-10-03 09:00
31275.00placed2026-10-02 08:00
41020.00placed2026-10-03 10:00
51360.00placed2026-10-02 15:00

The run succeeds, but the table has no channel column. dbt keeps loading the table's existing columns. Now we set on_schema_change='append_new_columns' and run again.

models/orders.sql
{{ config(
    materialized='incremental',
    unique_key='order_id',
    incremental_strategy='delete+insert',
    on_schema_change='append_new_columns'
) }}

select order_id, customer_id, amount, status, updated_at,
       channel
from {{ ref('stg_orders') }}
{% if is_incremental() %}
where updated_at > (
    select max(updated_at) - interval 1 day from {{ this }}
)
{% endif %}
Terminal
$ dbt run -s orders
 Succeeded model main.orders (incremental)

Finished 'run' successfully for target 'dev'
Processed: 1 model
Summary: 1 total | 1 success
SQL - check the table (DuckDB)
select order_id, status, updated_at, channel from orders order by order_id
Result5 rows
order_idstatusupdated_atchannel
1placed2026-10-01 09:00NULL
2shipped2026-10-03 09:00store
3placed2026-10-02 08:00NULL
4placed2026-10-03 10:00web
5placed2026-10-02 15:00store

dbt adds the column, then loads the batch. But look at orders 1 and 3: channel is NULL. New columns are not backfilled. Only orders 2, 4 and 5, inside the lookback window, got a value.

on_schema_changewhat it does
ignoredefault: new column dropped
failstop the run
append_new_columnsadd, no backfill
sync_all_columnsadd and remove

The fail option stops the run when the columns differ, and sync_all_columns also drops columns that were removed. And on_schema_change only tracks top-level columns, not nested fields.

My choice

On important models I like fail, so a schema change is a decision, not a surprise. Then append the column, and backfill the old rows with a full refresh.

Question 5: Your manager asks which incremental strategy to use: append, merge or delete plus insert. How do you decide?

By how the data changes. Append when rows never change, merge when rows update by key, and delete+insert when you replace a whole set.

strategyuse it whenkey
appendrows never change: events, logsnone
mergerows update by key: upserta unique row key
delete+insertreplace a whole set: all lines of an order, a daythe set key

Append is the cheapest and needs no key, but a rerun can duplicate. Let's see merge against delete+insert on order lines. lines_merge merges on line_id, and lines_delete_insert uses delete+insert on order_id. Order 7 has two lines. We build both.

seeds/raw_order_lines.csv
line_id,order_id,sku,qty,updated_at
1,7,A,1,2026-10-05 09:00:00
2,7,B,2,2026-10-05 09:00:00
3,8,A,1,2026-10-05 10:00:00
models/lines_merge.sql
{{ config(
    materialized='incremental',
    incremental_strategy='merge',
    unique_key='line_id'
) }}

select line_id, order_id, sku, qty, updated_at
from {{ ref('raw_order_lines') }}
{% if is_incremental() %}
where updated_at > (select max(updated_at) from {{ this }})
{% endif %}
models/lines_delete_insert.sql
{{ config(
    materialized='incremental',
    incremental_strategy='delete+insert',
    unique_key='order_id'
) }}

select line_id, order_id, sku, qty, updated_at
from {{ ref('raw_order_lines') }}
{% if is_incremental() %}
where updated_at > (select max(updated_at) from {{ this }})
{% endif %}
Terminal
$ dbt build -s +lines_merge +lines_delete_insert
 Succeeded seed  main.raw_order_lines (table)
 Succeeded model main.lines_delete_insert (incremental)
 Succeeded model main.lines_merge (incremental)

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

Now the source edits order 7 down to one line, with qty 3. We rerun both models and compare them.

seeds/raw_order_lines.csv
line_id,order_id,sku,qty,updated_at
1,7,A,3,2026-10-06 09:00:00
3,8,A,1,2026-10-05 10:00:00
Terminal
$ dbt build -s +lines_merge +lines_delete_insert
 Succeeded seed  main.raw_order_lines (table)
 Succeeded model main.lines_delete_insert (incremental)
 Succeeded model main.lines_merge (incremental)

Finished 'build' successfully for target 'dev'
Processed: 2 models | 1 seed
Summary: 3 total | 3 success
SQL - check the table (DuckDB)
select 'merge' as strategy, line_id, order_id, sku, qty from lines_merge union all select 'delete+insert', line_id, order_id, sku, qty from lines_delete_insert order by 1 desc, 2
Result5 rows
strategyline_idorder_idskuqty
merge17A3
merge27B2
merge38A1
delete+insert17A3
delete+insert38A1

Merge updates line 1 to qty 3, but line 2 is never deleted, so it stays. Delete+insert deletes every row of order 7, then inserts the order's rows from the batch: one line. The catch is that the batch must hold every line of a changed order, or lines are lost.

The insert_overwrite strategy depends on the platform: it replaces whole partitions on BigQuery, Spark and Databricks, but the whole table on Snowflake. dbt-duckdb has append, merge, delete+insert and microbatch.

Big tables

On very large tables, incremental_predicates limit how much of the target a merge scans. For big time series, there is microbatch.

Question 6: An incremental model has drifted from the source. When and how do you run a full refresh?

An incremental table only sees what its filter lets through, so it can drift. When a drift check fails, rebuild it with --full-refresh.

Drift comes from three places:

  • A logic change: old rows keep the old logic
  • A source fix without a new timestamp: the filter never sees it
  • A new column: never backfilled

Our orders table has the last one. Let's check for drift by comparing the table to its source with an except query.

No command in this step.

SQL - check the table (DuckDB)
select order_id, channel from stg_orders except select order_id, channel from orders order by order_id
Result2 rows
order_idchannel
1web
3web

It returns orders 1 and 3, both web in staging. Then we rebuild with dbt run -s orders --full-refresh.

Terminal
$ dbt run -s orders --full-refresh
 Succeeded model main.orders (incremental)

Finished 'run' successfully for target 'dev'
Processed: 1 model
Summary: 1 total | 1 success
SQL - check the table (DuckDB)
select order_id, status, channel from orders order by order_id
Result5 rows
order_idstatuschannel
1placedweb
2shippedstore
3placedweb
4placedweb
5placedstore

is_incremental() is false, so dbt rebuilds the table from the full select, and every order has its channel now.

Run a full refresh after a logic change, after a schema change you need to backfill, or when a drift check fails. Select the model with a plus, like dbt build -s orders+ --full-refresh, so downstream models rebuild too. The flag applies to every incremental model you select.

Huge tables

A full refresh is slow and risky. Set full_refresh: false on them, so the flag is ignored, and backfill in batches instead.

Scenario 7: A billion-row events table must be loaded daily, with backfills when a day goes wrong. How do you design it, and where does microbatch fit?

I would use microbatch. You write the query for one batch, with no is_incremental() block, and dbt splits the load by event time.

Let's build it. fct_events uses incremental_strategy='microbatch' with event_time='event_at', one batch per day, starting October 1. Event time is also set on the parent, stg_events, so each batch reads only its own day from upstream, not the whole table. We build staging, then run the first load from October 1 up to October 4.

seeds/raw_events.csv
event_id,user_id,event_type,event_at
1,10,view,2026-10-01 08:00:00
2,10,click,2026-10-01 08:05:00
3,11,view,2026-10-02 09:00:00
4,12,view,2026-10-02 11:30:00
5,11,purchase,2026-10-03 14:00:00
6,12,click,2026-10-03 16:45:00
models/stg_events.sql
select
    event_id,
    user_id,
    event_type,
    cast(event_at as timestamp) as event_at
from {{ ref('raw_events') }}
models/events.yml
models:
  - name: stg_events
    config:
      event_time: event_at
models/fct_events.sql
{{ config(
    materialized='incremental',
    incremental_strategy='microbatch',
    event_time='event_at',
    batch_size='day',
    begin='2026-10-01',
    lookback=1,
    full_refresh=false
) }}

select event_id, user_id, event_type, event_at
from {{ ref('stg_events') }}
Terminal
$ dbt build -s +stg_events
 Succeeded seed  main.raw_events (table)
 Succeeded model main.stg_events (view)

Finished 'build' successfully for target 'dev'
Processed: 1 model | 1 seed
Summary: 2 total | 2 success
$ dbt run -s fct_events --event-time-start 2026-10-01 --event-time-end 2026-10-04
 Succeeded model main.fct_events (incremental)

Finished 'run' successfully for target 'dev'
Processed: 1 model
Summary: 1 total | 1 success
SQL - check the table (DuckDB)
select cast(event_at as date) as event_date, count(*) as events from fct_events group by 1 order by 1
Result3 rows
event_dateevents
2026-10-012
2026-10-022
2026-10-032

Three days, 2 events each. Now a day goes wrong: a late purchase lands on October 2, and a new event arrives on October 4. We backfill just that day with --event-time-start 2026-10-02 --event-time-end 2026-10-03.

seeds/raw_events.csv
event_id,user_id,event_type,event_at
1,10,view,2026-10-01 08:00:00
2,10,click,2026-10-01 08:05:00
3,11,view,2026-10-02 09:00:00
4,12,view,2026-10-02 11:30:00
7,13,purchase,2026-10-02 18:20:00
5,11,purchase,2026-10-03 14:00:00
6,12,click,2026-10-03 16:45:00
8,10,view,2026-10-04 07:10:00
Terminal
$ dbt seed -s raw_events
 Succeeded seed  main.raw_events (table)

Finished 'seed' successfully for target 'dev'
Processed: 1 seed
Summary: 1 total | 1 success
$ dbt run -s fct_events --event-time-start 2026-10-02 --event-time-end 2026-10-03
 Succeeded model main.fct_events (incremental)

Finished 'run' successfully for target 'dev'
Processed: 1 model
Summary: 1 total | 1 success
SQL - check the table (DuckDB)
select cast(event_at as date) as event_date, count(*) as events from fct_events group by 1 order by 1
Result3 rows
event_dateevents
2026-10-012
2026-10-023
2026-10-032

Only that batch is replaced: 3 events on October 2, the other days untouched, nothing duplicated. The event on October 4 waits for its own run. The rest of the design:

  • lookback: reprocesses the previous batch on every normal run, for late events
  • full_refresh: false: protects the table
  • dbt retry: reruns only the failed batches
  • Partition or cluster the warehouse table by event date

Key points to remember

  • unique_key + unique tests: reruns replace, never duplicate
  • is_incremental() and {{ this }}: the filter for existing tables
  • Late data: a lookback window, or filter on a load timestamp
  • on_schema_change: ignore is the default, nothing is backfilled
  • Drift: --full-refresh; big tables: microbatch backfills

The full script

Here is a Python script that writes the whole project and runs every dbt command above 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 #3 - Incremental models in real projects
# 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_03_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:
    +materialized: view
""")
write('seeds/raw_orders.csv', """\
order_id,customer_id,amount,status,updated_at
1,10,100.00,placed,2026-10-01 09:00:00
2,11,50.00,placed,2026-10-01 10:00:00
3,12,75.00,placed,2026-10-02 08:00:00
""")
write('models/stg_orders.sql', """\
select
    order_id,
    customer_id,
    cast(amount as decimal(10, 2)) as amount,
    status,
    cast(updated_at as timestamp) as updated_at
from {{ ref('raw_orders') }}
""")
write('profiles.yml', """\
shop:
  target: dev
  outputs:
    dev:
      type: duckdb
      path: dev.duckdb
      threads: 1
""")

# Step 1: Two incremental models: one without a key, one with unique_key
write('models/orders_no_key.sql', """\
{{ config(materialized='incremental') }}

select order_id, customer_id, amount, status, updated_at
from {{ ref('stg_orders') }}
{% if is_incremental() %}
where updated_at > (select max(updated_at) from {{ this }})
{% endif %}
""")
write('models/orders.sql', """\
{{ config(
    materialized='incremental',
    unique_key='order_id',
    incremental_strategy='delete+insert'
) }}

select order_id, customer_id, amount, status, updated_at
from {{ ref('stg_orders') }}
{% if is_incremental() %}
where updated_at > (select max(updated_at) from {{ this }})
{% endif %}
""")
dbt('dbt build')

# Step 2: is_incremental() and this in the compiled SQL, with and without --full-refresh
dbt('dbt compile -s orders')
dbt('dbt compile -s orders --full-refresh')

# Step 3: Order 2 changes, order 4 arrives, and unique tests are added: the model without a key duplicates
write('models/orders.yml', """\
models:
  - name: orders_no_key
    columns:
      - name: order_id
        data_tests:
          - unique
          - not_null
  - name: orders
    columns:
      - name: order_id
        data_tests:
          - unique
          - not_null
""")
write('seeds/raw_orders.csv', """\
order_id,customer_id,amount,status,updated_at
1,10,100.00,placed,2026-10-01 09:00:00
2,11,50.00,shipped,2026-10-03 09:00:00
3,12,75.00,placed,2026-10-02 08:00:00
4,10,20.00,placed,2026-10-03 10:00:00
""")
dbt('dbt build', expect_error=True)   # EXPECTED TO FAIL: the video shows this error

# Step 4: The model with unique_key: one row per order

# Step 5: A late row: order 5 was updated before the max, and arrives after it
write('seeds/raw_orders.csv', """\
order_id,customer_id,amount,status,updated_at
1,10,100.00,placed,2026-10-01 09:00:00
2,11,50.00,shipped,2026-10-03 09:00:00
3,12,75.00,placed,2026-10-02 08:00:00
4,10,20.00,placed,2026-10-03 10:00:00
5,13,60.00,placed,2026-10-02 15:00:00
""")
dbt('dbt build -s +orders')

# Step 6: A one-day lookback window picks up the late row
write('models/orders.sql', """\
{{ config(
    materialized='incremental',
    unique_key='order_id',
    incremental_strategy='delete+insert'
) }}

select order_id, customer_id, amount, status, updated_at
from {{ ref('stg_orders') }}
{% if is_incremental() %}
where updated_at > (
    select max(updated_at) - interval 1 day from {{ this }}
)
{% endif %}
""")
dbt('dbt run -s orders')

# Step 7: A new channel column upstream: on_schema_change ignore (the default)
write('seeds/raw_orders.csv', """\
order_id,customer_id,amount,status,updated_at,channel
1,10,100.00,placed,2026-10-01 09:00:00,web
2,11,50.00,shipped,2026-10-03 09:00:00,store
3,12,75.00,placed,2026-10-02 08:00:00,web
4,10,20.00,placed,2026-10-03 10:00:00,web
5,13,60.00,placed,2026-10-02 15:00:00,store
""")
write('models/stg_orders.sql', """\
select
    order_id,
    customer_id,
    cast(amount as decimal(10, 2)) as amount,
    status,
    cast(updated_at as timestamp) as updated_at,
    channel
from {{ ref('raw_orders') }}
""")
write('models/orders.sql', """\
{{ config(
    materialized='incremental',
    unique_key='order_id',
    incremental_strategy='delete+insert'
) }}

select order_id, customer_id, amount, status, updated_at,
       channel
from {{ ref('stg_orders') }}
{% if is_incremental() %}
where updated_at > (
    select max(updated_at) - interval 1 day from {{ this }}
)
{% endif %}
""")
dbt('dbt seed --full-refresh')
dbt('dbt run -s stg_orders orders')

# Step 8: on_schema_change='append_new_columns': the column is added, old rows are not backfilled
write('models/orders.sql', """\
{{ config(
    materialized='incremental',
    unique_key='order_id',
    incremental_strategy='delete+insert',
    on_schema_change='append_new_columns'
) }}

select order_id, customer_id, amount, status, updated_at,
       channel
from {{ ref('stg_orders') }}
{% if is_incremental() %}
where updated_at > (
    select max(updated_at) - interval 1 day from {{ this }}
)
{% endif %}
""")
dbt('dbt run -s orders')

# Step 9: Order lines: merge on line_id vs delete+insert on order_id
write('seeds/raw_order_lines.csv', """\
line_id,order_id,sku,qty,updated_at
1,7,A,1,2026-10-05 09:00:00
2,7,B,2,2026-10-05 09:00:00
3,8,A,1,2026-10-05 10:00:00
""")
write('models/lines_merge.sql', """\
{{ config(
    materialized='incremental',
    incremental_strategy='merge',
    unique_key='line_id'
) }}

select line_id, order_id, sku, qty, updated_at
from {{ ref('raw_order_lines') }}
{% if is_incremental() %}
where updated_at > (select max(updated_at) from {{ this }})
{% endif %}
""")
write('models/lines_delete_insert.sql', """\
{{ config(
    materialized='incremental',
    incremental_strategy='delete+insert',
    unique_key='order_id'
) }}

select line_id, order_id, sku, qty, updated_at
from {{ ref('raw_order_lines') }}
{% if is_incremental() %}
where updated_at > (select max(updated_at) from {{ this }})
{% endif %}
""")
dbt('dbt build -s +lines_merge +lines_delete_insert')

# Step 10: Order 7 is edited down to one line: rerun both models
write('seeds/raw_order_lines.csv', """\
line_id,order_id,sku,qty,updated_at
1,7,A,3,2026-10-06 09:00:00
3,8,A,1,2026-10-05 10:00:00
""")
dbt('dbt build -s +lines_merge +lines_delete_insert')

# Step 11: Drift check: rows in staging that differ from the incremental table

# Step 12: Rebuild the table with --full-refresh
dbt('dbt run -s orders --full-refresh')

# Step 13: A microbatch events model: the first load, October 1 to 3
write('seeds/raw_events.csv', """\
event_id,user_id,event_type,event_at
1,10,view,2026-10-01 08:00:00
2,10,click,2026-10-01 08:05:00
3,11,view,2026-10-02 09:00:00
4,12,view,2026-10-02 11:30:00
5,11,purchase,2026-10-03 14:00:00
6,12,click,2026-10-03 16:45:00
""")
write('models/stg_events.sql', """\
select
    event_id,
    user_id,
    event_type,
    cast(event_at as timestamp) as event_at
from {{ ref('raw_events') }}
""")
write('models/events.yml', """\
models:
  - name: stg_events
    config:
      event_time: event_at
""")
write('models/fct_events.sql', """\
{{ config(
    materialized='incremental',
    incremental_strategy='microbatch',
    event_time='event_at',
    batch_size='day',
    begin='2026-10-01',
    lookback=1,
    full_refresh=false
) }}

select event_id, user_id, event_type, event_at
from {{ ref('stg_events') }}
""")
dbt('dbt build -s +stg_events')
dbt('dbt run -s fct_events --event-time-start 2026-10-01 --event-time-end 2026-10-04')

# Step 14: A late event on October 2: backfill only that day
write('seeds/raw_events.csv', """\
event_id,user_id,event_type,event_at
1,10,view,2026-10-01 08:00:00
2,10,click,2026-10-01 08:05:00
3,11,view,2026-10-02 09:00:00
4,12,view,2026-10-02 11:30:00
7,13,purchase,2026-10-02 18:20:00
5,11,purchase,2026-10-03 14:00:00
6,12,click,2026-10-03 16:45:00
8,10,view,2026-10-04 07:10:00
""")
dbt('dbt seed -s raw_events')
dbt('dbt run -s fct_events --event-time-start 2026-10-02 --event-time-end 2026-10-03')
Next in this series

Part 4: Testing and Data Quality

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.

No comments:

Post a Comment

Note: Only a member of this blog may post a comment.