ADF - Fabric Lakehouse Tables as source in Dataflows

[SidTheSloth]

Hi, When using Fabric Lakehouse as a source in ADF dataflows, there is no ability to pass a filter clause back to source to restrict the data flowing into the Dataflows. A similar operation on any regular database works fine.

 We tried using Lakehouse Views as a workaround, they do not seem to be visible as a source in a dataflow either. Lakehouse Schemas are enabled, and could be the reason, but we're trying to avoid having to recreating a lakehouse without Schema support.

This severly impacts the abiltiy to restrict the number of rows flowing into the ADF data flows. We have some very large tables, and this is a problem area for us. Are we missing a trick? Any suggestions or is this a known issue?

[Anonymous]

Hi @SidTheSloth ,

The data can be preprocessed first with the help of an activity in the data pipeline, and then the result is used as a data source for dataflow gen2 to connect the two activities.

Fabric decision guide - copy activity, dataflow, or Spark - Microsoft Fabric | Microsoft Learn

Best Regards,
Adamk Kong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[SidTheSloth]

Thank you, while pre-processing the data to create a new can be a viable workaround, it adds to the processing load and overall run time. Considering parquets with hundreds of millions of rows, this is a lot of time.

I additionally want to clarify this is ADF pipelines not Fabric Data Factory pipelines. As a part of the newly added functionality, Ideally if we were able to filter at source(like we can with other SQL  based sources), that would be the right way to do things. The inability to query views  from dataflows (for Lakehouses with Schemas), compounds the problem.

 Appreciate your time. Thank you.

[frithjof_v · Accepted solution]

Is there a SQL connector in ADF dataflows?

Could you copy the Lakehouses' SQL connection string, and paste it in the SQL connector in ADF dataflow? Sounds like something which would allow for passing filters in the query. 

I'm hoping you won't get issues with SQL Analytics Endpoint update delays.