Forum Discussion
Community ChampionINSERT in fabric
How can I possibly go about performing INSERT in fabric.
To elaborate, I am querying sql db tbl with df gen2 connector and creating a Lakehouse tbl. The tbl contains a field called ID (integer). How can I only append only the new rows to the LH tbl based on bringing only the delta rows (max ID of sql tbl today - max ID of dataflow table yesterday)
Such as,
Select * from db tbl where ID > maxID dataflow yesterday and ID<= max ID sql tbl today
Is it possible to achieve? To be very clear I am not looking for a power bi incremental refresh approach as there is no datetime column.
To be very precise, I am asking if it is possible to run today, the yeterday's instance of dataflow table (contains all the IDs upto yeterday; i.e. dataflow instance) and compare it today's max ID in sql (sql instance) and query only the new IDs and append them to the lakehouse table (lakehouse instance) that contains the sql table instances upto yesterday (queried through dataflow). There is no UPDATE of rows required cause all the values of past IDs are not editable by the data entry operators.
6 Replies
Hi smpa01 ,
Thanks for using Fabric Community.
As I understand you are looking to implement incremental load using dataflow gen 2.
You can refer to this document inorder to get some idea: Pattern to incrementally amass data with Dataflow Gen2 - Microsoft Fabric | Microsoft Learn
Hope this is helpful. Please let me know if you have further queries.
smpa01Anonymous thanks for the link.
If you read through it, you will come across the below screenshot. What it is basically saying, query the full SQL table (select * from db tbl) and then utilize power query to pass on the MAX ID (from the exsitng dataflow) to filter only the rows that are in today's data set that are > yesterday's MAX ID. So if my db table has 1M+ rows , I need to send a query to sql db to query 1M+ rows everyday so that PQ can utilize filter to strain out only the delta rows (let's suppose 10 rows today) that I need to append to the lakehouse table. I want to send a query to sql db select * from db where ID > maxID dataflow yesterday and ID<= max ID sql tbl today) that can only return 10 rows without needing PQ to filter on the subsequent steps and available to me to append to the existing lakehouse table .
If I need to query the full sql table only to use filtering through PQ then it defeats the purpose. I should be able to author a custom SQL query with custom filters in place and send that query to SQL. I am writing native SQL query ( as oppose to transforming sql table using PQ ) and query folding is not avialble to me (and I can sacrifice that but not sending native sql query to db).
How do I resolve this?
Thank you in advance.
Hi smpa01 ,
Inorder to avoid query the full sql table, I suggest you create a new table latest_order_id (log table to store which is going to store the latest order_id till today). By this you can avoid quering entire table by simply fetching the order_id from other table.
Every time when you try to do an incremental load, try to update the existing value in latest_order_id table with latest order id from the latest data.
Hope it is helpful. Please let me know incase of further queries.
