Azure Synapse Analytics

Several tools under one roof

Analytics work rarely uses one engine.

Data has to be moved from source systems. Some of it needs processing that SQL handles awkwardly. Reporting queries want a warehouse. Exploration wants a way to look at files without committing to loading them.

Assembled from separate services, this means several environments, several security configurations and several places to look when something goes wrong.

Synapse puts these in one workspace. The engines remain distinct, and what they share is the workspace, the security model and the connection to storage.

Understanding the parts separately is the way to make sense of it, because the name covers four different things.

Dedicated SQL pools

This is the warehouse engine. Capacity is provisioned, data is distributed across nodes, and SQL queries run against it.

The characteristics are those of a provisioned warehouse. Capacity is chosen, it costs while running, and it can be paused when not needed. Performance depends on how data is distributed across the nodes, which is a design decision made when tables are created.

That distribution choice deserves emphasis. Tables distributed badly cause queries to move large amounts of data between nodes, which is the usual explanation when a distributed warehouse performs poorly. It is a modelling decision, not a setting to adjust afterwards.

Serverless SQL

This queries files in storage directly. No capacity is provisioned and billing follows the data a query processes.

Its value is removing a step. Files land in a data lake from various sources. Before deciding whether to load them into a warehouse, someone needs to know what is in them. Serverless SQL answers that with a query rather than a project.

It also serves data queried occasionally, where the effort of loading and maintaining it in a warehouse is disproportionate to how often anyone asks.

It is not a replacement for the warehouse. Files in general purpose storage are not organised for repeated analytical querying, and data used constantly by many people performs better loaded.

Spark pools

Spark handles processing that SQL does not suit.

Deeply nested or irregular files. Transformations expressed more naturally in code. Machine learning. Work where a procedural language is simply a better fit for what is being expressed.

Having Spark in the same workspace means the choice between SQL and Spark is made per task. Without it, the choice tends to be made once per platform, and then everything gets forced through whichever engine was chosen.

Spark pools are clusters, so they are sized, started and stopped. The cost model is that of a cluster rather than of a query.

Pipelines

Pipelines move data. They connect to source systems, extract, and land data in storage or the warehouse on a schedule.

The capability is closely related to Azure Data Factory, and having it inside the workspace means the movement of data and the analysis of it are built and monitored together rather than in separate services.

Choosing between the parts

The practical question with Synapse is not whether to use it but which engine to use for what.

Steady reporting on data used constantly suits a dedicated pool. Exploration and occasional queries over files suit serverless. Processing that SQL expresses awkwardly suits Spark. Movement suits pipelines.

Getting this wrong is the main way costs and performance disappoint. A dedicated pool left running for occasional queries is expensive. Complex transformation forced into SQL because the warehouse is where the data lives is painful to write and maintain.

Who uses it

Synapse is used by data engineers, analysts and platform teams on Azure. It suits organisations already invested in Azure, where identity, storage and monitoring integrate with what is in place.

Points to consider

Synapse is an Azure service. The official documentation is the reference for capabilities, limits and pricing.

Microsoft has since introduced Fabric, which covers overlapping ground with a different structure. Both exist, and the official documentation includes guidance on choosing between them. For anyone starting a new analytics platform on Azure, reading that guidance before committing is worthwhile.

The workspace is shared; the engines are not interchangeable. Each has its own performance characteristics, cost model and design considerations, and treating them as one system leads to expensive misunderstandings.

Distribution design in dedicated pools is the decision that most affects whether the warehouse performs, and it is difficult to change once tables hold data.

Getting started

The official documentation covers each engine separately along with the workspace concepts. Querying a few files with serverless SQL is the quickest way to see something useful, since it requires no capacity to be provisioned and no data to be loaded.