Synapse Serverless SQL Pool

Question

I am trying to find the limits of what I can and can't do with a serverless SQL Pool

Can you do type 2 uploads without needing to use the dedicated SQL Pool?

e.g.

PersonKey PersonID PersonName StartDate EndDate MostRecentFlag

1 244 Sue Smith 2020-01-01 2021-02-26 0

2 244 Sue Jones 2020-02-26 1

And if so can anyone point me to a good online resource that explains how best to do it?

Answer 1

You need to understand that there are two kinds of SQL pool: on-demand (serverless) and provisioned (dedicated). The on-demand SQL pool allows you to query data as it is, without the need for pre-defined structures or compute resources.

The dedicated SQL pool allows for more advanced analytics with a pre-provisioned, fully managed experience.

In your case, I assumae that you are asking about type 2 SCD uploads in a serverless SQL pool, it might not be as straightforward as it would be in a dedicated SQL pool due to the nature of on-demand queries and lack of certain DML operations like MERGE. However, you can use combinations of SELECT, INSERT, and UPDATE statements to implement type 2 SCD logic manually, by creating and managing the historical data, and flagging the most recent records.

Here is an example : https://techcommunity.microsoft.com/t5/azure-synapse-analytics-blog/transforming-your-data-lake-implementing-slow-change-dimension/ba-p/3718996

Serverless SQL pool benefits :

Based on the documentation, the serverless SQL pool in Azure Synapse Analytics is a powerful tool for various data professionals to explore, analyze, and transform data directly in the data lake. It facilitates basic discovery and exploration, enabling quick reasoning about data in diverse formats such as Parquet, CSV, and JSON. It also supports the creation of a logical data warehouse, providing a relational abstraction over raw or disparate data without the need for relocating and transforming it, ensuring an always up-to-date view of your data. Moreover, the serverless SQL pool allows for simplified and scalable data transformations using T-SQL. This capability is instrumental for data engineers in optimizing data transformation pipelines, data scientists in understanding the data’s content and structure, and data analysts and BI professionals in exploring data and creating reports using familiar tools and languages like T-SQL and Power BI.

Serverless SQL pool limitations : I let you discover them in this article.

If you are looking for the best practices : https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/best-practices-serverless-sql-pool