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
- Let's create the project and some sample data
- Question 1: An incremental model loads duplicate rows after a rerun. What did the model miss, and how does unique_key fix it?
- Question 2: A teammate asks what is_incremental and this do in an incremental model. How do you explain it?
- Question 3: Late-arriving records are missing from the incremental table. How do you handle late data?
- Question 4: A new column was added upstream and the incremental model ignored it. How does on_schema_change work?
- Question 5: Your manager asks which incremental strategy to use: append, merge or delete plus insert. How do you decide?
- Question 6: An incremental model has drifted from the source. When and how do you run a full refresh?
- 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?
- The full script
The short answer
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.
name: shop
version: '1.0'
profile: shop
models:
shop:
+materialized: vieworder_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:00select
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.
{{ 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 %}{{ 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 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 successBoth 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:
- name: orders_no_key
columns:
- name: order_id
data_tests:
- unique
- not_null
- name: orders
columns:
- name: order_id
data_tests:
- unique
- not_nullorder_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 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 errorselect order_id, status, updated_at from orders_no_key order by order_id, updated_at| order_id | status | updated_at |
|---|---|---|
| 1 | placed | 2026-10-01 09:00 |
| 2 | placed | 2026-10-01 10:00 |
| 2 | shipped | 2026-10-03 09:00 |
| 3 | placed | 2026-10-02 08:00 |
| 4 | placed | 2026-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.
select order_id, status, updated_at from orders order by order_id| order_id | status | updated_at |
|---|---|---|
| 1 | placed | 2026-10-01 09:00 |
| 2 | shipped | 2026-10-03 09:00 |
| 3 | placed | 2026-10-02 08:00 |
| 4 | placed | 2026-10-03 10:00 |
Four orders, one row each, with order 2 shipped.
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-refreshrun
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.
$ 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 successIn 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.
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.
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 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 successselect order_id, status, updated_at from orders order by order_id| order_id | status | updated_at |
|---|---|---|
| 1 | placed | 2026-10-01 09:00 |
| 2 | shipped | 2026-10-03 09:00 |
| 3 | placed | 2026-10-02 08:00 |
| 4 | placed | 2026-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.
{{ 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 run -s orders
Succeeded model main.orders (incremental)
Finished 'run' successfully for target 'dev'
Processed: 1 model
Summary: 1 total | 1 successselect order_id, status, updated_at from orders order by order_id| order_id | status | updated_at |
|---|---|---|
| 1 | placed | 2026-10-01 09:00 |
| 2 | shipped | 2026-10-03 09:00 |
| 3 | placed | 2026-10-02 08:00 |
| 4 | placed | 2026-10-03 10:00 |
| 5 | placed | 2026-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.
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.
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,storeselect
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') }}{{ 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 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 successselect * from orders order by order_id| order_id | customer_id | amount | status | updated_at |
|---|---|---|---|---|
| 1 | 10 | 100.00 | placed | 2026-10-01 09:00 |
| 2 | 11 | 50.00 | shipped | 2026-10-03 09:00 |
| 3 | 12 | 75.00 | placed | 2026-10-02 08:00 |
| 4 | 10 | 20.00 | placed | 2026-10-03 10:00 |
| 5 | 13 | 60.00 | placed | 2026-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.
{{ 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 run -s orders
Succeeded model main.orders (incremental)
Finished 'run' successfully for target 'dev'
Processed: 1 model
Summary: 1 total | 1 successselect order_id, status, updated_at, channel from orders order by order_id| order_id | status | updated_at | channel |
|---|---|---|---|
| 1 | placed | 2026-10-01 09:00 | NULL |
| 2 | shipped | 2026-10-03 09:00 | store |
| 3 | placed | 2026-10-02 08:00 | NULL |
| 4 | placed | 2026-10-03 10:00 | web |
| 5 | placed | 2026-10-02 15:00 | store |
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_change | what it does |
|---|---|
| ignore | default: new column dropped |
| fail | stop the run |
| append_new_columns | add, no backfill |
| sync_all_columns | add 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.
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.
| strategy | use it when | key |
|---|---|---|
| append | rows never change: events, logs | none |
| merge | rows update by key: upsert | a unique row key |
| delete+insert | replace a whole set: all lines of an order, a day | the 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.
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{{ 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 %}{{ 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 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 successNow the source edits order 7 down to one line, with qty 3. We rerun both models and compare them.
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 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 successselect '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| strategy | line_id | order_id | sku | qty |
|---|---|---|---|---|
| merge | 1 | 7 | A | 3 |
| merge | 2 | 7 | B | 2 |
| merge | 3 | 8 | A | 1 |
| delete+insert | 1 | 7 | A | 3 |
| delete+insert | 3 | 8 | A | 1 |
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.
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.
select order_id, channel from stg_orders except select order_id, channel from orders order by order_id| order_id | channel |
|---|---|
| 1 | web |
| 3 | web |
It returns orders 1 and 3, both web in staging. Then we rebuild with dbt run -s orders --full-refresh.
$ dbt run -s orders --full-refresh
Succeeded model main.orders (incremental)
Finished 'run' successfully for target 'dev'
Processed: 1 model
Summary: 1 total | 1 successselect order_id, status, channel from orders order by order_id| order_id | status | channel |
|---|---|---|
| 1 | placed | web |
| 2 | shipped | store |
| 3 | placed | web |
| 4 | placed | web |
| 5 | placed | store |
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.
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.
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:00select
event_id,
user_id,
event_type,
cast(event_at as timestamp) as event_at
from {{ ref('raw_events') }}models:
- name: stg_events
config:
event_time: event_at{{ 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 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 successselect cast(event_at as date) as event_date, count(*) as events from fct_events group by 1 order by 1| event_date | events |
|---|---|
| 2026-10-01 | 2 |
| 2026-10-02 | 2 |
| 2026-10-03 | 2 |
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.
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 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 successselect cast(event_at as date) as event_date, count(*) as events from fct_events group by 1 order by 1| event_date | events |
|---|---|
| 2026-10-01 | 2 |
| 2026-10-02 | 3 |
| 2026-10-03 | 2 |
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 eventsfull_refresh: false: protects the tabledbt 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
# ========================================================================
# 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')
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.