adding-dbt-unit-test_skill

Overview

This skill helps you add unit tests for dbt SQL models and adopt test-driven development practices. It guides creation of model-input-output unit tests, choosing fixture formats, and running tests so models only materialize if unit tests pass. Use it to reduce regressions, validate complex SQL logic, and speed safe refactors.

How this skill works

The skill inspects the target model, collects its upstream dependencies, and guides you to create a YAML unit test that supplies mocked inputs and an expected output. It recommends the appropriate fixture format (dict, csv, or sql), handles special cases like incremental or ephemeral dependencies, and shows the commands to run only unit tests or a full build gated by test results.

When to use it

• When implementing or validating complex SQL logic (regex, window functions, multi-join conditions).
• When practicing TDD: write failing unit tests first, then implement model logic.
• Before a major refactor to lock behavior and prevent regressions.
• When verifying a bug fix to ensure the issue is resolved and remains fixed.
• For high-criticality models upstream of exposures or public/contracted outputs.

Best practices

• Mock only the subset of columns used by the test to keep fixtures concise (unless using sql format which requires all columns).
• Prefer dict format by default; use csv for fixtures or sql when testing ephemeral models or unsupported types.
• Include all ref/source dependencies as inputs to avoid compilation errors.
• Run unit tests in development and CI, and exclude them from production builds to save compute.
• Sanitize any real sample data before using it in fixtures to remove PII or secrets.

Example use cases

• Add a unit test to verify count logic when joining order and order_items for edge cases (zero items).
• Create tests for date math and truncation logic to ensure consistent behavior across warehouses.
• Write a failing unit test for a reported bug, implement fix, then confirm tests pass.
• Unit test a model that depends on an ephemeral model using sql-format fixtures.
• Use unit tests in CI to block merging changes that break model business rules.