How to add surrogate key from dimension table to fact table?

Archive
1

Hello all, I am new to dbt and would like to know, how to add surrogate key from dimension table to fact table?
I have multiple dimension tables that do not have primary key. I have created surrogate key using {{ dbt_utils.surrogate_key([‘field_a’, ‘field_b’[,…]]) }}.

Now I need to connect dimensions to fact table using surrogate keys. Appreciate the help!

Thank you!

1 Like
2

Hey there,

Not sure if I got you, but that’s how I do it:

  1. Generate surrogate keys in the dimensions (e.g. customer & sales office)
  2. Keep the natural business key
  3. Add surrogate keys from dimensions into your fact (e.g. sales_orders) by joining them on the natural key like (I am sparing out table aliases and CTE’s you may want to use for better readability in longer queries):
Select 
dim_customer.customer_id, 
dim_sales_office.sales_office_id, 
fct_sales_order.* 
From 
fct_sales_order 
left join dim_customer 
on fct_sales_order.customer_number = dim_customer.customer_number
Left join dim_sales_office 
on fct_sales_order.sales_office_number = dim_sales_office.office_number

Note:

  • the records for which you don’t find a dimension surrogate should be checked and the reason identified. You should add a case statement to change these null values to “-1” so they can be added to the facts PK.
  • It is recommendable to create composite keys (customer_id, sales_office_id,…) in your dims and facts for reusability and consistency.

Don’t know of any alternative, but it there are better approaches, I am happy to hear.
Hope that answers your question?