cleandataengineering.aiData Platform, from the big picture to the code
You are here: Start here › Metadata store › Runtime tables

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.

runone per run iddataset_runone per datasetstep_logwhat each step didfile_logevery batcherror_logwhat went wrongconfig_snapshotsettings usedwatermarkone valuebookmarknamed valuestable_leasewho writeson success only
A run has dataset runs. The highlighted box is the unit everything hangs on. The watermark and bookmark tables are written only when a dataset run closes as succeeded.

The tables and what one row means

TableOne row meansKey
runtime.runOne run of a pipeline over a domain: its id, domain, environment, what triggered it, its type and its status.run_id
runtime.dataset_runOne 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_logOne step of a dataset run: the step's name, a status, counts and a message.step_id
runtime.file_logOne batch that ingestion handled: batch key, file name, delivery window, checksum, declared rows, rows, status, reason and load type.file_id
runtime.error_logOne error, with the step and the error's class. The dataset may be empty.error_id
runtime.config_snapshotOne configuration document, stored once per fingerprint, with the time it was first seen.fingerprint
runtime.watermarkOne 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.bookmarkOne 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_auditOne reset of a watermark by hand: old value, new value, reason, who, when.audit_id
runtime.table_leaseOne table being written right now: the run holding it and when the lease expires.table_name
runtime.batch_voidOne loaded batch that was declared bad afterwards, with the reason and the approver.(dataset_id, batch_key)
runtime.backfill_planOne plan that names the windows gold must rebuild and the date from which its corrections count.plan_id
runtime.correctionOne corrected value: the business key, the field, the new value, the old value and the dates it applies to.correction_id
runtime.dq_resultWhat one quality rule found in one dataset run: rows checked, rows failed, the action.result_id
runtime.dq_profileThe 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.

TableRow
runtime.runingest-2026-11-04, domain tyre, environment dev, trigger schedule, type normal, status succeeded.
runtime.config_snapshotThe dataset view of the sales order lines, stored under its fingerprint if that fingerprint was not seen before.
runtime.dataset_runStatus succeeded, watermark before 4100, watermark after 4350, counts with one loaded batch and 250 rows.
runtime.file_logThe file's name, window 2026-11-04, declared rows 250, rows 250, status loaded, load type incremental.
runtime.step_logStep ingest.push.change_feed with status succeeded and the counts.
runtime.watermark4350, the highest change_seq loaded.
runtime.bookmarkfeed_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 this
src/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 msg

Rule 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

ValueWhere it isMeaning
4 hoursLEASE_SECONDS in the leases moduleHow long a lease lasts before another run may take it over.
4000 charactersruntime._redact and ERROR_CAP in the portThe longest message kept.
10 charactersThe SQL functions and MIN_REASON in the portThe shortest reason accepted for a reset, a void or a backfill plan.
normal, reload, backfillThe check on runtime.run.run_typeThe 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.

Not built yet, or known gaps
  • 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_result has 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.