COPY INTO won't reload your fixed file: the filename-tracked idempotency trap

COPY INTO trusts the path, not the content

COPY INTO loads data from a file location into a Delta table. It is retryable and idempotent: files in the source location that have already been loaded are skipped on subsequent runs — and the official docs make the uncomfortable part explicit: “Files in the source location that have already been loaded are skipped. This is true even if the files have been modified since they were loaded.”

This is the right behavior for scheduled batch loads — a crashed job can re-run safely — but it is the wrong mental model when your workflow is “fix the file, re-run the load.”

Setup

dbutils.fs.put(
  "/tmp/copyinto_demo/orders_001.csv",
  "order_id,total\n1,42.50\n2,19.99\n",
  overwrite=True
)

CREATE TABLE IF NOT EXISTS bronze_orders (
  order_id INT,
  total DOUBLE
);

COPY INTO bronze_orders
FROM 'dbfs:/tmp/copyinto_demo'
FILEFORMAT = CSV
FORMAT_OPTIONS ('header' = 'true', 'inferSchema' = 'true');

Before

Step 1 — the silent skip

The vendor fixes a typo in place — same filename, 19.99 → 29.99 — and the load re-runs.

dbutils.fs.put(
  "/tmp/copyinto_demo/orders_001.csv",
  "order_id,total\n1,42.50\n2,29.99\n",
  overwrite=True
)

COPY INTO bronze_orders
FROM 'dbfs:/tmp/copyinto_demo'
FILEFORMAT = CSV
FORMAT_OPTIONS ('header' = 'true', 'inferSchema' = 'true');

“Files in the source location that have already been loaded are skipped. This is true even if the files have been modified since they were loaded.”

Step 2 — force re-reads, but it doesn't dedupe

The documented escape hatch is COPY_OPTIONS ('force' = 'true'): “If set to true, idempotency is disabled and files are loaded regardless of whether they've been loaded before.” FILES and PATTERN only scope which files are considered; they do not bypass the already-loaded check. Only COPY_OPTIONS ('force' = 'true') disables load tracking.

COPY INTO bronze_orders
FROM 'dbfs:/tmp/copyinto_demo'
FILEFORMAT = CSV
FILES = ('orders_001.csv')
FORMAT_OPTIONS ('header' = 'true', 'inferSchema' = 'true')
COPY_OPTIONS ('force' = 'true');

Demo only — DELETE + force is non-atomic

This sequence cleans up the forced-load demonstration, but it is not a production repair. A failure after DELETE and before COPY INTO leaves the target missing rows.

DELETE FROM bronze_orders
WHERE order_id IN (1, 2);

COPY INTO bronze_orders
FROM 'dbfs:/tmp/copyinto_demo'
FILEFORMAT = CSV
FILES = ('orders_001.csv')
FORMAT_OPTIONS ('header' = 'true', 'inferSchema' = 'true')
COPY_OPTIONS ('force' = 'true');

Step 3 — the production repair

Land the corrected drop under a new versioned filename, load only that new file into a staging table, then atomically MERGE into bronze_orders on the business key.

dbutils.fs.put(
  "/tmp/copyinto_demo/orders_001_v2.csv",
  "order_id,total\n1,42.50\n2,29.99\n",
  overwrite=True
)

CREATE OR REPLACE TABLE bronze_orders_repair_stage (
  order_id INT,
  total DOUBLE
);

COPY INTO bronze_orders_repair_stage
FROM 'dbfs:/tmp/copyinto_demo'
FILEFORMAT = CSV
FILES = ('orders_001_v2.csv')
FORMAT_OPTIONS ('header' = 'true', 'inferSchema' = 'true');

MERGE INTO bronze_orders AS target
USING bronze_orders_repair_stage AS source
ON target.order_id = source.order_id
WHEN MATCHED THEN
  UPDATE SET target.total = source.total
WHEN NOT MATCHED THEN
  INSERT (order_id, total) VALUES (source.order_id, source.total);

Validate per key

Validate the repaired production target by business key, not only by row count.

SELECT order_id, COUNT(*)
FROM bronze_orders
GROUP BY order_id
HAVING COUNT(*) > 1;
-- expect zero rows

The per-key uniqueness assertion is the real invariant; the before/after row listings above are the expected output.

FILES accepts up to 1000 file names and cannot be combined with PATTERN. For directories with a very large number of files, the Databricks docs recommend Auto Loader instead.

1. Never overwrite landed files in place — land each drop under a unique versioned name.
2. First-load in VALIDATE mode: COPY INTO ... VALIDATE ALL (or VALIDATE n ROWS) validates without writing.
3. Correcting a loaded file in production: land a new versioned file, COPY INTO a staging table, then MERGE on the business key (atomic). DELETE + force is non-atomic — demo only.
4. Corrupt files aren't tracked — fix and re-run normally, no force needed.
5. FILES caps at 1000 files and can't combine with PATTERN; very large directories belong on Auto Loader.
6. COPY INTO is append-only — enforce business-key uniqueness in silver (MERGE), not at load.

Use this when

One-time or scheduled batch loads of moderate file counts; retry-safe scheduled ingestion where re-runs must be safe; SQL-only ingestion pipelines.

Avoid this when

Continuous arrival or very large directories (the docs recommend Auto Loader); in-place file overwrites as a correction workflow — COPY INTO keys on the path, not the content; relying on COPY INTO for row-level dedupe — it is append-only, so dedupe in silver.