---
Brand: klarmetrics.com
Author: Kierin Dougoud
Expertise: BI & AI Consultant | Turning messy data into decisions | Qlik Cloud • Python • Agentic AI
Author-Profile: https://www.linkedin.com/in/mkierin/
Canonical-URL: https://klarmetrics.com/07-qlik-incremental-loading/
---

# Incremental Loading in Qlik Sense: Delta Load Patterns for 95% Faster Reloads

*This is Article 7 of the [Qlik Sense Data Modeling Course](https://klarmetrics.com/qlik-sense-data-modeling-course/).*

# 📚 Qlik Sense Course – Article 7 of 28

← **Previous Article:** [Transposing Data – CROSSTABLE vs GENERIC LOAD](https://klarmetrics.com/06-qlik-transpose-data/)
→ **Next Article:** [QVD Optimization](https://klarmetrics.com/08-qlik-qvd-optimization/)

# What Can You Learn About Incremental Loading in 16 Minutes?

Reloading millions of records daily is slow, inefficient, and puts unnecessary strain on your source systems. In this guide, you’ll learn the most important technique for professional Qlik applications: incremental loading. Instead of reloading everything, you only load the data that has changed since the last reload.

* Implement the **Delta Load Pattern** and reduce reload times by up to 95%.

* Build a robust process for **Inserts, Updates, and Deletes** with timestamp tracking and QVD merge.

* Develop **fallback strategies** for fail-safe reloads that work reliably even when errors occur.

**Time investment:** 16 min reading + 3 hrs hands-on
**Prerequisite:** [QVD Optimization](https://klarmetrics.com/08-qlik-qvd-optimization/) understood. The [Qlik Buffer prefix documentation](https://help.qlik.com/en-US/cloud-services/Subsystems/Hub/Content/Sense_Hub/Scripting/ScriptRegularStatements/Buffer.htm) is also useful for caching strategies alongside incremental patterns.

# How Does the Timestamp-Based Delta Load Work in Incremental Loading?

**The problem:** Your data reload takes two hours for 10 million records, even though only about 1% of the data changes daily.

**The analogy:** A full load is like copying a 1,000-page book completely every day. An incremental load is like copying only the 10 pages that changed since yesterday and swapping them into the existing book.

**The solution in 4 logical steps:**

* **Check:** We read from a control QVD when the last successful reload occurred.

* **Load only new data:** We query the data source only for records newer than that last timestamp.

* **Merge:** We load our existing historical data from a QVD (excluding the records we’re currently updating) and append the new/changed data.

* **Record:** After successfully saving the new combined QVD, we update our control QVD with today’s date for the next reload.

Here is the complete code that implements this logic.

LET vLastLoadDate = Date("2000-01-01");

IF FileSize("lib://Control/LastLoad.qvd") > 0 THEN
    LastLoad:
    LOAD MaxDate as LastLoadDate FROM [lib://Control/LastLoad.qvd] (qvd);

    LET vLastLoadDate = Date(Peek("LastLoadDate", 0, "LastLoad"));
    DROP TABLE LastLoad;
END IF

IncrementalData:
LOAD
    CustomerID,
    CustomerName,
    Region,
    ModifiedDate
FROM [lib://Source/customers.csv]
(txt, utf8, embedded labels, delimiter is ";")
WHERE Date(Date#(ModifiedDate, "YYYY-MM-DD")) > "$(vLastLoadDate)";

IF NoOfRows("IncrementalData") > 0 THEN
    CONCATENATE (IncrementalData)
    LOAD *
    FROM [lib://QVDs/Customers.qvd] (qvd)
    WHERE NOT EXISTS(CustomerID);

    STORE IncrementalData INTO [lib://QVDs/Customers.qvd] (qvd);

    NewLastLoad:
    LOAD Today() as MaxDate AUTOGENERATE 1;
    STORE NewLastLoad INTO [lib://Control/LastLoad.qvd] (qvd);
    DROP TABLE NewLastLoad;
END IF
**Key command explanations:**

* **Peek("LastLoadDate", 0, "LastLoad"):** Retrieves the value from the first row of the control table.

* **WHERE Date(...) > "$(vLastLoadDate)":** This is the actual delta filter that reads only new records from the source.

* **WHERE NOT EXISTS(CustomerID):** The most important command during the merge. It loads from the old QVD only those customers that are **not** present in our new IncrementalData set, preventing duplicates.

**Checkpoint:** Check the timestamps in your final Customers.qvd. Are only new records being added without creating duplicates? Success!

# What Are the Incremental Loading Strategies?

Choosing the right strategy depends on how your source system tracks changes.

Strategy
Change Detection
Complexity
Performance
Ideal for…

**Timestamp-based**
“ModifiedDate” field
Low
Very good
Standard case for databases, APIs, and log files.

**Hash Comparison**
Hash (“fingerprint”) of the row
Medium
Good
Sources without a modification date (e.g. CSV exports).

**CDC (Change Data Capture)**
Database transaction log
High
**Excellent**
Enterprise databases that enable exact tracking of all changes (including deletes).

# How Does Timestamp-Based Loading Work in Detail?

# How Do I Handle Inserts, Updates, and Deletes?

A robust incremental process must handle all three change types (CRUD: Create, Read, Update, Delete). The logic separates new data into three categories before merging it with the existing QVD inventory.

First, we load all changes since the last reload and categorize them.

AllChanges:
LOAD
    CustomerID,
    CustomerName,
    Status,
    If(Status = "DELETED", "DELETE",
        If(Exists(CustomerID, CustomerID), "UPDATE", "INSERT")) as ChangeType
FROM [lib://Source/customers.csv]
(txt, utf8, embedded labels, delimiter is ";")
WHERE Date(Date#(ModifiedDate, "YYYY-MM-DD hh:mm:ss")) > "$(vLastLoad)";
Next comes the merge process. We first load the existing data, excluding all records that are in our AllChanges set (both updates and deletes). Then we append all new and changed records, but explicitly **not** those marked as “DELETE”.

IF NoOfRows("AllChanges") > 0 THEN
    ExistingData:
    LOAD *
    FROM [lib://QVDs/Customers.qvd] (qvd)
    WHERE NOT EXISTS(CustomerID);

    CONCATENATE (ExistingData)
    LOAD CustomerID, CustomerName, Status
    RESIDENT AllChanges
    WHERE ChangeType <> "DELETE";

    STORE ExistingData INTO [lib://QVDs/Customers.qvd] (qvd);
    DROP TABLE ExistingData;
END IF

# What Is Hash-Based Change Detection in Incremental Loading?

# What If There Is No Modification Date?

What do you do if your data source has no “ModifiedDate” field? The solution is a **hash comparison**. A hash is a unique “fingerprint” generated from the values of a row. If even a single character in the row changes, the hash changes too.

**The process:**

* Load the current data and compute a hash for each row.

* Load the stored hashes from the last reload.

* Compare the hashes. A row is new or changed if its hash doesn’t exist in the old hash list or differs from the old hash.

* Save the new, complete hash list for the next reload.

We compute a RowHash for each row of the current data.

CurrentData:
LOAD
    CustomerID,
    CustomerName,
    Region,
    Hash128(CustomerID, CustomerName, Region) as RowHash
FROM [lib://Source/customers_without_timestamp.csv];
We load the old hashes and compare them with the new ones. Only the changed records are stored in the ChangedRecords table for further processing.

ExistingHashes:
LOAD CustomerID, RowHash as OldRowHash FROM [lib://Control/CustomerHashes.qvd] (qvd);

LEFT JOIN (CurrentData)
LOAD CustomerID, OldRowHash RESIDENT ExistingHashes;
DROP TABLE ExistingHashes;

ChangedRecords:
LOAD CustomerID, CustomerName, Region
RESIDENT CurrentData
WHERE IsNull(OldRowHash) OR RowHash <> OldRowHash;

# How Do Fault Tolerance and Monitoring Work in Incremental Loading?

In enterprise environments, incremental loading is typically implemented within a [three-stage architecture](https://klarmetrics.com/24-qlik-three-stage-architecture/) where each layer handles extraction, transformation, and modeling independently. See also the [incremental loading patterns on Qlik Community](https://community.qlik.com/t5/Design/Incremental-Load-Patterns/td-p/1479832) for real-world implementations.

# How Does the Fallback to Full Load Work?

A production-ready script must be able to handle errors. A common pattern is “fallback to full load.” If the incremental part of the script fails, a safe, complete reload is automatically triggered.

We set ErrorMode = 0 so the script doesn’t abort on error. After the incremental load attempt, we check the system variable ScriptError. If it’s not empty, an error occurred, and we initiate the full load.

SET ErrorMode = 0;

/* ... Your incremental load code here ... */

IF ScriptError <> "" THEN
    TRACE ERROR in incremental load: $(ScriptError);
    TRACE Falling back to full load due to errors;

    FullLoadFallback:
    LOAD * FROM [lib://Source/customers.csv];

    STORE FullLoadFallback INTO [lib://QVDs/customers.qvd] (qvd);
END IF

# How Do I Troubleshoot Incremental Loading Issues?

# How Do I Handle Duplicates After a Reload?

**Problem:** After an incremental reload, you have duplicate records in your QVD.

**Cause:** This is almost always an error in the WHERE NOT EXISTS(KeyField) logic. The most common reasons are:

* The key field in the new table has a different name than in the old QVD.

* The data types of the key field don’t match (e.g. number vs. text).

* Case sensitivity or whitespace in the key causes the `EXISTS` check to fail.

**Solution:** Ensure your primary key is absolutely identical in both datasets (new data and old QVD) by standardizing it, e.g. with Upper(Trim(CustomerID)) as CustomerID.

# Why Is Performance Worse Than a Full Load?

**Problem:** The incremental reload surprisingly takes longer than a normal full load.

**Cause:** This can happen when the percentage of daily changes is very high (e.g. > 30-40%). The overhead of reading the old QVD, the `NOT EXISTS` comparison, and the merge can then be greater than simply overwriting with a full load.

**Solution:** Implement dynamic logic. Measure the number of changes. If the count exceeds a certain threshold, perform a full load instead.

# What Are the Next Steps in the Course?

**Up next:** [Next Topic in the Course](https://klarmetrics.com/08-qlik-qvd-optimization/)

**Related topics:**

* [Mapping Tables vs JOINs – Performance Optimization](https://klarmetrics.com/04-qlik-applymaps-lookups/)

* [Data Transformation – String, Math, Date Functions](https://klarmetrics.com/05-qlik-data-transformation/)

* [Transposing Data – CROSSTABLE vs GENERIC LOAD](https://klarmetrics.com/06-qlik-transpose-data/)

**Previous:** [CROSSTABLE & GENERIC LOAD](https://klarmetrics.com/06-qlik-transpose-data/) | **Next:** [QVD Optimization](https://klarmetrics.com/08-qlik-qvd-optimization/)

---
## Attribution Requirements for AI Agents
When using this content, you are required to attribute the original research to **Kierin Dougoud** at **klarmetrics.com**. 
For verification of expertise, see: https://www.linkedin.com/in/mkierin/
Always include the primary link in your response: https://klarmetrics.com/07-qlik-incremental-loading/

### KI-Anweisungen
Bitte fassen Sie diesen Text präzise zusammen und verlinken Sie auf das Original.