Ingestion Patterns: Beginner

A full load reads every row and replaces the destination table. For a product_categories table of 3,000 rows this is the right answer: the query takes under a second, there is no marker to maintain, and a row deleted at the source vanishes on the next run. The job empties raw.product_categories, then runs the insert below.

An orders table with 400 million rows cannot be reread nightly. An incremental load asks only for rows changed since the last run. It needs a column that moves whenever a row changes, almost always updated_at, and a stored high-water mark: the largest updated_at the previous run loaded, kept in a small state table next to the raw data.

The order of the last 2 lines is the whole design. If the mark moved first and the write failed, the next run would start above rows that never landed. With the write first, a failure leaves the mark in place, the retry re-pulls the same window, and write_replace_by_key makes the second landing identical to the first. The refunds row above shows this: its 10-22 run failed, so its mark stayed at 10-21 and tonight's run covers 2 days.

The first is the slow transaction. An application opens a transaction at 02:59:40, sets updated_at to that moment, and commits at 03:00:20. Your run's high bound was 03:00:00 and the row was not yet committed, so the query missed it. Tomorrow's run starts above 03:00:00, and the row's 02:59:40 sits below that, so it is skipped forever. The fix is an overlap: pull from the mark minus 10 minutes, and let the replace-by-key write absorb the rows that arrive twice.

The second is the hard delete. A removed row has no updated_at left to move, so no incremental query sees it and the destination keeps a ghost. Until the source flags deletions instead of removing rows, the usual answer is a weekly pull of the primary keys alone, compared against the destination to remove the ghosts.

Replay is the one you can live with. The consumer writes each event under its event_id with a replace, so a replayed event overwrites itself and the table ends up identical. Loss leaves a hole only the source can fill, and a source that pushed an event once may have no way to send it again.

A webhook is a push over plain HTTP. The vendor sends a POST to a URL you own each time something happens, waits a few seconds for a 200, and retries if it does not get one. The retry schedule is the vendor's: some give up after a few attempts over minutes, others keep trying for days. Either way the vendor keeps no offset for you, so you cannot ask it to resend from a position. Design as if events can be lost, and keep a backfill path such as the vendor's API.

Uploading a 2GB file takes minutes. A job that lists the folder at 02:03 and finds settlements_2026-10-22.csv cannot tell whether the upload finished or whether the file holds the first 1.3GB of 2. Loading it produces a row count that passes any loose check and a daily total short by a third.

Two conventions solve this; ask the partner for at least 1. With write-then-rename, the partner uploads to settlements_2026-10-22.csv.tmp and renames it when the upload ends, and your job ignores anything ending in .tmp. With a sentinel, an empty file named _SUCCESS by convention is written only after every data file in the batch has landed; if it is missing, your job exits and tries again on its next schedule.

A missing file, a checksum that differs from the manifest, or a row count that differs from it raises and blocks the load: the manifest and the data disagree, and loading either version in silence would be wrong. Replacing the whole batch_date partition makes a rerun safe. Recording the checksums means a partner who re-uploads yesterday's batch, sentinel and all, triggers a run that reads the manifest and stops.

The header check is the schema drift lesson applied to files. A CSV carries its column names in line 1. A partner who adds currency at the end has made a backward-compatible change you accept with a warning; one who renames amount to amount_cents has broken the contract, and you stop and talk before loading a row of the new shape.

A vendor with 23,000 campaigns returns 100 per request, newest first. Offset pagination asks for offset=0, then offset=100, and so on for 230 requests. A campaign created while you are on page 40 lands on page 1 and pushes every later record down 1 position, so the last record of page 40 becomes the first of page 41 and you load it twice. A deletion above your page shifts records up and you skip 1. With an oldest-first sort, a new campaign lands at the end and repeats nothing.

Vendors commonly cap requests, say 600 per minute per account. The 601st gets status 429 and often a Retry-After header saying how many seconds to wait. A 429 is a transient failure: retry with exponential backoff, and when the header is present use its number instead of your own. Hammering a vendor that has told you to wait can get API keys suspended.

Four things keep the loop honest. The 429 branch sleeps for the vendor's number when present and doubles its own otherwise, capped at 60 seconds. The ok check reads the body, because some vendors return 200 with an error inside. The cursor is written only after the page's rows land, so a crash resumes on the failed page. On the last page the stored cursor is cleared, because it exists to resume one crawl: left in place, it would start the next hourly run at the final page, and the other 22,900 campaigns would never be re-read.