Azure Synapse

Azure Synapse Analytics

Azure Synapse is an enterprise analytics service combining dedicated SQL pools (formerly SQL Data Warehouse), a serverless SQL pool, Apache Spark pools, and Data Factory-compatible pipelines — all within a single workspace with shared security, monitoring, and a unified Studio UI.

Strategic direction (verify before greenfield builds): Microsoft now positions Microsoft Fabric as the primary target for new analytics investments; Synapse is effectively in maintenance mode (no major new feature investment, though it remains supported with no announced end-of-life for the core SQL/Spark/pipeline workloads). New projects should evaluate Fabric first (see the azure-fabric skill); treat Synapse Data Explorer (retired October 7, 2025) and Synapse Link as transitional and plan migration to Fabric Eventhouse / mirroring. Existing Synapse workspaces continue to work — this skill remains valid for them.

Workspace Setup

az CLI Provisioning

# Create resource group and storage (ADLS Gen2)
az group create --name synapse-rg --location eastus

az storage account create \
  --name mysynapsestorage \
  --resource-group synapse-rg \
  --location eastus \
  --sku Standard_LRS \
  --kind StorageV2 \
  --hierarchical-namespace true   # Required for ADLS Gen2

# Create Synapse workspace
az synapse workspace create \
  --name my-synapse-ws \
  --resource-group synapse-rg \
  --storage-account mysynapsestorage \
  --file-system synapsefs \         # Container in ADLS Gen2
  --sql-admin-login-user sqladmin \
  --sql-admin-login-password "YourP@ssw0rd!" \
  --location eastus

# Open firewall for your IP (dev/test only)
az synapse workspace firewall-rule create \
  --name AllowMyIP \
  --workspace-name my-synapse-ws \
  --resource-group synapse-rg \
  --start-ip-address $(curl -s ifconfig.me) \
  --end-ip-address $(curl -s ifconfig.me)

# Enable managed VNet at creation (cannot change after)
az synapse workspace create \
  ... \
  --enable-managed-virtual-network   # Adds --managed-virtual-network flag

Managed VNet & Managed Private Endpoints

# Create a managed private endpoint to your ADLS Gen2
az synapse managed-private-endpoints create \
  --workspace-name my-synapse-ws \
  --resource-group synapse-rg \
  --pe-name my-adls-mpe \
  --file @mpe.json

# mpe.json:
# {
#   "name": "my-adls-mpe",
#   "properties": {
#     "privateLinkResourceId": "/subscriptions/.../storageAccounts/mydata",
#     "groupId": "dfs"
#   }
# }

# List managed private endpoints
az synapse managed-private-endpoints list \
  --workspace-name my-synapse-ws \
  --resource-group synapse-rg

Key: When managed VNet is enabled, all Spark and pipeline activities run inside the managed VNet. Data exfiltration protection blocks outbound traffic not through approved managed private endpoints.

Dedicated SQL Pool

A dedicated SQL pool is a provisioned MPP (Massively Parallel Processing) cluster with 60 fixed compute nodes that distributes data across distributions.

DWU Sizing

# Create dedicated SQL pool
az synapse sql pool create \
  --name MySQLPool \
  --workspace-name my-synapse-ws \
  --resource-group synapse-rg \
  --performance-level DW500c

# Pause (stops compute billing — storage still charged)
az synapse sql pool pause \
  --name MySQLPool \
  --workspace-name my-synapse-ws \
  --resource-group synapse-rg

# Resume
az synapse sql pool resume \
  --name MySQLPool \
  --workspace-name my-synapse-ws \
  --resource-group synapse-rg

# Scale DWU (can do while running — brief interruption)
az synapse sql pool update \
  --name MySQLPool \
  --workspace-name my-synapse-ws \
  --resource-group synapse-rg \
  --performance-level DW1000c

Table Distributions

Every table must be distributed. Choose incorrectly and you'll pay in shuffle costs.

-- Hash distribution (best for large fact tables with even key distribution)
CREATE TABLE dbo.FactSales (
    SaleId       BIGINT       NOT NULL,
    CustomerId   INT          NOT NULL,
    ProductId    INT          NOT NULL,
    SaleDate     DATE         NOT NULL,
    Amount       DECIMAL(18,2)
)
WITH (
    DISTRIBUTION = HASH(CustomerId),   -- join key to customers
    CLUSTERED COLUMNSTORE INDEX         -- CCI: best compression + analytics perf
);

-- Round-robin (best for staging tables, unknown join patterns)
CREATE TABLE dbo.Staging_Orders (...)
WITH (DISTRIBUTION = ROUND_ROBIN, HEAP);

-- Replicated (best for small dimension tables < ~2 GB)
CREATE TABLE dbo.DimProduct (
    ProductId   INT           NOT NULL,
    ProductName NVARCHAR(200) NOT NULL,
    Category    NVARCHAR(100)
)
WITH (
    DISTRIBUTION = REPLICATE,
    CLUSTERED COLUMNSTORE INDEX
);

-- Check distribution skew
SELECT
    pnp.pdw_node_id,
    pnp.distribution_id,
    SUM(pnp.used_page_count) * 8 / 1024.0 AS used_space_MB
FROM sys.pdw_nodes_db_partition_stats AS pnp
JOIN sys.pdw_distributions AS pd ON pnp.distribution_id = pd.distribution_id
WHERE pnp.object_id = OBJECT_ID('dbo.FactSales')
GROUP BY pnp.pdw_node_id, pnp.distribution_id
ORDER BY used_space_MB DESC;

Indexing

-- Clustered Columnstore Index (CCI): default recommendation for analytics
-- Best compression (5-10x), best scan performance for large tables

-- Clustered Rowstore Index: only when heavy point lookups or small tables
CREATE TABLE dbo.LookupTable (
    Id   INT NOT NULL PRIMARY KEY,
    Val  NVARCHAR(200)
)
WITH (
    DISTRIBUTION = REPLICATE,
    CLUSTERED INDEX (Id)    -- rowstore for PK lookups
);

-- Heap: staging / ELT intermediary tables (no index overhead on insert)
WITH (DISTRIBUTION = ROUND_ROBIN, HEAP)

-- Rebuild fragmented CCI
ALTER INDEX ALL ON dbo.FactSales REBUILD;

-- Check CCI health (row group quality)
SELECT
    OBJECT_NAME(rg.object_id) AS table_name,
    rg.state_desc,
    COUNT(*) AS row_group_count,
    SUM(rg.total_rows) AS total_rows,
    SUM(rg.deleted_rows) AS deleted_rows
FROM sys.pdw_nodes_column_store_row_groups rg
GROUP BY rg.object_id, rg.state_desc
ORDER BY table_name, state_desc;

Partitioning

-- Partition by date for large fact tables (improves partition elimination)
CREATE TABLE dbo.FactSales (
    SaleId     BIGINT,
    SaleDate   DATE,
    Amount     DECIMAL(18,2)
)
WITH (
    DISTRIBUTION = HASH(CustomerId),
    CLUSTERED COLUMNSTORE INDEX,
    PARTITION (SaleDate RANGE RIGHT FOR VALUES (
        '2023-01-01', '2023-04-01', '2023-07-01', '2023-10-01',
        '2024-01-01', '2024-04-01', '2024-07-01', '2024-10-01'
    ))
);

-- Switch in a new partition (zero-copy when both tables same structure)
ALTER TABLE dbo.FactSales_Staging
    SWITCH PARTITION 1 TO dbo.FactSales PARTITION 5;

Workload Management

-- Create workload group for BI users (isolated resources)
CREATE WORKLOAD GROUP BiUsers
WITH (
    MIN_PERCENTAGE_RESOURCE = 20,   -- always reserves 20% of DWUs
    CAP_PERCENTAGE_RESOURCE = 50,   -- cannot exceed 50%
    REQUEST_MIN_RESOURCE_GRANT_PERCENT = 5
);

-- Create classifier to route users to group
CREATE WORKLOAD CLASSIFIER BiClassifier
WITH (
    WORKLOAD_GROUP = 'BiUsers',
    MEMBERNAME = 'bi_login',
    IMPORTANCE = NORMAL
);

-- Monitor active requests
SELECT * FROM sys.dm_pdw_exec_requests
WHERE status = 'Running'
ORDER BY submit_time;

-- Find waiting requests and why
SELECT
    r.request_id,
    r.status,
    r.submit_time,
    w.type AS wait_type,
    w.object_type,
    w.object_name
FROM sys.dm_pdw_exec_requests r
JOIN sys.dm_pdw_waits w ON r.request_id = w.request_id
WHERE r.status = 'Suspended';

Gotcha — Statistics: Dedicated pool does NOT auto-create or auto-update statistics. Missing or stale statistics cause terrible query plans. Create after load:

CREATE STATISTICS stats_FactSales_CustomerId ON dbo.FactSales (CustomerId);
-- Or auto-create for all columns (use sp_create_stats or a maintenance job):
EXEC sp_create_stats;

Serverless SQL Pool

The serverless pool is always available, billed per TB of data scanned, and needs no provisioning. No CCI, no distributions — it's a distributed query engine over ADLS.

OPENROWSET (Ad-Hoc Queries)

-- Query Parquet files
SELECT TOP 100 *
FROM OPENROWSET(
    BULK 'https://myaccount.dfs.core.windows.net/mycontainer/sales/2024/**',
    FORMAT = 'PARQUET'
) AS r;

-- Query CSV with explicit schema
SELECT *
FROM OPENROWSET(
    BULK 'https://myaccount.dfs.core.windows.net/mycontainer/raw/orders.csv',
    FORMAT = 'CSV',
    PARSER_VERSION = '2.0',
    FIRSTROW = 2,
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n'
) WITH (
    order_id    INT         1,
    customer_id INT         2,
    order_date  DATE        3,
    amount      DECIMAL(18,2) 4
) AS r;

-- Query Delta Lake (reads _delta_log for schema + latest snapshot)
SELECT *
FROM OPENROWSET(
    BULK 'https://myaccount.dfs.core.windows.net/mycontainer/delta/orders/',
    FORMAT = 'DELTA'
) AS r
WHERE order_date >= '2024-01-01';

Credential Management

-- Create credential for managed identity access (workspace MSI)
CREATE DATABASE SCOPED CREDENTIAL WorkspaceIdentity
WITH IDENTITY = 'Managed Identity';

-- Create credential with SAS token
CREATE DATABASE SCOPED CREDENTIAL SasCredential
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = 'sv=2020-08-04&ss=b&srt=co&sp=r...';  -- SAS token (no leading ?)

-- Create data source referencing credential
CREATE EXTERNAL DATA SOURCE MyADLS
WITH (
    LOCATION = 'https://myaccount.dfs.core.windows.net/mycontainer',
    CREDENTIAL = WorkspaceIdentity
);

-- Use data source in queries
SELECT * FROM OPENROWSET(
    BULK 'sales/2024/',
    DATA_SOURCE = 'MyADLS',
    FORMAT = 'PARQUET'
) AS r;

CETAS (CREATE EXTERNAL TABLE AS SELECT)

CETAS is the serverless pool's primary way to persist query results:

-- Create external file format
CREATE EXTERNAL FILE FORMAT ParquetSnappy
WITH (FORMAT_TYPE = PARQUET, DATA_COMPRESSION = 'org.apache.hadoop.io.compress.SnappyCodec');

-- Run transformation and save result as Parquet
CREATE EXTERNAL TABLE dbo.monthly_sales_summary
WITH (
    LOCATION = 'aggregates/monthly_sales/',
    DATA_SOURCE = MyADLS,
    FILE_FORMAT = ParquetSnappy
)
AS
SELECT
    FORMAT(order_date, 'yyyy-MM') AS month,
    region,
    SUM(amount) AS total_sales,
    COUNT(*) AS order_count
FROM OPENROWSET(
    BULK 'sales/2024/',
    DATA_SOURCE = 'MyADLS',
    FORMAT = 'PARQUET'
) AS r
GROUP BY FORMAT(order_date, 'yyyy-MM'), region;

Gotcha — CETAS drops old files: Re-running CETAS on the same LOCATION does NOT truncate — it will mix new and old Parquet files. Drop and recreate the external table, or use a new LOCATION with a timestamp/version prefix.

Gotcha — Serverless charges per TB scanned: A full table scan of a large Parquet dataset is expensive. Always add partition filter predicates and ensure Parquet files use efficient column pruning. Delta format with partition pruning drastically reduces cost.

Spark Pools

# Create Spark pool
az synapse spark pool create \
  --name mysparkpool \
  --workspace-name my-synapse-ws \
  --resource-group synapse-rg \
  --spark-version 3.4 \
  --node-count 3 \
  --node-size Medium \
  --enable-auto-scale true \
  --min-node-count 3 \
  --max-node-count 10 \
  --delay 15              # autoscale delay minutes

Library Management

# Upload requirements.txt to workspace storage, then:
az synapse spark pool update \
  --name mysparkpool \
  --workspace-name my-synapse-ws \
  --resource-group synapse-rg \
  --library-requirements /tmp/requirements.txt

Spark-SQL Interop

# In a Synapse notebook (PySpark)

# Read from ADLS Gen2 (linked service handles auth)
df = spark.read.parquet("abfss://mycontainer@myaccount.dfs.core.windows.net/sales/")

# Read from dedicated SQL pool (Synapse connector — parallel read via PolyBase)
df = spark.read \
    .format("com.microsoft.azure.synapse.spark") \
    .option("url", "jdbc:sqlserver://my-synapse-ws.sql.azuresynapse.net:1433;database=MySQLPool") \
    .option("dbTable", "dbo.FactSales") \
    .option("tempDir", "abfss://mycontainer@myaccount.dfs.core.windows.net/spark-tmp/") \
    .load()

# Write to dedicated SQL pool
df.write \
    .format("com.microsoft.azure.synapse.spark") \
    .option("url", "jdbc:sqlserver://my-synapse-ws.sql.azuresynapse.net:1433;database=MySQLPool") \
    .option("dbTable", "dbo.StagingTable") \
    .option("tempDir", "abfss://mycontainer@myaccount.dfs.core.windows.net/spark-tmp/") \
    .mode("overwrite") \
    .save()

# Delta Lake in Spark pool
df.write.format("delta").mode("overwrite").save(
    "abfss://mycontainer@myaccount.dfs.core.windows.net/delta/orders/"
)
spark.sql("CREATE TABLE orders USING DELTA LOCATION 'abfss://...'")

Pipelines

Synapse Pipelines are ADF-compatible (same JSON schema, same activity types). Most ADF knowledge transfers directly.

# List pipelines
az synapse pipeline list \
  --workspace-name my-synapse-ws \
  --resource-group synapse-rg

# Trigger a pipeline run
az synapse pipeline create-run \
  --workspace-name my-synapse-ws \
  --resource-group synapse-rg \
  --name MyIngestionPipeline \
  --parameters '{"date": "2024-01-15"}'

# Monitor run status
az synapse pipeline-run query-by-workspace \
  --workspace-name my-synapse-ws \
  --resource-group synapse-rg \
  --last-updated-after 2024-01-15T00:00:00Z \
  --last-updated-before 2024-01-16T00:00:00Z

Integration Runtimes

• AutoResolveIntegrationRuntime: serverless, managed by Synapse (default for cloud activities)
• Self-Hosted IR: deploy on-premises or in a VM for on-prem data sources
• Managed IR (VNet): only available when workspace managed VNet is enabled; runs inside managed VNet

Synapse Link

Synapse Link creates a near-real-time analytical replica of operational data without impacting the transactional system.

Synapse Link for Azure Cosmos DB

-- After enabling Synapse Link on Cosmos DB container:
-- Query Cosmos DB analytical store from serverless pool

SELECT TOP 10 *
FROM OPENROWSET(
    'CosmosDB',
    'Account=mycosmosdb;Database=mydb;Key=<key>',
    orders
) WITH (
    id          VARCHAR(50)   '$.id',
    customerId  INT           '$.customerId',
    amount      FLOAT         '$.amount',
    orderDate   VARCHAR(30)   '$.orderDate'
) AS orders;

Synapse Link for SQL Server / Azure SQL

Replicates tables via Change Feed to dedicated or serverless pool. Provisioned via the Azure Portal or az cli:

az synapse link-connection create \
  --workspace-name my-synapse-ws \
  --resource-group synapse-rg \
  --name sql-link \
  --file @link-connection.json

Security

Column-Level Security

-- Grant column-level SELECT (dedicated pool)
GRANT SELECT ON dbo.Customers (customer_id, customer_name) TO [analyst_login];
-- Deny sensitive columns
DENY SELECT ON dbo.Customers (ssn, credit_card) TO [analyst_login];

Row-Level Security

-- Create predicate function
CREATE FUNCTION dbo.fn_security_predicate(@region NVARCHAR(50))
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS result
WHERE @region = SESSION_CONTEXT(N'region')
   OR IS_MEMBER('db_owner') = 1;

-- Apply security policy
CREATE SECURITY POLICY RegionFilter
ADD FILTER PREDICATE dbo.fn_security_predicate(region) ON dbo.FactSales,
ADD BLOCK  PREDICATE dbo.fn_security_predicate(region) ON dbo.FactSales AFTER INSERT
WITH (STATE = ON);

-- Set session context (application must set this per login)
EXEC sp_set_session_context N'region', N'West';

Dynamic Data Masking

-- Add masking to sensitive columns
ALTER TABLE dbo.Customers
ALTER COLUMN EmailAddress ADD MASKED WITH (FUNCTION = 'email()');

ALTER TABLE dbo.Customers
ALTER COLUMN CreditCard ADD MASKED WITH (FUNCTION = 'partial(0,"XXXX-XXXX-XXXX-",4)');

-- Grant UNMASK to privileged role
GRANT UNMASK TO [db_datareader];

Monitoring

DMVs for Dedicated SQL Pool

-- Active queries with elapsed time
SELECT
    r.request_id,
    r.status,
    r.submit_time,
    DATEDIFF(SECOND, r.submit_time, GETDATE()) AS elapsed_sec,
    r.command
FROM sys.dm_pdw_exec_requests r
WHERE r.status NOT IN ('Completed', 'Failed', 'Cancelled')
ORDER BY submit_time;

-- Query steps (which step is running / slowest)
SELECT
    r.request_id,
    rs.step_index,
    rs.operation_type,
    rs.status,
    rs.total_elapsed_time / 1000 AS elapsed_sec,
    rs.row_count
FROM sys.dm_pdw_exec_requests r
JOIN sys.dm_pdw_request_steps rs ON r.request_id = rs.request_id
WHERE r.request_id = 'QID12345'
ORDER BY rs.step_index;

-- Data movement waits (common bottleneck: shuffle/broadcast DMS operations)
SELECT
    rs.request_id,
    rs.step_index,
    rs.operation_type,
    rs.total_elapsed_time / 1000 AS step_sec,
    rs.row_count,
    rs.status
FROM sys.dm_pdw_request_steps rs
WHERE rs.operation_type IN ('BroadcastMoveOperation', 'ShuffleMoveOperation', 'TrimMoveOperation')
  AND rs.total_elapsed_time > 5000
ORDER BY rs.total_elapsed_time DESC;

Spark Application Monitoring

# List Spark applications
az synapse spark job list \
  --workspace-name my-synapse-ws \
  --spark-pool-name mysparkpool \
  --resource-group synapse-rg

# Get Spark application logs
az synapse spark job show \
  --workspace-name my-synapse-ws \
  --spark-pool-name mysparkpool \
  --resource-group synapse-rg \
  --livy-id <livy-id>

Gotchas & Operational Tips

1. Dedicated pool storage costs when paused: Compute stops but you pay for storage (DWU-hours × storage overhead). Data stored as proprietary distributed format — you cannot access it directly from ADLS while the pool is paused.

2. DWU scaling takes time: Scaling up from DW500c to DW2000c takes 5-10 minutes and causes a brief connection interruption. Plan maintenance windows.

3. Distribution skew kills performance: If 90% of your hash-distributed table rows land on 5 of 60 distributions, queries become single-node bottlenecks. Verify with sys.pdw_nodes_db_partition_stats. Choose distribution keys carefully.

4. Statistics must be maintained manually: Run EXEC sp_update_stats or schedule a weekly job. Stale stats after large loads cause optimizer to choose wrong join types.

5. Serverless charges per TB scanned (not per query): A complex multi-join over 10 TB of CSV files will cost ~$50 per run. Convert CSV to Parquet with appropriate partitioning to reduce by 10-50x. Use Delta for partition pruning.

6. CETAS location collision: Re-running CETAS to the same path does not overwrite — it appends new files alongside old ones. Always manage output locations explicitly.

7. Managed VNet is all-or-nothing: You cannot enable managed VNet after workspace creation. If you need private endpoints later, you must recreate the workspace.

8. Spark pool cold start: First Spark job on a cold pool takes 3-5 minutes. Set auto-pause delay to at least 30 minutes if you have interactive users.

9. Synapse Link CDC lag: Synapse Link for Cosmos DB typically has 2-5 minute lag. For SQL Server, initial full load can take hours for large tables. It is NOT a replacement for real-time streaming (use Event Hubs + Spark Streaming for that).

10. Workspace managed identity (MSI): The workspace has a system-assigned managed identity that should be granted Storage Blob Data Contributor on your ADLS Gen2. Many "access denied" errors in pipelines are due to missing MSI role assignments.