Order of Merged file in Copy Activity - Azure Synapse

Question

Order of Merged file in Copy Activity - Azure Synapse

Keshav Prasad 1

When I am trying to use the copy activity merge files copy behavior, the resulting file in sink has a different order than expected(i.e. the order of the rows in the original files is not maintained). Is there some way I can preserve the order after merging the files from source to sink in Synapse (not necessarily copy activity)?

1 answer

Your answer

Answer 1

MartinJaffer-MSFT 26,236

Hello @Keshav Prasad and welcome to Microsoft Q&A.

For Copy Activity there are no controls over the order. However, since the out-of-order must be caused by parallelism, reducing the parallelism to 1 (so there is only 1 writer) may reduce the shuffling. This could help with one part of the ordering.

I have not had cause to thoroughly explore the ordering behavior before, but I am aware there are two parts of this ordering to be aware of:

order of files read
order of rows within files

The combinations can give rise to multiple types of dis-order I explore below

Suppose we have files A, B, C, each having their own rows 1,2,3. Below I will illustrate possible dis-orders. Which do you see?

File ordering disorder, but row order good. expecting in order A,B,C but getting BAC
B1
B2
B3
A1
A2
A3
C1
C2
C3

File ordering ok, but row dis-order
A1
A3
A2
B3
B1
B2
C2
C1
C3

File interleaving but preserving in-file row ordering
A1
B1
C1
A2
A3
C2
B2
B3
C3

For Data Flow, there is more control, if you have an index or ID column to sort by.

Keshav Prasad 1 Reputation point

2021-11-24T07:02:43.023+00:00

Thanks a lot @MartinJaffer-MSFT for replying and posting a comprehensive comment. I tried setting the degree of parallelism to 1 just after I posted the question here and it worked like a charm. As you said there can be two types of ordering - order of files read and ordering of rows within the files. When the degree of parallelism was set as default, both types of ordering was happening. Now I have the required order which is-
A1
A2
A3
B1
B2
B3
C1
C2
C3
Only thing I am worried about is the cost of this operation as we are not at all using parallel copies and only using sequential copying now. Do you have some insights on this? We can have 40millions records per day, so the data is huge and performance and cost is a huge issue for us.
MartinJaffer-MSFT 26,236 Reputation points

2021-11-24T20:41:20.357+00:00

I'm afraid it is not possible with Copy Data activity, @Keshav Prasad .

You see, the whole basis of parallelism is to split up the work, and allow each individual worker to go at its own top speed. This means discarding synchronization. So if one worker is a little faster than another, the faster worker's data is eventually inserted before the slower worker's data.

To keep synchronized, means throttling all the workers to the slowest one and take turns in order writing.

The other possibility is to add a sorting step in Data Flow, and that definitely can slow things down.

If you goal is mainly to append files, maybe there is another solution.

Share via

Order of Merged file in Copy Activity - Azure Synapse

1 answer

Your answer