Azure Synapse Analytics Explained for Beginners

Azure has a habit of naming one thing after several. Azure Synapse Analytics is the opposite problem: one name for several things. People say "Synapse" and mean a data warehouse, or a notebook environment, or a pipeline tool, and all three are right. This guide takes the service apart so each piece makes sense.

What it is

Microsoft describes Azure Synapse as an enterprise analytics service that brings together SQL technologies for data warehousing, Spark for big data, and pipelines for data integration, with one web interface, Synapse Studio, over all of them (Microsoft Learn: what is Azure Synapse Analytics).

The container for everything is the workspace. A workspace lives in one region and is tied to an Azure Data Lake Storage account, which it uses as its default storage (Microsoft Learn: Synapse terminology). Inside it are the engines:

A workspace can have any number of dedicated SQL pools. It always has exactly one serverless SQL pool, there from the moment the workspace is created.

Dedicated SQL pool: the warehouse

A dedicated SQL pool is a data warehouse of the classic kind: tables, loaded with data, queried with T-SQL. What makes it different from an ordinary database is how it runs a query (Microsoft Learn: dedicated SQL pool architecture).

• A Control node receives the query, plans it, and splits it up.
• Compute nodes, between 1 and 60 of them depending on the size chosen, do the work in parallel.
• Every table is split into 60 distributions. A query is divided into 60 smaller queries, one per distribution.
• Storage is separate from compute. The pool can be scaled up or down, and paused, in which case you pay only for storage.

The pool's size is set in Data Warehouse Units, a bundled measure of CPU, memory and I/O. Data is held in columnar storage, which suits analytical queries that scan many rows and few columns. Data warehouse concepts covers the background.

Why distribution matters

With 60 distributions behind every table, the question for each table is: which rows go where? There are three options.

The choice controls two things at once: how evenly the data is spread, and how much data has to be moved between nodes to answer a join. The script below models both with real data, the 2,964,624 yellow taxi trips of January 2024 and the 265-row zone table (NYC TLC: trip record data). It places trips into 60 distributions three ways, and counts how many trips end up in a different distribution from the zone row they join to, assuming zones is hash-distributed on its key. It is a model of the idea, with a simple hash, not a run of Synapse.

import zlib
import numpy as np
import pandas as pd

D = 60                                   # a dedicated SQL pool divides every table into 60 distributions
trips = pd.read_parquet("yellow_tripdata_2024-01.parquet", columns=["PULocationID"])
trips["trip_id"] = np.arange(len(trips))
zones = pd.read_csv("taxi_zone_lookup.csv", usecols=["LocationID"])


def hashed(values):
    # crc32 of the key, modulo 60: a stand-in for the pool's own hash function
    home = {v: zlib.crc32(str(v).encode()) % D for v in pd.unique(values)}
    return values.map(home).to_numpy()


fact_layouts = {
    "round robin":            (trips["trip_id"] % D).to_numpy(),
    "hash on trip_id":        hashed(trips["trip_id"]),
    "hash on PULocationID":   hashed(trips["PULocationID"]),
}
zone_row_home = hashed(trips["PULocationID"])     # where each trip's zone row lives if zones is hash-distributed on LocationID

print(f"{len(trips):,} trips, {len(zones)} zones, {D} distributions; an even share is {1 / D:.1%}\n")
print(f"{'trips distributed by':24} {'largest distribution':>21} {'trips away from their zone row':>31}")
for name, home in fact_layouts.items():
    sizes = np.bincount(home, minlength=D)
    print(f"{name:24} {sizes.max() / len(trips):21.1%} {(home != zone_row_home).mean():31.1%}")

2,964,624 trips, 265 zones, 60 distributions; an even share is 1.7%

trips distributed by      largest distribution  trips away from their zone row
round robin                               1.7%                           98.3%
hash on trip_id                           1.7%                           98.3%
hash on PULocationID                      9.6%                            0.0%

Three results, three lessons.

Round-robin is perfectly even and useless for joins. Each distribution holds 1.7% of the trips, and 98.3% of trips are in a different distribution from their zone. To join the two tables, the engine has to move data between nodes first. Microsoft says the same: joins on round-robin tables require reshuffling data.

Hashing on a unique column is no better for this join. The spread is even and the rows are still in the wrong places, because the hash is on the wrong column.

Hashing on the join column puts every trip beside its zone, so nothing moves. The price is skew: trips are concentrated in a few zones, and the largest distribution holds 9.6% of the rows against an even share of 1.7%. The query waits for that distribution.

This is the central trade-off of the dedicated pool, and the reason the third option exists. A small table like zones is better replicated: a full copy on every compute node, so any fact table can join to it without moving anything, whatever the fact table is hashed on. The fact table is then free to be hashed on a column with many distinct, evenly spread values.

CREATE TABLE dbo.FactTrip
(
    TripId        BIGINT        NOT NULL,
    PickupZoneId  INT           NOT NULL,
    PickupTime    DATETIME2     NOT NULL,
    FareAmount    DECIMAL(9,2)  NOT NULL
)
WITH (DISTRIBUTION = HASH(TripId), CLUSTERED COLUMNSTORE INDEX);

CREATE TABLE dbo.DimZone
(
    ZoneId    INT           NOT NULL,
    Borough   VARCHAR(50)   NOT NULL,
    ZoneName  VARCHAR(100)  NOT NULL
)
WITH (DISTRIBUTION = REPLICATE, CLUSTERED COLUMNSTORE INDEX);

Getting data in efficiently is its own subject; see loading data into Synapse with ADF.

Serverless SQL pool: query the lake in place

The serverless SQL pool has no tables of its own to load. It is a query service over files already in the data lake: Parquet, Delta Lake and delimited text, read with T-SQL (Microsoft Learn: serverless SQL pool).

• Nothing to provision. The endpoint exists in every workspace.
• Pay per query. There is no charge for reserved resources; you are charged for the data your queries process.
• Good for exploring files, building a logical warehouse of views over the lake, and simple transformations.

The cost model changes how you write queries. A query that reads a whole folder of CSV is charged for all of it. The same data as Parquet, partitioned by date, lets the engine read only the columns and folders it needs, and so costs less.

Spark pools

A Spark pool is managed Apache Spark for notebooks and batch jobs in Python, Scala, SQL and .NET. Microsoft's description gives the practical numbers: instances start in about 2 minutes for pools under 60 nodes, and shut down by default 5 minutes after the last job (Microsoft Learn: Apache Spark in Synapse). Pools can autoscale, and because data lives in the lake, stopping a pool loses nothing.

Spark and SQL share the lake. A table defined over files by Spark can be queried by the serverless SQL pool. Apache Spark explained covers the engine.

Pipelines

Synapse pipelines are the same data integration engine as Azure Data Factory, inside the workspace: activities, triggers, data flows, more than 90 connectors. They orchestrate the other engines, running a notebook, then a stored procedure, then a copy. The differences from standalone Data Factory are covered in Synapse pipelines vs standalone ADF.

Which engine for which job

A typical arrangement uses all four: pipelines land raw files in the data lake, Spark cleans and shapes them, the dedicated pool serves the modelled result, and the serverless pool answers one-off questions about the raw data. Data lake vs data warehouse vs lakehouse explains how the storage patterns relate.

Where Fabric fits

Anyone learning Synapse now should read one notice. Microsoft's documentation pages for the dedicated and serverless SQL pools open with a statement about Microsoft Fabric Data Warehouse, advising that if you are new to data warehousing you should start with Fabric, and that existing dedicated SQL pool workloads can upgrade to it (Microsoft Learn: dedicated SQL pool overview).

That does not make Synapse knowledge obsolete. Many organisations run it, the documentation is maintained, and the ideas, distributed tables, separating storage from compute, querying files in place, carry directly into Fabric and into every other cloud warehouse. It does mean that for a new project, Fabric is the platform Microsoft is pointing to.

Common beginner mistakes

• Leaving a dedicated pool running. It bills for reserved compute whether queried or not. Pause it when idle.
• Using a dedicated pool like a transactional database. It is built for large scans and bulk loads, not many small inserts and lookups.
• Round-robin for everything. Fine for staging, slow for joins between large tables.
• Hashing on a low-cardinality or skewed column.
• Querying raw CSV with the serverless pool and being surprised by the bill.
• Treating the workspace as one product. Each engine has its own cost, security and tuning.

Azure Synapse and cloud-based data warehousing are covered in the Data Engineering course.

Sources

• What is Azure Synapse Analytics? (Microsoft Learn) — Microsoft Learn
• Azure Synapse Analytics terminology (Microsoft Learn) — Microsoft Learn
• Dedicated SQL pool architecture (Microsoft Learn) — Microsoft Learn
• What is dedicated SQL pool in Azure Synapse Analytics? (Microsoft Learn) — Microsoft Learn
• Serverless SQL pool in Azure Synapse Analytics (Microsoft Learn) — Microsoft Learn
• Apache Spark in Azure Synapse Analytics (Microsoft Learn) — Microsoft Learn
• TLC Trip Record Data (NYC Taxi and Limousine Commission) — NYC Taxi and Limousine Commission