In this lesson, you will learn how to automate data quality checks by applying dbt schema tests directly to your model column definitions.
Why Schema Tests Matter
As your data pipelines grow, ensuring that your models adhere to specific business rules is critical. Instead of writing custom SQL queries for every check, dbt allows you to define "schema tests" (also known as generic tests) within your YAML configuration files. These tests act as guardians, verifying that your data remains consistent, reliable, and trustworthy every time you run your project.
Configuring Column Constraints
Schema tests are defined in your models/schema.yml (or similar) file. You apply these tests to specific columns to enforce standard constraints like uniqueness or non-null values.
For example, imagine you have a dim_customers model. You want to ensure that every customer_id is unique and that no customer record exists without an email address. You would configure it like this:
version: 2
models:
- name: dim_customers
columns:
- name: customer_id
tests:
- unique
- not_null
- name: email
tests:
- not_null
How It Works
When you execute dbt test, dbt automatically generates and runs a hidden SQL query for each of these constraints.
unique: Checks that the column contains no duplicate values. If a duplicate is found, the test fails.not_null: Checks that every row contains a value in that column. If a row is missing data, the test fails.
You can also enforce referential integrity using the relationships test. If you have a fact_orders model, you can ensure that every customer_id exists in your dim_customers table:
- name: customer_id
tests:
- relationships:
to: ref('dim_customers')
field: customer_id
By declaring these constraints in your YAML files, you create a self-documenting "contract" for your data. If the underlying data violates these rules, your dbt run will alert you immediately, allowing you to catch issues before they impact downstream dashboards or stakeholders.
Key Takeaway: Schema tests enable you to enforce data integrity constraints like uniqueness, non-nullability, and referential integrity through simple YAML configuration rather than manual SQL validation.