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.
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 written | source number, set on insert only | source number, updated on change | silver batch number on every touched row | Delta change data feed (table version) | Iceberg changelog view |
|---|---|---|---|---|---|
| 1. first load (insert) | caught | caught | caught | caught | caught |
| 2. new row (append) | caught | caught | caught | caught | caught |
| 3. Type 1 update (MERGE) | MISSED | caught | caught | caught | caught |
| 4. Type 2 change (close + insert) | caught | caught | caught | caught | not run |
| 5. hard delete | MISSED | MISSED | MISSED | caught | caught |
| 6. full overwrite (D1 changed) | MISSED | MISSED | caught+ | caught+ | caught |
| 7a. batch 9 commits first | MISSED | caught | caught | caught | not run |
| 7b. batch 8 commits later | MISSED | MISSED | MISSED | caught | not run |
What each miss means, in plain words
- A number set on insert and never updated misses every update. Your worry was right: if
_seqonly 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.
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?
There are still two different numbers, with two different jobs. Do not mix them up:
| Number | Job | Who 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 ordering | The source system; carried through bronze into silver |
| The table's commit version or snapshot id | Bookmarks reads coming OUT of silver: what has gold already seen | The table format, at commit |
What this needs, and the catches
| Need | Why |
|---|---|
| Turn the change log on when the table is created | Delta: delta.enableChangeDataFeed = true. Iceberg keeps snapshots anyway; the changelog view reads them. |
| Keep history longer than gold can fall behind | VACUUM (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 MERGE | Our 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 differences | Overwrites are correct but make every row look changed. |
| A column bookmark only where there is no change log | Files dropped in folders or API pages: then every writer must follow the contract, and deletes must be soft deletes. |
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