dbt_utils Package

The dbt_utils package from dbt Labs is the most widely used dbt package. It provides macros and tests that fill gaps in standard SQL, work consistently across different warehouse adapters, and save you from writing common patterns by hand. This topic covers the most useful macros in detail with practical examples.

Installing dbt_utils

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

dbt deps

generate_surrogate_key

Creates a unique hash key from one or more columns. Use this when a table lacks a natural primary key and you need to create one from existing columns.

-- Without surrogate key: no single column uniquely identifies rows
select
    order_id,
    product_id,
    quantity,
    unit_price
from {{ source('ecommerce', 'order_items') }}
-- Problem: order_id alone repeats (multiple products per order)
-- product_id alone repeats (same product in many orders)

-- With surrogate key
select
    {{ dbt_utils.generate_surrogate_key(['order_id', 'product_id']) }} as order_item_id,
    order_id,
    product_id,
    quantity,
    unit_price
from {{ source('ecommerce', 'order_items') }}

The surrogate key is a hash (MD5 or SHA256 depending on your warehouse) of the concatenated column values. It is deterministic — the same input always produces the same hash.

star

Selects all columns from a relation except the ones you specify. Useful for removing metadata columns from staging models without listing every column manually.

-- Without star: list every column
select
    id,
    name,
    email,
    city,
    created_at
    -- forgot to add "status" here — bug!
from {{ source('crm', 'customers') }}

-- With star: select all except unwanted columns
select
    {{ dbt_utils.star(from=source('crm', 'customers'),
                      except=["_fivetran_deleted", "_fivetran_synced", "_ab_cdc_deleted_at"]) }}
from {{ source('crm', 'customers') }}

As new columns are added to the source table, the star macro automatically includes them without any code changes.

date_spine

Generates a table with one row per time period (day, week, month, etc.) between two dates. Essential for creating a date dimension table or for ensuring no dates are missing in a time series analysis.

-- models/dim_dates.sql
{{ config(materialized='table') }}

{{
    dbt_utils.date_spine(
        datepart="day",
        start_date="cast('2020-01-01' as date)",
        end_date="cast('2030-12-31' as date)"
    )
}}

This creates a table with columns date_day containing every calendar day from Jan 1 2020 to Dec 31 2030 — over 4,000 rows, generated without any source data.

pivot

Converts rows into columns. Takes a categorical column and creates one boolean or aggregated column per category.

-- Source data shape:
order_id | payment_method | amount
1        | credit_card    | 100
1        | paypal         | 50
2        | credit_card    | 200

-- models/orders_by_payment.sql
select
    order_id,
    {{ dbt_utils.pivot(
        column='payment_method',
        values=['credit_card', 'paypal', 'bank_transfer'],
        agg='sum',
        then_value='amount',
        else_value=0
    ) }}
from {{ ref('stg_payments') }}
group by order_id

-- Output shape:
order_id | credit_card | paypal | bank_transfer
1        | 100         | 50     | 0
2        | 200         | 0      | 0

get_relations_by_pattern

Returns a list of tables in your warehouse that match a naming pattern. Useful for building models that union many tables with similar names:

-- Suppose you have monthly partitioned tables:
-- raw.events_2024_01, raw.events_2024_02, ... raw.events_2024_12
{% set relations = dbt_utils.get_relations_by_pattern(
    schema_pattern='raw',
    table_pattern='events_2024_%'
) %}

select * from {{ dbt_utils.union_relations(relations=relations) }}

union_relations

Unions multiple tables together and adds a _dbt_source_relation column identifying which table each row came from:

{% set sources = [
    ref('stg_orders_us'),
    ref('stg_orders_eu'),
    ref('stg_orders_apac')
] %}

{{ dbt_utils.union_relations(relations=sources) }}

dbt_utils Generic Tests

# Combination of columns must be unique
- dbt_utils.unique_combination_of_columns:
    combination_of_columns:
      - order_id
      - product_id

# Column must have at least 95% non-null values
- dbt_utils.not_null_proportion:
    at_least: 0.95

# Expression must be true for all rows
- dbt_utils.expression_is_true:
    expression: "amount_dollars >= 0"

# Column must equal the sum of other columns
- dbt_utils.equality:
    compare_model: ref('fct_orders_archive')

Safe Divide

{{ dbt_utils.safe_divide('revenue', 'sessions') }}
-- Returns null instead of error when sessions = 0

Checking the Package Version

# To see available macros in the installed package:
ls dbt_packages/dbt_utils/macros/

# Or check the package documentation at hub.getdbt.com