Forum Discussion
Community ChampionINSERT in fabric
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.
Community ChampionAnonymous 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.Hi smpa01 ,
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond back with the more details and we will try to help .Hi smpa01 ,
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet .
In case if you have any resolution please do share that same with the community as it can be helpful to others .
If you have any question relating to the current thread, please do let us know and we will try out best to help you.
In case if you have any other question on a different issue, we request you to open a new thread .