When a B-tree row index pays off on a Delta table, and when it does not, answered with the engine's own numbers.

Create the index

Index the key used by targeted writes and enable incremental maintenance:

CREATE INDEX idx_orders_id
ON TABLE sales.orders (order_id)
USING btree
WITH (auto_update = true);
The index is stored as a child Delta table under the parent table, not inside a proprietary replacement format.

Run a targeted update

The index helps the engine locate the matching row. The update itself still commits through normal Delta Lake transaction semantics.

UPDATE sales.orders
SET status = 'shipped',
    shipped_at = CURRENT_TIMESTAMP
WHERE order_id = 987654321;
This is most useful when the predicate matches one row or a small set of rows in a large table.

The same index helps MERGE and DELETE

MERGE INTO sales.orders AS target
USING staging.order_updates AS source
ON target.order_id = source.order_id
WHEN MATCHED THEN UPDATE SET
    status = source.status;
The expensive locate step can use the indexed key instead of searching every parent file.

Keep the index current

With auto_update = true, each parent commit incrementally re-indexes changed files. For bulk loads, manual maintenance can be cheaper:

ALTER INDEX idx_orders_id
ON TABLE sales.orders
SET (auto_update = false);

INSERT INTO sales.orders
SELECT * FROM staging.orders_today;

REBUILD INDEX idx_orders_id
ON TABLE sales.orders;
A rebuild scans the parent table and replaces the child index atomically.

When a B+ tree fits

  • Point updates by order, customer, device, or transaction ID.
  • Narrow range lookups on uneven or random key distributions.
  • Repeated MERGE operations on the same business key.
  • Selective DELETE statements on large Delta tables.

Indexes add storage and write cost. They do not help broad scans that touch most of the table, and small tables may already be cheap to scan.

Inspect the index

DESCRIBE INDEXES ON TABLE sales.orders;
The result shows the algorithm, indexed columns, auto-update setting, parent version, leaf count, and size.

Measure it with SHOW STATS

You do not have to take the speedup on faith. Wrap the same lookup in SHOW STATS ACTUAL before and after you create the index and read one metric, delta_scan_exec_count. A value of 1 means a table scan ran, and 0 means the index answered the query with no scan at all.

SHOW STATS ACTUAL
SELECT txn_id, amount FROM sales.payments WHERE txn_id = 90000029;
Before the index this reports delta_scan_exec_count = 1; after the index it reports 0.

On a card-payment ledger spread across twelve files with shuffled transaction ids, that lookup opened a reader for all twelve files (scan_share_count = 12) just to return a single row. With a B+ tree index on the id, the same query reports delta_scan_exec_count = 0: the index pointer path resolved the row and no table scan ran.

The same metrics show where an index does not earn its place. A settlement table whose ids were written in sorted order already prunes to one file (scan_share_count = 1) with no index at all, because Delta data skipping does the work for free. A broad rollup that matches most rows reads every file whether the index exists or not. And DESCRIBE INDEXES exposes the running cost: leaf_count grows on every committed write while auto-update is on, which is the price a write-heavy table pays for fast reads. Turn auto-update off and the next write is cheap but the index goes stale until you rebuild it.

Every number here is reproducible. It is asserted, read, write, and update, in the delta-row-index-payment-ledger demo that ships with DeltaForge.

Why this matters

Delta Lake is excellent at analytical scans, but a selective update should not need an analytical scan just to find one row. A row-level B+ tree adds that lookup path while leaving the underlying Delta table interoperable.