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?
AUse only <code>pivot</code> macro on <code>employee_id</code> and <code>region</code> columns
BFirst use <code>surrogate_key(['employee_id', 'region'])</code> to create ID, then <code>unpivot(table=ref('employee_sales'), columns='Jan, Feb, Mar', name='month', value='sales')</code>
CUse <code>unpivot</code> macro first, then <code>surrogate_key</code> on <code>month</code> and <code>sales</code>
DCreate surrogate key by concatenating <code>Jan</code>, <code>Feb</code>, <code>Mar</code> columns, then pivot on <code>region</code>
Step-by-Step Solution
Solution:
Step 1: Create unique surrogate key
Use surrogate_key on employee_id and region to get a unique ID per employee-region.
Step 2: Unpivot monthly sales columns
Apply unpivot macro with columns='Jan, Feb, Mar', name='month', and value='sales' to convert months into rows.
Final Answer:
First use surrogate_key(['employee_id', 'region']) then unpivot with columns='Jan, Feb, Mar' -> Option B
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]
Quick Trick:Create surrogate_key first, then unpivot monthly columns [OK]
Common Mistakes:
MISTAKES
Using pivot instead of unpivot for monthly columns
Creating surrogate_key on month and sales columns
Concatenating month columns for surrogate_key
Master "Packages and Reusability" in dbt
9 interactive learning modes - each teaches the same concept differently
