ETL/ELT Data Warehouse Pipeline

The obvious places to look are the two ends: did the CRM extract pull the right rows, and does the BI dashboard show the right number? Both are cheap to check and both are misleading, because neither tells you where a wrong number came from. The real complexity sits in the middle band — the Staging/Landing Zone, Data Quality & Validation, Transform Layer, and Load Orchestrator — because that is the only stretch of the flow where a record's shape, meaning, and identity all change while nobody is watching. Extraction is a scheduled read; the semantic layer is a presentation contract. Between them, the Data Quality gate makes a keep-or-reject decision, the Transform Layer rewrites the record, and the Load Orchestrator decides when and in what order that rewritten record enters the warehouse. Three independent judgments, each capable of producing output that is internally consistent and still wrong.

The compounding problem is that these stages hide each other's failures. A Data Quality rule that silently drops records leaves a Transform Layer that runs perfectly on a shrunken set; a Transform that mangles a join key still loads cleanly, because the Load Orchestrator's job is sequencing, not meaning; and the semantic layer will faithfully aggregate whatever landed, presenting a plausible chart over a defective warehouse table. By the time the reporting endpoint looks wrong, the causal stage is several handoffs back, and the staging zone that held the original evidence may already have been overwritten by the Extraction Scheduler's next run. So the QA engineer's first instrument should be reconciliation between adjacent middle stages — counts and keys at staging versus post-validation, post-validation versus post-transform, post-transform versus warehouse — with the Data Quality layer's rejects captured rather than discarded. Test the endpoints second. They confirm the pipeline; they never explain it.

The failure mode most likely to blindside a team is the silent schema mismatch that drops columns. Every other item on this list eventually announces itself: a poison message halts processing, a timeout on an oversized batch throws, a job failure leaves records permanently unposted, a retry storm on persistent target failure lights up every dashboard you own. Dropped columns do none of that. The batch runs, the job exits clean, row counts land where they should, and the pipeline reports success — because from the loader's perspective nothing failed. What arrives downstream is a table that is structurally valid and semantically incomplete, and it stays that way through every subsequent run until someone notices a number is wrong.

The assumption that makes this surprising is that a successful load means a complete load — that green means the data is there. It's a reasonable expectation, and it holds for most of the failure modes above, which is exactly what makes it dangerous here: teams calibrate their trust in the success signal on failures that are loud, then carry that trust into the one case where the signal is blind. Payload schema drift after a vendor release is the usual trigger, and it compounds with aggregation double-counting on replay, because the reprocessing you run to fix the gap can corrupt the totals you were trying to repair. For QA, this means status-code and row-count assertions are not coverage. You need column-level contract checks against the expected schema on every load, alerting on the appearance of unmapped or missing fields rather than on job exit status, and reconciliation of aggregate values against source before anything downstream consumes them. And any replay path needs an idempotency test, because the remediation for this failure is itself a failure mode on the same list.

Picture the 02:00 extract job pulling yesterday's CRM changes from the vendor's REST endpoint into the raw landing zone: a page of `opportunity` records, one of them `opportunity_id: "OPP-884213"`, with `account_name: "Halvorsen Logistics"`, `stage: "Closed Won"`, `amount: 47500.00`, `currency: "USD"`, `closed_date: "2024-03-14"`, and `last_modified_utc: "2024-03-14T23:41:07Z"`. The job pages through with an OAuth bearer token, writes each page verbatim to landing as-received, and only then does the transform read that landed copy — normalizing `stage` to a warehouse status code, casting `amount` to a decimal, and shaping the row for the warehouse load. Because OPP-884213 sits in landing untouched, a transform that dies halfway through can be re-run against the exact same bytes without going back to the CRM at all.

What goes wrong at CP1 is upstream of the transform logic. The webhook that was supposed to announce OPP-884213's stage change may never arrive or arrive late, so the extract window closes without it and the record is simply absent from landing — silently, since nothing failed. The token can expire mid-pagination, so pages 1–6 land and pages 7+ don't, or the vendor throttles with 429s and the job quietly captures a partial set. And after a vendor release, `amount` may start arriving as `{"value": 47500.00, "currency": "USD"}` instead of a flat number, or `stage` may become `"ClosedWon"` without the space — landing accepts it fine, and the transform is where it breaks or, worse, coerces it to null. To test: replay a fixture set containing OPP-884213 and assert it appears in landing with `last_modified_utc` intact, then assert the transform emits the expected status code and `47500.00` decimal; force a 401 after page 6 and a 429 on page 3 and assert the job fails loudly rather than declaring success on a partial landing; feed the drifted payload shape for OPP-884213 and assert the transform raises a schema-mismatch error instead of writing a null `amount`; and drop the webhook for OPP-884213 entirely, then confirm a reconciliation check against the source count flags it as missing. Finally, kill the transform mid-run and re-run it against the untouched landed OPP-884213 to prove the re-run needs no re-extract.

Downstream of landing, OPP-884213 moves through validation and transform and arrives at the Load Orchestrator as a shaped row: `opportunity_id "OPP-884213"`, `account_key` resolved for Halvorsen Logistics, `status_code "CLSD_WON"` (normalized from `"Closed Won"`), `amount_usd 47500.00` as a decimal, `closed_date 2024-03-14`, and `source_last_modified_utc 2024-03-14T23:41:07Z` carried through as the ordering key. The orchestrator batches it with the rest of the night's opportunity rows and writes them to the warehouse fact table in chunks — say 5,000 rows per commit, with OPP-884213 sitting somewhere in chunk 4 of 9. Landing still holds the untouched original bytes, so a load that dies at chunk 4 can be driven again from the same transformed output without going back to the CRM. But "the load succeeded" is not "the report is right": the BI layer refreshes off the warehouse as a separate step, so there is a real window where OPP-884213 is physically in the fact table and the Closed Won pipeline dashboard still shows yesterday's number.

The failures here are all about posting, not parsing. A timeout mid-batch can commit chunks 1–3 and abandon 4–9, leaving OPP-884213 permanently unposted while the orchestrator logs a generic failure and the next night's run only carries newer changes — the record never comes back on its own. If the same opportunity was modified twice and both versions land, posting them out of `source_last_modified_utc` order means the older `stage: "Negotiation"` row overwrites `CLSD_WON` and the running Closed Won total is wrong. A re-run after partial commit can double-post OPP-884213 if the load isn't keyed idempotently, inflating `amount_usd` to 95000.00 in aggregate. Edge-case mapping bites too: if `status_code` came through null because the vendor sent `"ClosedWon"` and validation let it pass, the load may insert a row the dashboard silently excludes. And the target itself can reject the session mid-load, triggering retries that hammer a warehouse already under nightly contention. To test: run the full nightly with a forced timeout after chunk 3 and assert OPP-884213 is either fully absent or fully present, never half-written, and that the orchestrator surfaces a loud failure rather than a partial success; re-run that same batch and assert exactly one OPP-884213 row exists with `amount_usd 47500.00`, proving idempotency; feed two versions of OPP-884213 with `source_last_modified_utc` of 23:41:07Z and 21:10:00Z in reverse order and assert the final warehouse row is `CLSD_WON`; inject a null `status_code` for OPP-884213 and assert the load rejects it to a quarantine table instead of inserting; expire the warehouse session mid-chunk-4 and assert retries are bounded rather than storming; and finally, after a clean load, assert OPP-884213 is queryable in the fact table *and* separately assert the dashboard reflects it only after the BI refresh completes, with the gap measured against the reporting SLA.

OPP-884213 arrives at the warehouse as part of chunk 4 of 9 and is written not straight to the fact table but into the load's staging area first — the shaped row lands with `opportunity_id "OPP-884213"`, `account_key` for Halvorsen Logistics, `status_code "CLSD_WON"`, `amount_usd 47500.00`, `closed_date 2024-03-14`, and `source_last_modified_utc 2024-03-14T23:41:07Z` as the ordering key. The warehouse accepts the insert and returns success to the orchestrator, which marks chunk 4 committed and moves on. But acceptance into staging and promotion into the queryable fact table are not the same event, and neither is the BI refresh that reads the fact table afterward. So there are three distinct states OPP-884213 can be in on any given morning: staged but not promoted, promoted but not refreshed into the dashboard, or genuinely visible to the person asking why the Closed Won total looks light. The orchestrator's log only proves the first.

The failures cluster around that gap. OPP-884213 can sit accepted-but-unpromoted forever if the promotion step silently no-ops, and because the orchestrator already logged chunk 4 as committed, nothing retries it — the row exists in the warehouse and does not exist in the report. Target-side defaults are the quieter version: if the fact table declares `status_code NOT NULL DEFAULT 'OPEN'` or `amount_usd DEFAULT 0.00`, a column-mapping slip that omits either field means the load succeeds with a row that reads `OPEN` / `0.00` for Halvorsen Logistics — no error anywhere, just a $47,500 hole in the total. And upstream redelivery of chunk 4 after a retry can leave two staged OPP-884213 records with identical `source_last_modified_utc`, both promoted, aggregating to 95000.00. To test: load OPP-884213, then assert against the fact table directly rather than the orchestrator's success log, checking that no row remains in staging with `opportunity_id "OPP-884213"` after promotion completes; assert the promoted row's `status_code` is literally `CLSD_WON` and `amount_usd` is `47500.00` rather than the column defaults, which catches a mapping omission that a null-check would not; redeliver chunk 4 verbatim and assert exactly one OPP-884213 row survives promotion; and finally query the dashboard after the BI refresh and assert the Closed Won total moved by exactly 47500.00, with the elapsed time from load completion to dashboard visibility measured against the reporting SLA.

Once the promotion step finishes and OPP-884213 is a real row in `fact_opportunity`, the BI semantic layer picks it up on its own schedule — it doesn't read the fact table live, it reads it through a modeled view that maps the physical columns onto business names the dashboard understands: `amount_usd` becomes the measure `Closed Won Amount`, `status_code` becomes a filter dimension `Opportunity Status`, `closed_date` becomes the date grain the Q1 total rolls up on, and `account_key` joins out to `dim_account` for the Halvorsen Logistics label. The refresh runs, the cube or extract rebuilds, and only then does the $47,500 appear in the Closed Won tile. This is a separate delivery from the warehouse load — the fact table can be perfectly correct while the dashboard is still showing yesterday's number, and the load's success log says nothing at all about it.

Two things bite here. A silent schema mismatch: if the semantic layer's model still expects `status` and the fact table now exposes `status_code`, the modeled dimension resolves to nothing and OPP-884213 falls out of the `Opportunity Status = Closed Won` filter entirely — the row is in the warehouse, correct, and simply absent from the tile with no error surfaced, the exact same $47,500 hole as the earlier default-value case but produced one layer further down. And aggregation double-counting on replay: if the extract is rebuilt incrementally and chunk 4's promotion is re-run, the semantic layer can append OPP-884213's 47500.00 on top of a copy it already holds, showing 95000.00 for Halvorsen Logistics even though `fact_opportunity` contains exactly one row. To test, query the semantic layer's own output — not the fact table — filtered to `Opportunity Status = Closed Won` and Halvorsen Logistics, and assert exactly one OPP-884213 contribution of 47500.00; then rename or add a column in `fact_opportunity` in a staging environment and assert the model refresh fails loudly rather than quietly dropping OPP-884213 from the filter; then force a second refresh over the same promoted row and assert the Closed Won total is still 47500.00 higher than baseline, not 95000.00; and measure the wall-clock gap between promotion completing and OPP-884213 appearing in the dashboard against the reporting SLA, since that window is the one nobody is currently watching.