how to meet the requirement for the traditional design pattern for scd type 2 dimensional tables in the mart layer using dbt

Help
,
1

I want to establish how to meet the requirement for the traditional design pattern for scd type 2 dimensional tables in the mart layer using dbt. I cannot determine if an out-of-the-box feature exists to do the same in the mart layer. Could you please point me in the right direction?

I understand the snapshot feature’s functionality to track source data changes. I cannot use the dbt build and dbt run command to generate and maintain snapshot tables. I need to use he dbt snapshot command instead

2

Snapshots are dbt’s out-of-the-box interpretation of SCD 2 dimensions. The biggest drawback is that it can start tracking from a point onwards. So where you have historic data you would need to write your own code to load the historic data.

> “I cannot use the dbt build and dbt run command to generate and maintain snapshot tables. I need to use he dbt snapshot command instead”
Can you clarify what you mean with the statement above please? Why can’t you use dbt build for snapshots

Note: @Johann De Wet originally posted this reply in Slack. It might not have transferred perfectly.

3

Thank you for clarifying that Snapshots are dbt’s implementation pattern for SCD Type 2. My sincere apologies for having done my due diligence. I overlooked the fact that dbt build command runs models, snapshots, seeds and tests. Hence I should be able to use the snapshot configuration block at the top of the model file for implementing scd type 2 on dimension tables in the mart layer.

Thank you again for your time, and knowledge share.

1 Like