Ingest
Load daily survey and facility files from multiple partner sites with schema checks at entry.
ETL Pipeline Architecture for Multi-Country Research Operations
Raw extract to validated reporting model
Project B · Data Pipeline / ETL Architecture
A reproducible ETL pipeline for study operations.
This project documents how I would move survey and facility extracts from raw files into a validated reporting layer that can feed dashboards, briefs, and partner reporting without manual spreadsheet firefighting.
Pipeline objective
The practical goal is simple: stop every analysis cycle from starting with a new round of ad hoc cleaning. The pipeline standardizes raw extracts, applies validation rules, and publishes a reporting-ready table with audit flags preserved.
Validate
Run range checks, missingness rules, duplicate detection, and referential integrity against facility metadata.
Transform
Standardize names, dates, units, and indicator logic into a tidy reporting model.
Publish
Export clean tables and QA summaries for dashboards, analyst handoff, and stakeholder reporting.
Validation rules included
| Rule | Purpose |
|---|---|
| Unique household or patient ID | Prevent duplicate counting |
| Allowed facility IDs only | Catch site mapping errors early |
| Date ordering check | Ensure visit dates do not precede enrollment |
| Income and expenditure bounds | Flag impossible or clearly mis-entered values |
| Required fields by encounter type | Make downstream indicators safe to compute |
Output model
The pipeline produces three layers:
raw: immutable copies of source extractsstaging: standardized field names and basic harmonizationreporting: one clean fact table joined to a facility reference table and QA summary
Files in this project
- etl_pipeline.R for the scripted ETL workflow
- data/raw_household_extract.csv as a demo source file
- data/facility_reference.csv for lookup validation
- README.md describing the folder structure and flow
Why it belongs in the portfolio
This is the kind of work that makes dashboards trustworthy. It is also the bridge between research data management and modern data engineering: naming conventions, validation layers, reproducible jobs, and clear handoff contracts.