Reliable Large QVD Loads that wont break: A Chunked Parallel Blueprint with QA.

[AlipNath]

A familiar Problem for Large QVDs

We've all been here: You have a large Oracle table: 10M, 15M, 20M+ rows. You write your SQL SELECT, point it at your data connection, hit reload, and wait. 30 minutes later, the connection drops or the engine runs out of memory. Or an overzealous DBA kills your session. Either way you got no QVD, zero rows. Start again, only to get bummed out again!!

Cause: 

Qlik Sense LOAD-STORE operations are transactional. If a SQL SELECT fails mid-execution, all rows in memory are discarded and no QVD is written. For tables exceeding 10M+ rows over constrained Oracle connections, this creates a reliability problem that retries cannot solve

For the table I was working with say`W_XYZ_SALES_ORDERLINE_DIM` at ~16M rows across an Oracle Data Warehouse connection: this was failing roughly 50% of the time, even during the Development phase, eating away a lot of my progress and momentum.

Environment:

It will work with most of the recent Qlik Sense versions, while I am on Qlik Sense Enterprise May 2025

Resolution: Three Components

Here's the architecture, which calls for no external tools or custom connectors: just native Qlik Sense scripting and QMC task chaining.

• Chunked Loader with Timeout Guard

Instead of one massive SELECT, you define non-overlapping WHERE clauses that partition your source table. Each chunk loads independently, stores to its own QVD immediately, and then checks elapsed time. If a chunk exceeds your time ceiling, the script stops cleanly, but every chunk already completed is safe on disk.

Here's the core loop: I went with 13 chunks for this example; you can adjust per your needs

/*Start of Core Loop*/
SET vChunkCount = 13; //this can be based on your needs but 13 is a good max number
SET vMaxMinutes = 40; // max time windows defined for a QVD chunk load
SET vStartChunk = 1;    // Change this to resume after a timeout
// Initialize the QA log table — stays in the data model for validation 
[ChunkLog]: 
LOAD * INLINE [ Builder, GlobalBatch, ChunkFile, QVDLoaded, ElapsedMin, Status, LoadTimestamp ];
// ... (vWhere_1 through vWhere_13 defined above) ...

FOR vI = $(vStartChunk) TO $(vChunkCount)
    LET vChunkFile = 'CHUNK_' & Right('0' & '$(vI)', 2) & 'SALES_ORDERLINE_DIM.qvd';
    LET vStartTS   = Num(Now());

    [ChunkData]:
    SQL SELECT *
    FROM W_XYZ_SALES_ORDERLINE_DIM
    WHERE $(vWhere_$(vI));

    LET vChunkRows = NoOfRows('ChunkData'); /*Captures the current timestamp as a serial number*/

    // Data is safe the moment this line executes with QVD chunks
    STORE [ChunkData] INTO [$(vChunkPath)$(vChunkFile)] (qvd);
    DROP TABLE [ChunkData];

    LET vElapsedMin = Round((Num(Now()) - $(vStartTS)) * 1440, 0.1);/* Takes a second timestamp *after* the load and store are complete*/

    // Timeout guard - controlled stop, not a failure

// Append to QA log
    CONCATENATE([ChunkLog])
    LOAD * INLINE [
    Builder, GlobalBatch, ChunkFile, QVDLoaded, ElapsedMin, Status, LoadTimestamp
    '$(vBuilderID)', $(vI), '$(vChunkFile)', $(vChunkRows), $(vElapsedMin), '$(vStatus)', '$(=Now())'
    ];
    IF $(vElapsedMin) > $(vMaxMinutes) THEN // decision block for a long running chunk
        TRACE !!! TIMEOUT — Chunk $(vI) took $(vElapsedMin) min; 
        TRACE !!! Data is safe. Set vStartChunk = $(vI) to resume.;
        EXIT SCRIPT;
    END IF

NEXT vI
// Builder summary row
CONCATENATE([ChunkLog])
LOAD * INLINE [
Builder, GlobalBatch, ChunkFile, QVDLoaded, ElapsedMin, Status, LoadTimestamp
'$(vBuilderID)', 0, 'BUILDER A TOTAL', $(vTotalRows), $(vJobElapsed), 'COMPLETE', '$(=Now())'
];

STORE [ChunkLog] INTO [$(vChunkPath)LOG_BUILDER_A.qvd] (qvd);
/*End of Core Loop*/

Key details:

• `STORE` happens before the timeout check — so even the chunk that triggers the timeout is safely on disk.
• vStartChunk` is your resume mechanism. If chunk 5 times out, set `vStartChunk = 5` (or 6, if chunk 5 completed but was slow), and re-run. Chunks 1–4 are untouched.
• The `Right('0' & ...)` padding gives you `CHUNK_01`, `CHUNK_02`, etc. — clean, sortable, easy to inspect.

• Parallel Execution via QMC

Qlik Sense is single-threaded per app. But you can run two apps simultaneously. The approach with 13 QVD chunks/batches consideration:

- QVD Builder App A — loads chunks 1–7

- QVD Builder App B — loads chunks 8–13

- A Stitcher App fires after both are complete, concatenates all chunk QVDs into the final QVD

In QMC, set up the tasks like this:

- `Task_Builder_A` and `Task_Builder_B` — trigger on the same schedule (or start both manually)

- `Task_Stitcher` — trigger type "On task event" → select both builder tasks → condition: "When all selected tasks have completed successfully."

The scripts for A and B are identical in structure. The only differences are which chunk numbers they handle, and a `vGlobalOffset` variable in Builder B that maps its local loop index to the correct global chunk number.

Builder B's loop:

/* Start of Sample Builder B's loop*/

SET vChunkCount  = 6;

SET vGlobalOffset = 7;    // Local chunk 1 = global chunk 8



FOR vI = $(vStartChunk) TO $(vChunkCount)

    LET vGlobalChunk = $(vI) + $(vGlobalOffset);

    LET vChunkFile   = 'CHUNK_' & Right('0' & '$(vGlobalChunk)', 2) & '_SALES_ORDERLINE_DIM.qvd';

    // ... same load/store/timeout logic defined above in Core Loop

NEXT vI

/* End of Sample Builder B's loop*/

Both QVD builders write the same QVD folder (you can create a sub-folder for your needs). File names include the global chunk number, so there's no collision.

Note:  While you may scale beyond two Task Builders to handle more QVD chunks or increase parallelism, ensure the configuration remains within the limits of this "controlled window" architecture to maintain overall efficiency. Also, you can integrate the Task_Sticher directly into Task_Builder_B or your final Task Builder.

• The Stitcher with Validation

This is where the QA layer lives. The stitcher does three things:

a) Merges builder logs: Each builder stores a `ChunkLog` table as a QVD. The stitcher loads both and concatenates them into a master log. 

b) Reads and concatenates all chunk QVDs: Since both builders completed successfully (QMC guarantees this), all chunk files exist. The stitcher reads them sequentially: this is fast because QVD-to-QVD loading is optimized.

c) Adds a final validation row: The stitcher generates the total stitched row count and adds a summary row to the ChunkLog for validation.

/*Start of Stitcher Block*/

// Load QA logs from both builders into one master view
[ChunkLog]:
LOAD * FROM [$(vChunkPath)LOG_BUILDER_A.qvd] (qvd);

CONCATENATE([ChunkLog])
LOAD * FROM [$(vChunkPath)LOG_BUILDER_B.qvd] (qvd);

// Stitch all chunk QVDs into the final QVD
LET vStitchStart = Num(Now());

FOR vJ = 1 TO $(vChunkCount)

    LET vChunkFile = 'CHUNK_' & Right('0' & '$(vJ)', 2) & '_SALES_ORDERLINE_DIM.qvd';

    IF vJ = 1 THEN
        [FinalTable]:
        LOAD * FROM [$(vChunkPath)$(vChunkFile)] (qvd);
    ELSE
        CONCATENATE([FinalTable])
        LOAD * FROM [$(vChunkPath)$(vChunkFile)] (qvd);
    END IF

NEXT vJ

LET vFinalRows = NoOfRows('FinalTable');
LET vStitchMin = Round((Num(Now()) - $(vStitchStart)) * 1440, 0.1);

// Add final QVD summary row to the QA log (GlobalBatch 99 = final stitched QVD)
CONCATENATE([ChunkLog])
LOAD * INLINE [
Builder, GlobalBatch, ChunkFile, QVDLoaded, ElapsedMin, Status, LoadTimestamp
'STITCH', 99, '$(vFinalQVD)', $(vFinalRows), $(vStitchMin), 'FINAL', '$(=Now())'
];

STORE [FinalTable] INTO [$(vFinalPath)$(vFinalQVD)] (qvd);
DROP TABLE [FinalTable];

// Store master QA log for audit trail
STORE [ChunkLog] INTO [$(vChunkPath)LOG_MASTER_VALIDATION.qvd] (qvd);

/*End of Stitcher Block*/

The Validation Table: Your QA Dashboard

Each QVD Builder App and the stitcher all maintain a `ChunkLog` table that stays in the data model.

Builder	GlobalBatch	ChunkFile	QVDLoaded	ElapsedMin	Status
CHUNKBUILDER_A	0	BUILDER CHUNKBUILDER_A TOTAL	4443842	21.5	COMPLETE
CHUNKBUILDER_A	1	CHUNK_01W_XYZ_SALES_ORDERLINE_DIM.qvd	0	0.0	OK
CHUNKBUILDER_A	2	CHUNK_02W_XYZ_SALES_ORDERLINE_DIM.qvd	1000149	4.7	OK
CHUNKBUILDER_A	3	CHUNK_03W_XYZ_SALES_ORDERLINE_DIM.qvd	1247265	5.9	OK
CHUNKBUILDER_A	4	CHUNK_04W_XYZ_SALES_ORDERLINE_DIM.qvd	69491	0.3	OK
CHUNKBUILDER_A	5	CHUNK_05W_XYZ_SALES_ORDERLINE_DIM.qvd	1221497	6.0	OK
CHUNKBUILDER_A	6	CHUNK_06W_XYZ_SALES_ORDERLINE_DIM.qvd	504506	2.5	OK
CHUNKBUILDER_A	7	CHUNK_07W_XYZ_SALES_ORDERLINE_DIM.qvd	400934	2.0	OK
CHUNKBUILDER_B	0	BUILDER CHUNKBUILDER_B TOTAL	6673658	35.3	COMPLETE
CHUNKBUILDER_B	8	CHUNK_08W_XYZ_SALES_ORDERLINE_DIM.qvd	579282	2.8	OK
CHUNKBUILDER_B	9	CHUNK_09W_XYZ_SALES_ORDERLINE_DIM.qvd	1709395	8.5	OK
CHUNKBUILDER_B	10	CHUNK_10W_XYZ_SALES_ORDERLINE_DIM.qvd	110224	0.5	OK
CHUNKBUILDER_B	11	CHUNK_11W_XYZ_SALES_ORDERLINE_DIM.qvd	222459	1.1	OK
CHUNKBUILDER_B	12	CHUNK_12W_XYZ_SALES_ORDERLINE_DIM.qvd	506968	2.5	OK
CHUNKBUILDER_B	13	CHUNK_13W_XYZ_SALES_ORDERLINE_DIM.qvd	3545330	19.8	OK
STITCHER_DIM	99	W_XYZ_SALES_ORDERLINE_DIM.qvd	11117500	0.8	FINAL

Open the Stitcher app after a run, and you see every chunk from both builders plus the final QVD: one table, one glance. You can validate the results against your source DB tables.

Additional tips

How to Partition Your Table

The WHERE clause strategy depends on your table. What works well per my experience:

Date columns (e.g.,`W_UPDATE_DT`): Natural for time-series data. Group by year, quarter, or month, depending on row density. Don't forget the NULL bucket — `WHERE W_UPDATE_DT IS NULL` is often a surprisingly large chunk.

Integer surrogate keys (e.g., `ETL_PROC_WID`): Good for fact tables. Divide the min-max range into equal-width buckets. The risk: row density isn't always uniform. Always run a quick distribution query first before you proceed.

What to avoid: String columns (no range comparisons), non-indexed columns (WHERE clause without an index = full table scan per chunk = slower than a single load).

Gotchas I Hit Along the Way..

QvdNoOfRecords on missing files: I originally used the function`QvdNoOfRecords()` in the stitcher to pre-check each chunk file before loading. If the file doesn't exist, this function doesn't return -1 cleanly: it returns a value that breaks IF comparisons with a parsing error (`Unexpected token: '<'`).

The Fix: remove the pre-check entirely. If both builders completed via QMC task chaining, the files exist. Just load them directly.

Empty chunks are valid: If a WHERE clause returns zero rows, `STORE` creates a valid empty QVD. The stitcher's CONCATENATE handles empty QVDs without error. Don't skip them: skipping creates a gap in your chunk numbering that makes debugging harder when you really need to.

Oracle connection sessions: Two parallel builders = two Oracle sessions. If your DBA monitors concurrent connections, give them a heads-up. If the database can't handle two long sessions, stagger the builder task start times by 10–15 minutes.

Chunk sizing: Start with your timeout ceiling and work backward. If your ceiling is 40 minutes and your fastest chunk loads 1.5M rows in 20 minutes, your slowest chunk (same row count, different data distribution) might take 35. Aim for chunks that give you headroom: better to have 13 fast chunks than 8 chunks where 2 are borderline.

When You Need This

This is a case when working with large tables, especially for full loads for data fixes/corruption or during migration projects with a one-time historical load. Couple them to the scenarios below to find your fit:

- Your full load has failed more than once due to timeouts or connection drops

- Your Oracle DBA has asked you to limit session duration

- You're loading across a WAN or VPN

- You need a verifiable audit trail for data completeness

- Your table exceeds 10M rows, and your Qlik server is memory-constrained

Files and Naming

For reference, here's how the file landscape looks after a successful run. All files live in the same lib:// path (separable for production):

CHUNK_01_SALES_ORDERLINE_DIM.qvd    ← Builder A

CHUNK_02_SALES_ORDERLINE_DIM.qvd

...

CHUNK_07_SALES_ORDERLINE_DIM.qvd

CHUNK_08_SALES_ORDERLINE_DIM.qvd    ← Builder B

...

CHUNK_13_SALES_ORDERLINE_DIM.qvd

LOG_BUILDER_A.qvd                    ← Builder A validation log

LOG_BUILDER_B.qvd                    ← Builder B validation log

LOG_MASTER_VALIDATION.qvd            ← Stitcher master log

W_XYZ_SALES_ORDERLINE_DIM.qvd  ← Final stitched QVD

The chunk QVDs can be cleaned up with a PowerShell external program task chained after the stitcher or left in place for debugging.

This approach turned a 50% failure rate into a reliable, hands-off scheduled task with a built-in QA layer. The total script across all three apps is under 200 lines each. Nothing too fancy: just the right sequence of simple operations with a hard stop when things take too long.

Hope this saves someone a few late nights staring at a progress bar!