Data Loading and Transformation: Load Script, ETL Patterns, Incremental Loads, Resident and Preceding Loads
Execution order matters
The script runs top-to-bottom, but within a single table the preceding load (the block written above the FROM) is evaluated last: the bottom statement executes first, then each load above wraps that result. This is why a preceding load can rename or calculate fields produced by the statement below it.
RESIDENT loads from a table already in memory - cheap, and the standard way to avoid re-reading a source. DROP TABLE frees memory but is irreversible in that reload, so drop only after every table that needs the data exists. Mapping tables loaded with MAPPING LOAD and applied with ApplyMap are faster and lighter than a join, and crucially they never multiply rows; pass the third parameter ('Unknown') to handle unmapped keys.
For filtering, WHERE removes rows from the table being loaded, while KEEP/LEFT KEEP filters one table using another (useful for dropping rows whose key is absent from a master list). EXISTS() tests a field value against an already-loaded table. CROSSTAB pivots wide data to tall, and a generic load unpivots multiple measure columns.
Incremental pattern: load the QVD, read the maximum key or timestamp with Peek('Key', -1, 'Table'), load only newer source rows, CONCATENATE them onto the QVD table, and STORE back. Updated or deleted records will never appear in a pure greater-than load, so use a lookback window plus EXISTS on a unique row key.
Trap: a WHERE clause that applies a function to the loaded field, or any transformation, forces the slow unoptimized QVD path.