ADF Data Flow: Possible to Organize Sink Order?

Question

Hi,

Is it possible to order sinks in such a way that one sink activity will not start until another sink activity has completed? Example below, the Hub record has a key that is a foreign key in the Sat record, so the Hub record has to exist before the Sat record can be added.

Thanks, Chris

Answer 1

Hi PageChristopher-5206

Thanks for using MS Q&A platform and posting your query.

You can leverage sink order under settings tab.

Click anywhere outside the activities in data flow and you will see settings and in that custom sink ordering as below.

Hope that helps.

Answer 2

Hi PageChristopher-5206,

Thanks for the question and using MS Q&A platform.

By default, data is written to multiple sinks in a nondeterministic order. The execution engine writes data in parallel as the transformation logic is completed, and the sink ordering might vary each run.

However as I understand your scenario where you have foreign key dependency and the hub record has to exist first before Sat record can be inserted, you can leverage the Customer sink ordering functionality which we have in ADF.

To specify an exact sink ordering, enable Custom sink ordering on the General tab of the data flow. When enabled, sinks are written sequentially in increasing order ( Sink Hub should have write order 1 and then Sink Set as 2).

https://learn.microsoft.com/en-us/azure/data-factory/data-flow-sink#custom-sink-ordering

Please try this out and let us know in case of any questions.