Incremental Load in ETL: Watermark and MERGE Explained Simply

Incremental load in ETL means loading only new or changed rows. Imagine your company has an orders table with 50 crore rows. Every night, your ETL job copies the whole table again into the data warehouse. It takes four hours, costs a lot of cloud money, and one day it fails just before the morning report. Sound familiar?

The fix is simple: copy only what changed. This is called an incremental load, and it is one of the most common data engineering interview topics. Let’s break it down in simple words, with small SQL and PySpark examples you can actually use.

Full Load vs Incremental Load: The Basic Difference

A full load means you delete (or overwrite) the target and load all the source data again, every time.

An incremental load (also called delta load) means you load only the rows that are new or changed since the last successful run.

• Full load – easy to build, always correct, but slow and costly for big tables. Good for small master tables (like a country list) or the very first load.
• Incremental load – fast and cheap, but you must track “what changed” carefully. Good for big, growing tables like orders, transactions and logs.

In real projects, teams usually do one full load first (the initial load) and then run incremental loads every day or every hour.

Related reading: How to master SCD Type 2 and SQL window functions for interviews.

How Do We Know What Changed? 4 Common Methods

Microsoft’s Azure Data Factory documentation lists a few standard ways to find new or changed data. These ideas apply to any tool – Informatica, Spark, Airflow or plain SQL.

1. Watermark column – the source table has a column like updated_at (last-modified timestamp) or an increasing ID. You remember the highest value you loaded last time, and next time pick only rows above it. This is the most popular method.
2. Change Tracking / CDC – the database itself records which rows were inserted, updated or deleted. SQL Server’s Change Tracking is one example. CDC tools read the database log to capture every change.
3. File LastModifiedDate – for files in a data lake, pick only files modified after the last run. Simple, but scanning lakhs of files can be slow.
4. Time-partitioned folders – files land in folders like /2026/11/07/, so you read only the new day’s folder. Microsoft calls this the most performant option for files.

Step 1: The Watermark Table for Incremental Load in ETL

A watermark is just a bookmark. It says: “I have safely loaded everything up to this time.” We store it in a small control table. That is the main benefit of incremental load in ETL.

-- One row per source table
CREATE TABLE etl_watermark (
    table_name      VARCHAR(100),
    last_loaded_at  TIMESTAMP
);

INSERT INTO etl_watermark VALUES ('orders', '1900-01-01 00:00:00');

Now, the extract query picks only the changed rows:

SELECT o.*
FROM   orders o
WHERE  o.updated_at > (SELECT last_loaded_at
                       FROM   etl_watermark
                       WHERE  table_name = 'orders');

After the data is loaded successfully, move the bookmark forward to the highest updated_at you just loaded:

UPDATE etl_watermark
SET    last_loaded_at = (SELECT MAX(updated_at) FROM stg_orders)
WHERE  table_name = 'orders';

Golden rule: update the watermark only after the load succeeds. If you update it first and the load fails, those rows are skipped forever.

Step 2: Upsert with MERGE (Insert New, Update Old)

The changed rows contain two kinds of records: brand-new orders (insert them) and existing orders that changed, like status moving from “Shipped” to “Delivered” (update them). Doing both in one statement is called an upsert, and SQL gives us MERGE for it.

MERGE INTO dw_orders AS t
USING stg_orders AS s
ON t.order_id = s.order_id
WHEN MATCHED THEN
  UPDATE SET t.status     = s.status,
             t.amount     = s.amount,
             t.updated_at = s.updated_at
WHEN NOT MATCHED THEN
  INSERT (order_id, customer_id, status, amount, updated_at)
  VALUES (s.order_id, s.customer_id, s.status, s.amount, s.updated_at);

This works in Delta Lake (Databricks), Snowflake, SQL Server and most modern warehouses, with small syntax differences. This is the core idea behind incremental load in ETL.

Step 3: The Same Pipeline in PySpark with Delta Lake

Here is a complete, small incremental job in PySpark. It reads the watermark, picks new rows, removes duplicates, merges into the target and then moves the watermark. Many teams adopt incremental load in ETL for exactly this reason.

from pyspark.sql import functions as F
from pyspark.sql.window import Window
from delta.tables import DeltaTable

# 1. Read the last watermark
last_wm = (spark.table("etl_watermark")
           .filter(F.col("table_name") == "orders")
           .agg(F.max("last_loaded_at"))
           .collect()[0][0])

# 2. Pick only new or changed rows
changes = spark.table("src.orders").filter(F.col("updated_at") > F.lit(last_wm))

# 3. Keep only the latest version of each order (avoid duplicate matches)
w = Window.partitionBy("order_id").orderBy(F.col("updated_at").desc())
latest = (changes.withColumn("rn", F.row_number().over(w))
                 .filter(F.col("rn") == 1)
                 .drop("rn"))

# 4. Upsert into the Delta target
target = DeltaTable.forName(spark, "dw.orders")
(target.alias("t")
   .merge(latest.alias("s"), "t.order_id = s.order_id")
   .whenMatchedUpdateAll()
   .whenNotMatchedInsertAll()
   .execute())

# 5. Move the watermark ONLY after a successful merge
new_wm = latest.agg(F.max("updated_at")).collect()[0][0]
if new_wm is not None:
    spark.sql(f"""
        UPDATE etl_watermark
        SET last_loaded_at = '{new_wm}'
        WHERE table_name = 'orders'
    """)

Why step 3? Databricks documentation says only one source row can match a given target row. If the same order_id appears twice in your batch, the MERGE can fail. So we keep only the latest row per key using row_number().

Common Incremental Load in ETL Mistakes (and Interview Traps)

• Hard deletes are missed. If a row is deleted in the source, a watermark query never sees it. Use CDC/Change Tracking, a soft-delete flag (is_deleted), or a periodic full compare.
• Late-arriving data. A row may commit with an older updated_at after your job already ran. Many teams re-read a small overlap window (for example, the last 1–2 hours) and rely on MERGE to avoid duplicates.
• Using >= carelessly. >= re-reads the boundary rows; > may skip rows with the exact same timestamp. With MERGE, re-reading is safe, so overlap is usually the safer choice.
• No index on the watermark column. Filtering on updated_at in a huge source table without an index can be slow and load the source database.
• Plain INSERT instead of MERGE. If you only append changed rows, updated orders appear twice in the target.

A pipeline that gives the same result even if you run it twice is called idempotent. MERGE plus a watermark updated at the end makes your job idempotent – interviewers love this word. Always test incremental load in ETL with a rerun before going live.

Key Takeaways on Incremental Load in ETL

• Full load = reload everything; incremental load = load only new/changed rows.
• A watermark (control) table stores the last successfully loaded timestamp or ID.
• Use MERGE (upsert) to insert new rows and update changed rows in one step.
• Deduplicate the batch before MERGE – one source row per key.
• Update the watermark only after the load succeeds.
• Watermarks cannot see hard deletes – use CDC or soft-delete flags for that.

What Should You Do?

If you are preparing for interviews, practise explaining this flow in 60 seconds: initial full load → watermark → extract changes → dedupe → MERGE → update watermark. If you are on a project, check whether your big tables are still being fully reloaded every night – switching them to incremental loads is often an easy win in both time and cost.

FAQ

Sources

• Microsoft Learn – Incrementally load data from a source data store to a destination (Azure Data Factory)
• Databricks Docs – Upsert into a Delta Lake table using merge
• Microsoft Learn – Azure Databricks: Upsert into a Delta Lake table using merge