Study guide
5.3 Clinical & Financial Data Extraction
AHIMA Certified Health Data Analyst CHDA
Track your progress as you read
Finished sections get a check mark.
1 Introduction & CHDA Exam Overview
0.1 About the AHIMA CHDA Exam 0.2 How to Use This Study Guide
2 Chapter 1: Healthcare Delivery Systems & Revenue Cycle
1.1 Healthcare Delivery Settings & Organizational Structures 1.2 Healthcare Revenue Cycle Management & Reimbursement Models 1.3 Regulatory Reporting & Accreditation Agencies
3 Chapter 2: Health Informatics, Datasets & AI
2.1 Electronic Health Records (EHR) & Health Informatics Foundations 2.2 Healthcare Datasets, Benchmarks & Population Health 2.3 Analytics Hierarchy & Artificial Intelligence in Healthcare
4 Chapter 3: Clinical Terminologies, Classifications & Standards
3.1 Clinical Classification Systems: ICD-10-CM/PCS & CPT/HCPCS 3.2 Clinical Terminologies & Ontologies: SNOMED CT, LOINC & RxNorm 3.3 Interoperability & Data Exchange Standards: HL7 v2, CDA & FHIR
5 Chapter 4: Business Needs Assessment & Scoping
4.1 Stakeholder Engagement & Analytics Needs Elicitation 4.2 Defining Metrics, KPIs & Healthcare Value 4.3 Analytics Project Management & Analysis Planning
6 Chapter 5: Data Sourcing, Lineage & Collection Methods
5.1 Healthcare Data Sourcing & Lineage 5.2 Healthcare Data Collection Methods & Architectures 5.3 Clinical & Financial Data Extraction
7 Chapter 6: Data Cleansing, Transformation & Validation
6.1 Healthcare Data Quality Assessment 6.2 Data Cleansing & Anomaly Remediation 6.3 Data Transformation, Mapping & Validation
8 Chapter 7: Exploratory Data Analysis & Descriptive Statistics
7.1 Healthcare Data Types & Scales of Measurement 7.2 Measures of Central Tendency & Dispersion 7.3 Exploratory Data Analysis in Clinical Populations
9 Chapter 8: Inferential Statistics & Hypothesis Testing
8.1 Sampling Techniques & Probability in Healthcare 8.2 Hypothesis Testing Fundamentals 8.3 Parametric vs. Non-Parametric Tests
10 Chapter 9: Advanced Statistical Modeling & Risk Adjustment
9.1 Correlation & Regression Analysis 9.2 Risk Adjustment & Healthcare Severity Modeling 9.3 Survival Analysis & Model Evaluation
11 Chapter 10: Healthcare Epidemiology & Study Methodologies
10.1 Epidemiological Measures of Disease Frequency & Association 10.2 Healthcare Study Designs & Methodologies 10.3 Confounding, Bias & Health Equity Analytics
12 Chapter 11: Healthcare Data Visualization & Dashboard Design
11.1 Principles of Effective Healthcare Data Visualization 11.2 Selecting Appropriate Healthcare Visualizations 11.3 Executive & Clinical Dashboard Architecture
13 Chapter 12: Healthcare Performance Measurement & CMS Quality Programs
12.1 CMS Quality Programs & Value-Based Purchasing 12.2 Clinical Quality Measures & Benchmarking 12.3 Financial Analytics & Case Mix Index Analysis
14 Chapter 13: Communicating Insights & Narrative Analytics
13.1 Translating Complex Analytics for Diverse Audiences 13.2 Contextualizing Limitations, Assumptions & Uncertainty 13.3 Narrative Reporting & Data-Driven Decision Support
15 Chapter 14: Healthcare Data Governance Frameworks & Stewardship
14.1 Enterprise Data Governance Frameworks 14.2 Data Stewardship & Master Patient Index (MPI) Management 14.3 Metadata Management & Business Glossaries
16 Chapter 15: Healthcare Privacy, Security & Data Ethics
15.1 HIPAA Privacy & Security Rules for Analysts 15.2 De-Identification Methodologies 15.3 Data Ethics, Audit Trails & Secondary Data Use
5.3 Clinical & Financial Data Extraction
Key Takeaways Explain This Topic AI
Structured Query Language (SQL) logical execution order proceeds: FROM/JOIN -> WHERE -> GROUP BY -> HAVING -> SELECT -> DISTINCT -> ORDER BY -> LIMIT/OFFSET; mastering this execution sequence is vital for accurate healthcare cohort filtering and aggregation.
Relational joins serve specific clinical purposes: INNER JOIN isolates matching records, LEFT OUTER JOIN identifies cohorts with or without post-discharge events (e.g., readmissions returning NULL for non-readmitted patients), and FULL OUTER JOIN reconciles billing claims against payer remittances.
Common Table Expressions (CTEs) and Window Functions ( ROW_NUMBER() , RANK() , DENSE_RANK() , LAG() , LEAD() ) provide the advanced foundation for extracting longitudinal clinical trajectories, identifying most recent encounters, and calculating readmission intervals.
Healthcare-specific SQL logic requires precise handling of Length of Stay (LOS) rules (same-day stays = 1 day), CMS 30-day readmission methodology (excluding in-hospital deaths and AMA discharges), exact patient age date arithmetic, and defensive NULL handling ( COALESCE() , NULLIF() ).
Extracting claims data requires parsing ANSI ASC X12N 837 institutional (837I) and professional (837P) claim loop structures (Loop 2010AA Billing Provider, Loop 2300 Claim Header, Loop 2400 Service Line), interpreting Revenue Codes (e.g., 0450 ER, 0250 Pharmacy, 0200 ICU), and extracting CPT procedure modifiers (-25, -59, -X{EPSU}).
Last updated: August 2026
Clinical & Financial Data Extraction
Data extraction represents the bridge between raw healthcare databases and actionable analytical intelligence. For a Certified Health Data Analyst (CHDA), writing precise, performant Structured Query Language (SQL) queries and parsing complex electronic healthcare transaction sets (such as ANSI ASC X12N 837 claims) are essential competencies. Healthcare data extraction requires not only technical proficiency with relational algebra, Common Table Expressions (CTEs), and window functions, but also deep domain expertise in clinical event sequencing, CMS quality program logic, and reimbursement claim structures.
1. SQL Fundamentals & Query Execution Order for Health Data Analysts
To write bug-free queries, a health data analyst must understand the discrepancy between the syntactic order (how SQL is written) and the logical execution order (how the database engine processes the query): +---------------------------------------------------------------------------------------------------+
- | LOGICAL SQL QUERY PROCESSING PIPELINE |
- +---------------------------------------------------------------------------------------------------+
- [1. FROM & JOIN] --> Identify source tables, evaluate Cartesian products & join conditions
- [2. WHERE] --> Filter individual base rows before grouping (Pre-aggregation filtering)
- [3. GROUP BY] --> Collapse remaining rows into summary groups based on grouping keys
- [4. HAVING] --> Filter summary groups based on aggregate criteria (Post-aggregation)
- [5. SELECT] --> Evaluate expressions, column projections, and window functions
- [6. DISTINCT] --> Eliminate duplicate projected rows
- [7. ORDER BY] --> Sort the final result dataset (Ascending / Descending)
- [8. LIMIT / TOP] --> Restrict the number of output rows returned to the client
- +---------------------------------------------------------------------------------------------------+
- Critical Healthcare Filtering Distinction: WHERE vs. HAVING
WHERE Clause (Pre-Aggregation): Filters individual row-level clinical observations before any grouping occurs. For example, filtering for laboratory records where lab_result_value > 140 removes non-qualifying lab results before calculating averages.
HAVING Clause (Post-Aggregation): Filters summarized group records after the GROUP BY operation has aggregated data. For example, identifying super-utilizer patients who have had at least 4 distinct inpatient admissions: SELECT
- patient_id,
- COUNT(DISTINCT encounter_id) AS total_admissions,
- AVG(length_of_stay_days) AS average_los
- FROM fact_inpatient_encounters
- WHERE discharge_date >= '2025-01-01' -- Pre-filter: Only 2025 discharges
- GROUP BY patient_id
- HAVING COUNT(DISTINCT encounter_id) >= 4; -- Post-filter: Only patients with >= 4 stays
Common Analytical Pitfall: Placing an aggregate condition in the WHERE clause (e.g., WHERE COUNT(encounter_id) >= 4 ) triggers a database syntax error because individual rows cannot evaluate aggregate summaries.
2. Relational Joins in Healthcare Analytics Contexts
Relational joins merge data across normalized or dimensional tables based on common relational keys. Selecting the incorrect join type fundamentally alters clinical cohort counts and financial totals. +---------------------------------------------------------------------------------------------------+
- | RELATIONAL JOINS IN HEALTHCARE |
- +-----------------------------------+-----------------------------------+---------------------------+
- | INNER JOIN | LEFT OUTER JOIN | FULL OUTER JOIN |
- | - Only matching records in BOTH | - ALL records from left table | - ALL records from BOTH |
- | - Encounters WITH confirmed DX | - Left: All admitted patients | - Left: Hospital billing |
- | - Drops unmapped records | - Right: Readmission events | - Right: Payer remit 835 |
- | | - Non-readmits return NULL | - Highlights unbilled/unpd|
- +-----------------------------------+-----------------------------------+---------------------------+
- 1. INNER JOIN
Returns only rows where the join predicate matches in both tables. Records without a corresponding match in either table are completely excluded.
Healthcare Context: Extracting all inpatient encounters that have a confirmed, finalized primary diagnosis coded in the diagnosis table: SELECT
- e.encounter_id,
- e.patient_id,
- e.admit_date,
- d.icd10_code,
- d.diagnosis_description
- FROM fact_encounters e
- INNER JOIN dim_diagnosis d
- ON e.primary_diagnosis_sk = d.diagnosis_sk;
- 2. LEFT OUTER JOIN
Returns all rows from the left table, along with matched rows from the right table. When no match exists in the right table, all right-table columns return NULL .
Healthcare Context: Extracting a complete cohort of heart failure patients and determining whether each patient experienced a subsequent hospital readmission. Patients who were not readmitted are retained in the output dataset with NULL readmission attributes: SELECT
- index_stay.patient_id,
- index_stay.encounter_id AS index_encounter_id,
- index_stay.discharge_date AS index_discharge_date,
- readmit_stay.encounter_id AS readmit_encounter_id,
- readmit_stay.admit_date AS readmit_admit_date,
- CASE
- WHEN readmit_stay.encounter_id IS NOT NULL THEN 1
- ELSE 0
- END AS readmission_flag
- FROM fact_inpatient_encounters index_stay
- LEFT JOIN fact_inpatient_encounters readmit_stay
- ON index_stay.patient_id = readmit_stay.patient_id
- AND readmit_stay.admit_date > index_stay.discharge_date
- AND readmit_stay.admit_date <= index_stay.discharge_date + INTERVAL '30 days'
- WHERE index_stay.primary_diagnosis_code LIKE 'I50%'; -- Heart Failure
- 3. FULL OUTER JOIN
Returns all rows from both tables, pairing matching rows where available and filling NULL values for unmatched rows on either side.
Healthcare Context: Reconciling internal hospital billing ledger claims against external payer Electronic Remittance Advice (EDI 835) files. Highlights claims billed by the hospital with no payer remittance, as well as remittances received with no matching internal claim record.
4. CROSS JOIN (Cartesian Product)
Pairs every row of the left table with every row of the right table ( N × M N \times M N × M rows). Used in healthcare analytics to generate dense date grids crossed with hospital nursing units to ensure zero-filling for daily bed occupancy reporting on days with zero admissions.
3. Subqueries, Common Table Expressions (CTEs) & Window Functions
Complex clinical analytics require multi-stage data pipelines and non-aggregating partition calculations.
Subqueries vs. Common Table Expressions (CTEs)
Subqueries: Queries nested inside an outer SELECT , FROM , or WHERE clause. While effective for simple lookups, deeply nested subqueries become unreadable and difficult to debug.
Common Table Expressions (CTEs - WITH clauses): Define named, temporary result sets that can be referenced sequentially within the main query. CTEs dramatically improve code readability, maintainability, and modularity when constructing multi-stage clinical cohorts.
Advanced Window Functions in Healthcare
Unlike standard aggregate functions ( SUM , AVG ) which collapse multiple rows into a single summary output, Window Functions perform calculations across a specific subset (window) of rows related to the current row while preserving every individual row's granularity. FUNCTION() OVER (
- PARTITION BY partition_column -- Defines the cohort grouping (e.g., patient_id)
- ORDER BY sort_column -- Defines the sequence within the window (e.g., admit_date)
- ROWS/RANGE frame_clause -- Defines the physical sliding window boundaries
- )
- 1. ROW_NUMBER() , RANK() , and DENSE_RANK()
ROW_NUMBER() : Assigns a unique sequential integer (1, 2, 3...) to each row within a partition. Ideal for selecting the single most recent encounter or the primary attending physician: -- Extract only the most recent HbA1c test result for each diabetic patient
- WITH ranked_labs AS (
- SELECT
- patient_id,
- lab_order_id,
- lab_result_date,
- lab_result_value,
- ROW_NUMBER() OVER (
- PARTITION BY patient_id
- ORDER BY lab_result_date DESC
- ) AS rn
- FROM fact_lab_results
- WHERE loinc_code IN ('4548-4', '17856-6') -- HbA1c LOINC codes
- )
- SELECT patient_id, lab_order_id, lab_result_date, lab_result_value
- FROM ranked_labs
- WHERE rn = 1; -- Filter for most recent test
RANK() vs. DENSE_RANK() : Used when ranking providers by clinical volume or quality scores. When ties occur, RANK() skips subsequent ranks (e.g., 1, 2, 2, 4), whereas DENSE_RANK() does not skip ranks (e.g., 1, 2, 2, 3).
2. LAG() and LEAD() Functions
Access values from preceding ( LAG ) or subsequent ( LEAD ) rows within a partition without executing expensive self-joins.
LAG(column, offset) : Looks backward to retrieve attributes from a prior encounter (e.g., retrieving previous discharge date to calculate days since last discharge).
LEAD(column, offset) : Looks forward to retrieve attributes from a future encounter (e.g., retrieving next admission date to evaluate readmission timing).
- 4. Healthcare-Specific SQL Logic & Worked Queries
- 1. Inpatient Length of Stay (LOS) Calculation
Under standard hospital billing rules, Length of Stay (LOS) represents the number of days a patient occupied an inpatient bed. The admission day is counted, but the discharge day is not. However, if a patient is admitted and discharged on the same calendar day , the stay is statistically credited as 1 day (never 0 days): SELECT
- encounter_id,
- patient_id,
- admit_date,
- discharge_date,
- CASE
- WHEN DATEDIFF(day, admit_date, discharge_date) = 0 THEN 1
- ELSE DATEDIFF(day, admit_date, discharge_date)
- END AS calculated_inpatient_los_days
- FROM fact_inpatient_encounters;
- 2. CMS 30-Day All-Cause Readmission Identification Query
The CMS Hospital Readmissions Reduction Program (HRRP) evaluates unplanned 30-day all-cause readmissions following an eligible index admission. Stays where the patient expired in the hospital ( discharge_disposition = '20' ) or left Against Medical Advice (AMA) ( discharge_disposition = '07' ) are statutory exclusions from index admission cohorts: WITH encounter_trajectory AS (
- SELECT
- patient_id,
- encounter_id AS index_encounter_id,
- admit_date AS index_admit_date,
- discharge_date AS index_discharge_date,
- discharge_disposition,
- primary_diagnosis_code,
- - Retrieve the next subsequent admission date for this patient
- LEAD(encounter_id) OVER (
- PARTITION BY patient_id
- ORDER BY admit_date
- ) AS next_encounter_id,
- LEAD(admit_date) OVER (
- PARTITION BY patient_id
- ORDER BY admit_date
- ) AS next_admit_date
- FROM fact_inpatient_encounters
- WHERE encounter_type = 'Inpatient'
- )
- SELECT
- index_encounter_id,
- patient_id,
- index_admit_date,
- index_discharge_date,
- primary_diagnosis_code,
- next_encounter_id AS readmit_encounter_id,
- next_admit_date AS readmit_admit_date,
- DATEDIFF(day, index_discharge_date, next_admit_date) AS days_to_readmit,
- CASE
- WHEN DATEDIFF(day, index_discharge_date, next_admit_date) BETWEEN 1 AND 30
- THEN 1
- ELSE 0
- END AS is_30day_readmission
- FROM encounter_trajectory
- WHERE discharge_disposition NOT IN ('20', '07') -- Exclude in-hospital mortality & AMA
- AND index_discharge_date <= CURRENT_DATE - INTERVAL '30 days'; -- Ensure full 30-day follow-up
- 3. Accurate Patient Age Calculation at Admission
Calculating patient age requires precise handling of leap years and whether the patient's birthday has occurred prior to the admission date in the encounter year: SELECT
- patient_id,
- date_of_birth,
- admit_date,
- DATEDIFF(year, date_of_birth, admit_date) -
- CASE
- WHEN (MONTH(date_of_birth) > MONTH(admit_date))
- OR (MONTH(date_of_birth) = MONTH(admit_date) AND DAY(date_of_birth) > DAY(admit_date))
- THEN 1
- ELSE 0
- END AS exact_age_at_admission
- FROM dim_patient p
- INNER JOIN fact_encounters e ON p.patient_sk = e.patient_sk;
- 4. Defensive SQL & NULL Handling
Healthcare datasets frequently contain missing or null values in optional fields. Robust queries use COALESCE() and NULLIF() to prevent runtime errors and data skew:
COALESCE(expression, default_value) : Returns the first non-null argument in the list. Useful for populating default insurance categories: COALESCE(primary_payer_name, 'Self-Pay / Uninsured') .
NULLIF(value1, value2) : Returns NULL if value1 = value2 . Essential for preventing catastrophic divide-by-zero errors when calculating hospital Cost-to-Charge Ratios (CCR) or unit costs: SELECT
- department_id,
- total_costs,
- total_charges,
- - Prevent divide-by-zero if total_charges is 0
- total_costs / NULLIF(total_charges, 0) AS calculated_ccr
- FROM fact_department_financials;
- 5. Extracting & Parsing Electronic Claims Feeds (EDI)
Healthcare billing and claims feeds are transmitted using standardized electronic data interchange (EDI) formats governed by the ANSI ASC X12N standards mandated under HIPAA. +---------------------------------------------------------------------------------------------------+
- | ANSI ASC X12N 837 CLAIMS DATA STRUCTURE |
- +---------------------------------------------------------------------------------------------------+
- LOOP 1000A / 1000B : Submitter & Receiver Information (Clearinghouse / Payer Identifiers)
- LOOP 2000A / 2010AA: Billing Provider Hierarchical Level & Identity (NPI, Tax ID, Address)
- LOOP 2000B / 2010BA: Subscriber & Patient Demographics (Member ID, Group Number, DOB, Gender)
- LOOP 2300 : Claim Information Header (Total Charges, DRG, Principal Diagnosis, Dates)
- LOOP 2400 : Service Line Detail (Revenue Codes [SV2], CPT/HCPCS [SV1], Modifiers, Units)
- +---------------------------------------------------------------------------------------------------+
- 837I (Institutional) vs. 837P (Professional) Claims
ANSI ASC X12N 837I (Institutional Claim): Electronic equivalent of the UB-04 / CMS-1450 paper claim. Used by hospitals, skilled nursing facilities, home health agencies, and hospices. Captures facility revenue codes, ICD-10-PCS inpatient procedure codes, and MS-DRG/APC groupers.
ANSI ASC X12N 837P (Professional Claim): Electronic equivalent of the CMS-1500 paper claim. Used by physicians, mid-level practitioners, and freestanding outpatient clinics. Captures CPT/HCPCS procedure codes, professional modifiers, and Place of Service (POS) codes.
EDI File Structure & Delimiters
EDI transaction sets are plain-text, delimited data streams structured into segments and data elements:
Segment Terminator (typically ~ or newline): Marks the conclusion of a segment line (e.g., CLM...~ ).
Element Separator (typically * ): Separates individual data elements within a segment (e.g., HI*ABK:I509~ ).
Sub-element / Component Separator (typically : ): Separates sub-components within a composite data element (e.g., qualifier ABK and ICD-10 code I509 ).
- Sample 837 Claims Stream Breakdown CLM*CLAIM90210*14500.00***11:A:1*Y*A*Y*Y~
- DTP*434*RD8*20260301-20260305~
- CL1*1*1*01~
- HI*ABK:I509*ABF:I10*ABF:E119~
- LX*1~
- SV2*0450*HC:99284*750.00*UN*1~
- LX*2~
- SV2*0250*HC:J0696:25*450.00*UN*2~
- Parsing Key Financial Elements
Revenue Codes (837I Loop 2400 SV2 Segment): Standardized 4-digit codes identifying the specific hospital accommodation or ancillary department furnishing the service:
- 0450 – Emergency Room
- 0250 – Pharmacy (General)
- 0200 – Intensive Care Unit (ICU)
- 0300 – Laboratory (General)
- 0320 – Radiology (Diagnostic)
- 0360 – Operating Room Services
CPT/HCPCS Procedure Modifiers: Two-character alphanumeric codes appended to procedure codes in SV1 or SV2 to give vital clinical context without changing the core definition of the code:
Modifier -25 : Significant, separately identifiable Evaluation and Management (E/M) service by the same physician on the same day of a minor procedure.
Modifier -59 : Distinct procedural service performed on the same day, breaking standard NCCI unbundling edits.
Modifier -X{EPSU} : Specific subsets of modifier -59 ( -XE Separate Encounter, -XP Separate Practitioner, -XS Separate Structure/Organ, -XU Unusual Non-Overlapping Service).
Modifier -TC : Technical Component only (facility overhead, equipment, technologist time).
Modifier -26 : Professional Component only (physician interpretation and report).