14,000 Files, 40GB: Fixing a Slow Delta Table in Azure Synapse

2026-06-20 · Source: Data Engineering on Medium · Field: Technology & Digital — Data Science & Analytics, Cloud Computing & IT Infrastructure · Depth: Intermediate, quick

Summary

A 40GB Delta table in Azure Synapse experienced severe performance degradation, with Spark queries taking almost a minute before data reads commenced. The root cause was identified as an excessive number of small Parquet files, totaling around 14,000, many under 2MB, accumulated from months of hourly merge jobs. This high file count forced Spark to spend significant time processing the Delta transaction log and building query plans across numerous small files. The article details how applying Delta Lake maintenance operations like "OPTIMIZE", "Z-ORDER", and "VACUUM", along with strategic partitioning, effectively resolved the performance bottleneck by consolidating these fragmented files.

Key takeaway

For Data Engineers managing Delta Lake tables in Azure Synapse, proactively monitoring file counts is crucial to prevent performance bottlenecks. If your Spark queries exhibit slow startup times on Delta tables, investigate the underlying file fragmentation. Implement regular "OPTIMIZE" and "VACUUM" operations, potentially with "Z-ORDER" on key columns, to consolidate small files and maintain efficient query planning. This prevents accumulated small files from degrading overall data processing speed.

Key insights

Excessive small files in Delta tables significantly degrade Spark query performance by increasing transaction log processing time.

Principles

• Delta tables require regular file compaction.
• Small files increase query planning overhead.
• Transaction logs impact query startup time.

Method

Identify slow Delta table queries, check file counts, then apply "OPTIMIZE" (with "Z-ORDER" for query columns) and "VACUUM" commands to consolidate files and clean up old versions.

In practice

• Run "OPTIMIZE" on frequently updated tables.
• Use "Z-ORDER" on high-cardinality columns.
• Schedule "VACUUM" for log cleanup.

Topics

• Azure Synapse
• Delta Lake
• Apache Spark
• Data Optimization
• File Compaction
• Data Lakes

Best for: Data Engineer, Analytics Engineer

Related on AIssential

• Debunking 8 data layout myths: why Liquid Clustering outperforms partitioning — Liquid Clustering provides a flexible, performant, and scalable data layout for modern Lakehouses, overcoming partitioning's limitations.
• Processing 1 TB with DuckDB in less than 30 seconds — DuckDB can efficiently process terabyte-scale datasets, challenging its "small data" perception.
• Implementation of SCD2 On Truncate-Load Table With No Unique Column — SCD2 implementation on truncate-load Delta tables requires custom logic beyond standard Change Data Feed.
• I Found 487 GB Of Data Nobody Had Looked At In Three Years. Deleting It Felt Like Archaeology. — Regular storage audits can identify dormant data, reducing costs and improving database efficiency.
• Delta Lake Is the Foundation of Databricks. — Delta Lake serves as the foundational transactional storage layer for Databricks platforms, enabling the Lakehouse architecture by bringing ACID guarantees to data lakes.
• Zero-Downtime Patching in Lakebase Part 1: Prewarming — Lakebase is addressing planned database maintenance, which causes more workload disruption than unplanned failures, by aiming for "zero-downtime patching." This initial post details "prewarming," a te…

Open in AIssential →

Editorial summary, takeaway, and curation by AIssential. Original article published by Data Engineering on Medium.