Data PlatformZoom level 2
The config data model: every table and every column
The config schema is the platform's description of what it must load. It has nine tables. Every pipeline decision that differs between datasets is read from them.
The model is small on purpose. A dataset is one row with a handful of typed columns for the facts every pipeline needs, a key and value table for the many optional settings, and the whole manifest kept as a JSON document for everything else.
Goes in, what happens, comes out
config.load_domain and config.load_dataset check the document and replace the dataset's rows whole, in one transaction.config.dataset_view.A worked example: the materials dataset as rows
The tyre company's material master comes from the manifest file data-platform-catalog/slices/02-tyre/manifest/datasets/erp.material.yaml. After the loader runs, the store holds these rows for it. The first column is the table, the second is how many rows, the third is what they say.
| Table | Rows | Content |
|---|---|---|
config.domain | 1 | tyre, business time zone Asia/Kolkata, environments dev and prod. |
config.source | 1 | erp, environment prod. The row is shared with the two other ERP datasets. |
config.dataset | 1 | material, enabled, kind dataset, ingest mode pull, ingest read full_copy, history kind master, load pattern history, contract tyre-erp-material version 0.1.0. |
config.dataset_setting | 10 | The seven keys of the manifest's config block, such as load_type = FULL, and the three keys of its dedup block, such as dedup.order_by = extracted_at. |
config.dataset_column | 6 | material_code, description, compound_code, rim_size_in, tyre_class, extracted_at, in the contract's order. |
config.business_key | 2 | Kind primary, position 1, material_code. Kind dedup, position 1, material_code. |
config.dq_rule | One per quality rule in the contract | The rules the contract declares on rim_size_in and tyre_class. |
The tables, one at a time
config.setting_def: the dictionary of allowed settings
One row per setting that a manifest may use. Settings and the merge explains it in full. Its primary key is (scope, key), so the same key can exist at two scopes.
| Column | Type and rule | What it is for |
|---|---|---|
scope | Text. One of platform, source, dataset. | The level the setting belongs to. |
key | Text. | The setting's name, for example retry_count. |
value_type | Text. | The kind of value, for example int, bool, enum. |
default_value | Text, may be empty. | The value used when nothing sets the key. |
allowed | Array of text, may be empty. | The list of permitted values for an enum. |
description | Text. | A sentence for people. |
status | Text. registered or proposed. | Whether the key is settled or still a proposal from the specifications. |
config.domain: one row per domain
A domain is one business area with its own manifest folder. The tyre company is one domain.
| Column | Type and rule | What it is for |
|---|---|---|
domain | Text. Primary key. | The domain's name. |
business_timezone | Text, required. | The time zone in which a business day is counted. |
environments | Array of text. | The environments the domain runs in. |
manifest | JSON, required. | The whole platform.yaml as written, kept for reference. |
fingerprint | Text, required. | A short hash of the manifest. It changes when the file changes. |
loaded_at | Timestamp. | When the loader last wrote the row. |
config.domain_setting: defaults for a whole domain
| Column | Type and rule | What it is for |
|---|---|---|
domain | Text. Foreign key to config.domain. Deleted with its domain. | The domain the default applies to. |
scope | Text. | The scope under which the key was found in the dictionary: dataset or platform. |
key | Text. | The setting's name. |
value | Text, required. | The domain's value. |
The primary key is (domain, key). The pair (scope, key) is a foreign key to config.setting_def, so a row cannot exist for a key the dictionary does not have.
config.source: one row per source system
src/dataplatform/adapters/sql/postgres_meta.sql:55The whole table. It has four columns and nothing else.CREATE TABLE IF NOT EXISTS config.source (
source_id BIGSERIAL PRIMARY KEY,
domain TEXT NOT NULL REFERENCES config.domain (domain),
source_name TEXT NOT NULL UNIQUE,
source_env TEXT NOT NULL DEFAULT ''
);| Column | Type and rule | What it is for |
|---|---|---|
source_id | Number assigned by the database. Primary key. | The id datasets point at. |
domain | Text. Foreign key to config.domain. | The domain the source belongs to. |
source_name | Text, unique across the whole store. | The name used everywhere, for example erp. |
source_env | Text. | Which environment of the source system this is, for example prod. |
A source row is not loaded on its own. config.load_dataset creates it the first time a dataset names the source. There is no place on this table for connection details or other source-level settings. Settings and the merge says what that means.
config.dataset: one row per dataset
src/dataplatform/adapters/sql/postgres_meta.sql:62The centre of the model. The typed columns are the facts every pipeline asks for.CREATE TABLE IF NOT EXISTS config.dataset (
dataset_id BIGSERIAL PRIMARY KEY,
source_id BIGINT NOT NULL REFERENCES config.source (source_id),
dataset_name TEXT NOT NULL,
is_enabled BOOLEAN NOT NULL,
kind TEXT NOT NULL,
archetypes TEXT [] NOT NULL DEFAULT '{}',
ingest_mode TEXT NOT NULL DEFAULT '',
ingest_read TEXT NOT NULL DEFAULT '',
history_kind TEXT NOT NULL DEFAULT '',
load_pattern TEXT NOT NULL DEFAULT '',
contract_id TEXT NOT NULL,
contract_version TEXT NOT NULL,
manifest JSONB NOT NULL,
fingerprint TEXT NOT NULL,
loaded_at TIMESTAMPTZ NOT NULL DEFAULT now(),
UNIQUE (source_id, dataset_name)
);| Column | Type and rule | What it is for |
|---|---|---|
dataset_id | Number assigned by the database. Primary key. | The id every other table points at. |
source_id | Foreign key to config.source. | The source the dataset comes from. |
dataset_name | Text. Unique within its source. | The dataset's name, for example sales_order_line. |
is_enabled | Boolean, required. | Runs skip a dataset that is switched off. |
kind | Text, required. | dataset for a source dataset. gold_dimension or gold_fact for a gold table, which sits in the same table. Ingestion and silver skip the gold kinds. |
archetypes | Array of text. | The pipeline archetypes the manifest names, for example PA2. |
ingest_mode | Text. | push, pull or stream. |
ingest_read | Text. | How the source is read. With the mode it picks the ingestion pipeline. See Five pipelines. |
history_kind | Text. | master, transaction, event or snapshot. Silver picks the history type from it. |
load_pattern | Text. | The manifest's load.pattern. |
contract_id, contract_version | Text, required. | The data contract the columns were taken from. |
manifest | JSON, required. | The whole dataset manifest. Pipelines read the load, volume and history blocks from here. |
fingerprint | Text, required. | A short hash of the manifest and the contract together. |
loaded_at | Timestamp. | When the loader last wrote the row. |
config.dataset_setting: a dataset's own setting values
| Column | Type and rule | What it is for |
|---|---|---|
dataset_id | Foreign key to config.dataset. Deleted with its dataset. | The dataset. |
scope | Text. Always dataset, enforced by a check. | Lets the row point at the dictionary. |
key | Text. | The setting's name. |
value | Text, required. | The value. Every value is stored as text, whatever its type. |
The primary key is (dataset_id, key). The pair (scope, key) is a foreign key to config.setting_def.
config.dataset_column: the contract's columns
| Column | Type and rule | What it is for |
|---|---|---|
dataset_id | Foreign key to config.dataset. Deleted with its dataset. | The dataset. |
ordinal | Whole number. Primary key with the dataset id. | The column's position in the contract. |
column_name | Text. Unique within the dataset. | The column's name. |
logical_type | Text, required. | The contract's type: string, integer, number, timestamp, date or boolean. |
is_required | Boolean. | Whether the contract says the column must have a value. |
primary_key_position | Whole number, may be empty. | The column's place in the primary key, if it is part of it. |
description | Text. | The contract's description. |
classification | Text. | The contract's personal-data class. A column classed restricted is replaced by a token before bronze. |
config.business_key: which columns identify a row
src/dataplatform/adapters/sql/postgres_meta.sql:104A key column must be a column of the same dataset. The foreign key enforces it.CREATE TABLE IF NOT EXISTS config.business_key (
dataset_id BIGINT NOT NULL,
key_kind TEXT NOT NULL CHECK (key_kind IN ('primary', 'dedup')),
ordinal INT NOT NULL,
column_name TEXT NOT NULL,
PRIMARY KEY (dataset_id, key_kind, ordinal),
FOREIGN KEY (dataset_id, column_name) REFERENCES config.dataset_column (dataset_id, column_name)
ON DELETE CASCADE
);| Column | Type and rule | What it is for |
|---|---|---|
dataset_id | Whole number. | The dataset. |
key_kind | Text. primary or dedup. | The primary key identifies a row. The dedup key is the set of columns used to remove duplicates. |
ordinal | Whole number. | The column's position within the key. |
column_name | Text. Foreign key, with the dataset id, to config.dataset_column. | The column. |
config.dq_rule: a dataset's quality rules
| Column | Type and rule | What it is for |
|---|---|---|
dataset_id | Foreign key to config.dataset. | The dataset. |
rule_id | Text. Primary key with the dataset id. | Built by the loader as column, check and a counter, for example ordered_qty-sql-1. |
column_name | Text, may be empty. | The column the rule is on. Empty for a rule on the whole row or batch. |
check_name | Text. | The name of the check. |
params | JSON. | The check's own fields from the contract. |
severity | Text. critical, major or minor. | How bad a failure is. |
action | Text. quarantine, stop or warn. | What a failure causes. The loader derives it from the severity: critical stops, major quarantines, minor warns. |
enabled | Boolean. | Whether the rule runs. |
The settings this part reads
The model itself reads no setting. It is where settings are kept. The typed columns of config.dataset are filled from these manifest fields.
| Column | Manifest field | Values in the tyre manifests |
|---|---|---|
ingest_mode | ingest.mode | push, pull, stream |
ingest_read | ingest.read | change_feed, files, full_copy, pages, events |
history_kind | history.kind | master, transaction, event, snapshot |
load_pattern | load.pattern | history, among others |
kind | kind | dataset, gold_dimension, gold_fact |
The code that writes a dataset
src/dataplatform/adapters/sql/postgres_meta.sql:397The second half of config.load_dataset. The dataset's child rows are deleted and written again in the same transaction. -- the dataset's settings, columns and keys are replaced whole, in this transaction
DELETE FROM config.business_key WHERE dataset_id = ds;
DELETE FROM config.dataset_column WHERE dataset_id = ds;
DELETE FROM config.dataset_setting WHERE dataset_id = ds;
FOR k, v IN SELECT * FROM jsonb_each_text(COALESCE(p_doc -> 'settings', '{}')) LOOP
INSERT INTO config.dataset_setting (dataset_id, key, value) VALUES (ds, k, v);
END LOOP;
INSERT INTO config.dataset_column (dataset_id, ordinal, column_name, logical_type, is_required,
primary_key_position, description, classification)
SELECT ds, c.ord, c.x ->> 'name', c.x ->> 'logical_type', COALESCE((c.x ->> 'required')::BOOLEAN, FALSE),
(c.x ->> 'primary_key_position')::INT, COALESCE(c.x ->> 'description', ''),
COALESCE(c.x ->> 'classification', '')
FROM jsonb_array_elements(COALESCE(p_doc -> 'columns', '[]')) WITH ORDINALITY AS c (x, ord);
FOR kind, cols IN SELECT * FROM jsonb_each(COALESCE(p_doc -> 'business_keys', '{}')) LOOP
i := 0;
FOR k IN SELECT jsonb_array_elements_text(cols) LOOP
i := i + 1;
INSERT INTO config.business_key (dataset_id, key_kind, ordinal, column_name) VALUES (ds, kind, i, k);
END LOOP;
END LOOP;
RETURN ds;Walk through it. The three deletes clear the dataset's keys, columns and settings. The order matters: keys go first because they point at columns. The settings are inserted one row per key. The columns are inserted in one statement, and WITH ORDINALITY numbers them in the order the document lists them, which is the contract's order. The keys are inserted last with a counter per kind. Because the function is one transaction, a reader never sees a dataset with half its columns.
How it is tested
The contract table in tests/dataplatform/test_meta_store.py has cases for the model: columns come back in contract order, keys come back in position order, a business key that names a missing column is refused, and loading a dataset again leaves one row and replaces its settings whole.
tests/dataplatform/test_meta_store.py:190Two real cases. After a reload with fewer settings, the dropped key falls back to its default. ("load: a reload replaces settings whole",
seq(lambda s: s.load_dataset(dataset(settings={"load_type": "FULL"})), lambda s: setting(s, "retry_count")),
("2", "default")),
("load: a reload keeps one row per dataset",
seq(lambda s: s.load_dataset(dataset(fingerprint="fp-2")), lambda s: [d.fingerprint for d in s.datasets("shop")]),
["fp-material", "fp-2"]),- A source name is unique across the whole store, not within a domain. If a second domain loaded a dataset under a source name that already exists, the existing source row would be reused and would keep the first domain. Only one domain is loaded today, so this has not happened.
- A dataset that is removed from the manifest is not removed from the store. The loader reports it and leaves the rows. There is no function that deletes a dataset.
config.business_keyandconfig.dq_ruledo not cascade from the dataset in the same way as the other child tables. Since no function deletes a dataset, nothing depends on it yet.- Gold tables are stored as datasets under a source named
gold_plus the gold manifest's domain, for examplegold_sales. The word source is stretched to cover them. - The store does not check a value against the dictionary's type or allowed list. The loader does, and only as a warning. See Settings and the merge.