Part 2 — Finding What Changed in an Iceberg v3 Table, Without a CDC Tool

Row lineage and snapshots give you built-in change detection. Here’s the runbook.

Every data pipeline eventually asks the same question: what changed since I last looked? The usual answers are all workarounds — an updated_at column that someone has to remember to maintain, database triggers, or a separate CDC product bolted on to the side.

Apache Iceberg format version 3 builds the answer into the table format itself. Every row carries two hidden fields that the engine maintains for you, and every commit is stamped with a sequence number. Put those together and you can answer “what changed” with plain SQL.

This is a practical runbook. Every query in it was run against a real v3 table on a local Iceberg stack — Spark 3.5 writing, Trino 483 reading through a Web SQL editor — and the outputs shown are what actually came back. If you want to build the same environment, that’s covered in the companion post.

The mental model

An Iceberg table is a chain of snapshots. Every commit that changes data produces a new one and increments a table-wide sequence number.

In v3, every row additionally carries two hidden columns:

• _row_id — a permanent identity assigned when the row is first inserted. It never changes, no matter how many times the row is updated.
• _last_updated_sequence_number — the sequence number of the commit that last wrote this row. It moves every time the row is inserted or updated.

Compare a row’s stamp to the table’s current sequence, and you know whether it changed. That is the entire idea.

Three things this picture is making a point of:

Order 1’s stamp moved to 3 when it was updated. Order 2, never touched, keeps its stamp of 1 forever.

Order 5 simply disappears. A deleted row leaves no stamp behind. This matters a great deal later.

Compaction — sequence 4 — moved nobody’s stamp. It rewrote files, not rows. Lineage tracks logical change, so your maintenance jobs never pollute your change feed.

Where the numbers live

Three metadata sources, each answering a different question. All examples here are in Trino / Athena dialect — what you’d type into a SQL editor or the Athena console. The Spark spellings are at the end.

• Did anything change at all? → "orders$metadata_log_entries", columnlatest_sequence_number
• Which rows were inserted or updated? → row lineage, column "$last_updated_sequence_number"
• Which rows were deleted? → a time-travel diff: FOR VERSION AS OF … EXCEPTthe current table
• What kind of change was each commit? → "orders$snapshots", columns operation and summary

A note on the dollar signs: in Trino and Athena, hidden columns and metadata tables both use a $ prefix and must be double-quoted — "$row_id", "orders$files". Forget the quotes and you get a parser error.

The runbook

Step 0 — Establish a bookmark

Before you can detect change you need a reference point. Record the current sequence number and snapshot id together:

SELECT latest_snapshot_id, latest_sequence_number
FROM "orders$metadata_log_entries"
ORDER BY timestamp DESC
LIMIT 1;

latest_snapshot_id  | latest_sequence_number
---------------------+------------------------
  923266335020385310 |                      5

Store both values. The sequence number drives lineage queries; the snapshot id drives time travel. Persist them wherever your pipeline keeps state — a control table, DynamoDB, a file.

Why not just use the timestamp? Sequence numbers are strictly monotonic per commit and immune to clock skew. Timestamps are for humans.

Step 1 — Did anything change at all?

Re-run the Step 0 query and compare against your bookmark.

• Same number — nothing happened. Stop here.
• Higher — something changed. Continue to Step 2.
• Lower — the table was rolled back or replaced. Investigate before trusting lineage.

This is a metadata-only read. It scans no data and costs essentially nothing, so run it as often as you like.

Step 2 — Which rows were inserted or updated?

Filter on the lineage stamp. Anything written after your bookmark carries a higher sequence number:

SELECT order_id, status, "$row_id", "$last_updated_sequence_number"
FROM orders
WHERE "$last_updated_sequence_number" > 3      -- your bookmark
ORDER BY "$last_updated_sequence_number";

To tell an insert from an update, compare against the bookmark snapshot with time travel. A $row_id that didn't exist back then is an insert; one that did is an update:

WITH now_rows AS (
    SELECT "$row_id" AS rid, order_id, status,
           "$last_updated_sequence_number" AS seq
    FROM orders
    WHERE "$last_updated_sequence_number" > 3
),
then_rows AS (
    SELECT "$row_id" AS rid
    FROM orders FOR VERSION AS OF 3723179884882281014
)
SELECT n.order_id, n.status, n.seq,
       CASE WHEN t.rid IS NULL THEN 'INSERT' ELSE 'UPDATE' END AS change_type
FROM now_rows n
LEFT JOIN then_rows t ON n.rid = t.rid;

Step 3 — Which rows were deleted?

Lineage cannot answer this. A deleted row is no longer in the table to carry a stamp. Instead, diff the bookmark snapshot against the present:

SELECT order_id
FROM orders FOR VERSION AS OF 3723179884882281014   
-- snapshot at bookmark
EXCEPT
SELECT order_id
FROM orders;

Use "$row_id" instead of order_id when the business key itself might have been updated.

Step 4 — What happened in each commit?

When you need to know why the sequence moved — a bulk delete, a compaction, a schema change — read the snapshot log:

SELECT snapshot_id, operation, committed_at,
       element_at(summary, 'added-records')      AS added,
       element_at(summary, 'deleted-records')    AS deleted,
       element_at(summary, 'added-delete-files') AS dv_files
FROM "orders$snapshots"
ORDER BY committed_at;

Use element_at(summary, 'key') rather than summary['key']. The summary map only holds the keys relevant to each operation — a delete snapshot has no added-records — and Trino's bracket accessor throws Key not present in map on a missing key, whereas element_at returns NULL.

Get Sercan Bilgiç’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

What the operation column means for your change feed:

• append — an INSERT. Yes, new stamps appear.
• overwrite — an UPDATE or MERGE. Yes, stamps move.
• delete — a DELETE. Rows vanish; see Step 3.
• replace — compaction. No effect on lineage; physical only.

Step 5 — Advance the bookmark

Once processed, write the new sequence number and snapshot id from Step 1 back to your state store. Next cycle starts at Step 1.

A worked example

This is the real state of the demo table after a session of inserts, an update, two deletes, and a compaction. The bookmark was taken at sequence 3 (snapshot3723179884882281014).

Here is the metadata log, annotated:

latest_seq | snapshot        | what it was
------------+-----------------+----------------------------------------------
   NULL     | —               | CREATE TABLE — no data yet
   NULL     | —               | initial INSERT (seq 1); snapshot later expired
   2        | 8506…4853       | delete — order 5
   3        | 3723…1014       | overwrite — UPDATE order 1      <- bookmark
   3        | 3723…1014       | ADD COLUMN — metadata only, same seq
   4        | 3153…1616       | replace — compaction
   4        | 3153…1616       | expire_snapshots — metadata only, same seq
   5        | 9232…0385       | delete — order 6

Two things to notice. Sequence numbers repeat: schema changes and maintenance commit new metadata without touching rows. And the two NULLs are not corruption — they point at a snapshot that expire_snapshots has since removed. The log keeps the entry but can no longer resolve it.

Step 1. Current sequence is 5, bookmark is 3. Something changed.

Step 2. Rows inserted or updated since sequence 3:

(0 rows)

Nothing — and that’s correct. Sequence 4 was compaction (no logical change) and sequence 5 was a delete.

As a sanity check, the same query against the initial load instead:

order_id | status  | $last_updated_sequence_number
----------+---------+-------------------------------
        1 | SHIPPED |                             3

Only order 1 — the one row that was ever updated. The INSERT/UPDATE classifier from Step 2 confirms which it was:

order_id | status  | seq | change_type
----------+---------+-----+-------------
        1 | SHIPPED |   3 | UPDATE

Step 3. Rows deleted since sequence 3:

order_id
----------
        6

Order 6, deleted at sequence 5. Step 2 could never have surfaced this — which is exactly why both steps exist.

Step 4. The snapshot log, with the summary counts:

snapshot_id         | operation | added | deleted | dv_files
---------------------+-----------+-------+---------+----------
 8506594715504224853 | delete    | NULL  | 1       | NULL
 3723179884882281014 | overwrite | 1     | NULL    | 1        <- the UPDATE wrote a deletion vector
 3153255244570551616 | replace   | 5     | 6       | NULL     <- compaction: 6 in, 5 out, DV merged
  923266335020385310 | delete    | NULL  | NULL    | 1        <- the DELETE wrote a deletion vector

And the full current lineage of the table, for reference:

order_id | status  | $row_id | $last_updated_sequence_number
----------+---------+---------+-------------------------------
        1 | SHIPPED |       3 |                             3
        2 | NEW     |       4 |                             1
        3 | SHIPPED |       1 |                             1
        4 | NEW     |       2 |                             1

Notice that $row_id values 1–4 are not in order_id order. They were assigned in physical write order across partition files. Don't read meaning into them beyond identity.

The complete pattern

Put together as one repeatable procedure:

Guardrails

Snapshot retention must exceed your CDC lag. Step 3 time-travels to the bookmark snapshot. If expire_snapshots has already removed it, the deletes since then areunrecoverable — you're left with a full reconciliation. In the demo, the sequence-1 snapshot was expired within minutes, which is why it shows NULL in the metadata log. In production, set your retention comfortably beyond the longest gap between consumer runs.

Sequence numbers repeat in the metadata log. Schema changes and maintenance write new metadata under the same sequence. Always compare against the maximum, and use the snapshot operation to classify.

Compaction is invisible to lineage — by design. A replace snapshot increments the sequence but leaves every stamp untouched. Your change feed stays clean through maintenance windows. If you ever see stamps move during a replace, something else ran.

Lineage and equality deletes don’t mix. Per the spec, rows changed via equality deletes — which some streaming writers such as Flink use — are treated as delete-plus-fresh-insert, so they receive a new _row_id. Position deletes and deletion vectors, which Spark writes, preserve identity. Know which your writers use.

Sequence numbers are per-table. Don’t compare them across tables.

Appendix — the same queries in Spark

Spark’s dialect differs in three small ways: unquoted _ prefix instead of quoted $, dotted metadata tables instead of "table$name", and VERSION AS OF without FOR.

-- bookmark
SELECT latest_snapshot_id, latest_sequence_number
FROM lake.demo.orders.metadata_log_entries
ORDER BY timestamp DESC LIMIT 1;

-- upserts since bookmark
SELECT order_id, status, _row_id, _last_updated_sequence_number
FROM lake.demo.orders
WHERE _last_updated_sequence_number > 3;
-- deletes since bookmark
SELECT order_id FROM lake.demo.orders VERSION AS OF 3723179884882281014
EXCEPT
SELECT order_id FROM lake.demo.orders;
-- commit log
SELECT snapshot_id, operation, summary
FROM lake.demo.orders.snapshots
ORDER BY committed_at;