topic Re: Dataflow - Append in Dataflow

Dataflow - Append

tan_thiamhuat — Sun, 10 Aug 2025 13:48:43 GMT

I have this data pipeline which contains the Dataflow Gen2 and gets its inputDate for its API data:

The Dataflow uses Update method as Append:

That is, I want the SG_Rainfall data to be appended from StartDate (2025-05-01) to EndDate (2025-05-10).

However, when I query its data, I only has the last day data, that is only on 2025-05-10. Why?

The whole of the pipeline was executed successfully as below:

Re: Dataflow - Append

miguel — Sun, 10 Aug 2025 22:59:15 GMT

A few things to check:

Go into the 'Recent runs' of the Dataflow and check those runs. In the parameters section of the Dataflow, check that the value passed is the value expected by your parameter. You can copy the value from the recent runs dialog and then paste it once you have your Dataflow open and the parameter just to see how things would run.
- Check that you're getting the correct response from the API
Try to save the Dataflow again and redo the operation. You can also check with the REST API (Items - Get Dataflow Definition - REST API (Dataflow) | Microsoft Learn) the contents of your mashup.pq file to see if the logic for your data destination query and its settings are correct (as outlined here Dataflow Gen2 data destinations and managed settings - Microsoft Fabric | Microsoft Learn)

From the screenshot we're missing what dates were passed and we don't also have sufficient information as to how the API for https://api.data.gov.sg/v1/environment/rainfall might work when you pass a date that is perhaps in the future- if you pass a date from December 2025, will it give you an empty result and perhaps thats why you're getting it? are the results of the notebook creating values that are not in the range that is expected?

Re: Dataflow - Append

tan_thiamhuat — Mon, 11 Aug 2025 00:10:35 GMT

"Activity name","Activity status","Error","Run start","Duration","Input","Output"
"Dataflow1","Succeeded","","8/10/2025, 8:52:20 PM","51s","{
""dataflowId"": ""db6968e7-d4db-46e4-8406-fd7857e5e5d8"",
""workspaceId"": ""ca4821de-7641-44ea-9a6d-4311320a74c4"",
""notifyOption"": ""NoNotification"",
""dataflowType"": ""DataflowFabric"",
""parameters"": {
""inputDate"": {
""value"": ""2025-05-10"",
""type"": ""string""
}
}
}","{
""jobInstanceId"": ""823f9c67-3347-4cb3-b596-23e05b0f1365"",
""rootActivityId"": ""8c0a284e-ea3c-468e-bc42-bb0fb68930fc"",
""executionDuration"": 47
}"
"Dataflow1","Succeeded","","8/10/2025, 8:51:30 PM","49s","{
""dataflowId"": ""db6968e7-d4db-46e4-8406-fd7857e5e5d8"",
""workspaceId"": ""ca4821de-7641-44ea-9a6d-4311320a74c4"",
""notifyOption"": ""NoNotification"",
""dataflowType"": ""DataflowFabric"",
""parameters"": {
""inputDate"": {
""value"": ""2025-05-09"",
""type"": ""string""
}
}
}","{
""jobInstanceId"": ""082dd81b-610f-4e50-8e65-680b4e467ea7"",
""rootActivityId"": ""507ca1f6-cd0e-4c25-bcd1-588518070800"",
""executionDuration"": 46
}"
"Dataflow1","Succeeded","","8/10/2025, 8:50:39 PM","50s","{
""dataflowId"": ""db6968e7-d4db-46e4-8406-fd7857e5e5d8"",
""workspaceId"": ""ca4821de-7641-44ea-9a6d-4311320a74c4"",
""notifyOption"": ""NoNotification"",
""dataflowType"": ""DataflowFabric"",
""parameters"": {
""inputDate"": {
""value"": ""2025-05-08"",
""type"": ""string""
}
}
}","{
""jobInstanceId"": ""596daeee-1374-43ea-bcf5-45f400a790b4"",
""rootActivityId"": ""9ae9c9f3-e2d3-4269-91f6-357bcb7ad3f8"",
""executionDuration"": 46
}"
"Dataflow1","Succeeded","","8/10/2025, 8:49:49 PM","50s","{
""dataflowId"": ""db6968e7-d4db-46e4-8406-fd7857e5e5d8"",
""workspaceId"": ""ca4821de-7641-44ea-9a6d-4311320a74c4"",
""notifyOption"": ""NoNotification"",
""dataflowType"": ""DataflowFabric"",
""parameters"": {
""inputDate"": {
""value"": ""2025-05-07"",
""type"": ""string""
}
}
}","{
""jobInstanceId"": ""0e1d813c-46bf-4d38-af50-b63b4ef7ca6a"",
""rootActivityId"": ""0c04ec58-8a0d-493b-aeff-2f1b3d01037f"",
""executionDuration"": 46
}"
"Dataflow1","Succeeded","","8/10/2025, 8:48:59 PM","49s","{
""dataflowId"": ""db6968e7-d4db-46e4-8406-fd7857e5e5d8"",
""workspaceId"": ""ca4821de-7641-44ea-9a6d-4311320a74c4"",
""notifyOption"": ""NoNotification"",
""dataflowType"": ""DataflowFabric"",
""parameters"": {
""inputDate"": {
""value"": ""2025-05-06"",
""type"": ""string""
}
}
}","{
""jobInstanceId"": ""8c3ba27f-df34-4e9e-9b5d-c0e6615aba36"",
""rootActivityId"": ""90ea7971-848a-418b-b3a9-c6f8ab695c3a"",
""executionDuration"": 46
}"
"Dataflow1","Succeeded","","8/10/2025, 8:47:58 PM","1m 0s","{
""dataflowId"": ""db6968e7-d4db-46e4-8406-fd7857e5e5d8"",
""workspaceId"": ""ca4821de-7641-44ea-9a6d-4311320a74c4"",
""notifyOption"": ""NoNotification"",
""dataflowType"": ""DataflowFabric"",
""parameters"": {
""inputDate"": {
""value"": ""2025-05-05"",
""type"": ""string""
}
}
}","{
""jobInstanceId"": ""aaf15235-a3b1-4951-bb93-004bfa7bd3c1"",
""rootActivityId"": ""10d3c16b-3743-467a-8b44-3ab0adb23079"",
""executionDuration"": 46
}"
"Dataflow1","Succeeded","","8/10/2025, 8:47:08 PM","49s","{
""dataflowId"": ""db6968e7-d4db-46e4-8406-fd7857e5e5d8"",
""workspaceId"": ""ca4821de-7641-44ea-9a6d-4311320a74c4"",
""notifyOption"": ""NoNotification"",
""dataflowType"": ""DataflowFabric"",
""parameters"": {
""inputDate"": {
""value"": ""2025-05-04"",
""type"": ""string""
}
}
}","{
""jobInstanceId"": ""445b66dd-717a-4bf4-bc56-5dea27e885d2"",
""rootActivityId"": ""8ba4a3ca-53e9-49c2-ad56-1c8b8c0f436b"",
""executionDuration"": 46
}"
"Dataflow1","Succeeded","","8/10/2025, 8:46:07 PM","59s","{
""dataflowId"": ""db6968e7-d4db-46e4-8406-fd7857e5e5d8"",
""workspaceId"": ""ca4821de-7641-44ea-9a6d-4311320a74c4"",
""notifyOption"": ""NoNotification"",
""dataflowType"": ""DataflowFabric"",
""parameters"": {
""inputDate"": {
""value"": ""2025-05-03"",
""type"": ""string""
}
}
}","{
""jobInstanceId"": ""7873aa15-8f63-40c0-8ab8-32bd3d045efe"",
""rootActivityId"": ""15f5ed25-a11f-4499-af23-a6b95cc158a4"",
""executionDuration"": 46
}"
"Dataflow1","Succeeded","","8/10/2025, 8:45:16 PM","50s","{
""dataflowId"": ""db6968e7-d4db-46e4-8406-fd7857e5e5d8"",
""workspaceId"": ""ca4821de-7641-44ea-9a6d-4311320a74c4"",
""notifyOption"": ""NoNotification"",
""dataflowType"": ""DataflowFabric"",
""parameters"": {
""inputDate"": {
""value"": ""2025-05-02"",
""type"": ""string""
}
}
}","{
""jobInstanceId"": ""7f3c4cda-f420-4487-9043-4a8ba6f2fc0b"",
""rootActivityId"": ""c5028936-ece5-4527-a195-96b07d26ef03"",
""executionDuration"": 47
}"
"Dataflow1","Succeeded","","8/10/2025, 8:44:26 PM","49s","{
""dataflowId"": ""db6968e7-d4db-46e4-8406-fd7857e5e5d8"",
""workspaceId"": ""ca4821de-7641-44ea-9a6d-4311320a74c4"",
""notifyOption"": ""NoNotification"",
""dataflowType"": ""DataflowFabric"",
""parameters"": {
""inputDate"": {
""value"": ""2025-05-01"",
""type"": ""string""
}
}
}","{
""jobInstanceId"": ""b1bcb012-d158-4112-8b84-e7296db2a2ad"",
""rootActivityId"": ""395f3050-00d7-48bf-bb46-a4c70597f74f"",
""executionDuration"": 46
}"
"ForEach1","Succeeded","","8/10/2025, 8:44:26 PM","8m 48s","{
""ItemsCount"": ""10""
}","{}"
"Notebook_Dates","Succeeded","","8/10/2025, 8:43:57 PM","28s","{
""notebookId"": ""7dc6e468-bef6-4e11-bcf3-12b5805e50ab"",
""workspaceId"": ""ca4821de-7641-44ea-9a6d-4311320a74c4"",
""parameters"": {
""StartDate"": {
""value"": ""2025-05-01"",
""type"": ""string""
},
""EndDate"": {
""value"": ""2025-05-10"",
""type"": ""string""
}
}
}","{
""status"": ""Succeeded"",
""result"": {
""runId"": ""5b0acefd-7443-46fb-925c-11387b066a04"",
""runStatus"": ""Succeeded"",
""sessionId"": ""447ebac8-73b0-440a-a1fe-b77e69a5658a"",
""sparkPool"": ""ca4821de-7641-44ea-9a6d-4311320a74c4"",
""error"": null,
""lastCheckedOn"": ""2025-08-10T12:44:21.3733333Z"",
""metadata"": {
""isForPipeline"": null,
""runStartTime"": ""2025-08-10T12:44:06.63098Z"",
""runEndTime"": ""2025-08-10T12:44:21.3737615Z""
},
""highConcurrencyModeStatus"": null,
""exitValue"": ""[{\""date\"": \""2025-05-01\""}, {\""date\"": \""2025-05-02\""}, {\""date\"": \""2025-05-03\""}, {\""date\"": \""2025-05-04\""}, {\""date\"": \""2025-05-05\""}, {\""date\"": \""2025-05-06\""}, {\""date\"": \""2025-05-07\""}, {\""date\"": \""2025-05-08\""}, {\""date\"": \""2025-05-09\""}, {\""date\"": \""2025-05-10\""}]""
},
""message"": ""Notebook execution is in Succeeded state, runId: 5b0acefd-7443-46fb-925c-11387b066a04"",
""SparkMonitoringURL"": ""workloads/de-ds/sparkmonitor/7dc6e468-bef6-4e11-bcf3-12b5805e50ab/447ebac8-73b0-440a-a1fe-b77e69a5658a"",
""sessionSource"": """",
""sessionTag"": """",
""executionDuration"": 18
}"

Re: Dataflow - Append

tan_thiamhuat — Mon, 11 Aug 2025 00:11:55 GMT

I inserted the output as seen below, and those dates that I passed to the Dataflow is from 2025-05-01 to 2025-05-10, which are all valid for the API.

Re: Dataflow - Append

miguel — Mon, 11 Aug 2025 00:23:23 GMT

Could you share a screenshot of what your "recent runs" dialog for the Dataflow looks like? specifically the section thad reads the parameter value that was passed.

Here's an example on how that section might look like:

What we're after is the actual value that was received by the Dataflow to run.

The other notion is how the table was created. If you try to create a new table in the Lakehouse and set it to append, does it actually repro the behavior?

Re: Dataflow - Append

tan_thiamhuat — Mon, 11 Aug 2025 00:32:04 GMT

can you guide me to where can I extract the "recent runs" of Dataflow? I only managed to get the below which may not be what you are looking for.

Re: Dataflow - Append

tan_thiamhuat — Mon, 11 Aug 2025 00:41:37 GMT

The other notion is how the table was created. If you try to create a new table in the Lakehouse and set it to append, does it actually repro the behavior? --> yes, the table was initially newly created and populated with a date. Then it was populated with the data pipeline with Dataflow, and set to append.

Re: Dataflow - Append

miguel — Mon, 11 Aug 2025 00:50:11 GMT

You can check the Dataflow "recent runs" by going into the workspace list where your Dataflow is located, click the three dots (...) and select the option that reads "Recent runs". Then you can pick the run that you wish to share to navigate to it and then take a screenshot of it.

I tried to create a small repro of this scenario. My Mashup script ended up looking like this:

[StagingDefinition = [Kind = "FastCopy"]] section Section1; [DataDestinations = {[Definition = [Kind = "Reference", QueryName = "Query_DataDestination", IsNewTarget = true], Settings = [Kind = "Manual", AllowCreation = true, ColumnSettings = [Mappings = {[SourceColumnName = "timestamp", DestinationColumnName = "timestamp"]}], DynamicSchema = false, UpdateMethod = [Kind = "Append"], TypeSettings = [Kind = "Table"]]]}] shared Query = let Source = Web.Contents("https://api.data.gov.sg/v1/environment/rainfall", [Query= [date=myDate]]), Custom = Json.Document(Source), Navigation = Table.FromRecords(Custom[items]), #"Removed other columns" = Table.SelectColumns(Navigation, {"timestamp"}), #"Removed duplicates" = Table.Distinct(#"Removed other columns", {"timestamp"}), #"Changed column type" = Table.TransformColumnTypes(#"Removed duplicates", {{"timestamp", type datetimezone}}), #"Changed column type 1" = Table.TransformColumnTypes(#"Changed column type", {{"timestamp", type date}}), #"Removed duplicates 1" = Table.Distinct(#"Changed column type 1", {"timestamp"}), #"Sorted rows" = Table.Sort(#"Removed duplicates 1", {{"timestamp", Order.Descending}}), #"Kept top rows" = Table.FirstN(#"Sorted rows", 1) in #"Kept top rows"; shared Query_DataDestination = let Pattern = Lakehouse.Contents([CreateNavigationProperties = false, EnableFolding = false]), Navigation_1 = Pattern{[workspaceId = "REDACTED"]}[Data], Navigation_2 = Navigation_1{[lakehouseId = "REDACTED"]}[Data], TableNavigation = Navigation_2{[Id = "rainfall", ItemKind = "Table"]}?[Data]? in TableNavigation; shared myDate = "2025-01-01" meta [IsParameterQuery = true, IsParameterQueryRequired = true, Type = type text];

When I run it through the pipeline or even through the REST API to pass the values for evaluation, it does append the data. For example, I passed these values:

2025-01-01
2025-06-01
2025-08-04

Here's my data loaded into my Lakehouse table looks like:

And here's a screenshot of one of the evaluations for the Dataflow through the "recent runs" dialog:

Would you mind sharing a screenshot of how your Dataflow refresh activity looks like inside the data pipeline and the script that you're using to pass the parameter values to it? There's a possibility that perhaps you've set your parameter to be optional at your Dataflow level and you're not passing a value to it through the Dataflow activity in a Data pipeline. That'll mean that every execution just runs using the default value for that parameter which would explain with just uses the same value over and over again.

Re: Dataflow - Append

tan_thiamhuat — Mon, 11 Aug 2025 01:09:35 GMT

Re: Dataflow - Append

tan_thiamhuat — Mon, 11 Aug 2025 01:11:14 GMT

Re: Dataflow - Append

miguel — Mon, 11 Aug 2025 01:37:58 GMT

Would you mind sharing the screenshot of your Dataflow refresh activity inside of your pipeline?

also, to rule out a metadata sync issue, would you mind querying the data directly inside the Lakehouse and not use it's SQL Endpoint ?

Re: Dataflow - Append

tan_thiamhuat — Mon, 11 Aug 2025 01:46:55 GMT

Dataflow refresh activity inside of your pipeline -- can you guide the steps to get this?

Re: Dataflow - Append

miguel — Mon, 11 Aug 2025 02:18:58 GMT

I just saw the screenshots that you shared around the "Recent runs" of your Dataflow, so we can rule out that a value is not being passed to the Dataflow.

The best thing that you can do is to modify your data destination settings and point the solution to a new table.

If you can repro the behavior even when changing the data destination settings that create a new table, please share that repro steps with us. You can also use the simplified mashup code that I shared to compare it with your own. Anything that would help us reproduce this behavior on our environments will help us pinpoint what could be happening.

Re: Dataflow - Append

tan_thiamhuat — Mon, 11 Aug 2025 03:53:11 GMT

when I create a new table, there is no Append feature? meaning that I cannot have a range of dates. So, I need to create a new table for one date of data first? then run the same data pipeline with a range of dates for the append to the table created?

Re: Dataflow - Append

miguel — Mon, 11 Aug 2025 04:20:52 GMT

I'm not sure I follow.

What I'm suggesting is that you click the setting that you have for your data destination at this moment to try a new data destination definition that points to a new table.

It might look like this:

And in the next section it would look like the following:

Once those changes are set, you can save your Dataflow again and try to the pipeline again to see what results you might be receiving.

You can later try to point your data destination logic to an existing table and make sure that the update method is set to be "Append" and then save your Dataflow.

If you're still seeing odd results where only the last date is being loaded, please raise a support ticket through:
Microsoft Fabric service status

Do feel free to share that ticket privately and I can pass it over to the engineering team.

Re: Dataflow - Append

tan_thiamhuat — Mon, 11 Aug 2025 05:38:57 GMT

that was exactly the steps I performed.

Re: Dataflow - Append

tan_thiamhuat — Mon, 11 Aug 2025 05:42:08 GMT

when I click that Microsoft Fabric service status , and I click Submit a ticket button, it opens up my Power BI / Fabric page, which I cannot submit any ticket. Probably my account does not have enough rights to submit a ticket.

Re: Dataflow - Append

miguel — Mon, 11 Aug 2025 06:13:22 GMT

Feel free to send me a private message with your mashup.pq file so we can look closer at what your Dataflow definition is.

You can get that file from your Git repo if you've enabled Git on the workspace where your dataflow is located or use the Get Dataflow definition endpoint:

https://learn.microsoft.com/en-us/rest/api/fabric/dataflow/items/get-dataflow-definition

With that information I can take a closer look and try to repro the issue on my end

Re: Dataflow - Append

miguel — Wed, 13 Aug 2025 00:53:45 GMT

me and @tan_thiamhuat had an offline sync.

The issue was related to how the data destination settings were set. After manually changing the creation of a new table to go into the manual settings (and avoid the automatic settings) things worked as expected.

Marking this thread as resolved.

Re: Dataflow - Append

tan_thiamhuat — Wed, 13 Aug 2025 01:01:18 GMT

It was great and fantastic that Miguel help me solve this issue within 15mins when I took more than 2 days to look into what is wrong 😁. Thanks Miguel for your proactive effort to do it offline to get this solved. Appreciate it a lot.