Synapse Cost Optimisation Strategies

Synapse Cost Components

A Synapse workspace can generate costs from multiple sources simultaneously:

Optimising Dedicated SQL Pools

Dedicated SQL pools are the most expensive Synapse component. A DW1000c pool (the middle of the scale) costs approximately $12/hour or $8,760/month if left running 24/7. Optimisation here has the biggest financial impact.

Pause and resume automation

Pausing a dedicated pool stops compute billing entirely. If your analytical workloads run during business hours only, pausing outside those hours cuts costs by 60–70%.

# Pause a dedicated SQL pool (stops compute billing, retains data)
az synapse sql pool pause \
  --name my-dedicated-pool \
  --workspace-name my-synapse-workspace \
  --resource-group my-rg

# Resume the pool
az synapse sql pool resume \
  --name my-dedicated-pool \
  --workspace-name my-synapse-workspace \
  --resource-group my-rg

# Check current pool status
az synapse sql pool show \
  --name my-dedicated-pool \
  --workspace-name my-synapse-workspace \
  --resource-group my-rg \
  --query "status" \
  --output tsv

Schedule-based auto-pause with Azure Automation

Use an Azure Automation runbook triggered by a schedule to pause pools outside business hours:

# Example runbook logic (PowerShell):
# Pause at 7pm weekdays, resume at 7am weekdays
# Weekend: pause Friday 7pm, resume Monday 7am
# Estimated saving: DW500c ($6/hr) paused 12hrs/day + 48hrs weekend
# Running hours: (12hrs * 5 days) + 0 weekend = 60hrs/week vs 168hrs
# Saving: (168-60)/168 = 64% cost reduction
# $6 * 60 hrs/week * 4.3 weeks = $1,548/month vs $4,354/month PAYG

Scale DWUs to match workload requirements

The DWU count scales cost linearly and performance roughly linearly. Running at DW1000c when only DW300c is needed doubles the cost for no benefit. Scale up before major batch jobs and scale down for lighter ad-hoc query workloads.

# Scale pool to a different DWU count
az synapse sql pool update \
  --name my-dedicated-pool \
  --workspace-name my-synapse-workspace \
  --resource-group my-rg \
  --performance-level DW300c

# Check available performance levels for scaling
# DW100c, DW200c, DW300c, DW400c, DW500c, DW1000c, DW1500c, DW2000c, DW2500c, DW3000c, DW5000c, DW6000c, DW7500c, DW10000c, DW15000c, DW30000c

Optimising Serverless SQL Pool

Serverless SQL pool charges $5 per TB of data scanned. The amount of data scanned is controlled by your query design and file format. Poorly written queries can scan far more data than necessary.

Use Parquet instead of CSV

Parquet is a columnar format that allows Serverless SQL to skip columns not referenced in the query. A CSV file requires scanning every byte even if only one column is needed. For a 1 TB dataset:

• CSV query selecting 3 of 30 columns: scans ~1 TB = $5.00
• Parquet query selecting 3 of 30 columns: scans ~3/30 of data = ~100 GB = $0.50

Use partition pruning

Store data in partitioned folder structures (e.g., /year=2026/month=03/day=19/) and include partition columns in WHERE clauses. Serverless SQL uses partition pruning to skip entire folders that do not match the filter.

-- EXPENSIVE: scans entire dataset across all partitions
SELECT customer_id, amount
FROM OPENROWSET(
  BULK 'https://storage.dfs.core.windows.net/container/sales/**',
  FORMAT = 'PARQUET'
) AS data
WHERE YEAR(sale_date) = 2026;

-- OPTIMISED: partition pruning skips all non-2026 folders
SELECT customer_id, amount
FROM OPENROWSET(
  BULK 'https://storage.dfs.core.windows.net/container/sales/year=2026/**',
  FORMAT = 'PARQUET'
) AS data;

SELECT specific columns, not SELECT *

With Parquet’s columnar format, selecting only the columns you need reduces the data scanned and the cost proportionally.

Optimising Apache Spark Pools

Spark pools in Synapse charge per vCore-hour. A Small pool (4 vCores, 3 executors) costs approximately $0.00025/vCore-hour when running. The key optimisation levers are:

• Enable auto-pause: Spark pools can be configured to auto-pause after a period of inactivity (minimum 5 minutes). This prevents idle pool billing between sessions.
• Enable auto-scale: Configure minimum and maximum node counts. The pool scales up during heavy processing and down to minimum during light use.
• Use spot instances for Spark nodes: Synapse Spark pools support spot (low-priority) nodes at significantly reduced rates. Enable spot for non-critical analytics workloads.
• Right-size the node count: Many teams provision large Spark pools for initial testing and never scale back down. Review node counts against actual job parallelism.

# Create a Spark pool with auto-pause enabled
az synapse spark pool create \
  --name optimised-spark \
  --workspace-name my-synapse-workspace \
  --resource-group my-rg \
  --node-count 3 \
  --node-size Small \
  --enable-auto-pause true \
  --delay 15 \
  --enable-auto-scale true \
  --min-node-count 3 \
  --max-node-count 10

Storage Cost Optimisation in Synapse

Synapse workspaces use Azure Data Lake Storage Gen2 as their primary storage layer. Storage costs are modest compared to compute but scale with data volume. Key approaches:

• Compress data: Parquet with Snappy compression typically reduces data size by 3–5x compared to uncompressed CSV, directly reducing storage costs.
• Implement lifecycle policies: Move older analytical data from hot to cool tier (reduces cost from $0.023 to $0.01/GB/month) or archive tier ($0.00099/GB/month) for data accessed infrequently.
• Delete intermediate datasets: ETL pipelines often create temporary datasets that accumulate indefinitely. Add cleanup steps to pipelines that delete intermediate data after it is no longer needed.