Skip to main content

Unit Testing in dbt

In earlier modules, we wrote tests like not_null and unique. These are Data Tests. They run against the actual physical data sitting in your Snowflake warehouse to catch bugs in the source systems.

But what if you write a complex CASE WHEN statement to calculate a "Customer VIP Status", and you want to test if your SQL logic is actually correct? You don't want to rely on the live production data to test your logic, because production data constantly changes.

Instead, you use Unit Tests. Unit testing allows you to provide fake "mock" input data, and assert exactly what the output should be.

(Note: Unit Testing is a feature introduced in dbt v1.8. Ensure your project is up to date!)

Topics Covered​

  • Data Tests vs Unit Tests
  • Writing a Unit Test in YAML
  • Mocking Inputs
  • Running the Unit Test

1. Data Tests vs Unit Tests​

FeatureData Tests (unique, not_null)Unit Tests
What does it test?The actual data in the warehouse.The SQL logic inside your .sql file.
Does it require a live DB?Yes, it queries millions of rows.Yes, but it only processes a few mocked rows.
When to use it?To catch bad data from source systems.To catch bad SQL logic written by developers.

2. Hands-On: Writing a Unit Test​

Let's assume our Delhi-based Data Engineer, Rohan, wrote a staging model called stg_customers.sql with the following logic:

SELECT 
customer_id,
amount_spent,
CASE
WHEN amount_spent > 50000 THEN 'VIP'
ELSE 'Regular'
END as customer_status
FROM {{ ref('raw_customers') }}

Rohan wants to prove that his CASE WHEN logic works. He defines a unit test in his schema.yml file.

Step 1: Define the Test​

Under the models block, we add a unit_tests block.

# models/staging/schema.yml
unit_tests:
- name: test_vip_status_logic
description: "Ensure customers spending > 50k are marked as VIP."
model: stg_customers # The model being tested

Step 2: Mock the Inputs​

Next, Rohan must provide fake data for the upstream raw_customers table. He can define this using inline CSV format or YAML dictionaries.

    given:
- input: ref('raw_customers')
rows:
- {customer_id: 1, amount_spent: 60000} # Should be VIP
- {customer_id: 2, amount_spent: 40000} # Should be Regular
- {customer_id: 3, amount_spent: 50000} # Edge case: Should be Regular

Step 3: Assert the Expected Output​

Finally, Rohan must declare what the output of stg_customers should look like when given the mock data above.

    expect:
rows:
- {customer_id: 1, customer_status: 'VIP'}
- {customer_id: 2, customer_status: 'Regular'}
- {customer_id: 3, customer_status: 'Regular'}

3. Running the Unit Test​

To execute the unit test, you use the standard dbt test command.

dbt test --select stg_customers

What happens under the hood? dbt takes your fake input data, injects it into your stg_customers.sql code as Common Table Expressions (CTEs), executes the query in Snowflake, and compares the result to your expect block.

If they match perfectly, the test passes!

Because it only processes 3 rows of fake data, the unit test runs in milliseconds and costs practically zero compute credits.

Summary​

  • Data Tests validate the raw data. Unit Tests validate your SQL logic.
  • Unit tests are defined in YAML using the unit_tests: key.
  • You must define fake upstream data using the given: block.
  • You must assert the expected results using the expect: block.
  • Run them using the standard dbt test command.