Databricks DE · Topic 4.2

COPY INTO

A SQL command that batch-loads files into a Delta table and skips files it already loaded — idempotent, incremental, no streaming setup. This deep dive walks each capability with the code.

SQL commandidempotentSELECT transformVALIDATEforce reload

The one-liner

COPY INTO loads files from cloud storage / a UC Volume into a Delta table and skips already-loaded files on re-run — the simple, SQL-first, batch sibling of Auto Loader.

🏦 Analogy: importing bank statements into a spreadsheet. COPY INTO remembers which files it already imported, so re-running the import won't double-count — it just adds the new statements.

Idempotency — re-run safely

▸ run COPY INTO repeatedly; already-loaded files are skipped

Source empty.

1 · Basic load & the idempotency mechanism

COPY INTO tracks loaded files per target table and skips them on re-run — even if the file was later modified (tracked by path, not content). FORMAT_OPTIONS = parsing; COPY_OPTIONS = load behavior.

COPY INTO cat.sch.bronze_sales
FROM '/Volumes/cat/sch/vol/sales/'
FILEFORMAT = CSV
FORMAT_OPTIONS ('header' = 'true', 'inferSchema' = 'true')   -- parsing
COPY_OPTIONS  ('mergeSchema' = 'true');                       -- load behavior

PySpark — COPY INTO is SQL, run via spark.sql

spark.sql("""COPY INTO cat.sch.bronze_sales
  FROM '/Volumes/cat/sch/vol/sales/' FILEFORMAT = CSV
  FORMAT_OPTIONS ('header' = 'true')""")

2 · Transform on load with a SELECT

Wrap a SELECT to cast / rename / derive columns during ingestion — type raw strings without a second pass.

COPY INTO cat.sch.bronze_sales
FROM (
  SELECT CAST(order_id AS BIGINT) AS order_id,
         CAST(amount AS DECIMAL(10,2)) AS amount,
         to_date(order_date, 'yyyy-MM-dd') AS order_date
  FROM '/Volumes/cat/sch/vol/sales/'
)
FILEFORMAT = CSV FORMAT_OPTIONS ('header' = 'true');

3 · Target files (PATTERN / FILES) & dry-run (VALIDATE)

-- PATTERN and FILES are mutually exclusive; FILES caps at 1000
COPY INTO cat.sch.bronze_sales FROM '/Volumes/cat/sch/vol/sales/'
FILEFORMAT = CSV PATTERN = '2026-*.csv';            -- glob filter

-- Test parsing/schema/constraints WITHOUT writing
COPY INTO cat.sch.bronze_sales FROM '/Volumes/cat/sch/vol/sales/'
FILEFORMAT = CSV VALIDATE 50 ROWS;             -- or VALIDATE ALL

4 · Force a reload + evolve schema

COPY INTO cat.sch.bronze_sales FROM '/Volumes/cat/sch/vol/sales/'
FILEFORMAT = CSV
COPY_OPTIONS ('force' = 'true',        -- disable idempotency: reload everything
              'mergeSchema' = 'true');  -- evolve new columns in

💡 force=true is how you re-ingest after a fix (a modified same-path file is otherwise skipped). mergeSchema=true handles schema drift.

COPY INTO vs Auto Loader — click each

COPY INTO — SQL command, batch/scheduled, tracks loaded files.

✅ Simple, pure SQL, no checkpoint plumbing
✅ Great for periodic loads of modest file volumes
⚠️ Less scalable for millions of files

Auto Loader — cloudFiles streaming source, checkpointed.

✅ Scales to millions/billions of files; continuous or availableNow
✅ File-notification / file-events for huge dirs
⚠️ More setup (stream + checkpoint)

	COPY INTO	Auto Loader
Interface	SQL command	Structured Streaming
Scale	Thousands of files	Millions+
State	Tracks loaded files	Checkpoint (RocksDB)
Best for	Simple periodic loads	Large-scale continuous

Why it matters

Re-running ingestion is normal (retries, scheduled jobs); without idempotency you'd get duplicates. COPY INTO prevents that automatically.
Simplest path for periodic batch loads — pure SQL. A common "Auto Loader vs COPY INTO?" interview question.

📄 Real-world use case: a vendor drops a daily CSV into a UC Volume. A scheduled COPY INTO bronze_sales FROM '...' loads each new day's file exactly once — no checkpoint plumbing, just SQL in a job.

Uses, edge cases & limitations

Uses: scheduled batch loads of modest volumes; quick one-off loads; SQL-only pipelines; transform-on-load via SELECT.
Edge case: very large/high-frequency dirs → prefer Auto Loader / streaming tables; a modified-but-same-path file is skipped (use force); schema drift needs mergeSchema.
Limitation: less scalable than Auto Loader for huge file counts; idempotency is per-target-table file tracking — loading the same files into a different table loads them again. It dedups files, not rows.

Common gotchas

❌ Expecting it to scale to millions of files like Auto Loader — it won't.
❌ Assuming a re-run reloads everything — it skips already-loaded files (use force=true to override).
❌ Expecting an edited/re-uploaded same-path file to reload — it's skipped; use force.
❌ Forgetting mergeSchema when the incoming schema changes → load errors.
❌ Thinking it dedups rows — it dedups files, not record contents.