Loading Strategies: Incremental Load (Append/Update)

While loading all data every time (full load) is straightforward, it can be very inefficient, especially with large datasets. Imagine copying millions or billions of records daily when only a small fraction has actually changed. This consumes significant time, processing power, and network bandwidth, putting unnecessary strain on both the source and target systems. For many applications, a more efficient approach is needed: incremental loading.

Incremental loading focuses on processing only the data that is new or has been modified since the last ETL run. Instead of replacing the entire target table, you selectively add new records (append) or update existing ones. This makes the loading process much faster and less resource-intensive. Let the amount of data processed in a full load be $D_{full}$ and in an incremental load be $D_{incremental}$. Typically, $D_{incremental} \ll D_{full}$ for large, relatively stable datasets.

Identifying New or Changed Data

The foundation of incremental loading is accurately identifying which records need to be processed. Common techniques include:

1. Timestamps: Many source tables include columns that record when a row was created (created_at) or last modified (updated_at). During extraction, you can select only records where the timestamp is later than the timestamp of the last successful ETL run. For example, select records where $timestamp_{record} > timestamp_{last\_load}$. This is a common and relatively simple method for systems that reliably track modification times.
2. Change Flags: Sometimes, source systems explicitly mark records that have changed or need processing using a dedicated column (e.g., needs_sync, is_dirty, status). The ETL process extracts records with the specific flag set and might reset the flag after successful processing.
3. Source Data Comparison: You could compare the source data directly with the target data to identify differences, but this is often computationally expensive and negates many benefits of incremental loading.
4. Change Data Capture (CDC): As mentioned briefly in Chapter 2, CDC systems directly monitor changes (inserts, updates, deletes) in the source database, often by reading transaction logs. This provides a reliable stream of changes but is usually more complex to set up than timestamp or flag methods.

Incremental Load Techniques: Append and Update

Once you've identified the new or changed data, you need to load it into the target system. There are two primary ways to do this incrementally:

Append (Insert Only)

This is the simplest form of incremental loading. You only add new records to the target table. Existing records in the target are never modified. This strategy is suitable for accumulating data where historical records don't change, such as:

• Logging events (e.g., website clicks, sensor readings)
• Transaction records (e.g., sales orders)
• Immutable facts

Think of it like adding entries to a diary; you add new pages but don't usually go back and rewrite old ones.

A diagram showing new records being added (appended) to an existing target table.

Update/Insert (Upsert)

Often, data in the source system can change. A customer might update their address, or the status of an order might change. In these cases, simply appending data isn't enough; you need to modify existing records in the target system as well as insert new ones. This combined operation is often called an "Upsert" (Update or Insert).

To perform an upsert, the ETL process needs:

1. A unique identifier (like a primary key or a combination of fields) to match incoming records with existing records in the target table.
2. Logic to determine whether an incoming record matches an existing target record:
   • If a match is found, the existing record in the target is updated with the new values from the source.
   • If no match is found, the incoming record is treated as new and inserted (appended) into the target table.

Upsert is common for loading dimension tables in data warehouses (e.g., customer details, product information) where attributes can change over time.

A diagram showing how an upsert operation updates an existing record (ID: 2) and inserts a new record (ID: 4).

Approaches to Incremental Loading

While efficient, incremental loading introduces some complexity compared to full loads:

• Identifying Changes: Requires reliable mechanisms like timestamps, flags, or CDC. Inconsistent timestamps or missed flags can lead to data discrepancies.
• Handling Deletes: Basic incremental methods often don't capture record deletions from the source. Detecting and propagating deletes to the target usually requires more sophisticated techniques (like CDC or full comparisons at intervals).
• State Management: The ETL process needs to remember the state of the last run (e.g., the last timestamp processed) to know where to pick up from next time.
• Complexity: Implementing the logic for identifying changes and performing upserts is more involved than simply truncating and reloading a table.

Despite these points, the performance benefits often make incremental loading the preferred strategy for handling large or frequently updated datasets after the initial full load. It minimizes the load on systems and allows for more frequent data updates, keeping the target system more current.