This hub collects patterns for specific scenarios where dbt unit tests are useful — roughly 1% of transformations where logic is complex enough that data tests alone would not catch regressions. Each note covers one pattern with worked examples and pitfalls.
Prerequisites
- dbt Unit Tests When to Write Them — Decision criteria for which models justify unit tests
- Unit Tests vs Data Tests in dbt — Why you need both and what each catches
- dbt Testing Taxonomy — The five validation mechanisms dbt provides
Core Patterns
Unit Testing Incremental Models in dbt
The dual-mode testing pattern: override is_incremental() and mock this to test both full-refresh and incremental paths. Covers merge logic and the critical insight that expect blocks show inserts, not final table state.
Unit Testing Snapshot Consumers in dbt Three strategies for testing snapshot-related logic: pre-snapshot base models, SCD2 date range calculations in downstream consumers, and change detection hashing.
Unit Testing Window Functions in dbt How to design test data that validates partitioning, ordering, and framing. Key principles: out-of-order source rows, multiple partitions, and distinctive values that make wrong results obvious.
Unit Testing CASE WHEN Boundary Logic in dbt Systematic boundary testing: exact threshold values, just-under values, null handling, and the implicit ELSE. The pattern for customer segmentation, pricing tiers, and status derivation.
Unit Testing String Extraction in dbt Edge case documentation for regex and string manipulation: simple cases, complex domains, case handling, invalid inputs, and null handling. Regression protection for fragile parsing logic.
Marketing Analytics Patterns
Unit Testing GA4 Sessionization Session boundary detection, cross-midnight sessions, microsecond timestamps, and single-event sessions. Testing the logic that drives conversion rates and session duration.
Unit Testing Attribution Models in dbt First-touch and last-touch testing with multi-session journeys, single-touch conversions, and the no-conversion exclusion pattern.
Unit Testing Conversion Funnels in dbt Step-over-step conversion rate testing with the drop-off pattern: users who exit at each step, creating traceable numbers. Includes step-skipping edge cases.
Cross-Cutting Concerns
dbt Unit Test Edge Case Patterns
Three essential patterns: null handling in aggregations, empty tables using format: sql with WHERE false, and date boundary testing (fiscal years, leap years, cross-timezone).
Organizing dbt Unit Tests at Scale Tag strategies (criticality, domain, test type), CI pipeline tiers (PR checks, merge validation, production exclusion), and file organization.
dbt Macro Testing Patterns Testing macros via ephemeral wrapper models and integration test models. The compile-and-inspect debugging workflow.
Quick Reference
| Scenario | Pattern | Key Technique |
|---|---|---|
| Incremental model | Dual-mode | is_incremental: false/true + this |
| Snapshot consumer | Three strategies | Test pre-snapshot, post-snapshot, and hashing |
| Window function | Design test data | Out-of-order rows + multiple partitions |
| CASE WHEN tiers | Boundary testing | Exact + just-under + null |
| String parsing | Edge case docs | One row per input category |
| GA4 sessions | Boundaries | Cross-midnight + inactivity threshold |
| Attribution | Journey testing | Multi-touch + single-touch + no conversion |
| Funnels | Drop-off | One user exits per step |
| Empty tables | format: sql | WHERE false with typed columns |
| Macro logic | Ephemeral wrapper | Ephemeral model calling the macro |