topic Re: Delta Tables - Query folding? in Data Engineering

Delta Tables - Query folding?

frithjof_v — Sun, 01 Sep 2024 17:32:21 GMT

Hi,

I have created a Delta Lake table in the Files section of my Lakehouse.

I can connect to this table like this in Power BI Desktop:

Here's the M code:

Will the query fold?

I mean, if I apply some transformation steps e.g. filters or choose columns in Power Query, will those operations be foldable?

It doesn't seem to fold, based on the query folding indicator in Power BI Desktop.

The reason why I don't want to query the SQL Analytics Endpoint, is due to the potential for sync delays between Lakehouse and SQL Analytics Endpoint. That's why I connected Power Query directly to the delta table in the "Lake" part of the Lakehouse.

I'm wondering if Power Query can run foldable queries directly on Delta Lake tables (without querying the SQL Analytics Endpoint)?

Ref. also https://blog.fabric.microsoft.com/en-us/blog/read-data-from-delta-lake-tables-with-the-deltalake-table-m-function/

Re: Delta Tables - Query folding?

Anonymous — Mon, 02 Sep 2024 05:41:55 GMT

Hi @frithjof_v ,

Currently, Power Query does not support query folding for Delta Lake tables. This means that transformations like filters or column selections applied in Power Query may not be pushed back to the Delta Lake table, and instead, they are processed locally within Power Query.

In Power Query, Query Folding refers to converting a data transformation step into a single query statement that the source database can execute. This means that Power Query pushes the data transformation step to the data source for execution whenever possible, rather than processing all the data in Power Query.

If you want to know more about query folding see the official documentation below:

Understanding query evaluation and query folding in Power Query - Power Query | Microsoft Learn

If you have any other questions please feel free to contact me.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

Re: Delta Tables - Query folding?

andrewb279 — Wed, 04 Sep 2024 02:36:42 GMT

Chris Webb had a great blog post on this topic.

https://blog.crossjoin.co.uk/2023/12/17/incremental-refresh-on-delta-tables-in-power-bi/

As long as you partition the Delta table and configure your query to filter on the partition column, you can do incremental refresh. In other scenarios I don't believe incremental will work without going through the SQL endpoint.