dbt Test Failure Fix

In this tutorial, you'll learn about dbt test failure fix. We cover key concepts, practical examples, and best practices.

You run dbt test and get FAILED 3 of 10 tests — your model has not_null, unique, or relationship test failures, or a custom data test raised an exception.

Step-by-Step Fix

1. Examine which tests failed

dbt test --model my_model --store-failures

Expected output:

10 of 10 PASS
3 of 10 FAIL
1. not_null_my_model_id
2. unique_my_model_id
3. accepted_values_my_model_status

2. Query the failed records

-- Check records with NULL id
SELECT * FROM my_model WHERE id IS NULL;

-- Check duplicate ids
SELECT id, COUNT(*) FROM my_model
GROUP BY id HAVING COUNT(*) > 1;

-- Check invalid status values
SELECT DISTINCT status FROM my_model
WHERE status NOT IN ('active', 'pending', 'completed');

3. Fix generic test definitions

# Wrong — testing a column that allows NULLs
models:
  - name: my_model
    columns:
      - name: email
        tests:
          - not_null  # but email can be NULL

# Right — only test columns that must be non-null
models:
  - name: my_model
    columns:
      - name: order_id
        tests:
          - not_null
          - unique
      - name: email
        tests:
          - not_null
          - accepted_values:
              values: ['active', 'pending', 'completed']

4. Fix custom test syntax

-- Wrong — test with incorrect return type
-- tests/assert_positive_total.sql
SELECT * FROM my_model WHERE total < 0;
-- Returns rows on failure, but test always passes

-- Right — correct dbt test pattern
-- tests/assert_positive_total.sql
SELECT * FROM my_model WHERE total < 0
-- Returns rows only when the assertion fails

5. Run tests with detailed output

dbt test --store-failures --select test_type:singular

Common Mistakes

Prevention

• Run dbt test in CI/CD pipelines for every deployment.
• Use --store-failures to persist test results for analysis.
• Create custom generic tests for business logic validation.
• Document expected test coverage in the project README.

DodaTech Tools

Doda Browser's dbt test dashboard tracks test pass/fail history and highlights regression trends. DodaZIP archives test result exports for compliance reporting. Durga Antivirus Pro monitors for test failures that could indicate data quality issues.

Common Mistakes with test failure

1. Using foldl instead of foldl' causing stack overflow on large lists
2. Forgetting deriving (Show, Eq) on custom data types needed for debugging
3. Placing the wildcard pattern first in case expressions, making all subsequent patterns unreachable

These mistakes appear frequently in real-world DBT code. DodaTech's contributors have identified these patterns through analysis of open-source projects and production systems.

Practice Exercise

Write a pure function that safely divides two integers using Maybe, then test it with edge cases like division by zero and negative numbers.

This exercise reinforces the concepts covered in this guide. Try implementing it before checking online solutions.

FAQ