Merge Performance Issues

[Harshul]

The issues we are currently facing in our project is Slow Merge Performance.
Production database has 50 billion records (Historical data), data is partitioned(date) but not indexed.
Incremental data is has close 250 to 500 million records. Incremental data is for 2 dates mainly today and yesterday mostly inserts and updates no deletes.
So on running the merge statement it takes 1 hour to run the statement since databricks doesn't allow to write where in merge condition, so we have added a condition date >{condition} and <{condition} there is no significant improvement in the performance.
So I am thinking to try insert overwrite statement, so just wanted to know few details from the community.
1 Will the data will be still be good to consume like logically ? like the same result i will get if I run the merge statement ?
2 If yes the compute usage will go up down or nearly same?
3 Will it be able to provide any significant performance improvement?

[Renu_]

Hi @Harshul, 

1. Yes, if configured properly, INSERT OVERWRITE helps maintain data consistency. You can deduplicate incremental data using ROW_NUMBER() and use INSERT OVERWRITE with replaceWhere for efficient daily bulk updates.

2. Usually lower, since it avoid heavy shuffling and joins of merge

3. Yes, because only the affected partitions are rewritten, making it more efficient.