Homework 08 — dbt Extensions

Task 1 — Add an intermediate model with a relationships test

Create models/intermediate/int_actor_event_summary.sql that joins stg_actors and stg_events on actor and returns one row per actor with:

• actor — the actor identifier
• total_events — total event count (from stg_actors)
• n_distinct_types — count of distinct event types this actor produced
• most_common_type — the event type this actor produced most often

Then create models/intermediate/schema.yml declaring the model and adding a relationships test on actor: every value must exist in stg_actors.actor. Update dbt_project.yml to set +materialized: view for the new intermediate/ layer.

Self-check:

• dbt build runs the new model and the new test passes.
• Break the test: edit int_actor_event_summary.sql to add UNION ALL SELECT 'no_such_user' AS actor, 0, 0, NULL at the end. Confirm dbt build now fails on the relationships test. Restore the original SQL.

Task 2 — Promote mart_daily_event_counts to incremental

Change the materialization on mart_daily_event_counts.sql from table (inherited from the +materialized: table config at the marts layer) to incremental. Use ['event_date', 'type'] as the unique_key. The incremental filter should select only new rows:

{% if is_incremental() %}
WHERE event_date > (
  SELECT COALESCE(MAX(event_date), '1970-01-01'::date)
  FROM {{ this }}
)
{% endif %}

Run dbt build --full-refresh once to build from scratch, then run dbt build again without --full-refresh. The second run should be near-instant (no new rows arrive between runs).

Self-check:

• The second run’s log shows 0 rows affected or an equivalent message.
• Write 2–3 sentences explaining why incremental is overkill for this small dataset and what dataset size or rebuild time would make it the right call.

Task 3 — Add a singular freshness test

Add a singular test tests/recent_data_present.sql that fails the build if the most-recent event_date in mart_daily_event_counts is more than 7 days behind today. Use DuckDB’s CURRENT_DATE. The test is one SELECT that returns rows only when the freshness invariant is violated.

Run dbt build after adding the test.

Self-check:

• If your L02 Parquet is older than 7 days, the test fails — that’s the correct behaviour (a real freshness alert). Either refresh the data or tune the threshold to > 1000 days for the lab to let it pass.
• Confirm the test returns no rows on fresh data and one row on stale data. A test that always returns a row is permanently broken.