Skip to main content

04. Using dbt-utils

What is dbt-utils?​

dbt-utils is a popular community package for DBT that provides:

  • Pre-built macros for common patterns
  • Reusable SQL logic to speed up development
  • Best practices for modular, maintainable DBT projects

It saves time and ensures consistency across projects.

Before using this topic, make sure you understand packages.yml and dbt deps from the previous topic.


Why Use dbt-utils?​

Avoid reinventing the wheel for common tasks
Use tested macros from the community
Improve code quality and maintainability


Installing dbt-utils​

Add dbt-utils to your packages.yml:

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

Install the package:

dbt deps

Common dbt-utils Macros​

1️⃣ surrogate_key​

Generate a hashed key from multiple columns:

{{ dbt_utils.surrogate_key(['order_id', 'customer_id']) }}

2️⃣ get_column_values​

Return distinct column values:

{{ dbt_utils.get_column_values(table=ref('orders'), column='status') }}

3️⃣ star​

Select all columns dynamically:

SELECT {{ dbt_utils.star(from=ref('orders')) }}

4️⃣ union_relations​

Union multiple relations into a single result set:

{{ dbt_utils.union_relations(
relations=[ref('orders_2024'), ref('orders_2025')],
column_list=['order_id', 'customer_id', 'amount', 'order_date']
) }}

This is useful when you want to combine several similarly shaped tables without manually writing UNION ALL for each one.


5️⃣ date_spine​

Generate a date series:

{{ dbt_utils.date_spine(
datepart="day",
start_date="2024-01-01",
end_date="2024-12-31"
) }}

5️⃣ test_not_null​

Check for null values in a column:

{{ dbt_utils.test_not_null(ref('customers'), 'customer_id') }}

Example: dbt_utils.surrogate_key and dbt_utils.star​

Manual key generation (without dbt_utils)​

{{ config(materialized='table') }}

SELECT
order_id,
customer_id,
order_date,
amount,
order_id || '-' || customer_id AS surrogate_key
FROM {{ ref('stg_orders') }}

This manual approach works, but it depends on correct concatenation and may not handle null values cleanly.

Use dbt_utils.surrogate_key​

{{ config(materialized='table') }}

SELECT
order_id,
customer_id,
order_date,
amount,
{{ dbt_utils.surrogate_key(['order_id', 'customer_id']) }} AS surrogate_key
FROM {{ ref('stg_orders') }}

Why this is better:

  • It provides a consistent key format.
  • It handles null-safe concatenation.
  • It is easier to maintain when the key definition changes.

Manual column selection (without dbt_utils)​

SELECT
order_id,
customer_id,
order_date,
amount,
customer_name,
customer_email
FROM {{ ref('stg_orders') }}

Use dbt_utils.star​

SELECT
{{ dbt_utils.star(from=ref('stg_orders'), except=['customer_email']) }}
FROM {{ ref('stg_orders') }}

Why this is useful:

  • It simplifies SQL for wide tables.
  • It helps avoid typing many column names.
  • It lets you exclude a few columns while selecting the rest.

Example: dbt_utils.union_relations​

Manual union (without dbt_utils)​

SELECT order_id, customer_id, amount, order_date FROM {{ ref('orders_2024') }}
UNION ALL
SELECT order_id, customer_id, amount, order_date FROM {{ ref('orders_2025') }}

This works, but it becomes verbose as you add more relations.

Use dbt_utils.union_relations​

{{ dbt_utils.union_relations(
relations=[
ref('orders_2024'),
ref('orders_2025'),
ref('orders_2026')
],
column_list=['order_id', 'customer_id', 'amount', 'order_date']
) }}

Why this is better:

  • It reduces repetitive UNION ALL SQL.
  • It keeps the same column list across all relations.
  • It is easier to maintain when you add or remove source relations.

Using dbt-utils Tests in YAML​

Add in your schema.yml:

version: 2

models:
- name: customers
tests:
- dbt_utils.unique_combination_of_columns:
combination_of_columns:
- first_name
- last_name

Best Practices​

Use dbt-utils for common patterns Keep it updated with dbt deps Combine with custom macros for flexibility Check documentation for new features


Learn More​

dbt-utils GitHub Repository