A Nightly Chain That Publishes Whatever It Finds
Three source systems feed nine Qlik Sense apps. The nightly chain is fixed-time: 01:00 extracts to QVD (L0), 02:30 transforms (L1), 03:30 app reloads, users arrive at 07:00. Twice last month the 02:30 transform started before the 01:00 extract had finished and the apps published stale figures. Once an extract failed at 01:40, the transform quietly reused yesterday's QVD, and nobody noticed until a user did. Two teams write into the same QVD folder and a file named Sales.qvd from one source was overwritten by another source's job. Nobody can reproduce last quarter's figures as they were reported.
1. Redesign the layer architecture and the run dependency. Be specific about naming, ownership and how a downstream task knows its input is fresh.
Read the worked answer
Layers. L0 raw, one folder per source system, immutable once written. L1 conformed: business rules, conformed dimensions, deduplication. L2 app-level: only what a specific app needs. Crossing a layer boundary is always a new QVD, never an in-place overwrite.
Naming and ownership. L0_ERP_Sales_YYYYMMDD.qvd, source and run date in the file name. One owner per layer, enforced with folder permissions: extract jobs write L0, transform jobs write L1, app developers read only. That single rule removes the overwrite incident - Sales.qvd was two teams treating one shared name as their own.
Freshness. Every QVD carries a LoadDate and, where cheap, a row count. The transform asserts Max(LoadDate) = Today() against its inputs and fails if not. Without this, dependency ordering is still a timing assumption - it just fails less often.
Dependency, not clocks. Fixed times encode 'the extract usually takes 90 minutes'. Trigger the transform on successful completion of the extract jobs it depends on, and the app reloads on the transforms. If the scheduler only supports times, set the transform to start on success of the L0 task chain - Qlik's reload task dependencies do this - and keep the times as a fallback alarm, not as the contract.
2. An extract fails at 01:40. What should the transform do? Argue for a policy rather than a mechanism, then state the mechanism.
Read the worked answer
Policy first. There are two coherent answers and they are not interchangeable. (a) Fail the chain: no app reload, users see yesterday's published version, and the business is told. (b) Publish on stale data with an explicit 'data as of' indicator. Choose per app with the report's consumer. For anything used to make commitments - pricing, replenishment, regulatory reporting - mixed vintages are worse than a day-old complete picture, because a number that looks current and is not cannot be spotted. For a low-stakes operational dashboard, (b) with a loud indicator is acceptable.
Mechanism. Make failure loud by default: check ScriptError and the returned exit code, and let the task fail so the scheduler raises the alert. The specific incident - the transform quietly reusing yesterday's file - happened because nothing asserted the input's date; add the Max(LoadDate) = Today() check so it fails at the point of use, not at 07:00 in a user's inbox.
Never partial-publish. If half the sources loaded, do not publish a half-refreshed app. Either the whole chain succeeds or the previous version stands.
Always show data-as-of. Put the maximum load date on the sheet regardless of policy. It costs one KPI and it converts a support ticket into a glance.
3. How would you make last quarter's published figures reproducible, given that L0 files are currently overwritten nightly?
Read the worked answer
Reproducibility is a retention-and-provenance problem, not a Qlik feature.
Retention. Keep daily L0 snapshots for a rolling window (13 months is a common fit) and add month-end snapshots permanently from those dailies. Storage is cheap relative to the cost of being unable to answer an audit question. If volume forbids daily retention for the largest sources, keep dailies for the current quarter and month-ends forever, and write down that decision.
Provenance. Record the L0 load date in every app, expose it on every sheet, and log which L0 vintages each reload consumed. Then a reported figure can be traced back to specific files - which is the whole of the requirement. Without this, retaining files still leaves you guessing which vintage produced the number.
Script versioning. QVDs alone do not reproduce a report if the transform logic changed. Keep the load scripts in source control with a tag per release, plus the app version that was published. Files plus logic plus vintage equals reproducible.
One caution. Do not try to retrofit this by rebuilding last quarter's numbers from today's source data - the source has moved on, and a rebuild that does not match will undermine confidence in the whole exercise. State that the retention change is forward-looking and start it tonight.