Optimize the Dataflow processes

Hi tan_thiamhuat,

Thanks for reaching out to the Microsoft fabric community forum.

You're absolutely right to be concerned about the performance implications as more CSV files come in. From what you've described and based on the row counts and increasing duration, it looks like both your Dimension and Fact table Dataflows are recomputing everything from scratch using the full set of source data each time. That’s likely why the Fact table load time jumped from 2 minutes to 9 minutes when you added the third file.

Since your source (SFTP) data keeps growing and is a key input to the transformations, this kind of cumulative processing can lead to scaling issues over time. Even though it's technically working now, over time it'll get slower and potentially more error-prone.

One way to address this is by introducing incremental processing. Instead of processing all rows every time, you can track which files or records have already been processed either by using file metadata (like file name or timestamp), maintaining a simple "processed files" log table, or applying row-level logic based on a timestamp or unique identifier. This way, your Dataflows only process new or changed data.

Another approach is to land your raw data into a Delta table in the Lakehouse and use something like a Notebook or Dataflow Gen2 to incrementally merge new data into your Dimension and Fact layers. That gives you more control over what gets processed and can help avoid reprocessing the full dataset every run.

Also, if your source files have some kind of date or sequence indicator, partitioning the data on ingestion and only querying recent partitions during transformation can really help reduce load times.

If I misunderstand your needs or you still have problems on it, please feel free to let us know.  

Best Regards,
Hammad.
Community Support Team

If this post helps then please mark it as a solution, so that other members find it more quickly.

Thank you.