Suggestion on maintaining staging in Azure Data Lake or in Dedicated SQL Pool service

Rachana Ramya 1 Reputation point
2021-05-14T07:51:04.68+00:00

Hi Team,

We have proposed the below architecture for one of the Project.
96540-image.png

  • For one of the source we need to do the increment load based on the target business key column comparison. To maintain a staging area what is the best practice/Suggested approach. Is it maintaining staging in the Azure Data Lake Level or in the Dedicated SQL Pool level.

Thanks in Advance:)

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,113 questions
Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
9,067 questions
{count} votes

1 answer

Sort by: Most helpful
  1. KranthiPakala-MSFT 46,402 Reputation points Microsoft Employee
    2021-05-19T18:52:59.617+00:00

    Hi @RachanaRamya-389,

    Thanks much for your response and details.

    With the recent drop in storage cost for SQL Pool, there’s not a clear cost incentive to use a Data Lake for staging. So this question really comes down to whether you otherwise want a Data Lake.

    There’s nothing wrong with using a traditional DW architecture where all the storage tiers are stored in the RDBMS. But that staging data is typically not available for other teams to use. Putting it in a Data Lake is extra work, but enables other projects to access the data, and it allows that data to be combined with non-DW data.

    Hope the above info helps.


    Please don’t forget to Accept Answer and Up-Vote wherever the information provided helps you, this can be beneficial to other community members.