Databricks Interview Question : How Databricks Handles Data Updates (MERGE INTO)

Imagine this: you have a Delta Lake table with millions of rows in Databricks, and every hour you receive new data. Some records are new, some need updates, and some must be deleted. How do you perform this complex operation without crashing your clusters or breaking your SLAs?

The answer is **MERGE INTO** — Databricks' super‑efficient way to handle UPSERTs (UPDATE + INSERT) and DELETEs on Delta tables in a single atomic operation.

By the end of this blog, you’ll understand:

• How **MERGE INTO** works under the hood.
• How to optimize it for big data.
• Real‑world patterns used by companies.

Let’s dive in!

🎯 Why MERGE INTO Matters

Delta tables are the backbone of modern data lakes:

• ACID transactions
• Schema evolution
• Time‑travel

But without a proper way to update data, you’re stuck:

• Appending everything → duplicates
• Overwriting everything → high latency, no history

**MERGE INTO** solves this in one elegant statement:

• Insert new rows
• Update existing ones
• Delete obsolete ones

💡 Core Concept: How MERGE INTO Works

MERGE INTO compares two datasets:

• Target: Delta table (existing data)
• Source: New data (e.g., from a streaming microbatch)

Syntax outline (simplified):

MERGE INTO target_table AS t
USING source_table AS s
ON t.key = s.key
WHEN MATCHED THEN UPDATE SET *
WHEN NOT MATCHED THEN INSERT *

Key parts:

• USING source: The new incoming data
• ON t.key = s.key: The join key (id, partition_date, etc.)
• WHEN MATCHED THEN UPDATE SET *: When a row exists, update it
• WHEN NOT MATCHED THEN INSERT *: When a row doesn’t exist, insert it

Under the hood:

• Spark does a join between target and source
• For matching rows, it updates them
• For non‑matching, it inserts
• For WHEN NOT MATCHED BY SOURCE, it can delete target rows

🧪 Practical Example: Upserting User Data

Scenario: You have a user profiles Delta table and receive hourly updates.

Target table:

CREATE TABLE users (
  id STRING,
  name STRING,
  email STRING,
  last_login TIMESTAMP
) USING DELTA

Source table (hourly updates):

CREATE TABLE users_updates (
  id STRING,
  name STRING,
  email STRING,
  last_login TIMESTAMP
) USING DELTA

MERGE INTO statement:

MERGE INTO users AS u
USING users_updates AS up
ON u.id = up.id
WHEN MATCHED THEN
  UPDATE SET
    u.name = up.name,
    u.email = up.email,
    u.last_login = up.last_login
WHEN NOT MATCHED THEN
  INSERT *

What happens:

• Rows with matching id → updated
• Rows with new id → inserted
• Rows missing in source → unchanged

You can also add conditional updates:

WHEN MATCHED AND u.last_login < up.last_login THEN
  UPDATE SET
    u.name = up.name,
    u.last_login = up.last_login

This prevents unnecessary updates if the source isn’t fresher.

⚠️ Common Mistakes & Misconceptions

Many engineers make the same mistakes:

1. Skipping the ON key properly

• If the join key is ambiguous, Spark may not match rows correctly.
• Best practice: Use a composite key (e.g., (id, partition_date)).

2. Running MERGE INTO on huge tables without partitions

• Spark has to join all partitions, which can be slow.
• Solution: Filter partitions before merging.

MERGE INTO users AS u 
USING (SELECT * FROM users_updates WHERE partition_date = '2025-12-01' ) AS up
ON u.id = up.id ...

3. Ignoring schema evolution

• Source may have new columns.
• Use MERGE INTO ... WITH SCHEMA EVOLUTION to auto‑add new columns.

4. Forgetting the WHEN NOT MATCHED BY SOURCE clause

• Data can “linger” if you never delete old rows.
• If you must delete, use:

WHEN NOT MATCHED BY SOURCE THEN DELETE

5. Running MERGE INTO too frequently

• Every merge is a full scan of the affected partitions.
• Best practice: Batch merges every 15–30 minutes, not every second.

🚀 Pro Tips & Best Practices

1. Always use Delta tables

• MERGE INTO only works on Delta tables.
• Regular Parquet tables don’t support ACID transactions.

2. Optimize the join key

• Use high‑cardinality keys (id, event_id).
• Avoid using date alone as the key.

3. Filter by partition

• Merge only the relevant partitions (e.g., partition_date='2025-12-01').
• This reduces shuffle and I/O.

*4. Use `UPDATE SET ` carefully**

• If source has extra columns, it may overwrite them.
• Be explicit: UPDATE SET u.col1 = s.col1, u.col2 = s.col2.

5. Handle conflicts with WHEN MATCHED AND ...

• Example: Only update if source is fresher:

WHEN MATCHED AND u.last_updated < up.last_updated 
    THEN UPDATE SET u.data = up.data,
     u.last_updated = up.last_updated

6. Monitor concurrency

• Multiple MERGE INTO jobs on the same table can cause concurrency issues.
• Use short‑lived jobs and exponential backoff.

7. Use MERGE INTO for streaming

• Combine with Databricks Autoloader to ingest and merge in real time.

Interview question you might get:

“How do you atomically update a Delta table when new data arrives?”

Answer:

“I use MERGE INTO with a proper join key and partition filtering to upsert new rows and update existing ones.”

📌 Quick Recap

• MERGE INTO is Databricks’ way to atomically update Delta tables.
• It combines inserts, updates, and deletes in one statement.
• Use it with careful joins, partition filters, and schema evolution.

📣 Call to Action

If you found this useful :

Follow me on : Twitter : https://x.com/SriwWorld Instagram : https://www.instagram.com/sriwworldofcoding/ Youtube : https://www.youtube.com/@sriwworldofcoding?sub_confirmation=1 Medium : https://medium.com/@sriwworldofcoding Threads : https://www.threads.com/@sriwworldofcoding Facebook : https://www.facebook.com/profile.php?id=61576419014220

🔥 If you want to stay ahead in your career, start learning NOW!

I highly recommend these 2 practical, hands-on courses 👇 📌 Apache Airflow Bootcamp (Workflow Automation) 👉 https://www.udemy.com/course/apache-airflow-bootcamp-hands-on-workflow-automation/ 💡 Learn everything from basics to advanced: DAGs, scheduling, operators, sensors & real workflows

📌 PySpark for Data Engineers (Architecture + Interviews) 👉 https://www.udemy.com/course/pyspark-for-data-engineers-architecture-interviews/ 💡 Master Spark architecture, optimization, performance tuning & crack interviews like a pro

Now you’re ready to master MERGE INTO in Databricks. Go optimize those pipelines! 🚀