Data PlatformZoom level 2
The runtime tables: what happened, and the rules that keep the record honest
The runtime schema is the platform's memory. It records every run, what each run did to each dataset, and how far each dataset has been read.
Two kinds of trouble come from getting this wrong. If a position moves forward for a run that failed, data is skipped and nobody notices. If the record of a run can be written in any order, it cannot be trusted as evidence. The functions over these tables are built to rule out both.
The tables and what one row means
| Table | One row means | Key |
|---|---|---|
runtime.run | One run of a pipeline over a domain: its id, domain, environment, what triggered it, its type and its status. | run_id |
runtime.dataset_run | One dataset inside one run: status, the configuration fingerprint, the watermark before and after, counts, a message, start and end times. | (run_id, dataset_id) |
runtime.step_log | One step of a dataset run: the step's name, a status, counts and a message. | step_id |
runtime.file_log | One batch that ingestion handled: batch key, file name, delivery window, checksum, declared rows, rows, status, reason and load type. | file_id |
runtime.error_log | One error, with the step and the error's class. The dataset may be empty. | error_id |
runtime.config_snapshot | One configuration document, stored once per fingerprint, with the time it was first seen. | fingerprint |
runtime.watermark | One dataset's watermark and the run that last moved it. A watermark is the highest change value already taken from the source. | dataset_id |
runtime.bookmark | One named position of a dataset, such as a page cursor, a stream position per shard, or how far silver has read the file log. | (dataset_id, bookmark_key) |
runtime.watermark_audit | One reset of a watermark by hand: old value, new value, reason, who, when. | audit_id |
runtime.table_lease | One table being written right now: the run holding it and when the lease expires. | table_name |
runtime.batch_void | One loaded batch that was declared bad afterwards, with the reason and the approver. | (dataset_id, batch_key) |
runtime.backfill_plan | One plan that names the windows gold must rebuild and the date from which its corrections count. | plan_id |
runtime.correction | One corrected value: the business key, the field, the new value, the old value and the dates it applies to. | correction_id |
runtime.dq_result | What one quality rule found in one dataset run: rows checked, rows failed, the action. | result_id |
runtime.dq_profile | The profile of one dataset run that loaded something. | profile_id |
src/dataplatform/adapters/sql/postgres_meta.sql:133The dataset run. The checks on the status column are part of the rules.CREATE TABLE IF NOT EXISTS runtime.dataset_run (
seq BIGSERIAL UNIQUE,
run_id TEXT NOT NULL REFERENCES runtime.run (run_id),
dataset_id BIGINT NOT NULL REFERENCES config.dataset (dataset_id),
status TEXT NOT NULL CHECK (status IN ('running', 'succeeded', 'failed', 'skipped')),
config_fingerprint TEXT NOT NULL REFERENCES runtime.config_snapshot (fingerprint),
watermark_before TEXT,
watermark_after TEXT,
counts JSONB NOT NULL DEFAULT '{}',
message TEXT NOT NULL DEFAULT '',
started_at TIMESTAMPTZ NOT NULL DEFAULT now(),
ended_at TIMESTAMPTZ,
PRIMARY KEY (run_id, dataset_id)
);src/dataplatform/adapters/sql/postgres_meta.sql:160The file log, with the index that makes the latest status of a batch quick to find, and the load type added later in place.CREATE TABLE IF NOT EXISTS runtime.file_log (
file_id BIGSERIAL PRIMARY KEY,
run_id TEXT NOT NULL,
dataset_id BIGINT NOT NULL,
batch_key TEXT NOT NULL,
file_name TEXT NOT NULL,
delivery_window TEXT NOT NULL,
checksum TEXT NOT NULL,
declared_rows BIGINT,
row_count BIGINT NOT NULL,
status TEXT NOT NULL,
reason TEXT NOT NULL DEFAULT '',
recorded_at TIMESTAMPTZ NOT NULL DEFAULT now(),
FOREIGN KEY (run_id, dataset_id) REFERENCES runtime.dataset_run (run_id, dataset_id)
);
CREATE INDEX IF NOT EXISTS ix_file_log_batch ON runtime.file_log (dataset_id, batch_key, file_id DESC);
-- B8 (15 Nov): a full copy is a rebase point for rebuilds and housekeeping; added in place on an older store
ALTER TABLE runtime.file_log ADD COLUMN IF NOT EXISTS load_type TEXT NOT NULL DEFAULT 'incremental'
CHECK (load_type IN ('incremental', 'full', 'baseline'));A worked example: one run of the sales order lines
The tyre company's ingestion runs for 4 November 2026 and one change file for the sales order lines has arrived with 250 rows. The values are small made-up numbers to show the shape. The left column is the table, the right column is the row the run leaves behind.
| Table | Row |
|---|---|
runtime.run | ingest-2026-11-04, domain tyre, environment dev, trigger schedule, type normal, status succeeded. |
runtime.config_snapshot | The dataset view of the sales order lines, stored under its fingerprint if that fingerprint was not seen before. |
runtime.dataset_run | Status succeeded, watermark before 4100, watermark after 4350, counts with one loaded batch and 250 rows. |
runtime.file_log | The file's name, window 2026-11-04, declared rows 250, rows 250, status loaded, load type incremental. |
runtime.step_log | Step ingest.push.change_feed with status succeeded and the counts. |
runtime.watermark | 4350, the highest change_seq loaded. |
runtime.bookmark | feed_pos with the highest feed position loaded. |
Rule 1: a position moves only when the dataset run succeeds
src/dataplatform/adapters/sql/postgres_meta.sql:659The one function that moves a watermark or a bookmark.-- Close a dataset run. The watermark and bookmarks move only on success, in this same transaction as the run record -- (kept from sp_run_log_finished + sp_advance_watermark, now one call so they cannot drift apart). CREATE OR REPLACE FUNCTION runtime.finish_dataset_run(p_run_id TEXT, p_source TEXT, p_dataset TEXT, p_status TEXT, p_counts JSONB, p_watermark TEXT, p_bookmarks JSONB, p_message TEXT) RETURNS VOID LANGUAGE plpgsql SECURITY DEFINER SET search_path = pg_catalog, pg_temp AS $$ DECLARE ds BIGINT; st TEXT; after TEXT; BEGIN IF p_status NOT IN ('succeeded', 'failed', 'skipped') THEN RAISE EXCEPTION 'dataset run status % is not succeeded, failed or skipped', p_status USING ERRCODE = 'DPM03'; END IF; ds := runtime._dataset_run(p_run_id, p_source, p_dataset); SELECT status INTO st FROM runtime.dataset_run WHERE run_id = p_run_id AND dataset_id = ds FOR UPDATE; IF st <> 'running' THEN RAISE EXCEPTION '%.% in run % is already %', p_source, p_dataset, p_run_id, st USING ERRCODE = 'DPM04'; END IF; IF p_status = 'succeeded' THEN IF p_watermark IS NOT NULL THEN INSERT INTO runtime.watermark (dataset_id, watermark_value, last_run_id, updated_at) VALUES (ds, p_watermark, p_run_id, now()) ON CONFLICT (dataset_id) DO UPDATE SET watermark_value = EXCLUDED.watermark_value, last_run_id = EXCLUDED.last_run_id, updated_at = now(); END IF; INSERT INTO runtime.bookmark (dataset_id, bookmark_key, bookmark_value, last_run_id) SELECT ds, b.key, b.value, p_run_id FROM jsonb_each_text(COALESCE(p_bookmarks, '{}')) b ON CONFLICT (dataset_id, bookmark_key) DO UPDATE SET bookmark_value = EXCLUDED.bookmark_value, last_run_id = EXCLUDED.last_run_id, updated_at = now(); END IF; SELECT watermark_value INTO after FROM runtime.watermark WHERE dataset_id = ds; UPDATE runtime.dataset_run SET status = p_status, counts = COALESCE(p_counts, '{}'), watermark_after = after, message = runtime._redact(p_message), ended_at = now() WHERE run_id = p_run_id AND dataset_id = ds; END $$;
Walk through it. The status must be one of three words. The helper checks that the dataset was started in this run. The row is then locked with FOR UPDATE and must still be running, so a dataset run closes once. Only inside the branch for succeeded are the watermark and the bookmarks written. A watermark of nothing leaves the stored one alone. After the branch, the function reads the watermark back and writes it as watermark_after on the dataset run. Because the whole function is one transaction, the run record and the positions cannot disagree.
The caller cannot get around this. A pipeline has no right on runtime.watermark or runtime.bookmark, and no other function writes them apart from the audited reset.
Rule 2: a run is recorded in order
src/dataplatform/adapters/sql/postgres_meta.sql:562Opening a dataset run: the run must still be open, the dataset is started once, the snapshot is saved, and the current watermark is returned.CREATE OR REPLACE FUNCTION runtime.start_dataset_run(p_run_id TEXT, p_source TEXT, p_dataset TEXT,
p_fingerprint TEXT, p_config JSONB) RETURNS TEXT
LANGUAGE plpgsql SECURITY DEFINER SET search_path = pg_catalog, pg_temp AS $$
DECLARE ds BIGINT; before TEXT;
BEGIN
IF runtime._check_run(p_run_id) <> 'running' THEN
RAISE EXCEPTION 'run % is already finished', p_run_id USING ERRCODE = 'DPM04';
END IF;
ds := config._dataset_id(p_source, p_dataset);
IF EXISTS (SELECT 1 FROM runtime.dataset_run WHERE run_id = p_run_id AND dataset_id = ds) THEN
RAISE EXCEPTION '%.% was already started in run %', p_source, p_dataset, p_run_id USING ERRCODE = 'DPM04';
END IF;
INSERT INTO runtime.config_snapshot (fingerprint, config) VALUES (p_fingerprint, p_config)
ON CONFLICT (fingerprint) DO NOTHING;
SELECT watermark_value INTO before FROM runtime.watermark WHERE dataset_id = ds;
INSERT INTO runtime.dataset_run (run_id, dataset_id, status, config_fingerprint, watermark_before)
VALUES (p_run_id, ds, 'running', p_fingerprint, before);
RETURN before;
END $$;The same pattern runs through every function. A step, a file or a profile can only be logged for a dataset that was started in that run. A run cannot be closed while a dataset in it is still running. A second start of the same run id is refused. Each of these is refusal code DPM04.
Rule 3: the configuration used is kept
When a dataset run opens, the caller hands over the dataset's configuration document and its fingerprint. The document is stored in runtime.config_snapshot once per fingerprint, and the dataset run keeps the fingerprint. Months later, the question of which settings a run used is answered by joining the two.
Rule 4: secrets do not reach a message
src/dataplatform/adapters/sql/postgres_meta.sql:512Every stored message passes through this function first.-- Secrets out, then capped at 4000 characters (kept in step with dataplatform.meta.port.redact).
CREATE OR REPLACE FUNCTION runtime._redact(p_message TEXT) RETURNS TEXT
LANGUAGE sql IMMUTABLE SET search_path = pg_catalog, pg_temp AS $$
SELECT left(regexp_replace(
regexp_replace(COALESCE(p_message, ''),
'(password|pwd|accountkey|sharedaccesskey|sig|token|secret)=[^;&[:space:]]+', '\1=***', 'gi'),
'bearer[[:space:]]+[A-Za-z0-9._~+/=-]+', 'Bearer ***', 'gi'), 4000)
$$;The inner replacement finds a name such as password, token or secret followed by an equals sign and replaces the value up to the next separator with three stars. The outer one does the same for a bearer token. The result is cut to 4000 characters. Step messages, file reasons, error messages, dataset run messages and quality result details all pass through it. The same two patterns are in src/dataplatform/meta/port.py for the in-memory adapter.
Rule 5: one writer per table, with a time limit
src/dataplatform/adapters/sql/postgres_meta.sql:971Taking a lease is one insert that turns into an update only when the old lease has expired or belongs to the same run.-- NULL when this run holds the lease now (new, re-entered or taken over after expiry); else the run holding it.
CREATE OR REPLACE FUNCTION runtime.acquire_lease(p_table TEXT, p_run_id TEXT, p_ttl_seconds INTEGER) RETURNS TEXT
LANGUAGE plpgsql SECURITY DEFINER SET search_path = pg_catalog, pg_temp AS $$
DECLARE holder TEXT;
BEGIN
IF p_ttl_seconds IS NULL OR p_ttl_seconds <= 0 THEN
RAISE EXCEPTION 'a lease needs a positive time to live' USING ERRCODE = 'DPM03';
END IF;
INSERT INTO runtime.table_lease AS l (table_name, run_id, expires_at)
VALUES (p_table, p_run_id, now() + make_interval(secs => p_ttl_seconds))
ON CONFLICT (table_name) DO UPDATE
SET run_id = EXCLUDED.run_id, acquired_at = now(), expires_at = EXCLUDED.expires_at
WHERE l.expires_at < now() OR l.run_id = EXCLUDED.run_id;
SELECT l.run_id INTO holder FROM runtime.table_lease l WHERE l.table_name = p_table;
RETURN CASE WHEN holder = p_run_id THEN NULL ELSE holder END;
END $$;A lease is a row that says a run is writing a table. The insert succeeds when no row exists. When a row exists, the update happens only if the row's expiry time has passed or the row already belongs to this run. The function then reads the row and answers with nothing if this run holds it, or with the other run's id. A run that crashes leaves its lease behind, and the lease frees itself when its time is up.
src/dataplatform/leases.py:16The time limit every runner passes: four hours.LEASE_SECONDS = 4 * 3600 # longer than any one table's write; a crashed run's lease frees itself after thissrc/dataplatform/leases.py:20What a runner does when the table is busy: a failed step that names the holder, and a dataset run closed as skipped.def busy(store: MetadataStore, run_id: str, source: str, dataset: str, lease: str, holder: str) -> str:
"""Record that this run did not write because another holds the lease; the dataset run must be started."""
msg = f"{lease} is being written by run {holder}; this run does not write it (ORCH-04)"
store.log_step(StepRecord(run_id, source, dataset, STEP, "failed", {}, msg))
store.finish_dataset_run(run_id, source, dataset, "skipped", message=msg)
return msgRule 6: the file log is never edited
The file log only grows. A batch that is handled twice has two rows, and the newest one gives its status. A loaded batch that later turns out to be bad is not deleted. It gets a row in runtime.batch_void, with who approved it and why, and from then on every read reports its status as void. Reader: picking batches shows how silver uses that.
Rule 7: a reset by hand leaves a trace
src/dataplatform/adapters/sql/postgres_meta.sql:731The only other way a watermark changes. The audit row is written first.CREATE OR REPLACE FUNCTION runtime.reset_watermark(p_source TEXT, p_dataset TEXT, p_value TEXT, p_reason TEXT,
p_changed_by TEXT) RETURNS VOID
LANGUAGE plpgsql SECURITY DEFINER SET search_path = pg_catalog, pg_temp AS $$
DECLARE ds BIGINT := config._dataset_id(p_source, p_dataset); old TEXT;
BEGIN
IF length(btrim(COALESCE(p_reason, ''))) < 10 THEN
RAISE EXCEPTION 'a watermark reset needs a reason of at least 10 characters' USING ERRCODE = 'DPM03';
END IF;
IF btrim(COALESCE(p_changed_by, '')) = '' THEN
RAISE EXCEPTION 'a watermark reset needs who made it' USING ERRCODE = 'DPM03';
END IF;
SELECT watermark_value INTO old FROM runtime.watermark WHERE dataset_id = ds FOR UPDATE;
INSERT INTO runtime.watermark_audit (dataset_id, old_value, new_value, reason, changed_by)
VALUES (ds, old, p_value, p_reason, p_changed_by);
INSERT INTO runtime.watermark (dataset_id, watermark_value, last_run_id, updated_at)
VALUES (ds, p_value, NULL, now())
ON CONFLICT (dataset_id) DO UPDATE SET watermark_value = EXCLUDED.watermark_value, updated_at = now();
END $$;The settings this part reads
| Value | Where it is | Meaning |
|---|---|---|
| 4 hours | LEASE_SECONDS in the leases module | How long a lease lasts before another run may take it over. |
| 4000 characters | runtime._redact and ERROR_CAP in the port | The longest message kept. |
| 10 characters | The SQL functions and MIN_REASON in the port | The shortest reason accepted for a reset, a void or a backfill plan. |
| normal, reload, backfill | The check on runtime.run.run_type | The three run types. Only a normal ingestion run moves positions. |
None of these is a setting in the dictionary. They are constants in the code.
How it is tested
tests/dataplatform/test_meta_store.py:229Three real cases: the watermark moves on success, stays on failure, and stays when a success passes none. ("watermark: advances on success", seq(lambda s: one_run(s, "r1", "succeeded", "100"),
lambda s: s.watermark("erp", "orders")), "100"),
("watermark: kept on failure", seq(lambda s: one_run(s, "r1", "succeeded", "100"),
lambda s: one_run(s, "r2", "failed", "200"),
lambda s: s.watermark("erp", "orders")), "100"),
("watermark: kept when a success passes none", seq(lambda s: one_run(s, "r1", "succeeded", "100"),
lambda s: one_run(s, "r2", "succeeded", None),
lambda s: s.watermark("erp", "orders")), "100"),tests/dataplatform/test_meta_store.py:329The lease case: the second run is told who holds the table. ("lease: a second run finds it busy", seq(lambda s: s.acquire_lease("silver:erp.orders", "r1", 60),
lambda s: s.acquire_lease("silver:erp.orders", "r2", 60)), "r1"),The same table in tests/dataplatform/test_meta_store.py has a case that logs an error message containing a password, a token and a bearer header, and checks that the stored message shows three stars in their place. Another case logs a message of 9000 characters and checks that 4000 are kept.
- The configuration snapshot is keyed by the dataset's fingerprint, and that fingerprint covers the dataset manifest and the contract. It does not cover a registry default or a domain setting. If one of those changed while the dataset's own files did not, the fingerprint would be the same and the snapshot already stored would be kept. The record would then show the older merged settings.
- A lease has a fixed length and is not renewed while a run is working. A write that took longer than four hours could be overtaken by a second run.
- A run that crashes leaves its run and dataset run rows with status running. I found no function that closes them afterwards. A run id can be started only once, so the rerun needs a new run id.
- Redaction is by pattern. A secret that does not look like a name, an equals sign and a value, or like a bearer token, would pass. Counts, file names and the configuration snapshot are not redacted.
- There is no function to reset a bookmark, only a watermark. And the audited watermark reset has no caller in the platform code yet.
runtime.dq_resulthas no writer in the platform code that I could find. Silver records what its quality stage found in the step log instead.- Nothing removes old rows from the runtime tables. They grow with every run.
- The run id is planned to become the trace id. The comment on the table says so. Today it is a text the runner builds.