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

Navin Dondapati 836 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.
2,872 questions
Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
6,705 questions

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. HimanshuSinha-msft 18,531 Reputation points Microsoft Employee
    2020-12-09T01:25:58.377+00:00

    Hello @Navin Dondapati ,

    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