dbt_utils

Essential utilities for common dbt patterns.

Commonly Used Macros

generate_surrogate_key

Create a hashed surrogate key from multiple columns:

select
    {{ dbt_utils.generate_surrogate_key(['customer_id', 'order_date']) }} as order_key,
    ...
from orders

star

Select all columns except specified ones:

select
    {{ dbt_utils.star(from=ref('stg_orders'), except=['_loaded_at', '_batched_at']) }}
from {{ ref('stg_orders') }}

pivot / unpivot

Transform rows to columns and vice versa:

{{ dbt_utils.pivot(
    'status',
    dbt_utils.get_column_values(ref('orders'), 'status'),
    then_value='order_total'
) }}

date_spine

Generate a continuous date range:

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

Testing Macros

• •unique_combination_of_columns - Test composite uniqueness
• •equality - Compare two relations
• •expression_is_true - Test custom expressions
• •recency - Test data freshness

SQL Helpers

• •safe_add - Add with null handling
• •safe_divide - Divide with zero handling
• •nullif - Return null if condition met
• •get_column_values - Get distinct values