In the case of checkpoint rest (delelte checkpoint but keeping delta table), my understanding is that (foreachBatch with a MERGE INTO) is the only solution to avoid duplicates in the delta table.

for example,
-------
df = df1a.unionByName(df2a).unionByName(df3a) \
  .dropDuplicatesWithinWatermark(["your_key_columns"])

df.writeStream \
  .format("delta") \
  .outputMode("append") \
  .option("checkpointLocation", my_checkpoint_path) \
  .trigger(availableNow=True) \
  .table(f"{silver_catalog}.{silver_schema}.my_silver_table")

----

Assume the event time is reliable and watermark windows is wide.
Assume df1a, df2a data has already in the target delta table;
I want to add df3a.
So, I remove the checkpoint before running the above scripte.

Now, 
- the df contains unique rows from df1a+df2a+df3a. 
- df.writeStream appends the content of df to the target delta, which has already had unique rows from df1a+df2a.
In summary, the target delta will contain rows from (df1a+df2a) twice. They were write before and after the checkpoint reset.

This is why I asked if the "idempotent" means (foreachBatch with a MERGE INTO)

Do you agree?

Thanks again.