dbt Foundations: Sources, Staging and Tests

6.1 Why dbt: SQL as a tested project (45 min)

Concept. On Day 3 the panel was one SQL file plus a Python script of checks, run in the right order by hand. That works for one analyst and one table. It breaks when a team maintains dozens of tables that depend on each other. dbt (data build tool) turns SQL into a project:

dbt only does the T in ELT (extract, load, transform). dlt loaded the raw data; dbt transforms it inside DuckDB.

The layers we'll build:

6.2 Setting up the project and your first model (45 min)

Step 1 — the project folder.

mkdir -p transform/models/staging transform/models/intermediate transform/models/marts
mkdir -p transform/tests/generic transform/macros

Step 2 — transform/dbt_project.yml names the project, sets defaults per layer, and holds variables we'll use tomorrow:

name: northmart
version: "1.0.0"
profile: northmart

model-paths: ["models"]
test-paths: ["tests"]
macro-paths: ["macros"]

vars:
  first_week: "2026-01-05"      # Monday of week 1
  last_week: "2026-04-20"       # Monday of week 16
  campaign_id: "SPRING26"
  campaign_week: 9              # the email went out on Monday 2026-03-02

models:
  northmart:
    staging:
      +materialized: view
      +schema: staging
    intermediate:
      +materialized: view
      +schema: intermediate
    marts:
      +materialized: table
      +schema: marts

Step 3 — transform/profiles.yml tells dbt which database to use. The path is relative to the transform/ folder:

northmart:
  target: dev
  outputs:
    dev:
      type: duckdb
      path: ../data/northmart.duckdb   # relative to the transform/ folder
      threads: 4

Step 4 — transform/macros/generate_schema_name.sql. By default dbt would name our schemas main_staging and main_marts. This small macro keeps the names exactly as written:

-- Use the schema names from dbt_project.yml as they are (staging, marts, ...),
-- instead of dbt's default of prefixing them with the target schema (main_staging).
{% macro generate_schema_name(custom_schema_name, node) -%}
    {{ custom_schema_name if custom_schema_name else target.schema }}
{%- endmacro %}

Step 5 — check the connection. All dbt commands run from inside transform/, with --profiles-dir . so dbt uses our profiles.yml:

cd transform
dbt debug --profiles-dir .

  Connection test: [OK connection ok]
All checks passed!

Example — your first model. Create transform/models/first_model.sql with one line:

select 'Did the email work?' as question, 42 as answer

dbt run --profiles-dir . --select first_model
dbt show --profiles-dir . --select first_model

1 of 1 OK created sql view model main.first_model .............................. [OK in 0.12s]

| question            | answer |
| ------------------- | ------ |
| Did the email work? |     42 |

A model is just a SELECT. dbt wrapped it in CREATE VIEW main.first_model AS .... Delete first_model.sql now; it was only a test.

Keep two terminals today too: one for the API (only needed if you re-run dlt) and one inside transform/ for dbt. Run Python scripts from the repository root (cd ..).

6.3 Sources and staging models (60 min)

Concept. A source declares a table that dbt didn't create (here, dlt's raw tables). Models refer to it with {{ source('raw', 'orders') }}, never by hard-coded name, so dbt knows the dependency and can document it.

A staging model does the boring, essential work for one source table: rename columns, cast types, clean codes, remove rows that don't represent real events. Nothing else: no joins, no business logic.

Example 1 — declare the sources. transform/models/staging/_sources.yml:

version: 2

sources:
  - name: raw
    description: "Tables loaded from the NorthMart API by dlt (day05/northmart_pipeline.py)."
    schema: raw
    tables:
      - name: customers
      - name: products
      - name: orders
      - name: campaign_recipients

Example 2 — customers. transform/models/staging/stg_customers.sql. This is Day 2's clean_customers function, now in SQL:

-- One row per customer. Region codes cleaned; missing region kept as UNKNOWN.
select
    customer_id,
    cast(signup_date as date)                                  as signup_date,
    coalesce(upper(trim(profile__region)), 'UNKNOWN')          as region,
    cast(profile__age as integer)                              as age,
    cast(is_loyalty as boolean)                                as is_loyalty
from {{ source('raw', 'customers') }}

Example 3 — orders. transform/models/staging/stg_orders.sql. It flattens dlt's column names, casts the date, computes revenue, and removes cancelled orders:

-- One row per completed order. Cancelled orders never happened, so they are removed here.
with ranked as (
    select
        *,
        row_number() over (partition by order_id order by _dlt_load_id desc) as rn
    from {{ source('raw', 'orders') }}
)
select
    order_id,
    customer_id,
    cast(order_date as date)                                   as order_date,
    line__product_id                                           as product_id,
    line__quantity                                             as quantity,
    line__unit_price                                           as unit_price,
    line__discount_pct                                         as discount_pct,
    round(line__quantity * line__unit_price, 2)                as revenue
from ranked
where rn = 1                       -- safety net: one row per order_id
  and status = 'completed'

merge already removed duplicate orders on Day 5, so why the row_number()? Because staging shouldn't trust that every future load is perfect. If it ever receives the same order twice, it keeps the latest load. That costs one line and protects every model downstream.

Example 4 — the two small tables. stg_products.sql and stg_campaign_recipients.sql:

select product_id, category, base_price
from {{ source('raw', 'products') }}

select
    customer_id,
    campaign_id,
    cast(sent_date as date) as sent_date
from {{ source('raw', 'campaign_recipients') }}

Run the staging layer and preview one model with an inline query:

dbt run --profiles-dir . --select staging
dbt show --profiles-dir . --inline "select region, count(*) n from {{ ref('stg_customers') }} group by 1 order by 2 desc"

| region |    n |
| ------ | ---- |
| ON     | 1480 |
| QC     |  912 |
| BC     |  610 |
| ATL    |  476 |
| AB     |  448 |

dbt show previews 5 rows by default; add --limit 10 to see the UNKNOWN row too.

6.4 Tests: unique, not_null, accepted_values, relationships (45 min)

Concept. A dbt test is a query that looks for bad rows. If it finds any, the test fails. dbt ships four generic tests:

Tests live in a YAML file next to the models. Create transform/models/staging/_staging.yml:

version: 2

models:
  - name: stg_customers
    description: "One row per customer, regions cleaned."
    columns:
      - name: customer_id
        data_tests: [unique, not_null]
      - name: region
        data_tests:
          - accepted_values:
              arguments:
                values: ["ON", "QC", "BC", "AB", "ATL", "UNKNOWN"]
      - name: age
        data_tests: [not_null]

  - name: stg_orders
    description: "One row per completed order."
    columns:
      - name: order_id
        data_tests: [unique, not_null]
      - name: customer_id
        data_tests:
          - not_null
          - relationships:
              arguments:
                to: ref('stg_customers')
                field: customer_id
      - name: product_id
        data_tests:
          - relationships:
              arguments:
                to: ref('stg_products')
                field: product_id

  - name: stg_products
    columns:
      - name: product_id
        data_tests: [unique, not_null]

  - name: stg_campaign_recipients
    description: "Customers who received a campaign email, with the send date."
    columns:
      - name: customer_id
        data_tests:
          - unique
          - relationships:
              arguments:
                to: ref('stg_customers')
                field: customer_id

dbt build runs models and their tests, in dependency order:

dbt build --profiles-dir . --select staging

... OK created sql view model staging.stg_orders ......................... [OK in 0.30s]
... PASS unique_stg_orders_order_id ...................................... [PASS in 0.25s]
... PASS accepted_values_stg_customers_region__ON__QC__BC__AB__ATL__UNKNOWN  [PASS in 0.15s]
...
Done. PASS=17 WARN=0 ERROR=0 SKIP=0 NO-OP=0 REUSED=0 TOTAL=17

dbt 1.12 syntax. Test settings go under arguments: (as above). Older tutorials put values: directly under the test name; dbt still accepts that but warns that it's deprecated.

6.5 Day 6 mini-project: a fully tested staging layer (60 min)

Part 1 — watch a test fail. Tests only earn trust once you've seen them catch something. In stg_customers.sql, temporarily remove the cleaning so the line reads coalesce(profile__region, 'UNKNOWN') as region, then:

dbt build --profiles-dir . --select stg_customers

FAIL 10 accepted_values_stg_customers_region__ON__QC__BC__AB__ATL__UNKNOWN  [FAIL 10 in 0.18s]
  Got 10 results, configured to fail if != 0
Done. PASS=6 WARN=0 ERROR=1 SKIP=1 NO-OP=0 REUSED=0 TOTAL=8

dbt found the 10 messy codes (on, QC…). Notice SKIP=1: a test downstream of the failure was skipped, not run on bad data. Restore the upper(trim(...)) line and rebuild until everything passes.

Part 2 — complete the tests. Add tests to stg_products so that:

1. category only takes the five known values.
2. base_price is never missing.

Then run dbt build --profiles-dir . --select staging until it passes.

  - name: stg_products
    columns:
      - name: product_id
        data_tests: [unique, not_null]
      - name: category
        data_tests:
          - accepted_values:
              arguments:
                values: ["Grocery", "Home", "Electronics", "Apparel", "Beauty"]
      - name: base_price
        data_tests: [not_null]

Done. PASS=19 WARN=0 ERROR=0 ...

Part 3 — reconcile. Use dbt show --inline to count completed orders in stg_orders. Does it match Day 2's clean file (17,839)?

dbt show --profiles-dir . --inline "select count(*) as n, round(sum(revenue)) as revenue from {{ ref('stg_orders') }}"

|     n |   revenue |
| ----- | --------- |
| 17839 | 2,810,574 |

6.6 Save your work with Git (15 min)

cd ..                 # back to the repository root
git add .
git status            # transform/target/ and transform/logs/ must NOT appear
git commit -m "Day 6: dbt project with tested staging models"
git push

Day 6 checklist

• I can explain what dbt adds to plain SQL files
• I can configure a dbt project and profile for DuckDB and run dbt debug
• I can declare sources and write staging models that rename, cast, clean and filter
• I can add generic tests and read dbt build output, including failures and skips
• I have reconciled the dbt staging layer against Day 2's clean data