Forum Discussion

ahmedshalabyy12's avatar
ahmedshalabyy12
Helper V
1 year ago
Solved

Query folding

Dears,   How i can verify that my query is folded  that's the query : let // Define your start and end range RangeStart = #datetime(2025, 8, 1, 0, 0, 0), RangeEnd = #datetime(2025, 8, 10, 0,...
Need Help
  • rohit1991's avatar
    rohit1991
    1 year ago

    Hi ahmedshalabyy12 

    How to verify your query is folded (Databricks + Value.NativeQuery):

    1. Value.NativeQuery already sends SQL to Databricks. Folding only matters for steps after that step.

    2. In Power Query, right-click the last step >> View Native Query.

      • Clickable = still folded to SQL.

      • Greyed out = folding broke earlier.

    3. Do a quick litmus test: add Keep Top Rows >> 5 (or filter a date). Recheck View Native Query.

      • If you see LIMIT 5 (or your WHERE clause) in the SQL, it’s folded.

    4. Cross-check in Databricks >> SQL >> Query History while preview/refresh runs.

      • Your server log should show the final SQL with your filters. If it shows SELECT * and filters aren’t there, they were applied locally.

    5. Remember: many steps break folding (e.g., Table.Buffer, custom M, merges with non-foldable sources). Put them after you’ve narrowed data.

Madalina2801's avatar
Madalina2801
Advocate II
1 year ago

Hi,

 

I'm facing the same issue, not sure if query folding works or not. I can see in query history the query runs in a few seconds but the incremental refresh is taking a lot (almost two hours).

Is this a known issue related to the Databricks connector?

mmarcoux's avatar
mmarcoux
Frequent Visitor
1 year ago

Same issue, using the databricks connector, shows support for folding in UI, but incremental refresh says the query is not fully foldable. Disabling full folding in the incremental refresh setting in dataflow gen2 makes the run times go from 30m to 5hrs, at least for my workloads.

  • Madalina2801's avatar
    Madalina2801
    Advocate II
    1 year ago

    I managed to reduce the refresh duration from 2h to 20m after optimizing the dataset (I removed some high cardinality columns). In SSMS I saw the partitions were processed in 10m but the total refresh duration was 2h. It turned out that the problem was slow Power BI post-load processing. Even if the query runs fast on the source side in Databricks, the VertiPaq often recompresses column dictionaries across all partitions in the table, especially for high-cardinality or long text columns.