cancel
Showing results for 
Search instead for 
Did you mean: 
Data Engineering
Join discussions on data engineering best practices, architectures, and optimization strategies within the Databricks Community. Exchange insights and solutions with fellow data engineers.
cancel
Showing results for 
Search instead for 
Did you mean: 

Merge operation stuck on scanning files for matches

KacperG
New Contributor

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:

KacperG_0-1729157109666.png

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

4 REPLIES 4

LauJohansson
Contributor

Have you enabled liquid clustering on your tables? 

-werners-
Esteemed Contributor III

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
Contributor

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.

Pablo_Camacho
New Contributor II

Hello @Sourav-Kundu,

Could you kindly provide more detail on your third point:

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

I’m interested in understanding how to apply a BROADCAST hint (/*+ BROADCAST(table) */) within a MERGE statement. I've tried a few methods without success.

I'm currently encountering a similar issue as @KacperG. My straightforward merge statement seems to be stalled. The target table utilizes liquid clustering, and the source table is loaded into memory using `createOrReplaceTempView` before executing the merge. This situation is quite challenging, and I'm running out of options. Your input would be highly appreciated. Thank you!

Connect with Databricks Users in Your Area

Join a Regional User Group to connect with local Databricks users. Events will be happening in your city, and you won’t want to miss the chance to attend and share knowledge.

If there isn’t a group near you, start one and help create a community that brings people together.

Request a New Group