AdvancedPipeline Architecture · 38 min · 11 challenges

Ingestion Patterns: Advanced

Noon on Black Friday at Shopify: a celebrity sneaker drop opens and orders pour into a shared database. The event pipeline must copy those orders out without slowing checkout, hand every order to shipping even if the broker blinks, swallow a surge it can't stop, and keep every other merchant's data flowing while a single storefront eats the capacity.

Reading Live Databases Safely

Daily Life
Interviews
Every query an extract sends to a production database competes with the application the database serves. Checkout wants a few milliseconds per query and a cache full of hot rows, while a bulk extract wants every page of a big table once. Left alone, the extract wins because it asks for more, so a safe reader is built to lose.

Four ways a reader hurts its source

MechanismWhat the extract doesWhat the application feels
Held snapshotReads a 400M-row table inside 1 transaction for 6 hoursOld row versions pile up, so every query reads more pages
Cache evictionPulls the cold pages of a full scan into memoryHot rows get pushed out and are read from disk again
Disk and CPU contentionReads sequentially at full speedEvery query sharing those disks waits longer
Locks and connectionsTakes a table lock to dump, or opens 32 parallel connectionsWrites queue behind the lock, or the app's connection pool runs dry
Databases that keep multiple row versions leave each updated row's old version in place until no open transaction could still read it, so a 6-hour transaction makes every busy table carry 6 hours of dead versions. Live queries then scan more pages for the same rows, and latency climbs while CPU graphs look normal.

Read from a replica, in short transactions

A read replica takes cache and CPU contention off the primary, making it the first move for any large extract. But a replica constantly replays the primary's changes, and a long query can hold rows a replayed change must remove, so the replica cancels the query or pauses replay and falls behind. Reporting the replica's open snapshots to the primary stops the cancellations by moving the old-version pileup back onto the primary. Either way, the extract must run as many short transactions.
//

Keyset chunks instead of OFFSET

Paging with OFFSET makes the database read and discard every row before the page it returns, so each page costs more than the last. Keyset paging starts each chunk after the last key it read: every chunk is an index range read of 10,000 rows in its own short transaction, and the reader can stop or resume between chunks. On a 400M-row orders table read 10,000 rows at a time, the totals compare like this:

8 trillion
rows touched by OFFSET paging
20,000x
OFFSET's work relative to keyset
SELECT
order_id,
customer_id,
status,
total_cents,
updated_at
FROM orders
WHERE order_id > 184020000
ORDER BY order_id
LIMIT 10000

Throttle on the application's own signals

A fixed pause between chunks is a guess that fails on every busy day. One workable rule instead: start at 5 chunks per second; when replica lag passes 10 seconds or checkout's p99 latency passes 200 ms, halve the rate; after a full minute with both healthy, add 1 chunk per second. Then add limits the throttle can never raise, such as 2 connections and a 30-second statement timeout, so a bad query plan dies in seconds.

A consistent snapshot without a table lock

Starting log-based CDC on an existing table needs a full copy plus every change after it. The old recipe locks the table, records the log position and dumps the rows, blocking writes until the dump finishes. The watermark recipe keeps the log reader running and copies in keyset chunks, bracketing each chunk with 2 marker rows written to a small dedicated table: a low watermark before the SELECT, a high watermark after it. These are log positions, unrelated to event-time watermarks. Any key the log shows changing between the 2 markers is taken from the log and dropped from the chunk, since the log version is at least as new.
StepReader actionEmitted downstream
1Insert low watermark LNothing
2SELECT order_id 5001 to 5003; all 3 read as placedNothing yet
3Insert high watermark HNothing
4Read the log from L to H: order 5002 changed to shipped5002 shipped, from the log
5Reach H in the log and drop 5002 from the chunk5001 placed, 5003 placed
Multiple Choice

A nightly extract on a replica pages through orders with OFFSET in 1 transaction lasting 5 hours. The replica kept canceling it, so the team turned on snapshot reporting to the primary. It now finishes, but checkout latency on the primary creeps up every night. What fixes the cause?

The Transactional Outbox Pattern

Daily Life
Interviews
When an order commits, checkout has 2 jobs: save the order and tell shipping. The database and the broker are separate systems, and no transaction spans both. If checkout commits first and then publishes, a crash or broker outage between the 2 steps loses the event. Publishing first fails the other way: a rollback leaves shipping with an event for an order that doesn't exist.

The transactional outbox moves the second write into the first system. Checkout inserts the event as a row in an outbox table inside the order's own transaction, so both rows commit or neither does. A separate publisher reads committed outbox rows and sends them to the broker, retrying until it accepts. The animation runs 3 orders through both designs during a 2-second broker outage.

checkout writes the order to the database, then publishes the event to the broker0 s
service
checkout
WRITESdb, then broker
Postgres
orders
Kafka
order_events
shipping
shipping
DEDUPE ONid
shipping db
shipments
orders0 rows
order_id
status
records0 records
#
id
order_id
shipments0 rows
order_id
Three orders and a broker blip
1/14
In the dual-write variant, the order at second 3 arrives while the broker is down: its row commits but its publish fails, so shipping never hears of it. In the outbox variant, rows written during the outage wait in the table. Then the publisher crashes after sending 103 and before marking it, so its next run sends 103 again, and shipping dedupes on the outbox id to create 1 shipment. The outbox turns maybe-never into at-least-once; consumer dedupe makes it effectively-once.

Designing the outbox table

outbox
idPKBIGINT
aggregate_typeVARCHAR
aggregate_idVARCHAR
event_typeVARCHAR
schema_versionINTEGER
payloadJSON
created_atTIMESTAMP
published_atTIMESTAMP
ColumnWhy it is there
idThe dedupe key consumers store, and the polling publisher's read order. Never reused.
aggregate_idThe broker partition key, so each order's events land in 1 partition, in order.
event_type, schema_versionConsumers route and parse by declared type and version; versions change by expand-contract.
payloadThe event as its data contract defines it, built inside the transaction. Internal columns stay out.
published_atPolling publishers only: NULL until the broker acknowledges the row.

Polling publisher or CDC publisher

✓Polling publisher
  • Queries the outbox every few seconds; delay is about the poll interval
  • Adds query load on the primary at every poll, even an empty one
  • Writes published_at per acknowledgement: 1 extra write per event
  • Needs only SQL access
✗CDC publisher
  • Reads outbox inserts from the log; delay under a second
  • Adds no query load: the log is written anyway
  • Lets outbox rows be deleted soon after insert
  • Needs a running log reader whose retention and failover you own
Multiple Choice

At peak, a payments service writes 3,000 outbox rows per second on a primary near 70% CPU. Fraud scoring needs each event within 1 second, and log-based CDC already runs for 2 other services. Which publisher fits?

//

The gap a bookmark poller falls into

Sequence ids are assigned at insert, but rows become visible at commit. Transaction A takes id 207, transaction B takes 208, and B commits first. A poller that tracks id > last_id sends 208, moves its bookmark to 208, and never sees 207 when A commits a moment later. Polling on published_at IS NULL keeps a late-committing row visible until it is sent:

SELECT
id,
aggregate_id,
event_type,
schema_version,
payload
FROM outbox
WHERE published_at IS NULL
ORDER BY id
LIMIT 500
TIP
Per-order ordering survives late commits: an order's second event comes from a transaction that updates the same order row, so it waits for the first to commit and takes a higher id.
Multiple Choice

This poller runs every 2 seconds, then stores the highest id it sent as the next bookmark. Every order has an outbox row, yet a few orders a month never reach consumers. Why?

SELECT
id,
aggregate_id,
payload
FROM outbox
WHERE id > 48211
ORDER BY id
LIMIT 500

Backpressure at the Ingestion Layer

Daily Life
Interviews
Backpressure travels upstream from a slow stage to a faster one until it reaches the pipeline's edge. Ingestion is that edge, and the next hop upstream is a system you do not run. When arrivals outpace what ingestion can drain, you have 3 possible responses. Which of them are open to you depends on the source.
  • BufferAbsorb now, drain laterHold the surge in durable storage. Costs freshness and disk, and fails when the surge outlasts the buffer.
  • Signal the producerAsk it to slow downReturn 429 with Retry-After. For pull-based sources, stop pulling; for credit-based ones, stop granting credits. Works only if the producer holds data while it waits.
  • Drop selectivelyDiscard by ruleSample, shed low-priority event types, or keep only the latest value per key. Works only where consumers agreed to the loss.

Classify the source before choosing

SourceCan it wait?Can it replay?Response
Partner order webhooksYes, retries for a limited windowWithin that windowBuffer; return 429 near full
Mobile clickstreamNo, the app discards what it can't sendNoBuffer, then sample under pressure
Internal payment eventsYes, rows wait in its outboxYes, from the outboxSignal; the outbox is the buffer
Vehicle location pingsNoNoKeep the latest per vehicle, drop the rest
Log-based CDCYes, but the database holds its log meanwhileWithin log retentionDrain fast into your own log; slow down after it

Sizing the buffer you depend on

A worked surge: traffic normally runs at 2,000 events per second and workers drain 3,000. A promotion pushes arrivals to 12,000 per second for 15 minutes, with events averaging 1 KB. During the surge the backlog grows by the gap between arrivals and drain; afterwards it shrinks only by the spare drain capacity.
8.1 GB
backlog when the surge ends
45 min
peak freshness lag
135 min
time to drain the backlog
Buffer capacity must cover the longest planned surge, with margin. The peak lag must fit the freshness SLA, and if it does not, a bigger buffer cannot help: the fix is more drain capacity or a drop rule for part of the stream. Recovery time depends on spare capacity, so doubling the workers' headroom halves it.

When backpressure reaches the database

Slowing a CDC reader looks harmless because the source can wait, but the database waits by keeping every log segment the reader hasn't confirmed. A reader stalled for 6 hours on a primary writing 20 GB of log an hour pins 120 GB of disk, and a full disk stops the primary taking writes: ingestion has taken down checkout. So the CDC reader never absorbs backpressure: it copies changes into a durable log you own as fast as the database produces them, and every slowdown happens after that log. Track the log bytes retained for the reader, with a ceiling past which you drop it and re-snapshot in chunks.
Sources
Edge
Buffer
Drain
HTTPS
partner webhooks
app SDK
mobile clickstream
log reader
orders CDC
ingest API
admission
ERROR429 above 95% fill
broker
durable log
PARTITIONS48
workers
ingest workers
PARALLELISM3,000 events/s
lakehouse
raw tables
SLA1 hour

Webhooks and clickstream pass the admission rule. The CDC reader writes straight to the durable log, so the database never waits on a slow drain.

//

An admission rule that uses all 3

This handler fronts a shared ingestion endpoint. Below 70% fill it accepts everything. Between 70% and 90%, loss-tolerant events are sampled down to 1 in 10, and above 90% they are all dropped. Must-keep events are accepted until 95%, after which producers get 429 with a Retry-After header.
def admit(event, buffer, dropped):
fill = buffer.size() / buffer.capacity
if event.loss_tolerant:
sampled = event.sample_key % 10 == 0
if fill < 0.70 or (fill < 0.90 and sampled):
buffer.put(event)
else:
dropped[event.event_type] += 1
return 202, {}
if fill < 0.95:
buffer.put(event)
return 202, {}
return 429, {"Retry-After": "30"}
Dropped events still get 202, because a producer told to retry something you chose to discard only adds load. Every drop is counted by event type, since an uncounted drop shows up as a fake traffic decline.
Multiple Choice

A fleet of 50,000 scooters sends GPS positions every 2 seconds with no retries. The only consumer is a live map of each scooter's current position. The shared buffer nears full during a surge. Which response fits?

Multi-Tenant Ingestion Fairness

Daily Life
Interviews
A multi-tenant ingestion layer runs many customers' data through the same workers and queues and writes it to shared sinks. Sharing keeps hardware busy and cheap, and lets the largest tenant's worst day land on every tenant. Fairness means each tenant's freshness depends on its own load and plan, whatever its neighbors are doing.

One resync, 300 tenants waiting

A shared queue serves 301 tenants. At 09:00, tenant acme requests a full resync, and 50M rows enter the queue. The other 300 tenants each add 2,000 rows every 15 minutes, and the workers process 200,000 rows per minute.
SchedulerA small tenant's 09:05 batch waitsacme's resync finishes after
First in, first out250 minutes or moreAbout 250 minutes
Fair across tenants3 minutes at mostAbout 313 minutes
Fairness costs acme about an hour and saves each small tenant more than 4 hours. The small tenants' rows use 3 minutes of every 15, and acme gets the rest, because a fair scheduler is work-conserving: capacity a tenant leaves unused goes to whoever has work.

Deficit round robin across tenant queues

Fair scheduling needs a queue per tenant and a scheduler that hands out capacity in rounds. In deficit round robin, each tenant with work earns a quantum of rows per round, scaled by its weight, and spends that credit on its queued batches:
def schedule_round(queues, weights, deficit, quantum=2000):
for tenant, queue in queues.items():
if not queue:
deficit[tenant] = 0
continue
earned = quantum * weights.get(tenant, 1)
deficit[tenant] = deficit.get(tenant, 0) + earned
while queue and queue[0].rows <= deficit[tenant]:
batch = queue.popleft()
deficit[tenant] -= batch.rows
yield tenant, batch

Weights express plan tiers: a weight-4 tenant gets 4 times a weight-1 tenant's share while both have work. Resetting deficit for empty queues stops an idle tenant from banking credit and bursting later. A batch larger than its credit waits extra rounds, so large loads should arrive as keyset chunks.

When sharing itself is the risk

A scheduler divides worker time and nothing else: it cannot stop an oversized file from crashing a shared worker, or a single tenant from filling the buffer. Each stronger level of isolation costs more capacity:
LevelHow it worksProtects againstCost
Fair schedulingPer-tenant queues sharing a worker pool by weightQueue starvationLowest; a bad payload can still crash shared workers
Per-tenant quotasA token bucket per tenant on rows or bytes per second at admissionA single tenant flooding the bufferTenants over quota wait or get 429
BulkheadsSeparate worker pools per tier or for the largest tenantsCrashes and memory blowups spreadingIdle capacity in every pool
Dedicated cellA full ingestion stack for a single tenantEvery shared resourceHighest; for tenants whose load would dominate any pool

Per-tenant observability

Averages hide the tenant in trouble. With 300 tenants at 2 minutes of lag and a single tenant at 6 hours, mean lag reads around 3.2 minutes against a 15-minute SLA, and the dashboard stays green. Track freshness and dead-letter rate per tenant. Compare each tenant's volume against its own history and alert on its schema changes, then report the worst tenant and the count past SLA:
SELECT
tenant_id,
MAX(lag_seconds) AS worst_lag_seconds,
SUM(rows_ingested) AS rows_today,
SUM(rows_dead_lettered) AS rows_dead_lettered
FROM ingest_batches
WHERE batch_date = DATE '2026-10-24'
GROUP BY tenant_id
HAVING MAX(lag_seconds) > 900
ORDER BY worst_lag_seconds DESC

Carry tenant_id in every record's structure

Ingestion stamps tenant_id at admission and rejects records without it. From there it is a required envelope field, part of every table's primary key and a partition column in storage. Recovering the tenant later by joining to an accounts table fails when it matters most: when 2 tenants each have an order 1001, when an account moves between tenants, or when a departing tenant's rows must all be found and deleted.

orders
tenant_idPKVARCHAR
order_idPKBIGINT
statusVARCHAR
created_atTIMESTAMP
order_items
tenant_idPKVARCHAR
order_idPKBIGINT
line_noPKINTEGER
skuVARCHAR
quantityINTEGER
TIP
Partition the broker by tenant_id alone and the largest tenant lands in 1 hot partition. Key large tenants by tenant_id plus entity id instead: per-entity order holds, load spreads across partitions, and tenant_id still travels in every record.
Multiple Choice

Tenant boreal uploads 40 GB JSON files that exhaust worker memory. Each crash fails every batch on that worker, whichever tenants they belong to. Fair scheduling is already in place. What's the smallest change that protects the other tenants?

Dual-Write to Outbox + CDC

Daily Life
Interviews
A marketplace's checkout service publishes order_placed to the broker inside its database transaction before commit. It logs and ignores publish errors so checkout never fails on them. Its 6 consumers are shipping, invoicing, search, fraud and email, plus warehouse ingestion. A 30-day reconciliation against 9.2M orders found both failure kinds from the outbox section:
4,140
orders that never got an event
610
events for orders that rolled back
The missing events cluster around broker restarts, when the publish failed and the commit went ahead. The phantom events come from constraint violations at commit, after the publish had gone out. Both counts form the baseline, and the new path's target is zero.

Measure before you move

The reconciliation is a left join from orders to events, counted per hour so failures line up with incidents. count(DISTINCT ...) keeps duplicate deliveries from hiding a gap. The mirror query, from events to orders, counts phantoms.

SELECT
DATE_TRUNC('hour', o.created_at) AS order_hour,
COUNT(DISTINCT o.order_id) AS orders,
COUNT(DISTINCT e.order_id) AS orders_with_event
FROM orders AS o
LEFT JOIN order_placed_events AS e
ON e.order_id = o.order_id
WHERE o.created_at >= TIMESTAMP '2026-09-01 00:00:00'
GROUP BY DATE_TRUNC('hour', o.created_at)
ORDER BY order_hour

The migration in 5 phases

PhaseChangeExit check
1. ExpandAdd the outbox: checkout writes an order_placed row in the order's transaction, still publishing directlyEvery new order has exactly 1 outbox row
2. ShadowA CDC publisher fills a new topic, order_events_v2, that no consumer reads yetReconciliation on v2 shows zero missing and zero phantom for 7 days
3. Cut overMove consumers to v2 in turn: search, warehouse, fraud, email, invoicing, shippingEach consumer's output matches its v1 output across the overlap
4. ContractDelete the direct publish from checkoutv1 receives no new events
5. Clean upRetire the v1 topic once its retention passes; keep outbox rows 7 daysNothing reads v1
Phases 1 and 2 change nothing consumers see, so the new path is proven before anyone depends on it. Consumers then move from cheapest mistake to costliest: a wrong search index rebuilds in minutes, while a duplicate shipment costs money.

The overlap at cutover

Events on v1 carry no outbox id, so a consumer switching topics can't dedupe across the 2 streams by id. It switches on a business key instead: it notes the time of its last v1 event, reads v2 from 10 minutes before that, and upserts on (order_id, event_type), unique because an order is placed once. Duplicates in the overlap collapse, and any event v1 lost in those 10 minutes arrives from v2.

Multiple Choice

Shipping switches to v2. It starts reading v2 from 10 minutes before its last v1 event and dedupes on outbox id. What goes wrong?

service
checkout
operational database
orders + outbox
CDC publisher
log reader
MONITORretained log > 50 GB
broker
order events v2
PARTITIONS24
service
shipping
RETRY5ERRORdead-letter after 5 triesIDEMPOTENCYdedupe on outbox id
service
invoicing
IDEMPOTENCYdedupe on outbox id
batch loader
warehouse ingest
SLA15 minutes

Target state, 3 of the 6 consumers shown: a single transaction writes the order and its outbox row, the log reader publishes outbox inserts keyed by order_id, and consumers dedupe on outbox id.

What the new path costs to run

Costs to plan for
  • ▸Log retention: a stalled reader pins log on the primary, so set a retained-bytes ceiling. A reader that crosses it gets dropped and re-snapshotted.
  • ▸Outbox growth: around 9.2M rows a month, deleted after 7 days in off-peak keyset chunks.
  • ▸Failover: a new primary may lack the reader's log position. Replay outbox rows by id range within the 7-day retention.
  • ▸Payload ownership: checkout builds the event, so its schema falls under the data contract.
  • ▸Duplicates: delivery is at least once, so every consumer stores processed outbox ids and skips repeats.
Multiple Choice

Phase 2 has run 7 days. Reconciliation on v2 shows 12 orders missing, all committed within 2 minutes before a database failover. What should the team do?

PUTTING IT ALL TOGETHER

> Back at Shopify at noon on Black Friday, the sneaker drop tests every part of this lesson at once. The CDC reader that seeded the orders stream weeks earlier did it in watermark chunks on a replica, so the sale starts with a complete stream and no bloated tables. Each order commits with its outbox row, so a broker restart mid-drop costs a few seconds of delay instead of lost shipments. The surge fills the ingestion buffer, clickstream gets sampled, order events keep their reserved headroom, and the CDC reader keeps draining the log so the shared database never fills its disk. Deficit round robin keeps every other merchant's orders minutes fresh while the hot merchant's backlog drains, and the worst-tenant lag query is the number the on-duty engineer watches.

Keyset chunking appears 3 times: in the safe extract, in the watermark snapshot, and in deleting old outbox rows, because every bulk operation on a live database has to be small transactions.
The outbox is both a delivery mechanism and a backpressure buffer: payment events can be signaled to wait because their rows sit safely in the outbox.
A stalled CDC reader turns ingestion backpressure into a disk problem on the primary, which is the same harm to the application that the first section guards against.
Dedupe on a stable key is what makes at-least-once safe, from the outbox id at shipping to the (order_id, event_type) key at cutover.
tenant_id in the outbox and in the partition key is what lets fair scheduling and per-tenant freshness work downstream without lookups.
The dual-write migration is expand-contract applied to delivery: the outbox and v2 are added alongside the old path, proven by reconciliation, then the old path is removed.
KEY TAKEAWAYS
Read production databases in short keyset chunks from a replica, throttled on replica lag and application latency, so no snapshot stays open for hours.
Seed log-based CDC with watermark-bracketed chunks: changes the log shows between the markers override the chunk's copy, and no table lock is needed.
The transactional outbox commits the event with the business change; the publisher delivers at least once, and consumers dedupe on the outbox id.
Poll the outbox on published_at IS NULL, never on an id bookmark, and choose a CDC publisher when query load or delay rules polling out.
Match backpressure to the source: buffer what can replay, signal what can wait, drop only what consumers agreed to lose, and never let a CDC reader stall.
Fairness in shared ingestion comes from per-tenant queues and weighted scheduling. For stronger isolation, add quotas and bulkheads or split tenants into cells.
Stamp tenant_id at admission, carry it in keys and partitions, and watch the worst tenant instead of the average.
Migrate a dual write by expanding into an outbox, shadowing on a new topic, cutting consumers over on a business key, then removing the direct publish.

Read live databases gently, publish events reliably, and keep a single tenant's surge from starving the rest.

Category
Pipeline Architecture
Difficulty
advanced
Duration
38 minutes
Challenges
11 hands-on challenges

Topics covered: Reading Live Databases Safely, The Transactional Outbox Pattern, Backpressure at the Ingestion Layer, Multi-Tenant Ingestion Fairness, Dual-Write to Outbox + CDC

Lesson Sections

  1. Reading Live Databases Safely

    Every query an extract sends to a production database competes with the application the database serves. Checkout wants a few milliseconds per query and a cache full of hot rows, while a bulk extract wants every page of a big table once. Left alone, the extract wins because it asks for more, so a safe reader is built to lose. Four ways a reader hurts its source Databases that keep multiple row versions leave each updated row's old version in place until no open transaction could still read it, s

  2. The Transactional Outbox Pattern

    When an order commits, checkout has 2 jobs: save the order and tell shipping. The database and the broker are separate systems, and no transaction spans both. If checkout commits first and then publishes, a crash or broker outage between the 2 steps loses the event. Publishing first fails the other way: a rollback leaves shipping with an event for an order that doesn't exist. In the dual-write variant, the order at second 3 arrives while the broker is down: its row commits but its publish fails,

  3. Backpressure at the Ingestion Layer

    Backpressure travels upstream from a slow stage to a faster one until it reaches the pipeline's edge. Ingestion is that edge, and the next hop upstream is a system you do not run. When arrivals outpace what ingestion can drain, you have 3 possible responses. Which of them are open to you depends on the source. Classify the source before choosing Sizing the buffer you depend on A worked surge: traffic normally runs at 2,000 events per second and workers drain 3,000. A promotion pushes arrivals to

  4. Multi-Tenant Ingestion Fairness

    A multi-tenant ingestion layer runs many customers' data through the same workers and queues and writes it to shared sinks. Sharing keeps hardware busy and cheap, and lets the largest tenant's worst day land on every tenant. Fairness means each tenant's freshness depends on its own load and plan, whatever its neighbors are doing. One resync, 300 tenants waiting A shared queue serves 301 tenants. At 09:00, tenant acme requests a full resync, and 50M rows enter the queue. The other 300 tenants eac

  5. Dual-Write to Outbox + CDC (concepts: Change Data Capture)

    A marketplace's checkout service publishes order_placed to the broker inside its database transaction before commit. It logs and ignores publish errors so checkout never fails on them. Its 6 consumers are shipping, invoicing, search, fraud and email, plus warehouse ingestion. A 30-day reconciliation against 9.2M orders found both failure kinds from the outbox section: The missing events cluster around broker restarts, when the publish failed and the commit went ahead. The phantom events come fro