The first phase of the project was to build a relational synthetic dataset that could support an end-to-end CAUTI and CLABSI quality improvement analysis.
Rather than starting with one flat spreadsheet, the data were organized into separate but related tables resembling different pieces of a hospital data environment. This made it possible to practice relational validation, joins, surveillance review, device-exposure calculations, and process measurement before building the final Tableau model.
The original four tables were generated with Python and linked through primary and foreign keys. A fifth device-maintenance audit table was added later to extend the project from outcome surveillance into prevention-process measurement.
All data are synthetic. No real patients or hospitals are represented.
Table: patients
grain: one patient
primary key: patient_id
main relationship: parent table for encounters
rows: 1000
Table: encounters
grain: one hospitalization
primary key: encounter_id
main relationship: patient_id --> patients
rows: 1194
Table: device_events
grain: one device episode
primary key: device_id
main relationship: encounter_id --> encounters
rows: 865
Table: infection_surveillance
grain: one surveillance event
primary key: infection_id
main relationship: encounter_id --> encounters; optional device_id --> device_events
rows: 98
Table: device_bundle_audits
grain: one sampled maintenance audit
primary key: audit_id
main relationship: device_id --> device_events; encounter_id --> encounters
rows: 1368
Foreign-key columns were indexed in the original schema because the later analysis required repeated joins across patients, encounters, devices, surveillance events, and process audits.
patient_id
Unique patient identifier and primary key.
age
Patient age.
sex
Patient sex.
diabetes_flag
Indicates whether a history of diabetes was present in the synthetic patient record.
immunosuppressed_flag
Indicates whether immunosuppression was present.
bmi_category
Categorical BMI classification used for exploratory patient-context analysis.
encounter_id
Unique hospitalization identifier and primary key.
patient_id
Foreign key linking the hospitalization to a patient.
admission_date
Date of hospital admission.
discharge_date
Date of hospital discharge. Some values were intentionally or naturally missing within the synthetic generation process.
unit
Original hospital-unit value. This field intentionally contained inconsistent naming.
primary_diagnosis
Primary diagnosis associated with the encounter.
device_id
Unique device-episode identifier and primary key.
encounter_id
Foreign key linking the device episode to a hospitalization.
device_type
Foley or Central Line.
insertion_date
Date the device was inserted.
removal_date
Original documented device removal date. Some values were missing, and some intentionally occurred after discharge.
insertion_unit
Original unit where the device was inserted. This field used the same inconsistent unit naming represented in the encounter data.
infection_id
Unique surveillance-event identifier and primary key.
encounter_id
Links the surveillance record to the hospitalization.
device_id
Links the surveillance event to a specific device when one was documented. This field is nullable.
culture_date
Date the culture was collected or documented. Four records had no culture date.
organism
Organism identified in the surveillance record.
nhsn_criteria_met
Synthetic source surveillance flag indicating whether the record was originally marked as meeting NHSN criteria. This field was preserved during analysis and was not treated as an independently verified case determination.
audit_id
Unique identifier for each sampled maintenance observation.
device_id
Device episode being audited.
encounter_id
Hospital encounter associated with the device.
audit_date
Date of the maintenance observation.
unit
Standardized unit attributed to the audit.
device_type
Foley or Central Line.
hand_hygiene_compliant
Shared element indicating whether hand-hygiene expectations were met.
daily_necessity_review_completed
Shared element indicating whether continued device need was reviewed.
Foley-specific elements:
closed_system_maintained
drainage_bag_below_bladder
unobstructed_flow
Central-line-specific elements:
dressing_clean_dry_intact
hub_disinfection_compliant
chg_bathing_completed
overall_bundle_compliant
All-or-none measure. An audit was compliant only when every maintenance element applicable to that device type passed.
Elements that did not apply to a particular device type were stored as NA rather than being counted as failures.
The synthetic dataset was deliberately designed to require cleaning.
Fourteen raw unit labels represented three final analytical groups.
ICU variants included:
ICU
icu
Intensive Care Unit
MICU
SICU
Med-Surg variants included:
Med-Surg
Med/Surg
MedSurg
med surg
Medical-Surgical
Telemetry variants included:
Telemetry
Tele
TELE
Cardiac Tele
These were standardized to:
ICU
Med-Surg
Telemetry
The original values were retained in separate raw fields so the cleaning process remained traceable.
Fifty-four device records had a documented removal date occurring after the patient's discharge date.
Because inpatient device exposure should not continue beyond discharge for this analysis, the original removal date was preserved and a separate analytical end date was capped at discharge.
Four of 98 surveillance records had no culture date.
Because culture date was the project's available timing proxy, these records could not be assigned an admission-to-culture timing category and remained in a review group.
A small share of source NHSN criteria flags were intentionally altered during synthetic generation to imitate possible abstraction or data-entry errors.
The SQL analysis did not simply “correct” these flags. Instead, the original flag was preserved and compared with the device and timing evidence available in the dataset. Disagreement resulted in a surveillance-review category.
The broader SQL review also identified:
14 encounters with missing discharge dates
7 device episodes with missing removal dates
35 surveillance records without a linked device ID
7 cultures occurring after discharge
0 duplicate primary keys
0 orphaned foreign keys
0 devices inserted before admission
0 devices inserted after discharge
0 device removals occurring before insertion
The missing device IDs were not treated as errors by default. A surveillance culture can exist without a device link in the available data, so these records were retained and labeled as not device-assessable when appropriate.
The guiding principle was to preserve the source record and add analytical fields rather than overwrite the original data.
For example:
removal_date_raw
Original documented source removal date.
device_end_date_clean
End date used for device-day calculations after reconciliation with discharge.
device_date_corrected_flag
Indicates whether the analytical end date required correction.
device_date_issue_type
Records why the analytical date was changed.
A similar approach was used for unit names and infection-surveillance classifications.
This makes the cleaning process auditable: the original source value remains available next to the standardized or derived value.
Device-days were calculated inclusively from the insertion date through the cleaned device end date.
For example:
Insertion date = Device Day 1
Following date = Device Day 2
Next date = Device Day 3
After cleaning, the 865 device episodes represented:
ICU | 750 central line days | 1010 Foley days | 1760 total device days
med-surg| 548 central line days | 964 foley days | 1512 total device days
telemetry | 197 central line days | 443 Foley days | 640 total device days
total | 1495 central line days | 2417 foley days | 3912 total device days
No overlapping same-type device episodes were identified within the same encounter, so the 3,912 calculated device-days did not require deduplication for overlapping Foley or central-line episodes.
The infection-surveillance workflow was intentionally staged.
Each record was evaluated for:
Admission-to-culture timing
Device linkage
Device day at the time of culture
Whether the culture occurred while the device was present or the following day
Whether available device timing met the project's minimum exposure screen
Agreement or disagreement with the original source NHSN flag
Final project-level analytical classification
A device became eligible for the project's duration screen beginning on Device Day 3.
Culture date was used as a timing proxy because the synthetic dataset does not contain every clinical element required to reconstruct a formal NHSN Date of Event.
The final classification terms were therefore:
Analytical CAUTI Candidate
Analytical CLABSI Candidate
Needs Surveillance Review
Not Device-Assessable
Not Device-Associated Candidate
The words “analytical candidate” are intentional. These categories should not be interpreted as independently confirmed NHSN infections.
The 1,368 audit records represent sampled observations rather than an audit for every device on every device-day.
Some devices received multiple audits, while others received none.
Generation and SQL validation confirmed that:
Every audit ID was unique.
Every audit referenced a valid device and encounter.
Audit dates fell within the cleaned device-exposure window.
Audit unit matched the linked device unit.
Audit device type matched the linked device.
Device-specific maintenance elements used NA correctly.
The all-or-none compliance flag agreed with the underlying applicable elements.
A total of 812 of 865 devices received at least one maintenance audit, or 93.9%. Fifty-three devices had no sampled audit.
Absence of an audit was treated as missing process information, not as noncompliance.