topic Approach to monthly data snapshots in Data Engineering

Approach to monthly data snapshots

dpc — Mon, 22 Jul 2024 08:08:45 GMT

Hello
I'm building a datawarehouse with all the usual facts and dimensions
It will flush (truncate) and rebuild on a monthly basis
Users have the need to not only view the data now but also view it historically i.e. what it was a point in time
My initial thought was to use time travel functionality but, am I right in saying that that's the wrong approach here, and we should really archive or apply a date filter to the data?

So, an alternate archiving strategy would be better?

Thanks

Re: Approach to monthly data snapshots

Witold — Mon, 22 Jul 2024 08:21:08 GMT

This sounds like a SCD-2. Pick the approach which fits best for your use case.

FWIW, time traveling isn't supposed to be used for business queries, only for technical (e.g. select yesterday's changes). Due to the fact that historical entries are not persistent and will be removed ("vacuum") after a defined period.

Re: Approach to monthly data snapshots

szymon_dybczak — Mon, 22 Jul 2024 08:25:29 GMT

Agree with Witold. Best practice is to use time travel for recoveries from issues between 7 and 30 days. (you can set much higher period, but it's not recommended).
Beyond that use snapshots for archival history.

Re: Approach to monthly data snapshots

dpc — Thu, 25 Jul 2024 12:16:51 GMT

Great, thanks