Healthcare Data Engineering with dbt: Building a Governed Metric Platform

I spent two years building a data platform from the ground up for a Medicare Advantage primary care organization. Five clinic locations, about 117 users, roughly 30 providers serving women 65 and older. The platform runs 500+ dbt models on Databricks and feeds 30+ production dashboards for clinical operations, quality reporting, and risk adjustment analytics.

This is the technical writeup I wish I had found before starting. Three problems this platform solves, the architecture choices I made, and the code that holds it together.

The problem with ungoverned metrics in healthcare

When I started, reporting was scattered across Tableau workbooks. Different people built different dashboards from different data sources. A metric like "average HbA1c for diabetic members" had three different answers depending on which workbook you opened. No data contracts. No lineage. No way to trace a number on a dashboard back to the SQL that produced it.

In Medicare Advantage, that is not just messy. It is a compliance risk. CMS-HCC V28 risk adjustment scores determine revenue. HEDIS quality measures drive Star ratings, which drive bonus payments. RADV audits require you to reproduce every number you submitted. When the auditor asks "how did you calculate that RAF score," the answer has to be a deterministic query, not "the Tableau workbook Carol built in 2023."

I needed a platform where the metric definition is the authority. Where every number in a dashboard traces back to a single, versioned, tested transformation. Where adding a new metric means adding a row to a seed file, not building a new ad-hoc query.

The metric registry is the authority

The core of this platform is a metric registry built from dbt seeds. A seed is a CSV file that dbt loads as a table. In this case, the seed binds a metric name to its mart column, aggregation function, grain, and owner.

-- seeds/metric_registry.csv
metric_name,mart_model,column_name,aggregation,grain,owner,domain
avg_hba1c,mart_quality_diabetes,hba1c_value,avg,patient_month,quality_ops,clinical
raf_score_total,mart_risk_adjustment,raf_score,sum,member_year,risk_adjustment,finance
ed_visit_count,mart_utilization,ed_visits,sum,member_month,operations,utilization
hosp_admit_rate,mart_utilization,hosp_admits,sum,member_month,operations,utilization
med adherence_pct,mart_quality_medication,pdc_pct,avg,patient_month,quality_ops,clinical
 mammogram_compliance,mart_quality_preventative,screening_complete,count_pct,patient_year,quality_ops,clinical
total_member_months,mart_enrollment,member_months,sum,member_month,enrollment,enrollment
pcp_visit_count,mart_utilization,pcp_visits,sum,member_month,operations,utilization

That CSV is the single source of truth. Every metric the organization cares about is defined here with its canonical name, which mart model produces it, which column, how it aggregates, and at what grain.

Downstream, the BI layer reads this registry. A dashboard does not define its own SQL. It references the registry, and the registry points to the mart column. If someone wants to know what "average HbA1c" means, they look at the seed. If someone wants to know where it comes from, they check mart_quality_diabetes.hba1c_value and trace the lineage from there.

Adding a new metric is a pull request that adds a row to the CSV and the corresponding mart model. Removing a metric flags it in the registry and the CI gate catches any downstream references before they break.

From seeds through marts to BI

The data flow is: raw sources land in Databricks via CDC pipelines. dbt staging models clean and rename them. Intermediate models join and transform. Mart models produce the final aggregates at the grain the registry specifies.

-- models/marts/clinical/mart_quality_diabetes.sql
with diabetes_members as (

    select
        member_id,
        month_key,
        hba1c_value,
        hba1c_date,
        diabetes_diagnosis_flag
    from {{ ref('int_diabetes_panel') }}

),

aggregated as (

    select
        member_id,
        month_key,
        avg(hba1c_value) as hba1c_value,
        max(hba1c_date) as last_hba1c_date,
        count(distinct diabetes_diagnosis_flag) as dx_count
    from diabetes_members
    where diabetes_diagnosis_flag = true
    group by member_id, month_key

)

select * from aggregated

That mart model is version-controlled. It has tests. It has a YAML contract that locks its column names and types.

-- models/marts/clinical/mart_quality_diabetes.yml
version: 2
models:
  - name: mart_quality_diabetes
    description: >
      Monthly HbA1c measurements for diabetic panel members.
      Grain: one row per member per month. Source: EHR lab results
      filtered by ICD-10 diabetes diagnosis codes.
    owner:
      name: quality_ops
      email: quality_ops@organization.org
    meta:
      domain: clinical
      pii: false
      contract: enforced
    columns:
      - name: member_id
        description: De-identified member surrogate key
        tests:
          - not_null
          - unique:
              config:
                severity: warn
      - name: month_key
        description: YYYYMM partition key
        tests:
          - not_null
      - name: hba1c_value
        description: Average HbA1c for the month
        tests:
          - not_null
      - name: last_hba1c_date
        description: Most recent HbA1c test date in the month

The contract matters. When contract: enforced is set, dbt will fail the run if a column changes type or gets dropped. That means nobody can silently rename hba1c_value to a1c without the pipeline catching it. The downstream BI dashboards will not break because the contract prevents the breaking change from reaching production.

539 of 541 models in this platform have YAML descriptions. That is not accidental. When I started, documentation was the first thing I enforced. Every model gets a description, an owner, a domain tag, and a PII classification. You cannot merge a model without it.

The serving path: dbt to dashboard

The full serving path looks like this:

1. Raw data lands in Databricks via Change Data Feed (CDC) pipelines
2. dbt models transform it through staging, intermediate, and mart layers
3. Mart output lands in marts_bi_v3, a curated schema in Lakebase (Postgres)
4. A BFF layer exposes a GraphQL API with a metric() function
5. ChartSpec dashboards call metric() and render the results

The BFF layer is the bridge. It reads the metric registry from the Postgres catalog and exposes each registered metric as a typed GraphQL field. Dashboard authors do not write SQL. They reference a metric by its registry name and the BFF returns the data at the correct grain with the correct aggregation.

This is how you get governed metrics without trusting dashboard authors to write correct SQL. The metric definition lives in one place. The serving layer enforces it.

Data contracts and the consolidation ledger

Data contracts in dbt work by declaring what a model produces and locking it. But at 500+ models, you also need a way to track what changed, why, and who approved it. That is the consolidation ledger.

The ledger is a CSV seed that tracks every model in the platform:

-- seeds/model_consolidation_ledger.csv
model_path,grain,owner,materialization,contract_status,consumers,migration_status,notes
models/staging/ehr/stg_lab_results,encounter_id,staging,view,enforced,mart_quality_diabetes;mart_utilization,migrated,"Source: EHR lab interface. CDC via Databricks autoflow."
models/intermediate/int_diabetes_panel,member_month,intermediate,table,enforced,mart_quality_diabetes,migrated,"Joins stg_lab_results with stg_diagnosis for diabetic panel."
models/marts/clinical/mart_quality_diabetes,member_month,quality_ops,incremental,enforced,bi_v3_hba1c;bi_v3_quality,active,"Contract enforced. Grain locked to member_month."

Every row is a model. Every column answers a governance question. Which mart models consume this staging table? Is the contract enforced? Has it been migrated from the old Snowflake schema? What is the grain?

When a PR removes a model, the CI validator checks the ledger. If a consuming model still references the removed model, the build fails. When a PR renames a column, the validator checks whether any downstream model references the old name. This catches breaking changes before they reach production, regardless of who submitted the PR.

The platform went through a consolidation migration from Snowflake, Tableau, and Cube to dbt, Databricks, and Lakebase. The migration status column in the ledger tracked each model through that transition. Every model in production has migrated or active status. Nothing is half-migrated.

CI/CD for human and autonomous agent PRs

This is the part that surprised people the most. Both human developers and autonomous AI agents submit pull requests on this platform. Both go through the same CI gate.

# .circleci/config.yml (simplified)
version: 2.1
jobs:
  dbt-ci-gate:
    docker:
      - image: cimg/python:3.11
    steps:
      - checkout
      - run:
          name: Install dependencies
          command: pip install -r requirements.txt
      - run:
          name: dbt compile
          command: dbt compile --profiles-dir .circleci
      - run:
          name: dbt test
          command: dbt test --profiles-dir .circleci
      - run:
          name: Contract ledger validator
          command: python scripts/validate_ledger.py
      - run:
          name: CodeAnt AI review
          command: |
            if [ "$CIRCLE_PR_NUMBER" ]; then
              python scripts/codeant_review.py --pr "$CIRCLE_PR_NUMBER"
            fi

workflows:
  pr-gate:
    jobs:
      - dbt-ci-gate:
          filters:
            branches:
              ignore: main

Every PR against main runs through this gate. The steps:

• dbt compile catches syntax errors and ref resolution failures
• dbt test runs not_null, unique, accepted_range, and custom tests on every touched model
• The contract ledger validator checks that no model has been removed or renamed without updating all consuming models and the ledger
• CodeAnt provides an AI-assisted code review on the PR

Autonomous agents work on agent/* branches. An agent might refactor ten staging models, add new tests, update the ledger, and open a PR. That PR goes through the same CI gate as a human PR. The agent cannot bypass the gate. The agent cannot merge to main.

Human in the loop by design

This is the non-negotiable rule. No autonomous merge to main. Ever.

Agents write code. Agents run local dbt tests. Agents open pull requests. But a human reviews the PR, checks the CI results, and approves the merge. The CI gate catches most problems. The human catches the ones the gate misses, like "this metric definition does not match what the clinical team agreed on" or "this model is correctly built but solves the wrong problem."

The workflow looks like this for both humans and agents:

1. Branch from main (feature/* or agent/*)
2. Write dbt models, update tests, update YAML docs
3. Update the metric registry and consolidation ledger if needed
4. Open PR against main
5. CI gate runs: compile, test, ledger validation, CodeAnt review
6. Human reviews PR, checks CI output, approves
7. Merge to main triggers production run

The production run uses incremental materialization with full_refresh=false to safeguard Change Data Feed (CDF). Incremental models append and update only the changed partitions. A full refresh on a CDC pipeline would reprocess the entire history and corrupt the change tracking. The CI configuration enforces this by never running dbt run --full-refresh in production.

Incremental CDC pipelines

Raw data arrives through Databricks Change Data Feed. An incremental dbt model processes only the partitions that changed since the last run. The model definition uses a partition_by column (usually month_key or date_key) and a unique_key to handle upserts.

-- models/staging/ehr/stg_lab_results.sql
{{
  config(
    materialized='incremental',
    unique_key='encounter_id',
    partition_by={'field': 'month_key', 'data_type': 'string'},
    incremental_strategy='merge',
    full_refresh=false
  )
}}

select * from {{ source('ehr', 'raw_lab_results') }}

{% if is_incremental() %}
where month_key >= '{{ var("last_loaded_month") }}'
{% endif %}

The full_refresh=false config is set explicitly. Even if someone accidentally passes --full-refresh in a script, the model-level config prevents it in production. That protection exists because a full refresh on CDC data would lose the change tracking metadata and force a full re-ingest from the source system.

What this looks like in practice

The platform serves five clinic locations with 117 users across clinical operations, quality, risk adjustment, and finance. Thirty-plus dashboards are in production. All of them reference the metric registry. None of them define their own SQL.

When a new quality measure comes down from CMS, the workflow is: add a row to the metric registry seed, build or update the mart model, add tests, update the ledger, open a PR. CI validates it. Someone reviews it. It merges and the next production run makes the metric available to the BI layer.

When an autonomous agent identifies an optimization, like consolidating two intermediate models that produce the same grain, it opens a PR on an agent/* branch. The CI gate validates the consolidation does not break any downstream contracts. A human reviews whether the consolidation makes sense, checks the ledger update, and merges.

The old stack, Tableau plus Snowflake plus Cube, is fully retired. The consolidation reduced licensing costs and eliminated the "which source is right" problem that made governance impossible.

What I learned

A few things I did not expect going in:

• Governance is not a separate system. It is part of the CI gate, the metric registry, and the data contracts. You do not bolt governance on after the fact. You bake it into the workflow so it is harder to skip than to follow.
• The metric registry CSV is the most important file in the platform. More important than any individual model. If the registry is wrong, everything downstream is wrong. If the registry is right, the dashboards follow.
• Autonomous agents need the same guardrails as humans, not fewer. The CI gate treats an agent PR identically to a human PR. The only difference is the branch prefix.
• Contracts catch the problems tests miss. A test can verify a column is not null. A contract can verify the column still exists. When someone renames a column in a staging model, tests pass but the contract fails. That distinction matters at 500+ models.

If you are building a healthcare data platform, or rebuilding one that grew without governance, the pattern that worked here is: registry first, contracts on every model, ledger tracking every change, and a CI gate that does not care who opened the PR.