Healthcare ETL Pipeline using Python

Jun 5

5 min read

Introduction: The purpose of this project is to extract the health care data of patients and related Diagnosis information from the relevant XML files .The patient files type is XML format which holds information of patient demographics and unique identifier of Patient UUID and Diagnosis file type is XML format which holds information of patient diagnosis and each record is identifies as Diagnosis ID.

We need to extract data from these two files by using Python and apply the ETL logic like data massaging, Data quality checks and load data into PostgreSQL data base of patient and Diagnosis tables. This project will give clear picture of Patient Demographic information and health records.

ETL: ETL is an abbreviation for extract, transform, and load. This data retrieval and delivery process is essential to business insights and decision-making.

Key takeaways:

ETL extracts, cleans, and loads data from sources, providing organizations with accurate data for analysis and supporting reliable data pipelines.

ETL is central to modern data integration, and demand is rising alongside the growth of big data and real‑time analytics.
Effective ETL pipelines improve data quality by cleansing, validating, and standardizing information before it reaches analytical systems.
You can use ETL to consolidate data from multiple sources and enabling clearer insights in the data warehouse.

XML: XML (extensible Markup Language) is a markup language designed to store and transport data in a structured, readable format for both humans and machines. Unlike programming languages that execute commands, XML focuses on describing and organizing information using custom tags that define what each piece of data represents.

PostgreSQL : is a powerful, open-source object-relational database system that uses and extends the SQL language combined with many features that safely store and scale the most complicated data workloads. PostgreSQL has earned a strong reputation for its proven architecture, reliability, data integrity, robust feature set, extensibility, and the dedication of the open-source community behind the software to consistently deliver performant and innovative solutions. PostgreSQL runs on all major operating systems, has been ACID-compliant.

Python: Python is a computer programming language often used to build websites and software, automate tasks, and conduct data analysis. Python is a general-purpose language, meaning it can be used to create a variety of different programs and isn’t specialized for any specific problems. This versatility, along with its beginner-friendliness, has made it one of the most-used programming languages today.

Table Metadata

Patient Metadata (Column_Names )	Data Types	Diagnosis Metadata (Column_Names )	Data Types	Audit Table	Data Types
patient_id	integer	diagnosis_id	integer	run_id integer	integer
patient_uuid	uuid	diagnosis_uuid	uuid	pipeline_name	character
first_name	character	patient_uuid	character	load_type	character
last_name	character	icd10_code	character	source_file	character
date_of_birth	date	icd10_description	character	target_table	character
gender	character(1)	diagnosis_date	date	start_time	timestamp
email	character	severity	character	end_time	timestamp
phone	character	treating_physician	character	status	character
address_line1	character	facility_code	character	rows_extracted	integer
city	character	is_primary	character	rows_inserted	integer
state_code	character	source_file	character	rows_updated	integer
zip_code	character	created_at	timestamp	rows_deleted	integer
insurance_id	character	updated_at	timestamp	rows_rejected	integer
is_active	boolean	etl_run_id	integer	high_watermark	timestamp
source_system	character			error_message	text
source_file	character			run_metadata	jsonb
created_at	timestamp
updated_at	timestamp
deleted_at	timestamp
etl_run_id	integer

Data Massaging: is a term for cleaning up data that is poorly formatted or missing required data for a particular purpose. The term implies manual processing or highly specific queries to target data that is breaking an automated process or analysis.

Rules:

Category	Conditions	Data Input	Data Output
Name Normalization	Inconsistent casing, hyphens, apostrophes	'mary-jane o\'brien'	'Mary-Jane O\'Brien'
Phone Normalization	Mixed formats, country codes, non-digits	'1 (512) 555.0101'	'512-555-0101'
Email Normalization	Uppercase, invalid format	'JOHN@GMAIL.COM'	'john@gmail.com'
String Cleaning	Whitespace, extra spaces, truncation	' John Smith '	'John Smith'
Date Parsing	Multiple date formats in same file	'15/03/2024' or '2024-03-15'	date(2024, 3, 15)
Amount Parsing	String numbers, out-of-range values, negatives	'-50.00' or '99999999'	Clamped to valid range
UUID Validation	Malformed or missing UUIDs	'not-a-uuid' or None	New auto-generated UUID
Amount Parsing	String numbers, out-of-range values, negatives	'-50.00' or '99999999'	Clamped to valid range

Data Quality: is a broader category of criteria that organizations use to evaluate their data for accuracy, completeness, validity, consistency, uniqueness, timeliness and fitness for purpose.

Rules:

Patients — 10 Rules				Diagnoses — 7 Rules
Rule ID	Columns name	Data Quality Checks	Severity	Rule ID	Columns name	Data Quality Checks	Severity
PAT-DQ-001	patient_uuid	Valid UUID format	CRITICAL	DIAG-DQ-001	diagnosis_uuid	Valid UUID format	CRITICAL
PAT-DQ-002	first_name	Not null or empty	CRITICAL	DIAG-DQ-002	patient_uuid	Valid UUID format (FK)	CRITICAL
PAT-DQ-003	last_name	Not null or empty	CRITICAL	DIAG-DQ-003	icd10_code	Not null or empty	CRITICAL
PAT-DQ-004	date_of_birth	Not null	CRITICAL	DIAG-DQ-004	diagnosis_date	Not null	CRITICAL
PAT-DQ-004b	date_of_birth	Not in the future	CRITICAL	DIAG-DQ-004b	diagnosis_date	Not in the future	CRITICAL
PAT-DQ-004c	date_of_birth	Not before year 1900	WARNING	DIAG-DQ-005	icd10_code	Regex: letter + digits e.g. E11.9	WARNING
PAT-DQ-005	gender	Must be M / F / O / U	WARNING	DIAG-DQ-006	severity	Must be mild/moderate/severe/critical	WARNING
PAT-DQ-006	email	Format: user@domain.ext	WARNING
PAT-DQ-007	phone	Format: XXX-XXX-XXXX	WARNING
PAT-DQ-010	insurance_id	Max 50 characters	WARNING

Full Load: Also known as destructive load, a full load in ETL involves loading the entire dataset from the source system into the target database or warehouse.

Incremental Load: Also known as delta load, an incremental load involves loading only the new or updated data since the last data extraction from the source.

Stage Table: is a temporary table which holds the acts as interim layer which holds the data temporary during the ETL process.

Core ETL logic:

The full load ETL pipeline will parse the all source files of XML load into the python dictionaries without any cleansing .Once data is parsed it will go to the data massaging layer where it will strip the extra spaces in the names, phone numbers are re formatted, emails are lower case and validate data formats etc .Once the data massaging done it will go to data quality checks and apply 17 rules for example invalid UUID’s and unrecognized state codes, make sure gender has female /male ,email has proper format ,Insurance ID is not crossing more than 50 characters.

Once data quality checks and data massaging done successfully our python process will load the date into respective tables of Patient’s and Diagnosis. For initial load it will be a bulk insert and then incremental it will follow the same steps for parsing XML files followed data massaging and data quality checks only difference is incremental load will consider only delta records (updated records).

After successful run we are capturing all the load stats of full load and incremental into audit table call ETL_run_log. This audit table captures the stats of load start date, end date, number of records, last loaded load, job status etc .

Test results:

Patients Full load