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.
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.
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 source | Examples here | Brought in by | What changes arrive | Lands as | How often |
|---|---|---|---|---|---|
| Databases that change all day | MES (SQL Server), quality lab LIMS (Oracle), warranty system, plant maintenance | AWS DMS: full load once, then change data capture (CDC) | Every insert, update and delete, with an operation flag, commit time and change sequence | Parquet files, written continuously | Loaded every 15 minutes |
| Small or reference database tables | Press master, shift calendars, code lists; any database where CDC logging is not allowed | Our puller: the framework's built connectors (SQL Server, Oracle, PostgreSQL, MySQL) as a Databricks job over JDBC | A full copy (needed for any table that can delete rows), or rows above a watermark for append-only tables | Parquet plus a manifest | On a schedule, often daily |
| SAP S/4HANA | Sales orders, deliveries, stock, procurement, finance | Amazon AppFlow, SAP OData connector, reading released CDS views and extractors with delta | Only what changed since the last call, with SAP's change mode (new, changed, deleted) where the extractor gives it | Parquet plus a manifest written when the flow finishes | Every 30 minutes in plant hours; a nightly full copy for objects whose delta has no deletes |
| SaaS applications with an AppFlow connector | CRM (for example Salesforce), IT service desk | Amazon AppFlow | Rows changed since the last run | Parquet plus a manifest written when the flow finishes | Hourly |
| APIs without an AppFlow connector | Dealer management system API, natural-rubber price feed, weather | Our puller: the framework's built REST connector | Pages since the last cursor or date | JSON lines plus a manifest | On a schedule |
| Files from partners | Dealer sell-out, supplier certificates of analysis, logistics partners | AWS Transfer Family (SFTP), one login and folder per partner | New files; a correction is a new file | As received (CSV, Excel, XML), renamed on arrival to a unique key | On arrival |
| Machines and sensors | Curing presses, mixers, live historian tags | AWS IoT SiteWise Edge or IoT Core at the plant, into Amazon Kinesis Data Streams (7 days kept) | Events, one per reading | No files: the Kinesis stream is the landing | Every 15 minutes, trigger-once (readings are sent all the time and wait in Kinesis) |
| Historian history | Years of past press and mixer data, once | The historian's own export to S3 through Transfer Family (check current status for the historian's tools) | One-off backfill | Parquet or CSV | Once, through the bulk job |
| Spreadsheets people keep | Ideal cycle times not in MES, targets, mappings | YAML in Git first; otherwise an upload to a Unity Catalog volume by the named owner | A new version of the file; a correction says from which date it applies | As uploaded | On upload |
How to pick, in order
For a new source, ask these questions in order and stop at the first yes.
The landing contract: what every mover must deliver
How the lander reads each kind of landing
| Landing | Lander reads it with | Remembers 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 read | The 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 files | As files, read in commit order, keeping operation flag, commit time and change sequence as columns; silver applies them with AUTO CDC | As above; a bad file blocks the dataset (see failures) |
| Kinesis streams | A streaming table reading Kinesis directly, run trigger-once every 15 minutes: each run takes what has arrived since its checkpoint and stops | Stream checkpoint and Kinesis sequence numbers |
Not in the first version, and why
| Not in v1 | Why |
|---|---|
| Lakeflow Connect | Its 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 directly | Against 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 movers | Glue is a transformation engine; using it to copy adds a second engine to run and pay for. |
| Fivetran, Airbyte and similar | Not 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 images | PDF test reports and defect photos need a document pipeline; a later phase. |
| Email attachments | Partners move to SFTP instead; email is not a reliable or auditable way in. |
Where each mover stands
| Mover | Set up how | Status |
|---|---|---|
| AWS DMS | Tasks and table mappings generated from source_config into Terraform; one task per group of tables, so adding a table stops only its group | To build (generator); DMS itself is a service |
| Amazon AppFlow | Flows generated from source_config, one per object; an EventBridge rule per flow writes the manifest and starts the lander | To build (generator) |
| AWS Transfer Family | One server; a login, folder and rename workflow per partner, from source_config | To build (generator) |
| Our puller | The 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 job | Connectors built; the Databricks job and the manifest to build |
| SiteWise / IoT Core and Kinesis | Edge set up per plant with the plant's OT team; streams from Terraform | To 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.
The thirteen stages, in order
The status column is where each stands in the framework today.
| Stage | What it does | Set by | Status |
|---|---|---|---|
| 1 Contract check | On the bronze columns: every required column present, types compatible (see schema changes below) | required, data_type, contract version | To build |
| 2 Column projection, extract and rename | All 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 table | silver.columns, silver_column_name, source_path, explode_path | Rename, exclude and flatten built; 'listed only' to build |
| 3 Hygiene | Trim, collapse spaces, empty text to NULL, strip control characters and byte-order marks, normalise unicode and line endings | On by default for text; transforms, skip lists | Built |
| 4 Parse and type | Convert to the silver type using the column's formats: dates, timestamps, comma or point decimals, epoch numbers, booleans; widen integers; one decimal scale | silver_data_type, date_format, timestamp_format, decimal_separator, on_cast_failure | Built; formats and the failure action to add |
| 5 Time | Convert to UTC from the source's time zone; add the plant's local time beside it | source_timezone, local_time_zone_from | UTC built; source zone and local column to add |
| 6 Standardise values | Upper-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 values | column_role, transforms, unit_from / unit_to, code_map, null_replacement | Written but not switched on in the engine; units and code mapping to build |
| 7 Personal data | Tokenise, hash, mask or redact by the column's personal-data class | pii_class, pii_policy | Built |
| 8 Row filter | Drop rows that match a declared condition (test records, a cancelled flag); dropped rows are counted | row_filter | To build |
| 9 Quality rules | Rules from dq_rule; failing rows to quarantine with the reason | dq_rule | Built (SQL rules); DQX behind the same port |
| 10 Duplicates | None, exact, first or latest per business key, or flag only | dedup.mode, dedup.business_keys, dedup.order_by | Built |
| 11 Keys | Business key; conformed key from a mapping table (an unmapped code becomes 'unknown' and raises an alert) | is_primary_key, key_map | To build |
| 12 History and deletes | Append, SCD type 1, SCD type 2 or snapshot; soft deletes; changes in source order | dataset_kind, history, history_track_columns | Designed |
| 13 Provenance | Run id, load time, record hash, source file, batch | None: always on | Built |
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.
| Setting | Values | Default | Stage |
|---|---|---|---|
| silver.columns (dataset) | all_except_excluded, declared | all_except_excluded | 2 |
| silver_column_name | a name | the source name | 2 |
| source_path | a path into a JSON payload, such as $.order.id | none (a plain column) | 2 |
| explode_path, parent_keys (dataset) | the array of child records; the keys carried down | none | 2 |
| array_strategy | keep, join_as_text, take_first | keep | 2 |
| required | true, false | true for keys, false otherwise | 1 |
| silver_data_type | a Spark SQL type | the bronze type | 4 |
| date_format, timestamp_format | a pattern such as dd.MM.yyyy | ISO 8601 | 4 |
| decimal_separator | point, comma | point | 4 |
| on_cast_failure | quarantine, null_and_count, stop | quarantine | 4 |
| source_timezone | an IANA zone such as Europe/Budapest | UTC | 5 |
| local_time_zone_from | the column holding the plant | none | 5 |
| column_role | code, identifier, measure, text | none | 6 |
| transforms | a list of opt-in transforms | none | 3, 6 |
| unit_from, unit_to | units such as lbf and N, F and C | none | 6 |
| code_map | the name of a mapping table | none | 6 |
| null_replacement | a value | none | 6 |
| pii_class | none, name, email, phone, free_text and others | none | 7 |
| row_filter (dataset) | a declared condition | none | 8 |
| is_primary_key, key_map | true or false; a mapping table | false; none | 11 |
| dataset_kind (dataset) | master, transaction, event, snapshot | required | 12 |
| history (dataset) | append, scd1, scd2, snapshot | from dataset_kind | 12 |
| history_track_columns (dataset) | a list of columns | all listed columns | 12 |
JSON payloads: flattening happens here, not on the way in
# 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_failure | What happens | Use for |
|---|---|---|
| quarantine | The row goes to quarantine with the column, the value's problem and the rule; the rest of the table carries on | Keys, measures, dates: anything a number depends on (the default) |
| null_and_count | The value is set empty and the failures are counted in the quality results | Descriptive text the business can live without |
| stop | The table stops | Rare: 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 change | Bronze | Silver | Alert |
|---|---|---|---|
| A new column | Kept (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 renamed | Kept under the new name | Stops (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) | Kept | Accepted automatically when the silver type can hold it | None |
| A type is narrowed or changed (text to number, number to text) | Kept | Stops | 'Schema changed' |
| We add a column to silver | - | Filled from the next run; older rows refilled from bronze by a replay | Release note |
| We remove a column from silver | - | Never at once: it stops being filled for one contract version, then is removed with notice | Release 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 data | Examples | History | Why |
|---|---|---|---|
| Master and reference data | Press, material, dealer, customer, plant, defect codes | SCD type 2 | Facts 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 status | Sales order lines, deliveries, warranty claims, work orders | SCD type 1; type 2 where the business needs the past state | Usually 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 readings | Press events, sensor readings, downtime, test results | Append | They never change; duplicates removed by event id |
| Periodic snapshots | Daily stock, open orders at month end | Snapshot: append with the snapshot date | Each day is its own fact, not a version |
Decisions
| # | Decision | Status |
|---|---|---|
| D1 | Silver 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 default | Agreed |
| D2 | A failed conversion never silently becomes empty; the default action is quarantine | Agreed |
| D3 | The schema change rules above, with required per column and minor or major contract versions | Agreed |
| D4 | No free-form SQL from bronze to silver; only the standard transforms | Agreed |
| D5 | The thirteen stages, in this order | Agreed |
| D7 | JSON stays whole in bronze; silver extracts only listed paths; arrays of child records become their own silver table | Agreed |
| D6 | History by dataset kind: type 2 for master data, type 1 for transactions (type 2 where needed), append for events, snapshot for snapshots; tracked columns only | Agreed |
| D8 | One 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 functions | Agreed |
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).
| Level | Exists | Adapt | New | Retire | Rows |
|---|---|---|---|---|---|
| Platform (per environment) | 5 | 4 | 16 | 3 | 28 |
| Source | 3 | 3 | 13 | 1 | 20 |
| Dataset | 4 | 3 | 19 | 4 | 30 |
| Column | 10 | 1 | 10 | 1 | 22 |
| Gold fact | 0 | 0 | 8 | 0 | 8 |
| Domain | 0 | 0 | 4 | 0 | 4 |
| Total | 22 | 11 | 70 | 9 | 112 |
Platform (per environment) (28 rows)
| Setting | Values | Default | Read by | Tested in | Status | Note |
|---|---|---|---|---|---|---|
| runtime_environment | dev, preprod, prod | prod | recorder, deploy | S29 | adapt | 'staging' becomes 'preprod' |
| realm | aws-databricks, adf-adls | aws-databricks | deploy | S29 | adapt | was default_realm; chooses which generators the deploy runs |
| aws_region | AWS region code | ap-south-1 | deploy, mover | S29 | new | |
| catalog_prefix | text | the environment name plus _ | lander, silver, gold, deploy | S23 | new | dev_, preprod_, prod_ in front of bronze, silver, gold, ops |
| landing_bucket | S3 bucket | landing-<env> | mover, lander | S01 | new | replaces target_storage_account |
| landing_pii_bucket | S3 bucket | landing-pii-<env> | mover, lander | S21 | new | |
| secrets_backend | aws_secrets_manager | aws_secrets_manager | mover, lander | S29 | new | replaces key_vault_uri |
| secret_prefix | text | dp/<env>/ | mover, lander | S29 | new | |
| metadata_db_endpoint | Lakebase (Databricks managed PostgreSQL) instance and database | none | recorder, controller | S13 | adapt | was metadata_db_server (Azure SQL) |
| recorder_timeout_seconds | integer | 10 | recorder | S13 | new | |
| recorder_retries | integer | 2 | recorder | S13 | new | |
| job_timeout_minutes | integer | 30 | controller | S13, S14 | new | every generated job; the sweeper uses it too |
| metadata_schema_version | integer | none | recorder, deploy | S29 | exists | |
| default_file_format | DELTA | DELTA | lander | S29 | exists | |
| default_concurrency | integer 1-50 | 8 | controller | S01, S05 | adapt | was default_batch_count (ADF ForEach); now the for-each concurrency |
| redaction_extra_patterns | list of patterns | none | recorder | S21 | exists | |
| large_table_row_threshold, huge_table_row_threshold | integers | 10 million, 1 billion | controller | S30 | exists | decides segmenting in the bulk job |
| auto_retry_on_partial, auto_retry_max_attempts, auto_retry_delay_minutes | true or false; integers | false, 2, 30 | controller | S20 | exists | |
| tier_targets | freshness and paging per tier | tier 1: page any hour; tier 2: plant hours; tier 3: working hours | ops | S03, S11 | new | |
| mass_delete_limit_pct | percent | 5 | lander | S08 | new | datasets may override |
| held_file_days | integer | 7 | lander, ops | S17 | new | |
| pii_landing_hours | integer | 23 | ops | S21 | new | the hourly purge |
| stream_late_allowance_minutes | integer | 120 | gold | S10 | new | |
| ops_interval_minutes | integer | 15 | ops | S01, S14 | new | |
| heartbeat_interval_minutes | integer | 15 | heartbeat, ops | S22 | new | |
| key_vault_uri, target_storage_account, target_container_default, shir_name, staging_linked_service | - | - | - | - | retire | Azure only |
| flatten_function_url, flatten_function_secret_name, flatten_databricks_* | - | - | - | - | retire | flattening moved into silver (stage 2) |
| lifecycle_cool_days, lifecycle_archive_days | - | - | - | - | retire | storage tiers set by Terraform and S3 lifecycle rules |
Source (20 rows)
| Setting | Values | Default | Read by | Tested in | Status | Note |
|---|---|---|---|---|---|---|
| source_type | sql_server, oracle, postgres, mysql, sap, saas, rest_api, partner_files, stream, upload | none | controller, mover | S01, S02 | adapt | typed on source_master; adds sap, partner_files, stream, upload |
| mover | dms, appflow, transfer_family, kinesis, puller, upload | none | mover, controller, deploy | S01, S02, S06, S10 | new | |
| clock | schedule, appflow_finished, upload_copied | set by the mover | controller | S01, S02, S04 | new | one clock per source; streams use schedule (every 15 minutes, trigger-once), never a continuous run |
| schedule_cron, schedule_timezone | cron; time zone | every 15 minutes; UTC | controller | S01, S04 | adapt | moves from dataset to source: the lander job is per source |
| auth_method, connection_secret_name | as today; the secret is a Secrets Manager name | none | mover | S30 | adapt | kv:// references become Secrets Manager names |
| connect_timeout_seconds, rate_limit_rps | integers | 30; none | mover | S30 | exists | puller and REST |
| max_concurrent_datasets | integer 1-50 | default_concurrency | controller | S05 | exists | the for-each concurrency for this source |
| server, database, service_name, base_url and other connection keys | per source type, as today | none | mover | S30 | exists | used by the puller |
| integration_runtime | - | - | - | - | retire | ADF only |
| dms_task_group | text | one group per source | mover, deploy | S18 | new | adding a table pauses only its group |
| dms_min_log_retention_hours | integer | 72 | doctor | S03 | new | checked by the healthcheck, not enforced by us |
| dms_latency_limit_seconds | integer | 300 | lander, ops | S03, S22 | new | the quiet rule: bookmark is now minus latency while below this |
| appflow_connector_profile, appflow_flow_prefix | names | none | mover, deploy | S02 | new | |
| partner_login, partner_folder | names | none | mover, deploy | S06, S07 | new | |
| control_file | none, row_count_file | none | lander | S06 | new | |
| kinesis_stream, kinesis_retention_days | name; integer | none; 7 | lander, deploy | S10 | new | |
| has_personal_data | true, false | false | mover, lander | S17, S21 | new | routes landing to the personal-data bucket |
| owner, deputy | people from the identity provider | none | ops | S16, S25 | new | checked nightly against the identity provider |
| contract_ref, contract_version | path in Git; semver | none | silver | S16, S25 | new | |
| source_timezone | IANA zone | UTC | silver | S24 | new | columns may override |
Dataset (30 rows)
| Setting | Values | Default | Read by | Tested in | Status | Note |
|---|---|---|---|---|---|---|
| dataset_type, load_type, schema_name, object_name, custom_query | as today | none | mover, lander | S01, S08 | exists | |
| dataset_kind | master, transaction, event, snapshot | required | silver | S27 | new | sets the default history |
| domain | manufacturing, quality, sales, aftermarket | required | controller, silver | S01 | new | which domain pipeline builds it |
| tier | 1, 2, 3 | 3 | ops | S03, S11 | new | |
| freshness_target | minutes behind, or a time of day | from the tier | ops | S03, S22 | adapt | was sla_freshness_hours |
| expected_delivery | a time and days | none | ops | S22 | new | partner files: freshness judged against it |
| volume_band | auto, or fixed low and high | auto (from the last 8 same weekdays) | ops | S33 | new | |
| owner, deputy, steward | people | the source's | ops | S05 | new | |
| max_files_per_run, max_gb_per_run | integers | 500; 20 | lander | S18 | new | spreads big first loads over runs |
| watermark_column, watermark_initial_value | as today | none | mover | S30 | exists | puller, append-only tables only |
| change_order | dms, sap_counter, arrival, snapshot, event_time | set by the mover | silver | S01, S02 | new | the sequence AUTO CDC uses |
| delete_rule | dms_op, sap_change_mode, snapshot_compare, replace_slice, none | set by the mover | silver | S02, S09 | new | |
| replacement_scope | list of columns | none | silver | S07 | new | partner files: what a re-sent file replaces |
| mass_delete_limit_pct | percent | the platform's | lander | S08 | new | override |
| silver.columns | all_except_excluded, declared | all_except_excluded | silver | S23 | new | all by default; declared = projection to listed columns |
| row_filter | a declared condition | none | silver | S32 | new | |
| quarantine_max_pct | percent | 2 | silver | S32 | new | stop the table past this share of bad rows |
| dedup.mode, dedup.business_keys, dedup.order_by | as today | none | silver | S27 | exists | |
| history | append, scd1, scd2, snapshot | from dataset_kind | silver | S27 | new | |
| history_track_columns | list of columns | all listed columns | silver | S27 | new | |
| explode_path, parent_keys | a path; key paths | none | silver | S28 | new | child datasets from JSON arrays |
| profile.enabled, profile.when, profile.scope, profile.sample_pct, profile.columns, profile.suggest_rules | see the platform page | off, daily, new_rows, 10, all, true | silver, ops | S34 | new | |
| file_format, file_pattern, file_encoding, csv_*, excel_*, xml_record_xpath | as today | as today | lander | S06, S24 | adapt | read by the lander instead of ADF |
| load_profile, segmentation_*, segment_count | as today | auto | controller | S18 | exists | the bulk job |
| budget_daily_usd | number | none | ops | S33 | new | judged against the estimated cost split per dataset |
| realm | aws-databricks | the platform's | deploy | S29 | adapt | new realm name |
| target_container, target_path_template | - | - | - | - | retire | the landing layout is fixed by the landing contract |
| intended_dius, parallel_copies, enable_staging, sink_max_concurrent_connections, partition_* | - | - | - | - | retire | ADF copy settings |
| flattening_mode, flattening_engine, flattening_path_separator, primary_array_path, array_max_explode_rows | - | - | - | - | retire | replaced by source_path, explode_path and array_strategy in silver |
| lifecycle_cool_days, lifecycle_archive_days, lifecycle_delete_days | - | - | - | - | retire | retention set per layer by the platform |
Column (22 rows)
| Setting | Values | Default | Read by | Tested in | Status | Note |
|---|---|---|---|---|---|---|
| column_name, data_type, is_nullable, ordinal_position | as today | from discovery | silver | S23 | exists | |
| is_primary_key | true, false | false | silver | S27 | exists | |
| is_excluded | true, false | false | silver | S35 | exists | used with the default silver.columns = all_except_excluded |
| required | true, false | true for keys | silver | S25 | new | |
| silver_column_name | a name | the source name | silver | S23, S25 | exists | |
| source_path | a JSON path | none | silver | S28 | exists | |
| array_strategy | keep, join_as_text, take_first | keep | silver | S35 | adapt | explode moves to explode_path |
| silver_data_type | a Spark SQL type | the bronze type | silver | S24, S26 | exists | |
| date_format, timestamp_format | patterns | ISO 8601 | silver | S24 | new | |
| decimal_separator | point, comma | point | silver | S24 | new | |
| on_cast_failure | quarantine, null_and_count, stop | quarantine | silver | S24 | new | replaces cast_safety |
| cast_safety | - | - | - | - | retire | replaced by on_cast_failure |
| source_timezone | IANA zone | the source's | silver | S24 | new | |
| local_time_zone_from | a column naming the plant | none | silver | S31 | new | |
| column_role | code, identifier, measure, text | none | silver | S31 | exists | |
| transforms, skip_groups, skip_processors | lists | none | silver | S31 | exists | |
| unit_from, unit_to | units | none | silver | S31 | new | |
| code_map | a mapping table | none | silver | S31 | new | |
| key_map | a mapping table | none | silver | S31 | new | conformed keys; unmapped becomes unknown |
| null_replacement | a value | none | silver | S31 | exists | |
| pii_class | none, name, email, phone, free_text and others | none | lander | S21 | exists | |
| pii_purpose, pii_retention_days | text; integer | required when pii_class is set | lander, ops | S21 | new |
Gold fact (8 rows)
| Setting | Values | Default | Read by | Tested in | Status | Note |
|---|---|---|---|---|---|---|
| fact_name, grain | name; for example press-hour | required | gold | S01 | new | |
| window, lookback | hour or day; a duration | hour; 2 days | gold | S03, S10 | new | |
| inputs | datasets with a role: driver, dimension as of, contributor | required | gold | S01, S05 | new | |
| completeness | on, off | on | gold | S03, S05, S11 | new | |
| max_unknown_pct | percent | 1 | gold | S09 | new | |
| partition_column | a date column | the window's date | gold | S12 | new | |
| backfill_chunk_windows | integer | 24 | gold | S12 | new | |
| reads_corrections | true, false | false | gold | S12 | new |
Domain (4 rows)
| Setting | Values | Default | Read by | Tested in | Status | Note |
|---|---|---|---|---|---|---|
| domain_name | name | required | controller, deploy | S01 | new | |
| sources | list of sources | from its datasets | controller | S01 | new | the marker tables its wrapper watches |
| min_gap_minutes | integer | 15 | controller | S36 | new | |
| gold_facts | list of facts | none | controller | S01 | new | recompute 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.
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.
| Part | Does | Reads | Writes | Runs as | Safe to run twice because |
|---|---|---|---|---|---|
| 1 Mover | Brings data from a source to S3 landing (or Kinesis, for streams) | source_config | Files 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 controller | Starts the work: one clock per source, a marker for silver, the wrapper for gold | dataset_view, schedules, triggers, markers | master_run_log; the job run | Lakeflow Jobs, generated from metadata | Runs queue instead of overlapping; a run with nothing due ends as skipped |
| 3 Lander | Landing to bronze: picks new files from the file log, checks them, tokenises personal data, adds ingest columns, appends; writes the batch-ready marker | dataset_view, the file log, the suppression list | Bronze tables, file_log, run_log and step_log rows, the marker | Our PySpark job, one task per dataset | The file log says what was read; each batch is appended with a transaction id, so it lands once |
| 4 Silver builder | Bronze to silver: the thirteen standard stages (contract, select, clean, type, time, standardise, personal data, filter, quality, duplicates, keys, history, provenance), replacement rules | Bronze, dq_rule, dataset_config | Silver tables, quarantine tables, the pipeline event log | A Declarative Pipeline per source (silver is source-aligned), generated, started by markers | AUTO CDC orders changes by a defined sequence, so reprocessing gives the same result |
| 5 Conformer | Conforms sources on the way into gold, because silver stays source-aligned: MES production with SAP orders, materials mapped through mapping tables | Source-aligned silver, key mappings | Shared gold dimensions and inputs to gold facts | The common gold harness | Conforming happens once, in gold; silver is never joined across sources |
| 6 Gold builder | Silver to gold: star schemas, multi-input facts, completeness per window; the only writer of each gold fact | Source-aligned silver, mapping tables, corrections, input watermarks | Gold facts and dimensions | Materialized views for simple facts; our window-recompute job for multi-input facts | Each window is replaced whole (replaceWhere on date partitions), never patched, and only one job writes a gold fact |
| 7 Metric layer | Each metric defined once | Gold | Nothing stored; answers at query time | Unity Catalog metric views | Nothing to run |
| 8 Run recorder | The one library every part calls to log | - | runtime: master_run_log, run_log, step_log, file_log, error_log, dq_result, input_watermark, config_snapshot | Our Python library, writing to Lakebase through the stored procedures, with timeouts | Rows are keyed by run id and step; writing the same row twice updates it |
| 9 Ops builder | Every 15 minutes: copies runtime rows, joins system tables and CloudWatch, works out freshness, sweeps stuck runs, raises alerts | runtime schema, system tables, CloudWatch landing | prod_ops tables, alerts, incidents | A small Lakeflow job | It recomputes the last hour each time, so a missed run is caught up by the next |
| 10 Heartbeat | Proves the whole path is alive, even when no source sends anything, and is watched without our database | A tiny synthetic source | A row in every layer every 15 minutes; a Databricks SQL alert watches the newest one | A generated dataset like any other | Same as any dataset |
Every row says where it came from
| Layer | Columns added to every row | So 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_at | Why the row was parked, and by which run |
| Gold (our jobs) | _run_id, _window_start, _is_complete, _inputs_as_of | Which run built this window, whether every input had caught up, and how far each had |
| Gold (materialized views) | None: refreshed by the pipeline | Traced 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 input | Order of changes | Deletes |
|---|---|---|
| DMS changes | Sequence 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 deltas | Sequence 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 files | Order 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 |
| Streams | Event time; duplicates removed by reading id. | Readings are never deleted |
When a source changes shape
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.
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.
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.
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
- Runs queue, never overlap. A run with nothing new to do ends as skipped, not failed.
- Each run starts from its bookmark. It takes exactly the batches it has not processed yet, so a late run or a re-run neither skips nor doubles anything.
- A failure stays with its dataset. That dataset is blocked; the rest of the source carries on.
- Big loads have their own jobs. First loads, reloads and backfills never sit in the daily path.
The triggers
| Trigger | Used for | Starts |
|---|---|---|
| Schedule | Every DMS source (its files arrive all the time, so a clock is calmer than file events); the puller | The lander job of that source |
| AppFlow finished | SAP 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 API | The lander job of that source, so there is one clock: AppFlow's |
| Upload copied | Partner files and uploads: the Transfer Family workflow's Lambda step starts the lander after copying the file to landing | The lander job of that source |
| Table update on the marker | Silver: runs when one of its sources has finished a batch | The 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 builder | Gold multi-input facts: after a pipeline update, after a sensor hour closes, or when bookmarks pass incomplete hours | The window-recompute job, queued, never two at once |
| Schedule, every 15 minutes | Sensor streams | The streaming pipeline, trigger-once: each run reads what has arrived since its checkpoint and stops |
| Manual or API, queued | Re-runs, first loads, backfill plans, the heartbeat test | A 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
| Job | How many | Inside | Started by |
|---|---|---|---|
| Lander job | One per source | Open, a for-each over due datasets (concurrency from source_config), close and write the marker | Its one clock (schedule, AppFlow finished, or upload copied) |
| Bulk job | One per source without DMS | First 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 changes | Manual or API |
| Source wrapper and pipeline | One 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_minutes | Table update on its sources' markers |
| Window-recompute job | One per multi-input gold fact; the only writer of that fact | Changed windows from the last update, plus one chunk of any queued backfill plan; corrections laid over silver; completeness; reconcile; record | Called by the domain pipeline's wrapper; queued, never two at once |
| Streaming pipeline | One per stream family (press sensors, mixer sensors) | Kinesis to bronze to silver, trigger-once: each run takes what has arrived since its checkpoint and stops | Every 15 minutes, on a schedule |
| Stream hour job | One per stream family | Sensor aggregates for the closed hour and a re-check of the last 2 days (an input to gold); then calls the window recompute | Hourly, at 15 minutes past |
| Ops builder | One | Copy runtime rows, join system tables and CloudWatch, freshness, sweep stuck runs, alerts | Every 15 minutes |
| Personal-data purge | One | Deletes personal-data landing files loaded more than 23 hours ago, and held files past 7 days | Hourly |
| Cost split | One (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 alert | Every 15 minutes |
| Housekeeping | One | Retention deletes, VACUUM, OPTIMIZE where predictive optimization is off, erasure requests, daily DMS reconcile | Nightly |
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.
| Id | Made by | Means | Found in |
|---|---|---|---|
| master_run_id | Ours | One trigger of one job: all the datasets loaded together | master_run_log, the batch-ready marker, every run_log row of that trigger |
| job_run_id | Databricks | The Lakeflow job run | Stored on master_run_log and run_log; joins system tables (timings, cost, skipped and queued runs) |
| run_id | Ours | One 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_id | Databricks | The task (or for-each iteration) that did the work | Stored on run_log; links to the task's own log in Databricks |
| update_id | Databricks | One update of a Declarative Pipeline | Stored on run_log; joins the pipeline event log |
| segment_id | Ours | One slice of a bulk load | segment_run_log; run_log adds up its segments |
| config fingerprint | Ours | The exact merged settings a run used | config_snapshot; any run can be replayed with what it ran with |
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 table | One row per | Holds |
|---|---|---|
| master_run_log | One per trigger of a job | RUNNING, SUCCEEDED, PARTIAL, FAILED, or SKIPPED when nothing was due; counts of datasets attempted, succeeded, failed, blocked; run type; retry of which master run |
| run_log | One per dataset per stage | STARTED, SUCCEEDED, FAILED, SKIPPED, BLOCKED; rows read, written, rejected; the watermark before and after; durations; cause class |
| segment_run_log | One per segment of a bulk load | The segment's predicate, rows and status; the run adds them up |
| step_log | One per step inside a run | check, land, tokenise, guard, contract, quality, merge, reconcile: rows in and out, time taken |
| file_log | One per landing file | Name, 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_watermark | One per input | Two 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_log | One per failure | Cause class (source down, schema change, bad data, our bug, platform, capacity), scrubbed message, passing or not |
| dq_result | One per rule per run | Passed or failed, failing row count, action taken; tokenised keys only |
The run recorder: six calls
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.
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.
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 wrong | Caught by | What happens by itself | What a person does |
|---|---|---|---|
| Source down or slow | Mover: DMS task state-change events and AppFlow failure events (EventBridge); a lag alarm that treats missing data as breaching | AWS retries; the lander finds nothing new and ends as skipped; the source's bookmark stops moving | Freshness goes red from the bookmark at the promised time; incident for the source owner |
| DMS stops for hours, then resumes | As above, and the bookmark | DMS 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 restatements | Nothing, 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 count | Lander or puller, the same check | The 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 PARTIAL | The partner or source re-sends; the next run loads it |
| A DMS change file is unreadable | Lander, file check | The 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 incomplete | Repair the file, or have DMS reload the table (full load, then change capture); then the dataset unblocks |
| A source changes its columns | Lander and silver, contract check | See 'When a source changes shape' below | Update the contract; replay |
| Bad rows | Silver, quality rules | Rows parked in quarantine with the reason; the rest carry on | Steward reviews weekly; fixed rows are released and re-merged |
| Too many bad rows in one batch | The wrapper, after the update, from the quality results | Rules 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 it | Find out why the source sent so much bad data; release the dataset |
| A full copy comes back short | Lander, mass-delete guard | Nothing is appended to bronze; the files are held and the dataset BLOCKED; 'mass delete stopped' alert | Check the source; re-pull; then release or reject the held files |
| A partner re-sends a corrected file | Lander and silver | Lands 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 restatements | Nothing |
| Job or cluster dies halfway | Lakeflow task failure | Retried up to 2 times (only passing errors reach Lakeflow); the batch's transaction id stops a second append | Nothing, unless retries run out |
| Lakebase is slow or down | Run recorder timeouts; job timeouts and duration health rules | Jobs fail fast at their first task instead of hanging; stuck runs are finished by the sweeper | Platform on-call, alerted by Databricks job alerts and the heartbeat alert, neither of which needs Lakebase |
| A bug in our code | Any stage | Fails; cause class 'our bug'; no retry | Fix, 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.
| Alert | Fires when | Severity | Goes to |
|---|---|---|---|
| Dataset blocked | A 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 otherwise | The source's engineer; repeats once a day while blocked, as one incident |
| Master run PARTIAL | Some datasets of a trigger failed | Medium | The source's engineer |
| Freshness missed | A dataset's bookmark is behind its target | From the data product's tier | Data product owner and on-call |
| Schema changed | A contract check stopped a table | Medium | The source owner named in the contract |
| Mass delete stopped | The guard stopped a full copy | High | The source's engineer |
| Stream down | A scheduled streaming run failed or did not run, or windows marked DOWN | High | On-call; per-press alerts held back meanwhile |
| Press silent | No reading from one press for 30 minutes in a planned shift | High (OEE is tier 1) | Plant maintenance and on-call |
| Heartbeat missing | No heartbeat row for 30 minutes (a Databricks SQL alert, independent of Lakebase) | High | Platform on-call |
| Job slow or failed | Databricks job health rules and failure notifications | High | Platform on-call |
| Runs swept | The sweeper closed stuck runs | Low | Platform team, next working day |
| New column seen | An added column kept as rescued data | Low | The steward |
Re-runs, backfills and first loads
Five ways to run again, none in the way of the normal runs
| Kind | When | How |
|---|---|---|
| Re-run failed | Only the datasets that failed in a master run | A 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 bronze | Rebuild one silver table after a logic or contract fix | A 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 |
| Backfill | Rebuild past gold windows on purpose, for example after a correction | A 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 dataset | A new table, or a new source | For 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-extract | Fetch from the source again | Only 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
Changing settings
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.
| Part | Does | Status |
|---|---|---|
| Run recorder | Every part logs the same way, through six calls, with timeouts | Built in the framework (Azure SQL today); the Lakebase adapter and timeouts are to do |
| File log | Every landing file: checked, loaded or rejected, by which run | To build |
| Input watermarks | How far each input is loaded, in source time: completeness and freshness | To build |
| Ingest columns | Rows say which run and file wrote them, and their order in the source | Partly built: bronze columns exist; DMS and SAP order columns to add |
| Pipeline wrapper | Makes a Declarative Pipeline update look like any other run, and starts gold | To build |
| Ops builder and sweeper | prod_ops every 15 minutes; finishes stuck runs | To build; the health views exist in the framework for Azure |
| Health views | One row per dataset: last run, status, bookmark against target, rows against the usual band, failing rules | Built for Azure SQL (v_dataset_health); to port |
| Heartbeat | A synthetic dataset through every layer every 15 minutes, watched by a Databricks SQL alert that does not need Lakebase | Designed in the framework (PL_HEARTBEAT); to build on Databricks |
| Job health rules | Duration and failure alerts from Databricks itself, for when our own logging is the problem | To generate with the jobs |
| Doctor | A healthcheck before every deploy and on demand: database, secrets, buckets, grants, triggers, DMS tasks | Built 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.
| When | What runs |
|---|---|
| Every 15 min | Press 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 :15 | The stream job builds the closed hour's sensor aggregates and calls the window recompute |
| Hourly | The personal-data purge deletes landing files loaded more than 23 hours ago |
| Every 30 min, 06:00-22:00 | SAP orders and deliveries: AppFlow runs, then its lander |
| 01:00 | Nightly SAP full copies for objects without deletes in their delta; the sales pipeline follows |
| Overnight | Dealer sell-out files arrive; each starts its lander once copied to landing |
| 03:00 | Housekeeping: retention, erasures, VACUUM, the daily DMS reconcile |
| 06:00 | The night shift ends (shifts: 06:00, 14:00, 22:00, plant time) |
| 06:15 | The 05:00-06:00 sensor hour closes; the window recompute writes the production fact and sets completeness for the night shift |
| 06:20 | Ops builder: tier-1 data products green, amber or red from their bookmarks; red pages on-call |
| 06:30 | OEE for the morning meeting, with completeness shown. Readings that arrive later restate the hour and say so |
| 09:00 | Warranty 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
| Situation | Given | When | Checks | Result |
|---|---|---|---|---|
| S01 Normal 15 minutes of MES changes reach gold | DMS running for MES; 3 MES datasets with new change files in landing; all bookmarks current | The 00:15 schedule fires | 13 | 12 of 13 stated, 1 contradicted |
| S02 An SAP sales order line is deleted | AppFlow pulls the sales order delta every 30 minutes; the line exists in silver | The line is deleted in SAP and the next AppFlow run carries the delta | 5 | 4 of 5 stated, 1 gap |
| S03 DMS stops for 2 hours, then catches up | MES source changes all through the night shift | The DMS task stops at 01:00 and resumes at 03:00 | 8 | Passes |
| S04 A schedule fires while the previous lander run is still going | The 00:15 MES run is still loading at 00:30 | The 00:30 schedule fires | 3 | Passes |
| S05 One of twelve MES datasets has an unreadable file | Twelve MES datasets due; one has a truncated Parquet change file | The lander run reaches that file | 8 | Passes |
| S06 A partner file is unreadable | A dealer uploads a sell-out CSV that cannot be parsed | File arrival starts the dealer lander | 3 | Passes |
| S07 A partner re-sends a corrected file | Dealer D's October sell-out file was loaded yesterday | The dealer uploads a corrected October file with the same name | 4 | Passes |
| S08 The press master full copy comes back short | Press master has 2,000 presses; the puller pulls a full copy daily | Today's pull returns 300 rows, with a manifest that says 300 | 4 | Passes |
| S09 A press is really decommissioned | Press P has three years of facts | Press P disappears from the daily full copy (one row out of 2,000) | 3 | Passes |
| S10 The sensor stream is down for 40 minutes | Press sensors stream through Kinesis; a trigger-once pipeline picks them up every 15 minutes | The streaming pipeline stops at 02:00 and restarts at 02:40 | 5 | Passes |
| S11 One press goes silent while the others stream | 2,000 presses streaming; press Q in a planned shift | Press Q sends nothing for 30 minutes | 3 | Passes |
| S12 Three-month backfill after an ideal-cycle-time correction | Ideal cycle time for material M was wrong for 3 months; normal runs continue every 15 minutes | The owner submits the correction with an applies-from date | 5 | Passes |
| S13 Lakebase (the log database) is slow | Lakebase answers in 60 seconds instead of milliseconds | The next lander run starts | 4 | Passes |
| S14 A run commits to bronze but cannot close its log row | A lander run has committed rows to bronze | The Lakebase write in close_run fails | 4 | Passes |
| S15 The cluster dies in the middle of a load | A lander task is appending a micro-batch to bronze | The cluster is lost | 3 | Passes |
| S16 SAP renames a column | The delivery extract has a column renamed | The next delta lands | 4 | Passes |
| S17 A source adds a new column | The warranty system adds a free-text column | The next load lands | 3 | Passes |
| S18 A new dataset is added to a running source | MES is loading normally | A YAML change adds a 13th MES table | 5 | Passes |
| S19 Settings change between two runs | A quality limit is changed in YAML and deployed at 10:07 | Runs at 10:00 and 10:15 happen | 3 | Passes |
| S20 Re-run the datasets that failed | A master run ended PARTIAL with two FAILED datasets, now fixed at the source | An engineer starts a re-run | 3 | Passes |
| S21 Personal data arrives in a warranty claim | The warranty source sends a claim with the customer's name, phone and a note naming them | The lander loads it | 4 | Passes |
| S22 Nothing at all arrives for an hour | A quiet night; no source sends anything | An hour passes | 3 | Passes |
| S23 A 100-column source table, 30 columns wanted in silver | An MES table with 100 columns; the dataset lists 30 of them, 4 renamed | A normal load runs, and next month the source adds a 101st column | 5 | Passes |
| S24 Comma decimals and day-first dates from an EU plant | An EU plant sends '12,5' for a cure temperature and '31.10.2026' for a date; one row has 'abc' as the temperature | The load runs | 4 | Passes |
| S25 The source renames a listed column | A required listed column 'press_no' is renamed to 'press_number' at the source | The next load runs | 4 | Passes |
| S26 A source type is widened, another is changed | Silver types: counter BIGINT, batch_code STRING, weight DECIMAL(10,2) | The source widens an INT to BIGINT, and changes weight from a number to text | 2 | Passes |
| S27 History by kind of data | A 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 arrives | 5 | Passes |
| S28 A JSON API payload with nested order lines | The dealer API returns orders as JSON with a nested dealer object and an array of order lines | A response lands, and later the API adds a new field and drops an optional one | 5 | Passes |
| S29 Deploy the platform to a new environment | An empty pre-prod environment; platform settings for preprod in Git | The deploy and the first lander run happen | 6 | 2 of 6 stated, 4 gaps |
| S30 Our puller reads a database table and a REST API | An append-only SQL Server table pulled by watermark column, and a REST API with a rate limit; one table is above the large-table threshold | The puller runs | 6 | 3 of 6 stated, 2 gaps, 1 contradicted |
| S31 Values are standardised by column settings | A 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 time | Silver runs | 7 | Passes |
| S32 Test records and a bad-share limit | A dataset with row_filter dropping rows where is_test = 'Y', and quarantine_max_pct = 2 | A load has 1,000 rows: 10 test rows and 30 rows failing a quarantine rule | 3 | Passes |
| S33 Volume falls and cost rises | A dataset with volume_band = auto and budget_daily_usd = 20 | A Tuesday load writes 20% of the usual Tuesday rows, and the dataset's cost that day reaches 35 USD | 3 | 2 of 3 stated, 1 gap |
| S34 Profiling suggests a rule | profile.enabled on, profile.when daily, profile.sample_pct 10, profile.suggest_rules on | A column's empty share jumps from 0.1% to 8% | 3 | Passes |
| S35 A wide reference table taken whole, with a list column | A reference table where silver.columns = all_except_excluded, two columns marked is_excluded, and a tags column holding a list | Silver runs | 3 | 2 of 3 stated, 1 contradicted |
| S36 Two sources of one domain finish close together | The manufacturing domain watches the MES and press-master markers; min_gap_minutes = 15 | MES finishes at 00:15:45 and the press master at 00:18:00 | 3 | 1 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.
| Mover | Run controller | Lander | Silver | Integrator | Gold | Metric layer | Run recorder | Ops builder | Heartbeat | |
|---|---|---|---|---|---|---|---|---|---|---|
| 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 | ● | ● | ||||||||
| Total | 6 | 13 | 20 | 21 | 1 | 8 | 0 | 9 | 12 | 2 |
Which kinds of trouble each situation covers
| Normal day | Source down | Bad file | Schema change | Re-sent data | Late or out of order | Deletes | Crash | Slow dependency | Overlapping runs | Corrections | New or changed config | Personal data | Streaming | Bad 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 | ● | ||||||||||||||
| Total | 7 | 5 | 4 | 6 | 1 | 3 | 3 | 3 | 3 | 2 | 2 | 3 | 2 | 2 | 4 |
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.
| Run | Checks stated | Gaps | Contradicted | Situations passing | Would not work as written | Page disagrees with itself |
|---|---|---|---|---|---|---|
| Run 1 (2026-11-03) | 80 of 100 | 20 | 0 | 9 of 22 | 7 | not checked |
| Run 2 (2026-11-03) | 97 of 100 | 2 | 1 | 19 of 22 | 7 | 8 |
| Run 3 (2026-11-03) | 95 of 100 | 1 | 4 | 18 of 22 | 3 | 10 |
| Run 4 (2026-11-03) | 98 of 100 | 1 | 1 | 20 of 22 | 2 | 7 |
| Run 5 (2026-11-03) | 146 of 159 | 10 | 3 | 29 of 36 | 3 | 10 |
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