dbt-utils package tests - Mini Project: Build & Apply

Using dbt-utils Package Tests

📖 Scenario: You are working on a data analytics project using dbt (data build tool). You want to ensure your data models are accurate and reliable by using tests from the dbt-utils package.This project will guide you through setting up and using some common dbt-utils tests to validate your data.

🎯 Goal: Learn how to configure and apply dbt-utils package tests such as unique_combination_of_columns and not_null on your dbt models to ensure data quality.

📋 What You'll Learn

Have a dbt project with a model named orders

Use the dbt-utils package in your packages.yml

Write tests in the schema.yml file using dbt-utils macros

💡 Why This Matters

🌍 Real World

Data analysts and engineers use dbt-utils tests to ensure data quality and catch errors early in data pipelines.

💼 Career

Knowing how to apply dbt-utils tests is valuable for roles involving data modeling, data engineering, and analytics engineering.

1

Set up the orders model data

Create a simple orders model SQL file with columns order_id, customer_id, and order_date. Use the exact SQL below in models/orders.sql:

select 1 as order_id, 101 as customer_id, '2024-01-01'::date as order_date
union all
select 2, 102, '2024-01-02'::date
union all
select 3, 101, '2024-01-03'::date

dbt

# Create the orders model SQL file with the exact select statements
# Your code here

Hint

Write a SQL select statement with three rows and the exact columns order_id, customer_id, and order_date.

2

Configure dbt-utils package in packages.yml

Add the dbt-utils package version 0.9.6 to your packages.yml file exactly as shown below:

packages:
  - package: dbt-labs/dbt_utils
    version: 0.9.6

dbt

select 1 as order_id, 101 as customer_id, '2024-01-01'::date as order_date
union all
select 2, 102, '2024-01-02'::date
union all
select 3, 101, '2024-01-03'::date

# Add dbt-utils package to packages.yml
# Your code here

Hint

Open packages.yml and add the dbt-utils package with version 0.9.6.

3

Add dbt-utils tests in schema.yml

In your schema.yml file, write tests for the orders model using dbt-utils macros. Add a unique_combination_of_columns test on order_id and a not_null test on customer_id exactly as shown:

models:
  - name: orders
    tests:
      - dbt_utils.unique_combination_of_columns:
          combination_of_columns:
            - order_id
      - dbt_utils.not_null:
          column_name: customer_id

dbt

select 1 as order_id, 101 as customer_id, '2024-01-01'::date as order_date
union all
select 2, 102, '2024-01-02'::date
union all
select 3, 101, '2024-01-03'::date

# packages.yml content:
# packages:
#   - package: dbt-labs/dbt_utils
#     version: 0.9.6

# Add dbt-utils tests in schema.yml for orders model
# Your code here

Hint

Write the tests section in schema.yml using the exact dbt_utils test names and parameters.

4

Run dbt tests and show output

Run dbt test in your terminal and print the summary output line that shows how many tests passed and failed. For this exercise, simulate the output by printing exactly:

All tests passed! 2 tests successful.

dbt

select 1 as order_id, 101 as customer_id, '2024-01-01'::date as order_date
union all
select 2, 102, '2024-01-02'::date
union all
select 3, 101, '2024-01-03'::date

# packages.yml content:
# packages:
#   - package: dbt-labs/dbt_utils
#     version: 0.9.6

# schema.yml content:
# models:
#   - name: orders
#     tests:
#       - dbt_utils.unique_combination_of_columns:
#           combination_of_columns:
#             - order_id
#       - dbt_utils.not_null:
#           column_name: customer_id

# Print the test summary output below
# Your code here

Hint

Use a print statement to show the exact test summary output.

Practice

1. What is the main purpose of using dbt-utils package tests in your data models?

easy

A. To write complex SQL queries faster

B. To speed up data loading processes

C. To check data quality by validating conditions like uniqueness and nulls

D. To create visual dashboards automatically

Solution

1. Step 1: Understand the role of dbt-utils tests

   dbt-utils tests are designed to check data quality by validating conditions such as uniqueness, null values, and accepted values.

2. Step 2: Compare options with the purpose

   Only To check data quality by validating conditions like uniqueness and nulls describes checking data quality, which matches the purpose of dbt-utils tests.

3. Final Answer:

   To check data quality by validating conditions like uniqueness and nulls -> Option C

4. Quick Check:

   dbt-utils tests = data quality checks [OK]

Hint: Tests in dbt-utils check data quality, not speed or visuals [OK]

Common Mistakes:

• Thinking dbt-utils speeds up data loading
• Confusing tests with dashboard creation
• Assuming tests write SQL queries automatically

2. Which of the following is the correct syntax to add a uniqueness test using dbt-utils in a model YAML file?

easy

A. tests:
- dbt_utils.unique:
column_name: column_name

B. tests: - dbt_utils.unique: ['column_name']

C. tests: - dbt_utils.unique - column_name

D. tests: - dbt_utils.unique: {column_name}

Solution

1. Step 1: Recall dbt-utils test syntax in YAML

   The correct syntax uses column_name parameter under the test, like dbt_utils.unique: column_name: column_name.

2. Step 2: Check each option format

   tests: - dbt_utils.unique: column_name: column_name correctly uses the column_name key-value pair, matching dbt-utils documentation.

3. Final Answer:

   tests: - dbt_utils.unique: column_name: column_name -> Option A

4. Quick Check:

   Unique test syntax = column_name key [OK]

Hint: Use column_name: column_name for dbt_utils.unique [OK]

Common Mistakes:

• Broken YAML with multiple dashes
• Using list ['column_name'] instead of column_name key
• Using curly braces for parameters

3. Given this YAML snippet in a dbt model file:

tests:
  - dbt_utils.not_null:
      column_name: user_id
  - dbt_utils.unique:
      column_name: user_id

What will happen when you run dbt test?

medium

A. dbt will throw a syntax error due to wrong YAML format

B. dbt will check that user_id has no nulls and is unique

C. dbt will only check for nulls in user_id

D. dbt will ignore these tests and run default tests

Solution

1. Step 1: Analyze YAML test syntax

   The syntax uses column_name: user_id under dbt_utils tests, which is the correct format for not_null and unique tests.

2. Step 2: Identify dbt behavior

   This correct format will cause dbt to run both tests: no nulls and uniqueness on user_id.

3. Final Answer:

   dbt will check that user_id has no nulls and is unique -> Option B

4. Quick Check:

   Correct YAML format = runs both tests [OK]

Hint: dbt-utils tests use column_name: col correctly [OK]

Common Mistakes:

• Using lists [- user_id] instead of column_name key
• Wrong YAML indentation
• Missing quotes around column_name value

4. You wrote this test in your model YAML:

tests:
  - dbt_utils.accepted_values:
      column_name: ['status']
      values: ['active', 'inactive']

But when running dbt test, it fails with an error. What is the likely problem?

medium

A. The column_name should be a string, not a list

B. The values parameter should be a list under accepted_values, not a key-value pair

C. The accepted_values test requires a where clause

D. The values list must be a string, not a list

Solution

1. Step 1: Review dbt-utils accepted_values test syntax

   The accepted_values test expects column_name as a string identifier, not a list.

2. Step 2: Identify the YAML structure issue

   column_name: ['status'] passes a list instead of string 'status', causing an error.

3. Final Answer:

   The column_name should be a string, not a list -> Option A

4. Quick Check:

   accepted_values column_name = string [OK]

Hint: column_name must be string for accepted_values test [OK]

Common Mistakes:

• Passing column_name as list instead of string
• Using string instead of list for values
• Adding unnecessary where clause

5. You want to ensure a column email in your users model has no nulls and all values are unique using dbt-utils tests. Which YAML snippet correctly applies both tests?

hard

A. tests:
- dbt_utils.not_null:
email
- dbt_utils.unique:
email

B. tests:
- dbt_utils.not_null:
columns: ['email']
- dbt_utils.unique:
columns: ['email']

C. tests:
- dbt_utils.not_null:
- email
- dbt_utils.unique:
- email

D. tests:
- dbt_utils.not_null:
column_name: email
- dbt_utils.unique:
column_name: email

Solution

1. Step 1: Recall correct dbt-utils test syntax for columns

   dbt-utils not_null and unique tests expect column_name: email under the test name.

2. Step 2: Evaluate each YAML snippet

   Snippet with column_name: email for both tests correctly uses the required key-value format. The snippet missing colons after tests is invalid YAML. Using a list - email passes incorrect config type. Using columns is invalid key.

3. Final Answer:

   tests: - dbt_utils.not_null: column_name: email - dbt_utils.unique: column_name: email -> Option D

4. Quick Check:

   column_name: [col] under test name [OK]

Hint: Use column_name: email for not_null and unique tests [OK]

Common Mistakes:

• Omitting colon and using scalar email
• Using list - email for single-column tests
• Using wrong key columns instead of column_name