cleandataengineering.aiData Platform, from the big picture to the code
You are here: Start here › Silver to gold › Manifests and models

Data PlatformZoom level 2

Gold manifests and models: what a gold table declares

A gold table is described by two small things. The manifest is a file of settings that says what the table is. The model is the one query or function that holds the table's own business logic. A dimension has only a manifest. A fact has both.

Before anything is read, the harness turns each manifest into a plan, which is one frozen object holding every answer the build needs. If the manifest asks for something the harness cannot do, the plan refuses with a message that names the table, and no data is touched.

Gold manifestgrain, inputs, windowContractcolumns and typesOpen: Input's primary keyInput's primary keyfrom the storezoom in ›plan_from_viewchecks, then buildsGoldPlanfrozenOpen: ModelModelSQL or functionzoom in ›Open: HarnessHarnessruns the planzoom in ›
Three things go in on the left. The plan names the model but does not open it. The harness calls the model later.

A fact's manifest

data-platform-catalog/slices/02-tyre/manifest/gold/fact_otif.yaml:7The manifest of the on-time-in-full fact of the tyre company.
kind: gold_fact
name: fact_otif
domain: sales
pipeline: modelled
contract: ../../contracts/fact_otif.odcs.yaml
contract_version: 0.1.0
grain: [order_id, line_no]
inputs:
  - {dataset: erp.sales_order_line, bookmark: change_seq, role: driver}
  - {dataset: erp.delivery, bookmark: change_seq, role: driver}
dimensions:
  - {name: dim_dealer, key: dealer_sk, business_key: [dealer_code], as_of: order_ts}
  - {name: dim_material, key: material_sk, business_key: [material_code], as_of: order_ts}
keys: {surrogate: hash, unknown_member: -1, not_applicable: -2}
window: {column: promised_date, unit: day, lookback: 35}
build: {kind: sql, ref: gold/sales/fact_otif.sql}
materialize: table
corrections: true
completeness: {on_incomplete: flag}
checks:
  grain_unique: true
  orphan_rate_max: 0.01
  reconcile:
    against: silver
    measures:
      - {name: ordered_qty_total, column: ordered_qty}
    tolerance: {ordered_qty_total: 0}

Read it top to bottom. The grain is what one row stands for: here, one order line. The inputs are the silver tables the model reads. Each entry under dimensions names a gold dimension, the key column the fact will carry, the business key column the fact has, and the fact column whose time decides which version of the member applies. The window names the column the fact is rebuilt by, the size of one window, and how many windows back each run recomputes. The build names the model. The checks are run before anything is published.

A dimension's manifest

data-platform-catalog/slices/02-tyre/manifest/gold/dim_dealer.yaml:3The dealer dimension. There is no build: the common pipeline does all of it.
kind: gold_dimension
name: dim_dealer
domain: sales
pipeline: one_to_one
contract: ../../contracts/dim_dealer.odcs.yaml
contract_version: 0.1.0
grain: [dealer_sk]
inputs:
  - {dataset: dealer_api.dealer, role: driver}
history: scd2
keys: {surrogate: hash, unknown_member: -1, not_applicable: -2}
materialize: table

The keys a gold manifest may hold

The table lists each key, what it means, its allowed values and its default, as plan_from_manifest reads them. Gold keys are not in the settings registry, so the manifest is the only place they are set.

KeyWhat it meansAllowed values and default
kindWhether the table is a dimension or a fact.gold_dimension or gold_fact. Required.
pipelineHow the table is built.one_to_one for a dimension, modelled for a fact. Defaults from the kind.
domainThe domain that owns the table. It names the gold schema, gold_<domain>.Required.
contract, contract_versionThe contract that lists the table's columns and types. The loader refuses a version that does not match the contract file.Required.
grainThe columns that identify one row. For a dimension it is the key column.A list of contract columns.
inputs[].datasetA silver table the build reads, written as source and dataset.A dimension uses its first input only.
dimensions[].name, .keyA gold dimension the fact points at and the key column it fills.The key must be a contract column. The dimension must have a manifest.
dimensions[].business_keyThe fact's columns that hold the member's business key.A list of columns the model returns.
dimensions[].as_ofThe fact column whose time picks the member's version.A column the model returns.
window.column, .unit, .lookbackThe column a fact is rebuilt by, the size of a window, and how many windows back each run recomputes.Unit is hour, day or month. Lookback defaults to 0. A fact must have a window.
build.kind, build.refThe model: a SQL file, or a module and function.sql or dataframe. A fact must have one. A dimension must not.
correctionsWhether recorded corrections are laid over silver before the model runs.True or false. Default false.
completeness.on_incompleteWhat to do with a window whose inputs have not all arrived.flag publishes it marked incomplete. hold keeps it out. Default flag.
checks.grain_uniqueRefuse to publish if two rows share the grain.Default true.
checks.orphan_rate_maxThe largest share of rows allowed on the unknown member.A share, such as 0.01 for one row in a hundred. No default: without it orphans only warn.
checks.reconcile.measures, .toleranceColumns whose totals must equal the model's totals, and how far each may differ.Tolerance defaults to 0.
materializeThe physical form.Anything other than table must have one input and no corrections.

The plan object

src/dataplatform/gold/plan.py:67Every field of a gold plan. It is frozen: nothing downstream can change it.
@dataclass(frozen=True)
class GoldPlan:
    name: str
    domain: str
    kind: str  # gold_dimension | gold_fact
    pipeline: str  # one_to_one | modelled
    columns: tuple[tuple[str, str], ...]  # contract (name, logical type), in contract order
    grain: tuple[str, ...]
    inputs: tuple[Input, ...]
    dimensions: tuple[DimRef, ...] = ()
    history: str = ""
    window: Window | None = None
    build_kind: str = ""
    build_ref: str = ""
    corrections: bool = False
    on_incomplete: str = "flag"
    grain_unique: bool = True
    orphan_rate_max: float | None = None
    reconcile: Reconcile | None = None
    unknown_member: int = -1
    not_applicable: int = -2
    business_key: tuple[str, ...] = ()  # a dimension's: its input's primary key (the store says it, not the manifest)

Most fields are the manifest's keys under a typed name. Three are worked out. pipeline defaults from the kind. columns are the contract's columns with each logical type turned into an engine type. business_key is filled only for a dimension, and it does not come from the manifest at all. It is the primary key of the dimension's input, read from the metadata store, so the key of a dealer is stated in one place only.

The checks, in the order the code makes them

src/dataplatform/gold/plan.py:136The first half of plan_from_manifest: every refusal.
def plan_from_manifest(manifest: dict[str, Any], columns: list[tuple[str, str]],
                       input_keys: dict[str, list[str]] | None = None) -> GoldPlan:
    """The plan for one gold manifest. Refuses what the DPM linter refuses (ADR-003, ADR-006). `input_keys` maps an
    input dataset to its primary key (from the metadata store): a dimension's business key is its input's key."""
    name, kind = str(manifest.get("name")), str(manifest.get("kind"))
    if kind not in KINDS:
        raise GoldPlanError(f"{name}: kind {kind!r} is not one of {sorted(KINDS)}")
    pipeline = str(manifest.get("pipeline", KINDS[kind]))
    build = manifest.get("build") or {}
    if pipeline == "one_to_one" and build:
        raise GoldPlanError(f"{name}: a one-to-one table declares no build (ADR-003)")
    if pipeline == "modelled" and not build.get("ref"):
        raise GoldPlanError(f"{name}: a modelled fact needs a build reference (ADR-003)")
    if build and build.get("kind") not in BUILD_KINDS:
        raise GoldPlanError(f"{name}: build kind {build.get('kind')!r} is not one of {BUILD_KINDS}")
    if manifest.get("materialize", "table") != "table" and (
        len(manifest.get("inputs") or []) > 1 or manifest.get("corrections")
    ):
        raise GoldPlanError(f"{name}: a materialised view has one input and no corrections (ADR-006)")
    w = manifest.get("window")
    window = Window(str(w["column"]), str(w["unit"]), int(w.get("lookback", 0))) if w else None
    if window and window.unit not in UNITS:
        raise GoldPlanError(f"{name}: window unit {window.unit!r} is not one of {UNITS}")
    if pipeline == "modelled" and window is None:
        raise GoldPlanError(f"{name}: a fact built by window recompute needs a window (ADR-006)")
    cols = tuple(columns)
    names = {c for c, _ in cols}
    if window and window.column not in names:
        raise GoldPlanError(f"{name}: window column {window.column} is not a contract column")
    dims = _dims(manifest.get("dimensions") or [])
    missing = [d.key for d in dims if d.key not in names]
    if missing:
        raise GoldPlanError(f"{name}: dimension keys {missing} are not contract columns")
    checks = manifest.get("checks") or {}
    keys = manifest.get("keys") or {}
    on_inc = str((manifest.get("completeness") or {}).get("on_incomplete", "flag"))
    if on_inc not in ON_INCOMPLETE:
        raise GoldPlanError(f"{name}: completeness.on_incomplete {on_inc!r} is not one of {ON_INCOMPLETE}")
  1. Unknown kind. The kind is not a gold dimension or a gold fact. Refused.
  2. A model on a dimension. A one-to-one table declares a build. Refused, because the common pipeline is the only way a dimension is made.
  3. A fact without a model. A modelled table has no build reference. Refused.
  4. Unknown model kind. The build kind is not sql or dataframe. Refused.
  5. A materialised view that needs more. The table is not a plain table and has two inputs or corrections. Refused.
  6. Window problems. The unit is not hour, day or month, a fact has no window, or the window column is not a contract column. Refused.
  7. Dimension keys not in the contract. A fact names a key column its contract does not have. Refused.
  8. Unknown completeness choice. on_incomplete is not flag or hold. Refused.
  9. A dimension whose key is missing. The input's primary key is unknown, or is not among the dimension's contract columns. Refused.
src/dataplatform/gold/plan.py:199A dimension's business key is its input's primary key, and it must be in the dimension's contract.
def _dim_key(name: str, kind: str, manifest: dict[str, Any], names: set[str],
             input_keys: dict[str, list[str]]) -> tuple[str, ...]:
    if kind != "gold_dimension":
        return ()
    driver = str((manifest.get("inputs") or [{}])[0].get("dataset", ""))
    key = tuple(input_keys.get(driver) or ())
    if not key or set(key) - names:
        raise GoldPlanError(f"{name}: its input {driver}'s primary key {list(key)} is not in the dimension's contract")
    return key

From the manifest file to the harness

The loader writes each gold manifest into the metadata store as a dataset, beside the source datasets. Its source name is the gold schema, gold_sales for the sales domain, and its primary key is the grain. Because gold tables are datasets in the store, their runs, steps and bookmarks sit in the same run log as ingestion and silver.

src/dataplatform/meta/loader.py:189A gold manifest as a dataset of the store. Settings stay empty.
def gold_doc(domain: str, manifest: dict[str, Any], contract: dict[str, Any]) -> dict[str, Any]:
    """A gold manifest as a dataset of the store: source `gold_<manifest domain>` (its gold schema, ADR-012), the
    contract's columns, the grain as its primary key. Settings stay empty: gold has no registered keys yet."""
    plain = json.loads(json.dumps(manifest, default=str))
    return {
        "domain": domain,
        "source_name": f"gold_{manifest['domain']}",
        "source_env": "",
        "dataset_name": manifest["name"],
        "is_enabled": True,
        "kind": manifest["kind"],
        "archetypes": [],
        "ingest_mode": "",
        "ingest_read": "",
        "history_kind": str(manifest.get("history", "")),
        "load_pattern": "window_recompute" if manifest["kind"] == "gold_fact" else "replace",
        "contract_id": contract["id"],
        "contract_version": str(contract["version"]),
        "fingerprint": config_fingerprint({"manifest": plain,
                                           "contract": json.loads(json.dumps(contract, default=str))}),
        "settings": {},
        "columns": columns_of(contract),
        "business_keys": {"primary": list(manifest.get("grain") or []), "dedup": []},
        "manifest": plain,
    }
src/dataplatform/gold/harness.py:88The harness reads every enabled gold dataset, fetches each input's primary key, and builds the plans.
def plans(store: MetadataStore, domain: str) -> list[GoldPlan]:
    """Every enabled gold dataset's plan, from the store; dimensions first."""
    out = []
    for ref in store.datasets(domain):
        if ref.kind not in GOLD_KINDS or not ref.is_enabled:
            continue
        view = store.dataset_view(ref.source, ref.dataset)
        drivers = [str(i["dataset"]) for i in view["manifest"].get("inputs") or ()]
        keys = {d: store.business_keys(*d.split(".", 1)) for d in drivers}
        out.append(plan_from_view(view, keys))
    return order(out)
src/dataplatform/gold/plan.py:210Dimensions first, then facts, each group by name.
def order(plans: list[GoldPlan]) -> list[GoldPlan]:
    """Dimensions first, then facts; each group by name. A fact naming a dimension no plan builds is refused."""
    dims = {p.name for p in plans if p.kind == "gold_dimension"}
    for p in plans:
        unknown = [d.name for d in p.dimensions if d.name not in dims]
        if unknown:
            raise GoldPlanError(f"{p.name}: dimensions {unknown} have no gold manifest")
    return sorted(plans, key=lambda p: (p.kind != "gold_dimension", p.name))

A model

A model is the only code a person writes for a fact. It is pure, which means it reads only the inputs it is handed and writes nothing. A SQL model reads its inputs as views named by source and dataset joined with two underscores, such as erp__sales_order_line. It may use the named parameter :business_tz, the business time zone.

models/tyre/gold/sales/fact_otif.sql:6The model of the on-time-in-full fact. Its manifest is the first box on this page.
WITH first_promise AS (  -- the date first promised: the line's first version in the source order (ADR-016)
    SELECT order_id, line_no, promised_date
    FROM (
        SELECT order_id, line_no, promised_date,
               ROW_NUMBER() OVER (PARTITION BY order_id, line_no ORDER BY _seq_from, _valid_from) AS version_no
        FROM erp__sales_order_line
    ) AS versions
    WHERE version_no = 1
),

open_lines AS (  -- current, not deleted, still open: cancelled and rejected lines are left out (contract)
    SELECT order_id, line_no, dealer_code, material_code, ordered_qty, order_ts
    FROM erp__sales_order_line
    WHERE _is_current AND NOT _is_deleted AND line_status = 'open'
),

delivered AS (
    SELECT order_id, line_no,
           SUM(delivered_qty) AS delivered_qty,
           MAX(delivered_ts) AS delivered_ts
    FROM erp__delivery
    WHERE _is_current AND NOT _is_deleted
    GROUP BY order_id, line_no
),

judged AS (
    SELECT o.order_id, o.line_no, o.dealer_code, o.material_code, o.ordered_qty, o.order_ts,
           f.promised_date,
           COALESCE(d.delivered_qty, 0) AS delivered_qty,
           COALESCE(TO_DATE(FROM_UTC_TIMESTAMP(d.delivered_ts, :business_tz)) <= f.promised_date, FALSE) AS on_time,
           COALESCE(d.delivered_qty, 0) >= o.ordered_qty AS in_full
    FROM open_lines AS o
    INNER JOIN first_promise AS f ON o.order_id = f.order_id AND o.line_no = f.line_no
    LEFT JOIN delivered AS d ON o.order_id = d.order_id AND o.line_no = d.line_no
)

SELECT order_id, line_no, dealer_code, material_code, promised_date, ordered_qty, delivered_qty,
       on_time, in_full,
       CASE WHEN on_time AND in_full THEN 1 ELSE 0 END AS on_time_in_full,
       order_ts
FROM judged

Four things to notice. First, the model receives every silver row, including old versions and deleted rows, so it filters for itself with _is_current AND NOT _is_deleted. Second, it uses the old versions on purpose: first_promise takes the promised date of the line's first version, so a later reschedule does not move the goalpost. Third, it returns business keys such as dealer_code, not gold keys. The harness adds dealer_sk. Fourth, it returns order_ts, which is not in the fact's contract. The harness needs it to pick the dealer's version, and drops it when it puts the columns in contract order.

A dataframe model is a function with a fixed signature. Facts shows the one the tyre company has, models/tyre/gold/manufacturing/scrap.py.

A worked example: the plan of the on-time-in-full fact

The left column is a field of the plan. The right column is its value for fact_otif, read from the manifest above.

tablegold_sales.fact_otif
pipelinemodelled
grainorder_id, line_no
inputserp.sales_order_line, erp.delivery
dimensionsdim_dealer by dealer_code as of order_ts, dim_material by material_code as of order_ts
windowpromised_date, one day, 35 days back
buildSQL, gold/sales/fact_otif.sql
correctionstrue
on_incompleteflag
orphan_rate_max0.01, which is one row in a hundred
reconcileordered_qty, tolerance 0

How it is tested

tests/dataplatform/test_gold_plan.py tests the plan without any engine. One table of cases holds every refusal with a piece of its message. Another test builds the plan of every real tyre manifest.

tests/dataplatform/test_gold_plan.py:36The refusals as a table: a name, a manifest with one thing wrong, and the words the message must contain.
        ("one-to-one with a build", with_(DIM, build={"kind": "sql", "ref": "d.sql"}), DIM_COLS, "declares no build"),
        ("modelled without a build", with_(FACT, build={}), COLS, "needs a build reference"),
        ("unknown build kind", with_(FACT, build={"kind": "notebook", "ref": "n"}), COLS, "build kind"),
        ("modelled without a window", with_(FACT, window=None), COLS, "needs a window"),
        ("window unit unknown", with_(FACT, window={"column": "day", "unit": "week"}), COLS, "window unit"),
        ("window column not in contract", with_(FACT, window={"column": "dt", "unit": "day"}), COLS, "window column"),
        ("dimension key not in contract", with_(FACT, dimensions=[{"name": "dim_d", "key": "d_sk"}]), COLS,
         "dimension keys"),
        ("materialised view with two inputs", with_(FACT, materialize="materialized_view",
                                                    inputs=[{"dataset": "a.b"}, {"dataset": "c.d"}]), COLS,
         "materialised view"),
        ("unknown kind", with_(FACT, kind="gold_view"), COLS, "kind"),
        ("on_incomplete unknown", with_(FACT, completeness={"on_incomplete": "drop"}), COLS, "on_incomplete"),
        ("dimension key missing in its input", DIM, DIM_COLS, "primary key"),
    ],
)
def test_the_linter_refuses(case, doc, cols, problem):
    with pytest.raises(GoldPlanError, match=problem):
        plan_from_manifest(doc, cols, {})
tests/dataplatform/test_gold_plan.py:76Every tyre gold manifest becomes a plan, and the three dimensions sort first.
@needs_slice
def test_every_tyre_gold_manifest_is_a_plan():
    import yaml

    from dataplatform import contract as odcs

    keys = {"dealer_api.dealer": ["dealer_code"], "erp.material": ["material_code"], "mes.press": ["press_id"]}
    plans = []
    for path in sorted((SLICE / "manifest/gold").glob("*.yaml")):
        m = yaml.safe_load(path.read_text())
        c = yaml.safe_load((path.parent / m["contract"]).read_text())
        plans.append(plan_from_manifest(m, odcs.columns(c), keys))
    by = {p.name: p for p in order(plans)}
    assert [p.kind for p in order(plans)][:3] == ["gold_dimension"] * 3
    assert (by["fact_otif"].build_kind, by["fact_sales"].window.unit, by["fact_scrap"].build_kind) == (
        "sql", "month", "dataframe")
    assert by["fact_otif"].corrections and not by["fact_scrap"].corrections
Not built yet, or known gaps
  • Some manifest keys are read into the plan, or written in the manifest, and then used by nothing. history on a dimension, the role and bookmark of an input, keys.surrogate, and checks.reconcile.against are in this group. The values keys.unknown_member and keys.not_applicable reach the plan, but the engine uses the constants -1 and -2 from the keys module whatever the manifest says.
  • Gold has no keys in the settings registry, so a gold value cannot be set per domain or per environment the way a silver setting can.
  • The manifest cannot say that an input arrives as one file per member, such as one file per dealer. The harness works it out from ingestion's bookmarks.
  • The reconcile check says against: silver in the manifest, but the harness compares with the model's own output, not with silver.