DT7: dbt: transformations as tested code
Materialisations: view, table, incremental
🎯You'll be able to
Choose how each model is stored (view, table, incremental), configure an incremental model with a unique key, and know its limits.
🏠The everyday picture
Some dishes are cooked to order (a view: computed when asked), some are cooked in the morning for the day (a table), and a big pot of dal gets topped up as the day goes on instead of being remade (incremental).
📘What it really is
-
View: stores the SQL, runs it each time the model is queried. Cheap to build, always current, slower to read. SabziSathi's staging models are views.
-
Table: runs the SQL and stores the result, rebuilt in full on each
dbt build. Fast to read; rebuild time grows with the data. SabziSathi's dimensions and marts are tables. -
Incremental: on the first run builds the table; later runs process only new or changed rows and merge them in.
sql
{{ config(materialized='incremental', unique_key='order_line_id', incremental_strategy='delete+insert') }}
select ...
{% if is_incremental() %}
where o.order_date >= (select max(order_date) - interval 3 day from {{ this }})
{% endif %}
The unique_key makes re-processing safe (DT3 Lesson 2); the 3-day look-back picks up late rows (DT3 Lesson 3).
-
Limits of incremental: rows deleted from the source stay unless the look-back window replaces them; changes to the model's logic need a full rebuild with
dbt build --full-refresh. -
Start with views and tables. Make a model incremental when its full rebuild is too slow or costly, not before.
- Ephemeral models are pasted into the models that use them as CTEs, and not stored at all.
📝A worked example
Input: in the solution, reload one day with python -m pipeline.run --date 2026-10-30, then dbt build --profiles-dir . again, and count
marts.fct_order_lines.
Expected output: still 10,181 rows and ₹19,65,764 net, because the incremental model deleted and re-inserted the last 3 days' lines by
order_line_id instead of appending them. Project 2 test 9 does exactly this.
🌍Where it's used
- Large fact tables: event and transaction facts built incrementally every hour.
- Dimensions: usually tables, rebuilt in full, since they're small.
- Exploration: views for staging and early marts, while logic is still changing.
📝Case scenario
A team made every model incremental "for speed". When they changed how refunds were counted, only new rows used the new rule; the old ones kept the old rule, and month-on-month comparisons became wrong. A full refresh after the change, and tables for the small models, fixed it.
🖱️Try it
In the starter's fct_order_lines, add the incremental config and a where clause for new rows, without a unique_key. Run dbt build
twice after reloading a day, and count rows. Then add the key. Success: you saw rows multiply without the key and stay stable with it.
⚠️Common mistakes
- Incremental without a unique key. Sign: re-processed days are added twice. Fix:
unique_keyon the grain. - No look-back. Sign: late rows are skipped. Fix: re-process a short window each run.
- Forgetting full refreshes. Sign: old rows follow old logic after a change. Fix:
--full-refreshwhen the logic changes.
🛠️For your project
Project 2 test 9 checks fct_order_lines is incremental with a unique key and gives the same totals after a reload and rebuild.
✅Quick check
When is a table better than a view?
Show the answer
When the model is read often and is cheap enough to rebuild in full.
Why does an incremental model need a unique key?
Show the answer
So re-processed rows replace their old versions instead of being added again.