Skip to content

Day 1 Homework and Wrap-Up - Modeling, Testing & Macros

This pulls together everything from 1.2-modeling-refresh.md, 1.3-testing.md, and 1.4-jinja-and-macros.md into one closing checklist, plus two new requirements: a documentation/test-coverage health check, and a final PR.

Work on your dpg/<your_name>/day1 branch. Keep committing as you go - don't save it all for one giant commit at the end; a real commit history makes the PR review (and your own reasoning trail) much easier to follow, especially for the duplicate-article_id bug you diagnose but deliberately don't fix in section 1.

1. Finish the outstanding exercise steps

From 1.2-modeling-refresh.md

  • Trace the dim_shows lineage; write one sentence describing each model's job in the chain
  • Check how stg_podcasts__shows and dim_shows are materialized in dbt_project.yml; write one or two sentences on why staging is typically a view while marts are typically table
  • Read int_shows_clean_string_columns; add a comment at the top of the file documenting its purpose and any known string patterns it cleans
  • Point dim_shows at int_shows_clean_string_columns instead of stg_podcasts__shows directly, so those transformations actually carry through
  • Document int_shows_clean_string_columns in _int__models.yml by hand, matching the style already used for other models in that file (a good real-world candidate for dbt Wizard - written manually for this training)
  • Review everything dependent on the podcasts source and identify misplaced or repeated logic - specifically: does the staging model convert datetime columns from strings, does stg_podcasts__listens join two sources together at the staging layer, and is a seed used anywhere in staging
  • Refactor: move the episodes + listens join out of staging into a new intermediate model that computes completion_rate per individual listen row
  • Build a mart on top of that intermediate model calculating average duration listened and average completion rate per episode
  • Bucket the averaged completion rate into no_listens / completed (>90%) / partial / dropped_before_content (<10%) - applied after the aggregation, not inside the intermediate model

From 1.3-testing.md

  • Open _ads__models.yml; list every column across stg_ads__campaigns, stg_ads__impressions, and stg_ads__spend with no data_tests at all
  • For each untested column, decide whether it needs a test and which one (not_null, unique, accepted_values, relationships) - note any column you deliberately leave untested and why
  • Add the tests you decided on to _ads__models.yml
  • Run the new tests selectively with --select before running the whole project
  • Open stg_ads__campaigns.sql; note that it selects directly from mediapulse_raw.ads.campaigns rather than source(), and write down what that costs in terms of what dbt can test and document about the raw table
  • Add a unique test on article_id in _news__models.yml (alongside the existing not_null); run it and confirm it fails
  • Query mediapulse_raw.news.articles directly (not the staged model) and investigate the duplicate article_ids, paying particular attention to updated_at
  • Write down whether this looks like random dirty data or an explainable pattern (versioned articles), and how that distinction changes what the right fix actually is
  • In prose only - no SQL yet - describe what you'd change in stg_news__articles.sql to make the unique test pass, and which row per article_id should be treated as "current"

From 1.4-jinja-and-macros.md

  • Find the safe-division case expression already inline in fct_podcasts_listens (completion_rate)
  • Write a safe_divide(numerator, denominator, precision=4) macro that returns 0 on a zero denominator and otherwise the rounded division, with a default value for precision
  • Replace the inline case in fct_podcasts_listens with a call to safe_divide; confirm the model builds and the column's values are unchanged
  • Add a +schema config for the streaming and podcasts model folders in dbt_project.yml; run the affected models and check where they actually landed - is it the schema name you configured, or something else?
  • Open mediapulse_base/macros/generate_schema_name.sql and identify its two branches (custom_schema_name not set vs. set) - compare against dbt's default generate_schema_name macro to see exactly what's being overridden
  • Extend the existing macro (don't write a new one) with a condition on the current target's name, so dev builds land in one flat schema (e.g. dbt_yourname) while prod gets the full target-schema_custom-schema naming (e.g. prod_streaming)
  • Write a normalize_score_columns macro that loops over a list of (score_col, response_col) pairs, emitting the sentinel-cleaning case statement for both columns in each pair
  • Write a weighted_score macro that loops over a list of (score_col, response_col, rescale_factor) triples, emitting the weighted-score expression for each
  • Update stg_news__articles to call normalize_score_columns instead of the ten longhand case statements
  • Update the scoring mart to call weighted_score instead of the five longhand weighting blocks
  • Run dbt compile on both models before and after the change and diff the output - confirm the compiled SQL (and total_weighted_score per article) is unchanged

2. Project health check - documentation & test coverage

Don't aim for 100% coverage on either front - that's a vanity metric, not a quality bar. Instead, confirm the following are all true before moving on:

Documentation:

  • Every model you touched today has a top-level description - including int_shows_clean_string_columns, the new completion-rate intermediate model, and the new episode-level mart
  • The new intermediate model and mart from the modeling refresh extension are documented in the appropriate _models.yml, following the existing structure and style rather than inventing a new pattern
  • safe_divide has a description of what it does and what precision controls
  • normalize_score_columns and weighted_score each have descriptions - in particular, document the expected shape of the list each macro takes (pairs vs. triples), since that's exactly the kind of thing a future caller will get wrong without it written down
  • Any column whose meaning isn't obvious from its name has a description - this especially applies to the four completion-rate buckets (no_listens / completed / partial / dropped_before_content) and the exact threshold that defines each one
  • dbt docs generate runs clean with no missing-doc warnings on anything you touched today

Test coverage:

  • The ads-domain tests from section 1 are in place and passing
  • The unique test on article_id is in place and still failing, as expected - this is intentional (the fix is deferred to a later exercise), not something you forgot to close. Call this out explicitly in your PR rather than leaving a reviewer to wonder if you missed it.
  • fct_podcasts_listens still passes its existing tests on completion_rate after the safe_divide swap
  • The new completion-rate intermediate model and mart have at least basic tests (e.g. not_null/unique on whatever defines their grain) - the exercise didn't explicitly require this, so use judgment on what's actually worth testing here
  • dbt build runs clean - zero unexpected failures - across everything you touched today. The article_id uniqueness test is the one deliberate exception; everything else should be green.

If a fresh pass over the models you touched today still shows gaps, use judgment: a gap on a business-critical column is worth fixing; a gap on a free-text or clearly cosmetic column usually isn't. Write one line in your PR description on any gap you deliberately chose to leave open and why.

3. Open a PR

Open a PR from your dpg/<your_name>/day1 branch. The description should cover, at minimum:

  • Summary - one or two sentences on what today's work accomplished overall
  • Modeling refactor - the dim_shows chain you traced, how int_shows_clean_string_columns is now documented and actually wired into dim_shows, and what you moved out of the podcasts staging layer (and where it landed instead)
  • The new completion-rate mart - briefly describe the intermediate/mart split you built and how the four buckets are defined
  • Testing coverage - what gaps you found and closed in the ads domain, and what you deliberately left untested and why
  • The article_id bug - this is the centerpiece of today's diagnostic work, so don't undersell it: explain what you found querying the raw table, why it looks like versioned articles rather than random dirty data, and what you'd change in stg_news__articles.sql to fix it. Be explicit that you have not implemented this fix - the unique test is left intentionally failing, and the actual fix belongs to a later exercise.
  • Macros - a short note on safe_divide, the generate_schema_name dev/prod branch, and normalize_score_columns/weighted_score - including how you confirmed the compiled SQL was unchanged after switching the news-scoring models over to the macros

Tag the PR for review as if a teammate picking this up tomorrow needs to understand, from the description alone, exactly what's fixed, what's only diagnosed, and what's still broken on purpose.