Ensuring Row Order When Importing CSV with COPY INTO

[SanneJansen564]

Hi everyone,

I have a CSV file stored in S3, and it's critical for my process that the rows are loaded in the exact order they appear in the file.

Does the COPY INTO command preserve the original row order during the load? I need to make sure the bronze layer reflects the file's exact sequence for downstream parsing.

Has anyone dealt with this before or knows if there’s a way to guarantee the order is maintained?

Thanks in advance!

[WiliamRosa]

When loading CSV files using COPY INTO, it's important to note that row order is not guaranteed. This is because the process leverages Spark’s distributed architecture, which reads and processes data in parallel across different nodes. That parallelism can lead to rows being ingested in a different sequence than they appear in the original file.

If maintaining the exact row order is critical for your use case, a reliable solution is to include an explicit ordering column—such as a row_number—in the CSV before loading. After ingestion, you can sort the data based on that column to accurately reconstruct the original sequence.

This approach ensures consistency, especially when working with downstream transformations that depend on the initial row arrangement.

[SanneJansen564]

Is there any way to preserve or reconstruct the original row order during COPY INTO without adding a row_number column to the CSV?

[WiliamRosa]

You can try using input_file_name() or force a single partition read, but original row order still isn't guaranteed.

[SanneJansen564]

Does using a single partition during the load significantly impact performance?

[WiliamRosa]

yes, forcing a single partition can degrade performance, especially with large files.

[SanneJansen564]

thanks so much @WiliamRosa 

[WiliamRosa]

Not at all!

[szymon_dybczak]

I just want to say - moderators will be notify @WiliamRosa 

[SanneJansen564]

ok

[WiliamRosa]

Sanne, Szymon is right, even thought we know each other, please remove thes lastest solutions please.

[WiliamRosa]

tks @SanneJansen564