Azure Synapse Analytics Overview

What Azure Synapse actually is

Before Synapse existed, a typical Azure data architecture looked like this: Azure SQL Data Warehouse for relational analytics, HDInsight for Spark jobs, Azure Data Factory for ETL pipelines, and several storage accounts tying it all together. Every tool had its own pricing, its own access controls, and its own UI. Handoffs between tools meant copying data and debugging connection strings.

Synapse replaces that fragmented setup with a workspace model. One workspace gives you:

• Dedicated SQL pools — provisioned, MPP data warehouses with DWU-based pricing (the successor to SQL Data Warehouse)
• Serverless SQL pools — query-on-demand over files in your data lake using standard T-SQL, paying only per terabyte scanned
• Apache Spark pools — managed Spark clusters for Python, Scala, and R workloads, auto-scaling and auto-pausing
• Synapse Pipelines — a built-in ETL/ELT engine with 90+ connectors, functionally identical to Azure Data Factory
• Synapse Studio — a browser-based IDE where SQL analysts, data engineers, and data scientists work side by side

The workspace is backed by Azure Data Lake Storage Gen2 (ADLS Gen2). Raw files sitting in that lake are immediately queryable via serverless SQL — no loading required.

Architecture at a glance

Here is how the pieces fit together in a typical Synapse deployment:

┌─────────────────────────────────────────────────────────┐
│                   Azure Synapse Workspace               │
│                                                         │
│  ┌──────────────┐  ┌──────────────┐  ┌──────────────┐  │
│  │  Dedicated   │  │  Serverless  │  │    Spark     │  │
│  │  SQL Pool    │  │  SQL Pool    │  │    Pool      │  │
│  │  (DWU-based) │  │  (per TB)    │  │  (per vCore) │  │
│  └──────┬───────┘  └──────┬───────┘  └──────┬───────┘  │
│         │                 │                 │           │
│         └─────────────────┼─────────────────┘           │
│                           │                             │
│              ┌────────────▼────────────┐                │
│              │  ADLS Gen2 (your lake)  │                │
│              └─────────────────────────┘                │
│                                                         │
│  ┌──────────────────────────────────────────────────┐   │
│  │              Synapse Studio (web IDE)            │   │
│  └──────────────────────────────────────────────────┘   │
└─────────────────────────────────────────────────────────┘

The dedicated SQL pool stores data in its own columnar storage (not in ADLS directly), while the serverless SQL pool and Spark pools read from ADLS. This is an important distinction: dedicated pools give you index control and materialized structures; serverless and Spark give you schema-on-read flexibility against your raw lake.

Key use cases

Synapse fits best in three scenarios:

Serverless SQL vs Dedicated SQL: the most important decision

New Synapse users are often confused by having two SQL engines. Here is the practical difference.

The serverless SQL pool is always on. You do not provision it. You write a T-SQL query against files in your data lake, and Synapse distributes that query across a shared compute cluster. You pay for the data scanned — roughly $5 per terabyte in most regions. This is ideal for exploration, one-off reports, and building external tables over your lake. It cannot INSERT, UPDATE, or DELETE rows in lake files.

The dedicated SQL pool is a provisioned data warehouse. You choose a DWU (Data Warehouse Unit) size, and Synapse allocates a fixed number of compute nodes. You can pause the pool when not in use to stop paying for compute. Data is stored in columnar format in Synapse’s own managed storage. This is the right choice for workloads that need consistent fast query response, index management, and support for large numbers of concurrent BI users.

-- Serverless SQL: query a Parquet file in your lake directly
SELECT
    customer_id,
    SUM(order_total) AS lifetime_value
FROM
    OPENROWSET(
        BULK 'https://mydatalake.dfs.core.windows.net/silver/orders/*.parquet',
        FORMAT = 'PARQUET'
    ) AS orders
GROUP BY customer_id
ORDER BY lifetime_value DESC;

That query above runs against raw Parquet files. No table definitions, no loading step — just a path and a query.

Synapse Studio: one IDE for all roles

Synapse Studio is a browser-based development environment where every team member works. SQL analysts write T-SQL in the SQL script editor. Data engineers build Synapse Pipelines with a drag-and-drop canvas. Data scientists write Python notebooks that run on Spark pools. All of this lives in the same workspace, so you can reference the same datasets, share notebooks, and trigger pipelines from within a notebook — without switching tools.

The Studio also includes a built-in data explorer for browsing linked services and your lake hierarchy, a monitoring hub for tracking pipeline runs and Spark jobs, and a management area for configuring pools and access controls.

Integration with the rest of Azure

Synapse does not exist in isolation. It connects tightly with other Azure services:

• Azure Data Lake Storage Gen2 — the default primary storage for every workspace; also queryable as external storage via linked services
• Azure Active Directory / Entra ID — workspace access control; column-level and row-level security in dedicated pools
• Microsoft Purview — data cataloguing and lineage tracking; Synapse scans can register assets in Purview automatically
• Azure Machine Learning — Spark pools can be attached as compute targets for AML training jobs
• Power BI — Synapse Studio has a built-in Power BI integration; publish datasets directly from the workspace
• Azure Monitor / Log Analytics — diagnostic logs and metrics for dedicated pools and pipelines

Creating a workspace with the Azure CLI

You can provision a Synapse workspace in minutes using the Azure CLI:

# Create a resource group
az group create \
  --name rg-synapse-demo \
  --location eastus

# Create a storage account (ADLS Gen2)
az storage account create \
  --name stsynapsedemo \
  --resource-group rg-synapse-demo \
  --location eastus \
  --sku Standard_LRS \
  --kind StorageV2 \
  --hierarchical-namespace true

# Create the filesystem (container) in ADLS Gen2
az storage fs create \
  --name synapsefs \
  --account-name stsynapsedemo

# Create the Synapse workspace
az synapse workspace create \
  --name ws-synapse-demo \
  --resource-group rg-synapse-demo \
  --storage-account stsynapsedemo \
  --file-system synapsefs \
  --sql-admin-login-user sqladmin \
  --sql-admin-login-password "SecureP@ss123!" \
  --location eastus

After this completes (about 3–5 minutes), open Synapse Studio by navigating to the workspace in the Azure portal and clicking “Open Synapse Studio”.

What Synapse is not good at

Synapse is powerful but not the right tool for everything. Avoid it for:

• OLTP workloads — dedicated SQL pools are optimised for analytical read patterns, not row-by-row updates. Use Azure SQL Database or Cosmos DB for transactional data.
• Very small teams or low data volumes — if your data fits in a single Azure SQL Database and your team is two analysts, the overhead of managing a Synapse workspace is not worth it.
• Advanced MLOps pipelines — for teams doing heavy model training, experiment tracking with MLflow, and feature store management, Azure Databricks has a more mature ecosystem.
• Sub-second event processing — Synapse is not a streaming engine. Use Azure Stream Analytics or Spark Structured Streaming for real-time event processing.