Resolving a Slow CDC Bronze-to-Silver Merge on Databricks with Liquid Clustering

I ran a Lakehouse on Databricks behind a multi-tenant SaaS platform. Hundreds of tenants shared the same infrastructure. Through CDC, the platform ingested 150–200 million orders and customer change events a day.

In a typical single-tenant medallion setup, Gold carries the heavy read load, and Silver stays mostly internal. But on this platform, Silver was tenant-facing too. Near-real-time dashboards and order-status lookups queried it directly. Gold’s scheduled, aggregated refresh was too stale for that use case.

That meant one thing had to hold up no matter how much the platform grew: the bronze-to-silver merge that kept Silver current. It didn’t. Drawing from that experience, this article walks through how a merge like this breaks down, in the order the problem tends to reveal itself, not the order a checklist would suggest.

1 – Bronze-to-Silver Merge Performance Degradation on Databricks

The pipeline ran as a Databricks Job orchestrating a Lakeflow Declarative Pipeline, applying CDC changes into Silver via AUTO CDC rather than hand-written merge logic:

   
    dlt.create_auto_cdc_flow(
    target="silver.order_details",
    source="cdc_batch",
    keys=["tenant_id", "order_id"],
    sequence_by=col("updated_at"),
    apply_as_deletes=col("operation_type") == "d",
    stored_as_scd_type=1
  )
 

As tenant count and daily CDC volume grew, this merge began taking noticeably longer to complete, degrading past what the near-real-time SLA for tenant-facing Silver reads could tolerate. Bronze itself was never the problem; ingestion stayed cheap regardless of scale. A merge like this has a few places the cost can hide: how much of the target table it scans, how it shuffles data to match rows, and how much it ends up rewriting. Diagnosing it well means checking each one in turn, starting with the cheapest to test.

A slow merge on a growing table is rarely one thing going wrong. It’s usually one real problem wearing two or three cheaper-looking disguises.

2 – Spark Shuffle and Adaptive Query Execution (AQE) Tuning for MERGE INTO

AUTO CDC still generates a merge-style join under the hood, keyed on exactly the columns declared in keys. Apache Spark has to redistribute both sides of that join, so rows sharing tenant_id and order_id land together, and that redistribution is a shuffle. The shuffling is like reshuffling two decks of cards, so matching suits end up in the same pile. Below Spark’s autoBroadcastJoinThreshold, the smaller side gets broadcast instead, and the shuffle is skipped entirely; past that size, every merge shuffles, and cost scales with how much data moves.

I found a statically set spark.sql.shuffle.partitions value, left over from a much smaller deployment, that no longer matched the data volume at the time:

   
   SET spark.sql.adaptive.enabled = true;
   SET spark.sql.adaptive.coalescePartitions.enabled = true;
   SET spark.sql.adaptive.skewJoin.enabled = true;
   SET spark.sql.adaptive.advisoryPartitionSizeInBytes = 134217728; -- 128 MB

Adaptive Query Execution re-plans shuffle sizing at runtime instead of trusting a number set once and forgotten. I checked two more Databricks-native levers alongside it, low shuffle merge, which routes unmatched target rows around the shuffle entirely, and deletion vectors, which mark a changed row without rewriting the whole file it lives in, both largely automatic on current runtimes but worth confirming rather than assuming.

This helped, but only partially, and that’s the tip worth remembering: shuffle tuning makes the work Spark does more efficient, it doesn’t reduce how much of the table gets touched in the first place. If you’re only tuning shuffle, you’re optimizing the wrong side of the equation.

3 – Partitioning vs Merge Key Mismatch in Delta Lake

Checking the Spark UI’s scan metrics against incoming batch size told me the real story. The merge was opening far more of the table than a batch this size should ever need. AUTO CDC’s keys, tenant_id and order_id, define the merge’s actual join predicate. The table itself was partitioned on ingestion_date, an unrelated column. Delta Lake can only skip a partition when the query filters on the partitioning column, so nearly every date partition had to be opened on every run, for two compounding reasons:

CDC corrections and late-arriving updates can land on any day, regardless of when the order was created. And every tenant’s rows were interleaved inside every date partition, so a single tenant’s batch couldn’t skip past a partition just because it belonged to someone else, like a library that shelves books by the day they arrived instead of by subject, so finding one topic means checking every shelf.

A read query hits the identical wall, no matter how well-tuned the shuffle downstream is:

   SELECT * FROM silver.order_details
   WHERE tenant_id = 'tenant_123' AND order_id = 'ord_9981';

This is where a lot of merge-performance investigations quietly stall. It’s easy to keep on tuning things that’s cheap to tune. The layout mismatch needs to be fixed. But most people look at this as the last option.

4 – Z-Order, OPTIMIZE, and Cluster Scaling: Why They Don’t Fix Partition Pruning

A few more standard levers were worth considering before changing the table layout.  It’s worth being honest about why each one falls short.

Z-Order – OPTIMIZE … ZORDER BY (tenant_id, order_id) improves row ordering within whatever partitions get scanned. It doesn’t reduce how many get opened. Z-Order isn’t deprecated, it’s just solving a different problem than the one you have.

Scheduled OPTIMIZE, Optimized Writes, and Auto Compaction – keeping high-frequency CDC writes from fragmenting into too many small files cuts file-listing overhead within each partition. It doesn’t change which partitions a merge has to touch.

A bigger Job Cluster – more workers process the same scan volume faster, at a proportionally higher cost. That’s not a fix; it’s a more expensive way of doing the same unnecessary work.

Don’t mistake a partial improvement for progress toward the actual fix. Each of these levers can genuinely help, and each one will plateau at the same ceiling, because none of them change how many files get scanned.

5 – Liquid Clustering Implementation on Databricks

Databricks’ current recommended replacement for PARTITIONED BY plus ZORDER BY is Liquid Clustering, available on Databricks Runtime 15.4 LTS and above. The two layout strategies are mutually exclusive on a single table. Unlike Z-Order, which reorders data within an existing partition scheme, Liquid Clustering replaces the scheme entirely. Instead of physical partition directories, it organizes files around declared clustering keys and uses Delta Lake’s file-level statistics to skip files that cannot contain a match.

  
  ALTER TABLE silver.order_details
  REPLACE PARTITIONED BY WITH CLUSTER BY (tenant_id, order_id);

  OPTIMIZE silver.order_details;
 

tenant_id was the leading clustering key because it was the lower-cardinality dimension used in nearly every merge and query filter. It was followed by order_id, matching the keys already declared in the AUTO CDC flow. Migrating means removing the old partitioning, and Databricks physically reorganizes the table’s files as part of this step; at TB scale, that rewrite is real work worth scheduling for a low-traffic window rather than treating as instant. A genuine advantage over the old approach: unlike a partition column, the clustering key can be changed again later without a full rewrite, if the access pattern shifts.

A brand-new table skips the migration question entirely:

  
   CREATE TABLE silver.order_upsell (
    tenant_id STRING,
    order_id STRING,
    upsell_product_id STRING,
    updated_at TIMESTAMP
   )
   CLUSTER BY (tenant_id, order_id);
 

Where the ideal secondary key isn’t obvious upfront, CLUSTER BY AUTO hands the decision to Predictive Optimization, available only on Unity Catalog managed tables, which watches actual query and merge patterns and re-selects keys asynchronously on serverless compute:

   
   ALTER TABLE silver.order_attribute
   REPLACE PARTITIONED BY WITH CLUSTER BY AUTO;

A layout fix isn’t glamorous work. It’s also the only one on this list that removes the ceiling instead of raising it slightly.

Summary: Databricks Merge Optimization with Liquid Clustering

Tune shuffle first, it’s cheap, low-risk, and directly relevant since every merge is a join underneath. But don’t mistake a shuffle improvement for a solved problem. It only fixes how efficiently Spark moves data it was always going to touch. Z-Order, compaction, and a bigger cluster are reasonable for next steps within an existing layout, and each one will plateau at the same ceiling for the same reason: none of them change what gets scanned.

The layout is what decides everything downstream of it. Get the clustering key wrong, or skip re-evaluating a partitioning decision made years earlier for a different scale, and no amount of shuffle tuning or extra compute buys back what the layout is quietly costing on every single run.

Once the table was reclustered on tenant_id and order_id, the merge went back to scanning only what a batch that size should ever touch, no bigger cluster, no extra compute, just a layout that finally matched the query it was built to serve. Performance work on a merge like this isn’t really about Spark configuration. It’s about whether the table’s physical shape still agrees with how the business queries it, and revisiting that question before the business finds out the hard way.