signalpilot-labs Healthcare Data Science
Driving Table
When aggregating clinical metrics, drive FROM the fact/event table (encounters, procedures, diagnoses), not the dimension table (patients, providers). Patients with zero encounters MUST NOT appear in utilization reports - they have no data to aggregate.
Encounter-Based Grain
One patient encounter generates multiple diagnoses, procedures, medications, and cost entries. The grain of clinical fact tables is the EVENT (diagnosis, procedure), not the encounter or patient.
Do NOT aggregate to patient level without first verifying whether the task asks for patient-level or encounter-level output. Premature patient-level aggregation destroys per-encounter detail.
Clinical Code Hierarchies
Healthcare coding systems are hierarchical - a parent code rolls up child codes:
- ICD (International Classification of Diseases) - diagnoses and conditions
- CPT (Current Procedural Terminology) - physician procedures
- HCPCS (Healthcare Common Procedure Coding System) - equipment, supplies, non-physician services
- SNOMED CT - clinical terms (conditions, procedures, substances)
- DRG (Diagnosis Related Groups) - inpatient payment grouping (MS-DRG for Medicare, APR-DRG for all-payer)
- NUCC (National Uniform Claim Committee) - provider taxonomy/specialty codes
- LOINC - lab tests and clinical observations
- NDC (National Drug Codes) - drug/medication identifiers
- RxNorm - normalized drug names
- NPI (National Provider Identifier) - provider identification
- APC (Ambulatory Payment Classification) - outpatient payment grouping
- Revenue Codes - facility billing line items
- Place of Service Codes - where care was delivered
When the task asks for a "condition" or "procedure category," check whether the project maps to a specific hierarchy level. Use the mapping table's grain, not a substring of the code.
When multiple codes map to one encounter, the encounter appears multiple times in the fact table - this is correct grain, not a duplicate.
Taxonomy and Crosswalk Tables
When building a mapping between a complete taxonomy (all codes) and a partial crosswalk (only some codes have mappings), drive FROM the complete taxonomy table. LEFT JOIN the crosswalk onto it. Codes without a crosswalk mapping get NULL - that is correct, they are unmapped codes.
Driving from the crosswalk drops all codes that have no mapping, producing an incomplete taxonomy.
Cost Allocation
Healthcare costs are allocated across diagnoses and procedures within an encounter. SUM(cost) across all rows for a patient double-counts shared encounter costs.
When the task asks for "total cost," verify whether costs are pre-allocated (sum is correct) or shared (need to deduplicate by encounter first).
NULL Semantics
In clinical data, NULL means "not recorded," which is clinically different from "not present." A NULL diagnosis does NOT mean the patient is healthy - it means the data is incomplete.
NEVER filter NULL clinical columns unless the task explicitly excludes incomplete records. Filtering NULLs in clinical data silently drops patients with missing documentation.