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.
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.
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
| Table | Tier | One row per | Why it exists |
|---|---|---|---|
| platform_config | settings | platform-wide setting | Defaults for everything: time zone, retry count, alert channel |
| source_master | master | upstream system | A database, an API, a bucket, a topic |
| source_config | settings | setting of one source | Key-value, grouped (ingestion, quality, cost); new keys need no ALTER TABLE |
| dataset_master | master | dataset of a source | A table, a file pattern, a stream |
| dataset_config | settings | setting of one dataset | Overrides 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 setis_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.
| Table | Holds |
|---|---|
| run_log | One row per run per layer: status, counts, the settings fingerprint and snapshot |
| watermark | How far each dataset has been loaded; advanced only after a successful commit |
| dq_results, dq_violations | Each check's result per run, and the rows that failed |
| dataset_config_columns | Column-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
| Job | AWS | Azure | Google Cloud | Databricks | Open source |
|---|---|---|---|---|---|
| Metadata database | RDS or Aurora PostgreSQL | Azure Database for PostgreSQL | Cloud SQL for PostgreSQL | Lakebase (Postgres) | PostgreSQL |
| Secrets referenced from config | Secrets Manager | Key Vault | Secret Manager | Databricks secrets | Vault or sops |
| YAML → loader in CI | GitHub Actions or CodeBuild | GitHub Actions or Azure Pipelines | GitHub Actions or Cloud Build | GitHub Actions + bundles | GitHub 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.