How We Cut Refresh Time and Compute Cost by ~60% — the Mechanics, Not Just the Headline
"We reduced compute costs by 60%" is a great line in a slide deck and a mostly useless one in a technical forum, because it doesn't tell you why. Here's the actual breakdown from a recent Delta Lake performance pass on an enterprise Lakehouse migration, so the number is reproducible logic rather than a marketing stat.
Start with measurement, not tuning knobs
The single biggest mistake I see is reaching for OPTIMIZE, broadcast hints, or repartitioning before looking at why a job is slow. Everything below came from reading the Spark UI / query profile first and matching the fix to the actual bottleneck — usually one of: too many small files, a shuffle-heavy join, or partition skew.
1. Small-file compaction (OPTIMIZE)
Streaming and incremental Bronze/Silver writes naturally produce lots of small files. Every file-open has overhead, and at scale that overhead dominates scan time.
OPTIMIZE silver.entity_table;
For frequently-updated tables, scheduling this as a periodic maintenance job (not just ad hoc) matters — file counts creep back up quickly on high-churn tables.
2. ZORDER for multi-predicate filters
Once files are right-sized, co-locating related data matters for anything with selective filters across multiple columns:
OPTIMIZE gold.reporting_table
ZORDER BY (region, effective_date);
Z-ordering is only worth it on columns that are actually used in filter predicates by downstream consumers — check query history before picking columns, don't guess.
3. Broadcast joins on dimension tables
Small reference/dimension tables joined against large fact tables were, in several cases, defaulting to a shuffle join because size estimates were off or auto-broadcast thresholds weren't tuned for the actual table sizes. Explicit broadcast hints removed unnecessary shuffle stages entirely:
from pyspark.sql.functions import broadcast
result = fact_df.join(broadcast(dim_df), "dim_key")
4. Adaptive Query Execution (AQE) for runtime skew correction
A handful of joins had a small number of very hot keys — classic skew. Rather than hand-coding salting logic everywhere, enabling AQE let Spark re-optimize join strategy and coalesce shuffle partitions at runtime:
spark.conf.set("spark.sql.adaptive.enabled", "true")
spark.conf.set("spark.sql.adaptive.skewJoin.enabled", "true")For the specific joins where AQE's default skew handling wasn't enough, we fell back to explicit salting — but that was the exception, not the starting point.
5. Predicate pushdown and partition pruning — the "free" wins
These come mostly for free with Delta once your filters actually target partition columns and your queries are written to let the file-scan layer do the filtering rather than a downstream .filter() after a wide read. Worth an explicit query-plan check (EXPLAIN) rather than assuming it's happening.
Where the ~60% actually came from
Roughly, in order of impact on our workloads:
- File compaction removing small-file scan overhead — the single largest contributor
- Broadcast joins removing shuffle stages on dimension lookups
- AQE correcting partition skew at runtime without manual salting
- Right-sized target file size (~128MB–1GB) balancing parallelism against per-file overhead
None of these are exotic. The result came from methodically matching each fix to a measured bottleneck rather than applying all of them blindly to every job — some tables only needed compaction, others only needed the join fix.
A caveat worth stating plainly
This number is representative of the specific workloads it was measured against, not a platform-wide guarantee — job characteristics (join shape, file churn rate, skew) vary enough that the same fixes won't move every table by the same amount. If you're citing a similar number in your own writeup, I'd recommend keeping the before/after methodology alongside it rather than the headline number alone.
Would be interested to hear what's driven the biggest wins in others' Delta optimization work — skew handling, file sizing, or something else entirely?
