ETL / ELT
Intermediate 13 min read ETL / ELT

Ingestion Patterns

Match the extractor to the source's true rhythm

Ingestion is the shape of the source: pull (batch query), push (webhook), stream (CDC/events), or file drop. Each carries a cadence, a watermark story, and a failure mode; batch-vs-streaming is a spectrum, not a religion.

ingestionpullpushfile dropwebhookcadence
Prerequisites: ETL / ELT Pipeline Design
Before we start

What you will learn

  • Classify ingestion by pull/push/stream/file drop
  • Define watermarks and offsets per pattern
  • Pick batching and retry for the source's semantics
Introduction

What is this?

Ingestion patterns say HOW and HOW OFTEN you get data out of a source: polling (pull with a watermark), push (webhooks/deliveries), streaming (CDC/events on Kafka), or file drops (partitioned exports). Matching cadence and durability to the source's own behavior is most of the ingestion job.

Motivation

Why do you need it?

  • The wrong pattern (polling a stream, shipping events as CSVs) causes 90% of 'missing data' incidents.
  • Every source has a checkpoint story: watermark, offset, or folder partition — that's where correctness lives.
  • Ingestion is where latency, cost, and reliability are first negotiated.
Think about it

Mental model

Imagine

Checking the mailbox (pull) vs the postman ringing (push)

Think of

Poll vs webhook

You check the mailbox on a schedule and might miss if the mail arrived just after you left (watermark gap); the postman rings the moment it lands (push latency), but you must be home to sign for the package (reliability/retries).

Mechanics

How it works

  • Pull/poll: query the source by a watermark (updated_at/ID) on a schedule — simple, but lags max(cadence).
  • Push/webhook: source delivers changed records to an endpoint — low latency, needs idempotent handlers + retries.
  • File drop: source writes partitioned files to object storage; a sensor triggers downstream — natural for lakes.
  • Streaming (CDC/event): log-based capture emits committed changes — lowest latency, offsets for exactly ordering.
  • Deduplication layer: regardless of pattern, make downstream idempotent against duplicates.

Four ingestion shapes with their checkpoint.

Pull/poll
Push/webhook
File drop
Stream/CDC
Downstream
Hands-on

Examples

Watermark pull code shape

Watermark pull code shape
-- Pull ingestion (batch): each run fetches rows after the last watermark,
-- then advances it only after the batch is fully merged.
select * from source.orders
where updated_at > cast(:last_watermark as timestamptz)
order by updated_at;
 
-- Merge idempotently:
delete from typed.orders where order_id in (select order_id from :this_batch);
insert into typed.orders select * from :this_batch;
-- watermark = max(updated_at) in :this_batch

Pull + watermark + idempotent merge is the universal batch pattern; advances only after success.

Real world

In production

  • An ERN system pulls updated rows every 15 min by an updated_at watermark with a 1-day lookback.
  • Payment events push webhooks to a collector that persists raw and merges idempotently.
  • An analytics lake consumes partition-level file drops from a nightly ETL export.
Guidelines

Best practices

  • Advance watermarks/offsets only after the batch is safe (idempotent + durable).
  • Add a lookback window to pull patterns for late-arriving rows.
  • Make every handler idempotent: webhooks and streams deliver duplicates by design.
  • Match cadence to freshness SLO, not to 'what will the DB handle'.
Watch out

Common mistakes

  • Polling a push-capable source on a 5-minute cron and accepting stale dashboards.
  • Trusting folders' 'date parts' as complete before a close-out marker exists.
  • Webhook handlers that lack retries/queues, so a source re-delivery drops into a black hole.
When things break

Debugging tips

  • Row counts flat despite new source rows = watermark not advancing or lookback too small.
  • Duplicated events in the lake usually trace to a bad merge, not the source — check idempotency.
  • A file-drop job 'missing a day' is almost always a missing close-out marker, not an upload failure.
Interviews

Interview checkpoint

Practice

Hands-on lab

Full vs Incremental Pull Ingestion
30 min · intermediate
In progress
Objectives
  • Simulate a source table shared with your pipeline
  • Compare full-load vs watermark incremental row movement
  • Prove the incremental path only touches changed rows

Full vs Incremental Pull Ingestion

30 min · intermediate

0/5 steps
0%
sandbox · Linux sandbox
$
Try these commands
Test yourself

Quick quiz

0/2
1

The universal checkpoint for a pull/poll ingestion is the…

2

Webhook-style ingestion still needs idempotent merging because the source may deliver duplicates.

Go deeper

Official documentation

Fivetran connectors docs
docs
Kafka connect
docs