topic Re: Does MERGE statement preserve order? (Slowly Changing Dimensions) in Data Engineering

Does MERGE statement preserve order? (Slowly Changing Dimensions)

TMNGB — Tue, 26 Jul 2022 14:01:33 GMT

In the case of processing multiple source files - with potentially, one or multiple entity versions per source - being able to use the MERGE statement whilst preserving the order is key to ensure the correct versioning of entity versions (aka, version 1 starts at X to Y, then comes version 2 from Y to Z, etc).

However, as far as I can tell, there is no guarantee that the data will be processed (MERGED) according to the order in the DataFrame. Has anyone confirmed this?

The current way to bypass this is to process each extraction date separately for the MERGE statement however, it is quite a slow process since Azure takes a long time to MERGE.

Re: Does MERGE statement preserve order? (Slowly Changing Dimensions)

-werners- — Wed, 27 Jul 2022 11:06:39 GMT

you could guarantee order by actually ordering the dataframe which you want to merge or use a window function (and keeping only the most recent record f.e.).

The upsert is an expensive operation, so depending on the amount of data which has to be evaluated it can take a while indeed.

There are some tweaks possible though:

https://docs.microsoft.com/en-us/azure/databricks/kb/delta/delta-merge-into

Re: Does MERGE statement preserve order? (Slowly Changing Dimensions)

Noopur_Nigam — Mon, 29 Aug 2022 17:09:22 GMT

Hi @Guilherme Banhudo I hope that werners answer would have helped you. Please let me know if you still have doubts or queries.