Healthcare Claims Analytics dbt

A dbt Core analytics engineering project built on synthetic healthcare claims data — demonstrating staging/intermediate/mart modeling patterns, data quality testing, metric definitions, and documentation on a domain I know well.

The terminal output is mart_pmpm_by_lob: a tested, documented mart that calculates monthly PMPM (Per Member Per Month) by line of business.

---

What it demonstrates

• dbt modeling layers — staging views for cleaning, intermediate views for enrichment and business-logic flags, mart tables for final aggregation
• Data quality testing — schema tests (not_null, unique, accepted_values, relationships) plus custom singular tests for domain-specific rules
• Metric definitions — plain-English definitions written before the SQL, in metric_dictionary.md
• Semantic-layer thinking — every metric has a defined numerator, denominator, grain, and exclusion set
• Synthetic data generation — a Python script that produces realistic claims data with intentional data-quality errors for the tests to catch
• Macros — reusable Jinja SQL fragments; finalized_paid_filter() centralizes the finalized-paid business rule used across multiple mart models
• Sources — raw seeds declared as named sources in _staging.yml; staging models use {{ source() }} instead of {{ ref() }}, registering raw inputs formally in dbt's metadata
• Exposures — pmpm_dashboard exposure declared in _marts.yml, connecting mart_pmpm_by_lob to its downstream consumer and appearing as a terminal node in the lineage DAG
• dbt docs — column descriptions and model documentation throughout; run dbt docs serve to view the full lineage DAG from sources through marts to the exposure

---

Tech stack

Layer	Tool
Transformation	dbt Core 1.11.11
Database	DuckDB 1.10.1 (embedded, no server required)
Data	Python-generated synthetic CSV seeds
Python	3.13

---

Project structure

models/
  staging/          # views — rename and cast raw columns only
  intermediate/     # views — joins, enrichment, business-logic flags
  marts/            # tables — final facts, dims, and aggregates

macros/             # reusable Jinja SQL fragments
seeds/              # CSV source data (gitignored — generated by scripts/generate_seed_data.py)
tests/              # singular tests: SQL files that return rows when a rule is violated
scripts/            # synthetic data generation only
metric_dictionary.md  # plain-English metric definitions — source of truth before code

Model lineage

raw_claim_lines (seed)
  └─ stg_claim_lines
        └─ int_claim_lines_enriched ──── stg_providers
               │                    └─── stg_service_category_mapping
               └─ fct_claim_lines
                     ├─ mart_monthly_paid_claims
                     └─ mart_pmpm_by_lob ◄── fct_member_months ◄── stg_member_months

raw_providers (seed) ──► stg_providers ──► dim_providers

Every {{ ref() }} call is a dependency edge. dbt builds from this graph — no manual ordering needed.

---

Getting started

Step 1 — Create and activate a virtual environment

python -m venv .venv
.\.venv\Scripts\Activate.ps1

Step 2 — Install project dependencies

pip install -r requirements.txt

Step 3 — Generate the synthetic seed data

python scripts/generate_seed_data.py

This creates four CSV files in seeds/:

seeds/raw_claim_lines.csv          # ~9,000–12,000 claim lines + 4 seeded errors
seeds/raw_member_months.csv        # ~500 members × 24 months of eligibility
seeds/raw_providers.csv            # 24 providers
seeds/raw_service_category_mapping.csv  # 8 service code mappings

Step 4 — Configure your dbt profile

dbt looks for profiles.yml in ~/.dbt/. Copy the example and update the path:

# Copy profiles.yml.example to C:\Users\<you>\.dbt\profiles.yml
# Then edit the path field to an absolute path on your machine

healthcare_claims_ae_lab:
  target: dev
  outputs:
    dev:
      type: duckdb
      path: healthcare_claims.duckdb
      threads: 4

Step 5 — Verify the connection

dbt debug

Step 6 — Build everything

dbt build

dbt build runs seeds, models, and tests in dependency order. A clean run with all tests passing is the baseline.

Step 7 — Explore the docs and lineage

dbt docs generate
dbt docs serve

Open the local URL shown in your terminal. The lineage graph under Lineage shows the full DAG from seeds through to marts.

---

Core metrics

Metric	Definition
Paid Claims Dollars	Sum of paid_amount for finalized paid claim lines (paid or adjusted status), excluding denied, voided, and any line where paid_date precedes service_date
Member Months	Count of eligible member-month records (eligible_flag = 'Y')
PMPM	Paid Claims Dollars ÷ Member Months

Date basis: Claims use paid_month (derived from paid_date). Membership uses eligibility_month. See metric_dictionary.md for full definitions.

---

Data quality tests

The seed data includes intentional errors so there is always something meaningful for the tests to catch:

Issue seeded	Test
Duplicate claim_line_id	tests/assert_no_duplicate_claim_line_ids.sql
paid_date before service_date	tests/assert_no_paid_date_before_service_date.sql
Unknown service_code on a paid claim	tests/assert_all_paid_claims_have_mapped_service_category.sql
Null provider_id on a paid claim	relationships test in models/marts/_marts.yml

---

Key modeling concepts demonstrated

Grain — every model has a defined grain (what one row represents):

Model	Grain
stg_claim_lines	one row per claim line
stg_member_months	one row per member per eligibility month
fct_claim_lines	one row per claim line (all statuses)
mart_pmpm_by_lob	one row per paid month and LOB

Business logic in intermediate, not marts — finalized_paid_flag and paid_before_service_flag are derived in int_claim_lines_enriched. The mart models filter on flags, not on raw status values. This keeps business rules in one place.

Define before you calculate — metric_dictionary.md contains plain-English definitions for every metric, written before the SQL. This mirrors the discipline of a semantic layer.

---

What's next

Planned: incremental models, snapshots, dbt-utils/dbt-expectations, a semantic layer, and a Snowflake/Databricks migration with CI/CD. See CLAUDE.md for phase details, rationale, and the cloud warehouse strategy.