Azure Synapse Analytics Deep Dive: Dedicated vs Serverless SQL Pools & Spark Pools

Azure Synapse Analytics is not one product but one workspace that stitches three very different compute engines over a shared data lake: a dedicated SQL pool — a provisioned, massively parallel data warehouse; a serverless SQL pool — a query-on-demand engine that reads files in place and bills you per terabyte scanned; and an Apache Spark pool — a managed cluster for big-data transformation and machine learning. Bolted around them are Synapse Pipelines for orchestration and Synapse Link for near-real-time ingestion of operational data. The interview question is almost never "how do I use Synapse"; it is "given this workload, which of the three engines, and how do you tune it so it is fast and cheap".

Getting that answer right means understanding trade-offs that do not exist in a single-engine warehouse. A dedicated pool charges you for provisioned compute whether or not a query runs, so you pause it; a serverless pool charges nothing at rest but bills every terabyte a careless SELECT * drags off the lake. A dedicated pool spreads every table across sixty distributions, so a badly chosen hash key silently shuffles gigabytes on every join; a Spark pool ignores distributions entirely and instead needs Delta and partition folders. This guide walks the four things an interviewer will actually probe — the pick-the-engine decision, the dedicated pool's DWU-and-distribution model, the serverless pool's pay-per-TB file querying, and Spark plus the orchestration and Link glue — then closes on distribution and partition tuning, each paired with a Solution-Tail interview answer: code, a step-by-step trace, an output table, then a concept-by-concept breakdown of why it works.

When you want hands-on reps immediately after reading, drill the data-warehouse practice library →, rehearse lake-query shaping on the Spark SQL practice set →, and sharpen your query-tuning instincts on the optimization practice set →.

---

On this page

• Why Azure Synapse unifies the analytics stack in 2026
• Dedicated SQL pool — DWU, distributions & columnstore
• Serverless SQL pool — query the lake, pay per TB
• Spark pools, Synapse Pipelines & Synapse Link
• Distribution & partition tuning
• Cheat sheet — Synapse recipes
• Frequently asked questions
• Practice on PipeCode

---

1. Why Azure Synapse unifies the analytics stack in 2026

Synapse is a workspace of three engines, not a warehouse — the whole skill is picking the right one per workload

The one-sentence invariant: Synapse gives you three independent compute engines over one shared ADLS Gen2 lake, and the engineering decision is always which engine to point at a given workload and how to size its cost model. Everything else — Pipelines, Link, the studio UI — is glue around those three engines. An engineer who cannot say, in one breath, "provisioned MPP warehouse, pay-per-TB file query, or managed Spark" is going to reach for the wrong tool and either overspend or under-perform.

The three compute engines — what each is for.

• Dedicated SQL pool. A provisioned, massively parallel (MPP) relational data warehouse — the descendant of Azure SQL Data Warehouse. You buy compute in Data Warehouse Units (DWU), tables are physically distributed across sixty distributions, and it is the right engine for a governed star-schema EDW feeding high-concurrency BI.
• Serverless SQL pool. An always-on, zero-provisioning T-SQL engine that queries files sitting in the lake in place. You pay only for the terabytes each query processes. It is the right engine for ad-hoc lake exploration and a "logical data warehouse" over Parquet without loading anything.
• Apache Spark pool. A managed Spark cluster with notebooks (PySpark, Scala, Spark SQL, .NET). It autoscales and auto-pauses, reads and writes Delta/Parquet, and is the right engine for heavy transformation, data science, and ML feature pipelines.

The glue around the engines.

• Synapse Pipelines. Azure Data Factory embedded in the workspace — copy activities, mapping data flows, notebook and stored-procedure activities, and schedule / tumbling-window / event triggers. This is how you orchestrate a run across the three engines.
• Synapse Link. Near-real-time, no-ETL replication of operational data (Azure Cosmos DB, Azure SQL, Dataverse) into an analytical store you can query from Spark or serverless without touching the transactional system.
• One lake, one metastore. All engines read the same ADLS Gen2 storage, and Spark and serverless SQL share a metastore so a table one creates is visible to the other.

The cost models you must contrast.

• Dedicated = provisioned. Billed per DWU per hour while the pool is running, regardless of query volume; you pause it to stop compute charges. Storage is billed separately.
• Serverless = consumption. No idle cost at all; billed per terabyte of data processed by each query, so cost is a direct function of how much of the lake you scan.
• Spark = per-cluster-uptime. Billed for the vCore-hours the pool is alive; autoscale and a short auto-pause window keep the bill tied to actual work.

What interviewers listen for.

• Do you frame Synapse as "one workspace, three engines" rather than "Microsoft's warehouse"? — senior signal.
• Do you match provisioned vs consumption cost model to the workload's concurrency and idle profile? — required framing.
• Do you know that serverless has no storage of its own and reads files in place? — the single most common misconception to clear.
• Do you reach for Spark for transformation and dedicated for serving, instead of forcing one engine to do everything? — senior signal.

Worked example — routing three workloads to three engines

Detailed explanation. The clearest way to internalise Synapse is to take three concrete workloads and route each to the correct engine with a one-line justification. The same data lake underlies all three; only the engine and its cost model change. This is exactly the shape of the "design a Synapse solution" whiteboard question.

Question. A retailer has (a) a nightly star-schema refresh feeding 400 concurrent Power BI users, (b) a data scientist who wants to explore six months of raw clickstream Parquet once, and (c) a daily job that cleans and joins 2 TB of raw logs into curated Delta. Which engine for each, and why?

Input.

workload	shape	concurrency	cadence
a. BI serving	governed star schema	400 users	always-on
b. ad-hoc exploration	raw Parquet in lake	1 analyst	one-off
c. heavy transform	2 TB raw → curated	1 job	daily batch

Code.

a -> dedicated SQL pool   (provisioned MPP, result-set cache, high concurrency)
b -> serverless SQL pool  (OPENROWSET over Parquet, pay only for the TB scanned once)
c -> Apache Spark pool     (autoscaling transform, write Delta back to the lake)

Step-by-step explanation. Workload (a) is high-concurrency, low-latency serving of a curated model, so it justifies provisioned compute and columnstore — a dedicated pool, paused outside business hours if idle. Workload (b) is a single one-off scan; provisioning a warehouse for it would waste money, so serverless wins — you pay once for the terabytes that single query touches and nothing afterwards. Workload (c) is CPU-heavy transformation of semi-structured data, which is Spark's home turf; it autoscales for the batch, writes Delta, and pauses.

Output.

workload	engine	why
a	dedicated SQL pool	provisioned, high-concurrency serving
b	serverless SQL pool	zero idle cost, pay-per-TB one-off scan
c	Spark pool	scalable transform, Delta output

Rule of thumb. Serve with dedicated, explore with serverless, transform with Spark — and let all three share the same lake so data never has to be copied between them.

---

2. Dedicated SQL pool — DWU, distributions & columnstore

The dedicated pool spreads every table across 60 distributions — DWU is the compute dial and the distribution style decides how joins move data

The feature that defines a dedicated SQL pool is its MPP shared-nothing architecture: a single control node parses your query and coordinates, while your data lives across a fixed sixty distributions that compute nodes own and process in parallel. Everything about performance flows from one fact — a join is fast when the rows it matches already sit on the same distribution, and slow when Synapse has to shuffle rows across the network to line them up.

DWU — the compute dial.

• What it buys. A Data Warehouse Unit (DWU, or DWUxxxc in Gen2) bundles CPU, memory, and IO. You scale from DW100c up to DW30000c; raising DWU adds compute nodes that take ownership of the sixty distributions.
• Nodes vs distributions. The sixty distributions never change. At the smallest size one compute node owns all sixty; at the largest, sixty nodes own one distribution each. More DWU therefore means more parallelism and more memory per query, not more shards.
• Pause to stop billing. DWU is billed per hour while running; because compute and storage are decoupled, you pause the pool overnight and pay only for storage.

The three distribution styles — the core exam question.

• HASH. Rows are assigned to a distribution by a deterministic hash of a chosen column. This is the choice for large fact tables: two hash-distributed tables joined on their shared hash column are joined without data movement because matching keys land on the same distribution.
• ROUND_ROBIN. Rows are spread evenly across distributions with no hashing — the default when you specify nothing. Loads are fast, but any join or aggregation forces a shuffle. Correct for transient staging tables, wrong for tables you join repeatedly.
• REPLICATE. A full copy of the table is cached on every compute node. Joins against it need no movement because every node already has all the rows. Correct for small dimensions (a good rule is under ~2 GB compressed); wrong for large tables because the copy cost explodes.

Columnstore — the default storage.

• Clustered columnstore index (CCI). Dedicated pool tables default to a CCI: column-oriented, heavily compressed, organised into rowgroups of up to ~1,048,576 rows. It gives huge scan and aggregation speed for analytic queries.
• Rowgroup quality is everything. Compression and scan speed depend on full rowgroups. Loading under memory pressure, or trickle-inserting a few rows at a time, produces small rowgroups that bloat the table and slow scans — a problem you fix by loading in large batches and rebuilding the index.

Worked example — creating a hash-distributed columnstore fact table

Detailed explanation. The canonical dedicated-pool DDL sets three things at once: the distribution style, the distribution column, and the index. A large fact table almost always wants HASH on the column it is most often joined on, with the default clustered columnstore index. Getting this right at create time is the single highest-leverage tuning decision.

Question. Create a sales_fact table distributed so that joins to it on customer_key avoid data movement, stored as columnstore.

Input.

column	type	role
sale_id	bigint	degenerate id
customer_key	int	join key to dim_customer
sale_date	date	partition / filter column
amount	decimal(12,2)	measure

Code.

CREATE TABLE dbo.sales_fact
(
    sale_id       bigint       NOT NULL,
    customer_key  int          NOT NULL,
    sale_date     date         NOT NULL,
    amount        decimal(12,2) NOT NULL
)
WITH
(
    DISTRIBUTION = HASH(customer_key),
    CLUSTERED COLUMNSTORE INDEX
);

Step-by-step explanation. DISTRIBUTION = HASH(customer_key) tells Synapse to hash every row's customer_key and place the row on the resulting distribution, so all sales for a given customer sit together. When dim_customer is also hash-distributed on customer_key (or replicated), the join is distribution-local and no rows cross the network. CLUSTERED COLUMNSTORE INDEX stores the table column-wise in compressed rowgroups, which is what makes SUM(amount) GROUP BY scans fast. The engine writes the rows across the sixty distributions according to the hash.

Output.

property	value
distribution	HASH(customer_key) across 60 distributions
index	clustered columnstore (compressed rowgroups)
join to dim_customer on customer_key	no data movement

Rule of thumb. Hash-distribute a big fact on the key it is most often joined and grouped by; leave columnstore as the default and only rethink it for tiny tables.

Synapse interview question on eliminating data movement

Question. A query joins sales_fact (round-robin distributed) to dim_customer on customer_key and is slow; the plan shows a ShuffleMoveOperation moving billions of rows. Explain the cause and redistribute the tables so the join is movement-free.

Solution Using hash distribution aligned on the join key

Code.

-- Redistribute the fact by the join key using CTAS
CREATE TABLE dbo.sales_fact_v2
WITH
(
    DISTRIBUTION = HASH(customer_key),
    CLUSTERED COLUMNSTORE INDEX
)
AS SELECT * FROM dbo.sales_fact;

-- Small dimension: replicate so every node has a full copy
CREATE TABLE dbo.dim_customer_v2
WITH
(
    DISTRIBUTION = REPLICATE,
    CLUSTERED COLUMNSTORE INDEX
)
AS SELECT * FROM dbo.dim_customer;

-- Now the join is distribution-local
SELECT c.segment, SUM(f.amount) AS revenue
FROM   dbo.sales_fact_v2 f
JOIN   dbo.dim_customer_v2 c ON c.customer_key = f.customer_key
GROUP  BY c.segment;

Step-by-step trace.

stage	before (round-robin)	after (hash + replicate)
fact placement	rows spread arbitrarily	rows placed by hash(customer_key)
dim placement	round-robin / distributed	full copy on every node
join step	ShuffleMove billions of rows	local join, no movement
GROUP BY	after shuffle	on already-local rows

1. Under round-robin, matching customer_key values land on different distributions, so Synapse must shuffle the fact (or the dimension) across the network before it can join — the ShuffleMoveOperation.
2. CTAS with DISTRIBUTION = HASH(customer_key) rewrites the fact so every row is placed by the hash of its join key.
3. REPLICATE on the small dimension puts a complete copy on every compute node, so each distribution already has the customer rows it needs.
4. With both sides aligned, the join executes locally on each distribution and only the small grouped result is returned to the control node.

Output:

metric	round-robin	hash + replicate
data movement	billions of rows shuffled	none
dominant cost	network shuffle	local scan + aggregate

Why this works — concept by concept:

• Distribution alignment — hashing both join sides on the same column co-locates matching keys, so the join never crosses the network; this is the central MPP optimization.
• Replicate for small dimensions — a full per-node copy trades a little storage for zero movement, which is almost always the right deal under ~2 GB.
• CTAS to redistribute — you cannot alter a distribution in place, so CREATE TABLE AS SELECT is the idiom for re-shaping an existing table.
• Columnstore preserved — keeping the CCI on the rebuilt table retains compression and fast aggregation for the GROUP BY.
• Cost — the one-time CTAS is O(rows) I/O, after which every join is O(local rows) with zero shuffle, versus O(shuffled rows) network cost on every query before.

Warehouse
Topic — data-warehouse
MPP data-warehouse and distribution problems

Practice →

Optimize Topic — optimization Query-plan and data-movement optimization problems

Practice →

---

3. Serverless SQL pool — query the lake, pay per TB

Serverless SQL reads files in place and bills per terabyte — the whole game is scanning less of the lake

The serverless SQL pool is the mirror image of the dedicated pool: it has no provisioned compute and no storage of its own, so it costs nothing at rest and charges only for the terabytes each query reads off the lake. There is nothing to load — you point T-SQL at Parquet, CSV, or Delta files in ADLS Gen2 and query them where they lie. Because the bill is a direct function of bytes scanned, every tuning move is really the same move: read fewer bytes.

Two ways to read a file.

• OPENROWSET(BULK ...). Ad-hoc, no DDL — name a path (or wildcard) and a FORMAT (PARQUET, CSV, DELTA) and query it inline. Ideal for one-off exploration and for prototyping before you commit to a schema.
• External tables. For repeated access you create an EXTERNAL DATA SOURCE (the storage location + credential), an EXTERNAL FILE FORMAT (Parquet/CSV/Delta), and a CREATE EXTERNAL TABLE that gives the files a named, schema-on-read table other queries and BI tools can reference.

Cost control — scanning less.

• Columnar formats win. Parquet stores data column-wise, so SELECT a, b reads only those columns' bytes; a CSV forces a full-row read. Converting raw CSV to Parquet routinely cuts scan cost by an order of magnitude.
• Partition pruning. Lay files out in Hive-style folders (year=2026/month=03/...) and filter on filepath() / filename() so serverless skips whole folders instead of scanning the lake. This is the single biggest lever on the per-TB bill.
• Statistics. Serverless auto-creates and uses statistics on external data to build better plans; keeping them current improves join and filter estimates.

Writing back — CETAS.

• CREATE EXTERNAL TABLE AS SELECT (CETAS). Serverless can transform and write results back to the lake as Parquet, which is how you build a curated "gold" layer or materialise an expensive query so downstream reads scan far less.
• Logical data warehouse. External tables + views over the lake give you a queryable warehouse-like surface with zero data duplication into a provisioned store.

Worked example — querying Parquet in place with OPENROWSET

Detailed explanation. The everyday serverless pattern is an OPENROWSET over a Parquet folder with a WITH clause naming the columns you actually need. Because it is Parquet and you list only two columns, serverless reads only those columns' bytes — a fraction of a full scan.

Question. Sum amount by region from Parquet files under sales/ in the lake, reading only the columns you need.

Input.

abfss://[e-mail]/sales/
  year=2026/month=01/part-0001.parquet
  year=2026/month=02/part-0001.parquet   (columns: sale_id, region, amount, ...)

Code.

SELECT region, SUM(amount) AS revenue
FROM OPENROWSET(
    BULK 'https://lake.dfs.core.windows.net/data/sales/**',
    FORMAT = 'PARQUET'
) WITH (
    region varchar(40),
    amount decimal(12,2)
) AS rows
GROUP BY region;

Step-by-step explanation. OPENROWSET(BULK ..., FORMAT='PARQUET') points serverless at the sales/ tree; the ** wildcard recurses through the partition folders. The WITH (...) clause projects only region and amount, so Parquet's columnar layout lets serverless read just those two column chunks and skip the rest of every row. The engine scans the files in place, aggregates, and bills you for the bytes of those two columns across all matched files.

Output.

region	revenue
EMEA	812450.00
APAC	553120.00
AMER	967310.00

Rule of thumb. Always list the columns you need in the WITH clause over Parquet — a bare SELECT * reads every column's bytes and multiplies the per-TB bill.

Synapse interview question on cutting serverless cost

Question. A serverless query over three years of Parquet scans 1.8 TB and costs too much, but analysts only ever query one month at a time. The files are laid out as .../year=YYYY/month=MM/. How do you cut the bytes scanned without moving data?

Solution Using partition pruning with filepath()

Code.

SELECT SUM(amount) AS revenue
FROM OPENROWSET(
    BULK 'https://lake.dfs.core.windows.net/data/sales/year=*/month=*/*.parquet',
    FORMAT = 'PARQUET'
) WITH (amount decimal(12,2)) AS rows
WHERE rows.filepath(1) = '2026'      -- year=  folder
  AND rows.filepath(2) = '03';       -- month= folder

Step-by-step trace.

step	without pruning	with filepath() filter
folders considered	all year=/month=	year=2026/month=03 only
files opened	36 months of Parquet	1 month of Parquet
bytes scanned	~1.8 TB	~50 GB
billed data	1.8 TB	0.05 TB

1. The * tokens in the BULK path become positional wildcards; filepath(1) refers to the first * (the year= folder) and filepath(2) to the second (month=).
2. Filtering filepath(1) = '2026' AND filepath(2) = '03' lets serverless prune to a single folder before opening any file, so 35 of 36 months are never read.
3. Only the amount column is projected, so even within the surviving folder just one column's bytes are scanned.
4. The result is identical to the unpruned query but touches roughly 3% of the data, and the per-TB bill drops in proportion.

Output:

metric	full scan	pruned scan
bytes scanned	~1.8 TB	~50 GB
relative cost	1.0x	~0.03x

Why this works — concept by concept:

• Partition pruning — matching folder names against filepath() eliminates whole directories before any file is opened, so pruned data is never billed.
• filepath() positional wildcards — each * in the path is addressable by index, turning a Hive-style folder layout into a cheap predicate.
• Column projection — naming only amount in WITH means Parquet reads a single column chunk, compounding the saving.
• No data movement — the layout change is a folder convention, not an ETL job; the same files serve both pruned and full queries.
• Cost — bytes scanned drop from O(all partitions) to O(matched partitions), and serverless billing is linear in bytes, so cost falls in the same ratio.

Spark SQL
Topic — spark-sql
SQL-over-files and lake-query problems

Practice →

Warehouse Topic — data-warehouse Logical-data-warehouse and external-table problems

Practice →

---

4. Spark pools, Synapse Pipelines & Synapse Link

Spark transforms, Pipelines orchestrate, and Synapse Link streams operational data in — the three engines share one lake and one metastore

Where the SQL pools serve and query, the Apache Spark pool is Synapse's transformation and data-science engine, and it is wired into the same lake as everything else. The unifying idea to state in an interview: Spark, serverless SQL, and Pipelines all read the same ADLS Gen2 storage, and Spark and serverless share a metastore, so a table written by one engine is queryable by another without a copy — and Synapse Link feeds that lake near-real-time operational data with no ETL of your own.

Apache Spark pools.

• Managed clusters. You define a pool (node size Small/Medium/Large, min/max nodes) and it autoscales to the job and auto-pauses after an idle window, so you pay for vCore-hours actually used.
• Notebooks and languages. PySpark, Scala, Spark SQL, and .NET for Spark in interactive notebooks or as pipeline activities; the standard tool for cleaning, joining, and enriching big semi-structured data.
• Delta and Parquet. Spark reads and writes Delta Lake and Parquet in the lake, giving ACID transactions and time travel on the curated layers it builds.

The shared metastore — the integration that surprises people.

• Spark table → serverless external table. When Spark creates a table (Parquet or Delta) in the shared metastore, Synapse automatically exposes it to the serverless SQL pool as an external table — so an analyst can query Spark output in T-SQL with no extra setup.
• One definition, two engines. This is what makes the "transform in Spark, serve/explore in SQL" pattern seamless: there is one schema, not two.

Synapse Pipelines — orchestration.

• ADF, embedded. Pipelines are Azure Data Factory inside the workspace: Copy activities move data, Notebook activities run Spark, SQL pool stored-procedure activities run T-SQL, and mapping data flows do visual transformation.
• Triggers. Schedule, tumbling-window, and storage-event triggers start pipelines, and integration runtimes provide the compute for movement.

Synapse Link — near-real-time HTAP.

• Cosmos DB analytical store. Synapse Link for Azure Cosmos DB auto-syncs the transactional store into a column-oriented analytical store you query from Spark or serverless — analytics with no ETL and no load on the OLTP workload.
• Synapse Link for SQL. Change-feed replication from Azure SQL / SQL Server 2022 lands operational changes in a dedicated SQL pool for analytics, again without you writing pipelines.

Worked example — Spark writes Delta, serverless reads it

Detailed explanation. The signature Synapse integration is the hand-off from Spark to SQL through the shared metastore. Spark cleans raw data and writes a Delta table into a lake database; that table appears to serverless SQL automatically, so an analyst queries it in T-SQL without any export. This is the pattern that makes the multi-engine workspace pay off.

Question. In a Spark notebook, curate raw sales into a Delta table registered in a lake database, then query it from the serverless SQL pool.

Input. A raw Parquet folder raw/sales/ with sale_id, customer_key, sale_date, amount.

Code.

df = (spark.read.parquet("abfss://[e-mail]/raw/sales/")   # Spark pool notebook (PySpark)
           .filter("amount > 0"))

spark.sql("CREATE DATABASE IF NOT EXISTS lakehouse")
(df.write
   .format("delta")
   .mode("overwrite")
   .saveAsTable("lakehouse.sales_curated"))    # registered in shared metastore

-- --- Serverless SQL pool, no extra setup ---
SELECT TOP 10 customer_key, SUM(amount) AS revenue
FROM lakehouse.dbo.sales_curated          -- Spark's Delta table, visible in T-SQL
GROUP BY customer_key
ORDER BY revenue DESC;

Step-by-step explanation. Spark reads the raw Parquet, filters out non-positive amounts, and writes a Delta table via saveAsTable into the lakehouse database — which lives in the shared metastore. Because the metastore is shared, Synapse surfaces lakehouse.sales_curated to the serverless SQL pool as an external table automatically. The T-SQL query then aggregates that table directly, reading the Delta files in the lake with no copy, export, or additional DDL.

Output.

engine	action	result
Spark pool	writes lakehouse.sales_curated (Delta)	curated table in metastore
serverless SQL	SELECT ... GROUP BY on it	top customers by revenue, no copy

Rule of thumb. Transform and write Delta in Spark, then let serverless SQL query it through the shared metastore — one table definition serves both engines.

Synapse interview question on real-time analytics without ETL

Question. Product wants dashboards over live orders held in Azure Cosmos DB, but the DBA forbids analytical queries against the transactional container because they would spike RU cost and hurt the app. How do you serve near-real-time analytics without building an ETL pipeline?

Solution Using Synapse Link for Azure Cosmos DB

Code.

orders = (spark.read
    .format("cosmos.olap")                       # auto-synced analytical store, never OLTP
    .option("spark.synapse.linkedService", "CosmosLink")
    .option("spark.cosmos.container", "orders")
    .load())

(orders.groupBy("region")
       .sum("total")
       .write.format("delta").mode("overwrite")
       .saveAsTable("lakehouse.orders_by_region"))

Step-by-step trace.

stage	transactional store (OLTP)	analytical store (Synapse Link)
writes	app inserts orders	auto-synced within minutes
storage layout	row-based, RU-metered	column-based, decoupled
analytics read	forbidden (RU spike)	cosmos.olap free of OLTP
freshness	live	near-real-time

1. Enabling the analytical store on the Cosmos container makes Azure auto-replicate every write into a column-oriented copy, isolated from the transactional store's request-unit budget.
2. Spark reads via the cosmos.olap format, so the query hits the analytical store and never touches the OLTP container — no RU spike, no app impact.
3. The read is near-real-time because the sync lag is minutes, not a nightly batch, so dashboards see fresh orders.
4. Aggregations are written back as Delta for the serving layer, all without a single hand-built ETL pipeline.

Output:

requirement	met by
no OLTP impact	analytical store isolation
near-real-time	auto-sync (minutes)
no ETL	Synapse Link replication

Why this works — concept by concept:

• Analytical store isolation — the column-oriented copy is decoupled from request units, so analytics cannot degrade the transactional workload.
• No-ETL replication — Synapse Link maintains the sync for you, removing the pipeline you would otherwise build and monitor.
• cosmos.olap read path — pointing Spark at the analytical format guarantees queries hit the replica, not the live container.
• HTAP freshness — minutes-scale sync gives near-real-time analytics without the latency of a nightly batch.
• Cost — analytics cost is O(analytical-store scan), fully separate from the O(RU) transactional budget, so the two never compete.

Spark SQL
Topic — spark-sql
Spark SQL transformation problems

Practice →

PySpark Topic — pyspark PySpark Delta and lakehouse problems

Practice →

---

5. Distribution & partition tuning

Even distribution kills skew, and right-sized partitions keep columnstore healthy — this is where a slow dedicated pool becomes fast

Once tables are hash-distributed and columnstore-backed, the remaining performance work is making the distribution even and the partitions large enough. The failure mode interviewers love is subtle: a plausible-looking hash key produces data skew (some distributions hold far more rows than others), so the whole query waits on the busiest distribution; and over-eager partitioning shatters columnstore rowgroups so scans slow down. Say the invariant plainly: you want the sixty distributions balanced and every partition big enough to fill its rowgroups.

Choosing a hash key — four criteria.

• High cardinality. Many distinct values so rows spread across all sixty distributions; hashing on country (a handful of values) leaves most distributions empty.
• Even distribution of values. No single value dominating — a key where 40% of rows share one value creates a hot distribution regardless of cardinality.
• Used in joins / GROUP BY. Pick the column you most often join and aggregate on, so those operations become movement-free.
• Not frequently updated. The distribution column should be stable; changing it would mean moving the row to a different distribution.

Detecting skew.

• DBCC PDW_SHOWSPACEUSED. Reports rows per distribution for a table; a wide gap between the busiest and emptiest distribution is skew you must fix by choosing a better key (via CTAS).
• Watch the slowest distribution. MPP query time is bounded by the busiest distribution, so 5% skew can cost far more than 5% of runtime.

Partitioning — and the over-partition trap.

• Why partition. Range partitions (usually on a date) enable partition elimination on filters and, crucially, partition switching — a metadata-only swap of a loaded staging partition into the live table for near-instant loads and deletes.
• The 60 × 1M trap. Data is already split across 60 distributions, and each distribution's columnstore wants ~1M rows per rowgroup. So a partition needs roughly 60 million rows to fill one rowgroup per distribution; partitioning a 10-million-row table by day produces tiny, inefficient rowgroups. Partition coarsely (month, not day) unless the table is huge.

Other levers.

• Materialized views pre-compute expensive aggregations and are auto-maintained; result-set caching returns identical queries instantly; workload management (workload groups / importance) protects critical queries' memory and concurrency.

Worked example — detecting and fixing a skewed distribution key

Detailed explanation. The classic tuning bug is hash-distributing on a low-cardinality or lopsided column. Here a fact table is distributed on country_code; a handful of countries hold most of the rows, so a few distributions are enormous and the rest idle. The fix is to redistribute on a high-cardinality, evenly spread key.

Question. orders_fact is HASH(country_code) and queries are slow. Confirm the skew and redistribute so the sixty distributions are balanced.

Input.

distribution group	rows	note
busiest distribution	41,000,000	holds US + a few large markets
median distribution	900,000	most countries
emptiest distribution	0	no country hashes here

Code.

-- 1. Detect skew: rows per distribution
DBCC PDW_SHOWSPACEUSED('dbo.orders_fact');

-- 2. Redistribute on a high-cardinality, even key (order_id) via CTAS
CREATE TABLE dbo.orders_fact_v2
WITH
(
    DISTRIBUTION = HASH(order_id),
    CLUSTERED COLUMNSTORE INDEX,
    PARTITION ( order_month RANGE RIGHT FOR VALUES
                ('2026-01-01','2026-02-01','2026-03-01') )
)
AS SELECT * FROM dbo.orders_fact;

Step-by-step explanation. DBCC PDW_SHOWSPACEUSED prints rows per distribution and exposes the gap — 41M on the busiest versus 0 on some others — confirming country_code is a bad key. order_id is unique and monotonic, so hashing on it spreads rows almost perfectly across all sixty distributions. The CTAS also range-partitions by order_month (coarse, monthly) so each partition still holds enough rows per distribution to fill columnstore rowgroups. The rebuilt table is balanced and every join on order_id stays movement-free.

Output.

metric	HASH(country_code)	HASH(order_id)
busiest distribution rows	41,000,000	~1,050,000
skew (busiest / median)	~45x	~1.05x
query time	bounded by hot distribution	balanced across 60

Rule of thumb. If one value can dominate the key, it will dominate a distribution — hash on something unique and evenly spread, and confirm with DBCC PDW_SHOWSPACEUSED.

Synapse interview question on fast loads and deletes

Question. You must load a fresh day of data into a large orders_fact and drop the oldest month, both without a long-running INSERT/DELETE that blocks readers or rebuilds columnstore. What Synapse feature makes each operation near-instant, and what must be true for it to work?

Solution Using partition switching

Code.

-- Stage new data in a table with an IDENTICAL schema, distribution, and index
CREATE TABLE dbo.orders_stage
WITH (DISTRIBUTION = HASH(order_id), CLUSTERED COLUMNSTORE INDEX,
      PARTITION (order_month RANGE RIGHT FOR VALUES ('2026-04-01','2026-05-01')))
AS SELECT * FROM dbo.orders_source WHERE order_month >= '2026-04-01';

-- Switch the staged partition INTO the live table (metadata-only, instant)
ALTER TABLE dbo.orders_stage SWITCH PARTITION 2 TO dbo.orders_fact PARTITION 2;

-- Age out the oldest month by switching it OUT to an empty table, then drop
ALTER TABLE dbo.orders_fact SWITCH PARTITION 1 TO dbo.orders_archive PARTITION 1;

Step-by-step trace.

operation	naive approach	partition switch
load new month	INSERT ... SELECT (rebuilds rowgroups)	SWITCH staged partition in
drop old month	DELETE WHERE month=... (logged, slow)	SWITCH partition out, drop table
readers blocked	yes, long transaction	no, metadata-only
duration	minutes–hours	milliseconds

1. The staging table is created with identical distribution, index, and partition boundaries — the precondition for a switch; mismatched structure makes SWITCH fail.
2. ALTER TABLE ... SWITCH PARTITION moves a partition by metadata only: no rows are copied, so it completes almost instantly regardless of size.
3. Loading is switching the freshly built staging partition in; ageing out is switching the old partition out to a throwaway table you then drop.
4. Because nothing is physically rewritten, columnstore rowgroups stay intact and readers are never blocked by a long transaction.

Output:

goal	mechanism	cost
add newest month	SWITCH in from staging	metadata-only
remove oldest month	SWITCH out, drop	metadata-only

Why this works — concept by concept:

• Partition switching — moving a partition is a catalog pointer change, not a data copy, so load and delete become O(1) instead of O(rows).
• Structural match precondition — identical distribution, index, and boundaries let Synapse guarantee the switch is valid without touching rows.
• Rowgroup preservation — because data is never rewritten, columnstore compression built during staging is retained in the live table.
• Non-blocking — a metadata operation holds no long transaction, so BI readers keep querying throughout the load.
• Cost — the switch itself is O(1); the only real work is building the staging partition once, off to the side, before the swap.

Partitioning
Topic — partitioning
Partitioning and partition-switch problems

Practice →

Optimize Topic — optimization Skew-detection and warehouse-tuning problems

Practice →

---

Cheat sheet — Synapse recipes

Hash-distributed columnstore fact (dedicated pool).

CREATE TABLE dbo.fact (id bigint, k int, amt decimal(12,2))
WITH (DISTRIBUTION = HASH(k), CLUSTERED COLUMNSTORE INDEX);

Redistribute an existing table (CTAS).

CREATE TABLE dbo.fact_v2
WITH (DISTRIBUTION = HASH(order_id), CLUSTERED COLUMNSTORE INDEX)
AS SELECT * FROM dbo.fact;

Query Parquet in place (serverless).

SELECT region, SUM(amount)
FROM OPENROWSET(BULK 'https://lake.dfs.core.windows.net/data/sales/**',
                FORMAT='PARQUET')
     WITH (region varchar(40), amount decimal(12,2)) AS r
GROUP BY region;

External table over the lake (serverless).

CREATE EXTERNAL DATA SOURCE lake WITH (LOCATION='https://lake.dfs.core.windows.net/data');
CREATE EXTERNAL FILE FORMAT pq WITH (FORMAT_TYPE = PARQUET);
CREATE EXTERNAL TABLE dbo.sales (region varchar(40), amount decimal(12,2))
WITH (LOCATION='sales/', DATA_SOURCE=lake, FILE_FORMAT=pq);

Spark writes Delta, serverless reads it.

df.write.format("delta").mode("overwrite").saveAsTable("lakehouse.sales_curated")

SELECT * FROM lakehouse.dbo.sales_curated;   -- Spark's table, visible in serverless SQL

Partition switch (near-instant load).

ALTER TABLE dbo.stage SWITCH PARTITION 2 TO dbo.fact PARTITION 2;

Engine picker.

Situation	Engine
High-concurrency star-schema BI serving	dedicated SQL pool
One-off / ad-hoc query over lake files	serverless SQL pool
Heavy transform, ML, Delta curation	Apache Spark pool
Near-real-time analytics on OLTP data	Synapse Link

---

Frequently asked questions

What is Azure Synapse Analytics?

Azure Synapse Analytics is an integrated analytics workspace that combines three compute engines over one ADLS Gen2 data lake: a dedicated SQL pool (provisioned MPP data warehouse), a serverless SQL pool (pay-per-terabyte query engine that reads lake files in place), and an Apache Spark pool (managed clusters for transformation and ML). It adds Synapse Pipelines for orchestration and Synapse Link for near-real-time ingestion of operational data, so you can ingest, transform, serve, and explore without leaving one workspace.

Dedicated vs serverless SQL pool — which do I use?

Use a dedicated SQL pool when you have a curated star-schema warehouse serving high-concurrency BI: it is provisioned MPP with columnstore and result-set caching, and you pause it when idle. Use a serverless SQL pool for ad-hoc exploration and a logical data warehouse over lake files: it has no infrastructure, costs nothing at rest, and bills per terabyte a query scans. In short, dedicated is provisioned-and-paused for steady serving; serverless is consumption-based for spiky or exploratory querying.

What is a DWU?

A Data Warehouse Unit (DWU, written DWxxxc in Gen2) is the bundled compute measure — CPU, memory, and IO — you buy for a dedicated SQL pool, scalable from DW100c to DW30000c. Your data always lives across sixty distributions; raising DWU adds compute nodes that take ownership of those distributions, increasing parallelism and per-query memory rather than changing the number of shards. DWU is billed per hour while the pool runs, so you pause the pool to stop compute charges.

Which table distribution should I choose?

Use HASH on a large fact table, choosing a high-cardinality, evenly distributed column that you frequently join and group by, so joins avoid data movement. Use REPLICATE for small dimensions (roughly under 2 GB) so every compute node has a full copy and joins stay local. Use ROUND_ROBIN only for transient staging tables where fast loading matters more than join performance; it forces a shuffle on every join.

How is a Spark pool different from the SQL pools?

An Apache Spark pool is a general-purpose distributed compute cluster for transformation, data science, and ML, driven by notebooks in PySpark, Scala, or Spark SQL, and it reads and writes Delta and Parquet. It has no notion of the dedicated pool's sixty distributions; instead you tune it with Delta and partition folders. Because Spark shares a metastore with the serverless SQL pool, a table Spark writes is immediately queryable in T-SQL with no export.

What is Synapse Link?

Synapse Link provides near-real-time analytics over operational data with no ETL you build yourself. Synapse Link for Azure Cosmos DB auto-syncs the transactional container into a column-oriented analytical store you query from Spark or serverless, isolated from the app's request-unit budget; Synapse Link for SQL replicates changes from Azure SQL or SQL Server 2022 into a dedicated pool. In both cases you get fresh operational data for analytics without impacting the source system.

Practice on PipeCode

Pipecode.ai is Leetcode for Data Engineering — every Synapse idea above, from hash-distributing a fact to prune data movement, to partition-pruning a serverless scan, to switching a partition in for an instant load, maps to a hands-on practice room where you build the query against real graded inputs. PipeCode pairs each reading with 450+ DE-focused problems and a real-time scoring engine, so your answer to "how would you kill the data skew in this dedicated pool?" holds up under a senior interviewer's depth probes.

Practice data-warehouse problems now →
Spark SQL drills →