DataFlow Incremental Refresh

[Kaatiiaa]

Im trying to incrementally append data in Lakehouse using DataFlow (as described here: https://learn.microsoft.com/en-us/fabric/data-factory/tutorial-setup-incremental-refresh-with-datafl...)

I did everything in the same way as described.

If I open DataFlow I can see in preview that it works as my main query/table is filtered according to the filter based on maximum order_id (the result of the second query).

The problem is when I want to refresh the dataflow. I get an error message "We cannot use operator < for Table and Number types"

Any idea what is wrong? I guess that if I can see in preview that the table is filtered according to maximum order_id so data type is ok (number). The filter is set exactly as in the example described on LearnMicrosoft site.

Thank you

The whole text of error message:

Error Code: Mashup Exception Expression Error, Error Details: Couldn't refresh the entity because of an issue with the mashup document MashupException.Error: Cannot insert table., Inner exception: We cannot use operator < for Table and Number types., basic error: We cannot use operator < for Table and Number types. Details: Reason = Expression.Error;Message = We cannot use the < operator for Table and Number types.;Detail = [Operator = "<", Left = error "Microsoft.Mashup.Engine1.Runtime.ValueException: [Expression.Error] No value specified.#(cr)#(lf) at Microsoft.Mashup.Engine1.Language.ValueCreator.CreateValueForThrow(IThrowExpression throwExpr)#(cr)#(lf) at Microsoft.Mashup.Engine1.Language.ValueCreator.<>c__DisplayClass23_0.<CreateValueForRecord>b__0(Int32 index)#(cr)#(lf) at Microsoft.Mashup.Engine1.Runtime.RecordValue.DemandRecordValue.get_Item(Int32 index)#(cr)#(lf) at Microsoft.Data.Mashup.ProviderCommon.MashupResource.TryGetValue(Func`1 getValue, IValue& value, String& errorMessage)#(cr)#(lf)Record", Right = 2619 65];Message.Format = We cannot use the #{0} operator for types #{1} and #{2}.;Message.Parameters = {"<", "Table", "Number"};Microsoft.Data.Mashup.Error.Context = User GatewayObjectId: 4c147d88-9572-4e34-8a5b-4fe1391a873e (Request ID: 1318301e-5b5c-44a4-9ea9-30d02261f7e1).

[Kaatiiaa]

Which column do you mean? I am using Order ID to filter rows for incremental refresh. It is numeric column. Thank you 

[3CloudThomas]

Incremental refresh has to be on a datetime column

[Kaatiiaa]

According to this tutorial (https://learn.microsoft.com/en-us/fabric/data-factory/tutorial-setup-incremental-refresh-with-datafl...) in Datafow gen 2 you may achieve something similar to incremental refreshl not on date column and this tutorial shows how. I am trying to do the same thing 

[3CloudThomas]

You are correct, I did not know that. In semantic models we use DataTimes.

[miguel]

Check that your IncrementalOrderID query doesn't have staging enabled. It more than likely has and thats why its transforming that query into a table instead of leaving the value as a scalar. Right click the query and make sure that it doesn't have staging enabled.

[Kaatiiaa]

My IncrementalOrderID query doesn't have staging enabled. I doublechecked. Do you think it matters if Orders query has staging enabled? I think that when the tutorial was written the default for query was to have staging enabled. Now when you create new query it has staging disabled by default. So when I created and loaded whole table using Order uery the staging on this query was disabled. Now any combination of staging disabled/enabled on those two queries produces the same error ("We cannot use operator < for Table and Number types") but the details a different.

When both queries have staging disabled:

- Acitivity "ORDERS_WriteToDataDestination" failed

When OORDERS query has staging enabled and IncrementalOrderID query has staging disabled.:

- Table ORDERS failed

- Acitivity "ORDERS_WriteToDataDestination" idle

Any idea how to fix this?

Thank you!

[frithjof_v]

What does your filter statement look like in your M code?

If I understand correctly, your IncrementalOrderID is the query which returns the current Max ID from your destination table.

The IncrementalOrderID query should return only a scalar value (only a number, no columns and no rows). Not a table. And it should have disable staging.

Your Orders query (the new table data, which you will write to the destination by using Append) can also have staging disabled. But it will not fail even if you have staging enabled here.

The Orders table needs to have a column which contains the OrderID. You need to filter the Orders query by using the IncrementalOrderID query and the [OrderID] column of the Orders query.

Your filter statement in the Orders query should be similar to this M function:

Table.SelectRows(#"Orders", each [OrderID] > IncrementalOrderID)

Here, replace #"Orders" with the name of the previous step in your M code inside the Orders query.

[Kaatiiaa]

Yes, my IncrementalOrderID  (acuatlly in my model it is Event ID not Order ID) is the query which returns the current Max ID from my destination table. I believe it rerurns scalar as I did it excatly as in the tutorial. See the screen:

M code of my filter statement (filter step): You can see on the screen that the filter works as there a currently 25 rows with event_id greater than the IncrementalEventID (maimum Event ID). During the refresh those 25 rows should be added to my table. The destination for this query is the same existing table and the Update method is Append.

The source of my table is on-premise Orcale database but I guess surce type should not matter here - refreshing the table using Replace method works fine.

Any ideas what is wrong with this incremental refresh pattern? Thanks.

[frithjof_v · Accepted solution]

The setup seems good to me.

"refreshing the table using Replace method works fine."

Are you saying this exactly same dataflow runs successfully if you choose Replace method, it only fails if you choose Append method?

So your M code is exactly the same in both cases. The only change is the destination settings?

So the issue seems to be related to the destination settings?

However, using Replace method will erase all the old data so you don't want to use it in your case. You want to Append to keep the old data and add new data.

Btw, I see there are a couple of known issues with Data Factory <> Oracle.

https://learn.microsoft.com/en-us/fabric/get-started/fabric-known-issues

Could the issue be relates to the number format? Could you try to force the number format to whole number (Int64)?

(Please do the testing in a test workspace in your Fabric, not production workspace.)

Tbh I'm confused why you are getting an error.

Just to make sure, the IncrementalEventID query has disabled staging, and this query does not have a destination, right?

Perhaps you could create a support ticket to have the support team look at the case.

[Kaatiiaa]

“Just to make sure, the IncrementalEventID query has disabled staging, and this query does not have a destination, right?”

That was it! The IncrementalEventID query had a destination set - when doing everything as in the tutorial it says to copy the original query and change it to get the max ID. Apparently it copies the destination somehow – that is what was set:

When I removed the destination by clicking “x” it all now works fine. They mentioned in the tutorial to disable staging but it’s a shame they don’t mention to remove the destination from this query.

Thank you very much for being such an investigator! That helped a lot.

[3CloudThomas]

It message( We cannot use operator < for Table and Number type) sounds like the column type is numeric and not DateTime