From wire
Proactive skill for creating dbt unit tests. Auto-activates when working with dbt model testing, mock inputs/outputs, or transformation validation. Covers the Model-Inputs-Outputs pattern, format selection (dict/CSV/SQL), BigQuery-specific caveats, and production deployment guidance.
npx claudepluginhub rittmananalytics/wire-plugin --plugin wireThis skill uses the workspace's default tool permissions.
This skill automatically activates when creating or discussing dbt unit tests. It guides the creation of effective unit tests using dbt's native unit testing framework (dbt Core 1.8+), ensuring that complex transformation logic is verified with mock inputs and expected outputs before deployment.
Generates design tokens/docs from CSS/Tailwind/styled-components codebases, audits visual consistency across 10 dimensions, detects AI slop in UI.
Records polished WebM UI demo videos of web apps using Playwright with cursor overlay, natural pacing, and three-phase scripting. Activates for demo, walkthrough, screen recording, or tutorial requests.
Delivers idiomatic Kotlin patterns for null safety, immutability, sealed classes, coroutines, Flows, extensions, DSL builders, and Gradle DSL. Use when writing, reviewing, refactoring, or designing Kotlin code.
This skill automatically activates when creating or discussing dbt unit tests. It guides the creation of effective unit tests using dbt's native unit testing framework (dbt Core 1.8+), ensuring that complex transformation logic is verified with mock inputs and expected outputs before deployment.
Unit tests validate transformation logic in isolation -- they confirm that given specific input rows, a model produces the expected output rows. They complement schema tests (not_null, unique, relationships) which validate data quality in production.
This skill should activate when users:
Keywords to watch for:
Activate BEFORE writing or reviewing unit tests when:
unit_tests: block in any YAML fileExample internal triggers:
dbt-development skilltesting-reference.md in the dbt-development skillUnit tests are valuable when a model contains complex transformation logic that could break silently. Write unit tests for models that include:
| Logic Type | Example | Why Unit Test? |
|---|---|---|
| Regex extraction | regexp_extract(url, r'/product/(\d+)') | Regex is error-prone and hard to validate visually |
| Date calculations | date_diff(created_at, closed_at, DAY) | Edge cases around month boundaries, nulls |
| Window functions | row_number() over (partition by customer_id order by updated_at desc) | Partitioning and ordering logic is subtle |
| Case statements | Multi-branch CASE WHEN with business rules | Business logic branches need explicit verification |
| Multi-join logic | Joining 3+ tables with conditional inclusion | Join conditions can silently drop or duplicate rows |
| Conditional aggregation | sum(case when status = 'active' then amount end) | Filter conditions within aggregations |
| Type casting with logic | safe_cast with fallback values | Edge cases in type conversion |
| Deduplication | qualify row_number() over (...) = 1 | Dedup key selection affects which row survives |
| Pivoting/unpivoting | Dynamic column generation | Column mapping is fragile |
| Currency/unit conversion | Rate application with rounding | Precision and rounding errors |
Do not write unit tests for:
coalesce(field, 'Unknown'), lower(email), trim(name) -- these are already tested by the database engineRule of thumb: If the model's SQL could be understood correctly by reading it once, it probably does not need a unit test. If you need to "think through" what the SQL does, it needs a unit test.
Every dbt unit test follows the same pattern:
MODEL (what we're testing)
+ INPUTS (mock data fed to the model's dependencies)
= EXPECTED OUTPUTS (what the model should produce)
How it works:
ref() or source() the model depends onKey concepts:
dbt supports three formats for specifying mock data. Choose based on readability and requirements:
Best for: Most cases. Readable, explicit, easy to maintain.
unit_tests:
- name: test_status_classification
model: int_orders_classified
given:
- input: ref('stg_shopify__orders')
rows:
- {order_id: 1, status: "fulfilled", total: 100.00}
- {order_id: 2, status: "pending", total: 50.00}
- {order_id: 3, status: "cancelled", total: 75.00}
expect:
rows:
- {order_id: 1, status_category: "complete", total: 100.00}
- {order_id: 2, status_category: "in_progress", total: 50.00}
- {order_id: 3, status_category: "cancelled", total: 75.00}
Advantages: Most readable, easy to diff in code review, clear column-value mapping.
Limitations: Can become verbose for many columns.
Best for: Tabular data with many columns or many rows.
unit_tests:
- name: test_revenue_calculation
model: int_orders_revenue
given:
- input: ref('stg_shopify__orders')
format: csv
rows: |
order_id,quantity,unit_price,discount_pct
1,10,25.00,0.10
2,5,50.00,0.00
3,1,100.00,0.25
expect:
format: csv
rows: |
order_id,gross_revenue,net_revenue
1,250.00,225.00
2,250.00,250.00
3,100.00,75.00
Advantages: Compact, easy to read as a table, quick to add rows.
Limitations: Harder to spot column alignment issues. String values with commas need quoting.
Best for: Complex data setup, ephemeral model dependencies, or when you need database functions.
unit_tests:
- name: test_date_spine_join
model: fct_daily_active_users
given:
- input: ref('stg_app__user_sessions')
format: sql
rows: |
SELECT 1 AS user_id, TIMESTAMP '2024-01-15 10:00:00' AS session_start
UNION ALL
SELECT 1 AS user_id, TIMESTAMP '2024-01-15 14:00:00' AS session_start
UNION ALL
SELECT 2 AS user_id, TIMESTAMP '2024-01-16 09:00:00' AS session_start
expect:
format: sql
rows: |
SELECT DATE '2024-01-15' AS activity_date, 1 AS active_users
UNION ALL
SELECT DATE '2024-01-16' AS activity_date, 1 AS active_users
Advantages: Full SQL expressiveness, can use database functions for dates/timestamps, required for mocking ephemeral model dependencies.
Limitations: Least readable, harder to maintain, database-specific syntax.
When SQL format is required:
GENERATE_DATE_ARRAY in BigQuery)Unit test definitions go in the model's schema YAML file or in a dedicated test file:
models/
staging/
shopify/
stg_shopify__orders.sql
stg_shopify__orders.yml # schema + unit tests here
integration/
int_orders_classified.sql
int_orders_classified.yml # or here
warehouse/
fct_orders.sql
unit_test_fct_orders.yml # or in dedicated file (RA convention)
unit_tests:
- name: test_<descriptive_name> # Required. Snake_case, prefix with test_
description: > # Optional but recommended
Verify that [specific behavior] produces [expected result]
when given [specific input conditions].
model: <model_name> # Required. The model being tested
config:
tags: ["unit-test"] # Optional. Useful for selective runs
given: # Required. List of mock inputs
- input: ref('<upstream_model>') # or source('schema', 'table')
rows: # Mock rows for this input
- {col1: val1, col2: val2}
- {col1: val3, col2: val4}
- input: ref('<another_model>')
rows:
- {col_a: val_a}
expect: # Required. Expected output rows
rows:
- {result_col1: expected1, result_col2: expected2}
- {result_col1: expected3, result_col2: expected4}
- name: test_<another_behavior> # Multiple tests per model
model: <model_name>
given:
- input: ref('<upstream_model>')
rows: [] # Empty input -- tests zero-row handling
expect:
rows: [] # Expect empty output
unit_tests:
- name: test_with_custom_var
model: my_model
overrides:
vars:
my_date_var: "2024-01-15" # Override project vars
macros:
my_custom_macro: "hardcoded_value" # Override macro return values
given:
- input: ref('upstream')
rows:
- {id: 1}
expect:
rows:
- {id: 1, computed_date: "2024-01-15"}
For incremental models, you can provide the "existing" state of the model using this:
unit_tests:
- name: test_incremental_merge
model: fct_orders
overrides:
macros:
is_incremental: true # Simulate incremental run
given:
- input: this # The current state of the model
rows:
- {order_id: 1, status: "pending", updated_at: "2024-01-01"}
- input: ref('stg_orders') # New incoming data
rows:
- {order_id: 1, status: "shipped", updated_at: "2024-01-15"}
- {order_id: 2, status: "pending", updated_at: "2024-01-15"}
expect:
rows:
- {order_id: 1, status: "shipped", updated_at: "2024-01-15"}
- {order_id: 2, status: "pending", updated_at: "2024-01-15"}
# Run all unit tests in the project
dbt test --select test_type:unit
# Run unit tests for a specific model
dbt test --select test_type:unit,model_name
# Run a specific unit test by name
dbt test --select test_name
# Run unit tests as part of a full build
dbt build --select +my_model # Runs model + all associated tests
# Run with verbose output for debugging
dbt test --select test_type:unit -v
--empty FlagUse --empty to validate that unit test YAML parses correctly without actually running against the database:
dbt test --select test_type:unit --empty
This is useful for CI validation of test syntax before merging.
When a unit test fails, dbt shows a diff between expected and actual output:
FAIL 1/1 test_status_classification
Failure in unit_test test_status_classification (models/integration/int_orders_classified.yml)
Got 1 result, configured to fail if != 0
actual | expected | diff
{order_id: 1, | {order_id: 1, |
status_category: "active"} | status_category: "complete"} | !=
How to read the diff:
!= indicates mismatched rowsCommon failure causes:
"100" (string) but got 100 (integer). Match types exactly.100.00 but got 100.0 or 99.999999. See BigQuery caveats below.When writing unit tests for BigQuery targets, be aware of these platform-specific behaviors:
BigQuery STRUCT types require special handling in unit tests. Use SQL format for STRUCT inputs:
unit_tests:
- name: test_struct_extraction
model: stg_ga4__events
given:
- input: source('google_analytics', 'events')
format: sql
rows: |
SELECT
'event_1' AS event_id,
STRUCT('page_view' AS value) AS event_name,
[STRUCT('page_location' AS key, STRUCT('https://example.com' AS string_value) AS value)] AS event_params
expect:
rows:
- {event_id: "event_1", event_name: "page_view", page_location: "https://example.com"}
BigQuery TIMESTAMPs have microsecond precision. When comparing timestamps in expected output, match the precision:
# Correct -- explicit precision
expect:
rows:
- {created_at: "2024-01-15 10:00:00.000000 UTC"}
# Risky -- may fail depending on how the model outputs timestamps
expect:
rows:
- {created_at: "2024-01-15 10:00:00"}
Use SQL format if timestamp precision is problematic:
expect:
format: sql
rows: |
SELECT TIMESTAMP '2024-01-15 10:00:00 UTC' AS created_at
BigQuery GEOGRAPHY columns cannot be compared directly in dict format. Use SQL format with ST_GEOGPOINT:
given:
- input: ref('stg_locations')
format: sql
rows: |
SELECT 1 AS location_id, ST_GEOGPOINT(-122.4194, 37.7749) AS geo_point
BigQuery NUMERIC (38 digits, 9 decimal) and BIGNUMERIC (76 digits, 38 decimal) may cause precision mismatches:
# Use explicit cast in SQL format for precision-sensitive comparisons
expect:
format: sql
rows: |
SELECT NUMERIC '123.456789' AS amount
BigQuery distinguishes between DATE, DATETIME, and TIMESTAMP. Ensure your mock data matches the column type:
# DATE column
rows:
- {order_date: "2024-01-15"}
# DATETIME column
rows:
- {created_datetime: "2024-01-15T10:00:00"}
# TIMESTAMP column (requires timezone)
rows:
- {created_at: "2024-01-15 10:00:00 UTC"}
For projects targeting Snowflake:
PARSE_JSON()ARRAY_CONSTRUCT()Unit tests should run in CI/CD (development and staging) but typically not in production scheduled jobs:
# Production job -- exclude unit tests
dbt build --exclude-resource-type unit_test
# CI/CD job -- include unit tests
dbt build --select state:modified+
# (unit tests run automatically when their model is selected)
Rationale: Unit tests validate logic correctness, which is a development concern. Production jobs should run schema tests (data quality) but not re-validate logic that was already verified in CI.
dbt Cloud configuration:
--exclude-resource-type unit_test to the job commandstate:modified+| Mistake | Problem | Fix |
|---|---|---|
| Testing too much in one test | One test validates 5 different behaviors | Split into separate focused tests |
| Mocking every column | Test is brittle and hard to maintain | Mock only columns relevant to the behavior under test |
| Not mocking a dependency | NULL values propagate, test fails mysteriously | Ensure every ref() and source() used by the tested code path is mocked |
| Wrong data types in mock | "100" (string) vs 100 (integer) | Match column types from the source schema |
| Testing implementation, not behavior | Test breaks when SQL is refactored | Test the output contract, not how the SQL achieves it |
| Ignoring NULL handling | Model works with mock data but fails on real NULLs | Include NULL values in mock inputs to test edge cases |
| Copy-pasting between tests | Tests drift out of sync, hard to maintain | Use YAML anchors or extract common fixtures |
| Not testing empty inputs | Model crashes on zero rows in production | Add a test with rows: [] for each input |
Forgetting format: sql for ephemeral deps | Test fails because ephemeral model has no table | Always use SQL format when mocking ephemeral model dependencies |
| Hardcoding dates that expire | Test breaks when "tomorrow" becomes "yesterday" | Use overrides.vars with a fixed reference date |
| Model Layer | Unit Test Required? | Guidance |
|---|---|---|
Staging (stg_) | Recommended if non-trivial | Only if the staging model contains regex, date logic, or case statements beyond simple rename/recast |
Integration (int_) | Required if business logic present | Multi-source joins with conditional logic, entity resolution, deduplication |
Warehouse - Dimension (_dim) | Required | SCD logic, derived attributes, business classifications |
Warehouse - Fact (_fct) | Required | Metric calculations, status derivations, temporal logic |
Use dedicated unit test files with the naming pattern:
unit_test_{model_name}.yml
Place in the same directory as the model's schema YAML:
models/
warehouse/
fct_orders.sql
fct_orders.yml # Schema tests, column descriptions
unit_test_fct_orders.yml # Unit tests (separate file)
Rationale: Separating unit tests from schema definitions keeps both files manageable and makes it easy to find all unit tests via glob patterns (unit_test_*.yml).
unit_tests:
- name: test_{model_name}__{behavior_description}
# Examples:
# test_fct_orders__cancelled_orders_excluded_from_revenue
# test_int_customers__dedup_keeps_latest_record
# test_dim_products__inactive_products_flagged
For guidance on which types of tests to write (schema tests, unit tests, data tests) and how much coverage is needed per model layer, refer to testing-reference.md in the dbt-development skill. This skill covers only the mechanics of writing unit tests.
examples/ directory for complete, runnable unit test files
unit-test-example.yml -- Simple staging model unit testunit-test-complex.yml -- Complex unit test with window functions and date logicAdapted from dbt-labs/dbt-agent-skills (Apache-2.0 License). Original skill: adding-dbt-unit-test. Modified for Rittman Analytics conventions, BigQuery focus, and Wire Framework integration.