Azure Synapse Architecture: Pools, Workspaces, and Storage

The workspace as a security and resource boundary

A Synapse workspace is the container for everything. When you create a workspace, Azure provisions:

• A managed identity for the workspace (used to access storage and other services)
• A connection to a primary ADLS Gen2 account and filesystem you specify
• A built-in (serverless) SQL pool — always available, no additional provisioning
• A Synapse Studio endpoint (a URL at web.azuresynapse.net)

Everything you create inside Synapse — dedicated pools, Spark pools, pipelines, notebooks, SQL scripts — lives within this workspace. Role assignments on the workspace control who can access Synapse Studio. Separate RBAC assignments on the ADLS Gen2 account control who can read and write files.

Compute: three distinct engines

Synapse contains three compute engines, each designed for a different workload pattern. They share the workspace identity and linked storage, but their internal architectures are completely different.

Inside the dedicated SQL pool

The dedicated SQL pool uses a massively parallel processing (MPP) architecture. It has a single control node that receives queries and distributes work, and between 1 and 60 compute nodes that process data in parallel. The number of compute nodes is determined by the DWU tier you select.

Data in a dedicated pool is distributed across all compute nodes according to a distribution strategy you specify per table. There are three distribution options:

• Hash distribution — rows are assigned to a node based on a hash of a chosen column. Best for large fact tables that are frequently joined on the same column.
• Round-robin distribution — rows are distributed evenly in sequence. The default and simplest option; good for staging tables.
• Replicated distribution — a full copy of the table is stored on every compute node. Best for small dimension tables that are joined frequently.

Choosing the wrong distribution for large tables is the most common cause of poor dedicated pool performance because it forces data movement operations (DMS) across nodes during joins.

-- Create a fact table with hash distribution on customer_id
-- This avoids data movement when joining to a customer-keyed dimension
CREATE TABLE dbo.FactOrders
(
    order_id        BIGINT          NOT NULL,
    customer_id     INT             NOT NULL,
    order_date      DATE            NOT NULL,
    order_total     DECIMAL(18, 2)  NOT NULL
)
WITH
(
    DISTRIBUTION = HASH(customer_id),
    CLUSTERED COLUMNSTORE INDEX
);

-- Create a small dimension table as replicated
-- Every compute node will hold a full copy
CREATE TABLE dbo.DimCustomer
(
    customer_id     INT             NOT NULL,
    customer_name   NVARCHAR(200)   NOT NULL,
    country         NVARCHAR(100)   NOT NULL
)
WITH
(
    DISTRIBUTION = REPLICATE,
    CLUSTERED COLUMNSTORE INDEX
);

Inside the serverless SQL pool

The serverless SQL pool has no dedicated compute nodes you manage. When you submit a query, Synapse spins up execution against a shared, elastic infrastructure. The engine reads files directly from ADLS Gen2, Azure Blob Storage, or other supported storage — Parquet, CSV, JSON, Delta, and ORC formats are all supported natively.

Because there is no persistent compute, the serverless pool cannot maintain in-memory caches between queries. Each query starts cold. For frequently repeated queries against large files, this means the serverless pool will be slower than a dedicated pool with result-set caching enabled.

The serverless pool’s killer feature is external tables. You define a schema once and then query the table like any SQL table — but the data never leaves ADLS:

-- Create a data source pointing to your lake
CREATE EXTERNAL DATA SOURCE MyLake
WITH (
    LOCATION = 'https://mydatalake.dfs.core.windows.net/silver'
);

-- Create a file format definition
CREATE EXTERNAL FILE FORMAT ParquetFormat
WITH (
    FORMAT_TYPE = PARQUET
);

-- Create an external table — no data is copied
CREATE EXTERNAL TABLE dbo.ExternalOrders
(
    order_id     BIGINT,
    customer_id  INT,
    order_date   DATE,
    order_total  DECIMAL(18, 2)
)
WITH (
    LOCATION = '/orders/',
    DATA_SOURCE = MyLake,
    FILE_FORMAT = ParquetFormat
);

-- Query it just like a regular table
SELECT TOP 100 * FROM dbo.ExternalOrders WHERE order_date >= '2025-01-01';

Inside the Spark pool

A Synapse Spark pool is a managed Apache Spark cluster. When you define a pool, you choose a node size (Small, Medium, Large, XLarge, XXLarge — each mapping to a specific number of vCores and GB of RAM per node), a minimum and maximum node count for auto-scale, and an auto-pause timeout.

When you run a notebook or Spark job, Synapse allocates a cluster from the pool. If auto-scale is enabled, the cluster grows and shrinks based on the workload. When no jobs are running and the auto-pause timeout expires, all nodes are deallocated — you pay nothing for idle time.

The Spark pool uses a pool-level Spark configuration. You can customise Spark settings, install additional Python or Scala packages via a requirements file, and mount ADLS paths as mount points. Notebooks running on the same pool share the same SparkSession within a session, which means DataFrames created in one notebook cell are available in subsequent cells.

# PySpark in a Synapse notebook: read from ADLS, transform, write back
from pyspark.sql import SparkSession
from pyspark.sql.functions import col, sum as spark_sum, to_date

spark = SparkSession.builder.getOrCreate()

# Read raw JSON events from the bronze layer
raw = spark.read.json(
    "abfss://bronze@mydatalake.dfs.core.windows.net/events/2025/"
)

# Aggregate daily revenue per customer
daily_revenue = (
    raw
    .withColumn("event_date", to_date(col("timestamp")))
    .filter(col("event_type") == "purchase")
    .groupBy("customer_id", "event_date")
    .agg(spark_sum("amount").alias("daily_total"))
)

# Write to the silver layer in Delta format
daily_revenue.write.format("delta").mode("overwrite").save(
    "abfss://silver@mydatalake.dfs.core.windows.net/customer_daily_revenue/"
)

print(f"Wrote {daily_revenue.count()} rows to silver layer")

Storage: ADLS Gen2 as the backbone

Every Synapse workspace is associated with a primary ADLS Gen2 storage account at creation time. This account holds:

• Notebooks and SQL scripts authored in Synapse Studio
• Pipeline run outputs and intermediate data
• Spark job outputs (unless you specify a different path)
• Synapse Link analytical stores (for Cosmos DB integration)

You can add additional storage accounts as linked services. When you add an ADLS Gen2 linked service, you can browse its hierarchy in Synapse Studio and query it from serverless SQL or Spark without any additional setup — assuming the workspace managed identity has the right RBAC role.

It is a best practice to structure your ADLS Gen2 account in three layers, often called the medallion architecture:

• Bronze — raw ingested data, exactly as it arrived from the source, never modified
• Silver — cleaned, validated, and enriched data in Parquet or Delta format
• Gold — aggregated, business-ready datasets used for reporting and ML features

Synapse Pipelines ingest into bronze. Spark notebooks transform bronze to silver. Dedicated SQL pools or serverless SQL external tables expose gold to Power BI.

Network isolation with managed virtual networks

By default, Synapse workspaces are publicly accessible. For production workloads with sensitive data, you should enable a managed virtual network at workspace creation time. This places all Synapse compute resources inside a Microsoft-managed VNet that you do not have to configure yourself.

From the managed VNet, you create managed private endpoints to connect securely to ADLS Gen2, Azure Key Vault, and other services without traffic traversing the public internet. You cannot add a managed VNet to an existing workspace — it must be configured at creation.