Partial and Incremental Reloads in Qlik
- How-Tos FAQs
- November 15, 2020
What is a partial reload in Qlik, and how does it differ from an incremental load?
A standard reloads first deletes all tables in the existing data model and then runs the load script.
A partial reload retains all tables previously loaded in the data model. The partial reload statements, therefore, contain a preceding ADD, MERGE or REPLACE prefix. Standard tables are not affected by the script.
An incremental load is loading only new or updated records from the database into pre-defined QVD files. These differ from Partial Loads in that the QVD file and subsequent queries need to be structured according to defined parameters.
We would use these methods on vast datasets containing millions of rows, where a subset of data is required to update the model dataset.
Whether to utilize Partial Reloads or Incremental Loads is mainly based on preparation. It takes time and planning to implement Incremental loads, whereas Qlik offers a simplified version that is less flexible.
Partial Reload Examples:
TicketData: ADD LOAD SR_NUMBER, Minor, Major, Priority, [Reported Date] FROM [lib://DataFiles/SampleData.qvd] (qvd); TicketData: REPLACE LOAD SR_NUMBER, Minor, Major, Priority, [Reported Date] FROM [lib://DataFiles/SampleData.qvd] (qvd);
Data stored in Incremental loads can be separated by Date fields, Primary keys, Concatenated keys, or any other unique referenceable field. Reloading data from QVD’s runs significantly faster, and if the source data is not going to change, loading the delta data from the source is simple once defined into the QVD data structure.
The basic process:
- Define the data model. Identify the key to be utilized for a set record. It is critical to evaluate if the data at source could be updated at all. For Incremental loads, it is vital to ensure that the data stored in the QVD always matches the data from the source. Therefore we need to be sure that the source data is static and not subject to change.
- Initial data should be loaded into several qvd files that are identified by the chosen KEY. Dates are easily separated into individual files. Loading data into files separated by Year, Month, or even Week or Day, depending on load requirement and volume of data, simplifies naming conventions for QVD files. TransactionsYYYY.qvd, TransactionsYYYYMM.qvd, TransactionsYYYYMMDD.qvd, etc
- Load new or updated data from the database source table into new files.
Subsequent reloads from source reload into the new data models, placing less load on the source, which may be an operational database, the Qlik technology optimizes the reload from QVD’s up to 100 times faster in comparison to a source reload and at around a 90% compression of data storage compared to databases.
Structured Incremental reloads, planned and defined, are a solution that offers benefits in reload time, data source capacity planning, and general speed and performance of your Qlik installation, while partial reloads are a capable alternative to a thoughtfully structured environment.
Up Next: Learn Advanced Qlik Set Analysis