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 ALLSQL. - 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