dbt-utils (surrogate_key, pivot, unpivot)

Introduction

These dbt-utils macros help you easily create unique IDs and reshape your data tables. They save time and avoid errors when working with data.

When you need a unique ID for each row combining multiple columns.

When you want to turn rows into columns to summarize data.

When you want to turn columns into rows to analyze data differently.

When preparing data for reports that need a specific layout.

When cleaning data to make it easier to work with in SQL.

Syntax

dbt

-- surrogate_key
{{ dbt_utils.surrogate_key(['column1', 'column2']) }}

-- pivot
{{ dbt_utils.pivot(
    relation=ref('your_table'),
    column='column_to_pivot',
    value='value_column',
    aggregate='sum'
) }}

-- unpivot
{{ dbt_utils.unpivot(
    relation=ref('your_table'),
    columns=['col1', 'col2'],
    unpivot_column_name='new_column_name',
    unpivot_value_name='new_value_name'
) }}

surrogate_key creates a unique string ID by combining columns.

pivot turns row values into columns, summarizing data.

unpivot turns columns into rows, making data longer.

Examples

Creates a unique ID by combining user_id and order_id.

dbt

{{ dbt_utils.surrogate_key(['user_id', 'order_id']) }}

Turns product categories into columns showing total sales per category.

dbt

{{ dbt_utils.pivot(
    relation=ref('sales_data'),
    column='product_category',
    value='sales_amount',
    aggregate='sum'
) }}

Turns monthly sales columns into rows with month and sales columns.

dbt

{{ dbt_utils.unpivot(
    relation=ref('monthly_sales'),
    columns=['jan', 'feb', 'mar'],
    unpivot_column_name='month',
    unpivot_value_name='sales'
) }}

Sample Program

This example shows how to create a surrogate key combining user_id and product_category. Then it shows how to pivot sales by product category and unpivot monthly sales columns into rows.

dbt

with source_data as (
    select * from (values
        (1, 'A', 100, 10, 20),
        (2, 'B', 200, 15, 25),
        (3, 'A', 150, 12, 22)
    ) as t(user_id, product_category, sales, jan, feb)
),

-- Create surrogate key combining user_id and product_category
keyed as (
    select
        user_id,
        product_category,
        sales,
        {{ dbt_utils.surrogate_key(['user_id', 'product_category']) }} as surrogate_id,
        jan,
        feb
    from source_data
),

-- Pivot product_category to columns with sum of sales
pivoted as (
    select * from (
        select user_id, product_category, sales from keyed
    )
    pivot (
        sum(sales) for product_category in ('A' as A_sales, 'B' as B_sales)
    )
),

-- Unpivot jan and feb sales into month and sales columns
unpivoted as (
    select * from (
        select user_id, jan, feb from keyed
    )
    unpivot (
        sales for month in (jan, feb)
    )
)

select * from keyed;

-- To see pivoted and unpivoted results, replace last select with:
-- select * from pivoted;
-- select * from unpivoted;

Important Notes

The surrogate_key macro returns a string combining columns, useful for unique IDs.

Pivot and unpivot help reshape data but require careful column and value selection.

Always check your data after pivot/unpivot to ensure it matches your analysis needs.

Summary

surrogate_key creates unique IDs by combining columns.

pivot turns rows into columns to summarize data.

unpivot turns columns into rows to reshape data.

Practice

1. What is the main purpose of the surrogate_key macro in dbt-utils?

easy

A. To create a unique identifier by combining multiple columns

B. To convert rows into columns for summarization

C. To change column names in a table

D. To remove duplicate rows from a dataset

Solution

1. Step 1: Understand surrogate_key purpose

   The surrogate_key macro combines values from multiple columns to create a unique ID.

2. Step 2: Compare with other options

   Pivoting changes rows to columns, renaming columns is unrelated, and removing duplicates is different.

3. Final Answer:

   To create a unique identifier by combining multiple columns -> Option A

4. Quick Check:

   surrogate_key = unique ID creation [OK]

Hint: surrogate_key always combines columns to make unique IDs [OK]

Common Mistakes:

• Confusing surrogate_key with pivot or unpivot
• Thinking it renames columns
• Assuming it removes duplicates

2. Which of the following is the correct syntax to use the pivot macro in dbt-utils?

easy

A. {% set pivoted = dbt_utils.unpivot(table=ref('sales'), column='month', value='revenue') %}

B. {% set pivoted = dbt_utils.surrogate_key(columns=['id', 'date']) %}

C. {% set pivoted = dbt_utils.pivot(table=ref('sales'), column='month', value='revenue') %}

D. {% set pivoted = dbt_utils.pivot(table='sales', row='month', value='revenue') %}

Solution

1. Step 1: Identify correct macro and parameters

   The pivot macro requires a table, a column to pivot on, and a value column.

2. Step 2: Check syntax correctness

   {% set pivoted = dbt_utils.pivot(table=ref('sales'), column='month', value='revenue') %} uses table=ref('sales'), column='month', and value='revenue', which is correct.

3. Final Answer:

   {% set pivoted = dbt_utils.pivot(table=ref('sales'), column='month', value='revenue') %} -> Option C

4. Quick Check:

   pivot syntax = table, column, value [OK]

Hint: pivot needs table, column, and value parameters [OK]

Common Mistakes:

• Using unpivot macro instead of pivot
• Passing wrong parameter names like 'row' instead of 'column'
• Not using ref() for table argument

3. Given the following data in a table sales_data:

product | month | revenue
-------|-------|--------
A      | Jan   | 100
A      | Feb   | 150
B      | Jan   | 200
B      | Feb   | 250

What will be the output of this dbt-utils pivot macro call?

{% set pivoted = dbt_utils.pivot(table=ref('sales_data'), column='month', value='revenue') %}

medium

A. A table with columns: product, Jan, Feb and values filled with revenue

B. A table with columns: month, product, revenue repeated

C. A table with columns: product, revenue only

D. An error because pivot needs a group_by column

Solution

1. Step 1: Understand pivot behavior

   Pivot converts unique values in the 'month' column into new columns and fills them with 'revenue' values grouped by 'product'.

2. Step 2: Apply to given data

   For product A, Jan=100 and Feb=150; for B, Jan=200 and Feb=250. So columns are product, Jan, Feb with revenue values.

3. Final Answer:

   A table with columns: product, Jan, Feb and values filled with revenue -> Option A

4. Quick Check:

   pivot turns rows to columns = A table with columns: product, Jan, Feb and values filled with revenue [OK]

Hint: Pivot turns unique column values into new columns [OK]

Common Mistakes:

• Thinking pivot repeats original rows
• Expecting only product and revenue columns
• Assuming pivot requires explicit group_by parameter

4. You wrote this dbt-utils unpivot macro call:

{% set unpivoted = dbt_utils.unpivot(table=ref('sales_summary'), columns=['Jan', 'Feb'], name='month', value='revenue') %}

But you get an error. What is the most likely cause?

medium

A. The 'columns' parameter should be a string, not a list

B. The 'name' parameter must be 'column_name' instead of 'month'

C. The table reference is missing the schema

D. The 'columns' parameter must be passed as a string with comma-separated column names

Solution

1. Step 1: Check unpivot columns parameter format

   The unpivot macro expects the columns argument as a string listing columns separated by commas, not a list.

2. Step 2: Identify error cause

   Passing a Python list causes a syntax or runtime error; it must be a string like 'Jan, Feb'.

3. Final Answer:

   The 'columns' parameter must be passed as a string with comma-separated column names -> Option D

4. Quick Check:

   unpivot columns = comma-separated string [OK]

Hint: Pass columns as comma-separated string, not list, in unpivot [OK]

Common Mistakes:

• Passing columns as Python list instead of string
• Using wrong parameter names like 'name' instead of 'column_name'
• Ignoring table reference format

5. You have a table employee_sales with columns: employee_id, region, Jan, Feb, Mar. You want to create a unique surrogate key combining employee_id and region, then unpivot the monthly sales columns into rows with columns month and sales. Which dbt-utils macros and steps should you use?

hard

A. Use only pivot macro on employee_id and region columns

B. First use surrogate_key(['employee_id', 'region']) to create ID, then unpivot(table=ref('employee_sales'), columns='Jan, Feb, Mar', name='month', value='sales')

C. Use unpivot macro first, then surrogate_key on month and sales

D. Create surrogate key by concatenating Jan, Feb, Mar columns, then pivot on region

Solution

1. Step 1: Create unique surrogate key

   Use surrogate_key on employee_id and region to get a unique ID per employee-region.

2. Step 2: Unpivot monthly sales columns

   Apply unpivot macro with columns='Jan, Feb, Mar', name='month', and value='sales' to convert months into rows.

3. Final Answer:

   First use surrogate_key(['employee_id', 'region']) then unpivot with columns='Jan, Feb, Mar' -> Option B

4. Quick Check:

   surrogate_key then unpivot = First use surrogate_key(['employee_id', 'region']) to create ID, then unpivot(table=ref('employee_sales'), columns='Jan, Feb, Mar', name='month', value='sales') [OK]

Hint: Create surrogate_key first, then unpivot monthly columns [OK]

Common Mistakes:

• Using pivot instead of unpivot for monthly columns
• Creating surrogate_key on month and sales columns
• Concatenating month columns for surrogate_key