Merge operation stuck on scanning files for matches

[KacperG]

Hi

I'm executing simple merge, however it always stucks at "MERGE operation - scanning files for matches". Both delta tables are not big - source has about 100MiB in 1 file and target has 1,5GiB, 7 files, so it should be quite fast operation, however it stuck infinitly on this:

Both tables are external type and located on ADLS gen2. 
On the other hand using replace table using select * processes without problems.

[LauJohansson]

Have you enabled liquid clustering on your tables? 

[-werners-]

merging requires a lot more compute than an overwrite.
It has to check which files to replace.
If your data is very skewed, f.e. 60 or more percent of the data resides in a single file, that will be the bottleneck.
For merges to go fast one can use optimizations (like liquid clustering) or partition the table and do partition pruning while merging (basically an added where clause on the partition column).

[Sourav-Kundu]

Merge can take time due to various reasons.

You can try one or combination of the following options:

1. Use the OPTIMIZE command to compact small files into larger ones. This reduces the number of files that need to be read during the MERGE, improving performance.

2. You can use Liquid Clustering

3. If one of the tables in the MERGE operation is small, consider using broadcast joins

4. You can use Change Data Capture (CDC) to efficiently track and manage changes in data for MERGE operations. When using CDC, you can optimize your MERGE statements by only processing the changed records. This minimizes the volume of data scanned and processed, improving overall performance.