Testing Coverage

If you haven't already create a new branch called dpg/<your_name>/day1. For each exercise, commit your changes with a small explanation (using imperative tense) as a commit message.

eg. "Add relationships tests for touchpoints and contracts FKs"

1. Test coverage

1. Use dbt Catalog's Recommendations tab, filtered to Testing, to identify the biggest test coverage gaps and get a list of priorities on how to fill those gaps.
2. Write the tests yourself to fill those gaps.
3. Run dbt build and check those test gaps are closed.

This is normally dbt Wizard's job

In the real world, dbt Wizard can draft this YAML fast once you've identified the gaps. For this training, write it by hand.

2. Test parameters

Look at the Customer Relationship Management (CRM) data. There are currently four accepted_values tests. Your CRM systems are very volatile, and you are aware that changes are often seen when the systems gets a new update.

Identify which of these accepted values is most at risk of breaking the full pipeline and make a change that will allow for these kinds of changes. Look in the README.md to learn more about the CRM systems so that you can decide on the correct strategy.

Hint: What kind of changes should I implement?

You will need to decide which tactic you use for each accepted_values test. It will include (but may not be limited to) - Add more values into the list of values on the test, to account for incoming new values - Change the SQL code in the staging model to account for inconsistencies - Add a severity parameter to the test to allow for small changes/updates

3. Adding a test with modelling

Write a test to ensure that every member has been charged an applicable amount, according to the fee table below. - Note that the prices have never changed, so if someone is on the basic plan, the price should be fixed at €599,00 - Note that staff members get a discount of 12.5% - There is a seed file staff_members.csv which you can use to determine which users are staff members

The normal rates are as follows:

| Plan type | Price        |
|-----------|--------------|
| Basic     |   599        |
| Premium   |    1499      |
| Standard  |    999       |

Steps:

1. dim_subscriptions in mediapulse_base already has plan_type and monthly_fee_dollars calculated for you - but its marts are access: protected, so mediapulse_analytics can't ref() it directly. The staging layer is access: public, so build off stg_streaming__subscriptions_lifecycle_rec instead, using a cross-project ref().
2. Bring in the staff_members seed to work out which user_ids should get the staff discount.
3. The fee table above never changes, so hardcode it (a case statement or a small CTE) rather than adding a seed for three static prices.
4. Write a singular test that computes the fee you'd expect each member to be charged - full price for everyone else, discounted for staff - and returns any row where the fee actually charged doesn't match. Run it and see what it finds.

Hint: rounding

"12.5% off" is ambiguous once you're working in whole cents/euros. Decide deliberately whether you round, floor, or ceiling the discount, and apply it consistently - otherwise you'll get false positives on rows that are actually correct to the cent.

4. Update the documentation

Considering what you saw in the README.md in part 2, update the documentation in the _crm__sources.yml file to carry this documentation through into dbt.

Steps:

1. Re-read the "Known Caveats and upcoming changes in source" note in mediapulse_analytics/README.md, and match each caveat to the specific source table and column it affects.
2. For each affected column in _crm__sources.yml, extend the description: so it captures what a future reader can't tell from the column name alone: the unit contract_value_cents is actually stored in, the fact that all the timestamp fields are in local server time rather than UTC, and the upcoming value/casing changes on account_status, renewal_status, contract_tier, and touchpoint_type.
3. Write these updates yourself, then do your own line-by-line pass against the README afterward - this is exactly the kind of drafting dbt Wizard would normally do for you, but for this training you're writing it by hand and checking it yourself, to make sure you haven't missed (or invented) a caveat.
4. Decide whether any of this documentation also belongs on the stg_crm__* models themselves (in _crm__models.yml), not just on the source - that's what most of your teammates will actually read day-to-day.

Hint: documentation lives with the reader, not just the source

A source's description isn't automatically inherited by the staging models built on top of it. If a caveat matters to someone querying stg_crm__advertiser_accounts directly, consider documenting it there too, not only on the raw source table.

5. Overwrite a built-in test

In some bits of the data, "N/A" shows up where it really means NULL - but it's stored as the literal string, not an actual null value. Right now, running not_null against a column like this passes even though the data is functionally missing, because "N/A" is a non-null string as far as SQL is concerned.

Goal: override dbt's built-in not_null test so it can optionally treat specific string values as if they were null, without losing any of its normal behavior.

The overridden test should:

• Behave exactly like the standard not_null test when called with no extra arguments
• Accept an optional null_values parameter - a list of strings (e.g. ["N/A", "n/a", "-"]) - that should be treated as null for that specific test configuration
• Only apply this extra leniency where null_values is explicitly passed, so every other not_null test in the project keeps working exactly as it does today

Hint: Find out how dbt resolves not_null today

Before you can override a built-in test, you need to know where it's actually defined and how dbt decides which version of a test to run when more than one definition with the same name exists in scope.

dbt's built-in tests aren't special-cased - they're macros named test_<test_name>, resolved through the same dispatch mechanism as any other macro. See Writing custom generic tests for how a project-level macro with the same name takes precedence over the global one.

Hint: Write the overriding macro

Create a macro in your project that overrides not_null. It needs to accept the same arguments the built-in version does, plus your new null_values argument, and give null_values a sensible default so existing calls to not_null elsewhere in the project don't break.

See Jinja and macros for how macro arguments and default values are declared - this is what lets null_values be optional.

Hint: what the original test actually checks

Before you can extend the logic, you need to know exactly what condition the built-in not_null test asserts failure on. The Add tests to your DAG page explains how generic tests are structured as a select that returns failing rows - your override needs to return failing rows using the same shape, just with an extended definition of "null."

Hint: Apply it with null_values

Pick a column in the project that has this "N/A"-instead-of-null problem, and configure a not_null test on it that passes in your new null_values argument.

Generic test arguments are set under a column's data_tests: key in the model's YAML. See Add tests to your DAG for the syntax for passing extra configuration into a test.

Hint: Confirm both behaviors

Run the test you just configured and confirm it correctly treats "N/A" as missing. Then find (or temporarily add) a second not_null test elsewhere in the project that does not pass null_values, and confirm it still behaves exactly as before - i.e. a genuine empty string or other non-null junk value still passes, since only actual NULLs and your specified null_values should fail the test.

You don't need to run the whole project to check this. dbt test accepts a --select flag targeting a specific model or test, so you can check both cases quickly. See Node selection syntax.

6. Tag your tests, and store what breaks

Not every test matters equally, and a bare pass/fail count doesn't always tell you enough to debug a failure.

Steps:

1. Add a tags config (e.g. important) to a handful of your most critical tests - the ones guarding a primary key, or that should never be allowed to fail quietly - without tagging everything.
2. Run dbt build --select tag:important (or dbt test --select tag:important) and confirm only those tagged tests run.
3. Look at fct_streaming_events and pick one of the enriched fields that comes in through the join to the content catalog - ctnt_type, release_date, or runtime_minutes - that can come back null when the join doesn't find a match. Add a not_null test on it, and configure store_failures: true on that test.
4. Run it, then query the failures table dbt just created. What do the failing rows have in common?

Hint: tags aren't just labels

Tags slice a test run the same way --select does for models: dbt build --select tag:important only runs resources tagged important, wherever they live in the project. You can set a tags config at the column-test level, the model level, or in dbt_project.yml for a wide sweep - keep the set small enough that "important" still means something.

Hint: where the failures actually land

store_failures: true writes every failing row to a real table in your target schema instead of discarding them once the test finishes. That's the difference between "12 rows failed" and being able to see whether those 12 rows share a content_id, a date range, or something else worth chasing down.

Done?

You've closed real coverage gaps at scale, by:

• matching the right tactic to each accepted_values test based on how the CRM source is actually changing
• writing a test that checks real business logic rather than a single column in isolation
• carrying a vendor's caveats through into the project's own documentation
• overriding a built-in generic test without breaking its existing callers
• setting up tagging and store_failures so the team can run what matters most and actually see what a failure looked like