Home › Learn › Data platform architecture › Metadata store

Data platform architecture · component · the same on every platform

The metadata store

How a metadata-driven data platform stores its settings: five tables, one reader view, an override order, a YAML loader and a fingerprint on every run.

↑ The whole pictureMetadata storeIngestionBronze to silverGold

In plain words

The platform's rule book

Every pipeline needs to know things before it runs: where the source is, which columns are the key, how history is kept, which checks to run, who pays. In a metadata-driven platform none of that is written inside the pipeline. It lives in one place, the metadata store, and every pipeline reads its settings from there. Adding a source means adding rows, not writing code.

Control plane: manifest → metadata DB → generators → pipelinesSourcesLandingBronzeSilverGoldSemanticExperiencesyou are here: the control plane, which drives every layer
The metadata store sits in the control plane and drives every layer.

The design here follows the article Metadata DB Design for Data Engineering Platforms. It has three rules: a new setting needs no schema change; master data and settings are kept apart with real foreign keys; and readers get one stable interface that never changes when settings are added.

The model

Five tables in two tiers

TableTierOne row perWhy it exists
platform_configsettingsplatform-wide settingDefaults for everything: time zone, retry count, alert channel
source_mastermasterupstream systemA database, an API, a bucket, a topic
source_configsettingssetting of one sourceKey-value, grouped (ingestion, quality, cost); new keys need no ALTER TABLE
dataset_mastermasterdataset of a sourceA table, a file pattern, a stream
dataset_configsettingssetting of one datasetOverrides for this dataset only
-- PostgreSQL. One database per environment (dev, test, prod), same schema.
CREATE TABLE platform_config (
  config_key   text PRIMARY KEY,
  config_value text NOT NULL,
  description  text,
  updated_at   timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE source_master (
  source_id   uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  source_name text NOT NULL UNIQUE,
  source_type text NOT NULL,              -- postgres, sftp, rest, kafka …
  is_active   boolean NOT NULL DEFAULT true,
  created_at  timestamptz NOT NULL DEFAULT now(),
  updated_at  timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE source_config (
  config_id    uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  source_id    uuid NOT NULL REFERENCES source_master(source_id),
  config_key   text NOT NULL,
  config_value text NOT NULL,
  config_group text NOT NULL,              -- ingestion, quality, cost …
  is_active    boolean NOT NULL DEFAULT true,
  updated_at   timestamptz NOT NULL DEFAULT now(),
  UNIQUE (source_id, config_key)
);

CREATE TABLE dataset_master (
  dataset_id   uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  dataset_name text NOT NULL,
  source_id    uuid NOT NULL REFERENCES source_master(source_id),
  schema_name  text,
  table_name   text,
  is_active    boolean NOT NULL DEFAULT true,
  created_at   timestamptz NOT NULL DEFAULT now(),
  updated_at   timestamptz NOT NULL DEFAULT now(),
  UNIQUE (source_id, dataset_name)
);

CREATE TABLE dataset_config (            -- same shape as source_config
  config_id    uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  dataset_id   uuid NOT NULL REFERENCES dataset_master(dataset_id),
  config_key   text NOT NULL,
  config_value text NOT NULL,
  config_group text NOT NULL,
  is_active    boolean NOT NULL DEFAULT true,
  updated_at   timestamptz NOT NULL DEFAULT now(),
  UNIQUE (dataset_id, config_key)
);

The tables, in PostgreSQL. UUID keys make it safe to copy settings between the dev, test and prod databases.

Reading it

One view, one function, one override order

Pipelines never query the tables directly. They read one view, which returns one row per dataset with each tier's settings as a JSON object. Each tier is aggregated in its own subquery, so rows never multiply and a dataset with no settings gets an empty object instead of an error.

CREATE VIEW dataset_view AS
SELECT
  dm.dataset_id, dm.dataset_name, dm.schema_name, dm.table_name,
  sm.source_id,  sm.source_name,  sm.source_type,
  COALESCE((SELECT jsonb_object_agg(sc.config_key, sc.config_value)
            FROM source_config sc
            WHERE sc.source_id = sm.source_id AND sc.is_active), '{}'::jsonb)   AS source_config_json,
  COALESCE((SELECT jsonb_object_agg(dc.config_key, dc.config_value)
            FROM dataset_config dc
            WHERE dc.dataset_id = dm.dataset_id AND dc.is_active), '{}'::jsonb) AS dataset_config_json,
  COALESCE((SELECT jsonb_object_agg(pc.config_key, pc.config_value)
            FROM platform_config pc), '{}'::jsonb)                             AS platform_config_json
FROM dataset_master dm
JOIN source_master  sm ON sm.source_id = dm.source_id
WHERE dm.is_active AND sm.is_active;

The reader merges the three objects in a fixed order: platform, then source, then dataset. A dataset setting beats a source setting, which beats a platform default. It also computes a fingerprint of the merged settings.

import hashlib, json

def load_combined_config(conn, dataset_id: str) -> dict:
    row = conn.execute(
        "SELECT dataset_name, platform_config_json, source_config_json, dataset_config_json "
        "FROM dataset_view WHERE dataset_id = %s", (dataset_id,)).fetchone()
    if row is None:
        raise LookupError(f"dataset {dataset_id} is missing or inactive")
    combined = {}
    combined.update(row["platform_config_json"])   # lowest priority
    combined.update(row["source_config_json"])     # source overrides platform
    combined.update(row["dataset_config_json"])    # dataset overrides source
    return {"dataset_name": row["dataset_name"], "config": combined}

def fingerprint(config: dict) -> str:
    canonical = json.dumps(config, sort_keys=True, separators=(",", ":"))
    return hashlib.sha256(canonical.encode()).hexdigest()[:16]

Python. The fingerprint is a short hash of the merged settings, so two runs with the same fingerprint used exactly the same configuration.

Changing it

YAML in git, a loader, then the database

People and agents never edit the tables by hand. They change a YAML file through a pull request, where it is reviewed and tested. A loader then checks every key against a key registry (each key's table, type, allowed values and default) and writes the rows. Git is the history of every change; the database is what pipelines read at run time.

# manifests/shop_db.yaml  (edited by pull request; key names are illustrative)
source: shop_db
type: postgres
config:
  ingestion.connection_ref: kv://shop-db-readonly   # a secret reference, never the secret
  ingestion.mode: incremental
datasets:
  - name: orders
    schema: public
    table: orders
    config:
      ingestion.watermark_column: updated_at
      silver.key: order_id
      silver.history: scd1
      quality.on_bad_row: quarantine
      cost.owner: sales-analytics
  - name: customers
    schema: public
    table: customers
    config:
      ingestion.mode: cdc                            # overrides the source default
      silver.key: customer_id
      silver.history: scd2

The loader

Parses the YAML, rejects unknown keys and wrong types, then upserts source_master, source_config, dataset_master and dataset_config in one transaction. Removed keys are set is_active = false, not deleted, so history stays.

Protected keys

Keys that loosen a data contract or change a metric definition need an extra approver, and an agent can never change them on its own.

Runtime

Settings and state live apart

Settings say what should happen. State records what did happen: watermarks, run logs, quality results. Keeping them in separate tables means changing a setting never rewrites history, and every run can prove which settings it used.

CREATE TABLE run_log (
  run_id             uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  dataset_id         uuid NOT NULL REFERENCES dataset_master(dataset_id),
  batch_id           text NOT NULL,
  layer              text NOT NULL,               -- bronze, silver, gold
  status             text NOT NULL,               -- running, succeeded, failed
  config_fingerprint text NOT NULL,               -- which config this run used
  config_snapshot    jsonb NOT NULL,              -- the exact merged config
  rows_in            bigint, rows_out bigint, rows_quarantined bigint,
  started_at         timestamptz NOT NULL DEFAULT now(),
  ended_at           timestamptz
);
CREATE TABLE watermark (                          -- runtime state, never in the config tables
  dataset_id      uuid PRIMARY KEY REFERENCES dataset_master(dataset_id),
  watermark_value text NOT NULL,
  last_run_id     uuid REFERENCES run_log(run_id),
  updated_at      timestamptz NOT NULL DEFAULT now()
);

Every run stores the fingerprint and a full copy of the settings it ran with. A replay or backfill months later can show exactly what it used.

TableHolds
run_logOne row per run per layer: status, counts, the settings fingerprint and snapshot
watermarkHow far each dataset has been loaded; advanced only after a successful commit
dq_results, dq_violationsEach check's result per run, and the rows that failed
dataset_config_columnsColumn-level settings: types, primary key, PII class, silver name and type, transforms

What goes wrong

Common problems

A new setting needs a database change

Every new option means an ALTER TABLE, a migration and a release, so teams start hard-coding settings in pipelines instead.

Naive fix

Add a column each time and schedule a migration.

Right fix

Key-value settings with a key registry: a new key is a registry entry and a YAML line.

Nobody knows which settings a run used

A number changed last month; the settings have been edited three times since.

Naive fix

Read the current settings and hope they didn't change.

Right fix

Store the fingerprint and snapshot on every run_log row.

Dev settings leak into production

A test bucket path ends up in a prod pipeline.

Naive fix

An env column and careful WHERE clauses.

Right fix

One database per environment, same schema; YAML promoted through CI.

Settings edited directly in the database

Production behaves differently from what is in git, and nobody knows why.

Naive fix

Ask people to be careful.

Right fix

Only the loader writes; it is fed by YAML from git; direct writes are blocked by permissions.

Concept vs product

On each platform

JobAWSAzureGoogle CloudDatabricksOpen source
Metadata databaseRDS or Aurora PostgreSQLAzure Database for PostgreSQLCloud SQL for PostgreSQLLakebase (Postgres)PostgreSQL
Secrets referenced from configSecrets ManagerKey VaultSecret ManagerDatabricks secretsVault or sops
YAML → loader in CIGitHub Actions or CodeBuildGitHub Actions or Azure PipelinesGitHub Actions or Cloud BuildGitHub Actions + bundlesGitHub Actions

Product names change often; check the current docs before you build.

FAQ

Questions people ask

Why not just keep the YAML and skip the database?

YAML is great for writing and reviewing, but pipelines need fast, consistent reads, runtime state next to settings, and joins with run logs. The database gives that; git keeps the history.

Why key-value instead of proper columns?

Settings grow every month. Key-value rows absorb a new setting without a schema change, and the key registry gives back the type safety that columns would have given.

Why one database per environment?

So dev and test can never read or overwrite production settings by mistake, and so promoting a change is a normal release through CI.

What is the config fingerprint for?

It proves which settings a run used. Two runs with the same fingerprint ran with identical settings; a backfill can reuse a past run's snapshot exactly.

Next

Where to go next

↑ The whole picture · The medallion layers · Key concepts · The Data Engineering path

See it run: DataTwin Studio plays this component step by step on a real recorded run, including what happens when the same file arrives twice.