Foundation03 Ingestion · 1 of 3
One decision table
There are four source shapes. The shape is a property of the source, not a choice you get to make.
Stand 29.07.2026
#Pick by source shape, not by source system
WhatSource shape → pattern → what it costs to get wrong
The question that selects the pattern is not which system is this. It is can I tell what changed, and can I tell what was deleted? Connector, format and vendor all follow from those two answers.
| Source shape | Pattern | Lands in | Cost of the wrong choice |
|---|---|---|---|
| Files in a Volume, schema drifts | Auto Loader streaming table, schema location per stream | bronze.shop_order | a folder listing that double-counts any file landing twice |
| Change feed with a sequence (CDC) | AUTO CDC flow, sequence_by the change counter | bronze.sap_vbak, bronze.sap_vbap | a MERGE keyed on a timestamp: same-second changes apply in arbitrary order |
| Full extract, no change timestamp | append every extract, diff consecutive snapshots | bronze.sap_kna1 | no history at all, or every unchanged row versioned daily |
| Dated reference file, insert-only | Auto Loader, unique on (currency, rate_date) | bronze.fx_rate | one duplicated rate row fans out the join and doubles amount_eur |
| API with a paging cursor | land the raw response as files, then pattern one | a Volume, then bronze | a parse bug you cannot re-run — the API will not return that page again |
Why
The failure this prevents is pattern proliferation: six sources, six notebooks, each with its own idea of restart, deduplication and lateness. Nothing is broken and nothing is reviewable — the author of source three is the only person who can fix source three.
The subtler failure is choosing a pattern one shape too weak. The webshop feed starts as just read the folder each night, which holds until a file lands late, lands twice, or a run dies half-written. Each is a silent wrong number, and the fix is a backfill rather than an option change.
Doing it
- Answer both questions in the ticket first: can I tell what changed, can I tell what was deleted
- Land raw before parsing for anything you cannot re-request
- Give each stream its own schema location and checkpoint; never share a path
- Add the three technical columns in the framework, so a new source cannot forget them
Defaults
- Four patterns, each selected by an observable property of the source
- Raw lands before it is parsed; parsing belongs downstream of a durable copy
- Bronze is append-only and carries the three technical columns
- One stream, one schema location, one checkpoint
Gotchas
- Choosing CDC because the source system offers CDC, when what arrives is a nightly full dumpsequence_by is then identical for every row in the file, and last-writer-wins picks an arbitrary version of each customer.
- Reading a Volume with spark.read 'because there are only a few files'It works for a year; then the folder holds 400,000 files, the nightly listing takes six hours, and you migrate under pressure.
- Parsing during ingestionA field the shop team changed on Tuesday is unrecoverable for Monday, because bronze holds your interpretation of the file rather than the file.
- Assuming a full extract lets you skip historyIt is the only shape that reveals deletions — and the only one where a truncated file is indistinguishable from a mass delete.