📋 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:
| Problem | dbt solution |
|---|---|
| SQL scattered across notebooks and dashboards | Models: each SQL file is a versioned, named transformation unit |
| No tests — broken data is discovered in prod dashboards | Built-in schema tests: not_null, unique, accepted_values, relationships |
| Nobody knows which table depends on which | DAG lineage: ref() builds a dependency graph; dbt docs serve shows it visually |
| Rebuilding everything on each run is slow | Incremental models: only process new rows using is_incremental() |
| No documentation for stakeholders | dbt docs generate produces a browsable data catalogue from YAML descriptions |
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/.
mart_*. These are what dashboards and reports query.int_*. Not exposed to stakeholders — building blocks for marts.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.
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.
--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:
| Test | What it checks |
|---|---|
not_null | No NULL values in the column |
unique | No duplicate values in the column |
accepted_values | Column only contains values from a specified list |
relationships | Every value in this column exists in a referenced column (foreign key check) |
⚡ 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.
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:
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
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.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
ref('model_name') in a dbt model?is_incremental() Jinja macro return?Next: Cloud Warehouses — Snowflake, BigQuery, Redshift, Databricks.
🎤 Interview Q&A
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.
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.
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
.sqlfile; every test is a YAML declaration; every lineage relationship is inferred fromref(). - The three-layer architecture — staging (clean), intermediate (logic), mart (aggregate) — keeps each layer focused and reusable. Staging models read from
source(); everything else usesref(). - Four built-in tests cover most data quality needs:
not_null,unique,accepted_values,relationships. Declare them inschema.ymlalongside model descriptions. - Incremental models use
is_incremental()to filter only new rows on non-first runs. Always define aunique_keyto enable upsert semantics and prevent duplicates on re-runs. dbt buildis 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 serveproduces a browsable lineage graph and data catalogue.
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 →