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

Need a 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

Need a 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

Need a 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

Need a hint?

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