temporary vs non-temporary staging sql dedicated pool tables

kosmos 246 Reputation points
2022-10-08T09:14:42.37+00:00

I am ingesting data from Azure SQL DB to Synapse DWH and I have seen people are following 2 approaches:

1) Azure SQL DB --> Data Lake -----------------------------> temporary staging SQL dedicated pool table --> production SQL dedicated pool table
2) Azure SQL DB --> Data Lake --> SQL dedicated pool --> non-temporary staging SQL dedicated pool table --> production SQL dedicated pool table

I would like to know what is the best recommended practice. I have seen posts where they mention staging tables are always temporal and other posts where they aren't.

Thanks in advance

Azure SQL Database
Azure Data Lake Storage
Azure Data Lake Storage
An Azure service that provides an enterprise-wide hyper-scale repository for big data analytic workloads and is integrated with Azure Blob Storage.
1,426 questions
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,696 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. Vinodh247 13,801 Reputation points
    2022-10-08T09:29:34.98+00:00

    Hi

    Thanks for reaching out to Microsoft Q&A.

    There are some good answers for a similar question asked earlier, can you go through these and check if this helps?

    https://social.msdn.microsoft.com/Forums/officeocs/en-US/ca7863dc-7373-46a3-a1fe-36e3c71e2fd5/staging-table-vs-temp-table?forum=sqlintegrationservices

    Please Upvote and Accept as answer if the reply was helpful, this will be helpful to other community members.