Data PlatformZoom level 4
Source versioned history: the source already keeps the versions
Some sources keep history themselves. Every row they send is a version, with a valid-from date and a valid-to date. Silver should store those versions as delivered. It should not work out versions of its own.
What silver must do is check them. Two versions of one key that overlap, or that leave a gap, would make a lookup by date return two answers or none. Such a key is set aside whole, with the reason, and its stored history is left as it was.
A worked example
This is the overlap case of the test table. Key a has two versions, and the first ends at 01:30, after the second has started at 01:00. Key b has one clean version.
| Key | Value | Valid from | Valid to |
|---|---|---|---|
| a | 1 | 00:00 | 01:30 |
| a | 2 | 01:00 | open |
| b | 7 | 00:00 | open |
Key b is loaded. Key a is set aside with the reason versions overlap.
| Key | Value | _is_current | _is_deleted |
|---|---|---|---|
| b | 7 | yes | no |
The settings that drive it
src/dataplatform/silver/plan.py:240Where the plan reads the two validity columns and the gap rule.def _validity(name: str, history: str, hist: dict[str, Any], settings: dict[str, Any],
names: set[str]) -> tuple[str, str, bool]:
"""(valid_from column, valid_to column, gaps allowed) for history the source keeps (ADR-032): from the manifest's
history block, else the dataset settings. source_versioned needs both columns, and both must be contract columns."""
valid_from = str(hist.get("valid_from_column") or settings.get("valid_from_column") or "")
valid_to = str(hist.get("valid_to_column") or settings.get("valid_to_column") or "")
if history == "source_versioned" and any(not c or c not in names for c in (valid_from, valid_to)):
raise PlanError(f"{name}: source_versioned needs valid_from_column and valid_to_column that are contract "
"columns (ADR-032)")
gaps = str(hist.get("gaps_allowed", settings.get("gaps_allowed", "false"))).lower() == "true"
return valid_from, valid_to, gaps| Key | What it means | Allowed values | When absent |
|---|---|---|---|
history.type | Must name this history type. No kind maps to it. | source_versioned | The kind's default is used |
history.valid_from_column | The source column holding a version's start | A contract column | The plan refuses the dataset |
history.valid_to_column | The source column holding a version's end | A contract column | The plan refuses the dataset |
history.gaps_allowed | Whether a gap between two versions of a key is accepted | true, false | false |
The two column settings may also be given as dataset settings with the same names.
The code
src/dataplatform/stages/history.py:251An end date in the year 9999 means the version is open.FAR = "9999-01-01" # a validity end at or after this is "open" (sources write 9999-12-31 for "no end")
SV_WORK = ("_vf", "_vt", "_sv_rn", "_prev_vt", "_sv_reason", "_key_reason")src/dataplatform/stages/history.py:255The first half: collect the versions, keep one per start, and find the reason a key fails.def source_versioned(inc: DataFrame, cur: DataFrame | None, plan: SilverPlan) -> tuple[DataFrame, DataFrame | None]:
"""History the source already keeps (ADR-032): every version as delivered, with the source's own validity dates,
checked per key and never rebuilt. A key whose versions overlap, leave a gap (unless gaps are allowed) or end
before they start is quarantined whole, with the reason; its stored history is left as it was. Good keys get the
standard columns: the latest version is current; a source that closed it marks the key deleted."""
def stamp(df: DataFrame) -> DataFrame:
vt = F.col(plan.valid_to).cast("timestamp")
return df.withColumns({"_vf": F.col(plan.valid_from).cast("timestamp"),
"_vt": F.when(vt >= F.lit(FAR).cast("timestamp"), F.lit(None)).otherwise(vt)})
new = stamp(inc).withColumn("_existing", F.lit(False))
rows = None
events = new
if cur is not None:
rows = cur.join(new.select("_key_hash").distinct(), "_key_hash", "left_semi")
old = stamp(rows.drop(*[c for c in RECOMPUTED if c in rows.columns])).withColumn("_existing", F.lit(True))
events = new.unionByName(old, allowMissingColumns=True)
# one version per (key, start): the newest delivery wins over what silver holds
same = Window.partitionBy("_key_hash", "_vf").orderBy(F.col("_existing").asc_nulls_last(), *arrival_desc(events))
events = events.withColumn("_sv_rn", F.row_number().over(same)).where("_sv_rn = 1")
per_key = Window.partitionBy("_key_hash").orderBy(F.col("_vf").asc_nulls_first())
prev_vt = F.lag("_vt").over(per_key)
is_first = F.lag("_vf").over(per_key).isNull()
reason = (
F.when(F.col("_vf").isNull(), F.lit("a version has no start"))
.when(F.col("_vt").isNotNull() & (F.col("_vt") < F.col("_vf")), F.lit("a version ends before it starts"))
.when(~is_first & (prev_vt.isNull() | (prev_vt > F.col("_vf"))), F.lit("versions overlap"))
)
if not plan.gaps_allowed:
reason = reason.when(~is_first & (prev_vt < F.col("_vf")), F.lit("a gap between versions"))
events = events.withColumn("_sv_reason", reason)- Read the dates.
stampcasts the two validity columns to timestamps. An end at or after the year 9999 becomes empty, which means open. - Add the stored versions. The stored rows of the delivered keys are added, with their old history columns dropped.
- One version per key and start. If the same key and start date is present twice, a newly delivered row beats the stored row, and the newest arrival beats older ones.
- Check in date order. Each key's versions are walked by start date. A version with no start fails. A version that ends before it starts fails. A version that starts before the previous one ended fails as an overlap, and so does any version after an open one. When gaps are not allowed, a version that starts after the previous one ended fails as a gap.
src/dataplatform/stages/history.py:287The second half: bad keys to quarantine, good keys get the standard columns. events = events.withColumn("_key_reason", F.max("_sv_reason").over(Window.partitionBy("_key_hash")))
bad_new = new.join(events.where(F.col("_key_reason").isNotNull()).select("_key_hash", "_key_reason").distinct(),
"_key_hash")
record = [c for c in inc.columns if not c.startswith("_")]
_, quarantine = quarantine_rows(bad_new, F.concat(F.lit(f"{plan.table}: "), F.col("_key_reason")), "history",
record)
good = events.where(F.col("_key_reason").isNull())
last = F.lead("_vf").over(per_key).isNull()
closed = last & F.col("_vt").isNotNull()
out = good.withColumns({
"_valid_from": F.col("_vf"),
"_valid_to": F.col("_vt"),
"_is_current": last,
"_is_deleted": closed,
"_deleted_at": F.when(closed, F.col("_vt")),
"_seq_from": F.unix_timestamp(F.col("_vf")).cast("bigint"),
"_row_key": F.sha2(F.concat_ws("|", F.col("_key_hash"), F.col("_vf").cast("string")), 256),
}).drop(*SV_WORK, "_existing")
good_keys = good.select("_key_hash").distinct()
stale = None if rows is None else _stale(rows.join(good_keys, "_key_hash", "left_semi"), out)
return _finish(out, stale, plan), quarantineOne failing version gives its reason to the whole key. The delivered rows of a failing key go to quarantine with the table name and the reason. Only the business columns are recorded.
For the good keys the source's dates become _valid_from and _valid_to. The last version by start date is current. If the source closed that last version, the key is marked deleted and the end date is the time of deletion. The row key is a hash of the key hash and the start date. Stale rows are looked for only among the good keys, so a bad key's stored history is not touched.
How it is tested
tests/dataplatform/test_silver_stages.py:689Eight cases. Each lists whether gaps are allowed, the batches, and silver afterwards.SV_CASES = [
("two clean versions: the later one is current",
False, [[("a", 1, ts(0), ts(60)), ("a", 2, ts(60), None)]],
[("a", 1, False, False), ("a", 2, True, False)]),
("the source closed the last version: no current version, the key is deleted",
False, [[("a", 1, ts(0), ts(60)), ("a", 2, ts(60), ts(120))]],
[("a", 1, False, False), ("a", 2, True, True)]),
("an end at 9999 is open, not closed",
False, [[("a", 1, ts(0), datetime(9999, 12, 31, tzinfo=UTC))]],
[("a", 1, True, False)]),
("overlapping versions: the key is held, the other key loads",
False, [[("a", 1, ts(0), ts(90)), ("a", 2, ts(60), None), ("b", 7, ts(0), None)]],
[("b", 7, True, False)]),
("a gap is held when gaps are not allowed",
False, [[("a", 1, ts(0), ts(30)), ("a", 2, ts(60), None)]], []),
("a gap loads when the dataset allows gaps",
True, [[("a", 1, ts(0), ts(30)), ("a", 2, ts(60), None)]],
[("a", 1, False, False), ("a", 2, True, False)]),
("the same batch twice changes nothing",
False, [[("a", 1, ts(0), ts(60)), ("a", 2, ts(60), None)], [("a", 1, ts(0), ts(60)), ("a", 2, ts(60), None)]],
[("a", 1, False, False), ("a", 2, True, False)]),
("a later delivery closes the current version and adds the next",
False, [[("a", 1, ts(0), None)], [("a", 1, ts(0), ts(60)), ("a", 2, ts(60), None)]],
[("a", 1, False, False), ("a", 2, True, False)]),
]Three more tests check that no key is lost between silver and quarantine, that the quarantine reason names the table and the failure, and that the plan refuses the history type without its two columns.
- None of the nine tyre datasets uses this history type, so it has no recorded run. It is covered by its test cases only.
- The declarative engine refuses it. Source versioned datasets run on the job engine.
- A key that fails stays out of silver until the source sends a clean set of versions. There is no partial load of a key.