dbt · Macros, Packages & Advanced

dbt_utils highlights

Mediumdbt_utilssurrogate_keydate_spineunion_relations

Interview question

Explain dbt_utils helpers: generate_surrogate_key, date_spine, and union_relations.

How to answer

The short answer

dbt_utils is the most common community package. Three interview favorites:

1. generate_surrogate_key

Builds a hashed from one or more columns (replaces older surrogate_key naming in many versions):

SQL
select
    {{ dbt_utils.generate_surrogate_key(['customer_id', 'order_id']) }} as order_sk,
    customer_id,
    order_id
from {{ ref('stg_orders') }}

Use for wide composite grains or when source keys are messy.

2. date_spine

Generates a continuous date (or timestamp) series, great for filling gaps in time-series marts:

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

Then left-join facts to the spine so missing days appear as zeros/nulls intentionally.

3. union_relations

Unions many similarly shaped relations (e.g. monthly shards or shard-per-source tables) with optional column harmonization:

SQL
{{ dbt_utils.union_relations(
    relations=[ref('events_a'), ref('events_b')]
) }}

Mental model

Interview tip: Name these three quickly; they show you have used real dbt projects, not only ref and run.