📋 What dbt solves

Before dbt, analytics engineers wrote transformation SQL in notebooks or ad-hoc scripts. There was no version control for SQL, no way to test that a column was never null, no documentation of which table depended on which, and no reproducible way to re-run transformations from scratch.

dbt (data build tool) turns SQL into software engineering:

Problemdbt solution
SQL scattered across notebooks and dashboardsModels: each SQL file is a versioned, named transformation unit
No tests — broken data is discovered in prod dashboardsBuilt-in schema tests: not_null, unique, accepted_values, relationships
Nobody knows which table depends on whichDAG lineage: ref() builds a dependency graph; dbt docs serve shows it visually
Rebuilding everything on each run is slowIncremental models: only process new rows using is_incremental()
No documentation for stakeholdersdbt docs generate produces a browsable data catalogue from YAML descriptions
dbt Core vs dbt Cloud

dbt Core is the open-source CLI — free forever, runs anywhere (laptop, Airflow, GitHub Actions). dbt Cloud is a managed SaaS wrapper that adds a scheduler, IDE, CI/CD, and monitoring. For this lesson we use dbt Core with DuckDB as the warehouse — the fastest possible local setup: no server, no credentials, just a file.

📁 Project structure & three-layer architecture

A dbt project is a directory with a specific layout. Every model is a .sql file in models/. Tests live in schema.yml files alongside the models. Reusable Jinja logic goes in macros/. Static seed data (CSV files dbt loads into the warehouse) go in seeds/.

shell Lesson 30 · dbt project setup with DuckDB
🏪
Marts layer
Aggregated, stakeholder-facing tables. Owned by analytics/BI team. Named mart_*. These are what dashboards and reports query.
⚙️
Intermediate layer
Business logic: joins, window functions, derived columns. Named int_*. Not exposed to stakeholders — building blocks for marts.
🧹
Staging layer
1-to-1 with source tables. Rename columns, cast types, filter deleted records. Named stg_*. One model per source table.

🧹 Staging model — stg_payments.sql

The staging model reads directly from the raw source table and produces a clean, typed version. It never joins other models — it only touches one source at a time. The source() function tells dbt where raw data lives and lets it track freshness.

models/staging/stg_payments.sql Lesson 30 · staging model
dbt run --select stg_payments
Running with dbt=1.8.2 Found 6 models, 18 tests, 1 source Concurrency: 1 threads (target='dev') 1 of 1 START sql view model dev.stg_payments ................................. [RUN] 1 of 1 OK created sql view model dev.stg_payments ............................ [OK in 0.14s] Finished running 1 view model in 0 hours 0 minutes and 0.38 seconds. Completed successfully.
Views vs tables in dbt

By default, dbt creates staging models as views (no data is materialised). This is correct — staging is a thin transformation lens over raw data. Intermediate and mart models are typically materialised as tables so downstream queries don't re-run the logic on every hit. Configure materialisation in dbt_project.yml or with a {{{{ config(materialized='table') }}}} block at the top of the model file.

🏪 Mart model — mart_revenue_by_region.sql

The mart model uses ref() to depend on intermediate models. dbt builds the dependency graph from these references and guarantees correct execution order. You never hardcode a schema or table name — ref('int_payment_daily') resolves to the correct schema for your target environment.

models/marts/mart_revenue_by_region.sql Lesson 30 · mart model with ref()
dbt run --select +mart_revenue_by_region
1 of 3 START sql view model dev.stg_payments ............... [OK in 0.12s] 2 of 3 START sql table model dev.int_payment_daily ........... [OK in 0.41s] 3 of 3 START sql table model dev.mart_revenue_by_region ...... [OK in 0.28s] Completed successfully. 3 models: 3 OK, 0 ERROR
The + prefix: --select +mart_revenue_by_region tells dbt to run this model AND all its upstream dependencies (the + prefix). Without +, dbt runs only the specified model and assumes its upstream models already exist in the warehouse.

🧪 Tests — schema.yml built-ins and custom assertions

dbt ships with four generic tests that cover 80% of what you need. They live in schema.yml alongside your models:

TestWhat it checks
not_nullNo NULL values in the column
uniqueNo duplicate values in the column
accepted_valuesColumn only contains values from a specified list
relationshipsEvery value in this column exists in a referenced column (foreign key check)
models/staging/schema.yml Lesson 30 · built-in tests + descriptions
dbt test --select stg_payments
Running with dbt=1.8.2 Found 6 models, 18 tests, 1 source 1 of 5 START test not_null_stg_payments_payment_id ............... [RUN] 1 of 5 PASS not_null_stg_payments_payment_id .................... [PASS in 0.08s] 2 of 5 START test unique_stg_payments_payment_id ................. [RUN] 2 of 5 PASS unique_stg_payments_payment_id ...................... [PASS in 0.07s] 3 of 5 START test accepted_values_stg_payments_payment_status ... [RUN] 3 of 5 PASS accepted_values_stg_payments_payment_status ......... [PASS in 0.09s] 4 of 5 START test not_null_stg_payments_amount_kes ............... [RUN] 4 of 5 PASS not_null_stg_payments_amount_kes .................... [PASS in 0.06s] 5 of 5 START test relationships_stg_payments_customer_id ......... [RUN] 5 of 5 PASS relationships_stg_payments_customer_id .............. [PASS in 0.11s] Finished running 5 tests. Completed successfully. 5 passed, 0 failed, 0 warnings.

⚡ Incremental models — only process new rows

A table materialisation re-builds the entire table on every dbt run. For a payments table with 200 million rows, that takes hours. Incremental models solve this: on the first run they build the full table; on subsequent runs they only process rows added since the last run.

models/intermediate/int_payment_daily.sql Lesson 30 · incremental model
dbt run --select int_payment_daily (first run vs incremental)
# First run — full build 1 of 1 START sql incremental model dev.int_payment_daily ........ [RUN] 1 of 1 OK created sql incremental model dev.int_payment_daily ... [INSERT 1826 in 1.24s] # Second run (next day) — only yesterday's data 1 of 1 START sql incremental model dev.int_payment_daily ........ [RUN] 1 of 1 OK created sql incremental model dev.int_payment_daily ... [INSERT 1 in 0.09s]
dbt build — run + test in one command

dbt build is the recommended production command. It runs models and tests in the correct dependency order: it builds a model, immediately runs its tests, then proceeds to downstream models only if the tests pass. If a staging test fails, the mart model is never built, preventing bad data from propagating.

🗺️ Lineage — dbt docs generate & serve

Run two commands and get a browsable data catalogue with a visual lineage DAG:

shell Lesson 30 · generate and serve docs
Lineage graph view: In the dbt docs UI, the lineage graph shows source:borapay_raw.payments → stg_payments → int_payment_daily → mart_revenue_by_region. Any analyst can click a mart and trace exactly which raw table, transformation logic, and tests back it. This is the fastest way to answer "where does this number come from?"

⚠️ Common mistakes

Using hardcoded schema names instead of ref()
Writing FROM dev.stg_payments instead of FROM {{ ref('stg_payments') }} breaks dbt's lineage tracking, prevents environment switching (dev/prod), and means dbt cannot determine the correct build order. Always use ref() for model-to-model references and source() for raw tables.
Putting business logic in staging models
Staging models should be thin: rename, cast, filter. No joins, no business calculations. If you put a complex rolling window function in a staging model, it becomes hard to reuse and test. Business logic belongs in intermediate models where it can be clearly named and documented.
Incremental model without a unique_key
Without unique_key, dbt appends new rows on every incremental run — it never updates existing rows. If a payment status changes from pending to settled, the old row stays and you get duplicates. Define a unique_key that matches your business deduplication logic.

🧠 Quiz — 4 questions

Question 1 of 4
What is the purpose of ref('model_name') in a dbt model?
Question 2 of 4
In the three-layer dbt architecture, which layer is directly queried by BI dashboards?
Question 3 of 4
What does the is_incremental() Jinja macro return?
Question 4 of 4
Which dbt command runs models AND tests in the correct dependency order, stopping downstream models if an upstream test fails?
0
/ 4
dbt fundamentals mastered.

Next: Cloud Warehouses — Snowflake, BigQuery, Redshift, Databricks.

🎤 Interview Q&A

Easy What is the difference between source() and ref() in dbt?
+
source() is used to reference raw tables that exist in your warehouse outside of dbt — tables loaded by your ingestion tool (Fivetran, Airbyte, COPY command) rather than built by a dbt model. It requires the source to be declared in a schema.yml file with a sources: block, which enables dbt to track freshness (how recently the table was updated) and lets the lineage graph show the full data flow from raw source to mart. ref() is used to reference other dbt models — it resolves to the correct schema and table name for your current target environment (dev vs prod), and tells dbt to build the referenced model first. The rule of thumb: use source() in staging models when reading from raw tables; use ref() everywhere else.
Medium How do you handle late-arriving data in a dbt incremental model?
+
The standard incremental filter where created_at > (select max(created_at) from {{ this }}) will miss late-arriving records — rows that arrived in the source table after the last dbt run but have a timestamp older than the cutoff. There are two common approaches. First, use a lookback window: instead of filtering at the exact max timestamp, subtract a buffer — for example, where created_at > (select max(created_at) - interval '3 hours' from {{ this }}). This reprocesses the last 3 hours of data on every run, catching late arrivals at the cost of slightly more computation. Second, use event_time and batch_date separation: filter on a batch_loaded_at column (when the row arrived in your warehouse) rather than the business timestamp. This guarantees you never miss rows that were loaded into your data platform after the previous run, regardless of when the business event occurred. In production, the lookback window approach is the most common pattern for daily incremental models.
Hard How would you test that revenue numbers in a mart are consistent with the source payments table?
+
This is a custom singular test — a SQL file in the tests/ directory that dbt runs as an assertion. The convention is: a test passes if the query returns zero rows; it fails if any rows are returned. For revenue reconciliation you would write something like: SELECT 'revenue_mismatch' as issue, abs(source_total - mart_total) as delta FROM (SELECT sum(amount_kes) as source_total FROM {{ ref('stg_payments') }} WHERE is_settled) s CROSS JOIN (SELECT sum(total_revenue_kes) as mart_total FROM {{ ref('mart_revenue_by_region') }}) m WHERE abs(source_total - mart_total) > 0.01. The 0.01 tolerance handles floating-point rounding. If revenue diverges by more than a cent, the test returns a row, dbt marks it as failed, and your CI pipeline blocks deployment. The dbt-expectations package extends this further with expect_column_sum_to_be_between and similar statistical assertions, which are useful for detecting anomalies (a day where revenue drops to zero is likely a pipeline failure, not a real business event).

✅ Key takeaways

  • dbt turns SQL into versioned, testable, documented software. Every transformation is a .sql file; every test is a YAML declaration; every lineage relationship is inferred from ref().
  • The three-layer architecture — staging (clean), intermediate (logic), mart (aggregate) — keeps each layer focused and reusable. Staging models read from source(); everything else uses ref().
  • Four built-in tests cover most data quality needs: not_null, unique, accepted_values, relationships. Declare them in schema.yml alongside model descriptions.
  • Incremental models use is_incremental() to filter only new rows on non-first runs. Always define a unique_key to enable upsert semantics and prevent duplicates on re-runs.
  • dbt build is the production command: it runs models and their tests in dependency order and blocks downstream builds if upstream tests fail. dbt docs generate && dbt docs serve produces a browsable lineage graph and data catalogue.
Africa & India's Best AI Academy
🤖 AI-first 🎓 Placement Assistance

Build analytics pipelines engineers are proud of

Join the Best AI Program: The AI-Era Data Engineer — 5 months, fully online, with placement assistance. Week 1 is free.

Explore The AI-Era Data Engineer →