temporary vs non-temporary staging sql dedicated pool tables

Question

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

Answer 1

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.