Pull Ingestion from a Database

Pull ingestion is a query your job runs against a source database on a schedule. You decide when it runs and how much it asks for, which makes pull the most controllable shape, and asking for too much slows the application that shares the database.

Full load: ask for everything

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.

INSERT INTO raw.product_categories
SELECT
    category_id,
    category_name,
    parent_category_id,
    updated_at
FROM source.product_categories

Incremental load: ask for what changed

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.

SELECT
    order_id,
    customer_id,
    status,
    total_cents,
    updated_at
FROM source.orders
WHERE updated_at > TIMESTAMP '2026-10-22 03:00:00'
  AND updated_at <= TIMESTAMP '2026-10-23 03:00:00'
ORDER BY updated_at

The upper bound matters. Without it, the query captures rows written while it runs, and the mark you record may not match what you loaded. Freezing both bounds at the start makes the run repeatable: the same 2 timestamps return the same rows, which a rerun after a failure needs.

Write first, then move the mark

def run_incremental(conn, state, run_started_at):
    low = state.read_mark("orders")
    high = run_started_at
    rows = conn.query(ORDERS_SQL, low=low, high=high)
    write_replace_by_key(rows, table="raw.orders", key="order_id")
    state.write_mark("orders", high)

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.

Two rows an incremental load misses

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.

Full load

• Replaces the whole table each run
• No progress marker to maintain
• Picks up deletes for free
• Cost grows with table size

Incremental load

• Reads only the changed window
• Needs an updated_at column and a stored mark
• Blind to hard deletes
• Cost grows with change volume

A nightly job loads raw.orders incrementally in this order: advance the high-water mark to the run start time, run the query, write the rows with replace by order_id. On Tuesday the write failed halfway and the orchestrator's retry on Wednesday succeeded. Which rows are at risk?

• No rows. The retry re-pulled the same window because the write replaces by key, so whatever Tuesday half wrote was overwritten with the full set.
• The rows changed between Monday's mark and Tuesday's run start. The mark moved before the write, so the retry pulled from Tuesday's mark onward and never revisited them.
• The rows changed during the retry itself. The mark was set before the retry started, so anything changed while it ran was captured twice.
• Every row in the table. A failed write leaves the destination half replaced, and the retry cannot know which half landed and which did not.

About this lesson

This section is part of the Ingestion Patterns: Beginner lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.