dbt-utils (surrogate_key, pivot, unpivot) - Step-by-Step Execution

Concept Flow - dbt-utils (surrogate_key, pivot, unpivot)

Start with raw data

↓

Use surrogate_key to create unique IDs

↓

Apply pivot to transform rows into columns

↓

Apply unpivot to transform columns back to rows

↓

Final transformed dataset

This flow shows how raw data is first given unique IDs with surrogate_key, then reshaped wide with pivot, and finally reshaped long with unpivot.

Execution Sample

dbt

with source as (
  select * from raw_data
),
keyed as (
  select {{ dbt_utils.surrogate_key(['user_id', 'date']) }} as id, * from source
),
pivoted as (
  select * from {{ dbt_utils.pivot(
    source=ref('keyed'),
    pivot_column='metric',
    value_column='value'
  ) }}
),
unpivoted as (
  select * from {{ dbt_utils.unpivot(
    source=ref('pivoted'),
    column_name='metric',
    value_name='value',
    columns=['clicks', 'views']
  ) }}
)
select * from unpivoted

This code creates unique keys, pivots metrics into columns, then unpivots them back to rows.

Execution Table

Step	Action	Input Data Sample	Output Data Sample	Notes
1	Read raw_data	[{user_id:1, date:'2024-01-01', metric:'clicks', value:10}, {user_id:1, date:'2024-01-01', metric:'views', value:100}]	Same as input	Starting raw data with user metrics
2	Apply surrogate_key(['user_id','date'])	Same as step 1	[{id:'abc123', user_id:1, date:'2024-01-01', metric:'clicks', value:10}, {id:'abc123', user_id:1, date:'2024-01-01', metric:'views', value:100}]	Unique id 'abc123' created for user_id and date
3	Pivot on 'metric' column	Step 2 output	[{id:'abc123', user_id:1, date:'2024-01-01', clicks:10, views:100}]	Rows turned into columns by metric names
4	Unpivot columns ['clicks','views']	Step 3 output	[{id:'abc123', user_id:1, date:'2024-01-01', metric:'clicks', value:10}, {id:'abc123', user_id:1, date:'2024-01-01', metric:'views', value:100}]	Columns turned back into rows
5	End	Step 4 output	Same as step 4	Transformation complete

💡 All steps executed, data transformed from raw to keyed, pivoted, then unpivoted.

Variable Tracker

Variable	Start	After Step 2	After Step 3	After Step 4	Final
data_rows	Raw rows with user_id, date, metric, value	Rows with added 'id' column (surrogate_key)	Rows pivoted to wide format with metric columns	Rows unpivoted back to long format	Final unpivoted rows

Key Moments - 3 Insights

Why does surrogate_key create the same id for rows with the same user_id and date?

Because surrogate_key hashes the combination of specified columns (user_id and date), so rows sharing those values get the same unique id, as shown in execution_table step 2.

What happens to the 'metric' column after pivot?

The 'metric' values become new column names, so the original 'metric' column disappears and its values become column headers, as seen in execution_table step 3.

Why do we use unpivot after pivot?

Unpivot reverses pivot by turning columns back into rows, useful to restore long format for analysis, shown in execution_table step 4.

Visual Quiz - 3 Questions

Look at the execution_table at step 2, what does the 'id' column represent?

A The original user_id value

B A unique identifier created by combining user_id and date

C A random number assigned to each row

D The metric name

💡 Hint

Check step 2 in execution_table where surrogate_key creates 'id' from user_id and date

At which step does the data change from long format to wide format?

A Step 3

B Step 2

C Step 1

D Step 4

💡 Hint

Look at execution_table step 3 where pivot turns rows into columns

If we skip the unpivot step, what will the final data look like?

A Data will remain in long format with metric and value columns

B Data will lose the surrogate_key id

C Data will be in wide format with metric columns

D Data will be empty

💡 Hint

Refer to execution_table step 3 output before unpivot

Concept Snapshot

dbt-utils surrogate_key creates unique IDs by hashing columns.
Pivot turns row values into columns.
Unpivot reverses pivot, turning columns back to rows.
Use surrogate_key for stable keys.
Pivot/unpivot reshape data for analysis.

Full Transcript

This lesson shows how to use dbt-utils macros surrogate_key, pivot, and unpivot. We start with raw data containing user metrics. Surrogate_key creates a unique ID by combining user_id and date. Then pivot reshapes the data by turning metric names into columns, making it wide format. Finally, unpivot reverses this, turning columns back into rows for long format. The execution table traces each step with sample data. Key moments clarify why surrogate_key produces the same ID for matching columns, how pivot changes the metric column into headers, and why unpivot is used after pivot. The visual quiz tests understanding of these steps. The snapshot summarizes the key points for quick reference.

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