The big idea
What is a data integration pipeline?
A customer’s data lives across several systems — an API here, a nightly SFTP file there, a production database — and it’s messy, inconsistent, and huge. Your solution needs it in one clean, typed place. Copying it once by hand works for a demo and breaks the moment the data changes, grows, or arrives malformed.
Build a pipeline: connect to each source, stage the raw data, validate and map it onto a canonical schema (quarantining what doesn’t fit), transform and load it idempotently, and run it incrementally with reconciliation. It turns a customer’s mess into a warehouse you can trust.
How to read this: We add one piece at a time, problem then fix, and the diagram grows. Hit Begin.
Step 1 · The skeleton
Copy source to target
The naive version: Ingestion reads the source and writes straight to the Warehouse. It "works" once — until the source is paginated, the data is dirty, a run fails halfway, or tomorrow’s data changes. A one-shot copy is where every fragile integration starts.
Stand up Ingestion as the orchestrator between the Source Systems and the Warehouse. For now it just moves data across. Everything that follows makes that move reliable, validated, and repeatable.
A pipeline, not a copy: The one-time copy is the easy part. The hard part is doing it every day, on changing and imperfect data, without corrupting the target — which is the whole job.
Step 2 · Get the data out
Connectors + staging
The data doesn’t come one clean way. One source is a REST API you must page through; another is an SFTP file dropped nightly; another is a database you read directly. And writing straight into the warehouse means a mid-run failure leaves it half-loaded.
Design decision: Sources arrive as API pages, SFTP files, and DB reads — and a run can fail halfway. Where should extracted data land first?
The call: A staging area holds raw extracted data before it touches the warehouse. — Connectors pull from each source (API pagination, SFTP, DB, CDC) into a raw staging zone. You validate and transform from staging, so a failed run never half-loads the target — you just re-run.
Add Connectors that pull from each source in its own way (API pagination, SFTP fetch, DB read, or CDC stream) into a raw Staging area. Validate and transform from staging, so the warehouse is only ever touched by clean, complete data.
Stage before you load: A raw landing zone decouples extraction from loading: a mid-run failure leaves staging dirty and the warehouse untouched, so recovery is just a re-run — not a cleanup.
Step 3 · Clean or quarantine
Validate + map
Customer data is a museum: inconsistent field names, mixed date formats, nulls that mean five things, values that don’t fit your types. Load it as-is and one bad row silently corrupts everything downstream that trusts the warehouse.
Design decision: Some source rows are malformed — a non-numeric age, a missing key. What do you do with them?
The call: Validate against a canonical schema; route bad rows to quarantine. — Coerce each row to your typed canonical schema. Good rows continue; rows that fail go to a quarantine you can count and hand back to the customer — bad data becomes visible and contained, never silent corruption.
Add a Validate + Map step: coerce each staged row onto your canonical schema. Rows that pass continue to transform; rows that fail are routed to Quarantine — held for inspection and hand-back, never dropped or forced through.
Validate at the boundary: Catch bad data on the way in, before it can corrupt anything downstream. Quarantine (not drop) so failures are countable and recoverable — and a spiking quarantine rate is your schema-drift alarm.
Step 4 · Don’t re-copy everything
Incremental sync
Re-extracting the entire source every run is slow, expensive, and hammers the customer’s systems — and gets worse as the data grows. But you also can’t miss changes. You need only what’s new or changed since last time.
Design decision: Re-copying the whole source every run is too slow and heavy. How do you pull only what changed?
The call: Track a checkpoint (high-water mark or CDC offset) and pull only newer changes. — Record where the last run stopped — a max updated-at timestamp or a change-data-capture offset — and each run pulls only rows past it. Sync stays cheap and fast as the dataset grows, and nothing is missed.
Add a Scheduler that tracks a checkpoint — a high-water mark (max updated-at) or a CDC offset — so each run pulls only what changed since the last one. Keep a separate backfill path to reload history when you need it.
Incremental by checkpoint: Sync cost should scale with what changed, not with total size. A high-water mark or CDC offset makes each run cheap; a backfill mode reloads history on demand.
Step 5 · Load without doubling
Idempotent load
Runs fail and get retried; backfills overlap incrementals. If loading just appends, a re-run doubles rows and a partial retry leaves gaps. The warehouse has to end up correct no matter how many times a run happens.
Design decision: Runs get retried and backfills overlap incrementals. How do you load so a re-run never doubles rows?
The call: Upsert on a business key (merge), so a re-run updates instead of duplicating. — Load by merging on a stable business key: an existing key updates in place, a new key inserts. Re-running a batch or overlapping a backfill just re-writes the same rows — idempotent, so the warehouse is correct regardless of run history.
The Transform step loads into the Warehouse by upserting on a business key — an existing key updates, a new key inserts. A re-run or overlapping backfill re-writes the same rows instead of duplicating them: the load is idempotent.
Idempotent loads: Key every load on a stable business key and merge. Then run history stops mattering — retries, overlaps, and backfills all converge to the same correct warehouse.
Step 6 · Prove it landed
Reconciliation + monitoring
The customer swears the data isn’t syncing. Without evidence, "it works" is your word against theirs. And schema drift — a renamed column, a new format — can silently break a run that looks green. You need proof and early warning.
Add reconciliation: compare source counts and checksums against the warehouse per run, and report any added / removed / changed records — turning "is it syncing?" into a fact. Monitor quarantine rate, row counts, and run duration, with alerts, so drift and failures surface in minutes. The Analyst now queries data you can vouch for.
Reconcile + observe: A reconciliation report is often the artifact that convinces a skeptical customer the migration worked. Quarantine-rate and count alerts catch schema drift before it becomes a silent-corruption incident.
You did it
You just designed a customer data integration pipeline.
- Ingestion orchestrates the run between messy sources and a clean warehouse.
- Connectors pull from API / SFTP / DB / CDC into a raw staging area first.
- Validation coerces to a canonical schema; bad rows are quarantined, never dropped.
- A checkpoint (high-water mark or CDC offset) makes each run incremental, with a backfill path.
- Loads upsert on a business key — idempotent, so retries and overlaps never double or gap.
- Reconciliation proves the data landed; quarantine-rate alerts catch schema drift early.