A permits team re-runs fee file drops and must avoid double-counting when the same files land again. Which SQL ingest choice is best?

A permits team that re-runs fee file drops must avoid double-counting when the same files land again. COPY INTO instead of blind appends provides SQL ingest that records loaded files so duplicate replays do not inflate fee totals. Idempotent re-runnable drops are a core municipal finance control for permit fee lakes when jobs retry or operators replay a landing folder after a partial failure. Using INSERT INTO for fee drops so duplicate file replays skip prior files automatically misstates what INSERT does: INSERT INTO has no memory of which files it already loaded and never skips on its own, so replaying the same drop inserts the rows again and double-counts fees just like the pattern the stem warns against. Running TRUNCATE on staging table before replay so duplicate file replays avoid double-count fees clears only staging, not the fee-table duplicate risk—it provides no loaded-file record, so moving the replayed rows to fees counts them again and double-counts fees. Using COPY INTO with FORCE true so duplicate file replays skip previously loaded fees reverses what FORCE does: FORCE true tells COPY INTO to ignore its loaded-file record and reload files anyway, so replaying the same drop inserts the rows again and double-counts fees. Exam caveat: COPY INTO’s idempotency is per loaded file—if a newly named file contains the same business fee rows, you may still need business-key dedupe; the exam contrast here is COPY INTO versus blind append on re-runnable drops. Operational check: load a fee drop with COPY INTO, replay the identical files, confirm fee sums are unchanged, grant finance read via Unity Catalog, and document the landing folder contract so operators do not switch back to blind APPEND under pressure.