Approach to monthly data snapshots

[dpc]

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

[Witold · Accepted solution]

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.

[szymon_dybczak]

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. 

[dpc]

Great, thanks