cleandataengineering.aiData Platform, from the big picture to the code
You are here: Start here › Bronze to silver › Declarative engine

Data PlatformZoom level 2

The declarative engine: the same stages, history by AUTO CDC

A declarative pipeline is a way of running data work where you describe the tables you want and the product works out how to build and update them. The declarative engine runs our stage functions inside such a pipeline. For history it does not use our history stage. It uses the pipeline's own change capture, called AUTO CDC and also known as apply changes.

It exists to answer a fair question: why write history code at all when the product has a managed feature for it? The answer here is to support both, from the same settings, and to hold both to the same table of cases.

Open: BronzeBronzeread as a streamzoom in ›Open: Eleven stagesEleven stagesthe same functionszoom in ›Valid rowsa temporary viewSet-aside rowsan append flowAUTO CDCkeeps the historySilverOpen: Quarantine tableQuarantine tablezoom in ›Stage 10 (duplicates) and stage 12 (history) are left out of the flow
The highlighted box is our own code, the same functions the job engine calls. The grey box is the product's change capture.
Goes inThe dataset's plan from the metadata store, and its bronze table read as a stream.
What happensEleven of the thirteen stages run on the stream. Rows that pass go to a view. Rows set aside go to the quarantine table. The change capture reads the view and keeps the silver table.
Comes outA streaming silver table per dataset, and a quarantine table when a stage can set rows aside.

Why two stages cannot run in the flow

A declarative flow reads a stream. A stream hands over new rows and never the whole table. Two stages need the whole table. Stage 10 ranks rows of the same key against each other and looks into silver for repeats. Stage 12 lays the new changes beside silver's stored versions. So the recipe switches those two off and hands their work to the pipeline.

src/dataplatform/silver/declarative.py:17The two stages left out, and the name of the operation column.
OUTSIDE_THE_FLOW = frozenset({"duplicates", "history"})
OP_COLUMN = "_op"  # the change-feed operation the lander keeps (silver/plan.py TRANSPORT_COLUMNS)

The recipe: the plan translated for the pipeline

The recipe is a small object built from the plan. It holds no product code. It says which stages run in the flow and what to ask the change capture for.

src/dataplatform/silver/declarative.py:21What the change capture is asked to do, in product-free terms.
@dataclass(frozen=True)
class ChangeCapture:
    keys: tuple[str, ...]
    sequence_by: str
    scd_type: int  # 1 or 2
    delete_when: str | None  # a SQL condition on the change row, None when deletes are not applied
    track: tuple[str, ...]  # columns that open a new version (SCD2); empty = every column
    exclude: tuple[str, ...]  # change-feed columns not stored in silver
src/dataplatform/silver/declarative.py:31The recipe: the plan for the flow, and either a change capture or keys for removing repeats.
@dataclass(frozen=True)
class Recipe:
    flow_plan: SilverPlan  # the plan the flow runs: every stage except those outside the flow
    capture: ChangeCapture | None  # history datasets
    dedupe_keys: tuple[str, ...]  # append datasets: keys for streaming de-duplication; empty = none
    dedupe_within: tuple[str, int] | None = None  # (event-time column, days): bounded de-duplication state
    notes: tuple[str, ...] = ()  # what the engine cannot bound or keep, for the run log
src/dataplatform/silver/declarative.py:40How a plan becomes a recipe, or is refused.
def recipe(plan: SilverPlan) -> Recipe:
    flow = replace(plan, disabled=plan.disabled | OUTSIDE_THE_FLOW)
    if plan.history in ("scd1", "scd2"):
        if not plan.keys:
            raise PlanError(f"{plan.table}: the declarative engine needs business keys for {plan.history}")
        if not plan.order_by:
            raise PlanError(f"{plan.table}: the declarative engine needs a change order (load.history_order_by) "
                            f"for {plan.history}; arrival order is not a sequence")
        if plan.delete_detect == "key_compare":
            raise PlanError(f"{plan.table}: deletes found by comparing full copies are not supported by the "
                            "declarative engine; use silver_engine 'job'")
        deletes = plan.delete_detect == "change_feed" and plan.delete_apply in ("mark_deleted", "remove")
        capture = ChangeCapture(
            keys=plan.keys,
            sequence_by=plan.order_by,
            scd_type=2 if plan.history == "scd2" else 1,
            delete_when=f"{OP_COLUMN} = 'D'" if deletes else None,
            track=plan.track,
            exclude=(OP_COLUMN,) if plan.ingest_read == "change_feed" else (),  # only a change feed carries it
        )
        return Recipe(flow, capture, ())
    if plan.history == "append":
        keys = (plan.dedup_keys or plan.keys) if plan.dedup_mode != "none" else ()
        if not keys:
            return Recipe(flow, None, ())
        if plan.event_time and plan.lookback_days is not None:
            return Recipe(flow, None, tuple(keys), (plan.event_time, plan.lookback_days))
        return Recipe(flow, None, tuple(keys), None, (f"{plan.table}: no event time and late lookback declared, so "
                                                      "streaming de-duplication keeps every key it has seen",))
    raise PlanError(f"{plan.table}: history {plan.history!r} is not supported by the declarative engine "
                    "(ADR-035); use silver_engine 'job'")
  1. The flow plan. A copy of the plan with stages 10 and 12 added to its switched-off stages. Anything the dataset already switched off stays off.
  2. Type 1 and type 2. The plan must have business keys and an order column, and must not detect deletes by comparing full copies. Then a ChangeCapture is filled in.
  3. Append. No change capture. If the dataset removes duplicates, the keys for that are passed on. When the plan has an event time and a late lookback, they bound how long keys are remembered. Otherwise a note says the keys are remembered for ever.
  4. Anything else. Snapshot and source versioned history are refused with a message that names the job engine.

From our plan to the change capture call

Each row is one thing the job engine's history stage decides. The columns show where it sits in the plan, what the recipe turns it into, and the argument of the pipeline call that receives it.

WhatIn the planIn the recipeIn the call
The business keykeyskeyskeys
The source's order of changesorder_bysequence_bysequence_by
Type 1 or type 2history is scd1 or scd2scd_type 1 or 2stored_as_scd_type
Tracked columnstracktracktrack_history_column_list, or nothing when the list is empty
Deletesdelete_detect is change_feed and delete_apply is mark_deleted or removedelete_when is the condition _op = 'D'apply_as_deletes
Columns not storedingest_read is change_feedexclude holds _opexcept_column_list
realms/databricks/declarative/silver.py:38The generic pipeline definition for one dataset. It is the only place the product's functions are called.
def declare(name: str, bronze: str, r) -> None:
    table = name.replace(".", "__")

    def staged():
        return run_stages(r.flow_plan, spark.readStream.table(bronze), None, RUN_ID, AS_OF)  # noqa: F821

    dp.temporary_view(name=f"{table}__valid")(lambda: staged()[0])
    _, quarantine, _ = staged()
    if quarantine is not None:
        dp.create_streaming_table(f"{table}__quarantine")
        dp.append_flow(target=f"{table}__quarantine", name=f"{table}__quarantine_flow")(lambda: staged()[1])
    if r.capture is not None:
        c = r.capture
        dp.create_streaming_table(table)
        dp.create_auto_cdc_flow(
            target=table, source=f"{table}__valid", keys=list(c.keys), sequence_by=F.col(c.sequence_by),
            apply_as_deletes=F.expr(c.delete_when) if c.delete_when else None,
            except_column_list=list(c.exclude), stored_as_scd_type=c.scd_type,
            track_history_column_list=list(c.track) or None,
        )
    else:
        def appended():
            rows = spark.readStream.table(f"{table}__valid")  # noqa: F821
            if not r.dedupe_keys:
                return rows
            if r.dedupe_within:  # keys kept only as long as late rows may still come (the dataset's lookback)
                col, days = r.dedupe_within
                return rows.withWatermark(col, f"{days} days").dropDuplicatesWithinWatermark(list(r.dedupe_keys))
            return rows.dropDuplicates(list(r.dedupe_keys))  # unbounded; the recipe's note says so

        dp.table(name=table)(appended)

Walk through it. staged calls the same run_stages the job engine calls, with the flow plan, the bronze table as a stream, and no silver table. The rows that pass become a temporary view. If any stage can set rows aside, a quarantine table is created and an append flow feeds it.

For a type 1 or type 2 dataset the silver table is created as a streaming table and create_auto_cdc_flow is called with the six values from the table above. For an append dataset the valid rows are written as they come. With bounded keys, repeats are dropped within a watermark, which is the pipeline's limit on how late a row may still arrive. With unbounded keys every key ever seen is remembered.

realms/databricks/declarative/silver.py:73One loop over the metadata store declares every dataset. There is no file per table.
for ref in store.datasets(DOMAIN):
    name = f"{ref.source}.{ref.dataset}"
    if not ref.is_enabled or ref.kind in GOLD_KINDS:
        continue
    view = store.dataset_view(ref.source, ref.dataset)
    if (name not in ONLY) if ONLY else engine_of(view.get("settings", {}), platform) != "declarative":
        continue
    plan = plan_from_view(view, TOKEN_PATTERN)
    contract, untranslated = contract_rules(store.dq_rules(ref.source, ref.dataset))  # DQ-01, same as the job engine
    r = recipe(replace(plan, rules=plan.rules + contract))
    for note in untranslated:
        print(f"declarative silver: {note}")
    for note in r.notes:
        print(f"declarative silver: {note}")
    declare(name, f"{BRONZE}.{ref.source}.{ref.dataset}", r)

The loop reads the datasets of the domain from the metadata store. A dataset is declared when its silver_engine setting says declarative, or when it is named in the pipeline's only setting for a proof run. The plan is built the same way as for the job engine, the contract's row rules are added, and the recipe is made. The recipe's notes are printed.

How changes that arrive out of order are handled

The job engine sorts the events itself. The declarative engine passes the order column as sequence_by and the change capture does the ordering. A change with a lower sequence than one already applied is placed in history and does not become current. Arrival order is not a sequence, so the recipe refuses a type 1 or type 2 dataset that has no order column.

The spike that proved this feeds the pipeline a promised date moved twice, with the older change after the newer one.

spikes/sdp_silver/make_changes.py:10The spike's two batches. In batch 2 the change with sequence 30 comes before the change with sequence 20.
BATCHES = {
    1: [  # first load; dealer codes with stray spaces and a tab, which clean_text must strip
        {"order_id": "O1", "line_no": 1, "dealer_code": " D-0042 ", "promised_date": "2026-11-10", "qty": 4, "op": "I", "change_seq": 10},
        {"order_id": "O1", "line_no": 2, "dealer_code": "D-0042\t", "promised_date": "2026-11-10", "qty": 2, "op": "I", "change_seq": 11},
        {"order_id": "O2", "line_no": 1, "dealer_code": "D-0007", "promised_date": "2026-11-12", "qty": 8, "op": "I", "change_seq": 12},
    ],
    2: [  # changes: a promised date moved twice (the older change arrives after the newer), a qty change, a delete
        {"order_id": "O1", "line_no": 1, "dealer_code": "D-0042", "promised_date": "2026-11-12", "qty": 4, "op": "U", "change_seq": 30},
        {"order_id": "O1", "line_no": 1, "dealer_code": "D-0042", "promised_date": "2026-11-11", "qty": 4, "op": "U", "change_seq": 20},
        {"order_id": "O2", "line_no": 1, "dealer_code": "D-0007", "promised_date": "2026-11-12", "qty": 6, "op": "U", "change_seq": 31},
        {"order_id": "O1", "line_no": 2, "dealer_code": "D-0042", "promised_date": "2026-11-10", "qty": 2, "op": "D", "change_seq": 32},
    ],
}
spikes/sdp_silver/show.py:25The five checks the spike makes on the table the pipeline built.
checks = {
    "dealer codes cleaned by our stage function": all(r.dealer_code in ("D-0042", "D-0007") for r in rows),
    "O1/1: three versions in sequence order 10, 20, 30": [v[2] for v in versions("O1", 1)] == [10, 20, 30],
    "O1/1: current version holds the newest change (12 Nov), not the late older one": [v[0] for v in versions("O1", 1) if v[3] is None] == ["2026-11-12"],
    "O1/2: deleted, so no open version": all(v[3] is not None for v in versions("O1", 2)) and len(versions("O1", 2)) == 1,
    "O2/1: qty change opened a second version": [v[1] for v in versions("O2", 1)] == [8, 6],
}

The spike's notes record that all five checks passed on the managed pipeline service on 13 and 14 November 2026. They also record that the open-source pipeline could not run the same flow on that date, because no open table format supported the one Spark version that had type 2 change capture. The notes are in spikes/sdp_silver/README.md.

How deletes are handled

Only one kind of delete can be expressed: a row in a change feed with operation D. The recipe turns it into the condition _op = 'D', and the change capture treats a matching row as a delete of that key at that sequence. The _op column is then left out of the stored table.

Deletes found by comparing full copies cannot be expressed. Comparing needs the whole silver table, and the flow never has it. Such a dataset is refused.

Both apply rules, mark_deleted and remove, become the same condition. The declarative engine has no way to tell them apart.

What differs in the stored table

The two engines agree on what the history means. They do not store it the same way. Each row below is one difference.

Job engineDeclarative engine
A version's span_seq_from holds the start in the source order. _valid_from and _valid_to hold times.__START_AT and __END_AT, written by the change capture. In the spike they hold sequence values, 10, 20 and 30.
The current version_is_current is true__END_AT is empty
A deleted key, type 2The last version stays current and is flagged: _is_deleted true, _deleted_at set.The last version is closed: __END_AT is set and no version follows.
Row identity_row_keyThe keys and __START_AT
Columns our history stage addsPresentAbsent, because stage 12 does not run
Columns the other stages addPresentPresent. The key hash, the quality flags and the provenance columns come from stages that run in the flow.

Because of the third row, the two outputs cannot be compared column by column. The shared contract compares meaning instead. A helper reads the declarative table and says: the last version of a key is current, and it is deleted when it has an end.

src/dataplatform/silver/history_contract.py:53Turning a table that closes deleted keys into the contract's five values per version.
def normalise_closed(rows: Iterable[Mapping[str, Any]], key: str, value: str, start: str, end: str) -> list[Version]:
    """An engine that closes a deleted key's last version (end set, nothing after it): the last version of a key is
    'current'; it is 'deleted' when it is closed."""
    by_key: dict[Any, list[Mapping[str, Any]]] = {}
    for r in rows:
        by_key.setdefault(r[key], []).append(r)
    out: list[Version] = []
    for k in sorted(by_key):
        vs = sorted(by_key[k], key=lambda r: int(r[start]))
        for i, r in enumerate(vs):
            last = i == len(vs) - 1
            out.append((str(k), int(r[value]), int(r[start]), last, last and r[end] is not None))
    return out

How it is tested

tests/dataplatform/test_silver_declarative.py tests the recipe, which needs no pipeline. Its tables cover the change capture for five kinds of history dataset, the keys for append datasets, the bound on remembered keys, the two stages left out, and each refusal with its reason.

tests/dataplatform/test_silver_declarative.py:20Five history cases. Each names the plan and the exact change capture it must produce.
        ("SCD2 from a change feed with deletes", {},
         ChangeCapture(("order_id", "line_no"), "change_seq", 2, "_op = 'D'", (), ("_op",))),
        ("SCD1 transaction", {"history": "scd1", "kind": "transaction"},
         ChangeCapture(("order_id", "line_no"), "change_seq", 1, "_op = 'D'", (), ("_op",))),
        ("tracked columns carried", {"track": ("promised_date",)},
         ChangeCapture(("order_id", "line_no"), "change_seq", 2, "_op = 'D'", ("promised_date",), ("_op",))),
        ("no delete rule: deletes not applied", {"delete_detect": "none", "delete_apply": "none"},
         ChangeCapture(("order_id", "line_no"), "change_seq", 2, None, (), ("_op",))),
        ("an API source has no change-feed op column to exclude",
         {"ingest_read": "api", "delete_detect": "none", "delete_apply": "none"},
         ChangeCapture(("order_id", "line_no"), "change_seq", 2, None, (), ())),
    ],
)
def test_history_datasets_get_a_change_capture(case, kw, want):
    r = recipe(plan(**kw))
    assert r.capture == want
    assert r.dedupe_keys == ()
tests/dataplatform/test_silver_declarative.py:82Five refusals, each matched against the words of its message.
        ("snapshot history", {"history": "snapshot", "kind": "snapshot"}, "history 'snapshot'"),
        ("source-versioned history", {"history": "source_versioned"}, "history 'source_versioned'"),
        ("no change order", {"order_by": ""}, "needs a change order"),
        ("no business keys", {"keys": ()}, "needs business keys"),
        ("deletes by comparing full copies", {"delete_detect": "key_compare"}, "comparing full copies"),
    ],
)
def test_refused_with_the_reason(case, kw, says):
    with pytest.raises(PlanError, match=says):
        recipe(plan(**kw))

The pipeline itself can only be tested where the product runs. The spike holds a pipeline that declares one table per contract case, and a runner that feeds the batches round by round and compares the result with the same eight cases the job engine passes.

spikes/sdp_silver/lakeflow/transformations/contract.py:20One streaming table per contract case, with the value tracked and the note not.
def declare(s: str) -> None:
    @dp.temporary_view(name=f"changes_{s}")
    def changes():
        return spark.readStream.schema(SCHEMA).json(f"{BASE}/{s}")  # noqa: F821

    dp.create_streaming_table(f"c_{s}")
    dp.create_auto_cdc_flow(
        target=f"c_{s}",
        source=f"changes_{s}",
        keys=["k"],
        sequence_by=F.col("seq"),
        apply_as_deletes=F.expr("op = 'D'"),
        except_column_list=["op", "minute", "pos"],
        stored_as_scd_type=2,
        track_history_column_list=["v"],
    )
Not built yet, or known gaps
  • Three of the nine tyre datasets cannot run here: materials and presses, which find deletes by comparing full copies, and dealer sell-out, which is a snapshot.
  • A critical quality rule cannot hold the batch. The broken rows go to the quarantine table and the rest of the batch is written.
  • The pipeline definition writes nothing to our run log. It prints the recipe's notes. The pipeline's own event log is the record.
  • The stored table lacks _is_current, _is_deleted, _valid_from and _row_key. Nothing in the repository adds them back, so a reader written for the job engine's columns cannot read a declarative table as it is.
  • When a type 2 dataset lists no tracked columns, the call passes no list and the change capture tracks every column of the view. The job engine tracks only the kept contract columns. All four type 2 tyre datasets list their tracked columns, so the difference does not arise there. It was not tested.
  • Of the three append tyre datasets, only press readings has both an event time and a late lookback. Lab inspection results and production records get unbounded removal of repeats, with the recipe's note. This is read from the manifests and the code and was not run.
  • What the change capture does with a delete on a type 1 table is not shown anywhere in the repository. The spike and the contract cover type 2 only.
  • No recorded output of the eight contract cases on the pipeline is in the repository. The runner is there. Its result could not be checked for this page.