Flagship Case Study · Clinical Data Engineering
EVA CTMS Archive — Clinical Data ETL Pipeline
Turning a decade of unstructured clinical-trial files into a validated, searchable, audit-ready archive — engineered locally in Python, with zero PHI exposure.
Proyecto Destacado · Ingeniería de Datos Clínicos
Archivo EVA CTMS — Pipeline ETL de Datos Clínicos
Convertir una década de archivos de ensayos clínicos sin estructura en un archivo validado, consultable y listo para auditoría — construido localmente en Python, sin exponer PHI.
The Problem
El Problema
A decommissioned Clinical Trial Management System (CTMS) left roughly 9,925 documents in inconsistently named folders, alongside a separate 6,830-record patient database — with no SharePoint architecture in place. Before anything could be migrated, it had to be understood, cleaned, and verified.
The tempting shortcut would have been to hand the entire archive to an AI tool and let it sort out the structure, categories, and patient links in one pass. But that would mean uploading thousands of records — names, dates of birth, clinical notes, patient IDs, and medical records — to a system outside the organization's control, putting a large amount of protected data at risk. Avoiding exactly that is why this pipeline was built. Every script, validation check, and classification runs locally, on the organization's machine only.
Staff reported two concrete pain points the design had to solve: slow document lookup and unreliable protocol-deviation tracking.
Un Sistema de Gestión de Ensayos Clínicos (CTMS) desmantelado dejó cerca de 9,925 documentos en carpetas con nombres inconsistentes, junto a una base de datos de 6,830 pacientes — sin ninguna arquitectura de SharePoint. Antes de migrar nada, había que entenderlo, limpiarlo y verificarlo.
El atajo tentador habría sido entregar todo el archivo a una herramienta de IA para que ordenara la estructura, las categorías y los vínculos de pacientes de una sola vez. Pero eso significaría subir miles de registros — nombres, fechas de nacimiento, notas clínicas, IDs de pacientes y expedientes médicos — a un sistema fuera del control de la organización, poniendo en riesgo una gran cantidad de datos protegidos. Evitar precisamente eso es la razón por la que se construyó este pipeline. Cada script, verificación y clasificación se ejecuta localmente, únicamente en la máquina de la organización.
El personal reportó dos problemas concretos que el diseño debía resolver: búsqueda lenta de documentos y un seguimiento de desviaciones de protocolo poco fiable.
The Pipeline (Extract → Transform → Load)
El Pipeline (Extraer → Transformar → Cargar)
A 10-stage local Python ETL pipeline turns raw, messy inputs into a governed, validated schema. The cross-validation stage — highlighted below — is where the project's central finding was caught, before anything reached SharePoint.
Un pipeline ETL local de 10 etapas en Python convierte entradas crudas y desordenadas en un esquema gobernado y validado. La etapa de validación cruzada — resaltada abajo — es donde se detectó el hallazgo central del proyecto, antes de que nada llegara a SharePoint.
Extract
Transform
Load
Raw & messy → validate & standardize → governed, audit-ready schema
Crudo y desordenado → validar y estandarizar → esquema gobernado y listo para auditoría
The Central Finding
El Hallazgo Central
The pipeline identifies which patient a document belongs to by extracting an ID from the filename. Checked against the real patient database, 1,791 of 9,925 documents (18%) produced an ID that matched no patient in the database.
The cause is structural, not random. Filenames throughout the archive contain document control numbers, lab report numbers, dates, protocol codes, and sequential labels (like DEVIATION5) — many the same length and character mix as a genuine patient ID. Because real patient IDs and these incidental strings share the same structural signature, no pattern-matching rule can reliably separate them.
Why it matters: without this check, up to 1,791 documents could have been migrated tagged to the wrong patient — or to a patient who does not exist. In an archive meant to preserve accurate clinical records under HIPAA and ICH-GCP, that is a data-integrity risk, not a cosmetic one.
Why it is a finding, not a failure: the validation step exists specifically to catch this class of problem before anything moves. It worked. Nothing was migrated on an unverified patient link — the system flagged the uncertainty and stopped rather than guessing. The underlying issue lives in the source data's naming conventions and predates the project entirely.
El pipeline identifica a qué paciente pertenece un documento extrayendo un ID del nombre del archivo. Al comparar con la base de datos real de pacientes, 1,791 de 9,925 documentos (18%) produjeron un ID que no coincidía con ningún paciente.
La causa es estructural, no aleatoria. Los nombres de archivo del archivo contienen números de control de documentos, números de informes de laboratorio, fechas, códigos de protocolo y etiquetas secuenciales (como DEVIATION5) — muchos con la misma longitud y mezcla de caracteres que un ID real de paciente. Como los IDs reales y estas cadenas incidentales comparten la misma firma estructural, ninguna regla de coincidencia de patrones puede separarlos de forma fiable.
Por qué importa: sin esta verificación, hasta 1,791 documentos podrían haberse migrado etiquetados al paciente equivocado — o a un paciente que no existe. En un archivo destinado a preservar registros clínicos precisos bajo HIPAA e ICH-GCP, eso es un riesgo de integridad de datos, no algo cosmético.
Por qué es un hallazgo, no un fallo: la etapa de validación existe específicamente para detectar este tipo de problema antes de mover nada. Funcionó. Nada se migró con un vínculo de paciente sin verificar — el sistema marcó la incertidumbre y se detuvo en lugar de adivinar. El problema de fondo vive en las convenciones de nombres de los datos originales y es anterior al proyecto.
| Migration Clearance | Estado de Migración | Count | Cantidad |
|---|---|---|---|
| Cleared for migration | Listo para migrar | 964 | |
| Valid — resolve first (rename, PHI review, duplicate, deviation) | Válido — resolver primero (renombrar, revisión PHI, duplicado, desviación) | 7,163 | |
| Hold — orphaned: no match in patient database | Retenido — huérfano: sin coincidencia en la base de datos | 1,791 | |
| Hold — file flagged for delete | Retenido — archivo marcado para eliminar | 7 | |
| Documents | Documentos | 9,925 |
The Compliance Approach
El Enfoque de Cumplimiento
Local-only processing
Procesamiento solo local
Every script ran on the organization's machine. No document and no patient record was ever uploaded to the cloud or any external service.
Cada script se ejecutó en la máquina de la organización. Ningún documento ni registro de paciente se subió a la nube ni a ningún servicio externo.
PHI masking in every log
Enmascarado de PHI en cada registro
A dedicated PHI-safe logger masks identifiers everywhere — letters to X, digits to # — so even routine troubleshooting never displays a real identifier.
Un registrador seguro para PHI enmascara los identificadores en todas partes — letras a X, dígitos a # — para que ni siquiera la depuración muestre un identificador real.
No AI exposure to patient data
Sin exposición de datos a la IA
AI assisted only with writing and debugging code — never to view or process a real patient record. Debugging used masked shapes, aggregate counts, or synthetic fixtures.
La IA solo ayudó a escribir y depurar el código — nunca a ver ni procesar un registro real de paciente. La depuración usó formas enmascaradas, conteos agregados o datos sintéticos.
Audit-ready by design
Listo para auditoría por diseño
Versioning was enabled before any upload, with a read-only staff / edit core-team permission model — configured for HIPAA and ICH-GCP audit-readiness.
El versionado se activó antes de cualquier carga, con un modelo de permisos de solo lectura para el personal y edición para el equipo central — configurado para auditorías HIPAA e ICH-GCP.
What Was Built
Lo Que Se Construyó
Beyond the pipeline, the project delivered a complete SharePoint archive architecture:
- Five reference lists — the "phonebooks" documents link back to: Sites (69), Physicians (182), Coordinators (148), Disease Sites (125), Disease Categories (19).
- A document library ("Clinical Trial Documents") with 11 metadata columns (category, patient ID, recommended action, junk flag, notes) and 3 working lookups linking each document to its site, physician, and coordinator.
- Four custom views: Patient Lookup (one click shows a patient's whole history), Deviations Needing Review, Pending Cleanup, and By Category.
- A full documentation set — pipeline reference, generated-file guides, and the audit finding — so the process is reproducible by the next person.
Más allá del pipeline, el proyecto entregó una arquitectura completa de archivo en SharePoint:
- Cinco listas de referencia — las "guías telefónicas" a las que los documentos se vinculan: Sitios (69), Médicos (182), Coordinadores (148), Sitios de Enfermedad (125), Categorías de Enfermedad (19).
- Una biblioteca de documentos ("Clinical Trial Documents") con 11 columnas de metadatos (categoría, ID de paciente, acción recomendada, marca de descarte, notas) y 3 lookups funcionales que vinculan cada documento con su sitio, médico y coordinador.
- Cuatro vistas personalizadas: Búsqueda de Paciente (un clic muestra todo el historial de un paciente), Desviaciones por Revisar, Limpieza Pendiente y Por Categoría.
- Un conjunto completo de documentación — referencia del pipeline, guías de archivos generados y el hallazgo de auditoría — para que el proceso sea reproducible por la siguiente persona.
Results & Recommendations
Resultados y Recomendaciones
- 964 documents cleared for a pilot migration with validated metadata.
- The 1,791 orphaned documents were handed off as a documented decision for the data owners — not forced through on an unverified link.
- Recommended next build: a second, authoritative deviation registry sourced directly from the structured deviation tables (which carry real relational links) rather than filename inference — a stronger long-term answer to the deviation-tracking requirement.
- 964 documentos aprobados para una migración piloto con metadatos validados.
- Los 1,791 documentos huérfanos se entregaron como una decisión documentada para los dueños de los datos — no se forzaron con un vínculo sin verificar.
- Siguiente construcción recomendada: un segundo registro de desviaciones autoritativo, generado directamente desde las tablas estructuradas de desviaciones (que tienen vínculos relacionales reales) en lugar de inferir del nombre de archivo — una respuesta más sólida a largo plazo.
What This Demonstrates
Lo Que Esto Demuestra
End-to-end ETL
ETL de extremo a extremo
Extract, transform, and load on messy, real-world clinical data — at scale, reproducibly.
Extraer, transformar y cargar datos clínicos reales y desordenados — a escala y de forma reproducible.
Data-integrity validation
Validación de integridad
Validation logic that catches errors before they propagate into a system of record.
Lógica de validación que detecta errores antes de que se propaguen a un sistema de registro.
Compliance by design
Cumplimiento por diseño
HIPAA / ICH-GCP handling, PHI masking, and access control built in from the start.
Manejo HIPAA / ICH-GCP, enmascarado de PHI y control de acceso desde el inicio.
Reproducible design
Diseño reproducible
Schema-driven, documented, multi-stage pipeline the next engineer can re-run.
Pipeline multietapa, documentado y guiado por esquema que el siguiente ingeniero puede re-ejecutar.