Lesson 08 dbt Intro
Theme:

Homework 08 — Solutions

Lesson 08 · Data Engineering with Python · Worked solutions

Solution to Task 1 — Intermediate model with relationships test

models/intermediate/int_actor_event_summary.sql:

-- models/intermediate/int_actor_event_summary.sql
WITH per_type AS (
  SELECT
    actor,
    type,
    COUNT(*) AS n
  FROM {{ ref('stg_events') }}
  GROUP BY actor, type
),
top_type AS (
  SELECT
    actor,
    type,
    ROW_NUMBER() OVER (PARTITION BY actor ORDER BY n DESC) AS rn
  FROM per_type
)
SELECT
  a.actor,
  a.total_events,
  (SELECT COUNT(DISTINCT type)
   FROM {{ ref('stg_events') }} e
   WHERE e.actor = a.actor) AS n_distinct_types,
  (SELECT type FROM top_type t
   WHERE t.actor = a.actor AND t.rn = 1) AS most_common_type
FROM {{ ref('stg_actors') }} a

models/intermediate/schema.yml:

version: 2
models:
  - name: int_actor_event_summary
    description: "One row per actor with event-count, distinct-type count, and most common type."
    columns:
      - name: actor
        tests:
          - unique
          - not_null
          - relationships:
              to: ref('stg_actors')
              field: actor

Addition to dbt_project.yml under models: github_events::

    intermediate:
      +materialized: view

Common mistake

Declaring the relationships test on the parent (stg_actors) instead of the child (int_actor_event_summary). The test belongs to the child column — "every value in this child column must exist in the parent’s field." Putting it on the parent has no effect: stg_actors isn’t the model with orphan-row potential. The error mode is silent: the test runs but never catches anything because it’s pointing at the wrong model.

Solution to Task 2 — incremental materialization

models/marts/mart_daily_event_counts.sql with incremental config:

{{ config(materialized='incremental', unique_key=['event_date', 'type']) }}

SELECT
  event_date,
  type,
  COUNT(*) AS n_events,
  COUNT(DISTINCT actor) AS n_actors
FROM {{ ref('stg_events') }}
{% if is_incremental() %}
WHERE event_date > (
  SELECT COALESCE(MAX(event_date), '1970-01-01'::date)
  FROM {{ this }}
)
{% endif %}
GROUP BY event_date, type
ORDER BY event_date, type

First run from scratch: 

DBT_PROFILES_DIR=$(pwd) dbt build --full-refresh

Second run (incremental):

DBT_PROFILES_DIR=$(pwd) dbt build

The second run completes near-instantly. The log shows 0 rows affected because no new event_date values arrive between runs.

Why it’s overkill here: The course’s GitHub events Parquet is in the low hundreds of megabytes; a full rebuild on DuckDB takes a few seconds at most. The break-even for incremental is roughly “the model takes more than 5 minutes to fully rebuild AND new rows arrive faster than the rebuild cadence.” At 100 GB+ source tables where a full nightly rebuild takes 30+ minutes, incremental becomes essential. At the current scale, it adds operational complexity (back-fill semantics, late-arriving rows, schema-change handling) for zero throughput benefit.

Common mistake

Forgetting unique_key when declaring an incremental model. Without it, dbt build on subsequent runs does a bare INSERT without any deduplication. If a late-arriving event lands inside a previously-processed event_date partition, you get duplicate (event_date, type) rows in the mart silently. With unique_key set, dbt translates the incremental run into a MERGE or delete-insert operation (adapter-dependent) that handles deduplication automatically. Always declare unique_key for incremental models even when you “know” duplicates won’t happen — the data always surprises you eventually.

Solution to Task 3 — Singular freshness test

tests/recent_data_present.sql:

-- tests/recent_data_present.sql
-- Fails the build if the most-recent event_date in the daily mart is
-- more than 7 days behind today. Returns rows only when the invariant is violated.
SELECT
  MAX(event_date) AS latest,
  CURRENT_DATE AS today,
  CURRENT_DATE - MAX(event_date) AS days_stale
FROM {{ ref('mart_daily_event_counts') }}
HAVING CURRENT_DATE - MAX(event_date) > 7

To let the test pass against an aging Parquet snapshot during the lab: change > 7 to > 1000. You have effectively disabled the threshold — accept this for the lab and note that a real production SLA would keep the tight threshold and instead refresh the data on a schedule.

Common mistake

Writing the singular test to return rows unconditionally — for example, SELECT MAX(event_date) FROM mart_daily_event_counts with no HAVING clause. That always returns one row (there is always a max), so the test always fails, so dbt build is permanently broken. A singular test must return rows only when the invariant is violated. The HAVING ... > 7 clause is what makes the test correctly silent on fresh data and noisy only on stale data. Before adding the HAVING, run the bare SELECT to understand the output — then gate on the failure condition.

You’ve now built the canonical dbt sweet-spot: SQL models with a DAG, tests on every layer, and one materialization knob per model. Lesson 09 takes the same dbt project deeper into DuckDB as the local development warehouse — the project itself doesn’t move.