What are the best practices when it comes to modifying a huge (more than 500 M records) table in Azure SQL server in deployment.
By modification I mean the table gets updated for past few days (let's say 10 days) and the new data has to be written it to the huge. Note that the table already has some data for past 9 days in this example. In this case, I see these options to update the huge (target table):
Create a staging/temp (source) table with the last 10 days data and then merge, using the MERGE command, it to the target table. Here there is an issue that as there are many columns with no unique primary key, the comparison has to be done on multiple columns. In this case, does it make sense to use multiple columns in the join or use a single concatenated or hashed column created from all these columns?
delete the last 10 days of data and then insert the source table to target table. But I am bit cautious here since as this involves delete, the records are deleted first and then insert happens. What if the delete is successful and insert failed due to any database issue (may be due to a sudden downtime). Consider this as a deployed job where even if it fails for a day, it could recover in the next day's run. With MERGE command, if it fails, due to the roll back mechanism, the data is preserved at least.
delete the last 10 days of data and then append the table using databricks spark insert. Among the time consumption, this strategy was the best. However, as this too involves delete first, how to take care of the scenario if delete is successful and append fails.
truncate the entire table and then repopulate the entire table.
I see many advantages using MERGE but it take a long time for the operation to complete even with indexes are in place. The delete and append option gave the best timings however as the delete and append are independent operations, there is always a risk of losing the data as the append can fail. Is there a way to combine delete and insert in a single command so that it runs as a single block thereby ensuring that if any of these operation fail, it can rollback?
The SQL server is Microsoft SQL Azure (RTM) - 12.0.2000.8
What are the best options suggested here? Thank you in advance for any answers/comments.