Ingestion patterns#
Ingestion is getting data from source systems into the platform's raw layer, either by copying everything each time (a full load) or only what changed since the last run (an incremental load).
Before reading
Processing patterns: batch, micro-batch and streaming · Medallion layers: bronze, silver and gold
Why it matters#
Every table in the platform is only as complete and current as its ingestion. The choice of pattern decides how much load the source system takes, how fresh the data is, whether deletes are seen, and how hard it is to recover after a failure.
How it works#
A full load reads the whole source table or file and replaces the previous copy, or, where the raw layer is append-only, adds it as a new dated copy of which only the latest counts. It is simple and always correct, including for deleted rows, because whatever is not in the new copy no longer exists. It becomes expensive as tables grow, and it puts the most load on the source. It suits small reference tables, such as products or countries.
An incremental load reads only rows that changed. The usual method is a watermark: the pipeline stores the highest value of a change column it has seen, such as updated_at, and the next run asks for rows above it. A cursor is the same idea for APIs, where the source returns a token meaning "continue from here". Incremental loads are cheap, but they rely on the source. The change column must be updated on every change, including changes made by scripts and fixes, and it must be reliable enough to compare. Rows deleted in the source simply stop appearing, so an incremental query never sees deletes. A small overlap, re-reading the last few minutes on each run, protects against rows committed late with an older timestamp; the duplicates it creates are removed later.
flowchart TB
subgraph Full[Full load]
direction LR
S1[(products<br/>in source)] -->|read all rows| R1[(raw_products<br/>replaced)]
end
subgraph Inc[Incremental load]
direction LR
W[Stored watermark:<br/>updated_at 10:00] --> Q[Read rows with<br/>updated_at > 09:55]
S2[(suppliers<br/>in source)] --> Q
Q -->|append| R2[(raw_suppliers)]
Q -->|new watermark| W
end
Full ~~~ IncWhen deletes matter and the source has no reliable change column, the third option is log-based change data capture, which reads the database's own change log and has its own page.
Sources other than databases follow the same ideas. APIs are pulled page by page with a cursor or a time filter, within the rate limits the provider sets. Files, such as daily exports from a partner, arrive in a bucket or an SFTP folder; the pipeline tracks which files it has loaded, by name or by a manifest, so that each is loaded once.
Data can be pulled or pushed. In pull ingestion the platform connects to the source on a schedule, which suits databases and APIs. In push ingestion the source sends data as it happens, to an endpoint or an event stream such as Kafka, which suits applications and devices that produce events. Push gives fresher data and puts no query load on the source, but the platform must accept data whenever it arrives and handle bursts.
Whatever the pattern, data lands in the raw layer close to how it was delivered, with load metadata such as _source and _ingested_at. Transformation happens later, so a mistake in the logic can be fixed by rebuilding from raw instead of reloading from the source.
Key terms#
- Full load Copying the complete source dataset on every run.
- Incremental load Copying only what changed since the previous run.
- Watermark The stored position, such as the highest updated_at loaded, from which the next run continues (not the streaming watermark in Late and out-of-order data).
- Cursor A position token returned by an API to continue reading.
- Pull and push The platform fetches data, or the source sends it.
- Landing Writing data to the raw layer as delivered, with load metadata.
Example#
The shop loads products daily in full. Orders arrive through change data capture, not by query. The supplier database has no CDC and is loaded hourly by updated_at. Here source is the query engine's connector to each source database (see SQL query engines).
-- products: small, full copy appended; clean_products keeps
-- only the rows with the latest _ingested_at
INSERT INTO shop.raw_products
SELECT *, 'orders-db' AS _source, current_timestamp AS _ingested_at
FROM source.products;
-- suppliers: incremental, with a 5-minute overlap
INSERT INTO shop.raw_suppliers
SELECT *, 'supplier-db' AS _source, current_timestamp AS _ingested_at
FROM source.suppliers
WHERE updated_at > (SELECT last_value FROM shop.load_watermarks
WHERE table_name = 'suppliers') - INTERVAL '5' MINUTE;
-- after the insert succeeds, store max(updated_at) of this batch
-- in shop.load_watermarks, keeping the old value if the batch was empty
A product deleted in the source is missing from the next full copy of products. A supplier deleted in the source never shows up in the incremental suppliers load; if the shop needs supplier deletes, it must switch suppliers to change data capture, as it did for orders.
Pitfalls#
- Trusting updated_at blindly. Bulk fixes and triggers that skip it make rows invisible to incremental loads; compare row counts with the source regularly.
- Losing deletes. Incremental loads by timestamp never see them.
- Storing the watermark before the data is safely written. A failure in between skips data; store it in the same step or after the commit.
- Transforming on the way in. Business logic applied during ingestion cannot be rebuilt later without reloading from the source.
- Full loads that grow unnoticed. A table that was small a year ago can now take hours and strain the source.
Learn more#
- Joe Reis and Matt Housley, Fundamentals of Data Engineering (O'Reilly, 2022), chapter 7, ingestion.
- Martin Kleppmann, Designing Data-Intensive Applications (1st edition, 2017), chapter 11, change data capture.