You're Updating 1M Rows. Why Scan 100 Billion to Find Them?

Overview

Every MERGE INTO, and every join that feeds one, has to answer the same question: which rows in the target table match my source keys? You’re applying a CDC batch, upserting late events, correcting a few records. In most production pipelines, streaming or a daily batch, the source is much smaller than the target, and the matching rows are a tiny fraction of a large table.

Spark isn’t naive about this. It tries dynamic partition pruning (DPP), using the source keys to skip target partitions that can’t match. That works well when your updates cluster into a few partitions. But in a large fact table, late-arriving data lands more or less randomly across the table, so the source keys can touch hundreds of partitions, or every partition in the worst case. DPP prunes almost nothing, and Spark falls back to scanning and shuffling most of the target to find the rows. The cost ends up scaling with the size of the table, not the size of your change.

We covered one half of that pain in MERGE INTO Updates a Slice of Your Table — So Why Shuffle All of It?. This post is about the other half. Even a low-shuffle merge still has to locate the matching rows.

There’s a better answer than scanning everything. Keep an index that already knows where each key lives, and read only the files that hold matches.

The problem — finding rows is a full-table operation

Say you’re updating 1 million keys in a 10 TB table of ~100 billion rows, spread across roughly 20,000 data files of 512 MB each. Because the incoming source rows can sit in any data file, DPP can’t shrink the target by much for most real-world merges, so the plan has to consider all of them:

• Scan ~20,000 files to expose their keys.
• Shuffle the target so rows with equal keys co-locate with the source.
• Join, discover which files actually held matching rows, and discard the rest.

You paid to read and move 100 billion rows to touch a million. Predicate pushdown and DPP help only when your filter lines up with partitioning that happens to isolate the change set. On an arbitrary join key with randomly-distributed updates, they don’t fire, and you’re back to reading most of the table.

The idea — let an index point straight at the rows

An index turns “find the rows” from a scan into a lookup. Quanton maintains two kinds, both backed by the Hudi metadata table and exposed on Iceberg through XTable.

Index	Maps	Use it when the join key is
Record-Level Index (RLI)	record key → file + row position	the table’s record key
Secondary Index (SI)	any column value → record key	a non-key column you join or merge on

The two chain together. An SI on its own doesn’t reveal a file location — it resolves your join column to a record key, and the RLI then turns that record key into the exact file and row position.

For table formats that don’t support record keys, the index skips that hop and maps the column value straight to the _file and _pos that hold it.

Either way, the lookup returns the exact physical location of every matching row, not a probability and not a partition, the file, the row position within that file, and the partition it belongs to.

key           _file                          _pos    _partition
--------      ---------------------------    -----   ----------
1000234   →   data/part-00017-....parquet    4821    region=us
1000238   →   data/part-00017-....parquet    9110    region=us
1000512   →   data/part-00311-....parquet    277     region=eu

With that in hand, the engine skips straight to the files that actually hold matching rows and ignores the rest. How many files that is depends on your data. When your changes cluster into a few files, it’s a tiny fraction of the table; even when a million updates scatter across many files, the engine goes to them directly instead of scanning and shuffling all 20,000 to find them. Either way, that’s the theoretical minimum I/O to locate the change.

The rows still have to be written, and that’s where the low-shuffle columnar MERGE path takes over. It updates the matched files in place, columnar, without shuffling any file’s data across the network. That’s what matters most. The win isn’t that Quanton touches fewer files; it’s that it shuffles nothing, however many files the matches land in. The index finds the rows, the columnar merge writes them with no shuffle, and together they give an optimal merge path no matter how the matches are distributed.

How Quanton wires it into the plan

This isn’t partition pruning. The index lookup returns the actual parquet files and row positions that match, and Quanton hands those straight to the Arrow-based parquet reader, which reads only those files and skips to the relevant row groups inside them. It’s a targeted read, not a filter that hopes to skip partitions.

The bigger idea is Dynamic Indexes. The columns that hurt most are the ones you repeatedly join and merge on, exactly the keys that force Spark to shuffle enormous amounts of data. Quanton maintains an index on those columns and applies it automatically at plan time, so a query that used to shuffle the whole table now resolves its keys through the index instead. The optimization happens dynamically, per query, with no change to your SQL.

You don’t change the query. Turn the index-aware path on once, and Quanton applies it whenever a usable index is present:

MERGE INTO customers t
USING source_updates s
ON t.id = s.id
WHEN MATCHED THEN UPDATE SET t.amount = s.amount
WHEN NOT MATCHED THEN INSERT *

spark.quanton.sql.optimizer.dynamicPruning.enabled=true

The important part is that the decision is made per query, at plan time. Quanton doesn’t blindly use the index. It reads the column statistics for the join key to estimate how many target files the source keys could actually touch, and then picks the cheaper plan:

• If plain DPP already narrows the target to very few files, the index earns nothing, so Quanton just lets DPP run.
• When the keys are spread across most of the table and DPP would prune almost nothing — the common case for late-arriving updates — Quanton switches to the index lookup.

Same SQL either way. The plan adapts to the data in front of it, run by run, instead of committing to one strategy up front.

Full scan vs. index lookup

Updating ~1M keys scattered randomly across a 10 TB table (~20,000 files of 512 MB):

The work now scales with the size of your change, not the size of your table. That’s what MERGE was supposed to do all along.

Beyond keys — expression and vector indexes

A secondary index is just one shape of “map a value to where it lives.” The same machinery covers more:

• Expression indexes index a computed value like lower(email), date(ts), or substr(sku, 1, 4), so a merge or filter on that expression prunes the same way a raw-column index does.
• Vector indexes index embeddings of unstructured data like text, images, and audio, so you can find the nearest rows to a query vector without scanning every record. It’s the same lookup pattern, now inside the same table.

Different index, same principle — don’t scan to find something you can look up.

Takeaway

A join or a merge is, underneath, a search for the target rows that match your source. Scanning and shuffling the entire table to run that search is the expensive way to do it. An index answers the same question by pointing at the exact file and row position, so Quanton reads only the files that match, only the row groups inside them that hold matches, and skips the target shuffle. The cost of changing your data finally scales with how much you’re changing, not with how much you’ve stored.