Infra keeps getting faster, but the fix for a slow notebook is usually a config line, not a bigger cluster.
One Spark Config, 32 Minutes Saved: Replacing MERGE with Dynamic Partition Overwrite
By @Phani_sannala , co-authored with @sridharplv
Our gold notebook processed a few million rows daily and took 40+ minutes. The bottleneck was not the cluster. It was MERGE.
The insight: BW does not send changed rows, it sends slices
SAP BW style extracts do not tell you "these 37 rows changed." They hand you the complete, restated fiscal period, top to bottom. If the source gives you the authoritative full slice, a row-level reconciliation tool is answering a question you never asked.
What MERGE cost us
The join. Every run, a shuffle-heavy join against the full target, just to rediscover what the source contract already told us.
The first version of every gold notebook ended the same way, because thatโs what every tutorial and every instinct tells you to do with incremental loads:
MERGE INTO gold.working_capital t
USING staging s
ON t.company_code = s.company_code
AND t.fiscal_period = s.fiscal_period
AND t.doc_number = s.doc_number
WHEN MATCHED THEN UPDATE SET *
WHEN NOT MATCHED THEN INSERT *
Write amplification. Rewriting every Parquet file that contains a touched row.
Reasonable. Defensive. Wrong for this workload.
Key fragility. SAP composite keys (ROCANCEL, record modes, language keys) are a minefield. Get the key slightly wrong and MERGE does not error. It silently duplicates or updates the wrong rows.
The zombie row bug
Cost and speed are annoying. This next one is a correctness bug, and itโs the strongest argument in the whole post.
Say your gold table holds this for period 006:
fiscal_period doc_number amount
------------- ---------- ------
2026006 100 450
2026006 101 250
2026006 102 700
Yesterday, document 102 was reversed in SAP. Todayโs extract arrives with the complete restated period 006:
fiscal_period doc_number amount
------------- ---------- ------
2026006 100 500
2026006 101 250
Doc 100 got updated. Doc 101 is unchanged. And doc 102 is simply absent โ the sourceโs way of saying it no longer exists.
Now watch what MERGE does. It updates doc 100. It โupdatesโ doc 101 to an identical value (wasted write). And doc 102? MERGE never even looks at it. MERGE only reasons about rows that arrived. Rows that vanished from the source are invisible to it. Doc 102 stays in your gold table forever โ a zombie row โ and your period total is now overstated by 700.
You can bolt on a WHEN NOT MATCHED BY SOURCE THEN DELETE clause to handle this, but now you've made the expensive join even more expensive, and you'd better scope it perfectly to the incoming slices or you'll delete history you meant to keep. You're writing increasingly clever code to reimplement, badly, what partition overwrite gives you for free.
The fix: two lines
spark.conf.set("spark.sql.sources.partitionOverwriteMode", "dynamic")
df.write.insertInto("gold.working_capital", overwrite=True)
What we gained
Speed. 40+ minutes to under 8, on the same cluster. The reconciliation join is simply absent from the plan.
Correctness by construction. No zombie rows, no dependency on getting an SAP composite key exactly right.
Idempotency for free. Job dies halfway? Rerun it. The same partitions are overwritten and you land in the same end state.
The honest fine print
1. Slices must be complete. A partial extract will happily overwrite a full partition with 100 rows. Add a row-count sanity check so it fails loudly instead of deleting quietly.
2. insertInto matches columns by position, not by name. Reorder a select upstream and it will silently write revenue into the quantity column. Pin the final select to the target column order and assert the schema before the write.
3. Partition grain must match delivery grain. Partition by how the source delivers, not by what feels natural for queries.
4. replaceWhere works too. We preferred dynamic partition overwrite because the partitions to replace are inferred from the data, not from a predicate we would have to construct and keep correct every run.
Takeaway
MERGE is a great tool. It became a problem only because it was the default, applied without asking how the source actually delivers data. Before picking a write pattern, characterize the delivery pattern. Row-level CDC belongs to MERGE. Full-slice delivery belongs to partition overwrite.
