6.3 Incremental Load Patterns: Insert, Update & Delete Strategies

QSDA Qlik Sense Data Architect Study Guide Exam

Track your progress as you read

Finished sections get a check mark.

1 Introduction: About the QSDA Exam

About the QSDA Exam: Current Facts, Objectives & How to Use This Guide

2 Chapter 1: Business Requirements, Data Granularity & Security

1.1 Business Requirements, KPI Formulation & Data Granularity 1.2 Dimensionality Analysis & Slowly Changing Dimensions (SCD) 1.3 Data Security Architecture & Section Access Implementation

3 Chapter 2: Data Connectivity & Source Integration

2.1 Relational Databases, ODBC/OLE DB & Cloud Data Warehouses 2.2 REST API Connectors, Pagination Strategies & Web Data 2.3 Data Connections, lib:// Pathing & Qlik Cloud Space Governance

4 Chapter 3: Dimensional Modeling & Schema Architecture

3.1 Dimensional Modeling: Star Schema vs. Snowflake in Qlik Sense 3.2 Multi-Fact Modeling: Fact Concatenation vs. Link Tables 3.3 Resolving Synthetic Keys ($Syn) and Circular References

5 Chapter 4: QIX Engine Architecture & Key Performance Optimization

4.1 The QIX In-Memory Associative Engine: Symbol Tables & Data Tables 4.2 Composite Keys, Cardinality & AutoNumber Memory Optimization 4.3 Field Scoping, QUALIFY / UNQUALIFY & Semantic Naming

6 Chapter 5: Load Scripting Fundamentals & Table Operations

5.1 Script Structure, Preceding Load & Resident Operations 5.2 Merging Tables: Joins, Keeps & Concatenation Semantics 5.3 Mapping Tables: ApplyMap vs. Lookup & Join Performance 5.4 Advanced Table Reshaping: Crosstable, Generic & Inter-Record Functions (Peek/Previous) 5.5 Script Documentation, Organization & Reusable Include Files

7 Chapter 6: QVD Architecture, Incremental Loads & Temporal Logic

6.1 QVD File Architecture: Optimized vs. Unoptimized Loads 6.2 Multi-Tier QVD Architecture: Extract, Transform & Presentation Layers 6.3 Incremental Load Patterns: Insert, Update & Delete Strategies 6.4 Master Calendars, Canonical Dates & Temporal Logic

8 Chapter 7: Advanced Transformations, Data Cleansing & Validation

7.1 IntervalMatch & Hierarchy Modeling (Hierarchy & HierarchyBelongsTo) 7.2 Data Cleansing, Null Handling & Script Control Flow 7.3 Data Model Validation, Source Reconciliation & Subset Ratios 7.4 Script Debugging, Trace Logging & Security Auditing

6.3 Incremental Load Patterns: Insert, Update & Delete Strategies

Key Takeaways Explain This Topic AI

Incremental loading processes only new, updated, or deleted records rather than the full historical dataset, reducing reload durations from hours to minutes on tables with tens of millions of rows.

High-watermark extraction identifies the latest timestamp or transaction ID in the existing QVD using Peek() or file metadata and passes it to the source query SQL WHERE clause.

The Insert-Only pattern appends immutable delta records to historical QVDs using CONCATENATE and an optimized QVD reload.

The Insert & Update (Upsert) pattern loads delta records first, then concatenates historical QVD rows filtered with WHERE NOT EXISTS(PrimaryKey), so the newest version of each key wins; check the reload log to see whether that historical read stays optimized.

Deleted records require specialized detection strategies such as Primary Key Sweeps (WHERE EXISTS), soft-delete flag filtering, or Change Data Capture (CDC) audit tables.

Last updated: October 2026

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.

Important

The Overlap Window Principle :

Always subtract an overlap safety margin (e.g., 15 minutes, 1 hour, or 1 day depending on transactional batch cycles) from the high-watermark when querying the source database: // 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');

Because the downstream merge logic automatically deduplicates keys using WHERE NOT EXISTS() , overlapping records are safely resolved without creating duplicates.

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

Important

Load Delta Records FIRST, Historical QVD SECOND :

The delta table must be ingested into memory before loading the historical QVD. When the delta records load, the QIX engine registers their primary keys in the symbol table. When the historical QVD subsequently loads with WHERE NOT EXISTS(PrimaryKey) , the QIX engine discards any historical row whose key already exists in memory. This eliminates outdated versions and retains the newly modified delta records.

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

Strategy Mechanism Source Requirement Performance Profile

1. Primary Key Sweep (ID Sweep) Queries all active primary keys from source; filters merged QVD with WHERE Exists(Key) . None (standard primary key) Fast; single-column index scan on database.

2. Soft Delete Flags Database updates record with is_deleted = 1 and updates ModifiedDate . Database schema support ( is_deleted column) Maximum throughput; handled natively by Pattern 2 Upsert.

3. Change Data Capture (CDC) Queries database transaction audit logs (e.g., Debezium, Oracle GoldenGate, AWS DMS). CDC replication tooling enabled Enterprise-grade; captures DML action ( INSERT , UPDATE , DELETE ).

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

Feature Pattern 1: Insert-Only Pattern 2: Upsert Pattern 3: Insert / Update / Delete (Sweep)

Source Requirements Auto-increment ID or CreatedDate LastModifiedDate or CDC trigger LastModifiedDate + Primary Key index

Script Complexity Low Moderate High
Database Overhead Minimal (range scan) Minimal (range scan) Low to Moderate (range scan + full PK index scan)

Qlik RAM Requirement Low (only new rows + QVD streaming) Low (delta rows + QVD streaming) Moderate (requires resident table for sweep filtering)

Handles Modifications? No (creates duplicate rows) Yes Yes
Handles Hard Deletions? No No (retains ghost records) Yes (purges removed keys)

Typical Enterprise Use Cases System logs, IoT telemetry, POS scans Orders, Customer accounts, Shipments Regulated financial ledgers, inventory balances

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 :

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.

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) .

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

Warning

Pitfall 1: Loading Historical QVD Before Delta Records in Upsert Inverting the load sequence: loading the historical QVD first and then attempting to concatenate the delta table with WHERE NOT EXISTS(PrimaryKey) . In this inverted order, the historical keys already occupy the symbol table, causing QIX to discard the newly updated delta rows! Always load the delta records first.

Warning

Pitfall 2: Timestamp Truncation and Timezone Drift Extracting source timestamps with Floor(ModifiedDate) or converting timezones inconsistently between the database query and the Qlik variable. If the database updates records with microsecond precision and Qlik truncates to integer days, records modified on the border of the high-watermark can be skipped or duplicated.

Warning

Pitfall 3: Breaking Optimization During Historical QVD Concatenation Writing CONCATENATE (Orders) LOAD ... FROM [Orders.qvd] (qvd) WHERE NOT Exists(OrderID) AND OrderYear >= 2024; . Adding AND OrderYear >= 2024 creates a compound condition, which always forces Qlik to unpack and evaluate every record. Filter historical years in an upstream design or maintain separate partitioned QVDs.

Warning

Pitfall 4: Neglecting Back-Dated Adjustments Relying on TransactionDate instead of LastModifiedDate as the high-watermark. If an accountant modifies a January transaction in March, an incremental query filtering on TransactionDate >= '2026-03-01' will never extract the modified January record. High-watermark extraction must strictly track modification audit timestamps.