Home › Data engineering › Gold harness spec

Data engineering · open specification · version 0.1 (draft for review)

Gold harness spec: facts and SCD2 dimensions from silver

One harness that builds every gold dimension and fact from silver, as robustly as Databricks AUTO CDC builds SCD2 tables, and also does what AUTO CDC does not: surrogate keys, fact lookups, late dimensions, late facts and proof that no record was lost. This page is the specification: requirements, design and low-level design. No code yet; code follows once the spec is agreed.

In one paragraph. Metadata says what each gold table is. A run reads only the silver changes since its watermark, up to a mark fixed at the start. Pure functions decide everything: order, duplicates, SCD2 versions, key lookups (as-of the fact's date and current), inferred members for late dimensions, rejects. Before writing, the run proves its counts balance. It writes with one MERGE tagged with the run id, then moves the watermark. A crash anywhere is fixed by running again.

Lesson first

In plain words

Gold is the layer people report from. It has two kinds of table. Dimensions describe things (customers, products, plants). Facts record what happened (order lines, readings, payments) and point at dimensions by key. Building gold means turning clean silver tables into these, every hour or every day, without losing or doubling anything.

silver tablesclean, conformed,change feed onthe gold harness (one code base)readchanges sincewatermarkplanpurefunctionschecknothinglostwriteoneMERGEmetadata says what to build; the code says how, oncedimensionsSCD Type 1 or Type 2factscurrent view or as-of viewrun log · watermarks · rejects

Silver in, gold out. The harness is the one place where reading, deciding, checking and writing are done right.

Two questions decide most of the design. First, does a dimension keep history (SCD Type 2) or only the current value (Type 1)? Second, when a fact points at a dimension that keeps history, should it show the values true on the fact's date or true today? This harness supports both, side by side.

1 Jan10 Mar1 Sepcustomer 7 · version 1 · Pune · sk 701version 2 · Delhi · sk 702version 3 · Goa · sk 703order A, 20 Feborder B, 12 Mar (late)as-of view: each order keeps the version valid on its own date. Current view: both show Goa through the durable key.

The same two orders, two views. As-of: each order sees the city on its own date. Current: both see today's city.

WordWhat it means here
Fact tableThings that happened, with numbers: an order line, a sensor reading, a payment. Many rows, mostly added, rarely changed.
DimensionThe who, what, where of a fact: customer, product, plant. Few rows, and they change slowly.
SCD Type 1A dimension that overwrites: only the current value is kept.
SCD Type 2A dimension that keeps history: a changed value closes the old row and opens a new one, with valid_from and valid_to.
Surrogate key (sk)A number made by the warehouse to identify a dimension row. Facts store it instead of the source's own key.
Durable keyOne number per real-world member (customer 7) that never changes across SCD2 versions. Lets a fact show today's values.
Version keyOne number per SCD2 row (customer 7 as of March). Lets a fact show the values true on its own date.
As-of lookupFind the dimension version whose valid_from and valid_to surround the fact's event date.
WatermarkA bookmark per source table: the last change already processed, so the next run reads only newer changes.
Inferred memberA placeholder dimension row created when a fact arrives before its dimension; filled in when the real row arrives.
HarnessHere: one code base plus metadata that builds every gold table the same way, so robustness is written once, not per table.
Pure functionA function whose output depends only on its inputs: no reading tables, no clock, no random numbers inside. Easy to test with small tables.

The bar

Why a harness, and the bar it must meet

Hand-writing each gold table repeats the same hard problems (ordering, replays, late data, keys) table after table, and each copy fixes them a little differently. AUTO CDC shows what robust looks like for SCD2. This harness must meet that bar for every gold table, and add the parts AUTO CDC does not cover. The SCD2 writer it builds on is tested on the AUTO CDC or own PySpark page.

RobustnessWhat AUTO CDC doesWhere this spec covers it
OrderingSEQUENCE BY orders changes per keyFR-05: order column per source; ties broken by commit version
DuplicatesSame key and order value applied onceFR-05: dedupe on key plus order value in the plan
Out-of-order eventsOlder events are placed correctly in historyFR-09, FR-10: as-of lookup on event time; backdated dimension changes re-key affected facts
DeletesAPPLY AS DELETE WHENFR-11: delete rule per source (close, soft delete, or reversal for facts)
Replays and retriesCheckpoint makes a batch apply onceFR-03, FR-12: watermark moves only after a balanced, committed write; reruns are no-ops
Incremental readsStreams read only new dataFR-03: change data feed by commit version
History protectionpipelines.reset.allowed = falseFR-14: full rebuild of an SCD2 target needs an explicit, logged approval
What AUTO CDC does not doSurrogate keys, fact lookups, inferred members, reconciliationFR-06 to FR-10, FR-13: the reason this harness exists

1. Requirements

What it must do

Functional requirements

RequirementWhat it means
FR-01 Declare, don't codeEvery gold table is described in metadata: kind, grain, keys, sources, lookups, load mode, trigger, checks. Adding a table needs no new code.
FR-02 DimensionsBuild dimensions as SCD Type 1 or Type 2 from one or more silver sources, with tracked and untracked columns (untracked change in place, no new version).
FR-03 Read only what changedRead each silver source incrementally from its watermark using the change data feed, up to a high mark fixed at run start; full read only where FR-04 says so.
FR-04 Full or incrementalEach table declares its load mode; the harness applies the rules in the load-mode table, and can force a bounded backfill for a date range.
FR-05 Order and dedupeEach source declares an order column; changes for one key are applied in that order, duplicates once, ties broken by commit version.
FR-06 Surrogate keysEvery dimension member gets a durable key and every SCD2 version a version key; both are stable across reruns and rebuilds.
FR-07 Two fact viewsEach fact lookup declares a mode: current (store the durable key) or as-of (store the version key valid on the fact's event date). A fact may store both.
FR-08 Fact change modesEach fact declares how it changes: append-only events, upsert of current state (accumulating snapshot), versioned (SCD2 on the fact grain), or reversal (correction as minus and plus rows).
FR-09 Late-arriving dimensionA fact whose dimension member does not exist yet gets an inferred member and a key at once; the member is filled in place when it arrives; old inferred members raise alerts.
FR-10 Late-arriving facts and backdated changesLate facts use as-of lookups on their own event date. A backdated SCD2 change re-keys only the affected facts (by key and date range).
FR-11 Deletes and correctionsEach source declares a delete rule: close the dimension row, soft-delete the fact, or post a reversal. Hard deletes for privacy follow FR-15.
FR-12 Safe to rerunRunning the same range twice gives the same table. A crash at any step leaves nothing half applied that a rerun cannot finish.
FR-13 Nothing lost silentlyEach run proves rows in equal rows written, unchanged, rejected and filtered; checks run before the watermark moves; a failed balance stops the run.
FR-14 Protect historyA full rebuild of an SCD2 dimension or versioned fact needs an explicit flag, is logged, and keeps key maps so keys do not change.
FR-15 Privacy deletesRemove or mask one person across dimension versions and facts without rebuilding history for everyone.
FR-16 Fact from another factA fact may read another gold fact only under the rules in the design (published, watermarked, no cycles, same or coarser grain).
FR-17 TriggersEach table declares its trigger: table update, file arrival, schedule or continuous, following the trigger table.
FR-18 Run recordEvery run writes one run-log row: source versions read, dimension versions used, counts, checks, duration, cost tags.

Quality requirements

QualityWhat it means
Correct before fastInvariants (one current row per key, no overlaps, balanced counts) are checked every run, not just in tests.
Testable without a clusterAll decisions live in pure functions over small tables; reading and writing are thin adapters. Unit tests run on a laptop in seconds.
DeterministicNo current time, random numbers or monotonically_increasing_id inside the plan; the run passes in its run id and run time.
PortablePorts for reader, writer, key service, watermark store and clock. Delta on Databricks first; Iceberg and plain Spark as other adapters.
Cost boundedA run's work grows with the number of changes, not the size of the table; full reads only where declared.
ObservableRun log, metrics and lineage per run; failures say which check failed with counts.
GovernedRuns as a service principal; Unity Catalog grants; personal data columns tagged in metadata.

Out of scope for version 1

  • Building silver from bronze (that is the job of the silver hop).
  • Semantic models, metric views and BI (the next hop).
  • Sub-second serving (the fast lane on the streaming page).

2. Design

How it is put together

Three layers. Metadata describes each gold table. A pure core takes small tables in and gives tables out, and makes every decision. Adapters do the reading and writing behind fixed interfaces (ports): a source reader (change feed, snapshot, full), a table writer (Delta MERGE first, Iceberg later), a key service (key map), a watermark store, a clock and a check sink. Swapping Delta for Iceberg means a new adapter, not new logic.

lockthe tablefixhigh markread changesplan (pure)checkbalancewrite MERGElog+ move marka crash anywhere before the last step: the rerun reads the same range and the write is a no-op or completes

Every run follows the same seven steps for every table. Only the planning step differs, and it is chosen by metadata.

Tables run in dependency order: dimensions before the facts that look them up, and source facts before facts built from them. The orchestrator (Lakeflow Jobs) reads the order from depends_on.

2.1 When to use a table trigger

TriggerUse it whenVerdict
Table update trigger (Lakeflow Jobs)The table depends on a few silver tables that update at irregular times; you want gold minutes after silver.Pursue for most facts and dimensions
File arrival triggerThe source is files in a volume or S3 path, before silver. Belongs to the silver job, not gold.Pursue, upstream of gold
ScheduleBusiness wants a fixed time (06:00 after close), or many sources must be complete first (period-end facts).Pursue for periodic snapshots and finance
Continuous or streamingSeconds matter and the table is append-only with current-view lookups only.Pursue with care: as-of lookups and inferred members are costly per micro-batch
Trigger every table on every silver changeChatty sources fire runs every minute; cost with little value.Drop: add a minimum interval or debounce in the trigger settings

2.2 Which tables load full and which incremental

Table typeDefaultException
Dimension, SCD1 or SCD2Incremental from the change data feedFull compare only when the source has no change feed (snapshot adapter)
Small reference dimension (under about a million rows, no history needed)Full overwrite is fine and simplestMust still keep its key map so keys do not change
Append-only fact (events, readings, transactions)Incremental by commit versionBounded backfill for a date range when a rule changes
Accumulating snapshot fact (order lifecycle)Incremental upsert by grain keyFull rebuild only with FR-14 approval
Periodic snapshot fact (month-end balances)Full for the period being built, never touching closed periodsClosed periods are frozen and tagged
Aggregate fact built from another factIncremental by affected grain (recompute only changed days or keys)Full when the aggregation rule changes
Source with no change feed and no reliable timestampFull snapshot compare by hashAsk the owner to turn on the change feed: FR-03

The watermark is per gold table and per source, stored as the source's commit version, not a timestamp: versions never tie and never go backwards. The high mark is fixed at run start, so data arriving during the run waits for the next run instead of half joining this one.

2.3 How to create surrogate keys

OptionStrengthWeaknessVerdict
Identity column on the dimensionSimple; numbers are compactA rebuild renumbers everything and breaks facts; parallel writers contendDrop for SCD2 dimensions
Hash of the natural key (64-bit)Same key every time, no lookup table, parallel-safeCollisions become likely at billions of members (birthday problem); SCD2 version needs key plus valid_fromPursue with care: only with a collision check in every run
Key map table per dimension (natural key to durable key, assigned once)Stable across rebuilds; supports inferred members; compact integers for BIOne more table and a lookup joinPursue: durable keys come from here
Version key = hash of durable key and valid_from, or a sequence from the key mapStable for the same version on rerunMust not change when an inferred row is filled in placePursue
Source system's own key as the fact foreign keyNo workBreaks with SCD2, mixed sources and key reuseDrop

Reserved keys: -1 unknown (the fact had no key value at all) and -2 not applicable (the dimension does not apply to this fact). Inferred members get ordinary keys, flagged in the key map.

2.4 Facts with current lookups and facts with as-of (SCD2) lookups

A non-historical fact looks up dimensions by durable key (or a Type 1 dimension's key): reports always show today's values, and a dimension change never touches the fact. A historical fact looks up the SCD2 version valid on the fact's event date and stores its version key: reports show values as they were. Many facts need both, so the spec lets a lookup be current, as-of or both. The as-of lookup is a range join on event date between valid_from and valid_to, run only for the keys in the batch against the pinned dimension version.

The fact itself can also change over time. Four declared modes: append (events never change), upsert (one row per grain holds the latest state, as in an accumulating snapshot of an order's life), versioned (SCD2 on the fact grain, when auditors need every state of the row) and reversal (a correction posts a minus row and a plus row, as finance ledgers require).

2.5 Late-arriving dimensions and late-arriving facts

day 1: order forcustomer 99 arrivesno customer 99 yet:add inferred memberorder gets sk 990now, not laterday 3: customer 99lands in silverfill the inferred rowin place, keep sk 990facts need no reworkinferred for morethan N days?raise an alert

The key is given once and never changes; only the placeholder values are filled in later.

CaseHarness behaviorRejected alternativeVerdict
Fact arrives before its dimension memberInferred member, key now, filled later in placePark the fact in a suspense table and retry (delays numbers, needs retry logic)Pursue inferred member
Fact arrives days lateAs-of lookup on its event date finds the right versionLookup on load date (wrong version)Pursue as-of
Dimension change backdated before facts already loadedRe-key queue: affected keys and date range; rework only those factsRebuild the whole factPursue re-key queue
Dimension member deleted at sourceClose the version; facts keep their keysDelete the row (orphans facts)Pursue close
Fact corrected at sourceBy fact change mode: upsert, new version, or reversal rowsOverwrite silentlyPursue declared mode
Event far older than the open period (after month-end close)Route to a correction period or a review table per finance ruleChange a closed periodPursue routing

2.6 Can a fact refer to another fact?

PatternRuleVerdict
Aggregate a fact to a coarser grain (daily sales from order lines)AllowedPursue: read the published gold fact incrementally by affected grain
Consolidate two facts at the same grain (planned vs actual)Allowed, joined on conformed dimension keys and the grainPursue
Look up an attribute of another fact row by its business key (order header for order lines)Allowed only as a degenerate dimension or through a shared key, never on surrogate row idsPursue with care
Chain facts three or more deepHard to backfill and debugDrop: build from silver or add a conformed dimension
Two facts reading each otherA cycleDrop: the dependency graph must have no cycles
Read a fact that has no published watermark or is mid-runReads half a loadDrop: read only the version recorded as complete in the run log

2.7 Pure functions, so it can be unit tested

Rule: no function that decides anything reads a table, the clock or a random source. The adapters read the data and pass it in; the run passes its id and time in; the functions return rows and counts. Then each hard case is a few rows of input and expected output, tested on a laptop.

FunctionTakesReturnsNote
plan_dimensionchanges, the target rows for the changed keys, key map rows, the table spec, run idrows to write, key-map additions, countsNo reads, no clock: the run passes run id and run time in
plan_factchanges, pinned dimension slices for the keys in the batch, key map rows, the table spec, run idfact rows, inferred members, rejects, countsAs-of and current lookups happen here
plan_rekeyre-key requests, affected fact rows, dimension versionsfact rows with corrected keysSame lookup logic as plan_fact, reused
balancethe counts from a planpass or fail with the numbersThe record-drop proof, as a function
invariantsrows of an SCD2 table for some keysa list of broken rulesAlso used by the tests

Banned inside the core: reading or writing tables, current time functions, random numbers, row ids that depend on partitioning (such as monotonically_increasing_id), and collecting data to the driver.

3. Low-level design

The details

Metadata per gold table

FieldWhat it holdsExample
tableGold table name and ownergold.fact_order_line, owner sales-data
kinddimension or factfact
grainColumns that make one row uniqueorder_id, line_no
scd_type / fact_modeDimension: 1 or 2. Fact: append, upsert, versioned or reversalupsert
sourcesSilver tables, read mode (change feed, snapshot, full), order column, delete rulesilver.order_line, change feed, order by updated_at, soft delete
tracked / untrackedFor SCD2: columns that make a new version, and columns that change in placetracked: city, segment
lookupsPer dimension: natural key columns, mode (current, as-of, both), event date column, on missing (inferred, unknown, park)customer by customer_id, both, order_date, inferred
measuresNumeric columns and how they add up (additive, semi-additive, non-additive)amount additive, stock semi-additive
load_modeincremental, full, periodincremental
depends_onGold tables that must finish first (dimensions, or facts per the rules)dim_customer, dim_product
triggertable update, schedule, file arrival, continuous; minimum intervaltable update, at most every 15 minutes
checksThresholds: unknown key rate, reject rate, count drift, control totalsunknown key under 0.1 percent
layoutClustering keys, retention, privacy tagscluster by order_date, customer_sk

State tables (in the metadata store)

TableColumnsPurpose
watermarktable, source, last version read, run id, timeWhere each source was read up to. Moved only by a successful run.
run_logrun id, table, from and to versions per source, dimension versions used, counts in, inserted, updated, unchanged, rejected, filtered, check results, status, durationOne row per run; the evidence for every number in gold.
key_mapdimension, natural key (as text and hash), durable key, created by run, inferred flag, filled by runDurable keys, assigned once.
rejectsrun id, table, reason, the row as JSONRows that could not be loaded, with why. Counted in the balance.
rekey_queuedimension, durable key, from date, to date, created by run, done by runFacts to re-point after a backdated dimension change.

One run, step by step

  1. Take a lock for the table (one run at a time per table) and create a run id.
  2. For each source, read the watermark and fix the high mark as the source's current version. The run reads the range after the watermark up to the high mark, and nothing newer, so a rerun reads the same range.
  3. Pin the version of each dimension the run will look up, and record it in the run log. A fact run never sees a dimension half written.
  4. Read the changes from each source for that range (change data feed, or the snapshot adapter). This is the only step that reads tables.
  5. Plan with pure functions: dedupe and order changes, drop no-op changes, build SCD2 versions or fact rows, look up keys (as-of and current), create inferred members, apply delete rules, route rejects. Output: rows to write, key-map additions, re-key requests, rejects, counts.
  6. Check before writing: the counts balance (rows in equal inserted plus updated plus unchanged plus rejected plus filtered), the grain is unique, thresholds hold. Any failure stops the run here, and nothing is written.
  7. Write: key-map additions first, then one MERGE into the target, then rejects and re-key requests. Each write carries the run id, so a retried write is detected and skipped.
  8. Check after writing: SCD2 invariants on the touched keys, control totals against silver for the touched dates.
  9. Write the run-log row and move every watermark to its high mark. Release the lock.
  10. If anything fails before step 9, the watermark has not moved. The rerun reads the same range: the planner's order check and the run-id check make already applied rows a no-op.
Why the watermark moves last: Delta cannot commit two tables in one transaction. So the target write is made safe to repeat (the run id is recorded with the write, and the planner skips changes not newer than what is applied), and the watermark moves only after it. The worst case of a crash is a repeat of work, never a gap.

SCD2 dimension rules

  • Changes per key are ordered by the order column, then commit version; duplicates on key and order value collapse to one.
  • A change that alters no tracked column makes no version; untracked columns update the current row in place.
  • Events not newer than the latest applied point for a key, counting closed rows, are skipped and counted as filtered (so a replay after a delete does not bring the member back).
  • A backdated change (valid_from earlier than the current version) splits the right version and adds a re-key request for facts of that key from that date.
  • Filling an inferred member is an in-place update of the placeholder row, never a new version.

Fact rules

  • Grain is unique after the write; a second change to the same grain in one batch keeps the latest by order column (upsert mode) or both (versioned mode).
  • Lookups run against the dimension version pinned at step 3, only for the keys in the batch.
  • Missing member: inferred, unknown or park, as declared; each counted separately in the run log.
  • Deletes follow the declared rule; reversal mode never updates a posted row.

Error handling

  • Row-level problems (bad value, missing mandatory key) go to rejects with a reason and count in the balance.
  • Table-level problems (balance fails, grain broken, threshold crossed) stop the run before any write, and the watermark stays.
  • Schema change in silver: new nullable columns are added to gold automatically if declared; anything else stops the run and asks for a metadata change.

Backfill and rebuild

  • Backfill is a run with an explicit date range and its own run id; it uses the same plan and checks.
  • Rebuilding an SCD2 dimension or versioned fact needs the protect-history flag, keeps the key map, and is compared to the old table before swap.

Performance

  • Cluster dimensions by natural key and is_current, facts by event date and the most used key.
  • Broadcast small dimension slices; use a range-join hint for as-of lookups on large ones.
  • Each run reads only touched keys and dates from the target.

4. Testing

How it is tested, SCD2 first

TestWhat it provesWhen it runs
Unit, table-drivenEvery pure function, one row per case: new key, no-op, change, two changes in one batch, delete, delete then re-insert, late event, replay, replay after deleteEvery commit, on a laptop
SCD2 invariantsExactly one current row per key, no overlapping or gapped ranges, valid_from before valid_to, keys stable across rerunsEvery unit test and every production run
IdempotenceRun the same input twice; the second run changes nothingEvery commit
Order independenceSplit the same changes into batches in different ways; the result is the sameEvery commit (randomized batch splits with a fixed seed)
Late dataFact before dimension, late fact, backdated dimension change, event after period closeEvery commit
Contract tests per portEach reader and writer adapter honors the same behavior (Delta, Iceberg)On adapter change
IntegrationA small Delta lakehouse: silver to gold end to end, including a crash between write and watermarkNightly
SimulationA simulated business with planted incidents (duplicates, late files, deletes), checked against the known truthBefore release; see DataTwin

The SCD2 cases already have a working, table-driven example with 11 rows on the AUTO CDC page; the harness keeps that shape for every pure function. See also Shift left for testing without a cluster.

5. Record-drop and other checks

Proving nothing was lost

CheckRuleOn failure
Count balanceRows in equal inserted plus updated plus unchanged plus rejected plus filtered (duplicates, late, no-op), per source, per runFail the run
Grain uniquenessNo two rows share the grain key after the writeFail the run
Control totalsSum of each additive measure per event date, silver against gold, for the touched datesFail above tolerance
Unknown and inferred keysShare of facts pointing at unknown or inferred members; inferred members older than N daysWarn, then fail above threshold
OrphansFact keys with no dimension row at allFail the run
Silver to gold coverageEvery silver key changed in the range appears in gold or in rejectsFail the run
Count driftRows per day compared with the same weekday over recent weeksWarn
FreshnessTime since the last successful run against the table's agreed delayAlert
Reject rateRejected rows as a share of rows readWarn, then fail above threshold
SCD2 invariants after writeOn the keys this run touchedFail the run

6. Decisions

Decisions with verdicts

DecisionChoiceVerdict
Where the robustness livesOne harness with metadata, not a notebook per tablePursue
Dimensions with CDC input on DatabricksAUTO CDC for the SCD2 history, the harness for keys and checks around itPursue with care: two systems; pick one per platform
Surrogate keysKey map for durable keys, stable version keysPursue
Facts storeBoth the version key (as-of) and the durable key (current) where reports need bothPursue
Late dimensionInferred member filled in placePursue
Watermark storeRun-log and watermark tables in the metadata store, moved only after checks passPursue
Trigger defaultTable update trigger with a minimum intervalPursue
Streaming gold facts with as-of lookupsMicro-batches with pinned dimension versionsPursue with care
Fact-to-fact chains three deepNot allowedDrop

7. Open questions

To agree before code

QuestionOptions and suggestion
Q1 Metadata homeYAML in Git, or tables in the metadata store (Lakebase or Postgres)? Git gives review; tables give runtime edits. Suggestion: Git is the source, deployed to tables.
Q2 One engine for SCD2Use AUTO CDC for dimension history and the harness only for keys and facts, or the harness for both? Depends on whether the team runs Lakeflow pipelines.
Q3 Inferred-member ageHow many days before an inferred member fails the run rather than warns?
Q4 Closed periodsFinance rule for events after close: post to the next open period, or a correction period?
Q5 Privacy deletesMask in place or delete rows? Affects version keys and control totals.
Q6 IcebergIs an Iceberg writer needed in year one, or only Delta?

FAQ

Questions people ask

How do I build fact tables from silver robustly, like AUTO CDC?

Read only changes since a watermark fixed per run, plan in pure functions that order, dedupe and look up keys, prove the counts balance, write with one MERGE carrying the run id, and move the watermark only after the checks pass. A rerun then reads the same range and changes nothing twice.

Should a fact table store the SCD2 version key or the durable key?

Store the version key when reports need the dimension values true on the fact's date, the durable key when they need today's values, and both when both are asked for. The durable key comes from a key map and never changes.

How do I handle a fact that arrives before its dimension?

Create an inferred member in the dimension with a real surrogate key, load the fact with it, and fill the member in place when the dimension row arrives, keeping the same key. Alert on members still inferred after a set number of days.

Can a fact table read from another fact table?

Yes, for aggregates to a coarser grain and consolidations at the same grain, if the source fact is published with a watermark and the dependency graph has no cycles. Avoid deep chains and joins on surrogate row ids.

When should a gold table use a table update trigger?

When it depends on a few silver tables that change at irregular times and should follow them within minutes. Use a schedule for period-end facts and add a minimum interval for chatty sources.

How do I prove no records were dropped?

Every run checks that rows read equal rows inserted, updated, unchanged, rejected and filtered, that the grain is unique, and that control totals match silver for the touched dates, before the watermark moves.

Related

Where it connects

SCD2: AUTO CDC or own PySpark · Hop 3: silver to gold · The metadata store · Time travel as SCD Type 2? · Slowly changing dimensions · DataTwin · All data engineering