topic Re: Ordering csv files to import historical data in a lakehouse in Pipelines

Ordering csv files to import historical data in a lakehouse

pmscorca — Tue, 30 Dec 2025 06:39:51 GMT

Hi,

I need to import historical data about some file groups in a lakehouse.

Each file group has a specific prefix saved in a configuration table in a warehouse.

Each file group has a name composed of filegroupprefix_nn_fromdate_mm_todate, where nn and mm represent two digits (f.e. 01, 02, 03, 04, ..., 10, 11, 12, 13 and so on) and fromdate and todate are in yyyymmdd format.

For a specific file group, e.g. filegroup1prefix, I could have these csv files to import:

filegroup1prefix_01_20250101_01_20250201
filegroup1prefix_01_20250101_02_20250202
filegroup1prefix_01_20250101_03_20250203
filegroup1prefix_01_20250101_04_20250204
etc. etc.

Each file covers about two months of data: the filegroup1prefix_01_20250101_01_20250201 csv file contains data between 20250101 date (January) and 20250201 date (February).
Moreover, filegroup1prefix_01_20250101_02_20250202 data have to overwrite filegroup1prefix_01_20250101_01_20250201 data, filegroup1prefix_01_20250101_03_20250203 data have to overwrite filegroup1prefix_01_20250101_02_20250202 and so on.
So, in order to avoid any mistakes, I need to work the csv files following the file name order.
I think to implement a pipeline that runs:

a lookup against the configuration table in the warehouse to get the file group prefixes,
a for each to save in a variable the specific prefix, then a get metadata against the on-premise folder to get the name of all csv files, then a filter to select the csv files belonging to the specific group file, but I cannot order these file names saved in an array.

Now, any suggests to me in order to solve a such issue, please? Thanks

I've tried to search a specific regular expression, something similar to "@sort"

Update: while I think a good solution, I've created this idea -->
Feature to order an array into a pipeline - Microsoft Fabric Community
Please vote it, thanks.

Re: Ordering csv files to import historical data in a lakehouse

lbendlin — Mon, 29 Dec 2025 19:05:42 GMT

not clear why the order of processing would be important here? What kind of mistakes are you anticipating?

Re: Ordering csv files to import historical data in a lakehouse

pmscorca — Mon, 29 Dec 2025 21:45:21 GMT

Because old files could overwrite more recent files.

Re: Ordering csv files to import historical data in a lakehouse

tayloramy — Tue, 30 Dec 2025 14:48:51 GMT

Hi pmscorca,

The way that I would approach this is by landing the files in a lakehouse, and then using a notebook to load them so you can ensure things load in the order you want (filename, and not the sys modified time).

Then you load the data into a lakehouse table, and from there you can move it into the warehouse.

Re: Ordering csv files to import historical data in a lakehouse

v-pnaroju-msft — Wed, 31 Dec 2025 17:52:12 GMT

Thankyou, lbendlin and tayloramy for your responses.

Hi pmscorca,

We appreciate your inquiry on the Microsoft Fabric Community Forum.

Based on my understanding, in Fabric Data Pipelines, arrays returned by Get Metadata or Lookup activities cannot be sorted, and the execution order of ForEach is non-deterministic. Consequently, pipelines cannot guarantee filename based processing order, which may result in older CSV files overwriting newer data when overwrite logic is applied.

One workaround is to follow the approach suggested by @tayloramy.

Your idea to support array sorting within pipelines is valid and addresses the gap. Until such a capability is introduced, notebook based ordered ingestion is a feasible workaround.

Additionally, please refer the links below:
How to use notebooks - Microsoft Fabric | Microsoft Learn
Overview of Fabric deployment pipelines - Microsoft Fabric | Microsoft Learn
The Microsoft Fabric deployment pipelines process - Microsoft Fabric | Microsoft Learn

We hope the information provided helps to resolve the issue. Should you have any further queries, please feel free to contact the Microsoft Fabric community.

Thank you.

Re: Ordering csv files to import historical data in a lakehouse

pmscorca — Sat, 03 Jan 2026 11:34:05 GMT

Hi, thanks four you reply but I hopo to vote my idea, please.

I'd like to use a notebook only to order the file names saved in an array, next I'd like to use the for each activity to iterate the array from the first ordered file name to the last one: it is the right approach, isn't it? Thanks

Re: Ordering csv files to import historical data in a lakehouse

lbendlin — Sat, 03 Jan 2026 13:04:27 GMT

Not really. You are trying to prevent parallelism from happening, and you are reducing the robustness of your ingestion process. Typical "fighting the API" pattern.

Re: Ordering csv files to import historical data in a lakehouse

v-pnaroju-msft — Mon, 05 Jan 2026 18:25:42 GMT

Hi pmscorca,

Thank you for the followup. We have already upvoted the suggestion on the forum.

Based on my understanding, using a notebook to sort file names and then passing the resulting array to a pipeline ForEach is not a reliable pattern in Fabric. Pipelines do not guarantee ordered execution across activity boundaries. When file order is critical, both sorting and ingestion should be performed within the notebook. Pipelines ought to be used only to orchestrate the execution of the notebook. This is the supported and most robust approach in Fabric.

We hope this information helps resolve the issue. If you have any further queries, please feel free to contact the Microsoft Fabric community.

Thank you.

Re: Ordering csv files to import historical data in a lakehouse

pmscorca — Tue, 06 Jan 2026 15:04:06 GMT

Hi and many thanks for your reply.

Any suggests to perform a such ingestion within a notebook? Thanks

Re: Ordering csv files to import historical data in a lakehouse

v-pnaroju-msft — Wed, 07 Jan 2026 05:58:46 GMT

Hi pmscorca,

Thank you for the followup.

Based on my understanding, to ensure correct overwrite behavior you should list, sort, and ingest the files sequentially within a Fabric notebook. This ensures deterministic execution and is the supported approach when file order matters. Pipelines should be used only to orchestrate the execution of the notebook.

We hope this information helps to resolve the issue. Should you have any further queries, please feel free to contact the Microsoft Fabric community.

Thank you.

Re: Ordering csv files to import historical data in a lakehouse

pmscorca — Wed, 07 Jan 2026 08:12:44 GMT

Ok, a sample notebook to accomplish a such task, please? Thanks

Re: Ordering csv files to import historical data in a lakehouse

v-pnaroju-msft — Fri, 09 Jan 2026 16:43:22 GMT

Hi pmscorca,

Please find the sample code and screenshots attached below, which may help resolve the issue:
1.Input files:

2. Output table:

3. Sample code:

from pyspark.sql import functions as F

from pyspark.sql import SparkSession

spark = SparkSession.builder.getOrCreate()

# Step 1: Path to files in Lakehouse

files_path = "Files/bronze/filegroup1prefix/"

# Step 2: List CSV files

files_df = (

spark.read.format("binaryFile")

.load(files_path)

.select("path")

.filter(F.col("path").endswith(".csv"))

)

# Step 3: Extract ordering number from filename

files_df = files_df.withColumn(

"seq",

F.regexp_extract(

"path",

r"_(\d{2})_\d{2}_\d{2}_\d{8}",

).cast("int")

)

# Step 4: Sort files in correct order

ordered_files = files_df.orderBy("seq")

# Step 5: Sequential ingestion with overwrite

for row in ordered_files.collect():

df = (

spark.read

.option("header", True)

.option("dateFormat", "MM/dd/yyyy")

.csv(row.path)

)

df.write \

.format("delta") \

.mode("overwrite") \

.saveAsTable("sales_final")

print("Ordered ingestion completed successfully.")

We hope the information provided is helpful. If you have any further queries, please feel free to contact the Microsoft Fabric community.

Thank you.

Re: Ordering csv files to import historical data in a lakehouse

v-pnaroju-msft — Mon, 12 Jan 2026 14:42:25 GMT

Hi pmscorca,

We would like to follow up and see whether the details we shared have resolved your problem. If you need any more assistance, please feel free to connect with the Microsoft Fabric community.

Thank you.