The whole design, in writing
Every step of the build above, written out: the problem each piece solves, the option that was taken and the ones that were not, the numbers, and how it fails in production.
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.
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.
Why this piece earns its place
The skeleton hides an assumption worth naming out loud: that exactly one run is ever in flight. The first time an extract runs longer than the gap between runs — a slow source, a heavy day — the next run starts while the first is still working, and now two runs share one checkpoint. Each reads roughly the same window, each moves the mark, and whichever finishes last decides where the following run begins, so a window can be skipped entirely. Keyed loads fix a sequential retry; they do nothing about two writers racing on the same bookmark. So I make a run a first-class object before I make it reliable: a lock so a second run waits or is skipped rather than overlapping, a run_id stamped on every staged file and loaded row, and a final status the orchestrator writes down instead of anyone assuming. Without that id, every later question — which rows did this run touch, what did it hold back, what should reconciliation compare — has no way to even be asked.
What the new pieces do
- Source Systemsclient
- The customer’s systems that hold the data — a SaaS API, a nightly SFTP drop, a production database. Messy, inconsistent, and the only source of truth you get.
- Ingestionbackend
- The orchestrator that runs the pipeline: pull from the source, stage it, validate, transform, load. Coordinates the connectors and tracks each run’s checkpoint.
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.
Sources arrive as API pages, SFTP files, and DB reads — and a run can fail halfway. Where should extracted data land first?
Writing directly means a failure partway through leaves the warehouse half-loaded and inconsistent, and you can’t validate before committing. You need a place to land raw data first.
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.
In-memory staging can’t hold large extracts and loses everything on a crash mid-run. Staging must be durable so a failed run is safely re-runnable.
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.
Why this piece earns its place
Staging looks like a buffer and behaves like a record, so the decision this step really introduces is how long raw extracts live there. Keep one run’s worth and you have decoupled extract from load and nothing more: the day you fix a mapping bug, the only way to reprocess last month is to go back and pull it from the customer again — slow, and sometimes impossible, because the source overwrote the rows you wanted. Keep everything forever and you are now the custodian of a full second copy of the customer’s data, in your account, including whatever they later ask you to delete, and paying to hold it. I would size the window to cover the gap between a bad load and someone noticing it, partition staged files by run_id so a re-run replaces that run’s files instead of piling up beside them, and make deletion something the customer can ask for rather than something you discover you cannot do.
- API·SFTP·DB·CDCconnectors
- rawstaging
- re-runnableon failure
What the new pieces do
- Connectorsservice
- Pulls data from wherever it lives — polling an API with pagination, fetching an SFTP file, reading a database, or streaming change-data-capture. Whatever access the customer grants.
- Stagingcache
- A landing area for raw, unmodified source data before anything touches the warehouse. Lets you validate and transform without half-loading the target on a failure.
Back of the envelope
- one connector per source type
- pagination, files, DB, change streams
- land raw in staging first
- never write the warehouse mid-run
- durable staging
- a failed run is safe to retry
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.
Some source rows are malformed — a non-numeric age, a missing key. What do you do with them?
Forcing garbage through with defaults corrupts the dataset — the analyst can’t tell a real zero from a defaulted one. Bad rows must be caught, not silently coerced into plausible-looking wrong data.
Dropping rows loses data invisibly, so no one notices until a report is wrong weeks later. Failed rows have to be captured and surfaced, not discarded.
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.
Why this piece earns its place
Quarantine contains bad data, and then it quietly becomes its own problem: a table that only ever fills. Two things keep it a defense instead of a slower kind of drop. Every held row has to carry the raw row exactly as it arrived, the rule that rejected it, and the run that saw it — the point is to fix the mapping and replay, and a row held without its reason is just a deletion you can count. And the validator has to tell a bad row from a bad schema. One customer typo is a row-level event; a renamed column fails every row in the extract, and patiently grinding a whole day through quarantine turns a one-line mapping fix into a full replay. So past some failure share I stop the run, leave the checkpoint exactly where it is, and page someone, rather than quarantining diligently all night. Containment only counts if somebody drains it.
- canonicalschema
- quarantinenot drop
- driftbecomes visible
What the new pieces do
- Quarantinecache
- Where rows that fail validation go — set aside for inspection and hand-back to the customer, never silently dropped. A rising quarantine rate is your schema-drift alarm.
- Validate + Mapservice
- Coerces each row onto your canonical schema — types, required fields, allowed values. Good rows pass; bad rows are routed to quarantine, not into the warehouse.
Back of the envelope
- coerce to a canonical schema
- types, required fields, allowed values
- bad rows → quarantine
- inspect + hand back, never silent
- quarantine rate = drift alarm
- a rename shows up instantly
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.
Re-copying the whole source every run is too slow and heavy. How do you pull only what changed?
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.
Running less often makes the data staler without fixing the core problem — each run still copies everything. You need incremental extraction, not a lower frequency.
Full extraction still reads and transfers the entire source every run — the expensive part — even if you discard most of it. The savings have to come at extraction, via a checkpoint.
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.
Why this piece earns its place
A high-water mark quietly assumes two things about the customer’s source, and both are false somewhere. It assumes every write touches the updated-at column — a bulk script or a hand-run SQL fix usually does not, so those rows sit behind your mark and are never seen again. And it assumes rows become visible in timestamp order, which they do not: a long transaction stamps a row before your mark but commits after you have already read past it, so that row is skipped permanently and nothing flags it. The defenses are cheap. Overlap the window — start each run slightly behind the last checkpoint rather than exactly on it, which costs a little re-extraction and is harmless precisely because the load is keyed. Advance the checkpoint only after a run has fully succeeded. Take a CDC offset over a column whenever the customer will grant it, because it comes from the log rather than from a field anyone can forget to set. And note what no high-water mark can ever see: a row deleted at the source leaves nothing behind to pull, which is why removals have to be reported rather than inferred.
- checkpointper run
- CDC / high-waterincremental
- backfillon demand
What the new pieces do
- Schedulerservice
- Runs the pipeline on a schedule and tracks a checkpoint (a high-water mark or CDC offset), so each run pulls only what changed — with a backfill path to reload history.
Back of the envelope
- high-water mark or CDC offset
- pull only newer/changed rows
- cost scales with change
- not with total data size
- keep a backfill mode
- reload history when needed
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.
Runs get retried and backfills overlap incrementals. How do you load so a re-run never doubles rows?
Appending doubles rows on any re-run or overlapping backfill, and there’s no way to correct it after the fact. Loads must be keyed so a repeat is a no-op or an update, not a duplicate.
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.
Full delete-and-reload is safe but re-extracts and re-writes everything — throwing away the incremental sync you just built and hammering the source. Keyed upserts get idempotency without the full reload.
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.
Why this piece earns its place
All of this rests on the business key being stable, and in customer data it frequently is not. If the only thing identifying a record is an email address or a name-and-date pair, then the day the customer edits it the merge does exactly what you asked: it inserts a new row and leaves the old one sitting there, plausible and orphaned. Row counts still look reasonable, so nothing complains. So I spend real time choosing the key before I write the merge. Take the source’s own immutable identifier if it has one, however ugly, over anything a human types. If I have to synthesize a key, I hash only the fields the customer confirms never change, and I keep the mapping so a key can be traced back. And I treat a changed key as what it actually is — a delete plus an insert — which means something has to detect it, because an upsert by itself never will. A key chosen casually is a duplicate you meet months later.
- upserton business key
- re-run= no-op / update
- nodoubles or gaps
What the new pieces do
- Transformservice
- Cleans and reshapes the validated data into the model the warehouse expects — deduping, joining, deriving fields — then loads it idempotently on a key.
- Warehousedata
- The destination — a data warehouse or database the analyst queries. Loads are keyed and idempotent, so a re-run never doubles rows or leaves gaps.
Back of the envelope
- merge on a business key
- existing updates, new inserts
- idempotent by construction
- safe to retry any run
- backfill + incremental converge
- same correct result
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.
Why this piece earns its place
Reconciliation fails in practice for a dull reason: the two sides are never compared as of the same moment. Count the source now and it has moved on since the extract, so the numbers differ on every run, and a report that is permanently red teaches everyone to stop opening it. Reconcile as of the window the run actually pulled — the checkpoint boundary it started and finished on — not as of whenever the report happens to run. Checksums carry the matching trap. Validation and transform change values on purpose: a date is reformatted, a type is coerced, a field is derived. Checksum a raw source column against its transformed warehouse column and every row mismatches by design. So compare key sets and counts first, since those survive transformation, and reserve checksums for fields carried through untouched or for the staged copy taken before transform. Then give the report an owner and somewhere the customer sees it, because this only earns trust if it is read.
- counts + checksumsreconcile
- quarantine ratedrift alarm
- per-runreport
What the new pieces do
- Analyst / Appclient
- Whoever consumes the loaded data — a dashboard, an analyst, a model. They trust the warehouse to be correct and complete, which the pipeline has to earn.
Back of the envelope
- reconcile source vs warehouse
- counts, checksums, added/removed/changed
- alert on quarantine spikes
- schema drift shows up first here
- a report earns trust
- proof the migration landed
You did it
You just designed a customer data integration pipeline.
Everything you assembled, in order
- 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.