FDE Foundations · Module 5: Data & Integration
ETL Pipelines in the Field
Field ETL is mostly validation and quarantine, not transformation: expect the source to lie about its own schema, and park what fails instead of dropping it.
11 min reading
Objectives
- Structure an extract-transform-load job for messy sources
- Decide between full reloads and incremental sync
- Design a validation stage with a quarantine path
Extract: assume variety
Real sources deliver the same entity in several shapes: three invoice layouts, two date formats, one legacy export with unnamed columns. Extract raw payloads untouched into a staging area first. When a mapping bug appears, the original payload is still there and reproducible.
Transform: validate before you convert
Run validation before transformation: required fields present, types correct, identifiers resolvable, values in expected ranges. Only validated rows proceed to the canonical model. This ordering means your transformation code never sees garbage, which keeps it small and testable.
Load: choose sync strategy deliberately
Full reloads are simple and idempotent but slow at volume. Incremental syncs need a reliable change signal (updated-at, change log, webhooks) and reconciliation to catch missed events. A pragmatic default: incremental with a scheduled nightly full reconciliation pass that diffs counts and checksums per entity.
Quarantine is a feature
Rows that fail validation go to a quarantine store with payload, error, source, and attempt metadata. The report of quarantined rows is part of the deliverable, not an internal detail: an honest error report is what the customer's data team will judge you on.
Quick check
An optional 2-3 question self-check. Answers never leave your device, are not stored, and never count toward any assessment.
Exercise
Design a fictional ETL job that loads supplier invoices from email attachments plus an ERP export: staging approach, five validation rules, sync strategy with reconciliation, and quarantine record fields.
Pass criteria
Staging keeps raw payloads, five concrete validation rules, a named sync strategy with a reconciliation mechanism, and quarantine fields sufficient for reprocessing.