Difficulty: Hard | Concepts: PySpark Notebook, Incremental Load, Watermarking, Delta Lake, MERGE

"How would you load only the new or changed rows every day, instead of reprocessing the entire table?" is one of the most important Fabric Notebook interview questions, because full reloads don't scale — and interviewers want to see that you reach for incremental load with a watermark by default.

What Is Incremental Load / Watermarking?

An incremental load pulls only the rows that are new or have changed since the last successful run, instead of re-reading an entire source table every time. A watermark is the mechanism that tracks "how far we've already loaded" — typically the maximum value of a reliable, ever-increasing column such as ModifiedDate or an auto-incrementing ID. On each run, the pipeline or notebook reads the last saved watermark value, filters the source for rows greater than that value, processes only those rows, and then saves the new maximum value as the watermark for next time. This keeps runtime and cost proportional to the amount of new data, not the size of the whole table — critical once a source table grows into millions of rows.

Scenario

A Transactions table in an on-prem SQL Server has 200 million rows and grows by ~50,000 rows a day. Re-reading the entire table nightly is too slow and too expensive. Design a Fabric Notebook that loads only new/changed rows into the Lakehouse.

Sample Watermark Store

-- A small Delta table used purely to track load progress
Table: load_control
--------------------------------
TableName       LastWatermark
Transactions    2026-06-30 23:59:59

Notebook Solution (PySpark)

# 1. Read the last watermark
control_df = spark.read.format("delta").table("load_control")
last_watermark = control_df.filter("TableName = 'Transactions'") \
                            .collect()[0]["LastWatermark"]

# 2. Pull only new/changed rows from the source
new_rows_df = spark.read.jdbc(
    url=jdbc_url,
    table=f"(SELECT * FROM Transactions WHERE ModifiedDate > '{last_watermark}') AS t",
    properties=jdbc_props
)

# 3. Upsert into the Delta target
from delta.tables import DeltaTable
target = DeltaTable.forName(spark, "transactions_silver")
target.alias("t").merge(
    new_rows_df.alias("s"),
    "t.TransactionID = s.TransactionID"
).whenMatchedUpdateAll().whenNotMatchedInsertAll().execute()

# 4. Update the watermark to the new max value
new_watermark = new_rows_df.agg({"ModifiedDate": "max"}).collect()[0][0]
spark.sql(f"""
  UPDATE load_control
  SET LastWatermark = '{new_watermark}'
  WHERE TableName = 'Transactions'
""")

Visual Representation

load_control table: LastWatermark = 2026-06-30 23:59:59 | Read source WHERE ModifiedDate > LastWatermark | 50,000 new/changed rows returned (not 200 million) | MERGE into transactions_silver (upsert) | Update load_control: LastWatermark = 2026-07-01 23:59:59

Common Mistake to Avoid

A frequent error is updating the watermark before confirming the MERGE succeeded. If the write fails partway through but the watermark has already advanced, those rows are silently skipped forever. Always update the watermark as the last step, after a successful write, ideally inside the same transactional boundary or with a clear rollback path.

Conclusion

The interview signal here is whether you default to incremental processing for large, growing tables, and whether you understand that a watermark is only safe when it's updated after a confirmed successful load — not before.