cleandataengineering.aiData Platform, from the big picture to the code
You are here: Start here › Bronze to silver › The thirteen stages › 12 History › Deletes

Data PlatformZoom level 4

Deletes: how they are detected and how they are applied

A row that is deleted at the source must not stay in silver looking alive. Silver also must not lose the fact that the row existed. This page follows a delete from the moment the platform learns of it to what the silver table holds afterwards.

There are two separate questions, and the manifest answers each with its own setting. The first is detection: how does the platform learn that a row is gone? The second is application: what does silver do about it?

Change feeda row with operation DFull copya key is missingAny other sourcenothing is detectedOpen: Mass-delete guardMass-delete guardin ingestionzoom in ›Delete eventplaced in source ordermark_deletedflag and timeremoverow taken outnonedelete ignored
The left column is how a delete is found. The right column is what it does to silver. In between, every delete becomes an ordinary event in the key's history.

Detection: three settings

The setting is load.deletes.detect. Each row below is one of its values.

ValueHow the delete is foundWhere in the codeTyre datasets
change_feedThe change feed sends a row for the delete, with the operation D in the column _op. The row carries the key, the order value and the commit time._incoming keeps the row as an eventSales order lines, deliveries
key_compareA full copy is the whole source table. A key that is live in silver and missing from the newest copy was deleted at the source._key_compare_deletes makes a delete event for itMaterials, presses
noneNothing is detected.Dealers, dealer sell-out, and the three event datasets

A delete in a change feed

src/dataplatform/stages/history.py:50A delete row is an event like any other, unless the dataset applies no deletes.
def _incoming(df: DataFrame, plan: SilverPlan) -> DataFrame:
    out = df.withColumn("_op", _op(df)).withColumn("_ts", _ts(df, plan)).withColumn("_existing", F.lit(False))
    out = out.withColumn("_dts", F.col("_ts"))  # when a delete took effect; a stored version brings its own (_existing)
    if plan.delete_apply == "none":
        out = out.where(F.col("_op") != "D")
    return out

Nothing special happens at detection. The delete row passes the earlier stages like any row, because it has a key and the business columns. Here it gets its operation and its time. If the dataset's apply rule is none, the row is dropped on the last line and the delete never reaches history.

A key missing from a full copy

src/dataplatform/stages/history.py:69Making delete events for keys the newest copy no longer has.
def _key_compare_deletes(inc: DataFrame, cur: DataFrame, plan: SilverPlan) -> DataFrame | None:
    """A full copy is the whole source: a key live in silver and missing from the copy was deleted at the source
    (DPC-0017). The delete takes the copy's order value and time. The mass-delete guard has already held any copy
    missing too many keys (DPC-0054)."""
    if plan.delete_detect != "key_compare" or plan.delete_apply == "none" or plan.ingest_read != "full_copy":
        return None
    live = cur.where(F.col("_is_current") & ~F.col("_is_deleted"))
    gone = live.join(inc.select("_key_hash").distinct(), "_key_hash", "left_anti")
    copy = inc.agg(
        F.max(plan.order_by).alias("_copy_pos"),
        F.max("_ts").alias("_copy_ts"),
        F.max("_batch_key").alias("_copy_batch"),
        F.max("_source_file").alias("_copy_file"),
        F.max("_window").alias("_copy_window"),
    ).where(F.col("_copy_pos").isNotNull())
    # `copy` is a global aggregate (agg with no grouping): one row or none, so the product has exactly as many rows as
    # `gone`; it stamps the copy's order value and time on each deleted key. Broadcast: no shuffle of `gone`.
    # shiftleft: allow SL-COST-CODE-14 -- intended: `copy` is a one-row global aggregate, so no row multiplication
    dels = _existing(gone).crossJoin(copy.hint("broadcast"))
    return dels.withColumns({
        plan.order_by: F.col("_copy_pos"),
        "_pos": F.col("_copy_pos"),
        "_ts": F.col("_copy_ts"),
        "_dts": F.col("_copy_ts"),
        "_op": F.lit("D"),
        "_existing": F.lit(False),
        "_batch_key": F.col("_copy_batch"),
        "_source_file": F.col("_copy_file"),
        "_window": F.col("_copy_window"),
    }).drop("_copy_pos", "_copy_ts", "_copy_batch", "_copy_file", "_copy_window")
  1. Three conditions. The function does nothing unless detection is key_compare, the apply rule is not none, and the dataset is read as a full copy.
  2. Find the missing keys. live is silver's current rows that are not deleted. gone is the live rows whose key is not in the incoming copy.
  3. Read the copy's stamp. copy is one row holding the copy's highest order value, its latest time, and its batch, file and window.
  4. Build the delete events. Each missing key's stored row is turned into an event and stamped with the copy's values. The operation is set to D and the event is marked as new. So the delete takes the copy's order value and time.

The result is a delete event that looks just like one from a change feed. From here on the two kinds of delete are handled by the same code.

The mass-delete guard: a short copy never reaches silver

Finding deletes by comparison has a danger. If an extract breaks and delivers only part of the table, every key it left out looks deleted. The guard stops this in ingestion, before the copy is written to bronze. Because silver reads only loaded batches, a held copy is never compared. The ingestion steps page covers the other entry steps.

src/dataplatform/ingest/steps.py:244The guard: the share of known keys missing from the copy, against the declared limit.
def mass_delete_problem(spec: Spec, rows: list[Row], previous: set[tuple[Any, ...]] | None,
                        tokenizer: Tokenizer | None = None) -> str:
    """A full copy that would delete more than the declared share of the last accepted copy's keys (DEL-04).
    `previous` holds the keys as stored (a restricted key column holds its token), so the copy's keys are compared as
    stored too: with a `tokenizer`, a restricted key column is tokenised the way `protect` will."""
    limit = spec.load.get("mass_delete_max_share")
    if spec.read != "full_copy" or limit is None or not previous:
        return ""
    classes = {c.name: c.classification for c in spec.columns if c.classification in RESTRICTED}

    def stored(r: Row, k: str) -> Any:
        if tokenizer is not None and k in classes and r[k] not in (None, ""):
            return tokenizer.token(classes[k], str(r[k]))
        return r[k]

    now = {tuple(stored(r, k) for k in spec.primary_key) for r in rows}
    gone = len(previous - now) / len(previous)
    if gone > float(limit):
        return (
            f"mass delete held: {len(previous - now)} of {len(previous)} keys ({gone:.1%}) are missing, "
            f"more than the {float(limit):.0%} allowed (DEL-04); nothing appended, dataset blocked"
        )
    return ""

The function returns a reason when the copy must be held, and an empty text otherwise. It only acts on a full copy, only when the dataset declares a limit, and only when there are known keys to compare with. It counts the known keys that the copy lacks, divides by the number of known keys, and holds the copy when the share is above the limit. At exactly the limit the copy loads.

src/dataplatform/ingest/pipelines.py:234What the copy is compared with: silver's live keys, or the last accepted copy while silver is empty.
def _guard_keys(ctx: Ctx, last_ok: str) -> set[tuple[Any, ...]] | None:
    """What the mass-delete guard compares a full copy with: silver's current keys (DPC-0054), or the last accepted
    copy in bronze while silver has never been written (the first days, or a binding without silver)."""
    s = ctx.spec
    if ctx.silver is not None:
        keys = ctx.silver.current_keys(s.source, s.dataset, s.primary_key)
        if keys is not None:
            return keys
    return ctx.writer.keys(s.table, s.primary_key, {"_batch_key": last_ok}) if last_ok else None
src/dataplatform/adapters/spark_silver.py:244Silver's answer: the keys of rows that are current and not deleted.
    def current_keys(self, source: str, dataset: str, keys: tuple[str, ...]) -> set[tuple[Any, ...]] | None:
        df = self._read(self.silver_path(source, dataset))
        if df is None:
            return None
        live = df.where(F.col("_is_current") & ~F.col("_is_deleted")).select(*keys).distinct()
        return {tuple(r[k] for k in keys) for r in live.collect()}

The comparison is with silver's live keys. That choice matters. Keys silver has already marked deleted are not counted as missing again, so a table that slowly shrinks does not trip the guard. Before silver has ever been written, the guard compares with the last copy that was accepted into bronze.

data-platform-catalog/slices/02-tyre/manifest/datasets/erp.material.yaml:21Materials: deletes by key compare, marked, and a copy missing more than 5 percent of the keys is held.
history: {kind: master, track_columns: [description, compound_code, rim_size_in, tyre_class]}
load:
  pattern: history
  history_order_by: extracted_at
  batch_key: [source_batch_id]
  new_columns: hold_in_bronze
  deletes: {detect: key_compare, apply: mark_deleted}
  quarantine_stop_share: 0.0
  mass_delete_max_share: 0.05

A held copy is logged with the status held_mass_delete, nothing is appended, and the dataset run fails. The next good copy is compared with the last accepted one, not with the held one.

Application: three settings

src/dataplatform/silver/plan.py:64The three values of load.deletes.apply.
DELETE_APPLY = ("mark_deleted", "remove", "none")
ValueWhat happens to the key in silverWhere in the code
mark_deletedThe row stays. _is_deleted is true and _deleted_at holds the delete's time.scd2 and scd1 set the two columns
removeEvery row of a key whose current version is deleted gets the action delete, and the merge removes them all._finish
noneThe delete is ignored. The key stays as it was._incoming drops the row, and _key_compare_deletes returns nothing
src/dataplatform/stages/history.py:120The remove rule: a key whose current version is deleted loses every row it has.
def _finish(out: DataFrame, stale: DataFrame | None, plan: SilverPlan) -> DataFrame:
    out = out.drop(*[c for c in WORK if c in out.columns]).withColumn("_action", F.lit("upsert"))
    if plan.delete_apply == "remove":  # a key whose current version is deleted loses every version it has
        live_delete = (F.col("_is_current") & F.col("_is_deleted")).cast("int")
        gone = F.max(live_delete).over(Window.partitionBy("_key_hash")) > 0
        out = out.withColumn("_action", F.when(gone, F.lit("delete")).otherwise(F.lit("upsert")))
    return out if stale is None else out.unionByName(stale, allowMissingColumns=True)

What type 2 and type 1 do with a delete

Both treat the delete as an event placed in the key's source order. They differ in what a row is.

Type 2Type 1
Where the delete landsIn the version that is open at the delete's place in the order. It opens no version of its own.It competes with the other events of the key. It wins when it has the highest order value.
What silver holds afterwardsThe same version, still current, with the same start and row key, flagged deleted.The one row of the key, flagged deleted. Its _seq_from is the delete's order value.
Earlier historyEvery earlier version stays as it was.There is none.
A delete that arrives lateIt marks the older version it falls in. The current version stays live.It loses to the newer stored row and changes nothing.
With the rule removeEvery version of the key is removed, because its current version is deleted.The row is removed.

A live row is one where _is_current is true and _is_deleted is false. A deleted key therefore has a current row and no live row. Here is contract case 7 again. The delete was committed at 00:05.

KeyValue_seq_from_valid_to_is_current_is_deleted_deleted_at
a11openyesyes00:05

A deleted key that comes back

Sources do restore rows. A material is withdrawn and later reinstated. Nothing special is coded for this. It falls out of treating a delete as an event.

SituationWhat silver holds afterwardsHow this is known
Type 2, the key returns with the same tracked valuesThe same version, live again. The returning row has a higher order value than the delete, so it becomes the row that represents the version, and the deleted flag is gone.The third case of the full copy test below
Type 2, the key returns with a different tracked valueA new current version. The old version stays in history, not current, and still flagged deleted.Run for this page on DuckDB. No test covers it.
Type 1, the key returnsThe newer row replaces the deleted one and the flag is gone.Run for this page on DuckDB. No test covers it.

The first row works because of how versions are opened. The delete event has no tracked hash, so the returning row is compared with the values before the delete. They are equal, so no new version opens. The type 2 page explains the hash.

What gold sees

Gold reads silver's live rows, the ones that are current and not deleted, when it wants what exists today. A deleted member is still in silver, so a fact that refers to it can still find it. The dimension build carries _is_deleted into gold so that reports can leave deleted members out of counts. The gold dimensions page has the detail.

How it is tested

tests/dataplatform/test_silver_stages.py:387Full copies: a missing key is marked, no rule leaves it alone, and a key that comes back is live again.
@pytest.mark.parametrize(
    ("case", "deletes", "copies", "want"),
    [
        ("a key missing from a full copy is marked deleted (key compare)", {"detect": "key_compare",
                                                                          "apply": "mark_deleted"},
         [[("a", 1, 1), ("b", 2, 1)], [("a", 1, 2)]], [("a", 1, True, False), ("b", 2, True, True)]),
        ("no delete rule: the key stays", {"detect": "none", "apply": "none"},
         [[("a", 1, 1), ("b", 2, 1)], [("a", 1, 2)]], [("a", 1, True, False), ("b", 2, True, False)]),
        ("a key that comes back is live again", {"detect": "key_compare", "apply": "mark_deleted"},
         [[("a", 1, 1), ("b", 2, 1)], [("a", 1, 2)], [("a", 1, 3), ("b", 2, 3)]],
         [("a", 1, True, False), ("b", 2, True, False)]),
    ],
)
def test_history_full_copy_deletes(spark, case, deletes, copies, want):
    p = plan(kind="master", read="full_copy", columns=[("id", "string"), ("v", "integer"), ("seq", "integer")],
             history={"track_columns": ["v"]}, load={"history_order_by": "seq", "deletes": deletes})
    silver = _apply(spark, p, copies, "id string, v long, seq long")
    assert rows(silver, "id", "v", "_is_current", "_is_deleted") == want, case
tests/dataplatform/test_ingest_steps.py:176The guard as a table: first copy, nothing missing, 40 percent missing, exactly at the limit, not a full copy, no limit.
        ("first copy", "full_copy", None, ["A"], 0.05, False),
        ("nothing missing", "full_copy", {("A",), ("B",)}, ["A", "B", "C"], 0.05, False),
        ("40% missing", "full_copy", {(k,) for k in "ABCDEFGHIJ"}, list("ABCDEF"), 0.05, True),
        ("at the limit", "full_copy", {(k,) for k in "ABCDEFGHIJ"}, list("ABCDEFGHI"), 0.10, False),
        ("not a full copy", "files", {("A",), ("B",)}, [], 0.05, False),
        ("no limit declared", "full_copy", {("A",), ("B",)}, [], None, False),
    ],
)
def test_mass_delete(case, read, previous, now, limit, held):
    reason = mass_delete_problem(spec(read=read, mass=limit), [{"id": k} for k in now], previous)
    assert bool(reason) is held
tests/dataplatform/test_ingest_pipelines.py:305The guard against silver: keys silver already marked deleted do not count as missing.
        ("silver's keys are the comparison (DPC-0054): a short first copy is held", None, "ABCDEFGHIJ", "ABCDEF",
         "failed"),
        ("silver never written: the last accepted copy in bronze is the comparison", "ABCDEFGHIJ", None, "ABCDEF",
         "failed"),
        ("keys silver already marked deleted do not count as missing", "ABCDEFGHIJ", "ABCDEF", "ABCDEF",
         "succeeded"),
        ("a copy as long as silver loads", None, "ABCDEF", "ABCDEFG", "succeeded"),
    ],
)
def test_mass_delete_guard_compares_with_silver(tmp_path, case, first, silver, second, status):
    r = Rig(tmp_path)
    if first:
        r.copy("2026-11-08T19:30:00+00:00", first)
        r.run(W1)
    r.copy("2026-11-09T19:30:00+00:00", second)
    assert r.run(W2, silver=SilverKeys(silver))["erp.mat"].status == status, case

Type 2's delete is case 7 of the contract, and type 1 has its own case, a delete is soft. In the recorded 14-day run of the tyre company at full scale (4 November 2026, local Spark), five order lines were deleted at the source on day 4, and all five were kept in silver and marked deleted. On day 9 a material copy arrived with 720 of 1,800 keys missing, which is 40 percent, and the guard held it. Both results are quoted from docs/W1C-SILVER.md.

What is not handled

Not built yet, or known gaps
  • A hard delete at a source that has no change feed and sends no full copy is never seen. The dealer interface is read page by page since the last cursor, and its manifest says detect none. A dealer removed there stays live in silver for ever.
  • A change feed that drops a delete, or a delete that is filtered out before the feed, is not found by any later comparison. A change feed dataset is never compared with a full copy.
  • Events are never deleted. Append history does not read the operation column.
  • A snapshot has no deletes. A row a newer file leaves out is switched to not current, with no flag and no time.
  • The guard only exists for full copies with a declared limit. It compares with the keys silver holds at that moment, so the very first copy of a dataset has nothing to be compared with.
  • No tyre dataset uses the remove rule. It is tested on a small table (every version of a deleted key goes, other keys stay) but has not been run on the simulated company.
  • The declarative engine cannot detect deletes by comparing full copies, and it stores a delete differently. The declarative engine page explains both.