Data Engineering for Healthcare: HIPAA, HL7/FHIR, PHI Masking

healthcare data engineering is one of the few domains in 2026 where the pipeline architecture is legally binding. A wrong join, a stray column in a Parquet file, or a lookup table that leaks a patient MRN into an analytics dashboard is not a bug — it is a HIPAA breach with a 60-day notification clock, a per-record fine schedule that starts at $137 and tops out at $71,162 per violation category per year, and a mandatory posting on the HHS "Wall of Shame" once you cross 500 affected individuals. Every senior data engineer who touches a patient record needs to hold three mental models simultaneously: the HIPAA Privacy Rule (who is allowed to see PHI), the HIPAA Security Rule (how PHI is protected in storage and transit), and the interoperability contract — HL7 v2 for legacy hospital systems and FHIR R4 for every 21st Century Cures Act–compliant vendor built after 2020.

This guide is the senior-DE deep dive that ties those three models to a runnable pipeline. It walks through the two-headed HIPAA statute and the 18 identifiers that legally define PHI, the HL7 v2 pipe-delimited parser over MLLP alongside the FHIR R4 REST + Bundle model and the SMART-on-FHIR OAuth flow, the phi masking toolkit senior teams actually run in production (HIPAA Safe Harbor strip, Format-Preserving Encryption for deterministic tokens, per-patient date shift, k-anonymity + l-diversity + t-closeness for statistical de-identification), the audit-logging + row-level-security pattern that satisfies HIPAA §164.312(b), and the OMOP Common Data Model cohort pipeline that turns raw EHR extracts into research-grade longitudinal patient timelines. Each section pairs a teaching block with a Solution-Tail interview answer — code, a step-by-step trace, an output table, then a concept-by-concept breakdown of why it works.

When you want hands-on reps immediately after reading, drill the SQL practice library → for the cohort and audit-log query patterns, rehearse on joins problems → for the ICD-10 / SNOMED crosswalk and OMOP star joins, and sharpen the aggregation axis with the aggregation drills → for the per-patient windowed rollups.

---

On this page

• Why healthcare DE differs in 2026
• HL7 v2 and FHIR ingestion
• PHI masking + de-identification
• Audit logging + access controls
• Cohort building + research pipelines
• Cheat sheet — healthcare DE recipes
• Frequently asked questions
• Practice on PipeCode

---

1. Why healthcare DE differs in 2026

HIPAA is a two-headed statute — Privacy Rule governs who sees PHI, Security Rule governs how it is stored and moved

The one-sentence invariant: the HIPAA Privacy Rule (45 CFR §164 Subpart E) tells you who is allowed to see Protected Health Information, and the HIPAA Security Rule (Subpart C) tells you how PHI must be safeguarded — the two rules together bind every data engineer touching a patient record, and every downstream engineering choice (schema, join key, retention, cloud provider) follows from them. Once you internalise "Privacy = access, Security = safeguards," the entire hipaa data pipeline interview surface collapses to a sequence of consequences from that split.

PHI vs PII — the 18-identifier contract.

• PHI (Protected Health Information) is any individually identifiable health information created, received, or maintained by a HIPAA-covered entity — a superset of PII in the healthcare context. If it can be tied to a specific patient and it touches health, it is PHI.
• The HIPAA Safe Harbor 18 identifiers are the specific fields that must be stripped for a data set to be considered "de-identified" under §164.514(b)(2): names, geographic subdivisions smaller than a state, dates (except year) related to an individual, phone numbers, fax numbers, email addresses, SSNs, medical record numbers, health plan beneficiary numbers, account numbers, certificate/license numbers, vehicle identifiers, device identifiers, URLs, IP addresses, biometric identifiers, full-face photographs, and any other unique identifying number, characteristic, or code.
• PII (Personally Identifiable Information) is the broader NIST concept — any data that can identify a person. All PHI is PII; not all PII is PHI. In practice, HIPAA is the stricter regime, so PHI controls win.

HITECH breach notification + minimum-necessary standard.

• The HITECH Act (2009) added teeth to HIPAA — a 60-day breach notification clock, tiered penalty amounts adjusted annually for inflation (in 2026: $137 to $2,067,813 per violation category per year), and mandatory HHS posting on the OCR "Wall of Shame" once a breach affects 500+ individuals.
• The minimum-necessary standard (§164.502(b)) requires that every request for, use of, or disclosure of PHI be limited to the minimum necessary to accomplish the intended purpose. For data engineers this means: no SELECT *, no lifting an entire patient table into a marketing pipeline, no exporting fields the downstream consumer does not need.
• Business Associate Agreements (BAAs) — §164.308(b)(1) — are contracts that bind every downstream vendor (cloud provider, ETL tool, analytics platform) to the same HIPAA safeguards. AWS, Azure, and GCP each publish a BAA-eligible service list; using a non-BAA service for PHI is a breach on its own.

21st Century Cures Act + TEFCA — the interoperability mandates.

• The 21st Century Cures Act (2016) — specifically the ONC Cures Rule finalised 2020 — made information blocking a federal offence. Certified EHRs must expose a FHIR R4 API for patients and third-party apps; refusing to share data without a legal reason triggers civil monetary penalties.
• TEFCA (Trusted Exchange Framework and Common Agreement, 2022 → 2026) is the federal interoperability backbone. Qualified Health Information Networks (QHINs) exchange patient records nationwide under a common trust framework; every large hospital system now participates.
• USCDI v4 (US Core Data for Interoperability, 2026 revision) defines the minimum data elements every certified EHR must expose. Data engineers building patient-facing apps target USCDI v4 as the baseline schema.

What interviewers actually probe.

• Do you say "PHI is any individually identifiable health information — the Safe Harbor 18 identifiers are the operational definition" in the first sentence? — required answer.
• Do you mention the BAA chain unprompted when discussing cloud storage? — senior signal.
• Do you cite the 60-day breach notification clock and the minimum-necessary standard as the two operational rules that shape schema design? — senior signal.
• Do you distinguish HIPAA (federal), state privacy laws (CCPA / CMIA), and the 21st Century Cures Act (interoperability) as three parallel regimes? — senior signal.

The 2026 reality — what changed since 2022.

• HIPAA Privacy Rule NPRM (Notice of Proposed Rulemaking, 2024 → finalised 2026) shortened the individual right-of-access response window to 15 calendar days (from 30) and clarified that electronic records must be delivered in the format requested when feasible.
• HITECH penalty schedule was re-tiered by the 21st Century Cures Act — the highest tier (wilful neglect, not corrected) now caps at $2,067,813 per identical violation per year (up from $1.5M pre-inflation-adjustment).
• State laws stacked — California CMIA, Texas HB 300, New York SHIELD Act — often add requirements beyond HIPAA (Texas requires state-employee HIPAA training; California CMIA gives patients broader private right of action). Cross-state data flows must satisfy the strictest applicable law.
• Cloud BAAs are now table stakes — AWS covers 130+ services under BAA; Azure covers most Enterprise-tier services; GCP maintains a per-service list. Snowflake, Databricks, and Fivetran all sign BAAs.

Worked example — is this field PHI or not?

Detailed explanation. Data engineers face this question constantly during schema review: a new column arrives from a source system and you need to decide whether it triggers PHI handling (encryption, audit, masking). The rule is mechanical: is the field on the Safe Harbor 18 list and is it tied to an identifiable individual in a health context?

Question. Given a mixed schema from a hospital data warehouse, classify each column as PHI, PII-not-PHI, or non-sensitive. Justify each call against the Safe Harbor 18 identifiers.

Input.

column	sample value	context
patient_mrn	MRN-482910	hospital patient record
patient_zip5	94103	patient address
patient_zip3	941	truncated zip
encounter_date	2026-07-15	admission date
encounter_year	2026	derived year only
provider_npi	1234567890	rendering provider identifier
lab_result_ldl	118	mg/dL
device_serial	DEV-A19-88213	infusion pump serial
photo_url	s3://phi/...	full-face photo
ip_address	10.0.4.22	patient portal login

Code.

SAFE_HARBOR_18 = {
    "name", "geo_subdivision_lt_state", "date_related_to_individual",
    "phone", "fax", "email", "ssn", "mrn", "health_plan_number",
    "account_number", "certificate_license", "vehicle_id",
    "device_id", "url", "ip", "biometric", "full_face_photo",
    "any_other_unique_code",
}

def classify(col: str, val: str, in_health_context: bool) -> str:
    hits = {
        "patient_mrn": "mrn",
        "patient_zip5": "geo_subdivision_lt_state",   # zip5 is <state
        "patient_zip3": None,                          # 3-digit zip is Safe Harbor OK
        "encounter_date": "date_related_to_individual",
        "encounter_year": None,                        # year alone is OK
        "provider_npi": None,                          # NPI is public directory
        "lab_result_ldl": None,                        # clinical, not identifying
        "device_serial": "device_id",
        "photo_url": "full_face_photo",
        "ip_address": "ip",
    }
    ident = hits.get(col)
    if ident and in_health_context:
        return f"PHI (identifier: {ident})"
    if ident:
        return f"PII (identifier: {ident}, not health-linked)"
    return "non-sensitive"

Step-by-step explanation.

1. patient_mrn is on the list (medical record number) and it's tied to a patient in a hospital context — PHI.
2. patient_zip5 is a geographic subdivision smaller than a state and mapped to a patient — PHI. The 3-digit truncated zip (patient_zip3) is explicitly allowed by Safe Harbor unless the 3-digit zip covers fewer than 20,000 people (in which case it becomes PHI).
3. encounter_date is a date related to an individual — PHI. encounter_year alone is allowed (year-only is not on the list).
4. provider_npi is the rendering physician, not the patient — public directory data, not PHI. The rule targets patient identifiers.
5. lab_result_ldl is clinical information without an identifier attached — not PHI on its own. It becomes PHI only when joined with an identifier column (which is why joined PHI tables are the highest-risk artefacts in a warehouse).
6. device_serial is a device identifier that ties to a patient's infusion pump — PHI. photo_url pointing to a full-face photo — PHI. ip_address used to identify a patient's portal login — PHI.

Output.

column	classification	reason
patient_mrn	PHI	Safe Harbor identifier #7 (MRN)
patient_zip5	PHI	geo subdivision smaller than state
patient_zip3	non-sensitive	Safe Harbor allows 3-digit zip (with population caveat)
encounter_date	PHI	date related to individual
encounter_year	non-sensitive	year alone permitted
provider_npi	non-sensitive	provider directory data
lab_result_ldl	non-sensitive alone; PHI when joined	clinical without identifier
device_serial	PHI	Safe Harbor identifier #13 (device ID)
photo_url	PHI	Safe Harbor identifier #17 (full-face photo)
ip_address	PHI	Safe Harbor identifier #15 (IP)

Rule of thumb. When in doubt, treat the column as PHI and gate it with encryption + audit; the false-positive cost is small (one extra key rotation) and the false-negative cost is a breach notification. Every schema-review checklist should include a "is this on the Safe Harbor 18?" gate.

Worked example — minimum-necessary rewrite of a query

Detailed explanation. A marketing analytics team asks for "patient info for the diabetes campaign." A naive SELECT * FROM patients violates minimum-necessary. The fix is to narrow the projection to the fields the campaign actually needs and to filter the population to the cohort at hand.

Question. Rewrite a bad SELECT * query to satisfy the HIPAA minimum-necessary standard for a diabetes email campaign. Show the before/after and highlight what was dropped.

Input.

requesting team	actual need
marketing	send email to T2DM patients who opted in

Code.

-- BROKEN — violates minimum-necessary
SELECT *
FROM patients p
JOIN conditions c ON p.mrn = c.mrn
WHERE c.icd10 LIKE 'E11%';   -- T2DM

-- FIXED — projected to exactly what the campaign needs
SELECT p.email_hash,           -- one-way hash for the ESP; not the raw email
       p.language_pref
FROM patients p
JOIN conditions c ON p.mrn = c.mrn
JOIN comms_prefs cp ON p.mrn = cp.mrn
WHERE c.icd10 LIKE 'E11%'
  AND cp.email_opt_in = TRUE
  AND cp.email_opt_in_scope @> ARRAY['marketing']
  AND p.deceased_ind = FALSE;

Step-by-step explanation.

1. The broken query returns every patient column: name, DOB, address, MRN, SSN, insurance, encounter history — massively over-scoped for an email campaign.
2. The fixed query projects only email_hash (a one-way hashed email suitable for handing to an Email Service Provider without exposing raw address) and language_pref (needed to pick the template).
3. The WHERE filters cohort to actively-consented recipients: T2DM diagnosis (E11 ICD-10 chapter), explicit opt-in to marketing scope, not deceased. The deceased_ind filter alone prevents a class of very-bad edge-case breaches.
4. email_hash is a HMAC-SHA256 of the email using a per-purpose key. The ESP receives only the hash; the mapping to a raw email lives inside the PHI zone. If the ESP is ever breached, the hash is not directly exploitable.
5. The query is now defensible under minimum-necessary: exactly the fields required for the campaign, filtered to the cohort with legal basis to receive it.

Output (row counts and column diff).

metric	before	after
columns returned	43	2
PHI columns returned	12	0 (email hashed, no MRN)
rows returned	all T2DM (1.2M)	opted-in living T2DM (94K)
BAA needed with ESP	yes (raw PHI leaves)	no (only pseudonymous hash)

Rule of thumb. Every downstream extract to a non-clinical consumer (marketing, analytics dashboards, ML training) starts from a minimum-necessary query, never SELECT *. Put a lint rule in your CI that fails any query hitting a PHI table with a wildcard projection.

Worked example — BAA scope check for a cloud extract

Detailed explanation. A DE lead wants to lift a de-identified extract into a third-party notebook environment for exploratory analysis. Even "de-identified" data is safer to treat as PHI-scope during the transit and storage steps if the de-identification hasn't been formally attested. The BAA scope question is: does every service touching this data have a BAA with your organisation?

Question. A senior engineer wants to run a Jupyter notebook against a Snowflake extract via a small SaaS analytics tool. Trace the BAA chain and show where the pipeline breaks.

Input.

step	service	BAA status
1 — storage	Snowflake	BAA in place
2 — extract to CSV	Snowflake COPY INTO s3	AWS S3 BAA in place
3 — notebook host	Third-party SaaS notebook	no BAA
4 — visualisation	Same SaaS BI feature	no BAA

Code.

# baa-check.yaml — mandatory per-service gate in the deployment pipeline
services:
  snowflake:
    tier: "Business Critical"
    baa: signed 2024-03
    services_covered: [warehouse, snowpipe, tasks, streams]
  aws_s3:
    baa: signed 2019-11
    services_covered: [s3, kms, iam, cloudtrail]
  third_party_notebook_saas:
    baa: NOT SIGNED
    action: BLOCKED — either sign BAA or self-host notebook in the BAA-covered VPC

Step-by-step explanation.

1. Snowflake at the Business Critical tier has a BAA — safe to store PHI.
2. Snowflake COPY INTO s3://phi-extracts/... moves data into an S3 bucket under the organisation's AWS account; AWS BAA covers S3, KMS, IAM, and CloudTrail — safe.
3. The moment the notebook SaaS pulls that CSV over its own infrastructure, PHI leaves the BAA-covered chain — an unauthorised disclosure under §164.502.
4. The fix: either the vendor signs a BAA (many notebook SaaS vendors will for enterprise customers), or the notebook is self-hosted (JupyterHub or SageMaker) inside the BAA-covered VPC so the compute never leaves BAA scope.
5. The gate belongs in the deployment pipeline, not in a manual review — CI checks a signed manifest of BAA-covered services and blocks any deployment that adds a new service without a signed BAA.

Output.

decision	rationale
pipeline blocked	notebook SaaS is not BAA-covered
workaround 1	vendor signs BAA; document scope of services
workaround 2	self-host notebook in BAA-covered VPC
workaround 3	de-identify data via Safe Harbor + Expert Determination before extraction

Rule of thumb. Every new service that touches PHI must be added to a signed BAA manifest before the deployment ships. Treat the manifest as a compliance-critical config, versioned in source control with a required security-team approval on every change.

Senior interview question on healthcare regulatory framing

A senior interviewer often opens with: "Walk me through the two or three regulatory pillars a healthcare data engineer must satisfy in 2026, and show how each one changes a decision you would otherwise make."

Solution Using the HIPAA + interoperability + BAA-chain framework

Healthcare data engineering — the 3 pillars

1. HIPAA Privacy Rule (who sees PHI)
   - Minimum-necessary standard  → projection lint on every query
   - Authorisations required     → tracked in consent table
   - Individual right of access  → 15-day export SLO

2. HIPAA Security Rule (how PHI is protected)
   - Access control       → RBAC + RLS + break-glass log
   - Audit controls       → §164.312(b) immutable log, 6-year retention
   - Integrity            → digital signature / hash on ingestion
   - Encryption           → AES-256 at rest, TLS 1.3 in transit

3. Interoperability + BAA chain
   - 21st Century Cures Act → FHIR R4 API for patient/app access
   - USCDI v4 minimum       → standard columns exposed
   - BAA per service        → cloud/vendor manifest, CI-enforced
   - TEFCA participation    → QHIN outbound + inbound

Step-by-step trace.

Decision	Naive default	With HIPAA lens	Reason
Analytics table projection	SELECT *	explicit column list	minimum-necessary
Cloud vendor choice	cheapest region	BAA-eligible service	§164.308(b)(1)
Log retention	90 days	6 years, immutable	§164.316
Encryption at rest	KMS default	envelope-encrypted per-column keys	Security Rule §164.312(a)(2)(iv)
Third-party ML platform	Fastest sign-up	BAA + tenant isolation	disclosure control
Row-level access	app-side filter	SQL row-level security policy	Security Rule access control

The naive default is not a bug on a non-PHI dataset; it becomes a bug the moment PHI is present. The point of the pillars framework is to make every decision automatically PHI-safe by default.

Output:

Pillar	Data-engineering artefact
Privacy Rule	column-lineage lineup + purpose-of-use tags
Security Rule	encryption + RLS + audit log
Interop + BAA	FHIR API + BAA manifest + TEFCA endpoint

Why this works — concept by concept:

• Two-headed statute — the Privacy Rule and the Security Rule answer different questions ("who" vs "how") and lead to different engineering artefacts. Confusing them (encrypting your way out of a Privacy Rule violation) does not work.
• Minimum-necessary as a linter — the rule is easy to state, hard to enforce. The scalable answer is a CI check: any SQL touching a PHI table must have an explicit projection and a documented purpose tag.
• BAA chain is a graph, not a checklist — every service in the data path is a node; a missing BAA on any single node breaks the whole path. Treat the manifest as a build artefact, not a spreadsheet.
• Cures Act made interop legally binding — refusing to expose a FHIR API is now information blocking. Roadmaps that plan to "get around to FHIR" are non-starters.
• Cost — HIPAA compliance adds ~10–20% to the DE build cost (encryption, audit, BAA reviews, training) and shortens the time between "we shipped it" and "we got sued for not shipping it." The math almost always favours getting it right the first time.

SQL
Topic — sql
SQL practice for healthcare projection + minimum-necessary rewrites

Practice →

SQL Topic — joins Join problems for PHI + consent + cohort filters

Practice →

---

2. HL7 v2 and FHIR ingestion

hl7 pipeline and fhir data engineering — the pipe-delimited legacy and the JSON future run side-by-side in every 2026 hospital

The mental model in one line: HL7 v2 is a pipe-delimited, segment-oriented, MLLP-transported message format from 1989 that still moves 95% of intra-hospital clinical traffic in 2026, while FHIR R4 is a JSON REST + Bundle format from 2019 that every 21st Century Cures Act–certified EHR must expose — a modern hospital DE pipeline reads both in parallel and normalises to a single bronze layer. Once you internalise "HL7 v2 for legacy, FHIR for modern, same landing zone," the entire ehr data pipeline interview surface collapses to two parsers feeding one downstream contract.

HL7 v2 — the pipe-delimited legacy.

• Segments and fields — every message is a sequence of newline-separated segments (MSH, PID, OBX, ...). Fields inside a segment are separated by |; components inside a field by ^; repetitions by ~; sub-components by &. The MSH segment (Message Header) is always first and defines the delimiters.
• Message types — ADT^A01 (patient admission), ADT^A03 (discharge), ADT^A08 (patient update), ORM^O01 (order message), ORU^R01 (observation result), SIU^S12 (schedule information). The two-letter type + trigger event is the contract.
• MLLP transport — Minimal Lower Layer Protocol. TCP framing with <VT> (0x0B) start byte, <FS><CR> (0x1C 0x0D) end. The ACK/NAK response is another HL7 message on the same connection.
• The 2.x versions — 2.3.1 (dominant install base), 2.5.1 (US Meaningful Use requirement), 2.7+ (newer facilities). Version incompatibility is the source of endless integration pain.

FHIR R4 — the JSON future.

• REST resources — every clinical concept is a REST resource: Patient, Observation, Encounter, Condition, MedicationRequest, AllergyIntolerance, Immunization, etc. Each has a URL: GET /Patient/123.
• Bundle — a container resource that batches multiple resources into a single call. Used for atomic transactions (type=transaction), search results (type=searchset), and bulk export (type=collection).
• SMART-on-FHIR OAuth — the standard authorisation flow. A user (or app) requests a scope like patient/*.read or system/Observation.read, gets an access token, and every subsequent FHIR call carries the token in the Authorization: Bearer header.
• Bulk data — the $export operation returns NDJSON files per resource type (Patient.ndjson, Observation.ndjson, ...) via a polling status endpoint. This is how you extract population-level data without hammering the REST endpoint per patient.

FHIRPath — the query language you can't avoid.

• A CSS-selector-like path syntax for FHIR resources: Observation.value.ofType(Quantity).value extracts the numeric value from a quantity-typed observation.
• Used inside search parameters, invariant rules, and the $evaluate-measure operation. Every serious FHIR pipeline touches FHIRPath sooner or later.
• FHIRPath is standardised in the FHIR spec — libraries exist for Java, JS, .NET, and Python (fhirpath-py).

The bronze-layer contract — one landing zone for both formats.

• Both parsers land into the same bronze schema: event_source (hl7v2 | fhir_r4), resource_type (Patient | Observation | Encounter | ...), event_id, patient_id, event_time, payload_json, ingested_at.
• The HL7 v2 parser maps PID → Patient resource, OBX → Observation resource, PV1 → Encounter resource. The mapping is lossy in exotic edge cases but covers the 80% ingest path cleanly.
• Downstream silver layer normalises the JSON payload into typed columns per resource type; gold layer feeds analytics and the OMOP CDM.

Common interview probes on ingestion.

• "What are the differences between HL7 v2 and FHIR?" — segment/pipe vs REST/JSON, ADT vs Encounter, MLLP vs HTTPS.
• "How do you handle MLLP framing?" — start byte 0x0B, end bytes 0x1C 0x0D, ACK/NAK response required within timeout.
• "What is a FHIR Bundle and when do you use it?" — container for multiple resources; use for transactions, search results, bulk data.
• "What is SMART-on-FHIR and why does it matter?" — the standard OAuth 2.0 authorisation for FHIR APIs; enables third-party apps against certified EHRs.

Worked example — parsing an HL7 v2 ADT message

Detailed explanation. A hospital admission triggers an ADT^A01 message from the EHR to every downstream consumer. The message is pipe-delimited; extracting the patient MRN, name, DOB, and admission time is the base ingest primitive every HL7 pipeline needs.

Question. Write a Python parser that takes a raw HL7 v2 ADT^A01 message and returns a dict of patient + admission fields. Handle the multi-repetition PID.3 (multiple patient identifiers).

Input.

field	example value
MSH	delimiters, sending/receiving app, timestamp, type
PID	patient identifiers, name, DOB, sex
PV1	admission class, ward, admit datetime

Code.

from datetime import datetime

def parse_adt_a01(message: str) -> dict:
    # MLLP framing already stripped upstream
    segments = message.strip().split("\r")

    header = segments[0].split("|")     # MSH
    # header[1] is field/component delimiters — "^~\&"
    msg_ts = header[6]                  # YYYYMMDDHHMMSS
    msg_type = header[8].split("^")     # ["ADT", "A01"]

    pid = next(s for s in segments if s.startswith("PID"))
    pid_f = pid.split("|")

    # PID.3 can repeat via ~; take the MR-typed identifier
    id_repetitions = pid_f[3].split("~")
    mrn = next(rep.split("^")[0]
               for rep in id_repetitions
               if rep.split("^")[-1] == "MR")

    name_parts = pid_f[5].split("^")    # family^given^middle
    dob_raw = pid_f[7]                  # YYYYMMDD
    dob = datetime.strptime(dob_raw, "%Y%m%d").date()
    sex = pid_f[8]

    pv1 = next(s for s in segments if s.startswith("PV1"))
    pv1_f = pv1.split("|")
    admit_class = pv1_f[2]              # I (inpatient) | O (outpatient)
    admit_ward = pv1_f[3].split("^")[0]
    admit_ts_raw = pv1_f[44]
    admit_ts = datetime.strptime(admit_ts_raw, "%Y%m%d%H%M%S")

    return {
        "mrn": mrn,
        "family_name": name_parts[0],
        "given_name": name_parts[1],
        "dob": dob.isoformat(),
        "sex": sex,
        "admit_class": admit_class,
        "admit_ward": admit_ward,
        "admit_ts": admit_ts.isoformat(),
        "message_ts": msg_ts,
        "message_type": "^".join(msg_type),
    }

Step-by-step explanation.

1. HL7 v2 uses \r (carriage return) as the segment separator — not \n. Splitting on \n is the classic bug that ships an empty parser to prod.
2. The MSH (Message Header) segment declares the encoding characters in MSH.2 (^~\&) — ^ component, ~ repetition, \ escape, & sub-component. Modern parsers assume the defaults; industrial parsers must read them.
3. PID.3 (Patient Identifier List) can repeat via ~ — a patient may carry an MRN, an SSN, an insurance ID all in one field. We filter for the MR-typed one; the type is the last component.
4. PID.5 (Patient Name) is a composite: family^given^middle^suffix^prefix. The order matters and is fixed by the standard.
5. PID.7 (DOB) is YYYYMMDD — no separator, always 8 digits, no timezone.
6. PV1.2 (Patient Class) uses coded values: I inpatient, O outpatient, E emergency, P pre-admit. PV1.44 is the Admit Date/Time (YYYYMMDDHHMMSS).

Output.

field	value
mrn	482910
family_name	Smith
given_name	John
dob	1978-04-12
sex	M
admit_class	I
admit_ward	3W
admit_ts	2026-07-15T09:14:22
message_ts	20260715091500
message_type	ADT^A01

Rule of thumb. Never write the HL7 v2 parser by hand for production. Use hl7apy (Python), HAPI (Java), or nhapi (.NET) — they handle escape characters, Z-segments (custom vendor extensions), and the 60+ standard segments. Hand-rolled parsers only make sense for a one-off extraction.

Worked example — MLLP listener that ACKs every message

Detailed explanation. The MLLP framing means you cannot just read from a TCP socket line by line. You must consume bytes until you see the end sentinel 0x1C 0x0D, then reply with an MSA (Message Acknowledgment) message wrapped in the same framing. Failing to ACK causes the sender to retry indefinitely and pile up connections.

Question. Write a minimal MLLP listener in Python that accepts an HL7 v2 message and returns an application ACK (AA — Application Accept).

Input.

protocol	framing bytes
MLLP	<VT> = 0x0B (start), <FS><CR> = 0x1C 0x0D (end)

Code.

import socket
from datetime import datetime

VT, FS, CR = b"\x0b", b"\x1c", b"\x0d"

def build_ack(orig_msh: bytes, ack_code: str = "AA") -> bytes:
    ts = datetime.utcnow().strftime("%Y%m%d%H%M%S")
    fields = orig_msh.decode("latin-1").split("|")
    ack_msh = (
        f"MSH|^~\\&|{fields[4]}|{fields[5]}|{fields[2]}|{fields[3]}|"
        f"{ts}||ACK^A01|{fields[9]}|P|2.5.1"
    )
    msa = f"MSA|{ack_code}|{fields[9]}"
    return VT + f"{ack_msh}\r{msa}\r".encode("latin-1") + FS + CR

def serve(host: str = "0.0.0.0", port: int = 2575) -> None:
    with socket.socket(socket.AF_INET, socket.SOCK_STREAM) as srv:
        srv.setsockopt(socket.SOL_SOCKET, socket.SO_REUSEADDR, 1)
        srv.bind((host, port))
        srv.listen(64)
        while True:
            conn, _ = srv.accept()
            with conn:
                buf = bytearray()
                while True:
                    chunk = conn.recv(4096)
                    if not chunk:
                        break
                    buf.extend(chunk)
                    if buf.endswith(FS + CR):
                        payload = bytes(buf[1:-2])   # strip VT ... FS CR
                        msh = payload.split(b"\r", 1)[0]
                        conn.sendall(build_ack(msh, "AA"))
                        buf.clear()

Step-by-step explanation.

1. Bind on TCP port 2575 (the de facto MLLP port). Accept connections and keep them open — most senders reuse a single long-lived TCP connection for hundreds of messages.
2. Read bytes into a buffer until the buffer ends with 0x1C 0x0D (the FS + CR end sentinel). That marks one complete HL7 message.
3. Strip the leading VT (0x0B) and trailing FS + CR — the payload between them is the raw HL7 message bytes.
4. Parse the MSH segment far enough to know the message control ID (fields[9]) and the sender/receiver — we swap them on the ACK.
5. Build an ACK message with MSA|AA|<control_id> (AA = Application Accept; AE = Application Error; AR = Application Reject).
6. Wrap the ACK in the same MLLP framing (VT + payload + FS + CR) and send it back on the same connection.
7. Reset the buffer and loop for the next message. Never close the connection unless the peer does — MLLP connections are long-lived.

Output (single message round-trip).

step	direction	bytes
1	client → server	`MSH
2	server → client	{% raw %}`MSH

Rule of thumb. Send ACK before you do any heavy processing — the sender's retry timer is often 5 seconds. Do the parse + persist asynchronously (queue the raw message and ACK immediately) so that a slow downstream cannot back up the ingress side and cause message replay storms.

Worked example — flattening a FHIR Bundle into a bronze table

Detailed explanation. A FHIR Bundle from a patient's chart contains a Patient resource plus dozens of Observations, Conditions, Encounters, and MedicationRequests. Landing that as a single JSON blob is fine; downstream analytics needs the resources broken out into typed rows in a bronze table.

Question. Given a FHIR Bundle JSON payload, emit one bronze-layer row per resource entry. Preserve the resource type, resource id, patient reference, and the raw JSON payload.

Input.
{% raw %}

{
  "resourceType": "Bundle",
  "type": "collection",
  "entry": [
    { "resource": { "resourceType": "Patient", "id": "p-9182", "name": [{"family": "Smith", "given": ["John"]}] } },
    { "resource": { "resourceType": "Observation", "id": "o-1", "subject": {"reference": "Patient/p-9182"}, "code": {"coding": [{"system": "http://loinc.org", "code": "2093-3"}]}, "valueQuantity": {"value": 210, "unit": "mg/dL"}, "effectiveDateTime": "2026-07-14T09:00:00Z" } },
    { "resource": { "resourceType": "Encounter", "id": "e-88", "subject": {"reference": "Patient/p-9182"}, "period": {"start": "2026-07-14T08:30:00Z", "end": "2026-07-14T10:00:00Z"} } }
  ]
}

Code.

import json, uuid
from datetime import datetime, timezone

def flatten_bundle(bundle: dict, ingested_at: datetime | None = None) -> list[dict]:
    ingested_at = ingested_at or datetime.now(timezone.utc)
    rows: list[dict] = []
    for entry in bundle.get("entry", []):
        r = entry.get("resource", {})
        rtype = r.get("resourceType")
        rid = r.get("id")
        subj = r.get("subject", {}).get("reference", "")
        patient_id = subj.split("/", 1)[1] if subj.startswith("Patient/") else None
        event_time = (
            r.get("effectiveDateTime")
            or (r.get("period") or {}).get("start")
            or r.get("recordedDate")
            or r.get("issued")
            or ingested_at.isoformat()
        )
        rows.append({
            "event_source": "fhir_r4",
            "resource_type": rtype,
            "resource_id": rid,
            "patient_id": patient_id or (rid if rtype == "Patient" else None),
            "event_time": event_time,
            "payload_json": json.dumps(r, separators=(",", ":")),
            "ingested_at": ingested_at.isoformat(),
            "row_id": str(uuid.uuid4()),
        })
    return rows

Step-by-step explanation.

1. Iterate every entry[].resource in the Bundle. Each entry is one FHIR resource.
2. Extract resourceType and id — the type is the join key for downstream typed silver views; the id uniquely identifies the resource within its type.
3. Resolve the patient reference — most non-Patient resources have subject.reference = "Patient/<id>". Split on / and keep the id part.
4. Pick the event time from a resource-specific field: effectiveDateTime for Observation, period.start for Encounter, recordedDate for Condition, issued for DiagnosticReport. Fall back to ingested_at if none is present.
5. Store the raw JSON as a compact string — no pretty-printing, no key reordering. Downstream parsing must be able to re-hydrate the exact source payload.
6. Add event_source = fhir_r4 and a UUID row_id for idempotency across replays.

Output (bronze rows).

event_source	resource_type	resource_id	patient_id	event_time
fhir_r4	Patient	p-9182	p-9182	2026-07-15T12:00:00Z
fhir_r4	Observation	o-1	p-9182	2026-07-14T09:00:00Z
fhir_r4	Encounter	e-88	p-9182	2026-07-14T08:30:00Z

Rule of thumb. Always flatten Bundles at ingest time; never store the raw Bundle as the atomic row. The Bundle is a transport container, not a storage unit — downstream queries expect one row per resource, not one row per Bundle.

Senior interview question on ingestion architecture

A senior interviewer might ask: "You need to build a real-time patient-arrival dashboard fed by a legacy Epic HL7 v2 feed and a modern Athenahealth FHIR feed. Walk me through the ingestion architecture — protocols, parsers, landing zone, and how you handle a 3am outage on one of the two feeds."

Solution Using MLLP + FHIR REST → shared bronze → dashboard silver

Two-protocol ingestion — one bronze layer

┌─ HL7 v2 (Epic) ─────────────────────────────────────────┐
│  Epic EHR ── MLLP TCP:2575 ── mllp-gateway ── kafka.hl7 │
│                              (ACK immediately)          │
└─────────────────────────────────────────────────────────┘
                              │
┌─ FHIR R4 (Athenahealth) ────┼─────────────────────────────┐
│  Poll GET /fhir/Encounter?  │   ── fhir-poller ── kafka.fhir│
│    _lastUpdated=gt<ts>      │   (SMART-on-FHIR token)      │
└────────────────────────────────────────────────────────────┘
                              │
                              ▼
              ┌────────────── kafka topic (union) ──────────────┐
              │  key = patient_id, value = raw payload + source  │
              └───────────────────────┬────────────────────────┘
                                      │
                                      ▼
                    ┌── flink / spark structured stream ──┐
                    │   HL7 parser  or  FHIR flattener    │
                    │   → bronze delta table              │
                    └──────────────────┬──────────────────┘
                                       │
                                       ▼
                   ┌── silver views (Encounter typed) ──┐
                   │   → dashboard (grafana / superset) │
                   └────────────────────────────────────┘

Step-by-step trace.

Step	HL7 v2 lane	FHIR lane
1 — transport	MLLP long-lived TCP	HTTPS + OAuth Bearer
2 — framing	VT / FS+CR sentinels	JSON body
3 — ack	MSA|AA within 5s	200 OK
4 — persist	raw bytes → kafka.hl7	raw JSON → kafka.fhir
5 — parse	hl7apy → dict	flatten_bundle → rows
6 — land	bronze delta (event_source=hl7v2)	bronze delta (event_source=fhir_r4)
7 — silver	typed Encounter view	typed Encounter view

Outage handling. If MLLP goes down, the Epic sender queues messages and retries — as long as mllp-gateway comes back up, no messages are lost. If FHIR polling fails, the poller replays from the last successful _lastUpdated watermark on recovery. Both approaches survive multi-hour outages without data loss.

Output:

Concern	HL7 v2	FHIR R4
Delivery model	push (sender initiates)	pull (poller initiates)
Auth	network-level (VPN + IP allow)	SMART-on-FHIR OAuth
Ordering	per-connection FIFO	per-resource _lastUpdated
Idempotency key	MSH.10 control id	resource id + meta.versionId
Replay on failure	sender retries via MLLP	poller re-reads by watermark
Latency	sub-second	sub-minute (polling interval)

Why this works — concept by concept:

• Two protocols, one landing zone — the bronze schema is protocol-agnostic (event_source column disambiguates). Downstream silver/gold layers do not care whether the source was HL7 v2 or FHIR.
• ACK-then-process on MLLP — separating the transport ACK from the parse guarantees that a parser bug or a slow downstream cannot back up the sender. The parser runs asynchronously off the Kafka topic.
• Watermark polling on FHIR — the poller stores the last successful _lastUpdated timestamp per resource type and replays from that point. Missed windows self-heal on the next successful poll.
• SMART-on-FHIR scope discipline — the poller's OAuth token is scoped to system/Encounter.read only; any attempt to pull other resource types fails at the API. Minimum-necessary enforced at the token boundary.
• Cost — HL7 v2 is O(1) per message on the ingress side (ACK is immediate); FHIR polling is O(delta since last watermark) per poll. Both approaches scale to millions of daily events per hospital with modest compute.

SQL
Topic — joins
Joins for HL7-to-FHIR bronze/silver merges

Practice →

SQL Topic — sql SQL drills for FHIR flattening + resource typing

Practice →

---

3. PHI masking + de-identification

phi masking is a three-layer defence — Safe Harbor strip, deterministic tokenisation, and per-patient date shift; skip any layer and re-identification becomes trivial

The mental model in one line: HIPAA §164.514 defines two paths to legal de-identification — Safe Harbor (strip all 18 identifiers) or Expert Determination (statistical attestation of low re-identification risk) — and a production phi masking pipeline typically combines Safe Harbor strip + Format-Preserving Encryption tokens + per-patient date shift + k-anonymity checks so the resulting dataset is safe for research, analytics, and non-BAA-scoped consumers. Once you internalise "strip, tokenise, shift, verify," the entire de-identification interview surface collapses to a repeatable pipeline you can lint in CI.

HIPAA Safe Harbor — the checklist path.

• Remove all 18 identifiers listed in §164.514(b)(2). Only year is allowed for dates directly related to an individual (birth, admission, discharge, death); ages over 89 must be aggregated to "90+".
• Geographic areas smaller than a state must be removed unless the initial three digits of the zip code cover a population of more than 20,000 people (approximately 17 zip prefixes are excluded and must be replaced with 000).
• The covered entity must have no actual knowledge that the remaining information could be used alone or in combination to identify an individual. "Actual knowledge" is a stricter test than "reasonable expectation."
• Simplest to implement; most restrictive on downstream utility (you lose exact dates, zip precision, granular ages).

Expert Determination — the statistical path.

• A qualified statistician attests in writing that the risk of re-identification is very small, using a documented statistical or scientific method. There is no fixed numeric threshold, but 0.09 (industry rule of thumb from Sweeney's work) is a common upper bound for r ≤ 0.09.
• Allows retention of some identifiers (e.g., partial dates, richer geographic detail) as long as the statistical risk analysis supports it.
• More flexible for research utility; expensive because you need a qualified expert to attest and re-attest as your data grows.

k-anonymity, l-diversity, t-closeness — the three-layer risk metric stack.

• k-anonymity — every combination of quasi-identifiers (age, zip, sex, race) appears in at least k records. Prevents "singling out" a patient from the shape of their non-direct identifiers. Common minimum: k = 5 for research; k = 10 for public release.
• l-diversity — within each k-anonymous equivalence class, the sensitive attribute takes at least l distinct values. Prevents attribute disclosure ("everyone in this 5-person group has HIV").
• t-closeness — the distribution of the sensitive attribute in each equivalence class is within threshold t of the distribution in the whole dataset. Prevents distributional disclosure ("this group is 90% HIV+ vs 5% overall").

Deterministic tokenisation — Format-Preserving Encryption (FPE).

• The problem — replace an MRN with a token, but the token must be the same length and character set as the MRN (so downstream systems don't break) and deterministic (so the same MRN always yields the same token for joins across systems).
• The tool — NIST SP 800-38G FPE modes (FF1 and FF3-1). Both use AES underneath. FF1 is preferred for security; FF3-1 is more efficient.
• Key management — keys live in a KMS / HSM; token generation calls the KMS. Rotating the key produces a different token for the same input, which breaks longitudinal analysis — key rotation for FPE requires a re-encryption pass.

Per-patient date shift — preserving intervals without leaking dates.

• For every patient, generate a random shift Δ in the range ±N days (e.g., ±365) using a seeded PRF keyed on the patient's stable id. Apply Δ to every date in that patient's record.
• Intervals within a patient are preserved (encounter-to-encounter days, medication duration). Absolute dates are meaningless.
• The shift is deterministic per patient — the same patient always gets the same shift on re-generation, so successive extracts remain joinable.

Redaction of free text — NER + regex.

• Clinical notes carry PHI inside prose: patient names in dictations, phone numbers in referrals, addresses in social history.
• Modern pipelines run Named Entity Recognition (spaCy clinical models, AWS Comprehend Medical, or a fine-tuned BERT) plus regex sweeps (phone patterns, SSN patterns, MRN patterns) and replace matches with typed placeholders ([PERSON], [PHONE], [MRN]).
• No 100% recall — expect 1-3% miss rate. Compensate with a downstream sensitivity check and never treat free-text de-identification as "safe for public release" without expert attestation.

Synthetic patient data — SynthEA.

• SynthEA generates entirely synthetic patient records grounded in real-world epidemiology. Zero PHI risk because no real patient is behind any record.
• Excellent for engineering, testing, demo data, and CI fixtures. Not a substitute for real data in research; the underlying models are approximations.

Snowflake DYNAMIC DATA MASKING — the warehouse-native path.

• Snowflake lets you attach a masking policy to a column: CREATE MASKING POLICY mrn_mask AS (val string) RETURNS string -> CASE WHEN CURRENT_ROLE() IN ('CLINICIAN') THEN val ELSE 'MASKED' END.
• Applied to the column via ALTER TABLE ... MODIFY COLUMN mrn SET MASKING POLICY mrn_mask. Every read from the column runs through the policy; the policy sees the caller's role/session context.
• Reversible for authorised roles; irreversible-appearing for others without a second export. Combines cleanly with row-level security.

Common interview probes on masking.

• "What are the 18 Safe Harbor identifiers?" — list them (or at least the top 10).
• "What is the difference between Safe Harbor and Expert Determination?" — checklist vs statistical attestation.
• "Why is deterministic tokenisation important?" — cross-system joins on the token instead of the raw MRN.
• "What is k-anonymity and why isn't it enough on its own?" — protects against singling-out but not against attribute or distributional disclosure; need l-diversity and t-closeness on top.

Worked example — Safe Harbor strip in a single SQL

Detailed explanation. A raw patients table in the warehouse holds every field the EHR ships. A de-identified patients_deid view for the analytics team is the workhorse artefact; it needs to strip every Safe Harbor identifier while keeping the analytics-useful columns intact.

Question. Write a SQL view that produces a Safe Harbor-compliant patients_deid from a raw patients table. Age must be capped at 89, only year is allowed on dates, and 3-digit zip must fall back to 000 for restricted zips.

Input.

column	example	is Safe Harbor identifier
mrn	MRN-482910	yes (id #7)
name	John Smith	yes (id #1)
dob	1978-04-12	yes (id #3, date)
age	48	derived; cap at 89
zip5	94103	yes (id #2, <state)
zip3	941	keep if pop > 20K
email	j.smith@...	yes (id #6)
phone	415-555-...	yes (id #4)
condition	E11.9	non-identifier

Code.

CREATE OR REPLACE VIEW patients_deid AS
SELECT
    -- Safe Harbor strips
    NULL::varchar     AS mrn,             -- id #7
    NULL::varchar     AS name,            -- id #1
    NULL::varchar     AS email,           -- id #6
    NULL::varchar     AS phone,           -- id #4
    NULL::varchar     AS ssn,             -- id #7 (adjacent)
    NULL::varchar     AS address_full,    -- id #2

    -- Dates: year only
    EXTRACT(YEAR FROM dob)              AS dob_year,
    LEAST(EXTRACT(YEAR FROM CURRENT_DATE) - EXTRACT(YEAR FROM dob), 90) AS age_capped,

    -- Geography: 3-digit zip with restricted-zip fallback
    CASE WHEN LEFT(zip5, 3) IN ('036','059','063','102','203','556','692','790','821','823','830','831','878','879','884','890','893')
         THEN '000'
         ELSE LEFT(zip5, 3)
    END                                    AS zip3,

    -- Non-identifier clinical data flows through
    sex,
    race,
    ethnicity,
    condition_primary,
    encounter_year        -- year-only, not full date

FROM patients
WHERE deceased_ind = FALSE
   OR (deceased_ind = TRUE AND EXTRACT(YEAR FROM deceased_date) < EXTRACT(YEAR FROM CURRENT_DATE) - 50);

Step-by-step explanation.

1. mrn, name, email, phone, ssn, address_full are all direct identifiers on the Safe Harbor list — set to NULL in the view.
2. dob is a date related to the individual — only dob_year is allowed. age_capped uses LEAST(age, 90) to comply with the "90+" rule for ages over 89.
3. zip5 is a geographic subdivision smaller than a state — not allowed. zip3 is allowed unless the 3-digit prefix is one of the 17 low-population prefixes; the CASE above replaces them with 000.
4. sex, race, ethnicity, condition_primary, encounter_year are not direct identifiers — they flow through. They are quasi-identifiers though, which is why k-anonymity is the next layer.
5. The WHERE handles the deceased-more-than-50-years-ago exception: dates related to individuals who died more than 50 years ago are no longer PHI under the 2013 HIPAA Omnibus Rule.

Output (sample row).

mrn	name	dob_year	age_capped	zip3	sex	condition_primary
NULL	NULL	1978	48	941	M	E11.9

Rule of thumb. Ship the de-identified view as the only interface the analytics team ever queries. Revoke SELECT on the raw patients table from every non-clinical role. Enforcement via GRANT is more reliable than "please only query the view."

Worked example — Format-Preserving Encryption token for MRN

Detailed explanation. For research where cross-system joins matter (e.g., matching hospital encounters to insurance claims), a raw MRN cannot be exported but a deterministic token can. FPE gives you a token in the same character set and length as the original MRN, so downstream systems and column widths do not need to change.

Question. Show a Python FF3-1 FPE implementation that turns an 8-digit MRN into an 8-digit token, using a KMS-held key. Demonstrate that the same MRN always yields the same token and that the token is reversible only inside the KMS boundary.

Input.

column	value
raw MRN	48291005 (8 digits)
token alphabet	digits 0-9
key	KMS-held 256-bit AES key, tweak per data domain

Code.

from ff3 import FF3Cipher  # pip install ff3

# Key is fetched from KMS at process start; tweak scopes the tokens to a domain.
# Rotating the key requires a re-encryption pass — plan for it.
KEY = get_kms_key("phi/fpe/mrn")             # 32-byte hex
TWEAK = "0123456789abcdef"                   # 8-byte hex domain tweak

cipher = FF3Cipher.withCustomAlphabet(KEY, TWEAK, "0123456789")

def token_from_mrn(mrn: str) -> str:
    # MRN is 8 digits — FF3-1 requires 2..56 chars for a 10-char alphabet
    return cipher.encrypt(mrn)

def mrn_from_token(token: str) -> str:
    # Reversal MUST live inside the PHI enclave; never expose in downstream apps
    return cipher.decrypt(token)

assert token_from_mrn("48291005") == token_from_mrn("48291005")   # deterministic
assert token_from_mrn("48291005") != "48291005"                    # not identity
assert mrn_from_token(token_from_mrn("48291005")) == "48291005"    # reversible w/ key

Step-by-step explanation.

1. FF3-1 is a NIST SP 800-38G FPE mode. It operates on strings of length 2..56 characters from a chosen alphabet, using AES underneath. The output preserves the alphabet and length of the input.
2. The TWEAK parameter scopes the encryption to a data domain — the same key with a different tweak produces different tokens. Use per-table tweaks so an MRN token in the encounter table cannot be joined to an MRN token in the claims table by accident.
3. Determinism means f(mrn) == f(mrn) — repeated calls yield the same token. This is what enables downstream joins on the token.
4. Reversibility requires the KMS key — the decrypt path lives strictly inside the PHI enclave (typically inside a Snowflake external function, a dedicated microservice, or an EMR compute inside the BAA-covered VPC).
5. Do not confuse with hashing. A SHA-256 hash is one-way but changes length and character set. FPE preserves both, at the cost of being reversible for whoever holds the key.

Output.

raw MRN	FPE token
48291005	71503482
30021198	92148307
48291005 (repeat)	71503482 (deterministic)

Rule of thumb. Use FPE only when downstream systems require the token to be joinable and format-compatible with the original. If joins don't matter, use a straight cryptographic hash (HMAC-SHA-256 with a per-purpose salt) — hashes are irreversible and simpler to defend.

Worked example — per-patient date shift

Detailed explanation. Research often needs to preserve time intervals between events (days between admission and readmission, medication duration) while hiding absolute dates. Per-patient date shift adds a random Δ from a bounded range to every date in that patient's record, deterministically per patient.

Question. Implement a per-patient date shift that adds a deterministic Δ in [-365, +365] days to every date column, seeded on the patient's stable id.

Input.

patient_id	encounter_date	discharge_date
p-9182	2026-07-14	2026-07-16
p-2003	2026-07-14	2026-07-20

Code.

import hashlib
from datetime import date, timedelta

SHIFT_KEY = get_kms_key("phi/date-shift")   # 32-byte secret from KMS
MAX_SHIFT_DAYS = 365

def shift_for(patient_id: str) -> int:
    # HMAC keyed by SHIFT_KEY over the patient id; take the first 4 bytes as int
    mac = hashlib.blake2b(patient_id.encode(), key=SHIFT_KEY.encode(), digest_size=4)
    raw = int.from_bytes(mac.digest(), "big")
    # Map to [-MAX_SHIFT_DAYS, +MAX_SHIFT_DAYS]
    return (raw % (2 * MAX_SHIFT_DAYS + 1)) - MAX_SHIFT_DAYS

def shift_date(patient_id: str, d: date) -> date:
    return d + timedelta(days=shift_for(patient_id))

# Applied per row
rows = [
    ("p-9182", date(2026, 7, 14), date(2026, 7, 16)),
    ("p-2003", date(2026, 7, 14), date(2026, 7, 20)),
]
for pid, enc, dis in rows:
    print(pid, shift_date(pid, enc), shift_date(pid, dis), (dis - enc).days)

Step-by-step explanation.

1. Derive a stable per-patient Δ via a keyed hash (BLAKE2b HMAC) of the patient_id. The key lives in KMS; without it, the shift is uninvertible from the outside.
2. Map the hash output to [-365, +365] — a full-year window keeps the shift meaningful for seasonal analysis (a summer admission stays summer-ish) while breaking absolute-date matching.
3. Apply the same Δ to every date column for that patient — encounter, discharge, medication start, medication end. Intervals within the patient are preserved (discharge - encounter stays the same); absolute dates are shifted.
4. Different patients get different shifts, so joining two patients' timelines on absolute date is meaningless.
5. Determinism is critical: successive extracts of the same patient produce the same shifted dates, so downstream joins across extracts still work.

Output.

patient_id	encounter_date (shifted)	discharge_date (shifted)	days_between (preserved)
p-9182	2025-11-02	2025-11-04	2
p-2003	2027-01-08	2027-01-14	6

Rule of thumb. Combine date shift with year-only truncation for maximum safety. On highly-sensitive research releases, add a ±30-day bucketing on top so exact dates are not even recoverable from an inside attacker who happens to know one patient's real admission date.

Senior interview question on the full masking pipeline

A senior interviewer might ask: "Design a PHI masking pipeline for a research extract. The output must be safe for a non-BAA-scoped academic collaborator, joinable to a claims extract from a third party, and preserve enough utility for a survival analysis. Walk through the layers."

Solution Using Safe Harbor strip + FPE tokens + date shift + k-anonymity gate

Research-safe PHI masking pipeline

┌─ Raw PHI (inside BAA + PHI enclave) ─────────────────────────────┐
│  patients, encounters, meds, labs                                │
└───────────────────────┬──────────────────────────────────────────┘
                        │
                        ▼
    ┌── Layer 1: Safe Harbor strip ──┐
    │  name/email/phone/SSN → NULL   │
    │  dob → year, age capped at 90  │
    │  zip5 → zip3 (with fallback)   │
    └────────────────┬───────────────┘
                     │
                     ▼
    ┌── Layer 2: FPE token per join key ──┐
    │  mrn → tk_mrn (FF3-1, per-domain)   │
    │  claim_id → tk_claim (separate key) │
    └────────────────┬────────────────────┘
                     │
                     ▼
    ┌── Layer 3: Per-patient date shift ──┐
    │  ±365d deterministic per patient    │
    │  intervals preserved                │
    └────────────────┬────────────────────┘
                     │
                     ▼
    ┌── Layer 4: k-anonymity + l-diversity gate ──┐
    │  k ≥ 5 on (age, zip3, sex, race)            │
    │  l ≥ 2 on primary_condition                 │
    │  drop rows in classes that fail             │
    └────────────────┬────────────────────────────┘
                     │
                     ▼
    ┌── Layer 5: Expert attestation + release ──┐
    │  qualified statistician signs risk memo   │
    │  release DUA to collaborator              │
    └───────────────────────────────────────────┘

Step-by-step trace.

Layer	Removes	Preserves
1 — Safe Harbor	direct identifiers	year, coarse geography
2 — FPE token	raw MRN, raw claim id	joinability on tokens
3 — Date shift	absolute dates	intervals per patient
4 — k-anonymity	rare quasi-id combinations	analytics on common groups
5 — Attestation	none (control gate)	provable due-diligence

After Layer 4, roughly 3-8% of rows typically drop (rare age/zip/condition combinations); Layer 5 documents the residual risk as very small (r ≤ 0.09).

Output:

Consumer	Sees	Can they re-identify?
Analyst inside PHI enclave (Layer 0)	Raw PHI	yes, has FK to KMS
Analyst outside enclave (Layer 3)	Tokens + shifted dates	no (needs KMS + shift key)
Academic collaborator (Layer 5)	Layer 3 minus small classes	no (attested r ≤ 0.09)
Public release	Not covered by this pipeline	(would need aggregation + differential privacy)

Why this works — concept by concept:

• Layered defence — Swiss cheese model — no single layer stops re-identification alone. Safe Harbor stops direct identifiers; FPE stops raw-key leakage; date shift stops absolute-date pinning; k-anonymity stops quasi-identifier singling. An attacker must defeat all four.
• Deterministic tokens for join utility — FPE tokens preserve the ability to join extracts across time and across systems, without which longitudinal analysis is impossible.
• Interval preservation — shifted-but-consistent dates keep survival curves, medication adherence windows, and time-to-event calculations valid; absolute dates leak nothing.
• k-anonymity gate — the automated drop of rows in rare quasi-identifier equivalence classes turns the abstract "risk ≤ small" into a mechanical check that runs in CI.
• Attestation as the legal shield — Expert Determination gives the release a documented, signed risk memo. When a regulator asks "why did you release this data?", the memo is the answer.
• Cost — each layer costs a small amount of CPU + engineering time (FPE is ~microseconds per token; date shift is O(1)); the k-anonymity check is the expensive one (O(rows) with a group-by). Total pipeline cost is trivial compared to the audit-and-litigation cost of a badly-de-identified release.

SQL
Topic — aggregation
Aggregation drills for k-anonymity + l-diversity checks

Practice →

SQL Topic — sql SQL practice for masking policies + Safe Harbor projections

Practice →

---

4. Audit logging + access controls

HIPAA §164.312(b) makes the audit log a first-class pipeline artefact — every PHI touch logged, tamper-evident, six-year retention

The mental model in one line: HIPAA Security Rule §164.312(b) requires "hardware, software, and/or procedural mechanisms that record and examine activity in information systems that contain or use electronic protected health information" — which translates in 2026 to an append-only, tamper-evident audit log capturing every PHI access, with row-level security gating the reads, encryption everywhere, a signed BAA on every service in the path, and a minimum six-year retention on the log itself. Once you internalise "log every touch, keep six years, tamper-evident," the entire hipaa data pipeline access-control interview surface becomes a mechanical checklist.

§164.312 — the Security Rule technical safeguards.

• (a) Access control. Unique user identification, emergency access procedure, automatic logoff, encryption/decryption. RBAC + RLS + break-glass covers the whole subclause.
• (b) Audit controls. Record and examine activity. This is the audit-log requirement — no fixed retention specified here, but §164.316(b)(2) mandates six-year retention for documentation, which OCR interprets as covering the audit log.
• (c) Integrity. Protect PHI from improper alteration or destruction. Cryptographic hashes on ingestion + immutable storage.
• (d) Person or entity authentication. Verify the person/entity is who they claim. MFA, certificates, federated identity.
• (e) Transmission security. Integrity + encryption in transit. TLS 1.3 minimum in 2026.

Encryption — the addressable specifications.

• HIPAA marks encryption as "addressable" (not "required") — the covered entity must implement it unless they can document why an equivalent alternative works. In 2026 reality, "addressable" means "you will implement it or explain to OCR at length why not."
• At rest — AES-256 (or AES-GCM-256 for authenticated encryption). Envelope encryption via KMS: data encrypted with a per-key data-encryption-key (DEK), DEK encrypted with a KMS-managed key-encryption-key (KEK).
• In transit — TLS 1.3 minimum. Deprecate TLS 1.2 across the fleet by 2026; TLS 1.0/1.1 have been non-compliant for years.
• Field-level encryption for the highest-sensitivity columns — MRN, SSN, biometric identifiers — encrypted with a per-column key so a bulk export leak still requires the KMS to decrypt.

Row-level security — the enforcement point.

• Snowflake / BigQuery / Postgres all support row-level security policies: CREATE ROW ACCESS POLICY p_ward AS (patient_ward) RETURNS BOOLEAN -> (patient_ward = CURRENT_USER_WARD()).
• The policy runs on every read; there is no way for a query to bypass it (unless the role holds OWNERSHIP or an explicit exception).
• Break-glass procedure: a special role (BREAK_GLASS_CLINICIAN) can override the policy, but every use is logged, alerted, and reviewed manually.

BAA — the contract that extends HIPAA to your cloud provider.

• Every service that "creates, receives, maintains, or transmits" PHI on behalf of a covered entity must be under a BAA. The provider commits to the same Privacy + Security Rule safeguards.
• AWS BAA covers 130+ services (2026 count). Non-BAA services (AWS Deep Racer, IoT SiteWise) are prohibited for PHI.
• Azure BAA covers Enterprise-tier services via the Microsoft Products and Services Data Protection Addendum.
• GCP BAA maintains a per-service HIPAA Alignment list.
• Snowflake / Databricks / Fivetran all sign BAAs on request; standard on Enterprise+ tiers.

Retention — six years is the floor.

• §164.316(b)(2) — retain "required documentation" for six years from creation or last effective date. OCR interprets this as covering audit logs, risk assessments, and policies.
• Many states extend retention: California requires seven years post-last-encounter for medical records themselves; some require indefinite retention for pediatrics.
• Immutable, tamper-evident storage (S3 Object Lock, Azure Blob immutability, GCS retention policies) is the compliance-safe backend.

Common interview probes on audit + access.

• "What does §164.312(b) require?" — audit controls: record and examine activity on PHI systems.
• "How long must audit logs be retained?" — six years minimum (via §164.316(b)(2)); often longer for state-law overlap.
• "What is a BAA and when do you need one?" — Business Associate Agreement; needed for every vendor that touches PHI on your behalf.
• "What is row-level security and how does it help with HIPAA?" — enforces the minimum-necessary standard at the query layer, so no application-side bug can leak more than the policy allows.

Worked example — audit log schema

Detailed explanation. The audit log is the single most-queried compliance artefact you own. Its schema needs to answer three questions instantly: "who accessed this patient's data?", "what did they see?", and "when?" — plus enough context to reconstruct the access decision.

Question. Design a minimal audit log schema that satisfies §164.312(b) and supports rapid subject-access-request response. Include the fields required for forensics.

Input.

requirement	source
record every PHI access	§164.312(b)
support who/what/when/where lookups	breach forensics + SAR
tamper-evident storage	§164.312(c) integrity
six-year retention	§164.316(b)(2)

Code.

CREATE TABLE audit_phi_access (
    audit_id            uuid           PRIMARY KEY,
    event_time_utc      timestamptz    NOT NULL,
    -- Who
    actor_id            varchar(64)    NOT NULL,      -- workforce user id / service id
    actor_role          varchar(64)    NOT NULL,      -- CLINICIAN | ANALYST | ...
    actor_session_id    uuid           NOT NULL,
    -- What
    resource_type       varchar(32)    NOT NULL,      -- Patient|Observation|... or table name
    resource_id         varchar(128)   NOT NULL,      -- patient MRN or resource id
    patient_id          varchar(64)    NOT NULL,      -- always denormalise the patient
    action              varchar(32)    NOT NULL,      -- READ | WRITE | UPDATE | DELETE | EXPORT
    columns_accessed    text[]         NOT NULL,      -- explicit projection list
    row_count           bigint         NOT NULL,      -- 1 for point read, N for bulk
    -- Why
    purpose_of_use      varchar(64)    NOT NULL,      -- TREATMENT | PAYMENT | OPS | RESEARCH
    consent_ref         varchar(128),                 -- consent document id if applicable
    break_glass         boolean        NOT NULL DEFAULT FALSE,
    -- Where
    source_ip           inet           NOT NULL,
    application         varchar(64)    NOT NULL,      -- EHR | ANALYTICS | ETL | ...
    -- Integrity
    prev_hash           bytea,                         -- chain to prior row's hash
    row_hash            bytea          NOT NULL,       -- SHA-256(row canonicalisation)
    retention_until     date           NOT NULL        -- >= event_time + 6 years
);

CREATE INDEX ix_audit_patient   ON audit_phi_access (patient_id, event_time_utc DESC);
CREATE INDEX ix_audit_actor     ON audit_phi_access (actor_id, event_time_utc DESC);
CREATE INDEX ix_audit_time      ON audit_phi_access (event_time_utc DESC);

Step-by-step explanation.

1. event_time_utc in UTC (never local) so cross-region forensics is unambiguous. Nanosecond precision is unnecessary; millisecond is plenty.
2. actor_id + actor_role + actor_session_id answer "who" — the session id ties back to the auth token that granted access.
3. resource_type + resource_id + patient_id answer "what" — always denormalise patient_id even when the resource is a Patient (so subject-access-request queries are one-index-hit).
4. action + columns_accessed + row_count capture the shape of the access — a SELECT name, dob returning 1 row is very different from SELECT * returning 10K rows.
5. purpose_of_use + consent_ref + break_glass capture "why" — the legal basis for the access. Break-glass alerts must fire in real time.
6. source_ip + application capture "where" — the network origin and the calling app.
7. prev_hash + row_hash form a hash chain: each row's row_hash = SHA-256(canonical_row || prev_hash). Tampering with any row invalidates every subsequent row's hash. This is the tamper-evidence property.
8. retention_until is set on insert to event_time_utc + 6 years; a nightly job moves expired rows to cold storage or deletes.

Output (sample audit rows).

audit_id	event_time_utc	actor_id	resource_type	patient_id	action	row_count
71b2…	2026-07-15T09:14:22Z	dr.smith	Patient	p-9182	READ	1
8a91…	2026-07-15T09:14:23Z	dr.smith	Observation	p-9182	READ	12
c30f…	2026-07-15T09:15:07Z	analytics-etl	patients_deid	(many)	EXPORT	94382

Rule of thumb. Every PHI-touching query goes through a data-access-layer wrapper that inserts the audit row before returning results to the caller. Client-side "please log your queries" is not compliance — the wrapper is.

Worked example — row-level security policy for ward isolation

Detailed explanation. A hospital with multiple wards wants clinicians to see only patients in their own ward, except when they're on-call for another ward. Snowflake row access policies enforce this at the query engine, so no application bug can leak.

Question. Write a Snowflake row-level security policy that restricts patients to the caller's assigned wards, with a lookup table for on-call overrides.

Input.

table	purpose
patients	PHI table, has ward column
workforce_wards	mapping of user → wards they can see
oncall_overrides	temporary broader access

Code.

-- Policy definition
CREATE OR REPLACE ROW ACCESS POLICY ward_isolation
AS (patient_ward varchar) RETURNS BOOLEAN ->
    CASE
        -- Superadmin roles bypass (audited elsewhere)
        WHEN CURRENT_ROLE() IN ('SECURITY_ADMIN', 'BREAK_GLASS_CLINICIAN') THEN TRUE
        -- Regular clinicians: assigned wards
        WHEN patient_ward IN (
            SELECT ward FROM workforce_wards
            WHERE workforce_id = CURRENT_USER()
              AND effective_from <= CURRENT_TIMESTAMP()
              AND (effective_to IS NULL OR effective_to > CURRENT_TIMESTAMP())
        ) THEN TRUE
        -- On-call overrides
        WHEN patient_ward IN (
            SELECT ward FROM oncall_overrides
            WHERE workforce_id = CURRENT_USER()
              AND oncall_window @> CURRENT_TIMESTAMP()
        ) THEN TRUE
        ELSE FALSE
    END;

ALTER TABLE patients ADD ROW ACCESS POLICY ward_isolation ON (ward);

Step-by-step explanation.

1. The policy is a function returning boolean per row — Snowflake evaluates it against every candidate row of a SELECT.
2. The first WHEN handles the superadmin bypass. BREAK_GLASS_CLINICIAN is a special role that must be granted explicitly and expires automatically; every use fires an alert.
3. The second WHEN reads the workforce_wards mapping — a clinician sees their assigned wards, effective-dated so historical assignments do not linger.
4. The third WHEN reads oncall_overrides — a scheduled on-call assignment gives temporary access, scoped to the on-call window (@> is Snowflake's range containment).
5. All other roles/rows return FALSE — the row is not returned to the query.
6. The ALTER TABLE ... ADD ROW ACCESS POLICY attaches the policy; from that moment, every SELECT on patients runs through the policy.

Output (query behaviour by role).

Caller	Query	Rows returned
Nurse A (ward 3W)	SELECT * FROM patients	ward=3W only
Nurse A on-call for 4E	SELECT * FROM patients	ward IN (3W, 4E)
Analyst (no ward)	SELECT * FROM patients	0 rows
SECURITY_ADMIN	SELECT * FROM patients	all rows (and audited)

Rule of thumb. Use RLS policies for the default access control. Reserve break-glass to true emergencies (patient safety on a foreign ward). Every break-glass use should page the security team, not just log — real-time review is the deterrent.

Worked example — hash chain for tamper-evident audit rows

Detailed explanation. A pure append-only table is not tamper-evident on its own — a rogue DBA with DELETE or UPDATE privilege can rewrite history. A hash chain on the audit rows makes any modification detectable, even by an attacker with database write access.

Question. Write a Postgres trigger that populates row_hash as SHA-256(canonical_row || prev_hash). Show how a downstream verifier can detect a tampered row.

Input.

column	role in chain
audit_id, event_time_utc, actor_id, patient_id, action	included in canonical form
prev_hash	linked to previous row
row_hash	computed by trigger

Code.

CREATE OR REPLACE FUNCTION audit_hash_chain() RETURNS trigger AS $$
DECLARE
    canon text;
    prev  bytea;
BEGIN
    SELECT row_hash INTO prev
    FROM audit_phi_access
    ORDER BY event_time_utc DESC, audit_id DESC
    LIMIT 1;

    canon := concat_ws('|',
        NEW.audit_id::text,
        NEW.event_time_utc::text,
        NEW.actor_id,
        NEW.patient_id,
        NEW.resource_type,
        NEW.resource_id,
        NEW.action,
        NEW.row_count::text,
        NEW.source_ip::text
    );

    NEW.prev_hash := COALESCE(prev, '\x00'::bytea);
    NEW.row_hash  := digest(canon || encode(NEW.prev_hash, 'hex'), 'sha256');
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_audit_hash
    BEFORE INSERT ON audit_phi_access
    FOR EACH ROW EXECUTE FUNCTION audit_hash_chain();

-- Verifier — replay the chain and flag mismatches
WITH ordered AS (
    SELECT *, LAG(row_hash) OVER (ORDER BY event_time_utc, audit_id) AS chained_prev
    FROM audit_phi_access
)
SELECT audit_id, event_time_utc
FROM ordered
WHERE prev_hash IS DISTINCT FROM COALESCE(chained_prev, '\x00'::bytea)
   OR row_hash <> digest(
        concat_ws('|', audit_id::text, event_time_utc::text, actor_id, patient_id,
                       resource_type, resource_id, action, row_count::text, source_ip::text)
        || encode(prev_hash, 'hex'), 'sha256'
      );

Step-by-step explanation.

1. The BEFORE INSERT trigger reads the previous row's row_hash (ordered by time then id) and stores it in the new row's prev_hash.
2. Canonical form is a |-joined string of the audit-critical fields in a fixed order. The order and separator are the contract; changing them invalidates every downstream verifier.
3. row_hash = SHA-256(canonical || prev_hash_hex) — the current row's hash depends on the previous hash, so tampering with row N invalidates row N+1's hash, which invalidates N+2, etc.
4. The verifier query walks the table in order, recomputes each expected chained_prev (the previous row's hash), and flags any row whose stored prev_hash disagrees or whose row_hash recomputes to a different value.
5. A rogue DBA who deletes or updates a row will produce a chain break at that position — visible on the next verification run.

Output (verifier on a tampered chain).

audit_id	event_time_utc	detection
8a91…	2026-07-15T09:14:23Z	prev_hash mismatch (row 71b2 was altered)
c30f…	2026-07-15T09:15:07Z	prev_hash mismatch (propagated)

Rule of thumb. Run the verifier nightly and page on any mismatch. Sign the daily chain head (SHA-256 of the last day's row_hash) to a WORM storage bucket (S3 Object Lock, Azure Immutable Blob) — the signed head anchors the chain to an external immutability guarantee.

Senior interview question on end-to-end audit + access

A senior interviewer might ask: "Walk me through, end-to-end, how a HIPAA-compliant read of a patient record flows from the clinician's app to your data warehouse and back. What are the enforcement points, the audit records, and what breaks if any one layer is misconfigured?"

Solution Using RBAC + RLS + audit-first data access layer

Clinician reads a patient chart — end-to-end

1. Clinician logs into EHR app (MFA required)          → session token
2. App calls data-access-layer (DAL) with token         → DAL validates token
3. DAL enriches: actor, role, ward assignments, purpose → attaches to session
4. DAL emits BEGIN audit row                            → append-only log
5. DAL issues SELECT with impersonated role             → Snowflake RLS runs
6. Snowflake returns rows matching RLS + column masks   → sees only ward + minimum-necessary cols
7. DAL emits END audit row (row_count, columns)          → hashes into chain
8. DAL streams rows back to EHR app                     → app renders chart
9. EHR app logs page view + patient_id                  → separate app-tier audit
10. Nightly verifier replays hash chain, flags anomalies → sec team review

Step-by-step trace.

Layer	Enforcement	Audit event
EHR app	MFA + session TTL	login, page view
DAL	actor lookup, RLS role impersonation	BEGIN + END rows
Snowflake	RLS policy + masking policy	query id in queries table
KMS	key access for column decryption	key-use event
Object store	encrypted at rest, TLS in transit	S3 access log
Verifier	nightly hash-chain replay	anomaly ticket

If any layer breaks: no MFA → unauthorised login; no DAL audit → invisible access; no RLS → cross-ward leak; no encryption → cleartext-at-rest breach; no hash chain → undetected tampering. The layers must all work; there is no "80% compliant."

Output:

Component	Owns	Failure mode
EHR app + IdP	identity, MFA, session	credential stuffing → impersonation
DAL	audit-first access, purpose tagging	missing audit → SAR gap
Snowflake RLS + masking	row + column filters	policy bug → over-share
KMS	encryption keys	key leak → offline decryption
S3 Object Lock	immutability + retention	policy off → tamper risk
Verifier	hash-chain replay	ignored alert → undetected breach

Why this works — concept by concept:

• Audit-first data access — the DAL emits the audit row before the SELECT. If the SELECT fails, the log still shows the attempt. This is the difference between "audit-after" (which loses failed accesses) and "audit-first" (which does not).
• RLS at the engine — enforcement inside the query engine means no application bug or SQL injection can bypass the policy. Defence in depth over app-layer filters.
• Break-glass with pager — the only way to override RLS is a role that pages the security team on use. The deterrent is real-time review, not just retrospective log analysis.
• Hash-chain tamper evidence — signed daily heads anchor the chain to WORM storage; a rogue DBA cannot rewrite history without producing a detectable chain break.
• BAA chain enforcement — the CI-enforced BAA manifest prevents new services from being wired in without a signed BAA, closing the "someone added Segment last quarter" leak.
• Cost — the audit + RLS + encryption layers add ~5-10% query latency, offset by O(1) per query. The compliance ROI (avoiding one 500-record breach's ~$2M cost) pays for the entire audit infrastructure many times over.

SQL
Topic — sql
SQL drills for audit log queries + row-level security patterns

Practice →

SQL Topic — joins Join problems for actor + resource + consent traceability

Practice →

---

5. Cohort building + research pipelines

OMOP CDM turns raw EHR chaos into a research-grade longitudinal patient timeline — one schema, N cohorts, joinable across health systems

The mental model in one line: the OMOP CDM (Observational Medical Outcomes Partnership Common Data Model) is a standardised schema — Person, Visit_Occurrence, Condition_Occurrence, Drug_Exposure, Measurement, Observation, Procedure_Occurrence, Death — plus a Concept vocabulary crosswalk (ICD-10 → SNOMED → OMOP concept_id) that turns raw EHR extracts from any source into a research-grade longitudinal patient timeline queryable with a portable cohort SQL. Once you internalise "map to OMOP concepts, build cohort via SQL, release with differential privacy," the entire healthcare analytics interview surface collapses to a repeatable ELT pipeline.

OMOP CDM — the tables you must know.

• Person — one row per patient. person_id (surrogate), gender_concept_id, year_of_birth, race_concept_id, ethnicity_concept_id. Note the year — Safe Harbor compliant by design.
• Visit_Occurrence — one row per encounter. visit_occurrence_id, person_id, visit_concept_id (Inpatient / Outpatient / ER), visit_start_date, visit_end_date.
• Condition_Occurrence — one row per diagnosis code. condition_concept_id (standard SNOMED), condition_source_value (raw ICD-10), condition_start_date, visit_occurrence_id.
• Drug_Exposure — one row per medication administration or prescription. drug_concept_id (RxNorm ingredient), drug_exposure_start_date, days_supply.
• Measurement — one row per lab / vital. measurement_concept_id (LOINC), value_as_number, unit_concept_id, measurement_date.
• Observation, Procedure_Occurrence, Device_Exposure, Note, Death — the rest of the clinical footprint.

The Concept vocabulary — where the magic lives.

• Concept is the master table of every clinical concept. Each row: concept_id (integer), concept_code, vocabulary_id (ICD-10, SNOMED, LOINC, RxNorm, ...), standard_concept (S = standard OMOP, N = non-standard).
• Concept_Relationship maps between vocabularies. Maps to relationships link ICD-10 codes to their SNOMED equivalents.
• Concept_Ancestor flattens the SNOMED hierarchy. Querying "all Type 2 Diabetes" traverses ancestor concept_id 201826 down to every child.
• Vocabulary version matters — OHDSI publishes new vocabulary bundles quarterly; teams pin a version per study to reproduce results.

Cohort building — the i2b2 tradition.

• A cohort is a set of person_ids satisfying inclusion + exclusion criteria within a time window. Classic i2b2 (Informatics for Integrating Biology and the Bedside) puts these in a UI; OMOP puts them in SQL.
• Inclusion: patient has diagnosis X (via Concept_Ancestor) and age Y at index date and enrolled in observation ≥ N days.
• Exclusion: patient has diagnosis Z (competing) or prior exposure to drug W.
• OHDSI's ATLAS tool generates OMOP cohort SQL from a UI, but every serious study includes hand-tuned SQL.

Longitudinal patient records — the payoff.

• After OMOP mapping, each patient is a time-ordered stream of events — encounters, diagnoses, medications, labs — keyed on person_id and date.
• Enables survival analysis, medication adherence, care-gap detection, real-world evidence for regulatory submissions.
• The same query runs on any OMOP-compliant instance — a study designed at UCSF can be re-run at Duke against Duke's OMOP, yielding comparable results without sharing PHI.

Real-World Evidence (RWE) pipelines.

• RWE = evidence about clinical benefit or risk derived from data outside classical randomised trials. FDA has an explicit RWE framework (2018 → 2026 updates).
• Pipeline shape: OMOP ELT → cohort SQL → propensity-score matching → survival analysis or comparative effectiveness → statistical report.
• Regulatory submissions attach the OMOP concept-set definitions and SQL so the analysis is reproducible.

Differential privacy for analytics releases.

• When aggregated cohort counts leave the PHI enclave (dashboards, publications, public datasets), differential privacy adds calibrated noise so any single individual's contribution is bounded.
• Laplace mechanism — add Laplace(0, sensitivity/ε) noise to numeric outputs. ε (privacy budget) balances utility vs privacy; ε = 1.0 is a common release setting.
• Small counts are suppressed — cells with count < 11 replaced by "≤10" or aggregated up. This is standard on CMS Public Use Files.

Common interview probes on cohorts.

• "What is OMOP CDM and why does it matter?" — standardised schema + concept vocabulary for cross-institution research.
• "How do you map ICD-10 to SNOMED for OMOP?" — Concept_Relationship with Maps to; use OHDSI's vocabulary bundles.
• "Walk me through a cohort SQL for T2DM patients over 40 with 180-day follow-up." — Person + Condition_Occurrence + Observation_Period joins with Concept_Ancestor for the diagnosis rollup.
• "What is differential privacy and when does it apply?" — noise-mechanism-based bounded-disclosure guarantee for aggregate releases.

Worked example — mapping raw ICD-10 to OMOP concept_id

Detailed explanation. Raw EHR data carries diagnosis codes as ICD-10 strings. OMOP requires them mapped to standard SNOMED-based concept_ids. The mapping is the first ELT step of any OMOP pipeline.

Question. Write the SQL that inserts condition_occurrence rows from a raw diagnoses_src table, translating ICD-10 to OMOP concept_id via the Concept_Relationship table.

Input.

diagnoses_src.mrn	diagnoses_src.icd10	diagnoses_src.dx_date
MRN-482910	E11.9	2026-07-14
MRN-482910	I10	2026-07-14
MRN-300211	E11.65	2026-06-30

Code.

INSERT INTO condition_occurrence (
    condition_occurrence_id, person_id, condition_concept_id,
    condition_source_value, condition_source_concept_id,
    condition_start_date, visit_occurrence_id, condition_type_concept_id
)
SELECT
    nextval('seq_condition_occurrence')                 AS condition_occurrence_id,
    p.person_id                                         AS person_id,
    COALESCE(std.concept_id, 0)                         AS condition_concept_id,
    src.icd10                                           AS condition_source_value,
    src_c.concept_id                                    AS condition_source_concept_id,
    src.dx_date                                         AS condition_start_date,
    v.visit_occurrence_id                               AS visit_occurrence_id,
    32817                                               AS condition_type_concept_id   -- EHR encounter diagnosis
FROM diagnoses_src src
JOIN person p
     ON p.person_source_value = src.mrn
JOIN concept src_c
     ON src_c.vocabulary_id = 'ICD10CM'
    AND src_c.concept_code = src.icd10
LEFT JOIN concept_relationship cr
     ON cr.concept_id_1 = src_c.concept_id
    AND cr.relationship_id = 'Maps to'
LEFT JOIN concept std
     ON std.concept_id = cr.concept_id_2
    AND std.standard_concept = 'S'
LEFT JOIN visit_occurrence v
     ON v.person_id = p.person_id
    AND src.dx_date BETWEEN v.visit_start_date AND COALESCE(v.visit_end_date, src.dx_date)
;

Step-by-step explanation.

1. person maps person_source_value = mrn → person_id. The person table is the pseudonymised identity spine; the raw MRN is never carried into OMOP tables directly.
2. concept (aliased src_c) resolves the raw ICD-10 string to a Concept row in the ICD10CM vocabulary; this becomes condition_source_concept_id.
3. concept_relationship with relationship_id = 'Maps to' walks to the standard concept — the SNOMED-CT concept that OMOP considers authoritative.
4. concept (aliased std) filters to standard_concept = 'S' — a hygiene check that the target really is a standard OMOP concept.
5. visit_occurrence links the diagnosis to the encounter it was recorded in — a range join on start/end date.
6. condition_type_concept_id = 32817 is the OMOP concept for "EHR encounter diagnosis." Different type ids exist for claims, problem list, patient-reported, etc.
7. COALESCE(std.concept_id, 0) — OMOP uses concept_id = 0 for "no valid mapping," which is a first-class value in every concept-id column.

Output.

condition_occurrence_id	person_id	condition_concept_id	condition_source_value	condition_start_date
1	9182	201826	E11.9	2026-07-14
2	9182	320128	I10	2026-07-14
3	2003	443729	E11.65	2026-06-30

Rule of thumb. Store both condition_source_value (raw ICD-10) and condition_concept_id (standard SNOMED). The raw value is your audit trail; the standard id is your query surface. Never throw away the raw value — vocabulary bundles change, and re-mapping needs the source.

Worked example — cohort SQL for T2DM patients on Metformin

Detailed explanation. A study asks: "Among Type 2 Diabetes patients aged 40+, who is on Metformin at index date and has ≥ 180 days of observation before and after?" This is a classic OMOP cohort SQL, exercising Concept_Ancestor for the disease rollup, Drug_Exposure for the medication, and Observation_Period for the follow-up window.

Question. Write the OMOP cohort SQL. Index date = first condition_occurrence for T2DM.

Input.

criterion	value
Disease	Type 2 Diabetes (SNOMED 201826, all descendants)
Age at index	≥ 40
Medication	Metformin (RxNorm ingredient concept 6809, all descendants)
Observation	≥ 180 days before index, ≥ 180 days after index

Code.

WITH t2dm_first AS (
    SELECT co.person_id,
           MIN(co.condition_start_date) AS index_date
    FROM condition_occurrence co
    WHERE co.condition_concept_id IN (
        SELECT descendant_concept_id
        FROM concept_ancestor
        WHERE ancestor_concept_id = 201826           -- Type 2 diabetes mellitus
    )
    GROUP BY co.person_id
),
with_age AS (
    SELECT t.person_id, t.index_date,
           (EXTRACT(YEAR FROM t.index_date) - p.year_of_birth) AS age_at_index
    FROM t2dm_first t
    JOIN person p USING (person_id)
),
with_metformin AS (
    SELECT wa.person_id, wa.index_date, wa.age_at_index
    FROM with_age wa
    WHERE EXISTS (
        SELECT 1
        FROM drug_exposure de
        WHERE de.person_id = wa.person_id
          AND de.drug_concept_id IN (
              SELECT descendant_concept_id
              FROM concept_ancestor
              WHERE ancestor_concept_id = 6809           -- Metformin ingredient
          )
          AND de.drug_exposure_start_date
              BETWEEN wa.index_date - INTERVAL '30 days'
                  AND wa.index_date + INTERVAL '30 days'
    )
),
with_followup AS (
    SELECT wm.person_id, wm.index_date, wm.age_at_index
    FROM with_metformin wm
    WHERE EXISTS (
        SELECT 1
        FROM observation_period op
        WHERE op.person_id = wm.person_id
          AND wm.index_date - INTERVAL '180 days' >= op.observation_period_start_date
          AND wm.index_date + INTERVAL '180 days' <= op.observation_period_end_date
    )
)
SELECT person_id, index_date, age_at_index
FROM with_followup
WHERE age_at_index >= 40
ORDER BY person_id;

Step-by-step explanation.

1. t2dm_first finds each patient's earliest T2DM diagnosis via Concept_Ancestor — the 201826 rollup captures every SNOMED descendant, so "T2DM with ophthalmic complications" (a child concept) counts.
2. with_age computes age at index using OMOP's year_of_birth (no month/day; Safe Harbor compliant).
3. with_metformin filters to patients on Metformin around index date (±30 days). EXISTS is preferred over IN for cardinality reasons — the drug_exposure table is huge.
4. with_followup requires the observation window fully covers ±180 days from index. observation_period tracks continuous enrolment; patients who left and returned get multiple rows.
5. Final SELECT applies age filter and returns the cohort. The output feeds propensity-score matching, outcome analysis, etc.

Output (cohort).

person_id	index_date	age_at_index
9182	2026-07-14	48
2003	2026-06-30	55
4471	2026-05-11	62

Rule of thumb. Always express disease inclusion via Concept_Ancestor, never via a hand-listed set of condition_concept_ids — the SNOMED hierarchy changes with every OHDSI vocabulary release, and hard-coded lists silently drift out of date.

Worked example — differential privacy release of cohort counts

Detailed explanation. A public dashboard wants to show weekly counts of diabetes patients by county. Raw counts leak individuals when the count is small (a county with 3 T2DM patients has three real people). Differential privacy adds calibrated Laplace noise + small-count suppression so any single patient's presence is deniable.

Question. Implement a differentially private release of weekly per-county T2DM counts with ε = 1.0 and small-count suppression at 11.

Input.

county	week	true_count
SF	2026-W28	4192
SF	2026-W29	4218
Marin	2026-W28	152
Marin	2026-W29	149
Alpine	2026-W28	3

Code.

import random, math

def laplace_noise(scale: float) -> float:
    # Inverse-CDF sampling: Laplace(0, scale)
    u = random.random() - 0.5
    return -scale * math.copysign(1.0, u) * math.log(1 - 2 * abs(u))

def release_dp(counts: list[dict], epsilon: float = 1.0,
               sensitivity: int = 1, suppression: int = 11) -> list[dict]:
    # Sensitivity = 1 because a single patient adds/removes 1 from any single cell.
    scale = sensitivity / epsilon
    out = []
    for row in counts:
        noisy = row["true_count"] + laplace_noise(scale)
        noisy = max(0, round(noisy))
        if noisy < suppression:
            out.append({**row, "release_count": None, "flag": f"suppressed(<{suppression})"})
        else:
            out.append({**row, "release_count": noisy, "flag": "released"})
    return out

Step-by-step explanation.

1. sensitivity = 1 because adding or removing one patient changes each per-county-week count by at most 1 (each patient counts once).
2. scale = sensitivity / epsilon = 1 / 1.0 = 1.0 → the Laplace noise has scale 1. Roughly 90% of noise samples fall within ±3.
3. laplace_noise is inverse-CDF sampling — draw a uniform, invert. Any real DP library (diffprivlib, tumult analytics) provides better sampling and privacy budget bookkeeping.
4. max(0, round(noisy)) clamps the noisy value to a non-negative integer — negative counts are absurd and would themselves leak information.
5. Small-count suppression: any cell < 11 is replaced with a None and flagged. This is standard on CMS Public Use Files and prevents "cell of 3" reveal.
6. The privacy budget ε is shared across the release — if the same underlying data is released 10 times with ε = 1, the effective budget is 10, so track it globally.

Output (released).

county	week	release_count	flag
SF	2026-W28	4193	released
SF	2026-W29	4217	released
Marin	2026-W28	151	released
Marin	2026-W29	150	released
Alpine	2026-W28	NULL	suppressed(<11)

Rule of thumb. DP is not a substitute for de-identification — use both. De-identification (Safe Harbor / Expert Determination) protects the underlying rows; DP protects the aggregate release. Publishing raw aggregates on de-identified data is a common mistake that leaks small-cell counts.

Senior interview question on the OMOP research pipeline

A senior interviewer might ask: "You are the DE lead for a new cardiovascular RWE program. Walk me through the pipeline from raw EHR extract to a publishable outcome analysis. Where does OMOP fit, what does the cohort SQL look like, and how do you defend the release?"

Solution Using OMOP ELT → cohort SQL → matched analysis → DP release

End-to-end RWE pipeline

┌─ Raw EHR ──────┐
│ patients_src   │
│ encounters_src │      ┌── OMOP ELT ──┐
│ diagnoses_src  │ ───► │ person       │
│ meds_src       │      │ visit_occ    │
│ labs_src       │      │ cond_occ     │
└────────────────┘      │ drug_exp     │
                        │ measurement  │
                        │ observation  │
                        │ obs_period   │
                        └──────┬───────┘
                               │
                               ▼
                     ┌── Cohort SQL ──┐
                     │ CTE inclusion  │
                     │ CTE exclusion  │
                     │ CTE index date │
                     └──────┬─────────┘
                            │
                            ▼
                 ┌── Analysis pipeline ──┐
                 │ Propensity match      │
                 │ Kaplan-Meier survival │
                 │ Cox proportional H    │
                 └──────┬────────────────┘
                        │
                        ▼
                ┌── DP release layer ──┐
                │ ε = 1, suppress < 11 │
                │ hash-chain audit     │
                └──────┬───────────────┘
                       │
                       ▼
              ┌── Publication + reproducibility ──┐
              │ concept-set + SQL attached        │
              │ OHDSI vocabulary version pinned   │
              └───────────────────────────────────┘

Step-by-step trace.

Stage	Input	Output	Enforcement
1 — Extract	source EHR	raw staging	BAA + encryption
2 — OMOP ELT	raw staging	OMOP tables	vocabulary map
3 — Cohort	OMOP tables	cohort person_ids	audited SQL
4 — Analysis	cohort + outcomes	HR + CI	pre-registered SAP
5 — DP release	aggregates	published table	ε budget log
6 — Publication	release + code	preprint	code + SQL attached

Every stage has a compliance gate: the extract must come from a BAA-covered source, the OMOP schema is pseudonymised (Safe Harbor year-only birth), the cohort SQL is code-reviewed, the analysis is pre-registered to prevent p-hacking, the release runs through DP + small-count suppression, and the publication includes reproducibility artefacts.

Output:

Layer	Legal artefact
Extract	BAA + data-use agreement
OMOP	de-identified schema (§164.514)
Cohort	code-reviewed SQL + concept sets
Analysis	pre-registered Statistical Analysis Plan
Release	ε-budget-tracked DP output
Publication	attached SQL + vocabulary version

Why this works — concept by concept:

• OMOP as the pivot — mapping to OMOP once means every downstream cohort/analysis is portable across institutions and reproducible across time. The pivot cost is high; the multiplier is huge.
• Concept_Ancestor for disease rollups — hand-listing every ICD-10 code for a disease is a bug factory; using the SNOMED ancestor concept id captures the whole hierarchy in one join.
• Observation_Period for enrolment discipline — RWE that ignores enrolment gaps overstates cohort size and understates event rates. The observation_period table is the enrolment-discipline gate.
• Pre-registered SAP + DP release — the pre-registration stops p-hacking; DP + suppression stops small-cell disclosure. Both are professional practice, not just compliance theater.
• Reproducibility artefacts — attaching the cohort SQL + concept set + vocabulary version makes the analysis re-runnable by any peer institution against their own OMOP. This is the standard of the OHDSI community and increasingly of FDA-facing submissions.
• Cost — an OMOP mapping is a 3-9 month one-time build for a mid-size institution; every subsequent study amortises across it. The RWE pipeline stack (OHDSI ATLAS, HADES R packages, Broadsea Docker) is open source, so licensing is not a barrier.

SQL
Topic — joins
Join problems for OMOP cohort + concept ancestor rollups

Practice →

SQL
Topic — aggregation
Aggregation drills for cohort counts + differential privacy releases

Practice →

---

Cheat sheet — healthcare DE recipes

• HL7 v2 parser stub. Split on \r, MSH first, delimiters from MSH.2 (^~\&). PID.3 repeats via ~ — pick the MR-typed identifier. Dates are YYYYMMDD (or YYYYMMDDHHMMSS), no timezone. Never write the industrial parser yourself — use hl7apy (Python), HAPI (Java), or nhapi (.NET).
• MLLP framing. Start = 0x0B (VT), end = 0x1C 0x0D (FS + CR). ACK is another HL7 message wrapped in the same framing; MSA|AA|<control_id> is Application Accept. Send ACK before heavy processing so senders do not retry.
• FHIR Bundle flattener. Emit one bronze row per entry.resource — resource_type, resource_id, patient_id (from subject.reference), event_time (resource-specific field), payload_json (compact). Never store the raw Bundle as one atomic row.
• SMART-on-FHIR OAuth scope. Use system/<Resource>.read for backend polling and patient/<Resource>.read for user-scoped apps. Scope the token to exactly the resource types the pipeline needs — anything else is a minimum-necessary violation.
• Safe Harbor 18 strip. Set all 18 identifiers to NULL; keep year only for dates; cap age at 90; 3-digit zip with fallback for the 17 restricted prefixes; year-only for encounter dates.
• FPE deterministic token. NIST FF3-1 with a KMS-held key + per-domain tweak. Same input → same token; reversal requires KMS access. Use FPE when downstream joins matter; use HMAC-SHA-256 when they don't.
• Per-patient date shift. Δ = HMAC(shift_key, patient_id) mod (2N+1) - N, applied to every date column for the patient. Preserves intervals; breaks absolute-date pinning. Combine with year-only truncation for maximum safety.
• k-anonymity gate. Group by quasi-identifiers (age bucket, zip3, sex, race); drop rows in groups smaller than k (typically 5). Layer l-diversity and t-closeness on the sensitive attribute.
• Audit log schema. event_time_utc | actor_id | actor_role | patient_id | resource_type | resource_id | action | columns_accessed | row_count | purpose_of_use | break_glass | source_ip | prev_hash | row_hash. Index on (patient_id, event_time_utc DESC) for SAR queries.
• Row-level security policy. Snowflake row access policy on the ward/organisation column with a lookup to a workforce-assignment table and an on-call override table. Break-glass role bypasses but pages the security team on use.
• Hash chain for tamper evidence. row_hash = SHA-256(canonical_row || prev_hash) on insert; nightly verifier replays and flags mismatches. Sign daily chain heads to WORM storage (S3 Object Lock, Azure Immutable Blob).
• BAA cloud checklist. Every service in the PHI data path must be on your provider's BAA-eligible list. Version-control a baa-manifest.yaml and gate deployments on a CI check that fails on any new non-BAA service.
• OMOP ELT skeleton. Raw → staging → person (year_of_birth) → visit_occurrence → condition_occurrence (Concept_Relationship 'Maps to' from ICD10CM) → drug_exposure (RxNorm) → measurement (LOINC). Keep both source_value and concept_id — the source is the audit trail; the concept is the query surface.
• OMOP cohort SQL. Disease inclusion via Concept_Ancestor; medication via drug_exposure ± window; enrolment discipline via observation_period. Never hand-list ICD codes — use the SNOMED ancestor concept id.
• Differential privacy release. Laplace noise with scale = sensitivity/ε (ε = 1.0 default); small-count suppression at 11; per-release ε budget tracked and logged.
• Encryption defaults. AES-256 (or AES-GCM-256) at rest via envelope encryption; TLS 1.3 in transit; per-column keys for MRN/SSN/biometrics; KMS rotation cadence documented in the SSP.
• Retention policy. Audit log ≥ 6 years (HIPAA §164.316(b)(2)); medical records per state law (often 7+ years post-last-encounter; longer for pediatrics). Immutable storage for the retention window.

Frequently asked questions

What is the difference between PHI and PII in healthcare data engineering?

PHI (Protected Health Information) is any individually identifiable health information created, received, or maintained by a HIPAA-covered entity — the operational definition is the HIPAA Safe Harbor 18 identifiers combined with a health-context link. PII (Personally Identifiable Information) is the broader NIST concept — any data that can identify a person, health-related or not. Every field of PHI is also PII, but not every PII field is PHI (e.g., your employer's HR system holds PII but not PHI). In healthcare data engineering, HIPAA is the stricter regime so PHI controls (encryption, audit, BAA, minimum-necessary) win by default. The single most common interview mistake is treating "de-identified per Safe Harbor" as "no longer sensitive" — de-identified data is not PHI under HIPAA but is often still regulated under state privacy law (CCPA / CMIA) and always still requires responsible data handling.

Do I need a Business Associate Agreement (BAA) to store PHI on AWS, Azure, or GCP?

Yes — every cloud service that "creates, receives, maintains, or transmits" PHI on your behalf must be covered by a BAA before you touch it with PHI. AWS covers 130+ services under its standard BAA (S3, EC2, RDS, Redshift, Snowflake-on-AWS, etc.); Azure covers Enterprise-tier services via the Microsoft Products and Services Data Protection Addendum; GCP maintains a per-service HIPAA Alignment list. Non-BAA services are prohibited — you cannot pipe PHI through AWS DeepRacer or a free-tier IoT service just because it's convenient. The senior-DE pattern is to maintain a version-controlled baa-manifest.yaml of every service your pipeline touches, with a CI check that fails any deployment adding an unlisted service. Snowflake, Databricks, and Fivetran all sign BAAs at their Enterprise+ tiers, so a modern warehouse stack is easily BAA-covered end-to-end.

HL7 v2 vs FHIR — which should I ingest in 2026?

Both — 2026 hospitals still run 95% of their intra-hospital clinical traffic over HL7 v2 (MLLP + pipe-delimited segments) while every 21st Century Cures Act–certified EHR must also expose a FHIR R4 REST API. A modern hl7 pipeline typically ingests HL7 v2 for real-time ADT / ORM / ORU messaging from legacy systems and FHIR R4 for population-level $export bulk data, patient-facing app APIs, and cross-hospital TEFCA exchange. Both parsers land into the same bronze schema (protocol-agnostic event_source column), so downstream silver/gold layers do not care about the source protocol. Trying to force everything through only one protocol in 2026 fails against the installed base on the HL7 v2 side or against the ONC certification requirement on the FHIR side.

What are the 18 HIPAA identifiers under Safe Harbor?

The HIPAA Privacy Rule §164.514(b)(2) lists them: (1) names, (2) geographic subdivisions smaller than a state — including street address, city, county, precinct, zip code (3-digit zips are conditionally allowed), (3) all elements of dates directly related to an individual (except year), and ages over 89, (4) telephone numbers, (5) fax numbers, (6) email addresses, (7) social security numbers, (8) medical record numbers, (9) health plan beneficiary numbers, (10) account numbers, (11) certificate/license numbers, (12) vehicle identifiers and serial numbers including license plates, (13) device identifiers and serial numbers, (14) URLs, (15) IP addresses, (16) biometric identifiers including finger and voice prints, (17) full-face photographic images and any comparable images, and (18) any other unique identifying number, characteristic, or code. Removing all 18 is the Safe Harbor path; keeping any of them requires the Expert Determination path with a qualified statistician's attestation.

What is the difference between Safe Harbor and Expert Determination for de-identification?

Safe Harbor (§164.514(b)(2)) is the checklist path — remove all 18 identifiers listed above and confirm the covered entity has no actual knowledge that the remaining data could re-identify. It is simple, easy to audit, and produces the most restrictive dataset (year-only dates, coarse zip, age capped at 90). Expert Determination (§164.514(b)(1)) is the statistical path — a qualified statistician applies generally accepted statistical or scientific principles and documents in writing that the risk of re-identification is very small. There is no numeric threshold in the regulation, but industry practice uses r ≤ 0.09 (Sweeney's threshold) as a common upper bound. Expert Determination allows retention of more identifiers (partial dates, richer geography) when the risk analysis supports it — much better research utility, at the cost of paying an expert to attest and re-attest. Most production phi masking pipelines combine Safe Harbor strip + FPE tokens + date shift + k-anonymity checks, then use Expert Determination as the legal shield on the final release.

How long must healthcare audit logs be retained under HIPAA?

Six years minimum. HIPAA §164.316(b)(2) requires that "documentation" — which the Office for Civil Rights consistently interprets to include the §164.312(b) audit logs — be retained for six years from creation or the last date it was in effect. Many state laws overlay stricter retention: California CMIA requires seven years post-last-encounter for medical records themselves; some pediatric records require retention until age of majority + a state-specific window; and CMS Conditions of Participation add their own retention rules for Medicare-participating hospitals. The senior-DE default is: audit log retention = max(6 years, applicable state law), stored on immutable / WORM storage (S3 Object Lock, Azure Immutable Blob, GCS retention policies) with a hash-chained integrity guarantee. Deleting audit logs before the retention window ends is a §164.316 violation on its own, independent of any underlying breach.

Practice on PipeCode

• Drill the SQL practice library → for the projection-rewrite, RLS-policy, and audit-log query patterns that show up every healthcare interview loop.
• Rehearse on joins problems → when the interviewer probes ICD-10 / SNOMED crosswalks, OMOP star joins, and consent + actor + resource traceability queries.
• Sharpen the aggregation library → for k-anonymity checks, per-patient windowed rollups, and differentially private release counts.
• For the broader surface, read top data engineering interview questions →.
• Stack the prerequisites with the only 5 skills you need to become a data engineer →.
• Level up the SQL axis with the SQL for data engineering interviews course →.
• For ETL system design, work through the ETL system design course →.
• For schema design, layer the data modeling for data engineering interviews course →.

Pipecode.ai is Leetcode for Data Engineering — every HIPAA, HL7/FHIR, and PHI masking recipe above ships with hands-on practice rooms where you write the minimum-necessary projection, the OMOP cohort SQL, the row-level security policy, and the differentially private release against real graded inputs. PipeCode pairs every reading with 450+ DE-focused problems and a real-time scoring engine, so you never have to wonder whether your `healthcare data engineering` answer holds up under a senior interviewer's depth probes.

Practice SQL now →
Join drills →