Testing

You've seen mediapulse_base's lineage and test gaps through dbt Catalog. Now close some of them in news, and podcasts - catching and fixing any bugs.

1. Test the same field at two layers

stg_podcasts__listens.platform already has an accepted_values test (ios, android, web, alexa, other) - but that's the resolved value, joined in from the platform_mapping seed. The raw field it comes from is tested nowhere at all.

Steps to success

1. Open _podcasts__sources.yml and find the platform column on the listens source. Its description already tells you what the raw values actually look like.
2. Add an accepted_values test on it at the source level, using the raw codes from that description - not the resolved names you'd use in staging.

2. A primary key test that should fail

fct_article_scores documents article_id as the primary key but there's no not_null or unique test backing that claim up. Add both and run them.

Hint: Do any of the tests fail?

Look at what fct_article_scores.sql actually selects from. You've seen this shape of problem before, in a different model, earlier today.

3. Fix it: dedupe before scoring

fct_article_scores builds directly off stg_news__articles, which is one row per article version, not one row per article - the same raw duplication you've already seen elsewhere in this project. int_news__articles_deduped exists specifically to collapse that down to one row per article_id, but fct_article_scores never wired up to it.

Point fct_article_scores at the correct upstream model, rebuild, and confirm your new tests from step 2 now pass.

4. Add a test from dbt_utils

fct_podcast_listens.completion_rate is documented as "capped at 1.0, so a listen longer than the episode duration counts as fully completed." Add a dbt_utils.accepted_range test (min_value: 0, max_value: 1) to actually verify that claim.

Hint: dbt_utils generic tests

dbt_utils tests are configured exactly like built-in generic tests, just namespaced - dbt_utils.accepted_range instead of accepted_values. See dbt-utils: accepted_range for its arguments.

5. Find the bug your test just caught

The test from step 4 should have failed. Open fct_podcast_listens.sql and look at how completion_rate is actually calculated - the docs promise a cap at 1.0 - does the SQL apply that?

Hint: Fix the calculation

Use least(...) so a session longer than the episode is capped at exactly 1.0, rebuild, and confirm the test from step 4 now passes.

6. Loosen a test to prepare for data changes

You get a message from the PodcastHub team: "Heads up - we're planning to add new platform options to the listens data at some point, but we don't know yet what they'll be called or when they'll land."

This is different from knowing exactly what's coming. Decide the right response for the platform tests you added in exercise 1, and apply it.

Hint: known vs. unknown future values

Padding an accepted_values list (increasing the list with the known incoming values) is the right call when you know exactly what's coming.

When you genuinely don't, severity: warn gets you visibility without breaking the build the day an unannounced value shows up.

Hint: prove it actually works

Temporarily delete one real value from the list, to simulate an unannounced new one appearing, and confirm the test now warns instead of erroring. Then put the value back - you're changing the severity, not the list.

7. Scope a test with where:

dim_authors.first_published_at and most_recent_published_at are legitimately null for an author with zero articles (total_articles = 0). But once an author has articles, a null there would be worth catching.

Add a not_null test on first_published_at, scoped with a where config, so it only enforces the rule where it should be in the data.

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.

Done?

You've closed real coverage gaps at two different layers of the same field, caught and fixed two real bugs with tests you wrote yourself, and reached for severity and where instead of the first default that compiled.