Building Incremental Data Loads That Scale

Reloading a whole table every night works until the table is large. Incremental loading, copying only what is new or changed since the last run, is how pipelines scale, but it introduces a failure mode that full reloads never have: rows that are silently skipped. This guide covers the main incremental patterns, then uses a month of real data to show how the choice of watermark column decides whether a pipeline is complete.

The main patterns

Microsoft's Data Factory documentation describes the common approaches (Azure Data Factory: incremental copy):

• Watermark: a column holding a last-updated timestamp or an incrementing key. Each run loads rows between the previous watermark and the new one, then stores the new watermark.
• Change tracking and change data capture: the database itself records inserts, updates and deletes, so the pipeline reads changes rather than scanning tables; see CDC explained.
• File modification time: copy files whose last-modified date is newer than the last run. It works, but the source must still list every file.
• Time-partitioned paths: when files land in folders such as /2024/01/15/, load only the new folders, which Microsoft calls the most performant approach for new files.

The watermark is the most common, and the easiest to get subtly wrong.

The watermark trap, measured

A watermark is only correct if every row that becomes available later has a watermark value greater than anything already loaded. Event timestamps often break that rule, because events become visible some time after they happen. Taxi trips are a clean example: a trip has a pickup time, when the meter was engaged, and a drop-off time, when it was disengaged (TLC: data dictionary), and a trip record can only exist once the trip ends. This simulation runs an hourly incremental load across January 2024 (NYC TLC: trip record data), treating each trip as available at its drop-off time, and compares three watermark strategies:

import urllib.request
from pathlib import Path
import pandas as pd

name = "yellow_tripdata_2024-01.parquet"
if not Path(name).exists():
    urllib.request.urlretrieve(f"https://d37ci6vzurychx.cloudfront.net/trip-data/{name}", name)
trips = pd.read_parquet(name, columns=["tpep_pickup_datetime", "tpep_dropoff_datetime", "fare_amount"])
trips = trips[trips["tpep_pickup_datetime"].between("2024-01-01", "2024-02-01", inclusive="left")
              & (trips["tpep_dropoff_datetime"] >= trips["tpep_pickup_datetime"])].reset_index(drop=True)
trips.columns = ["pickup", "available_at", "fare"]      # a trip record exists once the trip ends
runs = pd.date_range("2024-01-01 01:00", "2024-02-01 00:00", freq="h")
visible = trips["available_at"] <= runs[-1]
print(f"trips available by the last run: {int(visible.sum())}")

def hourly_loads(watermark_col, lookback="0h"):
    loaded = pd.Series(False, index=trips.index)
    watermark = pd.Timestamp("2000-01-01")                  # nothing loaded yet
    for run in runs:
        new = ((trips["available_at"] <= run) & (trips[watermark_col] > watermark - pd.Timedelta(lookback))
               & ~loaded)                                  # ~loaded stands in for a merge on the row's key
        if new.any():
            loaded |= new
            watermark = max(watermark, trips.loc[new, watermark_col].max())
    return loaded

for label, col, lookback in [("watermark on pickup time", "pickup", "0h"),
                             ("pickup time, 3-hour lookback", "pickup", "3h"),
                             ("watermark on availability time", "available_at", "0h")]:
    missed = visible & ~hourly_loads(col, lookback)
    print(f"{label:<32} missed {int(missed.sum()):>7} trips ({missed.mean():.1%}), fares {trips.loc[missed, 'fare'].sum():>13,.2f}")

trips available by the last run: 2963958
watermark on pickup time         missed  665555 trips (22.5%), fares 19,550,349.31
pickup time, 3-hour lookback     missed    1849 trips (0.1%), fares     50,328.56
watermark on availability time   missed       0 trips (0.0%), fares          0.00

The result is striking. Watermarking on pickup time lost more than a fifth of all trips and 19.5 million dollars of fares, with no error anywhere. Each hourly run advanced the watermark to the latest pickup it saw, a short trip that started minutes earlier, so every longer trip that had started before that moment but finished after the run was skipped forever. A three-hour lookback, re-reading a window behind the watermark and merging on a key, recovered almost everything but still missed 1,849 trips that took longer to appear. Watermarking on the time a record became available missed nothing.

Choosing the watermark column

The rule that follows: watermark on when the row arrived or changed in the source, not on when the business event happened. Good candidates are an ingestion timestamp set by the database, a last-modified column maintained by triggers or the application, or a strictly increasing sequence number. Check that the column is set on every insert and every update, including bulk fixes run by hand, and that it comes from one clock. When no such column exists, a lookback window with a merge is the fallback: size the window from measured delays, and monitor how many rows each lookback catches, because a rising count means the window is too small.

Test the load before trusting it

The pickup-time pipeline above would pass most casual checks: it runs every hour, loads plenty of rows and never errors. The failure only shows when you compare totals with the source. Before an incremental pipeline goes live, run it against historical data alongside a full reload and compare counts per day; any gap is a watermark problem to fix now, not after months of quietly incomplete reports.

Storing the watermark safely

• Advance the watermark only after the load commits. If the load fails, the next run starts from the old value and nothing is lost.
• Store the watermark with the data, ideally in the same transaction, so a crash between them cannot skip or duplicate a batch.
• Use the maximum value actually loaded, not the time the run started, which can skip rows committed during the run.
• Make loads idempotent with merges or partition replacement, so a rerun is harmless; idempotency in data pipelines covers the techniques.

What watermarks cannot see

• Deletes: a deleted row leaves nothing to select. Use soft-delete flags, periodic full comparisons, or change data capture.
• Late corrections to old data: if the source rewrites last month's rows without updating their change column, no watermark notices; handling late-arriving data covers reprocessing windows.
• Schema changes: a new column may be empty in rows loaded before it existed.

Scaling further

Incremental loads keep each run small, but the destination still grows. Partition target tables by date so merges touch only recent partitions, batch very large backlogs into bounded chunks, and schedule periodic reconciliations, comparing counts or checksums between source and target per day, to catch anything a watermark missed. Where these patterns sit in the wider design is covered in ETL vs ELT.

Data loading strategies, extraction techniques and workflow automation are part of the data processing and ETL module of the Data Engineering course.

Sources

• Incrementally copy data from a source data store to a destination data store (Azure Data Factory, Microsoft Learn) — Microsoft Learn
• Data dictionary: yellow taxi trip records (NYC Taxi and Limousine Commission) — NYC Taxi and Limousine Commission
• TLC Trip Record Data (NYC Taxi and Limousine Commission) — NYC Taxi and Limousine Commission