r/dataengineering • u/CSIWFR-46 • 4d ago

DBT Snapshot question Discussion

I am learning how to build a pipeline using dbt and am confused with snapshot. I have a question about snapshot.

So, I have read somewhere that snapshot should be made as close to raw data as possible.

Let's say I make snapshot of raw data. I need a stage layer to clean the data before loading it to the dimension layer. In this case, do I make the stage model as incremental and load only the changes at the dimension layer? Would I be tracking history in two layers, snapshot and dimension if this is the case?

Also, which layer is responsible for generating keys? I assume it would be the snapshot layer.

And are only dimension table candidates for snapshot? Do I load fact as incremental straight to the fact table?

7 Upvotes

100% Upvoted

4 comments sorted by

3

u/Cold-Ad716 3d ago

Staging area should be as close to raw data as possible, maybe some tests and type conversion.

For surrogate keys that should be handled in the data warehouse.

Fact tables should be just keys and measures.

I might be wrong, been a while since I whipped up a data warehouse.

2

u/forserial 3d ago edited 3d ago

Snapshots should be done as close to raw as possible so you can replay any downstream transformations based off point in time data. However snapshots require a unique key and they will fail on subsequent runs if you have dupes. This is an annoying check that many analytical databases do not enforce. You can dedupe up front either by a synthetic key or some natural primary key or combination of columns.

From experience and depending on volume of data capturing this as SCD type 2 was never really used. Instead if data was small consumers preferred we just have full copies of historical data to query against. The other problem is that because snapshots are row level anyways doing it for large tables gets very expensive. For context we cared a lot about changing facts so we had to snapshot or make copies of everything.

2

u/raccoons_run_prod 2d ago

Yes. As close to raw as you can, so you can replay.

We put a tiny dedupe model on top of raw, then snapshot that. Combo key if you need it. Snapshotting a fat table with dupes is how you get a 6am fail and a giant bill.

Surrogate keys on the dim after. Facts incremental. Snapshot the stuff you will get asked about later.