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

Dondapati, Navin 281 Reputation points
2020-12-08T02:30:26.787+00:00

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

Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
4,473 questions
Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
9,753 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. HimanshuSinha-msft 19,381 Reputation points Microsoft Employee
    2020-12-09T01:25:58.377+00:00

    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