Delete + Insert: Replacing the Run's Partition

The property, and the sentence that carries it

A load is idempotent when running it twice leaves the table exactly as running it once did. That single property is what makes retries, backfills, and late-data reconciliation safe, and it is why every distributed-systems answer in a data interview eventually lands on the same sentence:

True exactly-once delivery is impossible. At-least-once delivery plus an idempotent sink equals effectively exactly-once.

You cannot stop a queue, a scheduler, or an on-call engineer from delivering the same batch twice. You can make the sink not care. There are exactly three implementations, and this module grades all three. The first one is the one you reach for first.

Implementation 1: delete the partition, then insert it

The simplest idempotent pattern that works on every engine ever built:

1. DELETE every row of the target belonging to this run's partition.
2. INSERT ... SELECT that partition fresh from the batch.

The run owns the partition. Whatever was there before is gone, whether it was correct, half-written, or stale. Two runs of the same script land the same rows because the delete wipes the first run's output before the second one writes.

Order of evaluation

1. run_configrun_date = 2026-03-14, injected
2. DELETE dtwipe the run's partition
3. INSERT dtwrite it fresh from the batch
4. fct_ordersthe partition is owned, not appended to

You have written this mechanic before. In sql-l5-incremental-watermark-backfill you deleted a load_date range and reinserted it, and the partition date was typed into the SQL as a literal. That is the part this lesson replaces.

The date is a parameter, never a clock

The single most damaging anti-pattern in pipeline code is reading the wall clock. datetime.now(), CURRENT_DATE, or a hardcoded 'today' inside pipeline logic means:

• A rerun of yesterday's failed job processes today instead, silently.
• A backfill of March cannot work at all, because every run computes June.
• The same code produces a different answer depending on when it happened to execute.

The fix is that the run's date is an input. The orchestrator computes the data interval for the run and injects it, and the job reads it from there. In this lesson that injection is a run_config(pipeline, run_date) row, and your script reads run_date out of it. In Airflow it is the logical date passed into the task. In a warehouse job it is a bound parameter. The shape is always the same: the run is responsible for a time range, and something outside the job tells it which one.

That is also what makes the load re-runnable at all. A run bound to an immutable data interval produces the same output every time you run it, which is the precondition idempotency is built on.

Nothing can catch this failure for you by looking at output. A script with the date typed into it returns the correct partition on the day it was written, and on every rerun of that same day, which is why the exercises below cannot detect a literal either. The bug only surfaces the first time the run's date is not the date you typed, and by then it is a backfill that silently wrote the wrong day. Reading the parameter is a habit, not a test you can pass.

Atomicity, and where the boundaries go

The delete and the insert are one logical unit. If the delete lands and the insert fails, the partition is empty, which is worse than stale. Warehouses give you a transaction or an atomic INSERT OVERWRITE for this. The task boundary rule that goes with it is to keep extract, transform, and load in separate, independently re-runnable tasks, so a failure in one does not force you to redo the expensive ones.

Common mistake: deleting by a filter that is wider than what you insert. DELETE FROM fct_orders WHERE dt >= '2026-03-14' followed by an insert of one day quietly destroys every later partition. The delete predicate and the insert predicate must describe the same partition, which is why both should read the same parameter.

Interview nuance: when an interviewer says "make this backfill safe to rerun," lead with the sentence, then name delete-plus-insert as the implementation you reach for first, then say the date comes from the orchestrator and never from the clock. The delete itself is table stakes. The parameter is the detail that reads as production experience.

On a real platform this differs. Here you write a DELETE and an INSERT ... SELECT against SQLite. Hive, Spark SQL, and Databricks spell the same thing INSERT OVERWRITE on a partition, Delta Lake spells it replaceWhere, BigQuery replaces a partition with a WRITE_TRUNCATE load or query against a table$YYYYMMDD decorator (it has no INSERT OVERWRITE statement, so the alternative there is MERGE), Oracle can exchange a prepared partition in as a metadata operation, and Iceberg does it as a snapshot commit. Snowflake is the odd one out: its micro-partitions are automatic and not addressable, so there is nothing to swap at partition level and you replace a whole table with CREATE OR REPLACE TABLE ... SWAP WITH instead. All of them are delete-plus-insert with the atomicity handled for you. Airflow supplies the run_date this run_config row stands in for, as the run's logical date.