dbt-expectations for data quality - Step-by-Step Execution

Concept Flow - dbt-expectations for data quality

Write dbt model SQL

↓

Add dbt-expectations tests

↓

Run dbt test command

↓

dbt-expectations runs tests

↓

Tests pass?

No→Report errors

Yes↓

Data quality confirmed

This flow shows how you add dbt-expectations tests to your dbt models, run tests, and check if data quality passes or errors are reported.

Execution Sample

dbt

select * from {{ ref('my_table') }}

-- Add test for no nulls in id
{{ expect_column_values_to_not_be_null('id') }}

-- Add test for unique ids
{{ expect_column_values_to_be_unique('id') }}

This code selects data from a table and adds two dbt-expectations tests: no nulls in 'id' column and unique values in 'id'.

Execution Table

Step	Action	Test Run	Result	Notes
1	Run dbt test	expect_column_values_to_not_be_null('id')	Pass	No null values found in 'id' column
2	Run dbt test	expect_column_values_to_be_unique('id')	Fail	Duplicate values found in 'id' column
3	Report errors	Summary	Fail	Data quality test failed due to duplicates
4	Fix data or model	N/A	N/A	User updates data or model to fix duplicates
5	Re-run dbt test	All tests	Pass	All data quality tests pass

💡 Tests stop when all pass or errors are reported for fixing

Variable Tracker

Variable	Start	After Step 1	After Step 2	After Step 5
id column null check	Not run	Pass	Pass	Pass
id column uniqueness	Not run	Not run	Fail	Pass
data quality status	Unknown	Unknown	Fail	Pass

Key Moments - 2 Insights

Why does the uniqueness test fail even if the null check passes?

Because null values and duplicates are different issues. The null check (row 1) passes since no nulls exist, but duplicates cause the uniqueness test (row 2) to fail.

What happens after a test fails?

The failure is reported (row 3), and you need to fix the data or model before re-running tests (rows 4 and 5).

Visual Quiz - 3 Questions

Look at the execution table, what is the result of the null check test at step 1?

A Not run

B Fail

C Pass

D Error

💡 Hint

Check the 'Result' column in row 1 of the execution table.

At which step does the uniqueness test fail?

A Step 1

B Step 2

C Step 3

D Step 5

💡 Hint

Look at the 'Test Run' and 'Result' columns in the execution table rows.

If the duplicates were fixed before step 5, what would be the data quality status after step 5?

A Pass

B Fail

C Unknown

D Error

💡 Hint

Refer to the 'data quality status' variable in the variable tracker after step 5.

Concept Snapshot

dbt-expectations adds data quality tests to dbt models.
Write tests like expect_column_values_to_not_be_null or expect_column_values_to_be_unique.
Run tests with 'dbt test' command.
Tests pass or fail based on data.
Fix data if tests fail and re-run.
Ensures reliable, clean data in your warehouse.

Full Transcript

This visual execution shows how dbt-expectations helps check data quality in dbt projects. First, you write your dbt model SQL. Then, you add dbt-expectations tests to check things like no nulls or unique values in columns. When you run 'dbt test', these tests run on your data. If tests pass, data quality is confirmed. If tests fail, errors are reported so you can fix data or models. After fixing, you re-run tests until all pass. The execution table traces each test step, showing pass or fail results. The variable tracker shows how test results change over time. Key moments clarify why some tests fail while others pass and what to do next. The quiz checks your understanding of test results and flow. This process helps keep your data clean and trustworthy.

Practice

1. What is the main purpose of dbt-expectations in data projects?

easy

A. To create visual dashboards for data

B. To load data into the database

C. To write tests that check data quality in your models

D. To write SQL queries faster

Solution

1. Step 1: Understand dbt-expectations role

   dbt-expectations is a package that helps add tests to check data quality.

2. Step 2: Identify main function

   Its main function is to write simple tests like checking for nulls or uniqueness in data models.

3. Final Answer:

   To write tests that check data quality in your models -> Option C

4. Quick Check:

   dbt-expectations = data quality tests [OK]

Hint: Remember: dbt-expectations is for testing data quality [OK]

Common Mistakes:

• Confusing dbt-expectations with visualization tools
• Thinking it loads or transforms data
• Assuming it speeds up SQL writing

2. Which of the following is the correct way to add a null check test using dbt-expectations in a schema.yml file?

easy

A. tests: - expect_column_values_to_be_null

B. tests: - dbt_expectations.expect_column_values_to_not_be_null

C. tests: - dbt-expectations.expect_column_values_to_not_be_null

D. tests: - expect_column_values_to_not_be_null

Solution

1. Step 1: Recall dbt-expectations test syntax

   In schema.yml, tests are listed by their function name without the package prefix.

2. Step 2: Identify correct test name

   The correct test for checking no nulls is expect_column_values_to_not_be_null.

3. Final Answer:

   tests: - expect_column_values_to_not_be_null -> Option D

4. Quick Check:

   Test names exclude package prefix [OK]

Hint: Use test function names without package prefix in schema.yml [OK]

Common Mistakes:

• Including package name in test list
• Using wrong test name like 'expect_column_values_to_be_null'
• Using hyphens instead of underscores

3. Given this snippet in a schema.yml file:

tests:
  - expect_column_values_to_be_between:
      min_value: 10
      max_value: 100

What does this test check for the column?

medium

A. Values are between 10 and 100 inclusive

B. Values are unique

C. Values are not null

D. All values are exactly 10 or 100

Solution

1. Step 1: Understand the test name

   The test expect_column_values_to_be_between checks if values fall within a range.

2. Step 2: Interpret parameters

   Parameters min_value: 10 and max_value: 100 mean values must be between 10 and 100, including these limits.

3. Final Answer:

   Values are between 10 and 100 inclusive -> Option A

4. Quick Check:

   Between test checks range limits [OK]

Hint: Between test checks inclusive range by default [OK]

Common Mistakes:

• Thinking values must be exactly min or max
• Confusing with null or uniqueness tests
• Assuming exclusive range instead of inclusive

4. You wrote this test in your schema.yml:

tests:
  - expect_column_values_to_be_unique_or_nulls

But dbt throws an error when running tests. What is the likely cause?

medium

A. The test name is incorrect; it should be expect_column_values_to_be_unique_or_nulls

B. The test name is incorrect; it should be expect_column_values_to_be_unique_or_null

C. The test requires parameters min_value and max_value

D. The test cannot be used in schema.yml files

Solution

1. Step 1: Check test name spelling

   The correct test name is expect_column_values_to_be_unique_or_null without the trailing 's'.

2. Step 2: Understand test usage

   This test does not require parameters and is valid in schema.yml files.

3. Final Answer:

   The test name is incorrect; it should be expect_column_values_to_be_unique_or_null -> Option B

4. Quick Check:

   Exact test names matter for dbt-expectations [OK]

Hint: Check exact test function names for typos [OK]

Common Mistakes:

• Adding extra characters to test names
• Assuming parameters are needed for all tests
• Thinking some tests can't be in schema.yml

5. You want to ensure a column email in your users model has no nulls and all values match a simple email pattern using dbt-expectations. Which test setup in schema.yml is correct?

hard

A. tests:
- expect_column_values_to_not_be_null
- expect_column_values_to_match_regex:
regex: '^[\w.-]+@[\w.-]+\.\w+$'

B. tests:
- expect_column_values_to_be_null
- expect_column_values_to_match_regex:
regex: '^[\w.-]+@[\w.-]+\.\w+$'

C. tests:
- expect_column_values_to_not_be_null
- expect_column_values_to_be_unique

D. tests:
- expect_column_values_to_not_be_null
- expect_column_values_to_match_regex:
regex: '[e-mail]'

Solution

1. Step 1: Identify null check test

   Use expect_column_values_to_not_be_null to ensure no null emails.

2. Step 2: Identify regex pattern test

   Use expect_column_values_to_match_regex with a regex matching email format.

3. Step 3: Evaluate options

   tests: - expect_column_values_to_not_be_null - expect_column_values_to_match_regex: regex: '^[\w.-]+@[\w.-]+\.\w+$' correctly combines both tests with a valid regex pattern.

4. Final Answer:

   tests: - expect_column_values_to_not_be_null - expect_column_values_to_match_regex: regex: '^[\w.-]+@[\w.-]+\.\w+$' -> Option A

5. Quick Check:

   Null check + regex pattern = correct test setup [OK]

Hint: Combine null check and regex for pattern validation [OK]

Common Mistakes:

• Using expect_column_values_to_be_null instead of not null
• Using a fixed string instead of regex pattern
• Omitting the regex test for pattern check