The Ingestion Engine: Power Query M, Query Folding & Source Pushdown

# The Ingestion Engine: Power Query M, Query Folding & Source Pushdown Power Query is the data extraction and transformation gateway of the Microsoft Power Platform. While end-users interact with its graphical interface, every transformation compiles into **M**—a purely functional, case-sensitive, order-independent formula language. In high-scale enterprise reporting, understanding how the M evaluation engine delegates transformations to source systems is the boundary between sub-second dashboards and fatal gateway timeouts. --- ## 1. The Power Query Evaluation Container & Storage Modes When connecting Power BI to external databases, you must determine how data is physically held and queried: ``` ┌────────────────────────────────────────────────────────────────────────────────────────┐ │ POWER BI STORAGE MODES COMPARED │ ├───────────────────┬────────────────────────────────────────────────────────────────────┤ │ Storage Mode │ Physical Architecture & Engine Execution │ ├───────────────────┼────────────────────────────────────────────────────────────────────┤ │ Import Mode │ Ingests, structures, and compresses data into the in-memory │ │ │ VertiPaq columnar database. Maximum DAX performance. │ ├───────────────────┼────────────────────────────────────────────────────────────────────┤ │ DirectQuery Mode │ Zero data cached locally. Visual interactions generate dynamic │ │ │ T-SQL/native queries executed directly on the source database. │ ├───────────────────┼────────────────────────────────────────────────────────────────────┤ │ Dual Mode │ Hybrid mode for dimensions in composite models. Acts as VertiPaq │ │ │ cache for Import visuals and folds locally for DirectQuery joins. │ ├───────────────────┼────────────────────────────────────────────────────────────────────┤ │ Direct Lake Mode │ Modern Fabric standard. Directly reads Delta Parquet tables on │ │ (Microsoft Fabric)│ OneLake into VertiPaq RAM without ETL refresh or SQL translation. │ └───────────────────┴────────────────────────────────────────────────────────────────────┘ ``` --- ## 2. VertiPaq's 3-Stage Columnar Compression Pipeline The VertiPaq in-memory engine achieves dramatic memory reductions (typically 10x to 20x) through three sequential encoding passes: 1. **Value Encoding:** Applied to narrow-range integer columns. VertiPaq calculates the mathematical offset from the column minimum (`EncodedValue = ActualValue - MinValue`). This minimizes the bit-width required per row and allows the Formula Engine to perform mathematical aggregations directly on encoded values without dictionary lookups. 2. **Hash / Dictionary Encoding:** Applied to text strings, dates, and non-linear numbers. VertiPaq builds a distinct lookup dictionary and replaces raw values with zero-based bit-packed integer IDs. Dictionary size is determined by column **cardinality** (the count of unique values), which is why high-cardinality strings (GUIDs, timestamps) bloat model size. 3. **Run-Length Encoding (RLE):** After dictionary encoding, VertiPaq scans down the physical column and replaces contiguous identical values with `(ValueID, RunCount)` pairs. **Table sort order directly impacts RLE efficiency:** sorting a table by low-cardinality dimensions before higher-cardinality ones clusters identical values, reducing RAM consumption by up to 90%. --- ## 3. The Clean Ingestion Playbook & Roche's Maxim > **Roche's Maxim of Data Transformation:** *"Data should be transformed as far upstream as possible, and as far downstream as necessary."* Enterprise BI architects enforce three foundational ingestion principles: ### A. Upstream Pushdown vs. Calculated Column Waste Never use DAX calculated columns to clean data, parse text strings, or concatenate keys. Calculated columns are computed during data refresh and stored uncompressed in VertiPaq memory without the benefit of run-length sorting optimization. Perform all cleaning inside SQL views or Power Query steps. ### B. The Canonical Unpivot Pattern Operational spreadsheets and legacy reporting systems frequently export cross-tabulated matrices where time intervals (e.g. Month columns: `Jan`, `Feb`, `Mar`) stretch horizontally across the screen. Tabular engines cannot create relationships across dynamic columns. Developers must select attribute columns (e.g. `AccountID`, `Department`) and execute `Table.UnpivotOtherColumns` to normalize into relational `[Attribute, Value]` rows before loading into the model. ### C. Strict Type Coercion & Schema Hygiene Leaving column data types as `Any` or storing integer foreign keys as decimal floats increases dictionary overhead and disables Value Encoding. Always explicitly cast types in the initial ingestion step. --- ## 4. Query Folding Mechanics & Breaking Steps **Query Folding** is the abilit