This project was designed as an end-to-end healthcare quality improvement analysis focused on catheter-associated urinary tract infection (CAUTI) and central line-associated bloodstream infection (CLABSI) prevention.
I began with a fully synthetic 2024 inpatient dataset designed to resemble several connected sources that might be used in hospital infection-prevention work. The original database contained patient demographics, hospital encounters, invasive-device episodes, and infection-surveillance records. A fifth table of sampled device-maintenance audits was later added so the analysis could evaluate prevention processes alongside infection outcomes and device exposure.
The project intentionally included realistic data-quality problems rather than beginning with a perfectly clean dataset. These included inconsistent unit names, missing dates, device removal dates occurring after discharge, incomplete device linkage, and intentionally imperfect source surveillance flags. I used SQL in BigQuery to profile these issues, preserve the original values, create standardized analytical fields, and validate the resulting tables before performing any rate calculations.
A major part of the project was separating what the available data could support from what it could not. The synthetic surveillance data do not contain every clinical element required to independently reproduce formal NHSN CAUTI or CLABSI definitions. Culture date was therefore used as a timing proxy, device exposure was evaluated using the available insertion and end dates, and the source NHSN flag was preserved as one piece of evidence rather than treated as an unquestioned final classification.
For that reason, the final outcome measure is an analytical CAUTI or CLABSI candidate rather than a confirmed NHSN infection.
The project connects three types of quality measures:
Outcome measures
Analytical CAUTI and CLABSI candidate events and candidate rates per 1,000 relevant device-days.
Exposure measures
Foley catheter-days and central-line days, including the distribution of exposure across device-duration groups.
Process measures
Sampled maintenance audits, individual maintenance-element compliance, and all-or-none bundle compliance.
A fourth component—data quality—was retained throughout the analysis by tracking records requiring surveillance review, missing device links, missing culture dates, date inconsistencies, and audit coverage.
The source and process data included:
1,000 synthetic patients
1,194 hospital encounters
865 device episodes
535 Foley catheter episodes
330 central-line episodes
98 infection-surveillance records
1,368 sampled device-maintenance audits
After cleaning, the 865 device episodes represented 3,912 device-days:
2,417 Foley catheter-days
1,495 central-line days
The surveillance workflow identified 31 records that met the project's final analytical candidate logic:
16 analytical CAUTI candidates
15 analytical CLABSI candidates
Hospital-wide analytical candidate rates were:
CAUTI: 6.62 candidates per 1,000 Foley catheter-days
CLABSI: 10.03 candidates per 1,000 central-line days
Overall all-or-none maintenance bundle compliance was 77.1%, with 1,055 of 1,368 sampled audits meeting every applicable maintenance element.
The clearest combined process-and-outcome signal involved ICU central lines.
The ICU had 11 analytical CLABSI candidates across 750 central-line days, producing the highest unit-level CLABSI candidate rate at 14.67 per 1,000 central-line days. The same group had the lowest all-or-none maintenance bundle compliance at 61.4%.
Within ICU central-line maintenance, the two lowest individual element results were:
CHG bathing: 87.9%
Hub disinfection: 89.0%
These findings do not prove that lower bundle compliance caused the higher candidate rate. They identify ICU central-line maintenance as the strongest place to begin a more focused quality-improvement investigation.
Device duration also became important. Approximately 62.5% of all central-line days and 61.7% of all Foley catheter-days occurred during device episodes lasting at least five days. All 15 CLABSI candidates and 15 of the 16 CAUTI candidates were associated with devices in these 5+ day duration groups.
This does not mean a device should automatically be removed after a specific number of days. The duration bands were created for exploratory analysis. The finding supports closer attention to daily device-necessity review, appropriate indications, timely removal when a device is no longer required, and review of prolonged device episodes.
The project also identified a surveillance data-quality opportunity. Thirty records required additional review, including 26 where available device timing supported possible association while the source surveillance flag was negative and four records with missing culture dates. Another 33 records could not be assessed for device association because they did not contain a linked device.
The embedded Tableau report presents the project in four sections:
HAI Prevention Overview — overall rates, candidate counts, device exposure, bundle compliance, unit comparisons, and monthly trends.
Prevention Drivers and Improvement Opportunities — maintenance-element reliability, complete-bundle failures, device duration, and the relationship between process and outcome measures.
Case and Surveillance Context — candidate organisms, patient-context comparisons, surveillance-review status, and candidate-event detail.
Quality Improvement Summary and Next Steps — prioritized improvement opportunities, analytical methods, limitations, future directions, and measures for a future improvement cycle.
The dashboard should be interpreted as a demonstration of healthcare quality analytics methodology using synthetic data, not as an assessment of actual hospital performance.