Stop Scaling Your Spark Cluster: How File Layout Fixes Slow Lakehouse Pipelines
Updated: 6 hours ago

There is nothing quite like watching a tiny 200 MB batch update drag on for 50 painful minutes while your Spark cluster burns money doing practically zero real work. We turned that exact pipeline into a fast 5-minute run on half the nodes not by tweaking obscure JVM options, but by fixing the underlying file layout.
A 200 MB Update Shouldn’t Take 50 Minutes: Data Lakehouse Optimization Techniques That Actually Work
Here is a number that bothered me for weeks: 200 MB.
That was the size of our incoming batch updates. We were merging them into a 500 GB Delta table on S3 using a cluster that looked huge on paper, yet every run dragged out to nearly an hour.
You don’t need to be a staff data engineer to realize something was fishy. The cluster’s compute capacity was more than enough for the job size, yet every run ground to a halt.
When I opened the Spark UI during a run, the problem was obvious. Thousands of tasks were spending time reading millions of tiny files, only to re-write those same files back to storage. Adding more servers didn't help—when your bottleneck is storage speed, extra computing power just sits around waiting.
If you run data lakehouses in production, you have likely run into this exact headache. Real performance optimization comes down to controlling file layout, separating write-heavy and read-heavy paths, and adopting sensible staging patterns.
To show how much file layout impacts real-world systems, let’s look at two scenarios—an e-commerce platform struggling with customer profile updates, and a financial firm managing real-time fraud detection.
The Root Cause: Why Small Updates Trigger Massive I/O
To fix slow lakehouse updates, you have to account for how object stores function under the hood. Object storage services like S3, GCS, and Azure Blob are immutable; you cannot update bytes inside an existing file in place.
When you run a MERGE (upsert) in Delta Lake or Apache Iceberg, the engine executes two distinct phases:
Locate: Scan the target table to figure out which exact files hold rows matching the incoming batch.
Rewrite: Load those entire matching files into memory, apply updates and inserts, then write completely new Parquet files out.
Here is the catch: these engines operate on whole files, never individual rows.
Here is the catch: these engines operate on whole files, never individual rows. If your table spans 500 GB across 15,000 small files (~30 MB each) and your 200 MB batch touches keys distributed randomly, welcome to I/O hell. Spark pulls, parses, and rewrites 4,000 files just for a few changed records, turning a tiny batch into 120 GB+ of pure write amplification.
To make matters worse, writing those updated slices creates even more tiny files, guaranteeing the next batch runs even slower.
Fixing the Small File Problem (Prune, Cluster, Compact)
My immediate reaction was typical: double the cluster size and adjust spark.sql.shuffle.partitions. That shaved off maybe 3 minutes without addressing the core bottleneck. Running periodic compaction jobs helps, but if every merge writes fragmented files across the table, compaction is just mopping around an overflowing sink.
To actually fix it, you have to attack file layout from three angles:
Prune Aggressively in Your Merge Predicate
If your table is partitioned by event_date and your incoming records only touch the last 7 days, state that directly in your join condition. Spark will not automatically prune partitions based on dynamic incoming data without an explicit range filter on the target side.
MERGE INTO prod.events AS target
USING staging.events_batch AS source
ON target.event_id = source.event_id
-- Explicit partition pruning: prevents scanning historical years
AND target.event_date >= current_date() - INTERVAL 7 DAYS
WHEN MATCHED THEN UPDATE SET *
WHEN NOT MATCHED THEN INSERT *;Cluster Your Data Around Join Keys
Partitioning works for broad boundaries like dates, but partitioning on high-cardinality values like user_id or event_id creates vast directories of micro-files. Instead, leverage Z-Ordering or Liquid Clustering to group related records within the same set of Parquet files.
When matching keys are grouped in 10 files rather than 4,000, file pruning skips 99% of the table using Parquet footer statistics before reading data.
OPTIMIZE prod.events
WHERE event_date >= current_date() - INTERVAL 7 DAYS
ZORDER BY (event_id);Enforce Target File Sizes at Write Time
Prevent small files during write operations rather than relying entirely on downstream cleanup. Set table configurations so jobs automatically coalesce writes into target file sizes around 128 MB to 512 MB:
ALTER TABLE prod.events SET TBLPROPERTIES (
'delta.autoOptimize.optimizeWrite' = 'true',
'delta.autoOptimize.autoCompact' = 'true'
);Note: Verify your engine version before enabling these properties—options like optimizeWrite and autoCompact vary between managed and open-source releases.
After applying partition filters to our MERGE logic, running targeted Z-Ordering, and enabling auto-compacting writes, our 50-minute job dropped down to under 5 minutes on half the cluster node count.
Case Study A: E-Commerce Customer Profile Sync Bottleneck
An enterprise e-commerce vendor maintained a 1.2 TB Delta table storing user account profiles, order histories, and preference vectors. Every 15 minutes, an ingestion pipeline synced updated user records from an operational PostgreSQL database into the data lakehouse. The incoming batch size was relatively small—around 150 MB containing approximately 45,000 modified user profiles.
Despite the small payload, each scheduled MERGE execution took over 42 minutes on a 32-node Spark cluster. Because user updates were randomly distributed across the entire customer base, the join condition matched rows spanning across 8,500 distinct Parquet files out of 10,000 total files in the table.
The engineering team implemented Liquid Clustering on user_id and added dynamic partition pruning. As a result, incoming batch updates were localized to just 45 clustered Parquet files instead of 8,500. Read I/O dropped by 99.4%, and job completion time fell from 42 minutes down to under 3 minutes, saving thousands of dollars in monthly cloud compute expenses.
Write-Heavy vs. Read-Heavy Optimization Strategies
A frequent architecture oversight is applying identical table settings across every pipeline layer. A raw log table taking thousands of writes per second needs entirely different handling than a Gold-layer reporting table queried by business tools all morning.
Optimizing Write-Heavy Pipelines (Copy-on-Write vs. Merge-on-Read)
By default, formats like Delta and Iceberg rely heavily on Copy-on-Write (CoW). Any update or delete forces an immediate file rewrite. CoW keeps read queries crisp because data files remain clean, but it imposes heavy overhead on write-intensive jobs.
For pipelines handling constant micro-batches, late-arriving CDC feeds, or frequent compliance deletions, shift toward Merge-on-Read (MoR)—implemented via Deletion Vectors in Delta Lake or Positional Deletes in Iceberg.
With Merge-on-Read:
Rather than rewriting a 500 MB file for a handful of changed records, the engine writes a tiny bitmap/delete file tracking obsolete row positions, appending modified rows into a new data file.
Writes wrap up in seconds instead of dragging out.
Query readers combine base files and delete files during scans, while asynchronous background tasks merge and compact them later.
Rule of thumb: Stick with Copy-on-Write for batch tables where read volume heavily outweighs writes. Favor Merge-on-Read / Deletion Vectors for high-frequency streaming and CDC operations.
Optimizing Read-Heavy Tables
When analytical queries or downstream engines hit a table continuously, optimize specifically for read execution speed:
Column Ordering: Put high-frequency filter columns (like tenant_id or created_at) near the start of the table schema. Engines collect min/max stats on initial columns by default; putting heavily filtered columns far out in wide schemas breaks file pruning.
Bloom Filters: For point lookups on unique string columns (such as transaction_id), Bloom filter indexing lets execution engines skip non-matching files without scanning data blocks.
Target Larger Files: Target 512 MB to 1 GB file sizes on read-heavy reporting tables to maximize sequential throughput and lower API request counts.
The Staging Layer: Cheap Insurance That Fixes Batch Sizing
Regardless of storage format, one operational practice protects production tables from corruption and fragmentation: avoid merging directly from raw ingestion streams straight into curated tables.
Instead, route incoming data through a lightweight Staging Layer:
Instead, introduce a lightweight Staging Layer (often a transient Bronze/Silver hop):

Why is a staging layer worth the extra step?
Batch Accumulation: If streaming tasks ingest small 5 MB chunks every minute, merging directly into production leads to severe file fragmentation. Writing mini-batches to staging as appends allows you to trigger target merges every 15–30 minutes with healthy batch sizes.
Deduplication: Upstream producers frequently send duplicate records. Deduplicating records in staging using window functions prevents table merge conflicts or redundant file rewrites.
Simplified Replays: If upstream schema changes or corrupted payloads break downstream transformations, having raw staging data available lets you fix pipeline logic and re-run without re-fetching source data.
Additional High-Leverage Optimizations Often Overlooked
Case Study B: Real-Time Financial Fraud Telemetry & Small File Explosion
Consider another common real-world scenario: a fintech platform processing millions of credit card transactions per hour. Their fraud engine relies on continuous stream ingestion using Apache Flink to stream raw transaction events directly into an Apache Iceberg lakehouse table every minute. Each micro-batch appends roughly 15 MB to 30 MB of data.
Within two days of continuous operation, the target Iceberg table accumulated over 120,000 micro-files. When downstream risk analytics models attempted to query historical transaction trends over a 30-day window, query planning times ballooned past 15 minutes before any actual data scanning began. The Spark driver was overwhelmed attempting to list and analyze file footers across the massive object storage hierarchy.
To resolve this real-world failure pattern, the team introduced a multi-tier compaction strategy: writing micro-batches to a transient staging layer with Merge-on-Read enabled, combined with an automated background worker running asynchronous bin-pack compaction every hour to collapse small files into optimized 512 MB Parquet blocks. This decoupled streaming ingestion latency from downstream analytical read performance, restoring query planning speeds to less than 2 seconds.
Beyond table layout and staging strategies, a few high-leverage tweaks frequently deliver big performance improvements:
Avoid Over-Partitioning: Partitioning by year/month/day/hour seems organized, but if individual partitions contain only a few megabytes, query planning degrades. Aim for partitions holding at least 1 GB of data. If volumes are low, partition by month or rely purely on clustering.
Adopt Zstd Compression: Parquet files default to Snappy for historical compatibility. Switching to Zstandard (zstd) yields 20% to 40% better compression ratios with similar CPU overhead, saving storage costs and network transfer times.
Handle Data Skew: A merge job hanging at 99% usually indicates data skew where one worker is processing a disproportionate volume of rows tied to a single key (or NULL values). Filter out unneeded keys before joining or apply salting techniques to distribute execution load evenly.
Maintain Table Metadata: Frequent commits build up metadata logs over time. Run routine maintenance like VACUUM or snapshot expiration to clean out stale transaction records while retaining time-travel capabilities.
Choosing the Right Format: Delta Lake, Apache Iceberg, or Apache Paimon?
While table layout principles apply universally across formats, each format comes with distinct defaults and ecosystem advantages:
Delta Lake: Great fit for Spark-centric pipelines. Built-in capabilities like Z-Ordering, Deletion Vectors, and Liquid Clustering streamline layout management, though advanced automated tooling is most complete within Databricks platforms.
Apache Iceberg: Excellent choice for multi-engine architectures (Trino, Snowflake, Spark, Flink). Features like partition evolution make modifying existing partition schemes straightforward without rewriting underlying historical data files.
Apache Paimon: Purpose-built for high-frequency streaming updates. Utilizing an LSM-tree layout, it handles continuous streaming upserts smoothly alongside tools like Apache Flink.
Selection Summary: Pick Delta Lake for Spark-heavy ecosystems, Iceberg for multi-engine platform flexibility, and Paimon for high-velocity streaming ingestion paths.
What I’d Tell My Past Self
When a simple data pipeline stalls, our immediate instinct is often to write complexSpark configs or scale up compute clusters. In reality, most lakehouse performance issues stem from poor file sizing and unoptimized storage patterns rather than insufficient compute hardware.
Lakehouse execution speeds depend directly on file layout efficiency. Managing file sizes, isolating staging ingestion from final merges, and picking the right update model will yield faster pipelines without increasing cloud costs.
Ultimately, scaling cluster compute should be your last resort when optimizing lakehouse pipelines. By diagnosing object storage I/O patterns, enforcing proper target file sizes, leveraging clustering techniques, and isolating write-heavy streams behind staging buffers, you can achieve orders-of-magnitude performance gains while keeping compute overhead to a minimum.
