Optimization: cutting serverless query cost on the lake - Athena and Synapse serverless SQL

#Billing in one line

You pay per TB scanned (Athena, per query with a minimum) or per TB processed (Synapse serverless). Scanning less data is the only lever, and it also makes queries faster.

#1. Find the expensive queries

Athena: query history / CloudTrail plus DataScannedInBytes per query:

ids=$(aws athena list-query-executions --work-group analysts --max-items 50 --query 'QueryExecutionIds' --output text)
aws athena batch-get-query-execution --query-execution-ids $ids \
  --query 'QueryExecutions[].[Statistics.DataScannedInBytes,QueryExecutionId]' --output text | sort -rn | head -20

(batch-get-query-execution takes up to 50 IDs per call; loop for more.)

Synapse serverless:

SELECT TOP 20 start_time, login_name, data_processed_mb, total_elapsed_time_ms, LEFT(command, 200) AS command
FROM sys.dm_exec_requests_history
ORDER BY data_processed_mb DESC;

-- data processed per day/week/month against limits
SELECT * FROM sys.dm_external_data_processed;

#2. Convert to columnar, compressed files

Athena CTAS to convert:

CREATE TABLE curated.orders
WITH (format = 'PARQUET', write_compression = 'ZSTD', partitioned_by = ARRAY['order_date'],
      external_location = 's3://lake/curated/orders/')
AS SELECT order_id, customer_id, amount, status, order_date FROM raw.orders_csv;

Synapse CETAS:

CREATE EXTERNAL TABLE curated.orders
WITH (LOCATION = 'curated/orders/', DATA_SOURCE = lake, FILE_FORMAT = parquet_format)
AS SELECT order_id, customer_id, amount, status, order_date
FROM OPENROWSET(BULK 'raw/orders/*.csv', DATA_SOURCE = 'lake', FORMAT = 'CSV', PARSER_VERSION = '2.0', HEADER_ROW = TRUE) AS r;

#3. Partition and prune

• Partition by the column most queries filter on (usually date), with partitions not too small (avoid thousands of tiny files).
• Athena: partition projection avoids catalog lookups for many partitions.
• Synapse serverless: use filepath() in WHERE to eliminate folders:

SELECT SUM(amount) FROM OPENROWSET(BULK 'curated/orders/order_date=*/*.parquet', DATA_SOURCE='lake', FORMAT='PARQUET') AS o
WHERE o.filepath(1) = '2026-10-24';

• Teach users: SELECT * on wide tables and queries without date filters are the expensive ones.

#4. Materialise repeated work

Dashboards hitting the same heavy aggregation every few minutes: create a summarised table (CTAS/CETAS) on a schedule, or serve from a warehouse/semantic model with caching.

#5. Limits

• Athena workgroups: per-query data scanned limit (BytesScannedCutoffPerQuery) and per-workgroup data usage alerts.

aws athena update-work-group --work-group analysts --configuration-updates \
  'BytesScannedCutoffPerQuery=1099511627776,EnforceWorkGroupConfiguration=true'   # 1 TB

• Synapse serverless: daily/weekly/monthly data processed limits:

EXEC sp_set_data_processed_limit @type = N'daily', @limit_tb = 2;

#6. Measure

Weekly: TB scanned per workgroup/user, top queries, cost trend. Expect big drops after CSV→Parquet and partition filters.

Tip

Converting the three most-queried CSV/JSON datasets to partitioned Parquet usually delivers most of the saving. Start there before tuning individual queries.

#Checklist

• Top queries by data scanned identified
• Hot datasets converted to compressed Parquet/Delta
• Partitioning and pruning (projection / filepath) in place
• Repeated aggregations materialised
• Per-query and per-period limits configured
• Weekly scan/cost review