Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
smpa01
Super User
Super User

INSERT 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.

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
6 REPLIES 6
v-gchenna-msft
Community Support
Community Support

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.

@v-gchenna-msft  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 .

smpa01_0-1700165935450.png

 

 

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.

 

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

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 .

Need more time pls

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Fabric Hack Slide Banner

Hack Together: The Microsoft Fabric AI Global Hack

Learn from experts, get hands-on experience, and win awesome prizes.