DE Incremental Data Loading

Every data pipeline faces the same challenge: how to keep a destination table current as the source data grows. Loading all the data every time — a full load — works at small scale. At large scale, it becomes too slow, too expensive, and too fragile. Incremental loading solves this by processing only the data that is new or changed since the last run.

Full Load vs Incremental Load

Full Load:
  Every run: Read ALL rows from source --> Write ALL rows to destination
  Source: 500 million rows
  Daily new rows: 100,000
  Daily run: Reads 500 million rows each time --> 99.98% redundant work

Incremental Load:
  Every run: Read only NEW/CHANGED rows since last run
  Daily run: Reads ~100,000 new rows --> 99.98% less work

At small scale, full loads are fine. A 10,000-row table reloads in seconds. A 10-billion-row table that grows by 1 million rows per day cannot reload fully each night — it would take hours, cost a fortune in compute, and likely miss its SLA window.

The Newspaper Delivery Analogy

A newspaper archive wants to add today's news to its collection. A full load is like reprinting the entire archive every day just to add today's edition. An incremental load is like printing only today's newspaper and adding it to the existing archive. The archive stays current. The printing cost covers only new content.

Detecting New and Changed Records

Incremental loading must identify which records to process. Several techniques detect new or changed data in the source system.

Timestamp-Based Incremental Loading

The most common approach. The source table has a column that records when each row was created or last modified — usually named created_at, updated_at, or modified_timestamp. The pipeline stores the timestamp of the last successful run and extracts only rows where the timestamp exceeds that watermark.

Timestamp-Based Incremental Extract:

Last successful run: 2024-05-14 23:59:59

SQL to extract new/updated records:
SELECT *
FROM source_orders
WHERE updated_at > '2024-05-14 23:59:59'
  AND updated_at <= '2024-05-15 23:59:59';

Result: Only rows created or modified on May 15 are returned.
        Unchanged historical rows are skipped entirely.

ID-Based Incremental Loading

When records only ever insert (never update), the auto-incrementing primary key works as the watermark. The pipeline stores the maximum ID from the last run and extracts all rows with a higher ID.

ID-Based Incremental Extract:

Last max order_id loaded: 5,842,100

SQL to extract new records:
SELECT *
FROM source_orders
WHERE order_id > 5842100;

Result: Only orders created after the last run.
Works ONLY for append-only sources -- updates are missed.

Checksum-Based Detection

For source systems without reliable timestamps or IDs, a checksum (hash) of each row detects changes. The pipeline computes the hash of each row in the source and compares it to the stored hash. Rows with different hashes have changed and need reprocessing. This approach is resource-intensive but handles any source structure.

Handling Late-Arriving Data

A common incremental loading problem: a record's updated_at timestamp belongs to yesterday, but the record arrives in the source system today due to a delayed API sync or a retry. A pipeline that extracts strictly from yesterday misses this record entirely.

Data engineers add a lookback window — re-extracting a small overlap from before the last watermark to catch late arrivals.

Lookback Window Pattern:

Last run completed: 2024-05-14 23:59:59

Extract with 3-hour lookback:
  FROM 2024-05-14 20:59:59  (3 hours before last run)
  TO   2024-05-15 23:59:59

Deduplication after load removes any records already processed
in the previous run, preventing duplicates from the overlap period.

Slowly Changing Dimension Incremental Loading

Dimension tables present a special challenge. A customer's city changes. The incremental load must detect this change and apply the correct SCD strategy — overwriting (SCD Type 1) or inserting a new row (SCD Type 2) — rather than simply appending the updated record.

SCD Type 2 Incremental Load:

Source: customer C001 now shows city = "Bangalore" (was "Delhi")

Incremental process:
1. Detect changed row (updated_at changed for C001)
2. Find existing active row in dimension: cust_key=1, city=Delhi, active=Y
3. Close old row: UPDATE dim_customer SET valid_to='2024-05-15', active='N'
                  WHERE cust_key=1
4. Insert new row: INSERT (cust_key=2, cust_id=C001, city=Bangalore,
                          valid_from='2024-05-16', active='Y')

Watermark Management

The watermark is the boundary marker that tells the pipeline where the last run finished. Managing watermarks correctly is critical — storing it too early causes records to be missed; storing it too late causes records to be reprocessed.

Watermark Table (pipeline state store):

+-----------------+----------------------------+--------+
| pipeline_name   | last_successful_watermark  | status |
+-----------------+----------------------------+--------+
| orders_daily    | 2024-05-15 23:59:59        | SUCCESS|
| customers_daily | 2024-05-15 23:59:59        | SUCCESS|
| products_daily  | 2024-05-14 23:59:59        | FAILED |
+-----------------+----------------------------+--------+

On each run:
1. Read watermark for this pipeline
2. Extract records from source AFTER the watermark
3. Process and load successfully
4. UPDATE watermark to current run time
5. If load fails: do NOT update watermark (pipeline retries same window)

Incremental Loading with dbt

dbt supports incremental models natively. Engineers mark a model as incremental, define the unique key, and dbt automatically runs a MERGE/UPSERT rather than a full table replace on subsequent runs — inserting new rows and updating existing ones efficiently.

dbt Incremental Model:

{{ config(materialized='incremental', unique_key='order_id') }}

SELECT
    order_id,
    customer_id,
    amount,
    updated_at
FROM {{ source('crm', 'orders') }}

{% if is_incremental() %}
  WHERE updated_at > (SELECT MAX(updated_at) FROM {{ this }})
{% endif %}

Summary

Incremental loading processes only new or changed records rather than reloading entire datasets on every pipeline run. Timestamp watermarks, ID boundaries, and checksums detect which records need processing. Lookback windows catch late-arriving data. Watermark management ensures no records are missed or duplicated across runs. dbt simplifies incremental model implementation with built-in support. Incremental loading is essential for any pipeline processing tables too large to reload fully within the pipeline's available time window.

Previous lesson

Back to course

Next lesson