I have a table of data that changes frequently. I don’t care about changes to data that is less than 30 days old. No need to track those versions. But once the data is about 30 days old I want to track changes to it. Is this possible using a single temporal table? Or would I need to have my ‘real time’ table with everything in it and a separate ‘archive table’ that we would ETL in only the 30 day old data and turn the temporal on that on?
Or would I need to have my ‘real time’ table with everything in it and a separate ‘archive table’ that we would ETL in only the 30 day old data/datetime and turn the temporal on that on?
You have to use this approach since when you configure temporal tables, you cannot setup an 30 days offset on some date attribute.
Therefore, as you pointed in the initial question, the logical workaround is to create a scheduled process which MERGE changes into the archival table that has enabled TEMPORAL TABLE functionality, so it will maintain a history of changes.