Why Built-in tests (unique, not_null, accepted_values, relationships) in dbt? - Purpose & Use Cases

The Big Idea

What if a simple test could save you hours of painful data cleanup?

The Scenario

Imagine you have a huge spreadsheet with thousands of rows of sales data. You want to make sure every order ID is unique, no customer ID is missing, and all product categories are valid. Doing this by scanning rows one by one or writing complex manual checks is exhausting and easy to mess up.

The Problem

Manually checking data quality is slow and error-prone. You might miss duplicates or forget to check some columns. It's hard to keep track of all rules, and fixing errors after analysis wastes time and causes wrong decisions.

The Solution

Built-in tests in dbt let you quickly and reliably check your data with simple commands. They automatically verify uniqueness, missing values, allowed values, and relationships between tables. This saves time, reduces mistakes, and keeps your data trustworthy.

Before vs After

✗ Before

SELECT order_id, COUNT(*) FROM sales GROUP BY order_id HAVING COUNT(*) > 1;

✓ After

tests:
  - unique:
      column_name: order_id

What It Enables

With built-in tests, you can confidently trust your data and focus on insights instead of hunting errors.

Real Life Example

A retail company uses dbt built-in tests to ensure every transaction has a unique ID, no customer info is missing, and product categories match the approved list before running sales reports.

Key Takeaways

Manual data checks are slow and risky.

Built-in tests automate common data validations.

This leads to faster, more reliable data analysis.