Skip to content

Unit Testing

Unit tests are not the same thing as the data tests. A data test runs against real materialized data and asks "is something wrong right now?" A unit test runs against small, hand-written mock rows and asks "does this SQL logic do what I think it does?" - before you ever touch the warehouse. dbt has supported this natively since v1.8, with a given/expect structure defined in YAML.

Exercise

mediapulse_analytics's int_adv_rep_touchpoint_cumcounts is exactly the kind of model dbt's own documentation says to unit test. It classifies each touchpoint's free-text notes as "real" or not using a chain of regexp_like rules with fiddly boundary conditions (a length threshold, a punctuation-only check, a list of placeholder tokens), and then builds cumulative window counts on top of that classification. Regex boundaries and running totals are both very easy to get subtly wrong, and almost impossible to eyeball against real warehouse data.

Step 1 - Read the logic you're about to test

  • Step complete

Open int_adv_rep_touchpoint_cumcounts.sql. Note the has_real_text case statement and its ordered rules: notes is null → false, empty after trim → false, pure punctuation → false, a known placeholder token (n/a, none, null, nil, test, tbd, todo, pending, repeated x, bare n/y) → false, fewer than 3 characters → false, otherwise true. Then note the alltime window counts (which never reset) versus the ytd counts (which add touchpoint_year to the partition so they reset every calendar year), and that both are inclusive of the current row.

Step 2 - Add a unit_tests: block for the classification logic

  • Step complete

In mediapulse_analytics/models/intermediate/_intermediate__models.yml, add a top-level unit_tests: key (a sibling of models:, not nested inside it) containing at least three named unit tests against int_adv_rep_touchpoint_cumcounts, covering the has_real_text boundaries:

  • a comment that lands exactly on the 3-character threshold (2 characters should be false, 3 should be true)
  • a placeholder token that must classify as false (e.g. n/a or pending)
  • a notes value that is only punctuation (e.g. ...)

Each test needs a given (mock rows for the stg_crm__touchpoints input the model actually ref()s) and an expect (the touchpoint_id and has_real_text you expect back).

Hint: given/expect structure

Each given entry ties mock rows to a specific input: ref(...) or input: source(...) that the model under test actually references, with rows supplied as dict, csv, or sql. expect is the output row(s) your model should produce from those inputs. You only need to include the columns you want to assert on in your expect rows - dbt compares just those. See Unit tests for the full structure.

Step 3 - Add a case for the cumulative window logic and the YTD reset

  • Step complete

Add one more unit test that feeds several touchpoints for the same advertiser + rep pair, spanning two calendar years, and asserts the running counts. Confirm that the _alltime counts keep accumulating across the year boundary while the _ytd counts reset to 1 on the first touchpoint of the new year, and that running_with_comments_* + running_no_real_text_* = running_total_touchpoints_* on every row.

Hint: ordering matters

The window functions order by occurred_at, touchpoint_id. Give your mock rows distinct timestamps so the expected running counts are unambiguous, and remember every count is inclusive of the current row.

Step 4 - Run your unit tests

  • Step complete

Run just your new unit tests (not the whole suite) and confirm they all pass. If one fails, work out whether your mock rows or your expected output was wrong.

Hint: selecting only unit tests

You can select by test type and model, e.g. dbt test --select int_adv_rep_touchpoint_cumcounts,test_type:unit. See Unit tests for the selection syntax.

Deliverable: the unit_tests: YAML block with your test cases (at least three classification boundaries plus the cumulative/YTD case), and confirmation (a screenshot or pasted CLI output) that they all pass.

Extension

Unit testing isn't limited to this one model, and it isn't free of trade-offs - both are worth exploring.

Step 1 - Apply it to a second model

  • Step complete

Pick either fct_advertiser_contracts's contract_length_days calculation or fct_ad_revenue's revenue-attribution logic (both in mediapulse_analytics). Write a real unit_tests: block against it, focused specifically on the edge case (a same-day contract where start_date = end_date, or a period with zero attributable revenue).

Step 2 - Check the constraint that matters most for this mesh

  • Step complete

Before you assume you can unit test anything: current dbt documentation lists specific model types and situations unit tests do not support - including models that live in, or are referenced from, another project. Given that mediapulse_analytics consumes public models from mediapulse_base via the mesh dependency, work out which of your project's models a unit test genuinely cannot cover, and why that limitation exists.

Hint: check the supported/unsupported list directly

Don't rely on memory or a summary for this - the exact list of supported and unsupported model types changes between dbt versions. Confirm against the current Unit tests page before you commit to an answer.

Step 3 - Decide where unit tests belong in your pipeline

  • Step complete

dbt Labs has a specific recommendation about running unit tests in production versus development/CI. State that recommendation, explain the reasoning behind it in terms of what a unit test's inputs actually are, and describe how you'd configure your CI/CD pipeline (conceptually - refer back to what you built in Deployment & CI/CD) to respect it.

Deliverable: the second model's unit_tests: YAML with a passing run, the specific mesh-related limitation you identified in step 2, and your CI/CD placement decision with reasoning.

Done?

You've written real unit tests against mocked inputs for two different models, and you know exactly where dbt Mesh boundaries stop this technique from working.

That's Group 3 complete - well done. You've reviewed Catalog, audited coverage, designed CI/CD, and gone deep on dbt Mesh, testing, masking, and unit tests across both projects.