Hi Team, We have proposed the below architecture for one of the Project. 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:)

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.

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

Rachana Ramya 1

Hi Team,

We have proposed the below architecture for one of the Project.

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:)

KranthiPakala-MSFT 46,442 Reputation points Microsoft Employee

2021-05-14T17:28:36.723+00:00

Hi @RachanaRamya-389,

Thanks for using Microsoft Q&A forum and posting your question.

Could you please provide few additional details so that we can better assist on this ask. When you say Staging are you using polybase? How large the increment is? Is your dedicated pool always up and is it the same as your actual target Dedicated SQL Pool? Is your dedicated SQL pool a legacy one (previously called as Azure SQL Datawarehouse) or is it created from Synapse Workspace? Is your staging data being transformed?

Looking forward to your response.

Thanks
Rachana Ramya 1 Reputation point

2021-05-17T06:16:21.463+00:00

Hi @KranthiPakala,

Thanks for your reply .

We are not using PolyBase in our current project, Directly loading data from source to staging using ADF. The daily increment load is less than 50k. Yes, The Dedicated SQL Pool is always up and running and same as our actual target dedicated SQL pool. We are using legacy dedicated SQL pool and not from Synapse workspace. Yes, we will again perform some transformations from staging to target DW.

I hope my response will be helpful to you to provide us the suggestion on using staging area in Azure Data Lake or Dedicated SQL pool.

Thanks

1 answer

KranthiPakala-MSFT 46,442 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.
Please sign in to rate this answer.
KranthiPakala-MSFT 46,442 Reputation points Microsoft Employee

2021-05-21T18:02:28.79+00:00

Hi @Rachana Ramya ,

Just checking to see if the above info was helpful? Do let us know if you have further query.

KranthiPakala-MSFT 46,442 Reputation points Microsoft Employee

2021-05-26T14:08:28.88+00:00

Hi there,

We still have not heard back from you. Just wanted to check if the above information was helpful? If it answers your query, please do click “Accept Answer” and/or Up-Vote, as it might be beneficial to other community members reading this thread. And, if you have any further query do let us know.
Sign in to comment

Share via

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

1 answer