Advanced Testing

You already know the four generic tests. This topic is about closing coverage gaps at scale, then thinking about the parameters generic tests support, like where, severity, and compare_model, instead of reaching for the first default that fits.

0. Create a new branch

Create a new branch called dpg/<your_name>/day1. You will commit all your changes to this branch.

1. Close the obvious gaps

1. Open Catalog's Recommendations tab for mediapulse_base and filter to Testing to identify the biggest test coverage gaps, then prioritize which to fill first. The streaming domain's staging layer doesn't even have a models.yml yet - that's a good place to start, since everything in it will show up as untested.
2. Write the missing tests yourself, model by model, choosing the generic test that actually fits what you know about the data.
3. Run dbt build and confirm the gaps you identified are actually closed.

Hint: this is where dbt Wizard would normally help

In the real world, dbt Wizard can draft this YAML fast. For this training, write it by hand - the point is learning to read a column and judge what test it actually needs. Sanity-check your own suggestions against what you know about the data before you run dbt build: a unique test on a column that isn't actually unique will just fail.

2. Get ahead of content_type changes

The owners of the streaming data have told you that content_type is getting two new values in the next release: - shorts (short-form clips under five minutes) - interactive (choose-your-own-path specials).

There is already some accepted_values tests being used on this column throughout the project. Decide the right fix and make it.

Hint: known vs. unknown future values

Adding values the list is the right call when you know exactly what's coming. Change the severity when you genuinely don't know what's coming next and want visibility without breaking the build.

3. Add a not NULL test on release_date

Open fct_streaming_events and look at where ctnt_type and release_date come from - both flow straight through from the join to content_catalog. A null release_date is expected when that join didn't find a matching content item (ctnt_type will be null too in that case) - but if ctnt_type did resolve, a null release_date means something's wrong in the catalog itself, not in the join.

Add a not_null test on release_date that only runs against the rows where it should actually hold. A plain not_null test would fail on every legitimately-unmatched event, and that's not the bug you're looking for.

Hint: generic tests take a where

Every generic test accepts a config: where: clause that filters which rows the test runs against - you don't need a bespoke singular test just to scope a not_null check. See Data test configurations for the exact syntax.

4. plan_type should never be blank

dim_subscriptions.plan_type already has an accepted_values test, but that only runs after the value has already been lowercased in the mart - an empty string or pure whitespace in the raw data could still slip through everything upstream of that. Add a dbt_utils.not_empty_string test on plan_type in the staging model instead, so it's caught at the source rather than after it's already been cleaned up.

5. int_dedupe_subscribers should actually be smaller

int_dedupe_subscribers is meant to collapse the subscriptions table down to one row per user. We can test that claim by adding a dbt_utils.fewer_rows_than test comparing int_dedupe_subscribers with the staging model it's built from. If this test ever passes with equal or more rows, the deduplication logic has silently stopped working.

6. dim_subscriptions isn't one row per user yet

Add a unique test to user_id on dim_subscriptions, alongside its existing not_null. It should fail.

Explain why this error exists and make a fix to the model.

Hint: Find out why before you fix anything

dim_subscriptions builds directly off stg_streaming__subscriptions_lifecycle_rec, and that staging model is one row per subscription event, not one row per user - a user who's upgraded plans or renewed shows up more than once.

Hint: Fixing the issue

Your team has already accounted for this. int_dedupe_subscribers exists for exactly this purpose - it's the model you wrote a row-count test against in step 5. Update dim_subscriptions to reference int_dedupe_subscribers instead of the staging model directly, then confirm your new unique test on user_id passes.

7. Prove the fct_streaming_events fan-out with a unique test

Add a unique test to event_id on fct_streaming_events, alongside its existing not_null. Run it - it will fail.

Why is event_id not unique?

stg_streaming__subscriptions_lifecycle_rec isn't one row per user, it's one row per subscription event - the same thing you just fixed on dim_subscriptions. fct_streaming_events joins on events.user_id = subscriptions.user_id with no other condition, so every watch event for that user gets duplicated once for every subscription event they've ever had.

You could use the same fix as above where int_dedupe_subscribers already collapses the subscriptions table down to one row per user: user_id.

That fix isn't free, though: with the old join, a watch event could in principle tell you which subscription was active when it happened. With int_dedupe_subscribers, every event for a user gets stamped with that user's single most recent subscription, regardless of which one was actually active at watched_at.

There are two options for this fix. Try out both and decide which one is most suitable for this use case.

Done?

You've closed real coverage gaps at scale, reached for the test parameter that actually fit instead of the first generic test that compiled, and used a unique test to prove a real fan-out bug - then fixed it with a model that already existed, rather than writing new SQL to paper over it.