Incremental Load on a 260M-Row Table with Silent Deletes
Order lines land in a SQL table of 260M rows, with roughly 1.8M rows inserted or updated per day. The nightly window is 90 minutes; a full extract QVD currently takes 68 minutes and the transform another 25, so the chain overruns most nights. Cancelled and hard-deleted orders keep appearing in reports for weeks. The script does a full reload every night and then applies a customer attribute mapping with ApplyMap across all 260M rows. ModifiedDate exists and is indexed, but it is NULL for about 0.4% of rows, and the source system occasionally backdates it when a correction is posted.
1. Design the incremental load. Specify the watermark, the lookback, how you deduplicate, and how you remove cancelled and hard-deleted rows.
Read the worked answer
Watermark. Peek('MaxModified', -1, 'LoadLog'), stored in a small QVD or log table that persists across reloads. Never keep the watermark only in a script variable or in an included file - it must be a loaded value, or the first failed reload silently resets you.
Extract. WHERE ModifiedDate >= $(vWatermarkMinusLookback) OR IsNull(ModifiedDate). The lookback is not optional: you already know the source backdates corrections, and transaction boundaries plus clock skew mean the same thing. Three days is the starting point; measure by comparing a 3-day and a 7-day delta against a full reload before you settle.
Deduplicate. Concatenate the delta onto the existing QVD, then keep one row per key at the latest ModifiedDate: either a resident load ordered by key and ModifiedDate desc with a preceding WHERE NOT EXISTS(Key), or a join to a Group By Key, Max(ModifiedDate) table. Whichever you pick, test it with a key modified twice in one delta - the 'first row wins' pattern is load-order sensitive and has silently kept stale rows in many apps.
Deletes. A deleted row produces no delta row, so nothing will ever remove it. Pick explicitly from: (a) a soft-delete flag in the source - best; (b) a monthly key-set comparison, anti-joining the full key list against the base to flag vanished rows; (c) a scheduled full reload. Do not leave this undecided - the cancelled-order complaints are exactly this gap, and they cost more trust than the overrun does.
Sequence. Extract first to save the window; put all enrichment and business rules in the transform layer.
2. The ApplyMap over 260M rows is a major cost. Where should it move, and how does load order - preceding loads, resident loads, WHERE clauses - change the cost?
Read the worked answer
Move it to the transform layer, and move it after the deduplication. ApplyMap runs once per row, per call. Applying it in the extract means you pay for every row in the 260M-row source, including the rows that the deduplication is about to discard and the rows the delta brings in only to overwrite older versions. Enrich the surviving delta row, not the whole history.
Load order is a correctness issue as much as a cost issue. In a Qlik load chain the statements execute bottom-up: the innermost load reads the source and applies its own WHERE first, the preceding load transforms the result, and the outermost sees the finished table. So a WHERE written at the bottom filters before the ApplyMap above it executes - put the filter as low as it will go and the mapping as high as it can go.
Second, enrichment before deduplication can map the same key to two different attribute values, and which one survives depends on row order. Enrich after the dedupe and that ambiguity disappears.
Also check. A GROUP BY or ORDER BY on a resident load of this size is a sort - it is disk-bound and is often where the 68 minutes actually goes. If you need the sort, sort the delta, not the concatenated 260M rows. And if the mapping table is small, consider a plain left join over the reduced set instead of ApplyMap; on this volume the join is usually cheaper, but measure both rather than assuming.
3. What happens to the 0.4% of rows with a NULL ModifiedDate under your design, and what is the failure mode you must build monitoring for?
Read the worked answer
Under a plain ModifiedDate >= watermark predicate those roughly 1M rows are excluded at the first load and then excluded forever - they never appear, never update, and nothing in the app says so. That is the worst class of defect: a permanent, silent hole.
Handling. (1) Do a one-time full load to establish a correct base, so the history is complete from the start. (2) In the delta, include NULL-modified rows that do not already exist in the base (IsNull(ModifiedDate) plus a key anti-join), which captures new rows of that type without re-reading all 1M every night. (3) Accept that a NULL-modified row which changes value will not be detected, and raise it as a source-system defect rather than trying to engineer around it forever.
Monitoring. Three cheap checks, run every reload: published row count of the base versus a Count(*) from the source for the same filter; count of rows added by each delta (a delta of 0 or of 30M is both wrong); and a max(ModifiedDate) reasonableness test against the reload time. Fail the task on an out-of-range result rather than publishing, because the symptom - users quietly seeing a stale order - is indistinguishable from a business problem.