cleandataengineering.aiData Platform, from the big picture to the code
You are here: Start here › Metadata store › Config data model

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.

domaintyredomain_settingdefaultssourceerp, mes, ...dataseterp.materialdataset_settingkey and valuedataset_columnfrom the contractbusiness_keyprimary, dedupdq_rulefrom the contractsetting_defthe dictionaryhashas
Read it left to right. The highlighted box is the dataset, the centre of the model. A business key points at a column of the same dataset. A setting row points at the dictionary of allowed settings.

Goes in, what happens, comes out

Goes inOne document per domain and one per dataset, built by the loader from the YAML manifest and the data contract.
What happensThe functions config.load_domain and config.load_dataset check the document and replace the dataset's rows whole, in one transaction.
Comes outRows in the nine tables, read back only through functions such as 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.

TableRowsContent
config.domain1tyre, business time zone Asia/Kolkata, environments dev and prod.
config.source1erp, environment prod. The row is shared with the two other ERP datasets.
config.dataset1material, 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_setting10The 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_column6material_code, description, compound_code, rim_size_in, tyre_class, extracted_at, in the contract's order.
config.business_key2Kind primary, position 1, material_code. Kind dedup, position 1, material_code.
config.dq_ruleOne per quality rule in the contractThe 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.

ColumnType and ruleWhat it is for
scopeText. One of platform, source, dataset.The level the setting belongs to.
keyText.The setting's name, for example retry_count.
value_typeText.The kind of value, for example int, bool, enum.
default_valueText, may be empty.The value used when nothing sets the key.
allowedArray of text, may be empty.The list of permitted values for an enum.
descriptionText.A sentence for people.
statusText. 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.

ColumnType and ruleWhat it is for
domainText. Primary key.The domain's name.
business_timezoneText, required.The time zone in which a business day is counted.
environmentsArray of text.The environments the domain runs in.
manifestJSON, required.The whole platform.yaml as written, kept for reference.
fingerprintText, required.A short hash of the manifest. It changes when the file changes.
loaded_atTimestamp.When the loader last wrote the row.

config.domain_setting: defaults for a whole domain

ColumnType and ruleWhat it is for
domainText. Foreign key to config.domain. Deleted with its domain.The domain the default applies to.
scopeText.The scope under which the key was found in the dictionary: dataset or platform.
keyText.The setting's name.
valueText, 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 ''
);
ColumnType and ruleWhat it is for
source_idNumber assigned by the database. Primary key.The id datasets point at.
domainText. Foreign key to config.domain.The domain the source belongs to.
source_nameText, unique across the whole store.The name used everywhere, for example erp.
source_envText.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)
);
ColumnType and ruleWhat it is for
dataset_idNumber assigned by the database. Primary key.The id every other table points at.
source_idForeign key to config.source.The source the dataset comes from.
dataset_nameText. Unique within its source.The dataset's name, for example sales_order_line.
is_enabledBoolean, required.Runs skip a dataset that is switched off.
kindText, 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.
archetypesArray of text.The pipeline archetypes the manifest names, for example PA2.
ingest_modeText.push, pull or stream.
ingest_readText.How the source is read. With the mode it picks the ingestion pipeline. See Five pipelines.
history_kindText.master, transaction, event or snapshot. Silver picks the history type from it.
load_patternText.The manifest's load.pattern.
contract_id, contract_versionText, required.The data contract the columns were taken from.
manifestJSON, required.The whole dataset manifest. Pipelines read the load, volume and history blocks from here.
fingerprintText, required.A short hash of the manifest and the contract together.
loaded_atTimestamp.When the loader last wrote the row.

config.dataset_setting: a dataset's own setting values

ColumnType and ruleWhat it is for
dataset_idForeign key to config.dataset. Deleted with its dataset.The dataset.
scopeText. Always dataset, enforced by a check.Lets the row point at the dictionary.
keyText.The setting's name.
valueText, 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

ColumnType and ruleWhat it is for
dataset_idForeign key to config.dataset. Deleted with its dataset.The dataset.
ordinalWhole number. Primary key with the dataset id.The column's position in the contract.
column_nameText. Unique within the dataset.The column's name.
logical_typeText, required.The contract's type: string, integer, number, timestamp, date or boolean.
is_requiredBoolean.Whether the contract says the column must have a value.
primary_key_positionWhole number, may be empty.The column's place in the primary key, if it is part of it.
descriptionText.The contract's description.
classificationText.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
);
ColumnType and ruleWhat it is for
dataset_idWhole number.The dataset.
key_kindText. primary or dedup.The primary key identifies a row. The dedup key is the set of columns used to remove duplicates.
ordinalWhole number.The column's position within the key.
column_nameText. Foreign key, with the dataset id, to config.dataset_column.The column.

config.dq_rule: a dataset's quality rules

ColumnType and ruleWhat it is for
dataset_idForeign key to config.dataset.The dataset.
rule_idText. Primary key with the dataset id.Built by the loader as column, check and a counter, for example ordered_qty-sql-1.
column_nameText, may be empty.The column the rule is on. Empty for a rule on the whole row or batch.
check_nameText.The name of the check.
paramsJSON.The check's own fields from the contract.
severityText. critical, major or minor.How bad a failure is.
actionText. quarantine, stop or warn.What a failure causes. The loader derives it from the severity: critical stops, major quarantines, minor warns.
enabledBoolean.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.

ColumnManifest fieldValues in the tyre manifests
ingest_modeingest.modepush, pull, stream
ingest_readingest.readchange_feed, files, full_copy, pages, events
history_kindhistory.kindmaster, transaction, event, snapshot
load_patternload.patternhistory, among others
kindkinddataset, 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"]),
Not built yet, or known gaps
  • 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_key and config.dq_rule do 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 example gold_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.