Forum Discussion

pbi_taken's avatar
pbi_taken
Icon for Helper I rankHelper I
1 year ago
Solved

Write sql query when source is a lakehouse table in dataflow gen2

Hi,   I'm wondering if it's possible to write sql query to filter data with dataflow gen2 when using a table from a lakehouse in fabric? Any good ideas on how to solve this? Thanks!
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi pbi_taken ,

     

    You can write sql queries to filter the tables in lakehouse.
    First you need to create a Dataflow Gen2 then select lakehouse as the destination, publish it and find that lakehouse then create a new notebook and write sql query to filter the tables. If you want to save the filtered results you need to use python query.

     

    %%sql
SELECT *
FROM lakehouse_table
WHERE column_name = 'desired_value'

     

    %%pyspark
# Read the original table
df = spark.read.table("original_table_name")

# Apply the filter
filtered_df = df.filter(df["column_name"] == "desired_value")

# Save the filtered data as a new table
filtered_df.write.mode("overwrite").saveAsTable("original_table_name")

    Best Regards,

    Neeko Tang

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

Poojara_D12's avatar
Poojara_D12
Icon for Super User rankSuper User
1 year ago

Hi pbi_taken 

Yes, you can filter data in a Dataflow Gen2 using SQL when working with a Lakehouse table in Fabric. Here's how you can approach it:

Steps to Achieve SQL Filtering:

  1. Use SQL Queries in Dataflow Gen2:

    • Dataflow Gen2 allows you to work with different data sources, and if your Lakehouse table is available in the dataflow, you can use SQL-based transformations to filter and manipulate data.
    • To filter data from a Lakehouse table, you can either write SQL queries directly if the source supports SQL, or use the SQL-based transformation within the Dataflow Gen2 interface.

  2. Create a Dataflow:

    • In Fabric, navigate to the Dataflow Gen2 section.
    • Add a Lakehouse as a data source.
    • Select the table from the Lakehouse that you want to query.

  3. SQL Query Transformation:

    • If the Lakehouse is connected through a SQL-based source (like SQL Server, Azure Synapse, or another relational database), you can use the SQL transformation in the dataflow to filter data.
    • In the dataflow editor, use the SQL query or DirectQuery option to write your SQL filter condition.

  4. Custom SQL Filter:

    • You can use SQL WHERE clauses to filter your data within the query itself.
    •  

 

SELECT *
FROM lakehouse_table
WHERE [column_name] = 'desired_value'

 

  1. Pushdown Filters:

    • Some sources support pushdown filters, where the SQL query is pushed directly to the source system (Lakehouse in this case), which improves performance.
    • Make sure that your Lakehouse source supports pushdown, which should automatically apply the filter at the source.

Recommendations:

  • Use DirectQuery if supported by your Lakehouse setup, as it allows SQL filtering directly in the source system and avoids pulling unnecessary data.
  • Write SQL within the dataflow query editor to filter the dataset before it’s loaded into Power BI or other destinations.Example SQL query:

Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Poojara
Data Analyst | MSBI Developer | Power BI Consultant
Please Subscribe my YouTube for Beginners/Advance Concepts: https://youtube.com/@biconcepts?si=04iw9SYI2HN80HKS