Choosing an Azure Synapse Option help

Question

Hi all,

we are starting to understand Azure Synapse and how it works and there appears to be a lot of options you can take for the journey.

So far I have the following

Option 1, Import data into Datalake (Azure Serverless SQL Pool) Then create External Tables and SQL SPs for transformation within Serverless. Finally import dims and facts into Dedicated SQL Pool. This data is then Imported into Power BI

Option 2 Import data into Datalake (Azure Serverless SQL Pool) Then create Notepooks for transformation. Finally import dims and facts into Dedicated SQL Pool. This data is then Imported into Power BI

(And so far I'm thinking Option 1 seems the best since people are more used to SQL)

Also because we import into Power BI. could an idea be to pause and unpause the dedicated SQL Pool to save money there since we don't need it on all the time?

However now I have got to the Lake database option and I'm not sure how this would work so if anyone could tell me which one of these is the best option (Or if I am on the right track completely)

Option 3, Import data into Datalake (Azure Serverless SQL Pool) Then create External Tables and SQL SPs for transformation. Finally copy dims and facts into Lake Database, meaning we don't need a dedicated sql pool at all?

Option 4, Import data into Datalake (Azure Serverless SQL Pool) Then create Notebooks for transformation. Finally copy dims and facts into Lake Database, meaning we don't need a dedicated sql pool at all?

Option 5, Import data into Datalake (Azure Serverless SQL Pool) Transform AND create reporting dims and facts in the Lake Database, meaning we don't need a dedicated sql pool at all?

Option 6, Import data into Datalake (Azure Serverless SQL Pool) Transform in the Lake Database, Import reporting dims and facts into the Dedicated SQL Pool

There are probably countless more options. The big question is, if you use the Lake database, does that mean you don't need to use the dedicated sql pool?

What is the point of the Lake database?

Any help to shed some light on the best options would be great. Bearing in mind we aren't dealing with billions of records here. Its not a HUGE data set

Answer 1

Hi@Debbie Edwards ,

Welcome to Microsoft Q&A platform and thanks for posting your question here.
It's great to hear that you are exploring Azure Synapse Analytics and its capabilities. I can help you understand the different options you have listed and provide guidance on the best approach for your use case.

Based on option which is best for you will depend on your specific needs and requirements.

Here is a summary of the options that you have mentioned:

Option 1: Import data into the Datalake (Azure Serverless SQL Pool), create external tables and SQL SPs for transformation, and then import dims and facts into the dedicated SQL pool. This is a good option if you need the high performance and scalability of a dedicated SQL pool for your reporting. However, it is also the most expensive option.

Option 2: Import data into the Datalake (Azure Serverless SQL Pool), create notebooks for transformation, and then import dims and facts into the dedicated SQL pool. This is a good option if you want to use notebooks for data transformation. However, it is still more expensive than using the Lake database alone.

Option 3: Import data into the Datalake (Azure Serverless SQL Pool), create external tables and SQL SPs for transformation, and then copy dims and facts into the Lake database. This is a good option if you want to use the Lake database for data transformation and analysis. It is also a more cost-effective option than using a dedicated SQL pool.

Option 4: Import data into the Datalake (Azure Serverless SQL Pool), create notebooks for transformation, and then copy dims and facts into the Lake database. This is a good option if you want to use notebooks for data transformation and the Lake database for data analysis. It is also a more cost-effective option than using a dedicated SQL pool.

Option 5: Import data into the Datalake (Azure Serverless SQL Pool), transform and create reporting dims and facts in the Lake database. This is the most cost-effective option, but it is also the most complex. You will need to use the Lake database to transform your data and to create your reporting dims and facts.

Option 6: Import data into the Datalake (Azure Serverless SQL Pool), transform in the Lake database, and import reporting dims and facts into the dedicated SQL pool. This is a good option if you need the high performance and scalability of a dedicated SQL pool for your reporting. However, it is more expensive than using the Lake database alone.

Which option is best for you?

The best option for you will depend on your specific needs and requirements. Here are some things to consider:

• Data size and complexity: For complex dataset, you may need to use the dedicated SQL pool to achieve the performance and scalability that you need. However, if you have a smaller and less complex dataset, the Lake database may be a sufficient option.
• Budget: The dedicated SQL pool is more expensive than the Lake database. If you are on a tight budget, the Lake database may be a better option for you.
• Technical expertise: The dedicated SQL pool is more complex to set up and manage than the Lake database. If you do not have the necessary technical expertise, the Lake database may be a better option for you.
• Use case: If you need to use the dedicated SQL pool for other purposes, such as running complex queries, then you will need to use it for your reporting as well. However, if you only need the dedicated SQL pool for reporting, you may be able to save money by using the Lake database alone.

Reference:

• Lake Database documentation: https://learn.microsoft.com/en-us/azure/synapse-analytics/database-designer/concepts-lake-database
• Dedicated SQL pool documentation: https://learn.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-overview-what-is
• Serverless SQL pool vs Dedicated SQL Pool: https://www.experts-exchange.com/articles/38116/Azure-Synapse-Serverless-Pool-vs-Dedicated-Pool-Unleashing-the-Power-of-Data-Analytics.html
• Synapse SQL Architecture: https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/overview-architecture

I hope this information helps you. Let me know if you have any further questions or concerns.
Hope this helps. Do let us know if you any further queries.

---

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.