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?
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.