Lab 08 — Your First dbt Project on DuckDB

Lesson 08 · Data Engineering with Python · ~45 min

Goal: At the end of this lab you will have (a) installed dbt-core and the DuckDB adapter, (b) loaded the L02 Parquet partitions into a DuckDB table that dbt sees as source('raw', 'events_raw'), (c) run dbt build and watched all models and tests pass, (d) viewed the lineage graph, and (e) intentionally broken a test to see dbt build fail.

Prerequisites

• dbt installed: data-engineering/sample-pipeline/requirements.txt pins dbt-core>=1.7,<2 and dbt-duckdb>=1.7,<2. Install from the repo root:

  pip install -r data-engineering/sample-pipeline/requirements.txt

• L02 Parquet output exists at data-engineering/sample-pipeline/data/parquet/date=*/. If not, run the L02 lab first to produce the partitioned Parquet files.
• One terminal capable of running python, duckdb, and dbt CLI commands.

Step 1 — Confirm dbt is installed

dbt --version

Expected output: a line showing Core: ... 1.8.x and Plugins: ... duckdb: 1.8.x. If you only see Core but no DuckDB plugin, install the adapter explicitly:

pip install dbt-duckdb

Step 2 — Load the Parquet into a DuckDB table dbt can see

dbt’s source() references a real database object. With DuckDB the cleanest approach is to create a persistent table in warehouse/dev.duckdb that wraps the Parquet partitions. This avoids copying data — DuckDB reads the Parquet files directly.

cd data-engineering/sample-pipeline
mkdir -p warehouse
duckdb warehouse/dev.duckdb

At the DuckDB prompt:

CREATE OR REPLACE TABLE events_raw AS
SELECT *
FROM read_parquet('data/parquet/date=*/*.parquet',
                  hive_partitioning => true);
SELECT COUNT(*) FROM events_raw;
.quit

Expected: the row count matches the L02 lab’s final event count. The table events_raw is now a persistent object in dev.duckdb that the dbt source raw.events_raw resolves to. The columns are type, actor, repo, created_at, and date (the hive partition column).

Step 3 — Inspect the dbt project

The project lives at data-engineering/sample-pipeline/dbt/. Read dbt_project.yml:

name: 'github_events'
version: '1.0.0'
config-version: 2

profile: 'github_events'

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

models:
  github_events:
    staging:
      +materialized: view
    marts:
      +materialized: table

The +materialized defaults set staging models to view (cheap, no storage) and mart models to table (queried often, worth storing). Individual models can override this default with {{ config(materialized='incremental') }}.

Step 4 — Configure the profile

dbt looks for profiles.yml in ~/.dbt/ by default. The repo ships an example at data-engineering/sample-pipeline/dbt/profiles.yml.example:

github_events:
  target: dev
  outputs:
    dev:
      type: duckdb
      path: '../warehouse/dev.duckdb'
      threads: 4
      schema: main

The cleanest local workflow: copy the example to profiles.yml (gitignored) in the same directory, then run dbt with DBT_PROFILES_DIR=$(pwd) so dbt finds the file next to the project:

cd data-engineering/sample-pipeline/dbt
cp profiles.yml.example profiles.yml

The path ../warehouse/dev.duckdb is relative to the dbt/ directory, pointing at the file you created in Step 2. With schema: main, dbt will create all models in DuckDB’s default main schema — the same schema that holds events_raw.

Step 5 — Read the source declaration

Open models/staging/sources.yml. The key section:

sources:
  - name: raw
    schema: main
    tables:
      - name: events_raw
        columns:
          - name: type
            tests: [not_null]
          - name: actor
            tests: [not_null]
          - name: date
            tests: [not_null]
          # ... (created_at, repo also declared)

dbt now knows that {{ source('raw', 'events_raw') }} resolves to main.events_raw in dev.duckdb. The column tests listed here will fire automatically when you run dbt build — they’re free, and they guard the input layer.

Step 6 — Read the staging models

models/staging/stg_events.sql — light cleaning of the raw source:

-- models/staging/stg_events.sql
SELECT
  type,
  actor,
  repo,
  created_at,
  date AS event_date
FROM {{ source('raw', 'events_raw') }}

The only transformation here is renaming the hive partition column date to event_date for clarity in downstream models. Every {{ source(...) }} call is a Jinja expression — dbt renders the template before sending the SQL to DuckDB.

models/staging/stg_actors.sql — one row per actor, derived from stg_events:

-- models/staging/stg_actors.sql
SELECT
  actor,
  MIN(created_at) AS first_seen_at,
  MAX(created_at) AS last_seen_at,
  COUNT(*) AS total_events
FROM {{ ref('stg_events') }}
GROUP BY actor

stg_actors ref()s stg_events. This edge becomes part of the lineage graph: dbt guarantees stg_events is built and tested before stg_actors starts.

Step 7 — Read the marts

models/marts/mart_daily_event_counts.sql:

-- models/marts/mart_daily_event_counts.sql
SELECT
  event_date,
  type,
  COUNT(*) AS n_events,
  COUNT(DISTINCT actor) AS n_actors
FROM {{ ref('stg_events') }}
GROUP BY event_date, type
ORDER BY event_date, type

models/marts/mart_top_actors_per_day.sql — the same window-function pattern from L07, now wrapped as a dbt model that gets built, tested, and tracked automatically:

-- models/marts/mart_top_actors_per_day.sql
WITH ranked AS (
  SELECT
    event_date,
    actor,
    COUNT(*) AS n,
    ROW_NUMBER() OVER (PARTITION BY event_date ORDER BY COUNT(*) DESC) AS rn
  FROM {{ ref('stg_events') }}
  GROUP BY event_date, actor
)
SELECT event_date, actor, n
FROM ranked
WHERE rn <= 10
ORDER BY event_date, rn

Step 8 — dbt build

From inside data-engineering/sample-pipeline/dbt/:

DBT_PROFILES_DIR=$(pwd) dbt build

Expected output (truncated):

Found 4 models, 9 tests, 1 source
1 of 13 START sql view model main.stg_events .......... [RUN]
1 of 13 OK created view stg_events ..................... [OK in 0.4s]
2 of 13 START test not_null_stg_events_type ........... [RUN]
2 of 13 PASS .......................................... [PASS in 0.1s]
...
Done. PASS=13 WARN=0 ERROR=0 SKIP=0 TOTAL=13

All models are built in DAG order. The staging models materialise as views (no storage cost). The two mart models materialise as physical tables. All schema tests pass after each model. The singular test no_negative_event_counts runs after mart_daily_event_counts is created.

Self-check: open DuckDB and query the result:

duckdb warehouse/dev.duckdb -c "SELECT * FROM mart_daily_event_counts LIMIT 5;"

Step 9 — View the lineage

DBT_PROFILES_DIR=$(pwd) dbt docs generate
DBT_PROFILES_DIR=$(pwd) dbt docs serve --port 8082

Open http://localhost:8082 in a browser. Click the lineage-graph icon (bottom-right corner of the UI). Expected: a graph showing:

• raw.events_raw → stg_events → stg_actors
• stg_events → mart_daily_event_counts
• stg_events → mart_top_actors_per_day

Every model shows its rendered SQL, column list, and which tests guard it. This documentation is assembled entirely from your ref() calls and YAML — no extra work. Stop the server with Ctrl-C when done.

Step 10 — Intentionally break a test

Edit models/staging/stg_actors.sql. Add a WHERE actor IS NULL clause to produce zero rows (choosing a condition that is always false for well-formed data):

SELECT
  actor,
  MIN(created_at) AS first_seen_at,
  MAX(created_at) AS last_seen_at,
  COUNT(*) AS total_events
FROM {{ ref('stg_events') }}
WHERE actor IS NULL  -- breaks the not_null + unique tests
GROUP BY actor

Re-run:

DBT_PROFILES_DIR=$(pwd) dbt build

The model builds (produces 0 rows) but the downstream tests on stg_actors may warn or pass-on-empty depending on dbt version. To see an undeniable failure: change stg_events.sql to reference a non-existent column — dbt build will fail at parse time with a SQL error. Restore the original SQL when you’re done.

The point of the exercise: tests fire automatically as part of every build. A regression is caught the same way a software unit test catches a regression — immediately, with a clear error message, before the bad data reaches any downstream consumer.

Step 11 — Selective rebuild

DBT_PROFILES_DIR=$(pwd) dbt build --select stg_events+

Expected: rebuilds stg_events and everything that ref()s it (stg_actors, mart_daily_event_counts, mart_top_actors_per_day). The + suffix means “this model and everything downstream”; +model (prefix) means “this model and everything upstream.” Once you know the selector syntax you stop running full project builds during development.

What you should leave with

• A fully-built local dbt project on DuckDB, with two staging models and two mart tables.
• First-hand observation of dbt build running the DAG in order, with tests firing at every step.
• The lineage graph for the project — generated automatically from your ref() calls.
• The instinct that "every transform is a model, every model has tests, every test runs on every build" is what dbt actually gives you over loose .sql files.

Troubleshooting