dbt-expectations for data quality - Deep Dive

Overview - dbt-expectations for data quality

What is it?

dbt-expectations is a package for dbt (data build tool) that helps teams check and maintain data quality by running tests on their data. It provides ready-made tests called 'expectations' that verify if data meets certain rules, like no missing values or values within a range. This makes it easier to catch data problems early and keep data trustworthy. It works by integrating with dbt models and running checks automatically during data transformations.

Why it matters

Without dbt-expectations, data teams might miss errors or inconsistencies in their data, leading to wrong decisions or broken reports. Manually writing tests for data quality is slow and error-prone. dbt-expectations solves this by offering reusable, standardized tests that save time and improve confidence in data. This means businesses can trust their data pipelines and avoid costly mistakes caused by bad data.

Where it fits

Before using dbt-expectations, learners should understand basic dbt concepts like models, tests, and how dbt runs SQL transformations. After mastering dbt-expectations, learners can explore advanced data quality frameworks, custom test writing, and integrating data quality checks into automated deployment pipelines.

Mental Model

Core Idea

dbt-expectations is like a checklist of data rules that automatically verifies if your data is clean and reliable during transformation.

Think of it like...

Imagine you are baking a cake and have a recipe checklist to ensure you add the right ingredients in the right amounts. dbt-expectations is that checklist for your data, making sure every piece fits the recipe before you serve it.

┌───────────────────────────────┐
│        dbt-expectations       │
├───────────────┬───────────────┤
│  dbt Models   │  Expectations │
│ (SQL tables)  │ (Data rules)  │
├───────────────┴───────────────┤
│ Runs tests on data during build│
│  ↓                            │
│ Flags errors or warnings       │
└───────────────────────────────┘

Build-Up - 7 Steps

1

Foundation

Understanding dbt and Models

Concept: Learn what dbt is and how it transforms raw data into models (tables or views).

dbt is a tool that helps you write SQL queries to transform raw data into clean tables called models. These models are like steps in a recipe, each building on the last. You run dbt to create or update these models in your database.

Result

You get organized, reusable SQL models that represent your cleaned and structured data.

Understanding dbt models is essential because dbt-expectations tests run on these models to check data quality.

2

Foundation

Basics of Data Quality Testing

Concept: Learn what data quality tests are and why they matter.

Data quality tests check if your data follows rules like 'no missing values' or 'values within a range.' These tests help catch errors early, so your reports and analyses are trustworthy.

Result

You understand that testing data is like checking ingredients before cooking to avoid mistakes.

Knowing why data quality matters motivates you to use tools like dbt-expectations to automate these checks.

3

Intermediate

Installing and Using dbt-expectations

🤔Before reading on: do you think dbt-expectations requires writing all tests from scratch or provides ready-made tests? Commit to your answer.

Concept: Learn how to add dbt-expectations to your project and use its pre-built tests.

You add dbt-expectations as a package in your dbt project by updating the packages.yml file. Then you can call its tests in your model's schema.yml file using simple syntax. For example, you can test if a column has no nulls by adding an expectation test without writing SQL yourself.

Result

Your dbt runs include automatic data quality checks using standard tests from dbt-expectations.

Using pre-built expectations saves time and ensures consistent, well-tested data quality rules.

4

Intermediate

Common Expectations and Their Use

🤔Before reading on: do you think all data quality tests check for missing values only? Commit to your answer.

Concept: Explore common types of expectations like uniqueness, non-null, value ranges, and string patterns.

dbt-expectations includes tests like expect_column_values_to_not_be_null, expect_column_values_to_be_unique, expect_column_values_to_be_between, and expect_column_values_to_match_regex. You specify these in your schema.yml to check your data columns automatically.

Result

You can quickly add multiple quality checks to your data models without writing custom SQL.

Knowing the variety of expectations helps you cover many data quality aspects easily.

5

Intermediate

Customizing Expectations for Your Data

🤔Before reading on: do you think dbt-expectations tests are fixed or can be customized? Commit to your answer.

Concept: Learn how to adjust parameters of expectations to fit your specific data needs.

Many expectations accept parameters, like setting min and max values for numeric columns or regex patterns for strings. You customize these in your schema.yml to match your data's rules, making tests flexible and precise.

Result

Your data tests become tailored to your business rules, catching relevant errors.

Customizing expectations ensures tests are meaningful and avoid false alarms.

6

Advanced

Integrating dbt-expectations in CI/CD Pipelines

🤔Before reading on: do you think data quality tests run only manually or can be automated? Commit to your answer.

Concept: Learn how to automate dbt-expectations tests in continuous integration and deployment workflows.

You can configure your CI/CD tools (like GitHub Actions or Jenkins) to run dbt commands including dbt-expectations tests on every code change. This ensures data quality checks happen automatically before deploying changes to production.

Result

Data quality issues are caught early in development, preventing bad data from reaching users.

Automating tests in pipelines enforces discipline and reliability in data projects.

7

Expert

Extending dbt-expectations with Custom Tests

🤔Before reading on: do you think dbt-expectations covers all possible data tests or can you add your own? Commit to your answer.

Concept: Understand how to write your own custom tests to extend dbt-expectations for unique data quality needs.

If your data rules are unique, you can write custom SQL tests in dbt and integrate them alongside dbt-expectations. This involves creating test SQL files and referencing them in schema.yml. You can also contribute to dbt-expectations open source to add new expectations.

Result

You gain full control over data quality testing, covering all edge cases.

Knowing how to extend tests prevents limitations and adapts quality checks to evolving data.

Under the Hood

dbt-expectations works by translating expectation definitions into SQL queries that run against your database tables during dbt runs. Each expectation corresponds to a SQL test that checks if data meets the rule, returning pass or fail results. dbt collects these results and reports them, allowing you to see which data checks passed or failed. This leverages the database's processing power and dbt's testing framework.

Why designed this way?

dbt-expectations was designed to leverage dbt's existing testing infrastructure and SQL-based transformations, avoiding the need for separate tools or languages. This design makes it easy to adopt, maintain, and scale within existing dbt projects. Alternatives like custom scripts or external tools were less integrated and harder to maintain.

┌───────────────┐      ┌───────────────┐      ┌───────────────┐
│ dbt-expectations│────▶│ SQL Test Query│────▶│ Database Table│
└───────────────┘      └───────────────┘      └───────────────┘
         │                     │                      │
         ▼                     ▼                      ▼
  Expectation defs      Run SQL checks         Data stored here
         │                     │                      │
         └─────────────▶ dbt Test Runner ◀────────────┘
                           │
                           ▼
                   Test Results Report

Myth Busters - 4 Common Misconceptions

Quick: Do you think dbt-expectations replaces all manual data validation? Commit to yes or no.

Common Belief: dbt-expectations can replace all manual data validation and no other checks are needed.

Reality: dbt-expectations automates many tests but cannot replace domain knowledge or manual review for complex data issues.

Why it matters: Relying solely on automated tests may miss subtle data problems that require human insight, leading to undetected errors.

Quick: Do you think dbt-expectations slows down dbt runs significantly? Commit to yes or no.

Common Belief: Adding dbt-expectations tests will make dbt runs very slow and inefficient.

Reality: While tests add some runtime, dbt-expectations is optimized to run efficiently using SQL and database power, usually adding minimal overhead.

Why it matters: Fearing slow runs might prevent teams from adopting valuable data quality checks, risking data issues.

Quick: Do you think dbt-expectations tests can only check for null values? Commit to yes or no.

Common Belief: dbt-expectations tests only check for missing or null values in data.

Reality: dbt-expectations includes a wide range of tests like uniqueness, value ranges, regex patterns, and conditional checks beyond nulls.

Why it matters: Underestimating test variety limits how thoroughly you check data quality.

Quick: Do you think dbt-expectations is only for small datasets? Commit to yes or no.

Common Belief: dbt-expectations is only practical for small datasets because it runs many tests.

Reality: dbt-expectations leverages the database engine to run tests efficiently, scaling well even for large datasets.

Why it matters: Avoiding dbt-expectations on big data misses out on automated quality benefits at scale.

Expert Zone

1

Some expectations can be combined or stacked to create complex data quality rules, but ordering and dependencies matter to avoid false positives.

2

dbt-expectations tests run inside the database, so understanding your database's query optimization can improve test performance.

3

Contributing new expectations to the open-source dbt-expectations package requires understanding both SQL and dbt's test framework conventions.

When NOT to use

dbt-expectations is not suitable when data quality checks require complex logic that cannot be expressed in SQL or when real-time streaming data validation is needed. In such cases, specialized data validation tools or custom code outside dbt are better.

Production Patterns

In production, teams integrate dbt-expectations tests into CI/CD pipelines to block deployments on test failures. They also use test result dashboards to monitor data health over time and combine dbt-expectations with alerting systems for proactive issue detection.

Connections

Unit Testing in Software Development

dbt-expectations applies the same principle of automated tests to data as unit tests do to code.

Understanding how software tests catch bugs early helps appreciate how dbt-expectations prevents data errors before they affect users.

Data Validation in Data Engineering

dbt-expectations builds on the broader concept of data validation by providing a standardized, automated way to implement it within dbt.

Knowing general data validation practices clarifies why dbt-expectations focuses on reusable, declarative tests.

Quality Control in Manufacturing

Both dbt-expectations and manufacturing quality control use checklists and tests to ensure products meet standards before release.

Seeing data quality as a production process with checkpoints helps understand the importance of automated, repeatable tests.

Common Pitfalls

#1 Skipping tests for new or changed data models.

Wrong approach:

schema.yml without any tests:
models:
- name: customers
columns:
- name: customer_id
- name: email

Correct approach:

schema.yml with dbt-expectations tests:
models:
- name: customers
columns:
- name: customer_id
tests:
- dbt_expectations.expect_column_values_to_be_unique
- dbt_expectations.expect_column_values_to_not_be_null
- name: email
tests:
- dbt_expectations.expect_column_values_to_match_regex:
regex: '^\S+@\S+\.\S+$'

Root cause:

Not adding tests leads to missing data quality checks, allowing errors to go unnoticed.

#2 Using default expectation parameters without adjustment.

Wrong approach:

expect_column_values_to_be_between:
min_value: 0
max_value: 100

Correct approach:

expect_column_values_to_be_between:
min_value: 10
max_value: 90

Root cause:

Ignoring data specifics causes false failures or missed errors.

#3 Running dbt-expectations tests only manually and irregularly.

Wrong approach:

Running dbt test only before major releases or ad hoc.

Correct approach:

Integrating dbt test with dbt-expectations into CI/CD pipelines to run on every code change.

Root cause:

Infrequent testing delays error detection and fixes.

Key Takeaways

dbt-expectations provides a powerful, easy way to automate data quality tests within dbt projects using reusable expectations.

Automated data quality checks catch errors early, improving trust in data and reducing costly mistakes.

Customizing and extending expectations ensures tests fit your unique data and business rules.

Integrating tests into CI/CD pipelines enforces continuous data quality and prevents bad data from reaching users.

Understanding dbt-expectations' design and limitations helps you use it effectively and know when other tools are needed.