Modeling Clinical Data with dbt

Once FHIR bundles and HL7 v2 messages have been flattened into raw warehouse tables, the next problem is turning that landing-zone sprawl into governed, testable clinical marts — and dbt is the tool that makes those transformations reproducible and auditable. This page is the modeling companion to the async batch processing workflow for large clinical datasets: where that guide moves millions of records through the pipeline, here we structure what lands on the other side into a layered warehouse with incremental fact loads, slowly-changing patient dimensions, and dbt tests as the data-quality gate. The design assumes your batch jobs have already exploded nested FHIR resources into columnar tables (one row per Observation, one per Encounter, one per Patient) that dbt can select from.

dbt construct to clinical modeling role

dbt gives you a small vocabulary of constructs, each of which maps cleanly onto a job in a clinical warehouse. Keep this table next to your models/ tree — it is the fastest way to decide where a given transformation belongs and which materialization it needs.

The layering is not cosmetic. Staging isolates every source-specific quirk (Epic vs. Cerner column naming, HL7 v2 field positions) into one place, so downstream models never reference raw tables directly. Intermediate models hold the joins and derivations that would otherwise be copy-pasted across marts. Marts are the only layer analysts query. This is standard dbt discipline, but in a clinical context it also gives you a clean audit boundary: de-identification and access controls attach to the marts layer, not scattered through the DAG.

Implementation pattern

Start at staging. A staging model does exactly one thing — take a raw flattened table and produce a clean, typed, uniquely-keyed relation. The surrogate key is generated with dbt_utils.generate_surrogate_key so downstream unique and relationships tests have a stable column to assert on.

-- models/staging/stg_observations.sql
-- One row per flattened FHIR Observation. Rename, cast, add a surrogate key.
with source as (
    select * from {{ source('fhir_raw', 'observation') }}
),

renamed as (
    select
        -- Deterministic surrogate key from the natural FHIR id + version.
        {{ dbt_utils.generate_surrogate_key(['id', 'meta_version_id']) }} as observation_sk,
        id                                as observation_id,
        subject_reference                 as patient_id,
        encounter_reference               as encounter_id,
        status                            as observation_status,   -- FHIR ObservationStatus
        code_coding_code                  as loinc_code,
        cast(effective_date_time as timestamp) as effective_at,
        cast(value_quantity_value as numeric)  as value_numeric,
        value_quantity_unit               as value_unit
    from source
    where id is not null
)

select * from renamed

The fact model is where incremental materialization earns its place. Clinical event volumes make a full rebuild on every run impractical, so the model appends only rows newer than what it already holds. The is_incremental() block is skipped on a full refresh and applied on every routine run; unique_key makes the load idempotent — a re-delivered batch updates the existing row rather than duplicating the event.

-- models/marts/fct_clinical_events.sql
{{
    config(
        materialized='incremental',
        unique_key='observation_sk',
        incremental_strategy='merge'
    )
}}

with obs as (
    select * from {{ ref('stg_observations') }}
)

select
    observation_sk,
    observation_id,
    patient_id,
    encounter_id,
    observation_status,
    loinc_code,
    value_numeric,
    value_unit,
    effective_at
from obs

{% if is_incremental() %}
    -- Only pull events newer than the max already loaded. The merge strategy
    -- upserts on observation_sk, so a replayed batch stays idempotent.
    where effective_at > (select coalesce(max(effective_at), '1900-01-01') from {{ this }})
{% endif %}

Patient demographics change over time — address, name, marital status, even administrative gender — and analytics often need the value that was true at the time of an encounter, not the latest value. That is a slowly-changing dimension, and dbt snapshots capture it as SCD type 2, writing dbt_valid_from and dbt_valid_to columns so history is queryable.

-- snapshots/patient_snapshot.sql
{% snapshot patient_snapshot %}
{{
    config(
        target_schema='snapshots',
        unique_key='patient_id',
        strategy='check',
        check_cols=['name_family', 'address_postal_code', 'marital_status', 'gender']
    )
}}
select
    id as patient_id,
    name_family,
    address_postal_code,
    marital_status,
    gender
from {{ source('fhir_raw', 'patient') }}
{% endsnapshot %}

Finally, the tests. In a schema.yml beside the fact model, declare the assertions that turn dbt into a data-quality gate. not_null and unique protect the surrogate key; relationships enforces referential integrity from the fact to the patient dimension; accepted_values pins coded columns to their bound value sets.

# models/marts/schema.yml
version: 2

models:
  - name: fct_clinical_events
    description: "One row per clinical observation event, keyed by observation_sk."
    columns:
      - name: observation_sk
        tests:
          - not_null
          - unique
      - name: patient_id
        tests:
          - not_null
          # Referential integrity: every event must reference a real patient.
          - relationships:
              to: ref('dim_patient')
              field: patient_id
      - name: observation_status
        tests:
          # FHIR R4 ObservationStatus required-binding codes.
          - accepted_values:
              values: ['registered', 'preliminary', 'final', 'amended',
                       'corrected', 'cancelled', 'entered-in-error', 'unknown']

  - name: dim_patient
    columns:
      - name: gender
        tests:
          - accepted_values:
              values: ['male', 'female', 'other', 'unknown']

A subtle but important point: those accepted_values lists are terminology snapshots, not constants. FHIR ObservationStatus and administrative gender are stable, but many coded columns bind to value sets (LOINC classes, condition categories, local order codes) that expand with each terminology release. Treat every accepted_values list as a maintained artifact with an owner, and review it whenever you upgrade a code system — a stale list either rejects newly-valid codes or silently accepts retired ones.

Validation and testing

The gate runs on the command line. dbt build executes models and their tests in DAG order, so a failing test halts anything downstream before bad data reaches a mart; dbt test runs the assertions alone against already-built tables. Wire dbt build --select state:modified+ into CI so every pull request that touches a model reruns its tests.

# Full build: run models + snapshots + tests in dependency order.
dbt build --select fct_clinical_events+

# Assertions only, against existing tables.
dbt test --select fct_clinical_events

# Incremental run in production (append new events, then re-assert).
dbt build --select fct_clinical_events

The most valuable failure to rehearse is the relationships test. Point the fact at dim_patient and drop a patient row (or feed an event whose subject.reference never resolved during flattening); the test fails with the exact orphaned keys, catching a broken join or a late-arriving dimension before analytics sees a fact row that references a patient who does not exist. Run it against a seed of known-bad fixtures in CI so referential drift fails the build, not a downstream dashboard.

For local confidence before opening a pull request, dbt build --select state:modified+ --defer --state ./prod-manifest builds only what changed against the production manifest, giving you the same gate CI will enforce without rebuilding the whole warehouse.

Gotchas and compliance constraints

• The incremental unique_key must be genuinely stable. If your surrogate key is derived from a field that a source system rewrites on re-delivery — a mutable composite, or a FHIR id that changes across versions — the merge inserts duplicates instead of upserting, and idempotency is lost. Key on the immutable natural identifier plus a version, and never on a load timestamp or row hash that shifts between runs.

• accepted_values lists drift with terminology releases. A list that is correct today rejects valid codes after the next value-set expansion. Own each list, version it, and reconcile it on every code-system upgrade; consider generating large lists from the terminology server rather than hand-maintaining them.

• Marts feeding analytics may need de-identification upstream. dbt runs inside the warehouse, so a mart built on identified rows is identified data. If a mart feeds analytics or research, apply HIPAA Safe Harbor removal of the 18 identifiers (or an expert-determination transform) in a dedicated model before the analytics mart, and enforce access controls at that boundary — not after the fact.

• Audit lineage is a compliance artifact, not just documentation. dbt docs generate produces the full DAG and column-level lineage, showing exactly which raw fields flow into each mart. Publish it and retain the run artifacts (manifest.json, run_results.json) so you can demonstrate provenance and test outcomes for every warehouse build.

• Stable keys — incremental unique_key derived only from immutable identifiers, verified against a replayed batch.

• Terminology owners — every accepted_values list has an owner and a review trigger tied to code-system releases.

• De-identification boundary — analytics marts are built downstream of a Safe Harbor / expert-determination model, not on identified rows.

• Lineage retained — dbt docs and run artifacts archived per build for audit.

Modeling clinical data with dbt turns a pile of flattened FHIR and HL7 tables into a warehouse where every transformation is versioned, every load is idempotent, and every mart is gated by tests you can point an auditor at.

Related

• Distributed FHIR processing with Apache Spark — the batch layer that flattens FHIR bundles into the raw tables dbt models
• Async batch processing for large datasets — parent guide: moving millions of clinical records through the pipeline
• Clinical data quality and validation frameworks — where dbt tests fit alongside broader validation strategy
• Clinical data parsing and transformation workflows — the full parsing-to-warehouse pipeline this modeling stage sits in