Skip to content

Day 1 Homework and Wrap-Up - Advanced Testing, Macros & Mesh

This pulls together everything from 1.2-advanced-testing.md, 1.3-jinja-macros-and-custom-schema-logic.md, and 1.4-dbt-mesh.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 bug you're about to find and fix in section 1.

1. Finish the outstanding exercise steps

From 1.2-advanced-testing.md

  • Use dbt Catalog's Recommendations (Testing category) against mediapulse_base to get a prioritized coverage-gap list - start with the streaming domain's staging layer, which doesn't have a models.yml yet
  • Write the tests to fill the identified gaps yourself (don't add a unique test on a column you haven't confirmed is actually unique)
  • Run dbt build and confirm the gaps you picked are actually closed
  • Decide the right fix for the incoming content_type values (shorts, interactive) - since these are known upcoming values rather than an unknown future change, that points toward padding the accepted_values list rather than loosening severity; apply that fix everywhere content_type is tested in the project, and note in your commit message why this case called for that tactic over the alternative
  • Add a not_null test on release_date in fct_streaming_events, scoped with config: where: to only the rows where ctnt_type did resolve - a plain not_null would fail on every legitimately-unmatched event, which isn't the bug you're after
  • Add a dbt_utils.not_empty_string test on plan_type in the staging model, not just relying on the accepted_values test that runs later in the mart after lowercasing
  • Add a dbt_utils.fewer_rows_than test comparing int_dedupe_subscribers against the staging model it's built from
  • Add a unique test on user_id to dim_subscriptions (alongside the existing not_null) - confirm it fails, and write down why (stg_streaming__subscriptions_lifecycle_rec is one row per subscription event, not per user)
  • Fix dim_subscriptions by pointing it at int_dedupe_subscribers instead of the staging model directly; confirm the unique test now passes
  • Add a unique test on event_id to fct_streaming_events - confirm it fails, and write down why (the join on user_id alone, with no date condition, fans every watch event out once per subscription event that user has ever had)
  • Implement both candidate fixes for the fan-out - (a) joining against int_dedupe_subscribers for a clean one-row-per-user join, and (b) keeping the full subscription history but adding the missing date-range condition so each event matches only the subscription that was actually active at watched_at - and decide which one you're shipping
  • Write down the real trade-off between the two: option (a) is simpler and always correct on row count, but stamps every event with the user's single most recent subscription regardless of which one was active when the event happened; option (b) preserves point-in-time accuracy but is more SQL to get right. State which one this use case actually needs and why

From 1.3-jinja-macros-and-custom-schema-logic.md

  • Find the safe-division case expression already inline in fct_ad_impressions.sql (click_through_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_ad_impressions.sql 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 ads 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 compare it against dbt's default generate_schema_name macro - as it stands they are identical, so the override changes nothing until you add the dev/prod branch below
  • 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)
  • Answer the extension prompt: should mediapulse_analytics duplicate this schema-naming macro, or share it? Given that a project dependency (per 1.4-dbt-mesh.md, step 1) only exposes mediapulse_base's public models as an API and never compiles or runs its actual code, decide whether sharing this macro across the mesh boundary is even on the table, and write down what that implies for keeping schema-naming behavior consistent between the two projects

From 1.4-dbt-mesh.md

  • Read mediapulse_analytics/dependencies.yml and write down what's different about a project dependency vs. the dbt_utils package dependency in packages.yml
  • For each of the seven listed models (stg_news__articles, stg_ads__campaigns, int_campaign_content_spend_allocation, dim_campaigns, dim_content_catalog, dim_subscriptions, fct_streaming_events), determine - from mediapulse_base/dbt_project.yml's project-level defaults and any folder-level overrides - whether mediapulse_analytics can currently ref() it
  • Read the team lead's request and write down explicitly: which models is this message asking you to make a real, ongoing commitment to (dim_content_catalog, dim_subscriptions, fct_streaming_events), and which is it explicitly telling you not to worry about yet (the ads/news staging models)
  • Add a folder-level default on streaming/ in dbt_project.yml to make the requested models public
  • Note (don't just assume) that this config change alone doesn't unblock the analytics team - cross-project ref() resolves against mediapulse_base's last successful production run, so this needs to be merged and built in prod before their dbt build will see it
  • Add contract: {enforced: true} under config for dim_content_catalog, dim_subscriptions, and fct_streaming_events; fill in every column's data_type, verified against actual warehouse output, not guessed
  • Confirm all three are materialized as table or view, then run dbt run --select dim_content_catalog dim_subscriptions fct_streaming_events and resolve anything the contract check flags
  • Decide whether the three staging models (stg_streaming__content_ctlg, stg_streaming__subscriptions_lifecycle_rec, stg_streaming__usr_watch_events_log) - also access: public today - should get a contract too, and write down your reasoning against what the team lead's request actually asked for vs. what a staging contract would cost you on the next upstream source change
  • Extension: add the v1/v2 versioning block to fct_streaming_events, with v1 keeping monthly_fee_cents and v2 replacing it with monthly_fee_dollars (the same / 100.0 conversion dim_subscriptions already does)
  • Set latest_version: 1 explicitly, create fct_streaming_events_v2.sql with the real conversion logic, and leave the existing file as v1
  • Set a realistic deprecation_date on v1 (roughly a quarter out)
  • Confirm dbt run --select fct_streaming_events builds both versions, and dbt run --select fct_streaming_events,version:latest builds only v1

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 (across testing, macros, and mesh) has a top-level description
  • dim_content_catalog, dim_subscriptions, and fct_streaming_events each document their contract in plain language somewhere visible - a future maintainer shouldn't have to reverse-engineer why a column can't just be renamed
  • The safe_divide macro has a description of what it does and what precision controls - macros without docs are exactly the kind of thing a new team member re-implements by accident six months from now
  • Any column whose meaning isn't obvious from its name has a description - this especially applies to monthly_fee_cents vs. monthly_fee_dollars now that both are in play across versions
  • dbt docs generate runs clean with no missing-doc warnings on anything you touched today

Test coverage:

  • Every primary key has not_null + unique - including the two you just fixed (user_id on dim_subscriptions, event_id on fct_streaming_events)
  • The content_type fix from section 1 is applied consistently everywhere the column is tested, not just in one model
  • The scoped release_date test and the staging-layer plan_type test are both in place and passing
  • The fewer_rows_than dedup test on int_dedupe_subscribers is in place and passing
  • dbt build runs clean - zero failing tests - across everything you touched today, including both fct_streaming_events versions

If a fresh look at Catalog's coverage report still shows gaps after this, use judgment: a gap on a business-critical column (foreign keys, anything feeding the contract) 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
  • Testing coverage - what gaps existed, what you closed, and what (if anything) you deliberately left open and why
  • content_type test parameters - why padding the accepted list was the right call here, specifically because the two new values were already known, not a hypothetical future change
  • The fan-out bug - this is the centerpiece of today's work, so don't undersell it: explain what dim_subscriptions and fct_streaming_events were actually doing wrong, how the unique tests proved it, which of the two fixes you shipped for the fct_streaming_events join, and why (point-in-time accuracy vs. simplicity)
  • Macros - a short note on safe_divide and the generate_schema_name dev/prod branch, plus your answer to the sharing-vs-duplicating extension question
  • dbt Mesh (access + contracts) - confirm which models are now public, why the staging layer was or wasn't also contracted, and a reminder that this needs a production run before the analytics team can actually see it
  • Versioning - flag the breaking change explicitly: monthly_fee_cents is being phased out, v2 is available now with monthly_fee_dollars, and state the deprecation date so a reviewer standing in for the analytics team can't miss it

Tag the PR for review as if the mediapulse_analytics team lead from 1.4-dbt-mesh.md were genuinely about to depend on this - the description should give them everything they'd need without having to ask you directly.