dbt_utils workhorses
The problem
You need a hashed key from several columns, or a uniqueness test on a composite, or a SELECT that includes every column except a few. Those problems are exactly why official dbt documentation points people at packages, and why dbt_utils exists. The package is not magic — you still have to pin a version and call the current macro names.
A working example
Installed from the dbt Hub as dbt-labs/dbt_utils, version 1.4.1, and executed in the companion project:
select
{{ dbt_utils.generate_surrogate_key(['order_id', 'customer_id']) }} as order_sk,
order_id,
customer_id,
status,
amount,
order_date
from {{ ref('stg_orders') }}
dbt run succeeded. The first row’s order_sk was 77c32878c6fe916f4ad27eae832464b5 (MD5-length hex). A unique and not_null test on order_sk passed.
Official dbt data-test documentation shows a second workhorse for composite uniqueness:
models:
- name: orders
data_tests:
- dbt_utils.unique_combination_of_columns:
arguments:
combination_of_columns:
- country_code
- order_id
That official snippet was not re-run here; the same package version provides the test. Official docs say this is especially useful for large datasets compared with concatenating a key only to test it.
Other frequently used macros in this family (star, deduplicate, date_spine, union_relations) live in the same package. Read the package documentation for the version you pin — argument names change.
The gotcha
The older macro name surrogate_key was renamed. A Stack Overflow question in this exact vein (“dbt_utils.SHA2() is not working in a macro”) is the class of error you get when a README you copied is a version behind the package you installed. After dbt deps, compile a tiny model (dbt compile -s …) and read the SQL before you ship the hash into a primary key.
generate_surrogate_key is a hash, not a stable business key from a source system. If you change the column list, or how nulls are coalesced, every key changes. Do not treat it as immutable history unless you have frozen the expression and the package version.
When not to use this
- A real source primary key already exists. Use it.
- You only needed
uniqueon one column — the built-in test is enough. - You were about to install
dbt_utilsin order to pull in all of its models. Official package behavior: models in an installed package are materialized when youdbt run, unless you disable them. Install it for the macros you call, and disable unused package models if they appear in your run.
generate_surrogate_key was executed with dbt_utils 1.4.1, dbt-core 1.12.7, and dbt-duckdb 1.11.1; unique/not_null tests on the hash passed. The unique_combination_of_columns YAML is from official dbt documentation and was not executed in this project.