dbt Test Types Explained: Schema vs Data Tests

⚡ dbt tests in 90 seconds—Schema vs Data tests!

⚡ Short

Two Types of Tests in dbt

1. Schema Tests (Built-In)

Define in YAML, dbt generates SQL

models:
  - name: users
    columns:
      - name: user_id
        tests:
          - unique
          - not_null
      - name: email
        tests:
          - unique
          - not_null
      - name: country_code
        tests:
          - accepted_values:
              values: ['US', 'CA', 'UK', 'DE']

4 built-in tests:

• unique - No duplicates
• not_null - No NULL values
• accepted_values - Value must be in list
• relationships - Foreign key check

2. Data Tests (Custom SQL)

Write SQL to test business logic

-- tests/assert_revenue_positive.sql
SELECT
  order_id,
  total_amount
FROM orders
WHERE total_amount < 0

If query returns rows = test fails!

When to Use Each

Schema Tests → Column-level checks

• Data types correct?
• No unexpected NULLs?
• Values in expected range?

Data Tests → Business logic checks

• Revenue calculations correct?
• Dates make sense? (order_date < ship_date)
• Cross-table consistency?

Common Gotcha 🚨

DON’T DO THIS:

tests:
  - unique  # ❌ Runs on EVERY column

DO THIS:

columns:
  - name: user_id
    tests:
      - unique  # ✅ Runs on specific column

Pro Tips

1. Start simple: unique + not_null on primary keys
2. Add gradually: Don’t test everything day 1
3. Test in CI/CD: dbt test before merging
4. Use severity: warn vs error for different impact levels

tests:
  - unique:
      severity: error  # Fails build
  - custom_check:
      severity: warn   # Logs warning

Quick Reference

Need	Use
Column uniqueness	Schema test: unique
Business rule	Data test: custom SQL
Foreign key	Schema test: relationships
Complex validation	Data test: custom SQL

That’s dbt testing! Run dbt test and ship with confidence. ✅

#dbt #DataQuality #DataEngineering #Analytics