I have 2 tables one having 800 Million Records and other having 150 Million Records, both these tables are full refresh tables on a weekly basis. Both the tables are date partitioned, having USI on 2 columns each.
These 2 tables store only 5 years of data from the load date.
Load strategy Currently what we have is Delete the entire tables using Delete all and load the data.
Do you think we will have some savings if we approach with below one?
Instead of deleting, if we drop the table and re-create with fresh DDL with only 5 years of partition weekly and load data.
any inputs on this please, Mainly i would like to know, if there is a SI on a table, when we do a Delete or Delete All, will it follow fast path delete or not.
you don't give much information what changes on a weekly basis. Will old data be changed?
How long takes your current approach?
Hi Ulrich thanks for your response, Its a full refresh, we clear all the data and re-load the 5 years of data from foundation tables. again next week we repeat the same process.
My question here is, which process helps me in clearing the data better & faster, Delete table or Drop table.