Why Query Folding Still Matters

Modern analytics platforms have faster engines, elastic compute, and more managed services than older BI stacks, but one old principle remains stubbornly important: do the expensive work as close to the capable data source as practical.

That is the reason query folding still matters for DP-600 work. A Fabric Analytics Engineer Associate may use Power Query in dataflows, semantic-model preparation, or other ingestion paths where transformations can either be pushed to the source or evaluated by the Power Query engine.

Folding is not a magic performance switch, and not every connector or transformation supports it. It is a way to reason about where work happens, how much data crosses the boundary, and why a seemingly innocent step can turn a fast pipeline into a slow one.

Query folding is an execution-location decision

Power Query reads an M expression and determines which transformations can be translated into operations the source understands. When folding succeeds, filters, projections, joins, and other supported work can be executed by the source rather than after a larger dataset has been retrieved.

That can reduce data transfer, memory use, and local processing. It can also take advantage of database indexes, partition pruning, distributed execution, and other capabilities that Power Query itself does not reproduce.

The important mental model is not ‘folded equals good.’ It is ‘where will this operation execute, and is that the best place for it?’

Folding can be full, partial, or absent

A query may fold end to end, fold only until a particular step, or not fold at all. Partial folding is common: early filters and column selection may execute at the source while a later custom transformation runs in the Power Query engine.

That means one unsupported step does not always invalidate every earlier optimization. Inspect the query plan or folding indicators instead of assuming the entire query has the same behavior.

Understanding the boundary is especially useful when a refresh slows after a new transformation is added. The performance change may come from moving a large amount of downstream work out of the source.

Filter early because row reduction compounds

If a source contains ten years of data but the analytical model needs only the last two, filtering after retrieval wastes bandwidth and processing. When the connector supports folding, an early filter can let the source eliminate irrelevant rows before they move.

Early column selection has a similar effect. Removing wide text or binary fields that are never used reduces data movement and later transformation cost.

These are core ideas in ETL profiling and preparation: understand the data volume, shape, and quality before building a transformation sequence that blindly processes everything.

A single step can change the entire cost profile

Developers often add a custom function, buffer operation, or transformation because it makes the M logic easier to express. If that step prevents later operations from folding, the query may begin retrieving far more data than before.

The change can be difficult to notice in a small development sample. It becomes obvious only when the production source contains millions of rows or when refresh concurrency increases.

Treat folding status as part of code review for important queries. A transformation that changes execution location deserves the same scrutiny as a new join or a new source.

Native queries can help and can also create trade-offs

In some scenarios, teams write a source-native SQL query rather than rely on Power Query to generate one. This can provide precise control over source execution, but it can also reduce the ability of later Power Query steps to continue folding unless the connector and function support folding over that native query.

The decision should be based on maintainability and execution behavior. Hand-written SQL is not automatically faster; generated folding is not automatically simpler.

Keep the source-side logic understandable and owned. A report refresh should not depend on an undocumented SQL fragment that only one developer can safely modify.

Dataflows make folding an operational issue, not just a desktop concern

Power Query Online uses the same broad optimization principle in dataflows and other Fabric experiences. When transformations run on scheduled or shared infrastructure, inefficient non-folded work can consume capacity and increase refresh duration across many assets.

A query that is acceptable for one analyst’s desktop may be expensive when executed every hour for a shared data product. Production scale changes the tolerance for unnecessary data movement.

That connects folding with broader performance optimization: efficient execution is partly about reducing work before asking for more compute.

Folding does not replace data-model design

Pushing transformations to a source can make ingestion faster, but it does not guarantee that the resulting analytical model is good. A perfectly folded query can still produce a wide table with mixed grain, duplicated dimensions, or weak business semantics.

Use query folding to make data preparation efficient, then apply sound data modeling so the semantic layer remains understandable and performant.

The two concerns reinforce each other. Reducing rows and columns early improves data movement; shaping facts and dimensions correctly improves downstream querying.

Incremental refresh depends on foldable filters in many sources

Incremental refresh commonly uses RangeStart and RangeEnd parameters to divide a large table into time-based partitions. For many relational sources, the date filter needs to fold so that each partition retrieves only the intended range rather than pulling the entire source and filtering locally.

This is why incremental refresh design should validate folding before deployment. A policy can be configured correctly in the UI and still perform poorly if the source query does not push the time predicate down.

The operational symptom may be long refresh duration or heavy source load rather than an obvious configuration error.

The right goal is observable, efficient execution

Do not turn query folding into a purity test. CSV files have no query engine to push work into. APIs may expose limited server-side filtering. Some transformations genuinely need to run in Power Query.

The broader discipline of data analytics is choosing an execution path that fits the data and workload rather than forcing every source into the same pattern.

Inspect what folds, reduce data early, test realistic volumes, and measure refresh behavior. The useful outcome is an efficient pipeline whose execution location is understood—not a green folding indicator for its own sake.

Development previews can hide folding problems because the Power Query editor often works with a limited preview rather than the full production volume. A step that feels instantaneous during authoring may become expensive during refresh. Validate important queries against production-scale data or a representative subset whose volume and distribution expose the real cost.

Query folding also affects source governance. Pushing a complex transformation into a shared operational database can reduce data movement but increase load on a system whose primary responsibility is transactions. Coordinate with source owners, use read replicas or analytical endpoints when appropriate, and schedule expensive extraction so optimization in one layer does not create instability in another.

Privacy levels and data combination behavior can influence how Power Query evaluates multi-source queries. When a query joins sources with different boundaries, the engine may need to protect data movement in ways that affect folding and performance. Multi-source transformations deserve explicit testing rather than assuming the same behavior as a single relational source.

Reusable functions can improve maintainability but should be designed with folding in mind. A custom M function invoked once per row can turn a set-based source operation into thousands of repeated evaluations. When possible, express transformations in a way the connector can translate as a set, or isolate the non-foldable logic after aggressive row reduction.

Diagnostics should focus on the actual native query and the step where execution changes. Native query viewing, folding indicators, source query monitoring, and refresh timings can show whether a filter or join was pushed down. A slow refresh becomes much easier to fix when the team knows whether the source returned ten thousand rows or ten million.

Finally, remember that query folding is connector-specific. A transformation that folds against SQL Server may not fold against a different source. Porting an M query between connectors therefore requires performance retesting even when the functional result is identical.

Step ordering can preserve or destroy source-side efficiency. Applying a foldable filter before a non-foldable custom transformation often allows the source to reduce the rowset first; reversing those steps may force the custom transformation to evaluate across the entire source result. Functionally identical M scripts can therefore have very different cost profiles.

Query folding should also be part of change review when source systems upgrade connectors, views, or data types. A query that folded last quarter may behave differently after a connector or source change. Periodic refresh baselines and native-query inspection help detect that regression before users experience a much longer processing window.

Query folding still matters because data movement and execution location still matter.

Faster platforms have not repealed that constraint. When teams understand which operations run at the source and which run in Power Query, they can design transformations that scale with data volume instead of discovering the boundary during production refreshes.