January 7, 2019 at 7:21 am
I've been playing with Temporal tables for a bit as I think it may help solve an issue we are seeing. I have a large amount of data with rows that change occasionally. Instead of storing a snapshot of the data daily as was originally requested, we want to basically turn it into a Type 2 slowly changing dimension, storing the data as it sits today, and providing all of the historical versions of the rows in case they are needed. < 10% of the rows are ever updated so storing multiple duplicate copies seems rather a waste. Currently the table is 2TB with about 2.4 Billion rows and we're starting to have some significant performance and space issues. We expect to have to retain this for 7-10 years and the 2.4 B rows is from the past 5 months... I expect to be able to get this under control and make it much easier to query when reconfigured as a temporal table.
Temporal tables seem like they are a good candidate for this so I've been experimenting with them. The only issue I'm running into currently is that I can all of the historical row version, but I want to get them into a version history table using the days when the data actually changed over the last few months, not the system versioned data time of when they are inserted.
Does anyone have a method or blog post to point me to of either 1) writing all the data and then going back to change the version timestamps, or 2) adding the correct timestamps as the table is being loaded?
Thanks,
-Luke.
January 8, 2019 at 8:40 pm
Hi Luke. I've thought about ways to be able to take history data and place them into a temporal table as a type of migration step. What have you tried? Have you created the temporal table structure, turned off system versioning so the tables are separated, then add your data with your date data, then turn system versioning back on? As long as your primary key is correct and your start and end dates are correct, I think this should work. I have not completely tested this process and I won't have time right away to do so, but I'd be happy to help once I find the time.
-Tom
January 9, 2019 at 9:41 am
Yeah, that's generally what I'm going to try to do. I hadn't really seen much on this on the web and was wondering if I was "just doing it wrong". I'm working on setting up some tests to make this work, but got pushed off to other priorities. I hope to get back to this by the end of the week.
Thanks,
-Luke.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply