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?
Detection: three settings
The setting is load.deletes.detect. Each row below is one of its values.
| Value | How the delete is found | Where in the code | Tyre datasets |
|---|---|---|---|
change_feed | The 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 event | Sales order lines, deliveries |
key_compare | A 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 it | Materials, presses |
none | Nothing 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 outNothing 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")- Three conditions. The function does nothing unless detection is
key_compare, the apply rule is notnone, and the dataset is read as a full copy. - Find the missing keys.
liveis silver's current rows that are not deleted.goneis the live rows whose key is not in the incoming copy. - Read the copy's stamp.
copyis one row holding the copy's highest order value, its latest time, and its batch, file and window. - 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 Nonesrc/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.05A 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")| Value | What happens to the key in silver | Where in the code |
|---|---|---|
mark_deleted | The row stays. _is_deleted is true and _deleted_at holds the delete's time. | scd2 and scd1 set the two columns |
remove | Every row of a key whose current version is deleted gets the action delete, and the merge removes them all. | _finish |
none | The 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 2 | Type 1 | |
|---|---|---|
| Where the delete lands | In 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 afterwards | The 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 history | Every earlier version stays as it was. | There is none. |
| A delete that arrives late | It 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 remove | Every 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.
| Key | Value | _seq_from | _valid_to | _is_current | _is_deleted | _deleted_at |
|---|---|---|---|---|---|---|
| a | 1 | 1 | open | yes | yes | 00: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.
| Situation | What silver holds afterwards | How this is known |
|---|---|---|
| Type 2, the key returns with the same tracked values | The 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 value | A 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 returns | The 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, casetests/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 heldtests/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, caseType 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
- 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
removerule. 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.