6.3 Incremental Load Patterns: Insert, Update & Delete Strategies

Incremental Load Patterns: Insert, Update & Delete Strategies

In modern data ecosystems, transactional datasets expand continuously. Enterprise systems routinely generate millions of new records daily across point-of-sale transactions, web event logs, financial ledgers, and sensor telemetry. Attempting to reload entire multi-year historical tables from source operational databases every day quickly becomes unviable: network bandwidth saturates, operational database performance degrades, reload tasks breach scheduled service-level agreements (SLAs), and server RAM exhausts during peak ingestion.

A core responsibility of the Qlik Sense Data Architect is designing and implementing Incremental Load Pipelines. An incremental load extracts only the delta—the records that have been inserted, updated, or deleted since the previous execution—and merges those delta records into the existing historical QVD file on disk.

1. The High-Watermark Extraction Principle

At the heart of every incremental load is the High-Watermark: a tracking variable representing the boundary between previously ingested data and newly modified data. The high-watermark is typically derived from a database-managed modification timestamp (LastModifiedDate, Updated_At) or an auto-incrementing sequential sequence ID (TransactionID).

INCREMENTAL HIGH-WATERMARK FLOW:
┌────────────────────────────────────────────────────────┐
│ Step 1: Read Max(ModifiedDate) from Existing QVD       │
│ vLastReload = Peek('MaxModified', 0, 'LastSyncTable')  │
└───────────────────────────┬────────────────────────────┘
                            ▼
┌────────────────────────────────────────────────────────┐
│ Step 2: Pushdown Delta Query to Source Database        │
│ SQL SELECT ... WHERE ModifiedDate >= '$(vLastReload)'  │
└───────────────────────────┬────────────────────────────┘
                            ▼
┌────────────────────────────────────────────────────────┐
│ Step 3: Ingest Delta Records into Qlik Memory          │
│ (Populates symbol table with new/updated keys)         │
└───────────────────────────┬────────────────────────────┘
                            ▼
┌────────────────────────────────────────────────────────┐
│ Step 4: Concatenate Historical QVD Rows                │
│ WHERE NOT EXISTS(PrimaryKey)                           │
│ (Drops outdated historical records matching delta keys)│
└───────────────────────────┬────────────────────────────┘
                            ▼
┌────────────────────────────────────────────────────────┐
│ Step 5: Store Merged Dataset into QVD                  │
│ STORE MergedTable INTO [lib://QVD/Orders.qvd] (qvd);   │
└────────────────────────────────────────────────────────┘

Deriving the High-Watermark Safely

To determine what data to extract, the script must inspect the existing QVD file. Rather than loading the entire multi-gigabyte QVD just to find the maximum timestamp, architects use targeted techniques:

// Technique A: Inspecting QVD file modification time via QvdCreateTime()
LET vLastExecTime = Timestamp(QvdCreateTime('[lib://QVD/Orders.qvd]'), 'YYYY-MM-DD hh:mm:ss');

// Technique B: Storing a dedicated lightweight 1-row metadata control QVD
ControlTable:
LOAD 
    MaxModifiedTimestamp 
FROM [lib://QVD/Orders_Control.qvd] (qvd);

LET vHighWatermark = Peek('MaxModifiedTimestamp', 0, 'ControlTable');
DROP TABLE ControlTable;

The Safety Buffer Window (In-Flight Transactions)

In high-volume databases, transactions can begin before the reload task starts but commit after the query finishes. A strict equality (WHERE ModifiedDate > '$(vLastExecTime)') will permanently skip transactions whose timestamps were recorded prior to query execution but committed immediately following it.

// Subtract 2 hours (2/24 of a day) as a safety buffer for in-flight commits
LET vSafeHighWatermark = Timestamp(vHighWatermark - (2/24), 'YYYY-MM-DD hh:mm:ss');

2. Pattern 1: Insert-Only (Append) Strategy

The Insert-Only (Append) pattern applies to immutable datasets where records, once written, are never modified or deleted.

Common Use Cases

• Web server access logs, firewall security logs, clickstream tracking
• Point-of-sale cash register scan receipts
• Telematics, vehicle GPS pings, industrial IoT sensor streams
• Accounting general ledger postings (where corrections are posted as reversing entries rather than in-place updates)

Production Script Pattern: Insert-Only

// 1. Establish the high-watermark
IF NOT IsNull(QvdCreateTime('[lib://QVD/EventLog.qvd]')) THEN
    // QVD exists: identify the highest existing EventID
    Header_Check:
    LOAD Max(EventID) AS MaxEventID FROM [lib://QVD/EventLog.qvd] (qvd);
    
    LET vMaxID = Peek('MaxEventID', 0, 'Header_Check');
    DROP TABLE Header_Check;
ELSE
    // Initial run: extract all historical data
    LET vMaxID = 0;
ENDIF

// 2. Extract new records from source database (Pushdown filtering)
LIB CONNECT TO 'Operational_Log_DB';

Events_Delta:
LOAD 
    EventID,
    EventTimestamp,
    EventType,
    MachineID,
    Payload;
SQL SELECT 
    event_id        AS EventID,
    event_timestamp AS EventTimestamp,
    event_type      AS EventType,
    machine_id      AS MachineID,
    payload         AS Payload
FROM system_logs.events
WHERE event_id > $(vMaxID);

// 3. Concatenate existing historical QVD via OPTIMIZED load
IF NOT IsNull(QvdCreateTime('[lib://QVD/EventLog.qvd]')) THEN
    CONCATENATE (Events_Delta)
    LOAD 
        EventID,
        EventTimestamp,
        EventType,
        MachineID,
        Payload
    FROM [lib://QVD/EventLog.qvd] (qvd);
    // No filter and no transformation, so this QVD read can stay optimized
ENDIF

// 4. Overwrite historical QVD with the merged dataset and free memory
STORE Events_Delta INTO [lib://QVD/EventLog.qvd] (qvd);
DROP TABLE Events_Delta;

3. Pattern 2: Insert & Update (Upsert) Strategy

The vast majority of enterprise operational datasets represent mutable business entities: orders change status (from Open to Processing, Shipped, and Delivered), customer addresses change, and inventory allocations fluctuate. Handling these tables requires the Insert & Update (Upsert) pattern.

The Golden Rule of Upsert Loading

Complete Production Script Template: Insert & Update

SET vQVDPath = 'lib://QVD_Store/Fact_Orders.qvd';

// -------------------------------------------------------------------
// STEP 1: Determine the High-Watermark Timestamp
// -------------------------------------------------------------------
IF NOT IsNull(QvdCreateTime('$(vQVDPath)')) THEN
    // Find the latest modified date from the existing historical QVD
    Control_Temp:
    LOAD 
        Max(ModifiedDate) AS MaxDate 
    FROM [$(vQVDPath)] (qvd);
    
    // Apply a 2-hour safety overlap buffer
    LET vHighWatermark = Timestamp(Peek('MaxDate', 0, 'Control_Temp') - (2/24), 'YYYY-MM-DD hh:mm:ss');
    DROP TABLE Control_Temp;
ELSE
    // Initial baseline reload: fetch everything from beginning of history
    LET vHighWatermark = '1900-01-01 00:00:00';
ENDIF

TRACE >> Ingesting Delta Records modified since: $(vHighWatermark);

// -------------------------------------------------------------------
// STEP 2: Extract Delta Records from Source Database (Pushdown)
// -------------------------------------------------------------------
LIB CONNECT TO 'Postgres_ERP_Warehouse';

Orders:
LOAD 
    OrderID,
    CustomerID,
    Date(Floor(OrderDate))       AS OrderDate,
    OrderStatus,
    OrderAmount,
    Timestamp(LastModifiedDate)  AS ModifiedDate;
SQL SELECT 
    order_id            AS OrderID,
    customer_id         AS CustomerID,
    order_date          AS OrderDate,
    order_status        AS OrderStatus,
    order_amount        AS OrderAmount,
    last_modified_date  AS LastModifiedDate
FROM sales.orders
WHERE last_modified_date >= '$(vHighWatermark)';

// -------------------------------------------------------------------
// STEP 3: Concatenate Historical QVD Skipping Records Present in Delta
// -------------------------------------------------------------------
IF NOT IsNull(QvdCreateTime('$(vQVDPath)')) THEN
    CONCATENATE (Orders)
    LOAD 
        OrderID,
        CustomerID,
        OrderDate,
        OrderStatus,
        OrderAmount,
        ModifiedDate
    FROM [$(vQVDPath)] (qvd)
    WHERE NOT Exists(OrderID); // Keeps only keys that are not in the delta
ENDIF

// -------------------------------------------------------------------
// STEP 4: Persist Merged Dataset into QVD and Cleanup
// -------------------------------------------------------------------
STORE Orders INTO [$(vQVDPath)] (qvd);
DROP TABLE Orders;

TRACE >> Incremental Upsert completed successfully. Target QVD updated.;

How WHERE NOT EXISTS(OrderID) Merges the Data

Because the delta rows were loaded first, every changed OrderID already exists as a field value when the historical QVD is read. WHERE NOT Exists(OrderID) skips those historical rows, so the merged table holds exactly one, newest version of each key. Practitioners disagree about whether the negated form keeps the QVD read optimized in every release (the reliably optimized filter is a single-parameter WHERE Exists(Field)), so confirm the mode in the reload log. Even a standard QVD read is far cheaper than re-querying the full history from the source database.

4. Pattern 3: Insert, Update & Delete Strategy

While the Upsert pattern elegantly resolves new and modified records, it has a critical blindspot: Physical Deletions (Hard Deletes).

The Hard Delete Problem

If an operator cancels an order and the source database executes DELETE FROM sales.orders WHERE order_id = 90210;, that row ceases to exist in the database. When the incremental reload queries WHERE last_modified_date >= '$(vHighWatermark)', the database returns no information about Order 90210. When the historical QVD is loaded with WHERE NOT EXISTS(OrderID), Order 90210 is not in the delta, so it is loaded from history. The deleted order remains in the QVD indefinitely as a ghost record, causing financial discrepancies.

Architectural Strategies for Resolving Deletes

Production Script Pattern: Primary Key ID Sweep

In the absence of a database CDC log, the Primary Key Sweep is the standard way to handle hard deletes. Qlik's own incremental-load documentation implements it by inner joining the merged table with SQL SELECT PrimaryKey FROM source; the version below does the same with an Exists() filter against a separately named key field:

SET vQVDPath = 'lib://QVD_Store/Fact_Orders.qvd';

// Step 1: Execute Standard Upsert Pipeline (Load Delta, Concatenate Historical)
// [Steps 1-3 from Pattern 2 run here, creating in-memory table 'Orders_Merged']

// Step 2: Extract ALL Active Primary Keys from Operational Database (ID Sweep)
LIB CONNECT TO 'Postgres_ERP_Warehouse';

ActiveKeys:
LOAD 
    ActiveOrderID;
SQL SELECT 
    order_id AS ActiveOrderID
FROM sales.orders; // Lightweight: scans single primary key index

// Step 3: Purge Deleted Records by Filtering Against Active Keys
Final_Orders:
LOAD 
    OrderID,
    CustomerID,
    OrderDate,
    OrderStatus,
    OrderAmount,
    ModifiedDate
RESIDENT Orders_Merged
WHERE Exists(ActiveOrderID, OrderID); // Retains only records existing in source

// Step 4: Cleanup Temporary Tables and Persist
DROP TABLES Orders_Merged, ActiveKeys;

STORE Final_Orders INTO [$(vQVDPath)] (qvd);
DROP TABLE Final_Orders;

5. Incremental Load Strategy Comparison Matrix

6. Enterprise Production Scenario: Financial Ledger Pipeline

Business Problem

A global financial institution manages a General Ledger transactional table containing 85 million records dating back seven years. Nightly reloads took 2 hours and 50 minutes, extracting the entire table from Oracle. Business requirements dictate that while historical ledger entries cannot be altered, transactions within the active accounting period (current quarter) can be adjusted by controllers, and uncommitted draft vouchers can be deleted.

Architectural Design & Solution

The lead architect implemented a hybrid Quarterly Boundary Incremental Pattern with ID Sweep:

1. Static Historical Base: Historical financial years (prior to the active fiscal year) were frozen into read-only annual QVDs (GL_2019.qvd through GL_2025.qvd) that are never reloaded.
2. Active Fiscal Year Upsert: The active fiscal year table (GL_ActiveYear.qvd, ~12 million rows) was configured with an incremental load:
   • High-watermark extraction queries rows where oracle_update_timestamp >= '$(vLastSync)'.
   • Historical QVD concatenated with WHERE NOT EXISTS(VoucherLineID).
3. Monthly Hard Delete Sweep: On the 1st of every month, an ID Sweep task extracts all active VoucherLineID values for the current fiscal year, purging deleted draft vouchers.

Impact: Daily reload duration dropped from 2 hours 50 minutes to 3 minutes and 15 seconds. Database CPU utilization on the Oracle financial cluster plummeted by 92%, and the reload task easily met the morning SLA.

7. Common Exam Pitfalls & Traps