October 13, 2016 at 4:34 pm
In my transform in my etl I have created permanent tables and prefixed them with "Temp" and i use these as stores to load data from the source system and then update my staging data. I know can create temptables in memory by setting the retain same connection and switch validation off but ive still to find a way that suites my needs using this. I dont like having lots of temp storage tables hanging about so im thinking of going down the path of creating permanent tables , populate them, update staging data using them and then delete them from schema. Just wondering what best practice would be?
October 13, 2016 at 6:09 pm
ps_vbdev (10/13/2016)
In my transform in my etl I have created permanent tables and prefixed them with "Temp" and i use these as stores to load data from the source system and then update my staging data. I know can create temptables in memory by setting the retain same connection and switch validation off but ive still to find a way that suites my needs using this. I dont like having lots of temp storage tables hanging about so im thinking of going down the path of creating permanent tables , populate them, update staging data using them and then delete them from schema. Just wondering what best practice would be?
If you go the route of creating permanent working tables, consider making a "scratch" database that's set to the SIMPLE Recovery Model, NOT putting anything in that database that must persist for longer that it's being used so that you ever have to back it up, and making sure that the table names reflect which package they're being used for so as to avoid contention with other packages.
And, don't forget to clean the litter box once in a while. I built a stored procedure in my "DBA" database that will rename any and every table that hasn't been used in a week (and it runs every day) or any other object that was created a week ago. This gives people a chance to recover from the mistake of trying to use scratch as a repository. The proc also automatically drops anything that has been renamed for a week.
--Jeff Moden
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply