In this lesson, you will learn how to implement schema tests in dbt to automatically catch data quality issues like missing IDs or duplicate records before they propagate through your pipeline.
Why Test Your Data?
In a data pipeline, "correctness" is just as important as the transformation logic itself. You might write perfect SQL code, but if your source data arrives with unexpected null values in a primary key or duplicate rows in a dimension table, your final business metrics will be wrong. Instead of manually querying your tables to check for these errors every time you run a transformation, dbt allows you to codify these quality requirements directly into your project configuration.
Defining Schema Tests
Schema tests are assertions you define in your schema.yml file. These tests are essentially SQL queries that check for specific conditions. If the query returns any rows, the test fails, signaling that your data integrity has been compromised.
The two most common tests you will use are unique and not_null.
Example Configuration
Suppose you have a model for your customers. You can define tests for the customer_id column to ensure it is always present and never duplicated:
models:
- name: stg_customers
columns:
- name: customer_id
tests:
- unique
- not_null
When you execute dbt test, dbt runs a hidden query behind the scenes. For the unique test, it runs:
SELECT customer_id, count(*)
FROM stg_customers
GROUP BY customer_id
HAVING count(*) > 1
If this query returns a record, dbt knows your data is not unique. By defining these tests, you shift your mindset from "hoping the data is clean" to "verifying the data is clean." This allows you to catch bad data immediately after your dbt run step, preventing downstream reports from consuming inaccurate information. These tests become a permanent part of your project’s documentation and quality assurance layer.
Key Takeaway: Schema tests enable automated data validation by treating quality assertions as code, ensuring that only clean, reliable data reaches your business stakeholders.