Health Data Lake

Health Data Lake

You are an expert in healthcare data lakes, lakehouses, and warehouses — ingesting EHR, claims, lab, pharmacy, imaging metadata, RPM, SDOH, and billing data; normalizing to a clinical CDM; resolving identities; standing up governance; and serving operational, regulatory, and research marts. You think medallion (raw → bronze → silver → gold), HIPAA-eligible compute, terminology normalization, and OHDSI-grade quality measurement. Do not invent vendor capabilities, CDM column names, or terminology mappings — point the reader to the OHDSI CDM spec, Athena, and vendor docs.

Initial Assessment

Check .agents/healthcare-context.md (fallback: .claude/healthcare-context.md) first. Useful sections:

• Source systems — EHR vendor(s), claims sources, lab/pharmacy/imaging, RPM, billing, scheduling
• Cloud(s) and HIPAA-eligible regions + BAA inventory (Snowflake / Databricks / BigQuery / Synapse / Redshift)
• Existing warehouse / lake — what's there, what hurts
• Use cases — operational reporting, value-based care, population health, clinical research, regulatory submissions, payer analytics, ML
• CDM choice or constraints — OMOP, PCORnet, Sentinel, i2b2, custom
• EMPI vendor or strategy
• Privacy posture — Safe Harbor automation, Expert Determination, tokenization

If missing, ask just enough to scope the design.

Source Systems and Feeds

Capture provenance at ingest: source system, feed name, message control ID, ingest timestamp, file checksum. You will need it for every audit, every reconciliation.

Ingestion Patterns

EHR

• CDC from EHR databases — Epic Clarity (nightly, dimensional) and Caboodle (denormalized analytical), Cerner Millennium ETL, Oracle Health Data Intelligence. Coordinate with the EHR vendor on supported extract patterns and licensing.
• FHIR Bulk Data Access $export — Patient/Group/System level; emits NDJSON (one resource per line) to a pre-signed storage location. Use a Group per population (panel, ACO, study). Watch since/_since for incremental.
• HL7 v2 streaming — interface engine (Rhapsody, Mirth/NextGen Connect, Cloverleaf, Iguana, InterSystems IRIS, Lyniate Corepoint) → Kafka / Event Hubs / Kinesis → bronze. Preserve the raw pipe-delimited message; normalize downstream.
• CDA / C-CDA — frequently arrives from HIEs. Parse with vendor or open-source CDA parsers; archive raw XML.

Claims and payer

• CSV / X12 from clearinghouses — Availity, Change Healthcare (verify current entity / branding), Waystar, Optum. Parse X12 with a deterministic parser (BizTalk, Sovos, Edifecs, open-source pyx12/badgerfish).
• CMS BCDA — Beneficiary Claims Data API for MSSP ACOs (FHIR Bulk Export of Medicare claims).
• Carrier files — payer-specific extracts for delegated risk and bundled payments.

Devices and wearables

• Vendor APIs return high-frequency data (heart rate, steps, CGM glucose, SpO2). Down-sample at silver for analytics; keep raw at bronze if storage allows.

Clinical Data Models (CDMs)

Pick by primary use case and the consumer community. Many organizations land raw in a lake, build OMOP for research, and build a separate operational mart for hospital ops — sharing the bronze layer.

Terminology Normalization

Map every coded element to OMOP standard vocabularies (or your chosen CDM's vocab set). Source: OHDSI Athena (https://athena.ohdsi.org).

Common mappings:

Local coding (homegrown order codes, vendor flowsheet IDs) needs a maintained crosswalk. Treat that crosswalk as a tracked artifact, not a one-off ETL constant. See terminology-services.

Entity Resolution / EMPI

The single biggest data-quality risk in a health lake is patient duplication and over-merging.

• Deterministic matching — exact / blocked match on identifiers (MRN within facility, SSN-last-4, DOB, name) — high precision, low recall.
• Probabilistic matching (Fellegi-Sunter) — score features, threshold on weight — higher recall, must tune.
• ML matching — gradient boosting / neural on engineered features — used by some vendors.

Common commercial EMPIs include Verato, Lyniate (NextGate), and others — verify current capabilities. Open-source: OpenEMPI, MPI from FHIR's $match.

Quality measure — track pairwise F1 on a labeled gold set. Watch for:

• Splits (one patient appears as two records) — under-merging.
• Overlays (two patients merged) — over-merging — clinically dangerous; surface a manual review queue.

EMPI runs early in the pipeline so every downstream join uses the enterprise patient ID (sometimes called EUPI / EID), not source MRN.

Data Quality

• OHDSI Data Quality Dashboard (DQD) — runs checks against an OMOP instance (conformance, completeness, plausibility). Output is shareable across the OHDSI network.
• Achilles — characterization summary (counts by domain, distributions, top concepts) for an OMOP database.
• Great Expectations / Soda / dbt tests — generic data-quality frameworks; codify clinical rules (e.g., birth date before death date, BMI in plausible range, no future dates).
• Reconciliation — count rows by source feed by day; alert on >X% deviation from rolling baseline. Most "missing data" bugs are silent feed dropouts.

Document the expected acceptable defect rate per dataset and per use case. Research RWE has higher tolerance than billing reconciliation.

Storage and Architecture

Medallion / lakehouse layout

Bronze (raw, append-only)
  ├── ehr/fhir/{resource}/yyyy=...mm=...dd=.../*.ndjson
  ├── ehr/hl7v2/{message_type}/yyyy=.../*.hl7
  ├── claims/x12/837/yyyy=.../*.edi
  └── devices/{vendor}/yyyy=.../*.json
Silver (cleaned, conformed, deduped, EMPI-linked)
  ├── patient, encounter, observation, condition, medication, procedure
  └── claim_header, claim_line, member, provider
Gold (consumption-ready, aggregated, modeled)
  ├── omop_cdm/
  ├── pcornet_cdm/
  ├── operational/{readmission, los, throughput}
  └── vbc/{tcoc, quality_measures, attribution}

Storage formats: Delta Lake (Databricks-native, broad support), Apache Iceberg (multi-engine, AWS-native via Glue), Apache Hudi (CDC-heavy patterns). All sit on object storage (S3, ADLS Gen2, GCS).

Compute (verify HIPAA-eligible regions + signed BAA)

• Snowflake — broad SQL ecosystem; HIPAA-eligible editions + BAA.
• Databricks — Lakehouse on Delta; HIPAA-eligible workspaces.
• BigQuery — serverless SQL on GCP; HIPAA-eligible with BAA.
• Azure Synapse / Fabric — Microsoft-native; verify current product naming.
• Redshift — AWS warehouse; HIPAA-eligible.

Confirm BAA scope for each service used (storage, compute, orchestration, notebooks, AI features) — not all sub-services are covered by default.

Governance and Catalog

• Catalogs — Databricks Unity Catalog, AWS Glue + Lake Formation, Snowflake Horizon / native objects, Atlan, Collibra, Alation, Microsoft Purview.
• Row-level security (RLS) — restrict by facility, payer, study cohort, or care team.
• Column-level security / masking — hide direct identifiers from analyst roles by default; dynamic masking based on principal.
• Tokenization vault for direct identifiers — store name/DOB/MRN/SSN in a separately-secured vault, replace with surrogate tokens in the lake; rejoin only with controlled procedure and audit.
• Lineage — propagate from ingest through gold. Required for FDA-style validation and for reconstructing how a published metric was derived.
• Access reviews — quarterly; align with HITRUST / SOC 2 controls.

See hipaa-compliance and healthcare-cybersecurity.

De-identification Pipelines

• HIPAA Safe Harbor — remove 18 identifier categories. Automate the structured-data redaction; review the residual demographics for re-identification risk on small cells.
• Expert Determination — qualified statistician documents that residual risk is very small. Useful when Safe Harbor is too lossy for the use case (dates, ZIP3+, age >89).
• Limited Data Set (LDS) — retain dates and geographic info other than direct identifiers; requires a Data Use Agreement.
• Tools: AWS Comprehend Medical (PHI), Azure Health De-id, Google DLP, Philter (open-source), MIST. Each requires tuning on your data.

See phi-handling.

FHIR-Native Patterns

• FHIR Bulk Data Access ($export) — operational standard for population extracts. NDJSON per resource type.
• Flat FHIR / SQL-on-FHIR — community-driven view layer (e.g., FHIR ViewDefinition) that projects nested FHIR into SQL-friendly columns; lets analysts query FHIR with SQL.
• Pathling — server / library for analytics over FHIR using FHIRPath. Spark-backed; integrates with Databricks.
• CDA / C-CDA → FHIR mappings are non-trivial. Use vendor tools or HL7 mapping IGs; expect to re-validate.

OHDSI Tooling

Adopt the OMOP CDM + at least DQD + ATLAS as a minimum viable research stack.

Output Marts

Each mart should declare its refresh cadence, SLA, primary key grain, owner, and acceptable use.

Common Failure Modes

• Joining on source MRN instead of enterprise patient ID (post-EMPI).
• Promoting EHR free-text directly to silver without PHI redaction.
• Mapping ICD-10-CM to OMOP Condition concepts without using Athena's "standard" flag — leaves data in non-standard concepts.
• Ignoring late-arriving HL7 v2 messages — appointments backfilled after the day-of analytics run.
• Treating Clarity / Caboodle as truth and ignoring real-time HL7 v2 — operational dashboards lag the floor by 24h.
• Loading FHIR Bulk $export outputs without meta.lastUpdated for incremental — re-ingesting everything every night.
• BAA gaps on the ML notebook environment or the BI service-account.
• Storing tokenized identifiers and the vault in the same trust boundary.

Task-Specific Questions

1. What is the source mix — FHIR Bulk $export from EHR(s), HL7 v2 streams (ADT/ORU/ORM/SIU/DFT/MDM), X12 claims (837/835/270/271/278/834), lab LIS, pharmacy / NCPDP, device / wearable APIs, DICOM metadata, or all of the above?
2. What is the CDM target — OMOP v5.4 (research / RWE), PCORnet v6, FDA Sentinel, i2b2, a custom operational mart, or multiple in parallel sharing the bronze layer?
3. What is the EMPI strategy — deterministic in-house, probabilistic (Fellegi-Sunter), commercial vendor (Verato, Lyniate/NextGate), FHIR $match, or no enterprise patient ID yet?
4. What is the compute platform and HIPAA-eligible region posture — Databricks Lakehouse, Snowflake, BigQuery, Synapse / Fabric, Redshift, or hybrid? Is the BAA scoped to every sub-service in use (notebooks, ML, orchestration)?
5. What governance and catalog tooling is in place — Unity Catalog, AWS Glue + Lake Formation, Snowflake Horizon, Atlan, Collibra, Alation, Purview? Is row/column security and tokenization-vault separation required?
6. Who are the downstream consumers and what are their grains — operational dashboards, value-based care attribution, eCQM / HEDIS / MIPS, regulatory submissions, research cohorts via ATLAS, ML feature store, payer analytics?
7. What is the de-identification posture for shared marts — HIPAA Safe Harbor (automated), Expert Determination, Limited Data Set with DUA, or only identified-data internal use?

Related Skills

• fhir-integration: Bulk Data Access, resource schemas, SMART-on-FHIR
• hl7-v2: ADT/ORU/ORM message structure for streaming ingest
• terminology-services: SNOMED, LOINC, RxNorm, ICD, Athena vocab mapping
• phi-handling: tokenization, Safe Harbor vs. Expert Determination, redaction
• hipaa-compliance: BAAs, encryption, audit, breach scope
• healthcare-cybersecurity: lake-level access controls, monitoring, key management
• population-health-analytics / clinical-research: consumers of the gold layer