Creating SCD2 tables in dbt

I don't want this blog to become the dbt blog so I have taken my time to post about this but do fully intend to do some more posts on dbt and the cool built in functionality. Equally I am quite happy with where my model is at at the moment so until I find some new tool to use or a new data source I am going to look to expand the section on the free training available. 

dbt has the ability to cater for creating scd(2) style tables called snapshots, details of it are included on the advanced materialization training. I set up my first snapshot model by creating an scd2 table for the activity type dim, so that if I get a new exercise type added it will create a new rows, equally if I delete or modify one of the old columns it will end date the old row and insert the new row. The preference is to do this off a date column for change time however I don't have this so do the merge against all columns. 

Snapshots sit in their own folder and have a fairly simple modelling structure shown in my example below. 

	{% snapshot SCD2_ACTIVITY_TYPE_DIM %}
	{{
	config(
	target_schema='WORKSPACE_15661914',
	strategy='check',
	unique_key='id',
	check_cols=['ID', 'DESCRIPTION'],
	)
	}}
	select * from {{ source('exercise_data', 'ACTIVITY_TYPE_DIM') }}
	{% endsnapshot %}

view raw dbtsnapshot.sql hosted with ❤ by GitHub