Your INSERT OVERWRITE just deleted data you never touched

Reproducible example — setup

CREATE OR REPLACE TABLE main.default.daily_sales (
  sale_id INT, dt DATE, amount DECIMAL(10,2)
) PARTITIONED BY (dt);

INSERT INTO main.default.daily_sales VALUES
  (1, DATE '2026-09-28', 120.00),
  (2, DATE '2026-09-29',  85.50),
  (3, DATE '2026-09-29',  41.00);

CREATE OR REPLACE TEMP VIEW staging_daily AS
SELECT 4 AS sale_id,
       DATE '2026-09-30' AS dt,
       CAST(99.00 AS DECIMAL(10,2)) AS amount;

Before

SELECT sale_id, dt, amount
FROM main.default.daily_sales
ORDER BY dt, sale_id;

The daily job, whose source holds only 2026-09-30 rows

INSERT OVERWRITE TABLE main.default.daily_sales
SELECT * FROM staging_daily WHERE dt = DATE '2026-09-30';

After

SELECT sale_id, dt, amount
FROM main.default.daily_sales
ORDER BY dt, sale_id;

The fix

INSERT INTO main.default.daily_sales
REPLACE USING (dt)
SELECT * FROM staging_daily;

This replaces only rows whose dt matches a dt present in the source, leaves every other row untouched, inserts rows with new keys, and — unlike REPLACE WHERE — deletes nothing when the source is empty. On DBR 16.3–17.1 the USING list must contain the full set of the table's partition columns; on 17.2+ any columns work, on partitioned, unpartitioned, or liquid-clustered tables.

The four replacement options

Key rules

Always validate the written data touches only the expected partitions — a single row in the wrong partition can unintentionally overwrite the entire partition. In Scala/Python, replaceUsing/replaceOn cannot be combined with replaceWhere, partitionOverwriteMode, or overwriteSchema. If data is accidentally overwritten, RESTORE undoes it.

1. Know your runtime's menu: SQL REPLACE WHERE 12.2+, REPLACE USING 16.3+ (full dynamic overwrite 17.2+), REPLACE ON 17.1+.
2. On 16.3–17.1, USING must name the full partition-column set.
3. Before running, list the source's distinct replacement keys: SELECT DISTINCT dt FROM staging_daily — confirm they are the partitions you intend to touch.
4. Never trust REPLACE WHERE with a possibly-empty source; REPLACE USING/ON are safe.
5. On any compute migration (classic → serverless/SQL warehouse), re-audit every SET spark.sql.sources.partitionOverwriteMode job — in SQL it is classic-compute-only and not supported elsewhere.
6. Keep RESTORE ready: note the table version before the overwrite.

Use this when

• Key-based daily replacements (REPLACE USING, 17.2+).
• REPLACE USING on 16.3–17.1 when USING names all partition columns.
• Fixed one-shot predicate replacements (REPLACE WHERE).
• NULL-safe or complex matching (REPLACE ON).

Avoid this when

• Starting a new workload that needs partition-scoped overwrites — use the modern options, not the legacy mode.
• Relying on the SET in SQL on warehouses/serverless (not supported).
• Combining replaceUsing/replaceOn with replaceWhere/partitionOverwriteMode/overwriteSchema in one writer.
• Using REPLACE WHERE with a possibly-empty source.