Survey Research Data Warehouse & ETL
A production-grade, single-machine data warehouse and ETL platform for high-integrity survey research operations, running inside WSL2 (Ubuntu 24.04) and Docker.
Background & Challenges
Large-scale field research operations require fast, reliable, and secure ingestion of data from remote field agents. Relying on manual downloads or slow overnight updates creates massive lags in detecting data entry mistakes, fabrications, or equipment issues.
This platform delivers a production-grade data engineering infrastructure to handle survey data streams, validate submissions in real-time, detect schema drift, and route clean data to dedicated client database schemas for instant analysis.
Key Objectives
- Real-Time Ingestion: Capture incoming webhook form submissions instantly and back up raw payloads.
- Orchestrated Ingests: Run scheduled incremental imports from SurveyCTO APIs with automatic retry rules.
- Automated QC: Verify submissions using 5 distinct visual, temporal, and numeric checks.
- Multi-Client Segregation: Store data securely inside dedicated Postgres schemas for each client.
- Tiered Cloud Backups: Package compressed DB logs and S3 buckets daily to Cloudflare R2 / Backblaze B2.
Architecture & Pipeline Flow
The platform splits data ingestion into two distinct paths (Real-time Webhook and Orchestrated Nightly ETL) to guarantee high availability and prevent data loss:
1. FastAPI Webhook Receiver
Field apps transmit submissions to a FastAPI endpoint authenticated via HMAC-SHA256 signatures. The incoming JSON payload is archived immediately into a MinIO object storage bucket (raw-bronze) for durability before processing.
2. Prefect Incremental Sync
Prefect 3 handles nightly incremental synchronization from the SurveyCTO V2 API. The workflow handles schema drift gracefully, flattening nested survey payloads and upserting them into database schema profiles.
3. Multi-Tenant Postgres Schemas
Ingested datasets are distributed across dedicated, permission-locked database schemas: client_mtn, client_unilever, and internal. Readers can only access authorized views, maintaining absolute data privacy.
Automated Quality Control (QC) Engine
To eliminate data fabrication and entry errors, the Prefect-driven QC engine runs five complex checks against every single submitted form, flagging anomalies in qc_system.qc_flags with detailed JSONB payloads:
Checks if surveyors filled out the forms faster than physical speeds allow, flagging possible superficial inputs.
Scans datasets for matching phone numbers or respondent identification metrics, preventing duplicate rewards.
Validates if the submission coordinates map strictly inside predefined geo-spatial research zones.
Identifies numerical anomalies in survey responses that lie outside normal statistical distributions.
Tech Stack
System Ports & Services
- FastAPI GatewayPort 8001
- PostgreSQL DBPort 5435
- MinIO ConsolePort 9001
- Prefect ServerPort 4200
- Metabase AppPort 3030
Key Metrics
- Database Engines Postgres 16
- Ingest Options FastAPI & API Sync
- QC Audit Rules 5 Automated Checks
- Security Protocols HMAC Signature
- Backup Storage R2 / B2 Cloud