dbt Packages & Tests

dbt_utils workhorses

generate_surrogate_key in a real run, unique_combination_of_columns, and why the old surrogate_key name still trips people up.

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

Verification

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.

Official docs