Home › Learn › Data platform architecture › On AWS + Databricks › The ETL, end to end

Data platform architecture · AWS + Databricks · runtime design

The ETL, end to end

How the core actually runs: which sources come in and how, the parts that move and shape the data, what starts each one, how one load moves from a source system to a metric, how we know a number is complete, the ids and log rows that tie it together, what happens when something breaks, and how we watch it. The concept pages say what each layer does; this page says how it runs.

↑ The platform on AWS + DatabricksIngestionBronze to silverGoldMetadata store

Start here

Seven rules the whole runtime follows

Everything below follows from these. If a design choice ever conflicts with one of them, the rule wins.

1
Push lands in S3; pull goes straight to bronze through the same steps. AWS services and source-side extractors bring files to S3 landing, and our one lander takes it from there. Our puller reads the source directly and runs the same lander steps in-process (contract check, personal-data tokenising, mass-delete guard, file and batch log), so pull needs no landing. Every batch source passes the same checks, and a source outage never stops the other sources. Sensor streams are the one stated exception: Kinesis is their landing and the streaming table their lander, because readings carry no personal data and Kinesis already keeps them in order for 7 days, ready to be read again.
2
Everything is a run, and every run leaves the same rows. A file load, a pipeline update and a 15-minute slice of a stream all get a run id and the same log rows, so one dashboard and one set of alerts cover all of them.
3
Every step can run twice. Re-running a step after a crash gives the same tables as running it once. That is what makes retries, re-runs and backfills safe without a person checking.
4
A number is complete only when every input has caught up. Each input carries its own bookmark: how far in source time it has been loaded. A gold hour is complete only when every input's bookmark is past the end of that hour. Otherwise it is published as incomplete, never as right.
5
One clock per source; Lakeflow runs the work. Each source has exactly one thing that says 'new data is ready': a schedule, or for AppFlow and partner uploads the AWS event that the transfer finished. Everything that processes batch data runs in Lakeflow Jobs; the streaming pipelines run on a schedule too, every 15 minutes, taking what has arrived and stopping, and nothing runs continuously. Two clocks on one path is how runs overlap, wait or never start.
6
Jobs are generated, never hand-built. Jobs, pipelines and mover settings are generated from the metadata and deployed with Asset Bundles and Terraform. Adding a dataset changes YAML, not a job.
7
Log before you work; nothing waits forever. A run that cannot write its start row does not start. Every call to the log database and every job has a timeout, so a slow database fails loudly instead of freezing quietly.

Sources and ingestion tech

What we load, and what brings each one in

Nine kinds of source cover a tyre maker. Five technologies bring them in: four AWS services (DMS, AppFlow, Transfer Family, Kinesis with SiteWise) and our own puller, built from the framework's existing connectors. The four AWS services deliver files to one landing area under one contract; our puller reads sources directly into bronze, running the same lander steps, so every batch source passes the same checks.

Kind of sourceExamples hereBrought in byWhat changes arriveLands asHow often
Databases that change all dayMES (SQL Server), quality lab LIMS (Oracle), warranty system, plant maintenanceAWS DMS: full load once, then change data capture (CDC)Every insert, update and delete, with an operation flag, commit time and change sequenceParquet files, written continuouslyLoaded every 15 minutes
Small or reference database tablesPress master, shift calendars, code lists; any database where CDC logging is not allowedOur puller: the framework's built connectors (SQL Server, Oracle, PostgreSQL, MySQL) as a Databricks job over JDBCA full copy (needed for any table that can delete rows), or rows above a watermark for append-only tablesParquet plus a manifestOn a schedule, often daily
SAP S/4HANASales orders, deliveries, stock, procurement, financeAmazon AppFlow, SAP OData connector, reading released CDS views and extractors with deltaOnly what changed since the last call, with SAP's change mode (new, changed, deleted) where the extractor gives itParquet plus a manifest written when the flow finishesEvery 30 minutes in plant hours; a nightly full copy for objects whose delta has no deletes
SaaS applications with an AppFlow connectorCRM (for example Salesforce), IT service deskAmazon AppFlowRows changed since the last runParquet plus a manifest written when the flow finishesHourly
APIs without an AppFlow connectorDealer management system API, natural-rubber price feed, weatherOur puller: the framework's built REST connectorPages since the last cursor or dateJSON lines plus a manifestOn a schedule
Files from partnersDealer sell-out, supplier certificates of analysis, logistics partnersAWS Transfer Family (SFTP), one login and folder per partnerNew files; a correction is a new fileAs received (CSV, Excel, XML), renamed on arrival to a unique keyOn arrival
Machines and sensorsCuring presses, mixers, live historian tagsAWS IoT SiteWise Edge or IoT Core at the plant, into Amazon Kinesis Data Streams (7 days kept)Events, one per readingNo files: the Kinesis stream is the landingEvery 15 minutes, trigger-once (readings are sent all the time and wait in Kinesis)
Historian historyYears of past press and mixer data, onceThe historian's own export to S3 through Transfer Family (check current status for the historian's tools)One-off backfillParquet or CSVOnce, through the bulk job
Spreadsheets people keepIdeal cycle times not in MES, targets, mappingsYAML in Git first; otherwise an upload to a Unity Catalog volume by the named ownerA new version of the file; a correction says from which date it appliesAs uploadedOn upload

How to pick, in order

For a new source, ask these questions in order and stop at the first yes.

1
Is it machine data? Kinesis, through SiteWise or IoT Core.
2
Is it SAP? AppFlow with the SAP OData connector. If SAP Datasphere or Business Data Cloud is licensed, the SAP team may choose those instead; the landing contract stays the same.
3
Is it a database with many changes a day, or can rows be deleted? AWS DMS with change capture, or a full copy by our puller if the table is small. A watermark pull never sees deletes.
4
Is it a small, append-only table or a reference list? Our puller, full copy or by watermark.
5
Is it a SaaS application? AppFlow if it has a connector; otherwise our puller's REST connector.
6
Is it files from someone outside? Transfer Family SFTP, one folder and one login per partner, and a written rule for what a re-sent file replaces.
7
None of the above? Ask how it can become a file in landing. If it cannot, it is a design discussion, not a quick add.

The landing contract: what every mover must deliver

1
One place. landing/<source>/<dataset>/<date>/, in the landing bucket of the environment. Personal-data sources use the personal-data landing bucket.
2
Unique names, never overwritten. Every file in landing has a name nobody else uses. Partners upload into their own staging folder; a Transfer Family workflow (a custom Lambda step) copies each upload to landing under a unique key (partner, file, arrival time) and removes it from staging. A re-sent or corrected file is therefore a new file; what it replaces is a rule in silver, written in the dataset's contract.
3
Complete or invisible. A file appears only when complete (S3 writes are all-or-nothing; uploads are copied to landing only after they finish).
4
Say how many. Our puller records the row count it read in the file log, as a manifest would. For AppFlow, an EventBridge rule on the flow-run event calls a small Lambda that writes the manifest and starts the lander through the Jobs API. Partners send a control file where agreed. DMS cannot, so DMS loads are reconciled daily per table against DMS's own table statistics.
5
Say what was deleted, and in what order. DMS adds its operation flag, commit time and change sequence to every row (full-load rows too). SAP rows carry the extractor's change mode where it exists. Full copies are compared with the previous copy in silver.
6
Keep the source's history long enough. Each database keeps its change log at least 72 hours, so DMS can catch up after an outage without a full reload.
7
No transforming. Movers change the format at most (to Parquet). Every rule about content lives in the lander and after.

How the lander reads each kind of landing

LandingLander reads it withRemembers what it read in
Files (DMS, AppFlow, SFTP, uploads)The lander lists only the date folders that can hold new files, takes the files that are new or checked (never loaded, rejected or held ones), checks the new ones (readable, header, row count) and reads exactly that list with a plain batch read. A batch keeps its number until it is loaded, so a re-run reads the same batch with the same transaction id and it cannot land twice. A dataset takes at most a set number of files or gigabytes per run, so a large first load spreads over several runs and never holds back the other datasets. Nothing lists the folder on its own, so nothing unchecked can slip in between the check and the readThe file log. Each batch is appended with a transaction id (the dataset and the batch number from the file log), so a batch committed before a crash is never appended twice
DMS change filesAs files, read in commit order, keeping operation flag, commit time and change sequence as columns; silver applies them with AUTO CDCAs above; a bad file blocks the dataset (see failures)
Kinesis streamsA streaming table reading Kinesis directly, run trigger-once every 15 minutes: each run takes what has arrived since its checkpoint and stopsStream checkpoint and Kinesis sequence numbers

Not in the first version, and why

Not in v1Why
Lakeflow ConnectIts managed connectors write straight into tables, skipping landing, the lander's tokenising and our run recorder: a second way in. Revisit per source for SaaS without personal data.
Reading SAP tables directlyAgainst SAP's licence terms for most customers, and fragile across upgrades. AWS DMS does not read SAP HANA as a source anyway (check current status).
Glue jobs as moversGlue is a transformation engine; using it to copy adds a second engine to run and pay for.
Fivetran, Airbyte and similarNot needed for these sources. If the company already pays for one, it can act as a mover by writing to S3 landing, under the same landing contract.
Documents and imagesPDF test reports and defect photos need a document pipeline; a later phase.
Email attachmentsPartners move to SFTP instead; email is not a reliable or auditable way in.

Where each mover stands

MoverSet up howStatus
AWS DMSTasks and table mappings generated from source_config into Terraform; one task per group of tables, so adding a table stops only its groupTo build (generator); DMS itself is a service
Amazon AppFlowFlows generated from source_config, one per object; an EventBridge rule per flow writes the manifest and starts the landerTo build (generator)
AWS Transfer FamilyOne server; a login, folder and rename workflow per partner, from source_configTo build (generator)
Our pullerThe framework's connectors: discovery and bulk reads for SQL Server, Oracle, PostgreSQL, MySQL, REST and SFTP. Any pull above the large-table threshold is split into segments (segment_run_log), in normal runs as well as in the bulk jobConnectors built; the Databricks job and the manifest to build
SiteWise / IoT Core and KinesisEdge set up per plant with the plant's OT team; streams from TerraformTo build, with the OT team

The standard transforms

Bronze to silver: thirteen generic stages, set per column

All of this happens between bronze and silver: bronze only ever appends what arrived, and silver is where columns are chosen, typed, cleaned, flattened and given history. Bronze keeps what the source sent; silver holds one clean, typed row per real thing, in the shape we chose. Between them sits one fixed list of generic transforms. Nothing in this step knows it is handling tyres: the same code runs for every table, and the metadata says which columns, which types, which formats and which history.

1
Generic, never domain-specific. Every transform from bronze to silver is the same code for every table, switched on and tuned per column in the metadata. Business logic (joining sources, derived measures, totals) belongs after silver, in the integrator and gold.
2
No free-form SQL in this step. Only the transforms in the list below. A need they cannot meet is a new generic transform, added once with its own tests, or logic for the integrator or gold.
3
Silver's shape is ours, not the source's. Silver holds the columns listed for the dataset, with the names and types we chose. Bronze keeps everything the source sent, so nothing is lost and a column can be added later by replay.
4
A value is never silently lost. A value that cannot be converted goes to quarantine, or is set empty and counted, as the column says. It never just becomes empty without a trace.

The thirteen stages, in order

The status column is where each stands in the framework today.

StageWhat it doesSet byStatus
1 Contract checkOn the bronze columns: every required column present, types compatible (see schema changes below)required, data_type, contract versionTo build
2 Column projection, extract and renameAll columns go on by default (minus any excluded); a dataset can instead list only the columns it needs; each can be renamed; for JSON payloads, only the listed paths are extracted into typed columns, and arrays of child records become their own silver tablesilver.columns, silver_column_name, source_path, explode_pathRename, exclude and flatten built; 'listed only' to build
3 HygieneTrim, collapse spaces, empty text to NULL, strip control characters and byte-order marks, normalise unicode and line endingsOn by default for text; transforms, skip listsBuilt
4 Parse and typeConvert to the silver type using the column's formats: dates, timestamps, comma or point decimals, epoch numbers, booleans; widen integers; one decimal scalesilver_data_type, date_format, timestamp_format, decimal_separator, on_cast_failureBuilt; formats and the failure action to add
5 TimeConvert to UTC from the source's time zone; add the plant's local time beside itsource_timezone, local_time_zone_fromUTC built; source zone and local column to add
6 Standardise valuesUpper-case codes; ISO country and currency codes; email and phone formats; unit conversion by factor; code mapping from a lookup table; a default for empty valuescolumn_role, transforms, unit_from / unit_to, code_map, null_replacementWritten but not switched on in the engine; units and code mapping to build
7 Personal dataTokenise, hash, mask or redact by the column's personal-data classpii_class, pii_policyBuilt
8 Row filterDrop rows that match a declared condition (test records, a cancelled flag); dropped rows are countedrow_filterTo build
9 Quality rulesRules from dq_rule; failing rows to quarantine with the reasondq_ruleBuilt (SQL rules); DQX behind the same port
10 DuplicatesNone, exact, first or latest per business key, or flag onlydedup.mode, dedup.business_keys, dedup.order_byBuilt
11 KeysBusiness key; conformed key from a mapping table (an unmapped code becomes 'unknown' and raises an alert)is_primary_key, key_mapTo build
12 History and deletesAppend, SCD type 1, SCD type 2 or snapshot; soft deletes; changes in source orderdataset_kind, history, history_track_columnsDesigned
13 ProvenanceRun id, load time, record hash, source file, batchNone: always onBuilt

Example: 100 columns in bronze, 30 in silver

A plant's MES table arrives with 100 columns. The dataset lists the 30 it needs; those 30 go on to silver, renamed and typed as listed; the other 70 stay in bronze. If the source adds a 101st column next month, bronze keeps it and silver does not change. If someone later needs one of the 70, they list it and replay from bronze.

The column settings

Set per column in the dataset's YAML (or per dataset where marked), loaded into the framework's column table. The last column says which stage reads it.

SettingValuesDefaultStage
silver.columns (dataset)all_except_excluded, declaredall_except_excluded2
silver_column_namea namethe source name2
source_patha path into a JSON payload, such as $.order.idnone (a plain column)2
explode_path, parent_keys (dataset)the array of child records; the keys carried downnone2
array_strategykeep, join_as_text, take_firstkeep2
requiredtrue, falsetrue for keys, false otherwise1
silver_data_typea Spark SQL typethe bronze type4
date_format, timestamp_formata pattern such as dd.MM.yyyyISO 86014
decimal_separatorpoint, commapoint4
on_cast_failurequarantine, null_and_count, stopquarantine4
source_timezonean IANA zone such as Europe/BudapestUTC5
local_time_zone_fromthe column holding the plantnone5
column_rolecode, identifier, measure, textnone6
transformsa list of opt-in transformsnone3, 6
unit_from, unit_tounits such as lbf and N, F and Cnone6
code_mapthe name of a mapping tablenone6
null_replacementa valuenone6
pii_classnone, name, email, phone, free_text and othersnone7
row_filter (dataset)a declared conditionnone8
is_primary_key, key_maptrue or false; a mapping tablefalse; none11
dataset_kind (dataset)master, transaction, event, snapshotrequired12
history (dataset)append, scd1, scd2, snapshotfrom dataset_kind12
history_track_columns (dataset)a list of columnsall listed columns12

JSON payloads: flattening happens here, not on the way in

1
Bronze keeps the payload whole. A JSON payload (an API response, an event) lands in bronze as one column, stored as VARIANT (Databricks' type for semi-structured data, check current status) or as text, untouched. Nothing is flattened on the way in, so a field nobody needed today is still there tomorrow.
2
Silver extracts only listed paths. Each silver column names its path in the payload (source_path, for example $.order.customer.id) and its type. Paths not listed stay in bronze. This is the same rule as listed columns: the payload's shape is the source's, silver's shape is ours.
3
Arrays of child records become their own table. An order with an array of lines gives two silver tables: order headers, and order lines with the order's key. The lines are a second dataset reading the same bronze table, with explode_path naming the array and parent_keys naming the keys it carries down. One row per real thing, at every level.
4
Small arrays of values stay as arrays. A list of tags or codes on one record stays an array column, or is joined into text, as the column says (array_strategy). Only arrays of records are split into tables.
5
Payload changes follow the schema rules. A new field is a new column (ignored until listed). A listed path that disappears is a removed column (required: stop; optional: empty with a warning). A value of the wrong type follows the column's on_cast_failure.
# bronze.dealer_api.orders: one row per API response, payload kept whole
#   payload = {"order": {"id": "SO-1001", "dealer": {"code": "D-77"},
#              "date": "31.10.2026", "lines": [{"sku": "205/55R16", "qty": "4"},
#                                               {"sku": "195/65R15", "qty": "2"}]}}

# silver.dealer_orders  (one row per order)
columns:
  - { name: order_id,    source_path: "$.order.id",          type: STRING, is_primary_key: true }
  - { name: dealer_code, source_path: "$.order.dealer.code", type: STRING, column_role: code }
  - { name: order_date,  source_path: "$.order.date",        type: DATE,   date_format: dd.MM.yyyy }

# silver.dealer_order_lines  (one row per line: a second dataset on the same bronze table)
explode_path: "$.order.lines"
parent_keys:  { order_id: "$.order.id" }
columns:
  - { name: sku, source_path: "$.sku", type: STRING, column_role: code, is_primary_key: true }
  - { name: qty, source_path: "$.qty", type: INT, on_cast_failure: quarantine }

A dealer API order: two silver tables from one bronze payload, only the listed paths extracted.

When a value will not convert

on_cast_failureWhat happensUse for
quarantineThe row goes to quarantine with the column, the value's problem and the rule; the rest of the table carries onKeys, measures, dates: anything a number depends on (the default)
null_and_countThe value is set empty and the failures are counted in the quality resultsDescriptive text the business can live without
stopThe table stopsRare: critical reference data where a bad value means the whole extract is wrong

When the source changes shape

The rule behind all of these: silver's shape is defined by our column list, not by the source, so most source changes either do not reach silver or are fixed in the metadata without silver changing.

Source changeBronzeSilverAlert
A new columnKept (personal-data sources: the file is held)Reaches silver by default; ignored for a dataset that lists its columns (declared)'New column seen', low
A listed column is removed-Required column: the table stops. Optional column: filled with empty values and a warning'Schema changed'
A listed column is renamedKept under the new nameStops (it looks like a removal). Fixed by mapping the new source name to the same silver name, so silver does not change at all'Schema changed'
A type is widened (int to bigint, longer text, more decimals)KeptAccepted automatically when the silver type can hold itNone
A type is narrowed or changed (text to number, number to text)KeptStops'Schema changed'
We add a column to silver-Filled from the next run; older rows refilled from bronze by a replayRelease note
We remove a column from silver-Never at once: it stops being filled for one contract version, then is removed with noticeRelease note

Each column is required or optional, and each contract version is minor (additive) or major (breaking), so consumers can tell which changes affect them.

History: SCD type 1 or type 2

Both, chosen per dataset. SCD type 1 overwrites a row when it changes (only the current version exists); SCD type 2 keeps every version with its valid-from and valid-to. The kind of data sets the default.

Kind of dataExamplesHistoryWhy
Master and reference dataPress, material, dealer, customer, plant, defect codesSCD type 2Facts join as of the event (a tyre's compound at cure time, the ideal cycle time that month), which needs the old versions
Transactions that change statusSales order lines, deliveries, warranty claims, work ordersSCD type 1; type 2 where the business needs the past stateUsually only the current state matters, and bronze keeps every change for audit. OTIF needs the date first promised, so sales order lines are type 2, with history_track_columns set to the promised date and quantity
Events and readingsPress events, sensor readings, downtime, test resultsAppendThey never change; duplicates removed by event id
Periodic snapshotsDaily stock, open orders at month endSnapshot: append with the snapshot dateEach day is its own fact, not a version
1
Only chosen columns make a new version. history_track_columns lists the columns whose change opens a new version; changes to the others (a last-updated stamp, a free-text note) update the current version in place. Without this, noisy columns multiply rows.
2
The kind sets the default. dataset_kind (master, transaction, event, snapshot) sets the history, so most datasets need no history setting at all.
3
Changing a dataset's history type is a replay. Moving a table from type 1 to type 2 rebuilds it from bronze, which still holds every change, so history is filled in for the past too.

Decisions

#DecisionStatus
D1Silver takes all columns by default (silver.columns = all_except_excluded); a dataset can list only the columns it needs (declared). Changed 3 Nov: was declared by defaultAgreed
D2A failed conversion never silently becomes empty; the default action is quarantineAgreed
D3The schema change rules above, with required per column and minor or major contract versionsAgreed
D4No free-form SQL from bronze to silver; only the standard transformsAgreed
D5The thirteen stages, in this orderAgreed
D7JSON stays whole in bronze; silver extracts only listed paths; arrays of child records become their own silver tableAgreed
D6History by dataset kind: type 2 for master data, type 1 for transactions (type 2 where needed), append for events, snapshot for snapshots; tracked columns onlyAgreed
D8One PySpark engine for every table: the same code runs the thirteen stages for any dataset, driven only by its metadata; a new table is new metadata rows, never new code. It runs on Spark (Databricks), not on local Python Delta libraries; the framework's processors are rewritten as Spark functionsAgreed

The settings

Every setting the runtime reads, and where it comes from

The runtime is driven by metadata, so its settings are its real interface. This is the complete list for AWS and Databricks, at six levels: platform (one set per environment), source, dataset, column, gold fact and domain. Each setting names the part that reads it and the test situations that exercise it, and is compared with the framework's registry today: exists (used as is), adapt (exists but Azure-shaped or with a new meaning), new, or retire (Azure Data Factory only). A setting is written once at the level where it belongs, and lower levels may override it (platform, then source, then dataset, then column).

LevelExistsAdaptNewRetireRows
Platform (per environment)5416328
Source3313120
Dataset4319430
Column10110122
Gold fact00808
Domain00404
Total2211709112
Platform (per environment) (28 rows)
SettingValuesDefaultRead byTested inStatusNote
runtime_environmentdev, preprod, prodprodrecorder, deployS29adapt'staging' becomes 'preprod'
realmaws-databricks, adf-adlsaws-databricksdeployS29adaptwas default_realm; chooses which generators the deploy runs
aws_regionAWS region codeap-south-1deploy, moverS29new
catalog_prefixtextthe environment name plus _lander, silver, gold, deployS23newdev_, preprod_, prod_ in front of bronze, silver, gold, ops
landing_bucketS3 bucketlanding-<env>mover, landerS01newreplaces target_storage_account
landing_pii_bucketS3 bucketlanding-pii-<env>mover, landerS21new
secrets_backendaws_secrets_manageraws_secrets_managermover, landerS29newreplaces key_vault_uri
secret_prefixtextdp/<env>/mover, landerS29new
metadata_db_endpointLakebase (Databricks managed PostgreSQL) instance and databasenonerecorder, controllerS13adaptwas metadata_db_server (Azure SQL)
recorder_timeout_secondsinteger10recorderS13new
recorder_retriesinteger2recorderS13new
job_timeout_minutesinteger30controllerS13, S14newevery generated job; the sweeper uses it too
metadata_schema_versionintegernonerecorder, deployS29exists
default_file_formatDELTADELTAlanderS29exists
default_concurrencyinteger 1-508controllerS01, S05adaptwas default_batch_count (ADF ForEach); now the for-each concurrency
redaction_extra_patternslist of patternsnonerecorderS21exists
large_table_row_threshold, huge_table_row_thresholdintegers10 million, 1 billioncontrollerS30existsdecides segmenting in the bulk job
auto_retry_on_partial, auto_retry_max_attempts, auto_retry_delay_minutestrue or false; integersfalse, 2, 30controllerS20exists
tier_targetsfreshness and paging per tiertier 1: page any hour; tier 2: plant hours; tier 3: working hoursopsS03, S11new
mass_delete_limit_pctpercent5landerS08newdatasets may override
held_file_daysinteger7lander, opsS17new
pii_landing_hoursinteger23opsS21newthe hourly purge
stream_late_allowance_minutesinteger120goldS10new
ops_interval_minutesinteger15opsS01, S14new
heartbeat_interval_minutesinteger15heartbeat, opsS22new
key_vault_uri, target_storage_account, target_container_default, shir_name, staging_linked_service----retireAzure only
flatten_function_url, flatten_function_secret_name, flatten_databricks_*----retireflattening moved into silver (stage 2)
lifecycle_cool_days, lifecycle_archive_days----retirestorage tiers set by Terraform and S3 lifecycle rules
Source (20 rows)
SettingValuesDefaultRead byTested inStatusNote
source_typesql_server, oracle, postgres, mysql, sap, saas, rest_api, partner_files, stream, uploadnonecontroller, moverS01, S02adapttyped on source_master; adds sap, partner_files, stream, upload
moverdms, appflow, transfer_family, kinesis, puller, uploadnonemover, controller, deployS01, S02, S06, S10new
clockschedule, appflow_finished, upload_copiedset by the movercontrollerS01, S02, S04newone clock per source; streams use schedule (every 15 minutes, trigger-once), never a continuous run
schedule_cron, schedule_timezonecron; time zoneevery 15 minutes; UTCcontrollerS01, S04adaptmoves from dataset to source: the lander job is per source
auth_method, connection_secret_nameas today; the secret is a Secrets Manager namenonemoverS30adaptkv:// references become Secrets Manager names
connect_timeout_seconds, rate_limit_rpsintegers30; nonemoverS30existspuller and REST
max_concurrent_datasetsinteger 1-50default_concurrencycontrollerS05existsthe for-each concurrency for this source
server, database, service_name, base_url and other connection keysper source type, as todaynonemoverS30existsused by the puller
integration_runtime----retireADF only
dms_task_grouptextone group per sourcemover, deployS18newadding a table pauses only its group
dms_min_log_retention_hoursinteger72doctorS03newchecked by the healthcheck, not enforced by us
dms_latency_limit_secondsinteger300lander, opsS03, S22newthe quiet rule: bookmark is now minus latency while below this
appflow_connector_profile, appflow_flow_prefixnamesnonemover, deployS02new
partner_login, partner_foldernamesnonemover, deployS06, S07new
control_filenone, row_count_filenonelanderS06new
kinesis_stream, kinesis_retention_daysname; integernone; 7lander, deployS10new
has_personal_datatrue, falsefalsemover, landerS17, S21newroutes landing to the personal-data bucket
owner, deputypeople from the identity providernoneopsS16, S25newchecked nightly against the identity provider
contract_ref, contract_versionpath in Git; semvernonesilverS16, S25new
source_timezoneIANA zoneUTCsilverS24newcolumns may override
Dataset (30 rows)
SettingValuesDefaultRead byTested inStatusNote
dataset_type, load_type, schema_name, object_name, custom_queryas todaynonemover, landerS01, S08exists
dataset_kindmaster, transaction, event, snapshotrequiredsilverS27newsets the default history
domainmanufacturing, quality, sales, aftermarketrequiredcontroller, silverS01newwhich domain pipeline builds it
tier1, 2, 33opsS03, S11new
freshness_targetminutes behind, or a time of dayfrom the tieropsS03, S22adaptwas sla_freshness_hours
expected_deliverya time and daysnoneopsS22newpartner files: freshness judged against it
volume_bandauto, or fixed low and highauto (from the last 8 same weekdays)opsS33new
owner, deputy, stewardpeoplethe source'sopsS05new
max_files_per_run, max_gb_per_runintegers500; 20landerS18newspreads big first loads over runs
watermark_column, watermark_initial_valueas todaynonemoverS30existspuller, append-only tables only
change_orderdms, sap_counter, arrival, snapshot, event_timeset by the moversilverS01, S02newthe sequence AUTO CDC uses
delete_ruledms_op, sap_change_mode, snapshot_compare, replace_slice, noneset by the moversilverS02, S09new
replacement_scopelist of columnsnonesilverS07newpartner files: what a re-sent file replaces
mass_delete_limit_pctpercentthe platform'slanderS08newoverride
silver.columnsall_except_excluded, declaredall_except_excludedsilverS23newall by default; declared = projection to listed columns
row_filtera declared conditionnonesilverS32new
quarantine_max_pctpercent2silverS32newstop the table past this share of bad rows
dedup.mode, dedup.business_keys, dedup.order_byas todaynonesilverS27exists
historyappend, scd1, scd2, snapshotfrom dataset_kindsilverS27new
history_track_columnslist of columnsall listed columnssilverS27new
explode_path, parent_keysa path; key pathsnonesilverS28newchild datasets from JSON arrays
profile.enabled, profile.when, profile.scope, profile.sample_pct, profile.columns, profile.suggest_rulessee the platform pageoff, daily, new_rows, 10, all, truesilver, opsS34new
file_format, file_pattern, file_encoding, csv_*, excel_*, xml_record_xpathas todayas todaylanderS06, S24adaptread by the lander instead of ADF
load_profile, segmentation_*, segment_countas todayautocontrollerS18existsthe bulk job
budget_daily_usdnumbernoneopsS33newjudged against the estimated cost split per dataset
realmaws-databricksthe platform'sdeployS29adaptnew realm name
target_container, target_path_template----retirethe landing layout is fixed by the landing contract
intended_dius, parallel_copies, enable_staging, sink_max_concurrent_connections, partition_*----retireADF copy settings
flattening_mode, flattening_engine, flattening_path_separator, primary_array_path, array_max_explode_rows----retirereplaced by source_path, explode_path and array_strategy in silver
lifecycle_cool_days, lifecycle_archive_days, lifecycle_delete_days----retireretention set per layer by the platform
Column (22 rows)
SettingValuesDefaultRead byTested inStatusNote
column_name, data_type, is_nullable, ordinal_positionas todayfrom discoverysilverS23exists
is_primary_keytrue, falsefalsesilverS27exists
is_excludedtrue, falsefalsesilverS35existsused with the default silver.columns = all_except_excluded
requiredtrue, falsetrue for keyssilverS25new
silver_column_namea namethe source namesilverS23, S25exists
source_patha JSON pathnonesilverS28exists
array_strategykeep, join_as_text, take_firstkeepsilverS35adaptexplode moves to explode_path
silver_data_typea Spark SQL typethe bronze typesilverS24, S26exists
date_format, timestamp_formatpatternsISO 8601silverS24new
decimal_separatorpoint, commapointsilverS24new
on_cast_failurequarantine, null_and_count, stopquarantinesilverS24newreplaces cast_safety
cast_safety----retirereplaced by on_cast_failure
source_timezoneIANA zonethe source'ssilverS24new
local_time_zone_froma column naming the plantnonesilverS31new
column_rolecode, identifier, measure, textnonesilverS31exists
transforms, skip_groups, skip_processorslistsnonesilverS31exists
unit_from, unit_tounitsnonesilverS31new
code_mapa mapping tablenonesilverS31new
key_mapa mapping tablenonesilverS31newconformed keys; unmapped becomes unknown
null_replacementa valuenonesilverS31exists
pii_classnone, name, email, phone, free_text and othersnonelanderS21exists
pii_purpose, pii_retention_daystext; integerrequired when pii_class is setlander, opsS21new
Gold fact (8 rows)
SettingValuesDefaultRead byTested inStatusNote
fact_name, grainname; for example press-hourrequiredgoldS01new
window, lookbackhour or day; a durationhour; 2 daysgoldS03, S10new
inputsdatasets with a role: driver, dimension as of, contributorrequiredgoldS01, S05new
completenesson, offongoldS03, S05, S11new
max_unknown_pctpercent1goldS09new
partition_columna date columnthe window's dategoldS12new
backfill_chunk_windowsinteger24goldS12new
reads_correctionstrue, falsefalsegoldS12new
Domain (4 rows)
SettingValuesDefaultRead byTested inStatusNote
domain_namenamerequiredcontroller, deployS01new
sourceslist of sourcesfrom its datasetscontrollerS01newthe marker tables its wrapper watches
min_gap_minutesinteger15controllerS36new
gold_factslist of factsnonecontrollerS01newrecompute jobs the wrapper calls

Checks. Every setting in use names the part that reads it. 0 settings in use are not exercised by any test situation yet: .

Source: _guides/etl_settings.py. Several rows group related keys (for example the six profiling settings). The framework's registry (docs/metadata_keys.md) will be updated from this list when building starts.

The runtime map

Ten parts, three bands

Read the middle row left to right: data moves from source systems to metrics. The band above decides when things run; the band below records everything that ran. Nothing in the middle row starts itself, and nothing runs without being recorded.

Run controller: Lakeflow Jobs generated from the metadata. One clock per source; silver on the batch-ready marker; gold called by silverreads dataset_view in Lakebase · writes master_run_log · queues, never overlapsSourcesSAPMES databaseQuality lab, warrantyDealer filesPress sensorsMoversDMS, AppFlow,Transfer Family,Kinesis, our pullerLanding (S3)unique file namesthe landing contractLander (ours)file check, tokenise,append to bronze,write the markerSilver builderDeclarative Pipelineper domain: contract,rules, AUTO CDCGold builderwindow recompute,completeness frominput bookmarksMetric viewsdefined once;dashboards, Geniethe lander never talks to a sourceRun recorder: run, step, file, error, quality and bookmark rows with the same ids, with timeoutsLakebase runtime schema → ops builder every 15 minutes (and the sweeper) → prod_ops: dashboard, alerts, incidents
The middle row moves and shapes data; the top band starts it; the bottom band records it.

The working parts

What each part does, reads and writes

Each part has one job and a fixed contract. The last column is the most important: it is what lets the platform retry without a person checking.

PartDoesReadsWritesRuns asSafe to run twice because
1 MoverBrings data from a source to S3 landing (or Kinesis, for streams)source_configFiles in landing under the landing contract (push); bronze batches written directly (pull)AWS DMS, AppFlow, Transfer Family, SiteWise and Kinesis (push), or our puller (pull, straight to bronze)Unique file names; a re-sent file is a new file
2 Run controllerStarts the work: one clock per source, a marker for silver, the wrapper for golddataset_view, schedules, triggers, markersmaster_run_log; the job runLakeflow Jobs, generated from metadataRuns queue instead of overlapping; a run with nothing due ends as skipped
3 LanderLanding to bronze: picks new files from the file log, checks them, tokenises personal data, adds ingest columns, appends; writes the batch-ready markerdataset_view, the file log, the suppression listBronze tables, file_log, run_log and step_log rows, the markerOur PySpark job, one task per datasetThe file log says what was read; each batch is appended with a transaction id, so it lands once
4 Silver builderBronze to silver: the thirteen standard stages (contract, select, clean, type, time, standardise, personal data, filter, quality, duplicates, keys, history, provenance), replacement rulesBronze, dq_rule, dataset_configSilver tables, quarantine tables, the pipeline event logA Declarative Pipeline per source (silver is source-aligned), generated, started by markersAUTO CDC orders changes by a defined sequence, so reprocessing gives the same result
5 ConformerConforms sources on the way into gold, because silver stays source-aligned: MES production with SAP orders, materials mapped through mapping tablesSource-aligned silver, key mappingsShared gold dimensions and inputs to gold factsThe common gold harnessConforming happens once, in gold; silver is never joined across sources
6 Gold builderSilver to gold: star schemas, multi-input facts, completeness per window; the only writer of each gold factSource-aligned silver, mapping tables, corrections, input watermarksGold facts and dimensionsMaterialized views for simple facts; our window-recompute job for multi-input factsEach window is replaced whole (replaceWhere on date partitions), never patched, and only one job writes a gold fact
7 Metric layerEach metric defined onceGoldNothing stored; answers at query timeUnity Catalog metric viewsNothing to run
8 Run recorderThe one library every part calls to log-runtime: master_run_log, run_log, step_log, file_log, error_log, dq_result, input_watermark, config_snapshotOur Python library, writing to Lakebase through the stored procedures, with timeoutsRows are keyed by run id and step; writing the same row twice updates it
9 Ops builderEvery 15 minutes: copies runtime rows, joins system tables and CloudWatch, works out freshness, sweeps stuck runs, raises alertsruntime schema, system tables, CloudWatch landingprod_ops tables, alerts, incidentsA small Lakeflow jobIt recomputes the last hour each time, so a missed run is caught up by the next
10 HeartbeatProves the whole path is alive, even when no source sends anything, and is watched without our databaseA tiny synthetic sourceA row in every layer every 15 minutes; a Databricks SQL alert watches the newest oneA generated dataset like any otherSame as any dataset

Every row says where it came from

LayerColumns added to every rowSo you can tell
Bronze_run_id, _file, _source_system, _ingested_at, and from the mover: _op, _commit_ts, _change_seq (DMS) or _change_mode (SAP)Which run and file brought this row, and its place in the source's order of changes
Silver_valid_from, _valid_to, _is_current, _is_deleted, _source_run_id (the lander run that brought the latest version)History, and the lander run behind each version. The silver pipeline update itself is traced by its update id in run_log
Quarantine_run_id, _rule_id, _reason, _quarantined_atWhy the row was parked, and by which run
Gold (our jobs)_run_id, _window_start, _is_complete, _inputs_as_ofWhich run built this window, whether every input had caught up, and how far each had
Gold (materialized views)None: refreshed by the pipelineTraced by the pipeline update id in run_log and its event log

Order, deletes and corrections

Changes in the right order, deletes never missed, shape changes caught

Silver keeps one row per real thing, with its history. That is only right if changes are applied in the order they happened, deletes are seen, and a fix to old data is told apart from a new change.

Kind of inputOrder of changesDeletes
DMS changesSequence by commit time, then DMS's change sequence (AR_H_CHANGE_SEQ), so two changes in the same instant keep their order. Full-load rows carry an operation flag too (IncludeOpForFullLoad). Every load of a table has a generation number, kept by the lander in the file log: when DMS starts a reload of a table (new full-load files for a table already in change capture), the lander opens a new generation. Its full-load rows sort after everything earlier and before its own changes, so stale changes can never win, and unread change files of the old generation are marked superseded, which unblocks a blocked dataset.Delete when _op = 'D'
SAP deltasSequence by the extraction run, then the extractor's change counter where it has one (check current status per object).Delete when the change mode says deleted; objects whose delta does not carry deletes are loaded as a nightly full copy instead
Full copies (puller, snapshots)Each copy is a snapshot in time. Silver compares it with the previous one (AUTO CDC from snapshot).A row missing from the new copy is deleted, unless the mass-delete guard stops the load
Partner filesOrder by arrival time.Each dataset's contract says what a file replaces, for example all rows for that dealer and month; silver replaces that slice with the newest file
StreamsEvent time; duplicates removed by reading id.Readings are never deleted
1
Deletes are soft. Silver never removes a row. In history tables (SCD2) a delete closes the current version: _valid_to is set and _is_deleted is true. In tables without history, the row stays with _is_deleted true. Current views, and every metric, leave deleted rows out, so a deleted order line stops counting in sales.
2
The mass-delete guard runs in the lander, before bronze. For a full copy, the lander compares the keys in the checked files with silver's current keys before appending anything. If more than a set share would be deleted (5% by default, set per dataset), nothing is appended, the files are held, the dataset is BLOCKED and the 'mass delete stopped' alert fires. Bronze never holds the short copy, so no later pipeline update can apply it. A pull that came back short matches its own manifest, so only this guard catches it.
3
Deleted members stay in dimensions. A decommissioned press keeps its row in gold's press dimension, flagged, so old facts still find it. Facts join dimensions as of the event time, never just to the current row.
4
A change is not a correction. A change is something new in the world (a press gets a new mould): history keeps the old value up to that date. A correction says the old value was wrong (the ideal cycle time was always 92 seconds, not 95). Silver keeps what the source said; corrections live in their own table (silver.corrections: entity, field, corrected value, applies from, applies to, who, why, status), and gold lays the active ones over silver when it builds a window.
5
Corrections reach gold only through a backfill plan. A new correction is pending. Only the window-recompute job reads corrections, and only active ones; materialized views never read the corrections table, so a fact or dimension that needs corrections is built by the recompute job. The backfill plan activates a correction and says which windows to rebuild and when, so a correction never restates months by itself at a busy hour. Activating a correction is not an input change for the normal recompute; only the plan rebuilds its windows.

When a source changes shape

1
A new column, source without personal data. Kept in bronze as rescued data and ignored until someone maps it. The contract check does not stop on an added column. A low-priority 'new column seen' note goes to the steward.
2
A new column, source with personal data. The file is held in the personal-data landing bucket (kept up to 7 days while held, instead of 1), the dataset is BLOCKED and the 'schema changed' alert fires, because an unknown column there could be personal. Its name is logged; its values never are. Once the contract says what it is, the held files load. Personal data is not kept waiting indefinitely: if the contract is not updated within 7 days, the held files are deleted (the alert escalates on day 5) and the source re-sends after the fix.
3
A removed, renamed or retyped column. Silver's contract check stops that table; silver stays as it was and the 'schema changed' alert goes to the source owner named in the contract. For sources without personal data, bronze still holds everything, so after the new contract version the table is replayed from bronze; for personal-data sources the held files are loaded under the new contract. SAP is never asked to re-extract.

What the lander does to personal data

The full privacy design is on the platform page; these are the steps that happen in the lander.

1
Tokenise before bronze. Names, phone numbers and emails are replaced by random tokens; the real values go only to the token vault (prod_pii_vault). Random, so deleting the vault entry really does make the data anonymous.
2
Scan free text. Free-text fields such as warranty notes go through a personal-data detector behind a port; names it finds are tokenised too.
3
Check the suppression list. People who asked to be erased are not loaded again.
4
Leave nothing behind. An hourly purge job deletes personal-data landing files the file log marks loaded more than 23 hours ago, so none outlives a day; held files are kept up to 7 days; a lifecycle rule at 8 days is only the backstop. No log row holds a real value: error messages are scrubbed, file_log reasons name the problem not the content, and dq_result holds counts and tokenised keys only.

Completeness and freshness

Knowing a number is complete, not just on time

Sources arrive at different speeds, and sometimes one stops. Without care, gold builds an hour from the inputs that happened to arrive and calls it done, and OEE is wrong with nobody told. These five rules prevent that.

1
Every input has a bookmark in source time. The lander writes how far bronze is loaded after every run, empty runs included: for DMS the newest commit time; for SAP, SaaS and the puller the extraction time of the run; for partner files the business date of the newest file. The wrapper writes how far silver has applied it after each pipeline update. Streams write the newest event time, per machine.
2
Quiet sources still move forward. A run that finds nothing new still moves the bronze bookmark to the time it looked; for DMS, while the task is running, that is 'now minus DMS's target latency' (CloudWatch's target latency counts changes DMS holds but has not yet written to S3, unlike source latency), never later than the newest commit time already landed plus that latency; a stopped task or a latency above the limit freezes it. Partner files start no run when nothing arrives, so their bookmark is the business date of the newest file and freshness is judged against the next expected delivery: on a quiet night nothing is expected and nothing is late. When bronze holds nothing silver has not applied, the silver bookmark is set equal to the bronze one by the ops builder, so a quiet source stays green without running silver.
3
Streams are tracked per machine. One silent press does not move a stream's newest event time, so the stream's bookmark is kept per press. Presses not in a planned shift are left out.
4
A window is complete when every input is past its end. The window-recompute job marks a press-hour complete only when every input it needs, including that press's sensor bookmark, is past the end of the hour (plus the late allowance for streams). Otherwise the hour is published as incomplete, left out of OEE's top and bottom, and the dashboard says so.
5
Incomplete hours are revisited. Each recompute run also takes the incomplete hours whose inputs have now caught up, even if no row in them changed. When the ops builder moves a bookmark past an incomplete hour, it queues a recompute run.
6
Freshness is the bookmark, not the last run. A dataset is fresh when its silver bookmark is within its target, whatever its runs say. A source that went quiet because it broke goes red; one that is genuinely quiet stays green.
7
Every rebuild of a published window is a restatement. Whether a late input caught up, a partner corrected a file, or a backfill ran, every window rebuilt after it was first published is logged in ops as a restatement and shown on the dashboards.

One run, step by step

A quarter-hour of MES data, from database to gold

Follow one load: changes to the press events table in the plant's MES database, picked up at 00:15 and in gold by 00:20. Times are illustrative.

1
All the time: DMS lands change files. AWS DMS reads the MES database's change log and writes Parquet files to landing/mes/<table>/<date>/, each row with its operation flag, commit time and change sequence.
2
00:15:00 One clock starts the lander. The MES lander job runs on a 15-minute schedule; that is its only trigger. If the previous run is still going, the new one waits in the queue instead of overlapping.
3
00:15:02 Open the master run. The first task writes the master_run_log row (RUNNING), reads dataset_view for every enabled MES dataset, stores the merged settings once in config_snapshot, and lists datasets with files not yet loaded. If none has, it moves each dataset's bronze bookmark by its quiet rule (a stopped DMS task keeps it frozen) and closes the master run as SKIPPED.
4
00:15:05 One task per dataset, eight at a time. Each opens its run_log row (STARTED) with the master run id, job run id and task run id.
5
00:15:06 Check the files, then load exactly those. The new files are checked (readable, expected header) and written to file_log as one numbered batch, status checked. The lander reads exactly that list, tokenises personal columns, adds the ingest columns and appends to bronze with the batch's transaction id.
6
00:15:40 Close the run after the commit. After the Delta commit the file log marks the batch loaded, and run_log becomes SUCCEEDED with rows read, written and rejected and the newest commit time as the run's watermark, in one Lakebase (PostgreSQL) transaction. If that write fails, the sweeper finishes it later from the evidence in bronze.
7
00:15:45 Close the master run and write the marker. The closing task runs even after failures. It marks the master run SUCCEEDED, PARTIAL (some datasets failed or are blocked) or FAILED, and writes the batch-ready marker: one row per dataset that loaded (dataset, master run, status) in that source's own marker table (prod_ops.batch_ready_<source>), a Unity Catalog Delta table written with Spark, so each domain's trigger watches only its own sources; blocked datasets get no row. The job itself ends green when every failure was a handled data problem; PARTIAL raises its alert from our log, not from a job failure.
8
00:16:00 The marker starts silver. The manufacturing wrapper job is triggered by updates to the marker tables of its sources (MES, the press master), not by each bronze table, so it never runs on a half-loaded source. It opens a run_log row, starts the pipeline update itself through the Pipelines API, stores the update id it gets back and the settings fingerprint the pipeline was generated from, and waits for the update to finish.
9
00:16:05 The silver pipeline runs. The thirteen standard stages, AUTO CDC in the defined order, soft deletes.
10
00:19:30 The wrapper closes the update and starts gold. It copies rows, expectation results and errors from the pipeline's event log into our tables, writes each input's bookmark to input_watermark, closes the run, then starts the window-recompute job directly (never on a table trigger, which could fire in the middle of an update).
11
00:20:00 Gold, with completeness. The recompute (the only writer of the production fact) takes the hours that changed in any input, plus any queued backfill chunk, rebuilds them whole with corrections laid over silver, and marks each hour complete only if every input's bookmark is past its end. Rebuilt hours that were already published are logged as restatements.
12
00:30:00 The ops builder sees it. It copies the runtime rows into prod_ops, joins job runs and cost from system tables, reads skipped and queued runs too, and updates freshness from the bookmarks.

Orchestration: jobs and triggers

What starts what

Lakeflow Jobs is the one orchestrator, and every job in it is generated from metadata. Each source has exactly one clock. Silver starts when a source has finished a batch, and gold starts when silver has finished. Nothing polls for no reason, and nothing starts on a half-written input.

Why there is no second orchestrator

Everything that runs (the puller, the lander, the silver engine, gold and the ops jobs) runs on Databricks, so Lakeflow Jobs already sees each run's status, cost and lineage in one place. Airflow (MWAA), Step Functions or Glue workflows would be one more system to run, secure and pay for, without solving a problem we have. The only steps that start outside Databricks are a source finishing on the AWS side, and those reach a job through EventBridge, a Lambda or a file arriving, as the table below shows.

Four rules every job follows

The triggers

TriggerUsed forStarts
ScheduleEvery DMS source (its files arrive all the time, so a clock is calmer than file events); the pullerThe lander job of that source
AppFlow finishedSAP and SaaS sources: an EventBridge rule on the flow-run event calls a Lambda that writes the manifest and starts the lander through the Jobs APIThe lander job of that source, so there is one clock: AppFlow's
Upload copiedPartner files and uploads: the Transfer Family workflow's Lambda step starts the lander after copying the file to landingThe lander job of that source
Table update on the markerSilver: runs when one of its sources has finished a batchThe source's wrapper job, which starts that source's pipeline update through the API
Called by the wrapper, the stream hour job or the ops builderGold multi-input facts: after a pipeline update, after a sensor hour closes, or when bookmarks pass incomplete hoursThe window-recompute job, queued, never two at once
Schedule, every 15 minutesSensor streamsThe streaming pipeline, trigger-once: each run reads what has arrived since its checkpoint and stops
Manual or API, queuedRe-runs, first loads, backfill plans, the heartbeat testA queued run of the lander or recompute job, or the bulk job for first loads, with a run type that says why

The jobs the generator writes

JobHow manyInsideStarted by
Lander jobOne per sourceOpen, a for-each over due datasets (concurrency from source_config), close and write the markerIts one clock (schedule, AppFlow finished, or upload copied)
Bulk jobOne per source without DMSFirst loads and full reloads for puller, AppFlow, historian and file sources, in segments; it writes to the same file log, so the lander job carries on exactly where it stopped. DMS tables use DMS's own full load instead, whose rows carry an operation flag and sort before that load's own changesManual or API
Source wrapper and pipelineOne per source (silver is source-aligned)A small job that starts the source's Declarative Pipeline update (its silver tables, defined in a loop from metadata) through the API, records it, and calls gold. One run at a time, queued (Databricks refuses a second update of a running pipeline). Each run takes every marker row newer than the last one it processed, stored on its run row, so a marker that lands during an update is picked up by the next queued run, no sooner than min_gap_minutesTable update on its sources' markers
Window-recompute jobOne per multi-input gold fact; the only writer of that factChanged windows from the last update, plus one chunk of any queued backfill plan; corrections laid over silver; completeness; reconcile; recordCalled by the domain pipeline's wrapper; queued, never two at once
Streaming pipelineOne per stream family (press sensors, mixer sensors)Kinesis to bronze to silver, trigger-once: each run takes what has arrived since its checkpoint and stopsEvery 15 minutes, on a schedule
Stream hour jobOne per stream familySensor aggregates for the closed hour and a re-check of the last 2 days (an input to gold); then calls the window recomputeHourly, at 15 minutes past
Ops builderOneCopy runtime rows, join system tables and CloudWatch, freshness, sweep stuck runs, alertsEvery 15 minutes
Personal-data purgeOneDeletes personal-data landing files loaded more than 23 hours ago, and held files past 7 daysHourly
Cost splitOne (part of the ops builder)Databricks bills per job run and pipeline update, not per dataset, so cost is split: a lander run's cost by each dataset's share of its task time (step_log), a pipeline update's cost by each table's share of rows written (event log). These estimates feed budget_daily_usd and the cost-over-budget alertEvery 15 minutes
HousekeepingOneRetention deletes, VACUUM, OPTIMIZE where predictive optimization is off, erasure requests, daily DMS reconcileNightly

One generated job, as an example. It is produced from the metadata for every source; people change the YAML in the metadata, never this file.

# Generated from dataset_view by the framework's generator; never edited by hand.
# (Asset Bundle job syntax: check current status)
resources:
  jobs:
    lander_mes:
      name: lander_mes
      schedule: { quartz_cron_expression: "0 0/15 * * * ?", timezone_id: "UTC" }   # the one clock
      max_concurrent_runs: 1
      queue: { enabled: true }               # a run waits; it never overlaps or vanishes
      timeout_seconds: 1800                  # nothing waits forever
      health:
        rules:
          - { metric: RUN_DURATION_SECONDS, op: GREATER_THAN, value: 900 }   # alert without our database
      tasks:
        - task_key: open_master_run
          python_wheel_task: { package_name: lake_ingest, entry_point: open_master_run,
                               parameters: ["--source", "mes"] }
          timeout_seconds: 120
        - task_key: load_each_dataset
          depends_on: [ { task_key: open_master_run } ]
          for_each_task:
            inputs: "{{tasks.open_master_run.values.due_datasets}}"
            concurrency: 8
            task:
              task_key: land_one
              python_wheel_task: { package_name: lake_ingest, entry_point: land,
                                   parameters: ["--dataset", "{{input}}"] }
              max_retries: 2                 # reached only by passing errors: the lander
              min_retry_interval_millis: 60000   # ends data errors itself, as handled
        - task_key: close_master_run
          depends_on: [ { task_key: load_each_dataset } ]
          run_if: ALL_DONE                   # closes the master run and writes the marker
          python_wheel_task: { package_name: lake_ingest, entry_point: close_master_run }

The lander job for the MES source, generated as an Asset Bundle resource.

Ids and log rows

One id for every piece of work

Seven ids, some ours and some Databricks', are written side by side, so our logs, Databricks' system tables and the data itself all join without guessing by time.

IdMade byMeansFound in
master_run_idOursOne trigger of one job: all the datasets loaded togethermaster_run_log, the batch-ready marker, every run_log row of that trigger
job_run_idDatabricksThe Lakeflow job runStored on master_run_log and run_log; joins system tables (timings, cost, skipped and queued runs)
run_idOursOne dataset through one stage (land, silver update, gold window)run_log, step_log, file_log, error_log, dq_result, and _run_id on rows our jobs write
task_run_idDatabricksThe task (or for-each iteration) that did the workStored on run_log; links to the task's own log in Databricks
update_idDatabricksOne update of a Declarative PipelineStored on run_log; joins the pipeline event log
segment_idOursOne slice of a bulk loadsegment_run_log; run_log adds up its segments
config fingerprintOursThe exact merged settings a run usedconfig_snapshot; any run can be replayed with what it ran with
master_run_logone trigger of the MES lander jobrun_log: press_event, landrun_log: downtime, landrun_log: work_order, landfile_log: 4 files checkedstep: tokenise, appenddq_result rowsbatch-ready marker: MES, this master runrun_log: manufacturing pipeline updateupdate id stored; input_watermark writtenrun_log: production fact, window recomputeEvery box carries the job run id from Databricks, so system tables add timings and cost to each one.Every row our jobs write carries the run id of the box that wrote it.
The log hierarchy for one MES trigger and the work it sets off.

The log tables

The framework's run tables, plus two new ones (file_log and input_watermark), in the runtime schema of the metadata database.

Log tableOne row perHolds
master_run_logOne per trigger of a jobRUNNING, SUCCEEDED, PARTIAL, FAILED, or SKIPPED when nothing was due; counts of datasets attempted, succeeded, failed, blocked; run type; retry of which master run
run_logOne per dataset per stageSTARTED, SUCCEEDED, FAILED, SKIPPED, BLOCKED; rows read, written, rejected; the watermark before and after; durations; cause class
segment_run_logOne per segment of a bulk loadThe segment's predicate, rows and status; the run adds them up
step_logOne per step inside a runcheck, land, tokenise, guard, contract, quality, merge, reconcile: rows in and out, time taken
file_logOne per landing fileName, size, rows, which batch, and a status: checked, loaded, rejected or held, with the reason (never the content). The lander takes every file that is new or checked (never loaded, rejected or held)
input_watermarkOne per inputTwo bookmarks in source time: how far bronze is loaded (written by the lander on every run, empty runs too) and how far silver has applied it (written by the wrapper after each pipeline update, and set equal to the bronze bookmark by the ops builder for inputs with nothing waiting). For streams, the stream hour job writes the per-press bookmarks. The basis of completeness and freshness
error_logOne per failureCause class (source down, schema change, bad data, our bug, platform, capacity), scrubbed message, passing or not
dq_resultOne per rule per runPassed or failed, failing row count, action taken; tokenised keys only

The run recorder: six calls

1
open_master_run(source, trigger) Checks the metadata database's schema version against metadata_schema_version and stops before any work if they differ. Writes the master run row, with the environment name; returns its id. Times out after 10 seconds and retries twice; if it still cannot write, the job fails before any work.
2
open_run(dataset, stage) Writes the run row with all ids it can see (job run, task run, update id).
3
step(name, rows_in, rows_out) One step row, measured for you. Step and quality rows are buffered and retried; a failure to log a step never fails the data work.
4
error(exc) Classifies the error (source timeouts, network errors and busy clusters are passing; bad data, schema changes and bugs are not), scrubs the message of data values and secret values, writes error_log, and says whether it is passing (retry) or not (end as handled).
5
quality(results) Writes dq_result rows from DQX or the pipeline's expectations.
6
close_run(status, counts, watermark) Marks the run and stores its watermark in one Lakebase (PostgreSQL) transaction, after the Delta commit. If it cannot, the sweeper finishes the run later from the evidence in bronze.

The sweeper: no run stays stuck

The Delta commit and the log database are two separate systems, so a crash between them can leave a run marked as started forever. The sweeper closes such runs from the evidence.

1
Finds stuck runs. Every 15 minutes the ops builder lists runs still STARTED past their job's timeout.
2
Looks for evidence. If bronze holds rows with that run id, the commit happened: the run is closed as SUCCEEDED with counts from bronze, its files are marked loaded and its watermark stored. If not, it is closed as FAILED and its files go back to 'checked', so the next run loads them.
3
Says what it did. Every sweep is logged, so a run closed by the sweeper is visible as such.

Streaming

Sensors fit the same model

A stream never ends, but our streaming pipelines never run continuously: they run trigger-once on a schedule, every 15 minutes, so each run has a clear start and end. These five rules make streams look like everything else to on-call and to the dashboard.

1
Every scheduled run is a run, and a missing one is never silent. Every 15 minutes the streaming pipeline runs trigger-once: it reads what has arrived in Kinesis since its checkpoint, writes bronze and silver, and stops. Each run gets one run_log row per stream with rows, lag and errors, like any other run. If a scheduled run failed or did not happen, the ops builder writes that window's row as DOWN; a stopped stream is never just a missing row.
2
The checkpoint plays the file log's role. The stream's checkpoint and Kinesis' sequence numbers decide what each run reads. input_watermark records the newest event time per press in silver, which is what freshness and completeness use.
3
Outages are bounded. Kinesis keeps 7 days, so a stream whose runs fail for less than that loses nothing; the next run that succeeds reads from its checkpoint.
4
Streams feed gold; they do not write it. At 15 minutes past each hour the stream job builds the hourly sensor aggregates for the closed hour (an input to gold, kept in silver), re-checks the last 2 days for late readings, moves the per-press bookmarks, and calls the window-recompute job, which alone writes the production fact.
5
Silent machines, but not alarm storms. A per-press check (no reading for 30 minutes in a planned shift) catches one quiet press. While the whole stream is down, the per-press check is held back, and one stream alert fires instead of hundreds.

When something breaks

Each failure has one place it is caught

For each kind of failure: where it is caught, what the platform does by itself, and the one thing a person needs to do. Only passing errors are retried; the lander ends data problems itself, as handled failures, so a retry never hides a bad file.

What goes wrongCaught byWhat happens by itselfWhat a person does
Source down or slowMover: DMS task state-change events and AppFlow failure events (EventBridge); a lag alarm that treats missing data as breachingAWS retries; the lander finds nothing new and ends as skipped; the source's bookmark stops movingFreshness goes red from the bookmark at the promised time; incident for the source owner
DMS stops for hours, then resumesAs above, and the bookmarkDMS catches up from the source's change log (kept 72 hours); the lander loads the backlog over a few runs; gold hours stay incomplete until the bookmark passes them, then fill in as restatementsNothing, unless the outage outlasts the change log: then DMS reloads the table (full load, then change capture)
A partner file is unreadable or short, or a pull returns fewer rows than the source countLander or puller, the same checkThe file is moved to landing/_rejected (a pulled batch is not appended) and file_log says why; the dataset's run ends FAILED as handled; other datasets carry on; master run PARTIALThe partner or source re-sends; the next run loads it
A DMS change file is unreadableLander, file checkThe dataset is BLOCKED at that file: no later change files are loaded, because skipping one would lose changes or apply them out of order. Other datasets carry on; the master run is PARTIAL; the 'dataset blocked' alert fires; its bookmark stops, so gold shows the hours as incompleteRepair the file, or have DMS reload the table (full load, then change capture); then the dataset unblocks
A source changes its columnsLander and silver, contract checkSee 'When a source changes shape' belowUpdate the contract; replay
Bad rowsSilver, quality rulesRows parked in quarantine with the reason; the rest carry onSteward reviews weekly; fixed rows are released and re-merged
Too many bad rows in one batchThe wrapper, after the update, from the quality resultsRules run row by row, so the batch's good rows are already applied and its bad rows are in quarantine; silver stays correct, only incomplete. Past quarantine_max_pct the dataset is BLOCKED: no further batches are applied until the steward releases itFind out why the source sent so much bad data; release the dataset
A full copy comes back shortLander, mass-delete guardNothing is appended to bronze; the files are held and the dataset BLOCKED; 'mass delete stopped' alertCheck the source; re-pull; then release or reject the held files
A partner re-sends a corrected fileLander and silverLands as a new file with a unique name; silver replaces the slice the contract names (dealer and month) with the newest file; gold rebuilds the affected windows, of any age, as restatementsNothing
Job or cluster dies halfwayLakeflow task failureRetried up to 2 times (only passing errors reach Lakeflow); the batch's transaction id stops a second appendNothing, unless retries run out
Lakebase is slow or downRun recorder timeouts; job timeouts and duration health rulesJobs fail fast at their first task instead of hanging; stuck runs are finished by the sweeperPlatform on-call, alerted by Databricks job alerts and the heartbeat alert, neither of which needs Lakebase
A bug in our codeAny stageFails; cause class 'our bug'; no retryFix, release, then re-run the failed runs

The alerts this runtime raises

Every alert has an owner from the metadata and goes to one incident per cause; the dashboard and paging rules are on the platform page.

AlertFires whenSeverityGoes to
Dataset blockedA DMS file failed its check, a full copy hit the mass-delete guard, or a personal-data source changed shape (the schema-changed alert goes to the source owner too)High for tier-1 inputs, Medium otherwiseThe source's engineer; repeats once a day while blocked, as one incident
Master run PARTIALSome datasets of a trigger failedMediumThe source's engineer
Freshness missedA dataset's bookmark is behind its targetFrom the data product's tierData product owner and on-call
Schema changedA contract check stopped a tableMediumThe source owner named in the contract
Mass delete stoppedThe guard stopped a full copyHighThe source's engineer
Stream downA scheduled streaming run failed or did not run, or windows marked DOWNHighOn-call; per-press alerts held back meanwhile
Press silentNo reading from one press for 30 minutes in a planned shiftHigh (OEE is tier 1)Plant maintenance and on-call
Heartbeat missingNo heartbeat row for 30 minutes (a Databricks SQL alert, independent of Lakebase)HighPlatform on-call
Job slow or failedDatabricks job health rules and failure notificationsHighPlatform on-call
Runs sweptThe sweeper closed stuck runsLowPlatform team, next working day
New column seenAn added column kept as rescued dataLowThe steward

Re-runs, backfills and first loads

Five ways to run again, none in the way of the normal runs

KindWhenHow
Re-run failedOnly the datasets that failed in a master runA queued run of the same lander job with run type rerun, linked to the failed master run. The file log means only what was not loaded is read. It writes new run rows; the earlier FAILED rows stay as history
Replay from bronzeRebuild one silver table after a logic or contract fixA full refresh of that one table in its pipeline; bronze is the source of truth, so nothing is fetched from the source again. Bronze is kept 3 years in standard storage (then archived, still readable), and retention jobs never delete from it inside that time
BackfillRebuild past gold windows on purpose, for example after a correctionA plan in Git (fact, windows, reason, cost). The window-recompute job, the only writer of that fact, works through it one chunk per run between normal updates. It never moves a run watermark or an input bookmark. Every rebuilt window is a restatement. Materialized views never read corrections, so they need no restating
First load of a new datasetA new table, or a new sourceFor DMS tables, DMS's own full load then change capture, loaded by the normal lander (the full-load rows sort before that load's own changes). For other sources, the bulk job in segments, writing to the same file log; the lander job then takes only files the bulk job did not, so there is no gap and no double load
Re-extractFetch from the source againOnly for sources that can resend. Never for SAP, which moves its own delta token: replay from landing or bronze. For DMS: DMS reloads the table, and the normal lander loads it

Adding a dataset to a running source

1
YAML, reviewed. The dataset's settings, contract, rules and owners in one pull request.
2
Source side, generated. For DMS: the DBA enables CDC on the table (a checklist item), and the generator adds the table to its DMS task group; only that group pauses. For AppFlow or the puller: a new flow or pull.
3
Tables first. The deploy creates the empty bronze table from the contract and registers the dataset, and creates the source's marker table if it is new, before any trigger refers to them.
4
First load. A DMS table is fully loaded by DMS itself, then change capture follows; the normal lander loads both, full-load rows first. Other sources use the bulk job in segments, recording files in the same file log; from the next run the lander job takes only the files the bulk job did not.

Changing settings

1
Every run records its settings. Lander runs store the merged settings in config_snapshot by their fingerprint. A pipeline is generated from settings too, and carries their fingerprint as a pipeline parameter; the wrapper stores it on the update's run row. Any run can say exactly which settings it used.
2
A settings change is a release. Settings go through the same release path as code. Changes to quality limits, watermarks, replacement rules and grants always take the full gates; other settings take the lighter path.
3
A change applies from the next run. A run in progress keeps the settings it started with; the next run picks up the new ones, and the logs show which run used which.

Observability: the working parts

What has to exist for anyone to see what ran

The dashboard, alerts and incidents are described on the platform page. These are the parts underneath, and where each stands today. Two of them (job health rules and the heartbeat alert) deliberately do not depend on our own log database, so they still work when it does not.

PartDoesStatus
Run recorderEvery part logs the same way, through six calls, with timeoutsBuilt in the framework (Azure SQL today); the Lakebase adapter and timeouts are to do
File logEvery landing file: checked, loaded or rejected, by which runTo build
Input watermarksHow far each input is loaded, in source time: completeness and freshnessTo build
Ingest columnsRows say which run and file wrote them, and their order in the sourcePartly built: bronze columns exist; DMS and SAP order columns to add
Pipeline wrapperMakes a Declarative Pipeline update look like any other run, and starts goldTo build
Ops builder and sweeperprod_ops every 15 minutes; finishes stuck runsTo build; the health views exist in the framework for Azure
Health viewsOne row per dataset: last run, status, bookmark against target, rows against the usual band, failing rulesBuilt for Azure SQL (v_dataset_health); to port
HeartbeatA synthetic dataset through every layer every 15 minutes, watched by a Databricks SQL alert that does not need LakebaseDesigned in the framework (PL_HEARTBEAT); to build on Databricks
Job health rulesDuration and failure alerts from Databricks itself, for when our own logging is the problemTo generate with the jobs
DoctorA healthcheck before every deploy and on demand: database, secrets, buckets, grants, triggers, DMS tasksBuilt in the framework (lake-ingest doctor); AWS probes to add
-- prod_ops.v_dataset_health: one row per dataset, the first thing on-call opens
SELECT d.dataset_name, d.tier, d.owner,
       r.status                          AS last_status,      -- incl. BLOCKED and DOWN
       w.as_of                           AS loaded_up_to,     -- the bookmark, in source time
       d.freshness_target_minutes,
       timestampdiff(MINUTE, w.as_of, current_timestamp()) AS minutes_behind,
       r.rows_written, b.rows_low, b.rows_high,              -- the usual band for this weekday
       q.rules_failed, q.rows_quarantined,
       f.files_rejected_today,
       c.cost_today_usd
FROM ops.dim_dataset d
LEFT JOIN ops.last_run r          USING (dataset_id)
LEFT JOIN ops.input_watermark w   USING (dataset_id)
LEFT JOIN ops.volume_band b       USING (dataset_id)
LEFT JOIN ops.quality_today q     USING (dataset_id)
LEFT JOIN ops.files_today f       USING (dataset_id)
LEFT JOIN ops.cost_today c        USING (dataset_id);

The health view on-call opens first: one row per dataset, judged by its bookmark, not by its last run.

A day in the life

Twenty-four hours of the runtime

What runs when on an ordinary day, for the plants' morning meeting at 06:30.

WhenWhat runs
Every 15 minPress and mixer sensor readings: the streaming pipeline runs once, takes what arrived in Kinesis into bronze and silver, and stops; MES changes land and load; the manufacturing pipeline follows on the marker; gold windows recompute; the ops builder refreshes bookmarks and freshness and sweeps; the heartbeat passes through
Hourly, at :15The stream job builds the closed hour's sensor aggregates and calls the window recompute
HourlyThe personal-data purge deletes landing files loaded more than 23 hours ago
Every 30 min, 06:00-22:00SAP orders and deliveries: AppFlow runs, then its lander
01:00Nightly SAP full copies for objects without deletes in their delta; the sales pipeline follows
OvernightDealer sell-out files arrive; each starts its lander once copied to landing
03:00Housekeeping: retention, erasures, VACUUM, the daily DMS reconcile
06:00The night shift ends (shifts: 06:00, 14:00, 22:00, plant time)
06:15The 05:00-06:00 sensor hour closes; the window recompute writes the production fact and sets completeness for the night shift
06:20Ops builder: tier-1 data products green, amber or red from their bookmarks; red pages on-call
06:30OEE for the morning meeting, with completeness shown. Readings that arrive later restate the hour and say so
09:00Warranty claims ready (tier 2)

How this design is tested

Twenty-two situations, each with checks it must pass

The design is tested on paper before anything is built. Each situation is a row in a table (_guides/etl_scenarios.py): what is true before, what happens, and a list of checks written as facts the platform must produce: which tables change, which log rows appear, whether a bookmark moves, which alert fires. A reviewer who did not write the page marks every check as stated (the page says it, with a quote), gap (the page is silent) or contradicted. A situation passes only when every check is stated. The same rows become DataTwin faults and then integration tests on Databricks, so the test is written once.

S05  One of twelve MES datasets has an unreadable file
GIVEN  Twelve MES datasets due; one has a truncated Parquet change file
WHEN   The lander run reaches that file
THEN
  S05.1  The file check catches it before the file is read
  S05.2  Because it is a DMS change file, that dataset is BLOCKED; no later files of that dataset are loaded
  S05.3  The other eleven datasets load and succeed
  S05.4  The master run is PARTIAL; the job run itself ends green (the failure was handled)
  S05.5  The failure is not retried by Lakeflow (it is not a passing error)
  S05.6  The blocked dataset's bookmark stops, so gold hours that need it are marked incomplete
  S05.7  An alert fires for the blocked dataset, from our logs
  S05.8  The fix path is to repair the file or have DMS reload the table (a new generation that sorts after earlier changes), after which the dataset unblocks

One row of the test table. Each THEN line is checked against this page and marked stated, gap or contradicted.

The situations and their latest result

SituationGivenWhenChecksResult
S01 Normal 15 minutes of MES changes reach goldDMS running for MES; 3 MES datasets with new change files in landing; all bookmarks currentThe 00:15 schedule fires1312 of 13 stated, 1 contradicted
S02 An SAP sales order line is deletedAppFlow pulls the sales order delta every 30 minutes; the line exists in silverThe line is deleted in SAP and the next AppFlow run carries the delta54 of 5 stated, 1 gap
S03 DMS stops for 2 hours, then catches upMES source changes all through the night shiftThe DMS task stops at 01:00 and resumes at 03:008Passes
S04 A schedule fires while the previous lander run is still goingThe 00:15 MES run is still loading at 00:30The 00:30 schedule fires3Passes
S05 One of twelve MES datasets has an unreadable fileTwelve MES datasets due; one has a truncated Parquet change fileThe lander run reaches that file8Passes
S06 A partner file is unreadableA dealer uploads a sell-out CSV that cannot be parsedFile arrival starts the dealer lander3Passes
S07 A partner re-sends a corrected fileDealer D's October sell-out file was loaded yesterdayThe dealer uploads a corrected October file with the same name4Passes
S08 The press master full copy comes back shortPress master has 2,000 presses; the puller pulls a full copy dailyToday's pull returns 300 rows, with a manifest that says 3004Passes
S09 A press is really decommissionedPress P has three years of factsPress P disappears from the daily full copy (one row out of 2,000)3Passes
S10 The sensor stream is down for 40 minutesPress sensors stream through Kinesis; a trigger-once pipeline picks them up every 15 minutesThe streaming pipeline stops at 02:00 and restarts at 02:405Passes
S11 One press goes silent while the others stream2,000 presses streaming; press Q in a planned shiftPress Q sends nothing for 30 minutes3Passes
S12 Three-month backfill after an ideal-cycle-time correctionIdeal cycle time for material M was wrong for 3 months; normal runs continue every 15 minutesThe owner submits the correction with an applies-from date5Passes
S13 Lakebase (the log database) is slowLakebase answers in 60 seconds instead of millisecondsThe next lander run starts4Passes
S14 A run commits to bronze but cannot close its log rowA lander run has committed rows to bronzeThe Lakebase write in close_run fails4Passes
S15 The cluster dies in the middle of a loadA lander task is appending a micro-batch to bronzeThe cluster is lost3Passes
S16 SAP renames a columnThe delivery extract has a column renamedThe next delta lands4Passes
S17 A source adds a new columnThe warranty system adds a free-text columnThe next load lands3Passes
S18 A new dataset is added to a running sourceMES is loading normallyA YAML change adds a 13th MES table5Passes
S19 Settings change between two runsA quality limit is changed in YAML and deployed at 10:07Runs at 10:00 and 10:15 happen3Passes
S20 Re-run the datasets that failedA master run ended PARTIAL with two FAILED datasets, now fixed at the sourceAn engineer starts a re-run3Passes
S21 Personal data arrives in a warranty claimThe warranty source sends a claim with the customer's name, phone and a note naming themThe lander loads it4Passes
S22 Nothing at all arrives for an hourA quiet night; no source sends anythingAn hour passes3Passes
S23 A 100-column source table, 30 columns wanted in silverAn MES table with 100 columns; the dataset lists 30 of them, 4 renamedA normal load runs, and next month the source adds a 101st column5Passes
S24 Comma decimals and day-first dates from an EU plantAn EU plant sends '12,5' for a cure temperature and '31.10.2026' for a date; one row has 'abc' as the temperatureThe load runs4Passes
S25 The source renames a listed columnA required listed column 'press_no' is renamed to 'press_number' at the sourceThe next load runs4Passes
S26 A source type is widened, another is changedSilver types: counter BIGINT, batch_code STRING, weight DECIMAL(10,2)The source widens an INT to BIGINT, and changes weight from a number to text2Passes
S27 History by kind of dataA material master (master data), sales order lines (transactions), press events (events) and daily stock (snapshot)A material's compound changes, an order line's quantity changes, a press event arrives twice, and a new stock snapshot arrives5Passes
S28 A JSON API payload with nested order linesThe dealer API returns orders as JSON with a nested dealer object and an array of order linesA response lands, and later the API adds a new field and drops an optional one5Passes
S29 Deploy the platform to a new environmentAn empty pre-prod environment; platform settings for preprod in GitThe deploy and the first lander run happen62 of 6 stated, 4 gaps
S30 Our puller reads a database table and a REST APIAn append-only SQL Server table pulled by watermark column, and a REST API with a rate limit; one table is above the large-table thresholdThe puller runs63 of 6 stated, 2 gaps, 1 contradicted
S31 Values are standardised by column settingsA row with status 'open ', force '450' in lbf, plant code 'IN-CH', SAP material 'M-4711' (mapped) and 'M-9999' (not mapped), an empty colour, and a reading timeSilver runs7Passes
S32 Test records and a bad-share limitA dataset with row_filter dropping rows where is_test = 'Y', and quarantine_max_pct = 2A load has 1,000 rows: 10 test rows and 30 rows failing a quarantine rule3Passes
S33 Volume falls and cost risesA dataset with volume_band = auto and budget_daily_usd = 20A Tuesday load writes 20% of the usual Tuesday rows, and the dataset's cost that day reaches 35 USD32 of 3 stated, 1 gap
S34 Profiling suggests a ruleprofile.enabled on, profile.when daily, profile.sample_pct 10, profile.suggest_rules onA column's empty share jumps from 0.1% to 8%3Passes
S35 A wide reference table taken whole, with a list columnA reference table where silver.columns = all_except_excluded, two columns marked is_excluded, and a tags column holding a listSilver runs32 of 3 stated, 1 contradicted
S36 Two sources of one domain finish close togetherThe manufacturing domain watches the MES and press-master markers; min_gap_minutes = 15MES finishes at 00:15:45 and the press master at 00:18:0031 of 3 stated, 2 gaps

What each situation touches

A dot means the situation exercises that part. A column with few dots is a part the test barely covers.

MoverRun controllerLanderSilverIntegratorGoldMetric layerRun recorderOps builderHeartbeat
S01●●●●●●●●
S02●●●●●
S03●●●●●
S04●●●
S05●●●●●
S06●●
S07●●●
S08●●
S09●●
S10●●●
S11●●
S12●●●
S13●●●●
S14●●
S15●●
S16●●
S17●●
S18●●●
S19●●
S20●●●
S21●●●
S22●●
S23●●
S24●
S25●●
S26●
S27●
S28●●
S29●●●
S30●●●
S31●
S32●●
S33●
S34●●
S35●
S36●●
Total61320211809122

Which kinds of trouble each situation covers

Normal daySource downBad fileSchema changeRe-sent dataLate or out of orderDeletesCrashSlow dependencyOverlapping runsCorrectionsNew or changed configPersonal dataStreamingBad values
S01●
S02●●
S03●●
S04●
S05●
S06●
S07●●
S08●●
S09●
S10●●
S11●●
S12●
S13●
S14●
S15●
S16●
S17●●
S18●
S19●
S20●●
S21●
S22●●
S23●●
S24●
S25●
S26●
S27●●
S28●●
S29●
S30●●
S31●●
S32●
S33●
S34●
S35●
S36●
Total754613333223224

Run by run

Each run checks the same rows against the page as it stood. Between runs the design was fixed, not the wording polished: a check that failed because a mechanism would not work changed the mechanism. Where a fix deliberately changed the expected behaviour, the row was updated and the change recorded.

RunChecks statedGapsContradictedSituations passingWould not work as writtenPage disagrees with itself
Run 1 (2026-11-03)80 of 1002009 of 227not checked
Run 2 (2026-11-03)97 of 1002119 of 2278
Run 3 (2026-11-03)95 of 1001418 of 22310
Run 4 (2026-11-03)98 of 1001120 of 2227
Run 5 (2026-11-03)146 of 15910329 of 36310

Before the table existed, a free-form walk-through of twelve situations found 3 failures and 9 with gaps, the biggest being that gold could call an hour complete while an input was missing. That led to the rewrite this table now checks.

Questions

Questions engineers ask

Why not one big job from source to gold?

Because sources arrive at different times. A job per source loads what has arrived; pipelines per domain run when a source has finished a batch. Nothing waits for the slowest source, and completeness tells everyone what has not arrived yet.

Why a batch-ready marker instead of triggering on bronze tables?

Each dataset commits to bronze at a slightly different moment. A trigger on the tables fires on the first one and runs silver on a half-loaded source. The marker is written once, after the whole batch, so silver always sees a finished batch.

Why does a Declarative Pipeline need a wrapper job?

The pipeline has its own event log and ids. The wrapper starts each update itself through the API, so it knows the update id, opens and closes our run row around it, copies counts and expectation results across, and then starts gold, so on-call sees one kind of run everywhere and gold never starts in the middle of an update.

Why not let Lakeflow retry every failure?

Lakeflow retries whatever fails. A bad file retried is still a bad file, and a retry after the file was moved away looks like success. So the lander ends data problems itself, as handled failures in our log, and only passing errors (a network blip, a busy cluster) reach Lakeflow's retry.

What if the logging database is down or slow?

Every call to it has a timeout, so jobs fail at their first task instead of hanging. Databricks' own job alerts, duration rules and the heartbeat alert do not need our database, so the problem is still seen.

Next

Where to go next

↑ The platform on AWS + Databricks · Ingestion · Bronze to silver · Gold · Metadata store · Test architecture