Home › Data engineering › Gold data modelling › Silver to gold › Who makes the bookmark?

Silver to gold · Learn it · 8 min read

Who makes the bookmark, and can gold miss a change?

Incremental loading is only as safe as its bookmark. This page tests seven ways silver can be written against four ways gold can read it, and shows which misses what. This is one page of Silver to gold.

Part A · 8

Who makes the bookmark, and can gold miss a change?

In the story, each silver row had a _seq column and gold read "rows with _seq after my bookmark". That was a simplification, and it hides a real danger. Silver is written in many ways: new rows appended, Type 1 updates, Type 2 close-and-insert, deletes, full overwrites, sometimes two writers at once. A column that every writer must remember to set correctly will, one day, be set wrong, and gold will silently miss a change.

The rule: how gold reads silver must not depend on how silver was written. So the bookmark should not be a column the writers fill in. It should be the table's own change log, which the table format keeps for every write, numbered when the write commits.

many ways to write silversilver tableone way to read itappend new rowsMERGE: Type 1 updateMERGE: Type 2 close + newdelete rowsfull overwritetwo writers at oncethe table's own change logv0 WRITEv1 WRITEv2 UPDATEv3 MERGE ...numbered at commit, in ordergold reader'all changes afterversion N, up to M'Writers can work any way they like. The reader only asks the table what changed since its bookmark.

Writers vary; the reader asks the table for changes since a version.

The test: seven ways of writing silver, four ways of reading it

We wrote one silver dealer table in each of these ways and, after each write, ran four gold readers from their own bookmarks. "caught" means the reader saw the changed dealer. "caught+" means it saw it, plus unchanged rows (safe, but extra work). "MISSED" means gold would never pick the change up. Delta 3.2 on Spark 3.5.1; the expected results are a table in the test, and the run fails on any difference. The Iceberg column is a separate run on Iceberg 1.9.2 (Type 2 and two-writer cases not run there).

How silver was writtensource number, set on insert onlysource number, updated on changesilver batch number on every touched rowDelta change data feed (table version)Iceberg changelog view
1. first load (insert)caughtcaughtcaughtcaughtcaught
2. new row (append)caughtcaughtcaughtcaughtcaught
3. Type 1 update (MERGE)MISSEDcaughtcaughtcaughtcaught
4. Type 2 change (close + insert)caughtcaughtcaughtcaughtnot run
5. hard deleteMISSEDMISSEDMISSEDcaughtcaught
6. full overwrite (D1 changed)MISSEDMISSEDcaught+caught+caught
7a. batch 9 commits firstMISSEDcaughtcaughtcaughtnot run
7b. batch 8 commits laterMISSEDMISSEDMISSEDcaughtnot run

What each miss means, in plain words

  • A number set on insert and never updated misses every update. Your worry was right: if _seq only goes up for new records, an updated record is never picked up again.
  • The source's change number, updated on each change, catches updates but misses three things. A hard delete leaves no row to read. A full overwrite from a full copy brings no new change numbers. And when two loads commit out of order, a lower number commits after gold has passed it.
  • A silver batch number stamped on every row a write touches is better: it catches updates and overwrites. But it only works if every writer follows the rule, it still misses hard deletes, and it still misses out-of-order commits, because the number is taken when a batch starts, not when it commits.
  • The table's change log (Delta change data feed, Iceberg changelog view) caught every case. The table numbers each commit itself, in commit order, and records inserts, updates, deletes and overwrites, whoever wrote them and however.
timebatch 8 startsgets number 8batch 9 startsgets number 9batch 9 commitstable version 7gold runsbookmark: 9 / v7batch 8 commitstable version 8gold runsby number: 8 is not after 9, so MISSEDby table version: v8 is after v7, caught

A number taken at the start of a batch can arrive late. A table version is given at commit.

Your question about a full overwrite of silver

A full overwrite replaces every row. In our run, Delta's change log recorded it as 3 deletes and 2 inserts: every row looks changed, so gold rebuilds the windows of every row. That is correct (nothing is missed), but it costs a full rebuild. Iceberg's changelog view removed the rows that did not really change and reported only D1. Either way, the better habit is not to overwrite silver: compare the full copy with silver's current rows and write only the differences (a MERGE on a row hash). Then the change log stays small and gold rebuilds only what changed. This is what the rulebook means by "silver turns the copy into changes".

So who generates the bookmark?

The table does. Gold keeps, per silver input, the last table version (Delta) or snapshot id (Iceberg) it has read. A run fixes the high mark as the latest version at its start, reads all changes between the two, and moves the bookmark last. No writer has to set anything, so any number of writing styles can feed the same gold.

There are still two different numbers, with two different jobs. Do not mix them up:

NumberJobWho makes it
The source's change sequence (CDC log position, source timestamp)Orders changes going INTO silver: which update came last, out-of-order arrivals, SCD Type 2 orderingThe source system; carried through bronze into silver
The table's commit version or snapshot idBookmarks reads coming OUT of silver: what has gold already seenThe table format, at commit

What this needs, and the catches

NeedWhy
Turn the change log on when the table is createdDelta: delta.enableChangeDataFeed = true. Iceberg keeps snapshots anyway; the changelog view reads them.
Keep history longer than gold can fall behindVACUUM (Delta) or snapshot expiry (Iceberg) deletes old versions. If gold's bookmark is older than the oldest version left, the harness must notice and do a logged full rebuild instead.
Write SCD Type 2 as one MERGEOur test wrote close and insert as two commits (UPDATE, then WRITE). A reader between them would see a closed version with no new one. One MERGE is one commit.
Turn full copies into differencesOverwrites are correct but make every row look changed.
A column bookmark only where there is no change logFiles dropped in folders or API pages: then every writer must follow the contract, and deletes must be soft deletes.
What our build does today (checked in the framework code, 14 Nov): the real harness does not read "rows changed since a bookmark" at all. Every run, it recomputes every window in the lookback range from silver's current rows, compares each window's numbers with what is published, and replaces only the windows that differ. Its watermarks only say how far each input has loaded, for completeness. So no way of writing silver can make it miss a change inside the lookback: deletes, overwrites and out-of-order commits are all simply recomputed. The price is a rebuild of the whole lookback range every night, and a change older than the lookback is only picked up through a correction plan, or the owed gold rerun after a silver rebuild. The table change log is therefore owed as an addition, not a fix: to skip a run when silver did not change, to shrink the work to the changed windows, and to spot changes older than the lookback and raise them instead of missing them. The story in Part A shows the change-driven variant because it is easier to follow.
The writers and readers used in this test (topologies.py)
"""Silver written seven ways; gold reading changes three ways. Shown on /learn/silver-to-gold/#bookmark.

Silver row columns:
  _src_seq        the source's change number, carried through (updated when the source sends a change)
  _src_seq_first  the same number, but set on insert and never updated (a common mistake)
  _batch          a silver batch number stamped on every row a write touches (a writer contract)
The third reader uses no column at all: the table's own change log (Delta change data feed, _commit_version).
"""
from delta.tables import DeltaTable
from pyspark.sql import functions as F

SCHEMA = ("dealer_id string, region string, name string, is_current boolean, valid_to string, is_deleted boolean, "
          "_src_seq long, _src_seq_first long, _batch long")


def create(spark, path, rows, batch):
    df = spark.createDataFrame([(d, r, n, True, "open", False, s, s, batch) for d, r, n, s in rows], SCHEMA)
    df.write.format("delta").option("delta.enableChangeDataFeed", "true").save(path)


def insert(spark, path, rows, batch):
    spark.createDataFrame([(d, r, n, True, "open", False, s, s, batch) for d, r, n, s in rows], SCHEMA) \
        .write.format("delta").mode("append").save(path)


def upsert_type1(spark, path, dealer, col, value, src_seq, batch):
    """Overwrite a value in place. _src_seq follows the source; _src_seq_first is left alone; _batch is stamped."""
    DeltaTable.forPath(spark, path).update(f"dealer_id = '{dealer}' AND is_current",
                                           {col: F.lit(value), "_src_seq": F.lit(src_seq), "_batch": F.lit(batch)})


def scd2_change(spark, path, dealer, region, src_seq, batch):
    """Close the current version, insert a new one."""
    t = DeltaTable.forPath(spark, path)
    cur = t.toDF().where(f"dealer_id = '{dealer}' AND is_current").first()
    t.update(f"dealer_id = '{dealer}' AND is_current",
             {"is_current": F.lit(False), "valid_to": F.lit("12 Nov"), "_src_seq": F.lit(src_seq), "_batch": F.lit(batch)})
    insert(spark, path, [(dealer, region, cur.name, src_seq)], batch)


def hard_delete(spark, path, dealer):
    DeltaTable.forPath(spark, path).delete(f"dealer_id = '{dealer}'")


def full_overwrite(spark, path, rows, batch):
    """Replace the whole table with a full copy from the source. A full copy usually brings no change numbers,
    so each row keeps the number it had (or 0 if new); the batch stamp goes on every row."""
    old = {r.dealer_id: r for r in spark.read.format("delta").load(path).where("is_current").collect()}
    out = [(d, reg, n, True, "open", False, old[d]._src_seq if d in old else 0, old[d]._src_seq_first if d in old else 0, batch)
           for d, reg, n in rows]
    spark.createDataFrame(out, SCHEMA).write.format("delta").mode("overwrite").save(path)


# ------------------------------------------------------------------ the gold readers
def read_by_column(spark, path, col, watermark):
    df = spark.read.format("delta").load(path)
    seen = sorted({r.dealer_id for r in df.where(F.col(col) > watermark).select("dealer_id").collect()})
    high = df.agg(F.max(col)).first()[0] or 0
    return seen, max(high, watermark)


def latest_version(spark, path):
    return DeltaTable.forPath(spark, path).history(1).first()["version"]


def read_change_feed(spark, path, watermark):
    latest = latest_version(spark, path)
    if latest <= watermark:
        return [], watermark
    ch = (spark.read.format("delta").option("readChangeFeed", "true").option("startingVersion", watermark + 1)
          .option("endingVersion", latest).load(path).where("_change_type != 'update_preimage'"))
    return sorted({r.dealer_id for r in ch.select("dealer_id").collect()}), latest