The Dorieh Approach: Declarative Data Modeling, Medallion Layers, and Fine-Grained Lineage

This page defines the concepts and vocabulary that the rest of the documentation relies on. Every term introduced here links to the page where it is developed in depth and to the code artifact that implements it. The documentation is self-contained: everything on this page can be verified against the repository’s own code, models and synthetic data.

Two languages, one pipeline 

A Dorieh pipeline is described by two complementary, declarative languages:

A workflow language — the Common Workflow Language (CWL) — describes the topology of the pipeline: which steps exist, what files and values flow between them, and which steps can run in parallel. The pipeline is a directed acyclic graph (DAG); CWL declares its nodes and edges but says nothing about what happens to the data inside a node.
The Dorieh data-modeling DSL — a YAML dialect documented in the data modeling reference and its extensions — describes what each node does to the data: which tables and views exist, how every output column is computed from input columns, what constitutes a valid record, and what to do with records that are not valid.

Both languages are declarative: they state what the result must be, and Dorieh generates the imperative code (DDL, SQL, triggers, loader invocations) that produces it. The generated SQL is ordinary PostgreSQL and can be inspected, which is what makes the pipeline auditable rather than a black box (see Why Data Platform?).

Two properties follow from this design and are relied on everywhere:

Immutability of inputs. A transformation never modifies the dataset it reads. Downstream layers are created as views, materialized views or CREATE TABLE ... AS SELECT statements over upstream objects; the as-ingested data is never updated in place.
Idempotency. Because outputs are pure functions of immutable inputs, a step can be re-run and yields the same result. The data loader supports this directly with its --incremental flag, which commits after every file and skips files that have already been ingested.

Medallion architecture as Dorieh implements it 

Dorieh organizes the tables and views of a domain into the three layers of the medallion architecture:

Bronze — data exactly as ingested. Dorieh creates a separate table for every source file (see storing raw Medicare data), because in longitudinal data the file structure often changes from year to year. When Dorieh introspects source files to generate a Bronze model, it appends two provenance columns to every table: a FILE column holding the original file name and a RECORD column holding the line number of the record in that file (see the data introspector). Bronze tables are immutable: no downstream step ever updates them.
Silver — harmonized, cleaned and federated data, built from Bronze data only — either directly or through intermediate Silver views — never from Gold aggregates. This is where heterogeneous yearly schemas are united into a single view, types are cast, codes are normalized and invalid records are filtered out and journaled.
Gold — analytic and quality-control (QC) aggregates, built only from Silver objects. Gold datasets are typically materialized views grouped by the dimensions users actually query. Audit journals produced during Silver validation (such as medicare_audit.admissions) are Silver-layer byproducts, so a Gold object such as qc_adm_union, which unions them with the accepted records, still satisfies this rule.

Two worked references are used throughout the documentation:

Layer	Climate tutorial	Medicare pipeline
Bronze	`bronze_temperature` table	`cms.*` tables, one per source file
Silver	`silver_temperature` view	`medicare.ps` / `_ps`, `beneficiaries`, `enrollments`, `admissions` and the federated admission views
Gold	`gold_temperature_by_state` materialized view	`medicare.qc_*` QC aggregates

The climate layers are built step by step in the Bronze–Silver–Gold climate tutorial from the model file doc/tutorial/climate/example1_model.yml. The Medicare layers are described in the Medicare case study and defined in src/python/dorieh/cms/models/medicare.yaml.

Dataset operators and field construction operators 

The data-modeling DSL is built around two kinds of operators:

A dataset operator is a node in the dataflow DAG: it consumes one or more datasets and produces a dataset. In the DSL, every table or view definition with a create clause is a dataset operator — the from key names the datasets it consumes, and optional group by, select and exclude keys shape the result. See the create statement reference.
A field construction operator describes how a single output field is computed from input fields. In the DSL, every column definition with a source or a computing expression is a field construction operator. See the column reference.

For example, the Gold layer of the climate tutorial is one dataset operator: it consumes silver_temperature and produces a materialized view aggregated by state and date (from example1_model.yml):

gold_temperature_by_state:
  description: |
    Temperature variations by US State
  create:
    type: materialized view
    from: silver_temperature
    group by:
      - us_state
      - date

Inside the Silver layer of the same model, a field construction operator derives a Celsius temperature from the Kelvin value ingested into Bronze:

- temperature_in_C:
    type: float
    description: Temperature in Celsius
    source: (tmmx - 273.15)

When an output field is derivable from other fields of the same output record, the DSL can delegate the computation to the database as a generated column. From the Medicare beneficiaries table (src/python/dorieh/cms/models/medicare.yaml):

- number_of_gap_years:
    type: INT
    source:
      type: generated
      code: "GENERATED ALWAYS AS (last_enrollment_year - first_enrollment_year + 1 - CARDINALITY(all_enrollment_years)) STORED"

Families of field transformations 

Field construction operators used across Dorieh models fall into a few recurring families:

Direct copies and casts. A column is carried through unchanged, possibly renamed or cast to a uniform type. The cast extension lets one column declare a different conversion for each source type, which is essential when the same variable arrives as text in one year and as a number in another.
Single-value conversions. A scalar function of one input value, such as the Kelvin-to-Celsius conversion (tmmx - 273.15) shown above, or parsing an eight-digit string into a date.
Rollups and approximations. A value is mapped to a coarser nomenclature, sometimes only approximately. In the Medicare model, when a county code is absent the residence county is approximated from the ZIP code (public.zip_to_fips3(year, zip)), and a companion flag fips3_is_approximated records that the value is an approximation rather than a fact. A validation column fips3_valdiated (sic — the misspelling is preserved in the physical schema) cross-checks ZIP, state and county codes.
Aggregations. Values of a group of records are combined with an aggregate function: standard SQL aggregates (MIN(dob), MAX(dod), AVG(temperature_in_C)), custom aggregations written as SQL expressions, and HLL hashes for approximate distinct counts. In Medicare tables, a generated column bene stores hll_hash_text(bene_id), so Gold-level QC views can compute NumDistinctBeneficaries (sic — preserved physical name) over any grouping without rescanning beneficiaries; see Using HyperLogLog.
Unions and federation across heterogeneous yearly schemas. A single Silver view is defined over many Bronze tables whose columns differ from year to year. A column may list several possible source names, be marked optional for years where it does not exist, and carry per-type casts. The Medicare ps view federates all cms.mbsf_ab* and cms.mcr_bene_* tables this way; see creating the federated patient summary.
Array collapse and unnesting. Repeated columns of one record are collapsed into an array — the 25 diag1 … diag25 columns of an inpatient record become a single diagnoses array in the Medicare admissions table — or, conversely, an array (or a set of monthly columns) is unnested so that each element becomes its own record, as in the Medicaid monthly view (see transposing columns).

Disambiguation rules 

This section is the canonical definition of disambiguation rules in the Dorieh documentation; the Medicaid and Medicare pages show the rules applied to real models.

An aggregation frequently expects a single value where the source records can disagree. When all records for one Medicare beneficiary are combined into a single beneficiaries row, that person has, in reality, exactly one date of birth — but the raw yearly files may report several. Dorieh never resolves such a conflict silently. Instead, a disambiguation rule has three parts:

Pick a deterministic primary value. A fixed, documented rule selects the canonical value — for example, the earliest date of birth (MIN(dob)), the latest date of death (MAX(dod)), or for OREC (Original Reason for Entitlement Code) the value from the earliest enrollment year with ties broken by the smallest code.
Keep the divergent value in a secondary column. The discarded alternative is preserved next to the primary: dob_latest is non-null only when the records disagreed on the date of birth (dod_earliest and orec_latest play the same role for their columns). From _beneficiaries in medicare.yaml:
```
- dob_latest:
    source: |
      CASE
        WHEN MAX(dob) <> MIN(dob) THEN  MAX(dob)
      END
```
Surface a consistent_* flag. A QC column classifies every entity as MISSING (no value at all), AMBIGUOUS (sources disagreed; the secondary column holds the alternative) or CONSISTENT. For date of death, where a null value is normal — most beneficiaries in any given year are alive — the flag uses NONE instead of MISSING. The Medicare QC view qc_enrl_bene defines consistent_dob, consistent_dod, consistent_sex, consistent_race and consistent_orec this way (the sex and race flags, which classify aggregated string lists, take only AMBIGUOUS or CONSISTENT).

Because the primary value is deterministic, the resulting tables are reproducible; because the secondary column and the flag are preserved, a project curator can still choose their own inclusion rule (for example, exclude all ambiguous records, or only those where the dates of birth differ by more than a threshold).

Disambiguation also determines where an attribute may live: an attribute belongs at the grain where it is invariant. OREC is set at enrollment and is invariant for the life of the beneficiary, so it is a column of beneficiaries; CUREC, the current reason for entitlement, legitimately varies by year, so it stays on enrollments, together with its consistency flag (consistent_curec). The full story — including the natural-join failure mode that motivated this rule — is told in Entitlement reason codes: OREC and CUREC.

Validation and journaling 

Dorieh treats validation as part of the data model, not as an afterthought. When a table declares an invalid-records policy, the DDL generator (the Domain class) emits a database trigger that applies three validation checks to every incoming record, in order:

Primary key integrity. A record whose primary-key columns are null cannot be identified and is rejected with reason PRIMARY KEY.
Consistency across records (referential integrity). The record must match an existing record in its parent table — for example, a Medicare admission must match an enrollment for the same beneficiary, year and state. A record with no match is rejected with reason FOREIGN KEY.
Elimination of duplicates. If a record with the same primary key already exists, the newcomer is rejected with reason DUPLICATE, and the audit record keeps a physical reference (REFCTID) to the row that was retained, whose own quality column is updated to mark that it had duplicates.

What happens to a rejected record is governed by the invalid.records policy in the DSL. The default, with no policy, is to raise an exception and stop. The policy can instead say IGNORE (drop silently — rarely appropriate) or, as the Medicare admissions table does, journal the record:

invalid.records:
  action: "INSERT"
  target:
    schema: $schema.audit

Journaling means every failed record is inserted into an audit table in a separate audit schema, together with a REASON code and a timestamp, instead of being silently dropped. The Gold QC view qc_adm_union then unions the audit records with the accepted ones (labeled with reason OK), so QC aggregates such as qc_admissions account for every incoming record exactly once, broken down by OK / PRIMARY KEY / FOREIGN KEY / DUPLICATE. Nothing is lost, and the share of rejected data is itself a queryable quality metric; see Medicare QC tables.

Fine-grained lineage 

Dorieh aims at cell-level lineage: for any value in any table, it should be possible to establish both how it was computed and from which raw records it came. Cell-level lineage is the combination of:

Column-level lineage, derivable statically from the field construction operators in the model: every column’s source expression names the upstream columns it depends on, so the chain of transformations from a Gold column back to Bronze columns can be reconstructed and drawn without running the pipeline.
Row-level lineage, provided by the FILE and RECORD provenance columns anchored in Bronze: FILE stores the original source file name and RECORD the line number within it. Silver objects carry these columns forward — the Medicare enrollments view, for instance, aggregates them so each enrollment row lists all contributing files and record numbers.

The data dictionary and lineage tooling generates these artifacts automatically from the model: a table-level lineage diagram, a documentation page per table and per column, a column-level lineage diagram for every column, and an index of columns across all tables. See the tutorial on constructing data dictionaries and lineage graphs for the climate model and the Medicare data dictionary and lineage for a production-scale example.

Where each concept lives 

Concept	Documentation page	Code artifact
Workflow language (CWL), DAG topology	Data processing pipelines	`src/cwl/*.cwl`
Data-modeling DSL (core syntax)	Data modeling reference	`dorieh.platform.data_model.domain`
DSL extensions (federation, casts)	Data modeling extensions	`ps` view in `src/python/dorieh/cms/models/medicare.yaml`
Medallion layers (teaching example)	Climate tutorial	`doc/tutorial/climate/example1_model.yml`
Medallion layers (case study)	Medicare pipeline	`src/python/dorieh/cms/models/medicare.yaml`
Disambiguation rules	This page; applied in Medicaid and Medicare	`_beneficiaries` and `qc_enrl_bene` in `medicare.yaml`
Validation and journaling	This page; invalid-records reference	validation trigger generator in `dorieh.platform.data_model.domain`
HLL approximate distinct counts	Using HyperLogLog	`bene` generated columns in `medicare.yaml`
Lineage and data dictionary	Lineage tutorial, Medicare lineage	`dorieh.platform.loader.introspector` (FILE/RECORD), dictionary tool
Terms and acronyms	Glossary	—