Data-vault and dimensional derivation

Derivation transforms a normalized logical and physical model into reviewable warehouse structures. The operation only changes model declarations: it does not read row values, generate ETL or load any destination.

Prerequisites

Start with a checked model containing logical entities, their mapped physical tables and mapped columns in one relational source target. Source tables and attributes must have consistent types and keys. Choose a distinct destination target and schema. The destination dialect matches the source.

Vault conventions

Vault derivation selects an explicit, ordered, non-null business key for each participating entity. A hub hub_<entity> contains the deterministic <entity>_hk character(64) hash key, business key columns, non-null load date and record source metadata. Each satellite sat_<entity> contains descriptive attributes, hub key, load date and record source, with a hub foreign key. Links lnk_<relationship> contain endpoint hub keys and their own key. The documented row-hashing convention is lowercase SHA-256 of length-prefixed, null-marked UTF-8 values. The phase stores the rule as provenance; hashing actual rows is the responsibility of a downstream pipeline.

Star conventions

Star derivation selects dimensions and fact event entities explicitly. Type 1 dimensions retain the logical business key as primary key. Type 2 dimensions additionally use a bigint surrogate key, effective start/end, a current-row flag, a uniqueness rule across business key and effective start, and an index for current-row lookup. Facts have a generated bigint key, specified dimension keys and numeric measures in source declaration order. Dimension relationships must have exactly one shortest relationship chain from each fact event, with optionality carried into the key column nullability. No implicit time dimensions, aggregates or bridges are generated.

CLI examples

Preview without writing:

bluep derive vault devdata/models/derivation --source-target normalized --target vault --schema vault --business-key customer=id --business-key sale=id
bluep derive star devdata/models/derivation --source-target normalized --target star --schema mart --dimension customer=type2 --fact sale=total:customer

Apply after reviewing the output by appending --write. Add --json to retrieve the deterministic machine-readable change report. Vault-specific options are --hash-key-suffix, --load-date and --record-source. Add --area <name> for a generated subject area.

Preview, review and write

Without --write, the command produces a read-only plan and exits with status 1 if changes are pending, 0 if unchanged. With --write, the command makes minimal atomic edits and reports the changed files. In the web editor, preview changes with the Derive panel before using its explicit Apply action. Apply checks the current model fingerprint and refuses a stale preview. Preview permissions are View; applying requires Edit.

Provenance and lineage

Every generated logical entity, attribute, table and column records its derivation kind, stable key, ordered source selectors, rule and SHA-256 signature. Source selectors can be used to trace provenance through impact and lineage views. Generated column mappings refer to the originating source column. Recomputed signatures include shape-affecting source and option information but exclude layout and source file locations.

Idempotence and conflicts

The engine matches prior generated objects by stable derivation key, never by display name. Repeating the same derivation gives only Unchanged decisions and the human-readable summary No derivation changes.. A source or option change may update or remove owned declarations. Unowned objects with desired names are never overwritten. All conflicts and validation errors abort without mutating the original model.

Git workflow

Record the source model, derivation options and resulting reviewed diff in the same branch. Preview after merging upstream changes; re-apply explicitly when the source evolves. Review object changes and provenance in code review before deploying any generated DDL.

Limitations

No data movement, ETL execution, live catalog derivation, bridge inference, aggregates, automatic time dimension, custom naming templates or incremental warehouse job scheduling is provided.