Hi

I’m new to data modelling so could use some help.

I’m building a personal project using a fairly standard 3NF sales database as the source data.

So far I have a pipeline that incrementally extracts data from the source system each day into a Raw storage layer, using a watermarking technique. The format here is:

raw/<table name>/<current date>/export.csv

My end goal is a Star Schema (I’ve already designed this).

I’d like the next part of my pipeline to load into Silver (cleaning up column names etc), and then load into my dimensions & facts within gold layer.

However, struggling with the logic of it, and what my silver layer should look like. I’m just conscious that in my gold layer I need to retain history with SCD2. 

I believe some people just upsert to Silver (update existing rows and append new rows). Then truncate/load into Gold Layer, but I think this would make it impossible to track SCD2.

Im getting a bit muddled with all of this so would appreciate some help.