I'm trying to use temporal tables for our data lake environment to track changing data over time. The first hurdle I hit is loading historical data to seed the tables. Everything is loading as "now" instead of as of the point in the past. Searching online the only solution I have found is to turn off system versioning, disable the period in the table, make changes to the table to fix the dates, then turn system versioning back on and hope that SQL Server will let me. Is there a better way to force the datetime values to be what I need them to be? Also, going forward, we are loading data that was updated yesterday and captured last night and being loaded into the lake early this morning. I need to use yesterday's date as the effective date for the temporal table effective date, not the current timestamp. Same issue, how can I force the datetime values?
4
-
1If you need this type of tight control over what is essentially a type 2 slowly changing dimension, then you usually roll your own. I don't know if temporal tables is the right fit for you.– Jonathan FiteMar 12, 2020 at 14:57
-
How do you load your data ? can you provide an example of the script (T-SQL) used to get the data ?– Dominique BoucherMar 12, 2020 at 15:15
-
Dominique, we are using Informatica to load the data. For the specific situation I'm facing, the data is coming from an external source, so the timeline is: Daily market events happen, at close of market day file is generated automatically, our ETL process starts early am following day to load data. The desire is to use the system versioned table because of the historical query functionality. Rolling my own still presents all kinds of issues, not least of which is managing the data migration to a history table, or keeping everything in one, I can't reproduce the "as of" functionality.– Mike NicewarnerMar 12, 2020 at 17:52
-
Jonathan, I'm afraid you might be right. This is a prime use-case for temporal tables, but only if I can override the default system timestamp when tracking changes. If this were an online system, the system timestamp certainly works, but I'm in a warehouse situation, the gap between business event and data lake table insert are separated by a lot of time.– Mike NicewarnerMar 12, 2020 at 17:54
Add a comment
|
1 Answer
The time in a temporal table is "system time" not "business time". That's the distinction that the feature name "System-Versioned Temporal Table" is getting at.
You inserted the data today, so it's versioned as of today. You can use the temporal table to track the changes you make going forward, and retain change history, but it doesn't replace a slowly-changing dimension for business analytics. As you noted, Temporal Tables belong in the "Data Lake" or "Raw" or "Staging" layer.
If you want to add additional history representing changes to the data before your initial load, you can turn system versioning off temporarily while you load data directly into the history table.
answered Mar 12, 2020 at 15:18
