Idempotence in Data Engineering

17 Oct, 2025

Idempotence

meaning - when you apply the same operation multiple times the final result should be the same.

Why is this important? Assume that you are running a ETL process where you have a temporary staging table from which you need to upsert data into a permanent data warehouse table. The query you write should make sure that if no matter how many times you run the query the data warehouse table should be at the same state.

Consider the following schemas for temporary staging table and warehouse table -

staging table -

crawled_date TIMESTAMP,
domain STRING,
http_status_code INT

iceberg table -

last_crawl TIMESTAMP,
domain STRING,
http_status_code INT

Consider the below upsert query -

MERGE INTO iceberg_table i
USING (
       SELECT domain, crawled_date, http_status_code
       FROM staging_table
) s
ON i.domain = s.domain
WHEN MATCHED THEN
       UPDATE SET
           http_status_code = s.http_status_code,
           last_crawl = current_timestamp
WHEN NOT MATCHED THEN
       INSERT (domain, last_crawl, http_status_code)
       THEN (s.domain, s.crawled_date, s.http_status_code)

You see where the problem lies? In the update statement, we are using the current_timestamp to update the last_crawl column in iceberg table, so if we run it twice we would get two different values for last_crawl based on the current timestamp. This violates the idempotency.

As most have expected, we need to use the crawled_date from the staging table to update the last_crawl in the iceberg table.

MERGE INTO iceberg_table i
USING (
       SELECT domain, crawled_date, http_status_code
       FROM staging_table
) s
ON i.domain = s.domain
WHEN MATCHED THEN
       UPDATE SET
           http_status_code = s.http_status_code,
           last_crawl = <b>s.crawled_date</b>
WHEN NOT MATCHED THEN
       INSERT (domain, last_crawl, http_status_code)
       THEN (s.domain, s.crawled_date, s.http_status_code)