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:

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.

Dataflow - Append | Microsoft Fabric Community

20 Replies

miguel
Community Admin
1 year ago
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.
- tan_thiamhuat
  Post Patron
  1 year ago
  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.
miguel
Community Admin
1 year ago
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?
- tan_thiamhuat
  Post Patron
  1 year ago
  "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
  }"
- tan_thiamhuat
  Post Patron
  1 year ago
  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.
  - miguel
    Community Admin
    1 year ago
    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?

miguel

Community Admin

1 year ago

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.

tan_thiamhuat
Post Patron
1 year ago
tan_thiamhuat
Post Patron
1 year ago

miguel
Community Admin
1 year ago
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 ?
- tan_thiamhuat
  Post Patron
  1 year ago
  Dataflow refresh activity inside of your pipeline -- can you guide the steps to get this?
  - miguel
    Community Admin
    1 year ago
    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.
miguel
Community Admin
1 year ago
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

Forum Discussion

Dataflow - Append

20 Replies

Related content

Dataflow Gen2 update method replace appends data

dataflow gen1

Fabric Dataflow Gen 2

Appending Data

connection to dataflows