SQL SERVER Source table create point in table of 70 gb table

Question

Hi Guys,

We want to create snapshot of an table everyday in azure synapse on sQldw, what would be best approach for daily load of 70GB table from on premise sql,oracle or sap hana, we want load the data in mill seconds max in minutes.

Hint -Similar to Time Travel in data bricks or Temporal table approach in SQL

Regards,
Navin

Answer 1

Hello @Anonymous ,

Thanks for the ask and using the forum .

Time travel is an upcoming feature, but until that, inserting a new copy of the table every day wouldn’t be terrible

Each year retention would be

70GB * 356 days/year / (1024 GB/TB) * $23/TB /Month = $573/Month

To speed up the data load and reduce the data transfer, if you know the diffs from the source system, you could combine the diffs with the previous day’s full copy to generate the next day.

Thanks
Himanshu