Data PlatformZoom level 4
Type 1 history: the latest state of a key wins
Type 1 history keeps one row per business key: its latest state. A change replaces the row. The past is not kept.
It is the default for transactions, such as the tyre company's deliveries. A delivery is updated as it moves, and reports only need where it stands now. The risk is the same as for type 2. If the latest arrival won, a late older change would overwrite newer data. So the latest change by the source's order wins.
A worked example
Delivery D-7 was changed twice at the source. The second change, with sequence 2, reached silver first. The first change, with sequence 1, arrived in a later batch.
| Arrives in | Delivery | Delivered quantity | change_seq |
|---|---|---|---|
| batch 1 | D-7 | 40 | 2 |
| batch 2 | D-7 | 36 | 1 |
Silver keeps the row with sequence 2. The late older change is ignored.
| Delivery | Delivered quantity | _seq_from | _is_current | _is_deleted |
|---|---|---|---|---|
| D-7 | 40 | 2 | yes | no |
The settings that drive it
data-platform-catalog/slices/02-tyre/manifest/datasets/erp.delivery.yaml:16Deliveries name their order column in the duplicate settings.dedup: {mode: business_key_latest, business_keys: [delivery_id], order_by: change_seq}data-platform-catalog/slices/02-tyre/manifest/datasets/erp.delivery.yaml:21The kind is transaction, so the history type is type 1.history: {kind: transaction}
load:
pattern: upsert
batch_key: [delivery_window, content_checksum]
new_columns: hold_in_bronze
deletes: {detect: change_feed, apply: mark_deleted}| Key | What it means | Value for deliveries |
|---|---|---|
history.kind | A transaction gets type 1 unless it names another type | transaction |
dedup.order_by | The source's order. It is used because load.history_order_by is not set. | change_seq |
load.deletes | Deletes arrive in the change feed and are marked | change_feed, mark_deleted |
The code
src/dataplatform/stages/history.py:178The whole of type 1.def scd1(inc: DataFrame, cur: DataFrame | None, plan: SilverPlan) -> DataFrame:
o = plan.order_by
events, rows = _events(_incoming(inc, plan).withColumn("_pos", F.col(o)), cur, plan)
latest = Window.partitionBy("_key_hash").orderBy(F.col(o).desc_nulls_last(), F.col("_existing").asc_nulls_first(),
*arrival_desc(events))
deleted = F.col("_op") == "D"
out = events.withColumn("_rn", F.row_number().over(latest)).where("_rn = 1").withColumns({
"_valid_from": F.col("_ts"),
"_valid_to": F.lit(None).cast("timestamp"),
"_is_current": F.lit(True),
"_is_deleted": deleted,
"_deleted_at": F.when(deleted, F.col("_ts")),
"_seq_from": F.col(o),
"_row_key": F.col("_key_hash"),
})
return _finish(out, _stale(rows, out), plan)The first lines are the same as type 2. The new rows become events, and the stored rows of the touched keys are added. The hub page explains those helpers.
The window latest ranks the events of a key. The highest order value comes first. At a tie a new change comes before the stored row, and after that the newest arrival wins. Rank 1 is kept and the rest are dropped.
The history columns are simple. The row is always current. There is no end of validity. _valid_from is the effective time of the change that won. _seq_from is its order value. The row key is the key hash, so the merge finds the one stored row of the key and overwrites it.
A delete is one more event. If the delete has the highest order value, the row that survives is the delete row, and it is flagged deleted with the delete's time. The row is not removed unless the dataset's rule is remove. If a newer change for the key arrives later, that change wins and the flag is gone.
How it is tested
tests/dataplatform/test_silver_stages.py:410Three cases: the latest wins, a late older change does not overwrite, a delete is soft. ("the latest change wins", [[("a", 1, "x", 1, "I", ts(0), 1)], [("a", 2, "x", 2, "U", ts(1), 2)]],
[("a", 2, 2, True, False)]),
("an older change arriving late does not overwrite", [[("a", 2, "x", 2, "U", ts(1), 1)],
[("a", 1, "x", 1, "I", ts(0), 2)]],
[("a", 2, 2, True, False)]),
("a delete is soft", [[("a", 1, "x", 1, "I", ts(0), 1)], [("a", 1, "x", 2, "D", ts(1), 2)]],
[("a", 1, 2, True, True)]),
],
)
def test_history_type1(spark, case, batches, want):
p = plan(kind="transaction", columns=SCD2["columns"],
dedup={"mode": "business_key_latest", "business_keys": ["id"], "order_by": "seq"},
load={"deletes": {"detect": "change_feed", "apply": "mark_deleted"}})
silver = _apply(spark, p, batches)
assert rows(silver, "id", "v", "_seq_from", "_is_current", "_is_deleted") == want, case
assert_history_invariants(silver)The second case is the worked example above. Its two batches arrive in the wrong order and silver must still hold sequence 2.
- Type 1 keeps no past. If a report later needs the earlier values of a delivery, the dataset has to move to type 2 and be rebuilt from bronze.
- Stage 10 already keeps only the latest change per key inside a batch for this kind of dataset, so the ranking here mostly decides between the batch and the stored row.