dbt-utils (surrogate_key, pivot, unpivot) - Mini Project: Build & Apply

Using dbt-utils Macros: surrogate_key, pivot, and unpivot

📖 Scenario: You work as a data analyst in a retail company. You have sales data with customer names, product categories, and sales amounts. You want to create a unique surrogate key for each sale, then transform the data to see sales by category in columns (pivot), and finally convert it back to a long format (unpivot) for detailed analysis.

🎯 Goal: Build a dbt model that uses dbt-utils.surrogate_key to create unique keys, dbt-utils.pivot to reshape data from long to wide format, and dbt-utils.unpivot to reshape data back from wide to long format.

📋 What You'll Learn

Create a source table with sales data including customer_name, category, and sales_amount.

Use dbt-utils.surrogate_key macro to generate a unique sale_id for each row.

Use dbt-utils.pivot macro to pivot sales amounts by category.

Use dbt-utils.unpivot macro to unpivot the pivoted data back to long format.

💡 Why This Matters

🌍 Real World

Retail and sales data often need reshaping to analyze sales by category or customer. Surrogate keys help uniquely identify transactions.

💼 Career

Data analysts and engineers use dbt and dbt-utils macros to clean, transform, and prepare data efficiently for reporting and analytics.

1

Create the initial sales data table

Create a dbt model called sales_data with a SELECT statement that returns these exact rows: customer_name as 'Alice', 'Bob', 'Alice'; category as 'Electronics', 'Clothing', 'Clothing'; and sales_amount as 100, 50, 75 respectively.

dbt

-- Create the sales_data model with the exact rows
-- Your code here

Need a hint?

Use values to create rows with customer_name, category, and sales_amount.

2

Add a surrogate key column

Add a new column called sale_id using the dbt_utils.surrogate_key macro on customer_name, category, and sales_amount in the sales_data model.

dbt

select
  dbt_utils.surrogate_key(array[customer_name, category, cast(sales_amount as text)]) as sale_id,
  customer_name,
  category,
  sales_amount
from (values
  ('Alice', 'Electronics', 100),
  ('Bob', 'Clothing', 50),
  ('Alice', 'Clothing', 75)
) as sales(customer_name, category, sales_amount)
-- Your code here

Need a hint?

Use dbt_utils.surrogate_key with an array of columns to create sale_id.

3

Pivot sales amounts by category

Use the dbt_utils.pivot macro to pivot the sales_amount values by category, grouping by customer_name. Select customer_name and the pivoted columns for 'Electronics' and 'Clothing' as electronics_sales and clothing_sales.

dbt

select
  customer_name,
  -- Use dbt_utils.pivot here to pivot sales_amount by category
  -- Your code here
from (
  select
    customer_name,
    category,
    sales_amount
  from (values
    ('Alice', 'Electronics', 100),
    ('Bob', 'Clothing', 50),
    ('Alice', 'Clothing', 75)
  ) as sales(customer_name, category, sales_amount)
) base
group by customer_name

Need a hint?

Use dbt_utils.pivot with relation, column, values, and value_column parameters.

4

Unpivot the pivoted sales data

Use the dbt_utils.unpivot macro to convert the pivoted sales columns electronics_sales and clothing_sales back into long format with columns category and sales_amount. Select customer_name, category, and sales_amount from the unpivoted result.

dbt

-- Use dbt_utils.unpivot here to unpivot the pivoted sales data
-- Select customer_name, category, and sales_amount
-- Your code here

Need a hint?

Use dbt_utils.unpivot with relation, columns, names, value_column_name, and name_column_name.