dbt_utils Package: The Macros Every dbt Project Ends Up Needing

If you’ve written more than a handful of dbt models, you’ve probably already hit one of the problems dbt_utils solves — generating a surrogate key from multiple columns, building a complete date range with no gaps, or unioning several similarly-shaped tables together. dbt_utils is the most widely installed community package in the dbt ecosystem precisely because these problems are so common that reinventing them per-project is wasted effort.

Installing dbt_utils

packages:
  - package: dbt-labs/dbt_utils
    version: [">=1.1.0", "<2.0.0"]

dbt deps

Once installed, every macro is called with the dbt_utils. prefix, as covered generally in dbt Packages.

generate_surrogate_key: Composite Keys Done Correctly

Many source tables don’t have a clean single-column primary key — an events table might only be uniquely identified by the combination of user_id, event_type, and timestamp. Hand-writing a hash of concatenated columns is error-prone (null handling, type casting, delimiter collisions). generate_surrogate_key handles all of it.

select
  {{ dbt_utils.generate_surrogate_key(['user_id', 'event_type', 'created_at']) }} as event_key,
  user_id,
  event_type,
  created_at
from {{ ref('stg_events') }}

This produces a consistent hash, correctly handling nulls (which would otherwise silently break naive string concatenation), across any warehouse dbt supports — the exact SQL generated differs by warehouse dialect, but the macro abstracts that difference away entirely.

date_spine: Filling In Missing Dates

A revenue-by-day report with gaps on days with zero orders is a subtly wrong report — a chart with missing data points instead of visible zeros. date_spine generates a complete, gapless sequence of dates you can left-join your actual data onto.

with date_range as (
    {{ dbt_utils.date_spine(
        datepart="day",
        start_date="cast('2024-01-01' as date)",
        end_date="cast(current_date as date)"
    ) }}
)

select
  date_range.date_day,
  coalesce(sum(orders.amount), 0) as daily_revenue
from date_range
left join {{ ref('stg_orders') }} as orders
  on date_range.date_day = orders.order_date
group by 1
order by 1

Every day in the range appears in the output, whether or not any orders happened that day — the difference between a report that shows “$0 on March 3rd” and one that simply omits March 3rd entirely and lets a reader draw the wrong conclusion.

union_relations: Combining Similarly-Shaped Tables

Manually writing union all across a dozen regional or sharded tables, and keeping column lists in sync as schemas evolve, gets tedious fast. union_relations handles column alignment automatically.

{{ dbt_utils.union_relations(
    relations=[
        ref('stg_orders_us'),
        ref('stg_orders_eu'),
        ref('stg_orders_apac')
    ]
) }}

If one relation is missing a column the others have, union_relations fills it with null rather than failing the whole union — a meaningfully safer default than a hand-written union all that breaks the moment schemas drift slightly out of sync.

Generic Test Macros: not_null_proportion, equal_rowcount, and More

dbt_utils isn’t only about data-generation macros — it also ships generic tests beyond dbt’s built-in set, usable exactly like unique or not_null in a YAML file.

models:
  - name: stg_orders
    columns:
      - name: discount_pct
        tests:
          - dbt_utils.not_null_proportion:
              at_least: 0.95

This test passes as long as at least 95% of rows have a non-null discount_pct, which is far more realistic for many real-world columns than a strict not_null test that fails the entire build over a small, expected percentage of missing values.

Other Frequently-Used Macros Worth Knowing

-- star() with exclusions — useful for staging models cleaning raw tables
select
  {{ dbt_utils.star(from=source('raw', 'orders'), except=["internal_notes", "_raw_metadata"]) }}
from {{ source('raw', 'orders') }}

dbt_expectations: A Complementary Package Worth Knowing

For teams whose data quality needs go beyond dbt_utils’s testing macros, dbt_expectations (inspired by the Python library Great Expectations) adds a much larger library of statistical and distribution-based tests.

columns:
  - name: order_amount
    tests:
      - dbt_expectations.expect_column_values_to_be_between:
          min_value: 0
          max_value: 100000
      - dbt_expectations.expect_column_mean_to_be_between:
          min_value: 10
          max_value: 500

These go further than dbt_utils’s simpler checks — verifying not just that values fall in a range, but that a column’s statistical properties (mean, standard deviation) stay within expected bounds run over run, which is useful for catching subtler data drift that a basic range check wouldn’t flag.

When Not to Reach for dbt_utils

Not every problem benefits from a generic macro. If your logic is genuinely specific to your business rules — a custom churn definition, a company-specific revenue recognition rule — writing your own macro or plain SQL is more transparent than forcing it through a generic tool that wasn’t designed for it. dbt_utils earns its place solving genuinely generic, cross-project problems; it isn’t a replacement for understanding your own data.

Common Mistakes

Not pinning the version. dbt_utils has had breaking changes across major versions — an unpinned install can silently change macro behavior on a routine dbt deps run, exactly as described in dbt Packages.

Using union_relations without checking column type compatibility. It aligns column names, but a column that’s an integer in one relation and a string in another will still cause a type error — the macro solves missing columns, not type mismatches.

Forgetting dbt_utils. as case-sensitive namespace prefix. A macro call without the correct package prefix simply won’t be found, producing a compilation error that looks like the macro doesn’t exist at all.

Summary

dbt_utils is close to a standard library for dbt projects — before writing a macro for a problem that feels generic, it’s worth five minutes checking whether this package already solved it, tested against a far larger set of edge cases than any single project’s macro would be.