Advanced Testing¶
Group 1 - Part 2
Generic tests cover "does this column look right." Singular tests let you assert something about your business logic that no generic test can express on its own - and dbt_utils fills in some common patterns generic tests don't cover out of the box.
Exercise¶
Step 1 - Read the logic you're about to test¶
- Step complete
Open mediapulse_base/models/intermediate/int_campaign_content_spend_allocation.sql and its documentation in _int__models.yml. Note the invariant the YAML already describes: for performance campaigns (sponsored_content, podcast_ad), zero-click content is excluded from both the numerator and the denominator, so the remaining content should still sum back up to the campaign's total spend.
Step 2 - Write a singular test for that invariant¶
- Step complete
Create a .sql file under tests/ that fails (returns rows) if, for any campaign_id, the sum of allocated_spend_cents doesn't reconcile with total_spend_cents (allow a small rounding tolerance - the allocation logic rounds to the nearest cent per row).
Hint: singular tests are just a select
A singular test is a .sql file in your tests/ directory containing a query that should return zero rows if everything is fine. Reference the model with ref() like anywhere else. See Singular data tests for the exact structure and how dbt discovers it.
Step 3 - Apply a dbt_utils generic test¶
- Step complete
dbt-labs/dbt_utils is already a dependency in mediapulse_base/packages.yml. Find one column in the project that a built-in generic test can't adequately cover, but a dbt_utils generic test can - impressions_count or clicks on stg_ads__impressions are good candidates for a bounded-range check.
Hint: dbt_utils generic tests
dbt_utils ships generic tests like accepted_range and unique_combination_of_columns that you use exactly like a built-in test, just namespaced with dbt_utils.. See the dbt_utils generic tests reference for the full list and required arguments.
Deliverable: your singular test file, plus the dbt_utils generic test you added and why you chose that column.
Extension¶
Not every failing assumption should stop a build. Test severity and scoping let you distinguish "this is broken" from "this is worth watching."
Step 1 - Find a genuinely "warn, don't fail" situation¶
- Step complete
Read mediapulse_analytics/models/marts/streamview_legacy/dim_legacy_content.sql and dim_legacy_subscribers.sql. Notice is_mapped is expected to be false for some rows - not every legacy id has been migrated to a current MediaPulse id yet. A strict not_null test on mapped_content_id would be wrong here, but you might still want to know if the proportion of unmapped rows suddenly spikes.
Step 2 - Write the monitoring test¶
- Step complete
Create a singular test in mediapulse_analytics/tests/ for dim_legacy_content (or dim_legacy_subscribers) that fails only if the proportion of is_mapped = false rows exceeds a threshold you choose - not if a single row is unmapped. Configure the test's severity as warn rather than the default error, since crossing this threshold should be visible without blocking the build.
Hint: configuring severity on a singular test
A singular test can set its own config() at the top of the .sql file, the same way a model does - that's where severity goes. See severity, error_if, and warn_if for the default behaviour and how to override it.
Step 3 - Compare to a case that should be error¶
- Step complete
Contrast the legacy-mapping case with a column elsewhere in the project where a failure genuinely should block the build (e.g. a primary key). Explain the difference in your own words - what makes one "data reality" and the other "a defect"?
Deliverable: your monitoring test file (with its warn-severity config and chosen threshold), and your error-vs-warn comparison.
Done?
You've written singular tests for business-logic invariants and configured severity deliberately, instead of treating every test failure as equally urgent.
Now head to Snapshots to track a slowly-changing dimension for the first time in this project.