Warehouse layers

The warehouse is built as a sequence of layers, each writing to its own schema. The value of the arrangement is that every transformation has one home: if a value looks wrong, the layer names tell you where to look, and you can query the intermediate result rather than reading the transformation and guessing.

Layer	Schema	Tables	Job
Input	dax_input_layer	15	Source data mapped to a common shape
Normalized	dax_normalized_layer	44	Reconcile codes, dates and identifiers
Claims preprocessing	dax_claims_preprocessing	200	Group claims into episodes, categorise services
Core	dax_core	36	The analytics-ready model
Data quality	dax_data_quality	35	Flag what is wrong, per source

Table counts are generated from the model metadata; see the data dictionary for the current figures.

Input layer

The contract with your source systems. A connector maps each source — a payer extract, an EHR database, a flat-file feed — onto the 15 input tables, and from that point on nothing downstream needs to know which vendor the data came from.

The input layer deliberately does not clean anything. Codes arrive as the source sent them, dates in the source's format, identifiers in the source's namespace. Keeping this layer faithful is what makes the pipeline auditable: when a downstream value is surprising, you can compare it against exactly what arrived.

Onboarding a new source means writing a mapping to these tables and nothing else. That is the whole point of the layer.

Normalized layer

Where the same fact reported differently becomes the same value.

Three kinds of work happen here:

• Terminology normalization. Source codes are mapped to standard code systems, and the normalized value is stored alongside the original rather than replacing it. Reference pages show both, as source_code and normalized_code. You can always see what the source actually said.
• Date and identifier normalization. Service dates are reconciled across claim headers and lines, and provider identifiers are resolved to NPIs.
• Reconciliation by voting. When several claims describe one event and disagree about an attribute — admit source, admit type, bill type, discharge disposition, DRG — the layer picks a single value rather than leaving the conflict for the analyst. Each of these has a voting model that gathers the candidates and a final model that resolves them.

Voting is the layer's most consequential idea, and worth knowing about: a downstream discharge disposition is a decision made from conflicting evidence, not a fact copied from one row. When a value looks wrong, the voting model shows you the candidates it chose between.

Claims preprocessing

The largest layer, and the one that turns billing records into clinical events.

• Encounter grouping. Claim lines belonging to one episode of care are assembled into a single encounter. This is what makes an inpatient stay count once instead of once per line, and it is why utilization should always be measured from encounter.
• Service categorisation. Each claim line is assigned a three-level service category — inpatient, outpatient, office-based and their sub-categories. Because one hierarchy is applied consistently, the cost and utilization marts can be pivoted across the same categories and reconcile to the same totals.
• Provider attribution. Member months are attributed to a responsible provider, so panel-level measurement is possible.

Claims that cannot be grouped into an encounter are counted as orphaned rather than dropped. Watch utilization.orphaned_claim_count: it is the honest signal that some of your data did not fit the model.

Most of this layer's 200 tables are intermediate steps. They are documented for lineage and debugging and grouped under Internal models in the reference navigation.

Core

What you query. Roughly 20 tables form the supported surface:

• Spine — patient, person_id_crosswalk
• Coverage — eligibility, member_month
• Events — encounter, medical_claim, pharmacy_claim
• Clinical — condition, procedure, lab_result, medication, observation, immunization, appointment
• Directory — practitioner, location
• Marts — cost, utilization

Every core table carries dax_last_run, so you can always confirm which build produced the rows you are reading, and data_source, so you can always tell which system a fact came from.

Data quality

The layer that tells you what to distrust. It runs two kinds of check:

• Structural — does each source deliver the expected tables and columns, with the expected data types, and do primary keys actually hold? Results land in the structural_* tables.
• Field-level — per-table flag models (patient_flags, encounter_flags, medical_claim_claim_flags and so on) mark records with missing or implausible values.

Because results are broken out by data_source, you can see that one payer's data is fit for a measure while another's is not — which is usually the real situation, and far more useful than a single overall quality score.

Read this layer before publishing a number, not after someone questions it.

Following a value through the layers

To work out why a discharge disposition reads the way it does:

1. dax_core.encounter — the value as published.
2. dax_normalized_layer — the discharge_disposition_final model, which chose it, and the voting model, which shows the candidates.
3. dax_input_layer.medical_claim — what each source actually sent.
4. dax_data_quality — whether that field is flagged for this source at all.

Four queries, no need to read any transformation code. That is what the layering buys you.