Load only what changed since the last run

How to do it

Copying every table in full each night gets slower and costlier as the data grows. Use an updated_at column the database sets on every write, or the log position from change data capture. A row committed late can carry an earlier updated_at, so re-read an overlap and let Make every load safe to rerun drop the repeats. Store every time in UTC. Deleted rows never show up in a change column, which Carry deletes into the warehouse handles.

Examples

Python example

from datetime import timedelta

OVERLAP = timedelta(minutes=15)

def load_changed_orders(source, warehouse):
    since = warehouse.cursor("orders") - OVERLAP
    rows = source.query("SELECT * FROM orders WHERE updated_at >= %s ORDER BY updated_at", (since,))
    if rows:
        load_orders(warehouse, rows)
        warehouse.save_cursor("orders", max(row["updated_at"] for row in rows))

JavaScript example

const OVERLAP_MS = 15 * 60 * 1000;

export async function loadChangedOrders(source, warehouse) {
  const since = new Date((await warehouse.cursor('orders')).getTime() - OVERLAP_MS);
  const rows = await source.query('SELECT * FROM orders WHERE updated_at >= $1 ORDER BY updated_at', [since]);
  if (rows.length === 0) return;
  await loadOrders(warehouse, rows);
  await warehouse.saveCursor('orders', rows.at(-1).updated_at);
}

How to check

Update a source row while a load runs on staging. The next run copies the change. Stop a load before it commits. The saved position stays where it was.

Related rules

• DEN-7 Make every load safe to rerun in Data engineering
• DEN-9 Carry deletes into the warehouse in Data engineering