What you will be able to do
- Choose between FILES and PATTERN, and reshape source columns with a SELECT inside COPY INTO
- Tell apart mergeSchema in FORMAT_OPTIONS and mergeSchema in COPY_OPTIONS
- Use VALIDATE, ignoreCorruptFiles and force, and predict their effect on idempotency
- Decide between COPY INTO and Auto Loader based on file volume and reprocessing needs
1.Choosing which files load and how their columns map
By default, COPY INTO reads every file under the source path in the declared FILEFORMAT. The COPY INTO reference page lists CSV, JSON, AVRO, ORC, PARQUET, TEXT and BINARYFILE as the supported values, and the getting-started page also lists XML among the supported source formats. If the source path is a root path, add a slash (/) at the end of it, for example s3://my-bucket/. Two clauses narrow the selection, and you can use only one of them per statement:
- FILES is an explicit list of file names, with a limit of 1000 files.
- PATTERN is a glob matched against paths under the source directory. It supports ?, *, character sets such as [a-g], and string sets such as {ab,cd}.
To reshape data as it loads, put a SELECT in the FROM clause. It can cast, rename, add constants and even use window operations. Aggregation, however, is limited to global aggregates: you cannot use GROUP BY in this syntax.
COPY INTO my_delta_table
FROM (SELECT to_date(dt) dt, event as measurement, quantity::double
FROM 's3://my-bucket/avroData')
FILEFORMAT = AVROHeaderless CSV is a special case. A SELECT can cast and name the positional _c0, _c1 and _c2 columns. The alternative is BY POSITION, or an explicit column list after the table name, which matches source columns to target columns by ordinal position. That syntax is supported only for headerless CSV. If the column counts do not match, COPY INTO raises an error.
Checkpoint 1 of 4· Fill the gap
Which keyword completes this sample so that it loads only file_a.csv through file_g.csv under folder1?
COPY INTO target_table
FROM (SELECT _c0::bigint key, _c1::int index, _c2 textData
FROM 's3://bucket/base/path')
FILEFORMAT = CSV
? = 'folder1/file_[a-g].csv'A glob such as file_[a-g].csv is given with PATTERN. FILES takes a parenthesised list of literal file names and cannot be combined with PATTERN.
Source: docs.databricks.comCheckpoint 2 of 4· Check yourself
You want COPY INTO to map the columns of a CSV file to target columns by position using BY POSITION. The files have a header row. What happens?
Positional matching requires FILEFORMAT = CSV with headers set to false. For other files, use name matching or a SELECT.
“This parameter is only supported for headerless CSV file format.”Source: docs.databricks.com
2.Schema options, validation, corrupt files and force
Two options share the name mergeSchema, and they act on different things. The FORMAT_OPTIONS version controls how the source files are read. The COPY_OPTIONS version controls whether the target table may change.
| Option | Set in | What it controls |
|---|---|---|
| inferSchema | FORMAT_OPTIONS | Infer data types of parsed records, or assume all columns are StringType |
| mergeSchema | FORMAT_OPTIONS | Infer the schema across multiple source files and merge each file's schema |
| mergeSchema | COPY_OPTIONS | Evolve the schema of the target Delta table based on the input schema |
| ignoreCorruptFiles | FORMAT_OPTIONS | Skip files that cannot be read due to corruption |
If the source files all share one schema, Databricks recommends leaving the FORMAT_OPTIONS mergeSchema at its default of false. If the input schema and the target schema are the same, the COPY_OPTIONS mergeSchema can also be false.
To check a load before committing it, add VALIDATE. This option needs Databricks Runtime 10.4 LTS or above. It checks three things without writing to the table: whether the data parses, whether the schema matches or would need to evolve, and whether nullability and check constraints are met. VALIDATE 15 ROWS limits the check, and a ROWS value under 50 returns a preview of up to 50 rows.
COPY INTO my_table
FROM '/path/to/files'
FILEFORMAT = <format>
[VALIDATE ALL]
FORMAT_OPTIONS ('ignoreCorruptFiles' = 'true')The ignoreCorruptFiles option is available in Databricks Runtime 11.3 LTS and above. The num_skipped_corrupt_files column in the COPY INTO result reports how many files were skipped. The same count appears as numSkippedCorruptFiles in DESCRIBE HISTORY. The last option to know is force in COPY_OPTIONS, which defaults to false. Setting it to true switches idempotency off, so COPY INTO loads every matching file whether or not it has been loaded before.
Checkpoint 3 of 4· Match them up
Match each setting to its effect
Tap a term, then the definition that fits it.
force turns idempotency off, and COPY_OPTIONS mergeSchema evolves the target table. VALIDATE checks the data without writing it, and ignoreCorruptFiles skips unreadable files without recording them as loaded.
“If set to true, idempotency is disabled and files are loaded regardless of whether they've been loaded before.”Source: docs.databricks.com
3.Concurrency, scale and when to choose Auto Loader instead
COPY INTO can run concurrently against the same table. Concurrent runs succeed as long as each one works on a distinct set of input files; otherwise you get a transaction conflict. Concurrency is not a way to speed things up, though: one command over many files usually beats many single-file commands. The documentation gives two legitimate reasons to run concurrently. One is when uncoordinated producers cannot share a single invocation. The other is when a very large directory is ingested one sub-directory at a time.
That second case leads to the decision the exam tests: COPY INTO or Auto Loader. For directories with a very large number of files, Databricks recommends Auto Loader when possible, rather than splitting the work across concurrent COPY INTO commands.
| Consideration | COPY INTO | Auto Loader |
|---|---|---|
| Expected file count over time | Thousands | Millions or more |
| Cost and efficiency at scale | More file-discovery operations | Fewer operations; can split processing into batches |
| Frequently evolving schema | Weaker primitives for inference and evolution | Better primitives for inference and evolution |
| Reloading a subset of re-uploaded files | Easier to manage | Harder to reprocess a select subset |
The two can also work together: COPY INTO can reload a subset of files while an Auto Loader stream keeps running. For SQL users who want a more scalable and robust option than COPY INTO, Databricks recommends streaming tables.
Checkpoint 4 of 4· Exam question
A source S3 bucket receives files from multiple upstream systems, and a data engineer wants a single `COPY INTO` run to ingest only the files under a `landing/2026/09/` prefix whose names end in `.csv`, ignoring `.json` and `.txt` files that land in the same prefix. Which clause should the engineer add to the statement?
Correct answer: A — `PATTERN = 'landing/2026/09/*.csv'` so the command matches only files under that prefix whose names end with the `.csv` extension using glob-style matching.
- A. This is correct. `PATTERN` accepts glob-style expressions, so restricting it to `*.csv` under the target prefix filters the source listing down to only the matching CSV files.
- B. This is incorrect. `FILES` expects a list of explicit file names rather than a glob expression, and it cannot be combined with wildcard matching to select a subset of files by extension.
- C. This is incorrect. There is no `extension` key recognized under `FORMAT_OPTIONS`; format options configure how a given file format is parsed, not which files are selected for the load.
- D. This is incorrect. `FILEFORMAT` tells `COPY INTO` which parser to use for the selected files; it does not filter the source listing by file extension, so non-CSV files would still be attempted.
Exam traps
Each one states something that sounds right. Open it to see what is actually true.
1.Setting mergeSchema in FORMAT_OPTIONS is enough to let new columns be added to the target table.Why is that wrong?
FORMAT_OPTIONS mergeSchema only merges schemas across the source files. Evolving the target Delta table requires mergeSchema in COPY_OPTIONS.
Covered in Schema options, validation, corrupt files and force
2.Adding force = true to a scheduled COPY INTO is a harmless way to make sure nothing is missed.Why is that wrong?
force disables idempotency, so every run reloads files that were already loaded and duplicates their rows.
Covered in Schema options, validation, corrupt files and force
3.Running many concurrent single-file COPY INTO commands is faster than one command over all the files.Why is that wrong?
Databricks advises against concurrency for performance. A single command over many files typically performs better.
Covered in Concurrency, scale and when to choose Auto Loader instead
Practise it for real
Load JSON files incrementally from a Unity Catalog volume into a schemaless Delta table with COPY INTO, and confirm that a rerun loads only the new files.
1.In a notebook attached to compute, create the schema <catalog>.copy_into_tutorial and the volume copy_into_source inside it, using a catalog where you have CREATE SCHEMA and CREATE VOLUME.
Why: The volume is the governed landing zone that COPY INTO reads from.
You should see: The schema and volume exist and appear in Catalog Explorer.
2.Use Python to write the first 20 rows of samples.wanderbricks.bookings, ordered by booking_id, as JSON to /Volumes/<catalog>/copy_into_tutorial/copy_into_source/bookings.
Why: This simulates a first batch of files arriving from an external system.
You should see: JSON files appear under the bookings directory of the volume.
3.Run CREATE TABLE IF NOT EXISTS <catalog>.copy_into_tutorial.bookings_target, then COPY INTO it FROM the bookings volume path with FILEFORMAT = JSON and mergeSchema = 'true' in both FORMAT_OPTIONS and COPY_OPTIONS.
Why: The table has no columns yet, so COPY_OPTIONS mergeSchema lets COPY INTO infer and apply the schema.
You should see: The table contains 20 rows and a schema inferred from the JSON.
4.Write a second batch of 20 rows, ordered by booking_id descending, as JSON to the same volume directory.
Why: New files are what COPY INTO treats as unloaded.
You should see: More JSON files appear in the directory.
5.Rerun the identical COPY INTO statement, then SELECT COUNT(*) from the table.
Why: Idempotency means only the files from the second batch should be loaded.
You should see: The count rises by the second batch only; the first batch is not loaded twice.
Stuck? Get a nudge
If the second run loads nothing, check that the new batch was written as new files in the same source directory.
Sources
Every claim above is drawn from one of these pages, quoted as it was written on the date shown.
- 1.
“A list of file names to load, with a limit of 1000 files. Cannot be specified with PATTERN.”
↩︎ Choosing which files load and how their columns map“One of CSV, JSON, AVRO, ORC, PARQUET, TEXT, BINARYFILE.”
↩︎ Choosing which files load and how their columns map“If the source file path is a root path, please add a slash (/) at the end of the file path”
↩︎ Choosing which files load and how their columns map“You can use aggregation expressions only for global aggregates–you cannot GROUP BY on columns with this syntax.”
↩︎ Choosing which files load and how their columns map“The data that is to be loaded into a table is validated but not written to the table.”
↩︎ Schema options, validation, corrupt files and force“As long as COPY INTO is invoked concurrently on distinct sets of input files, each invocation should eventually succeed”
↩︎ Concurrency, scale and when to choose Auto Loader instead“When ingesting directories with a very large number of files, Databricks recommends using Auto Loader when possible.”
↩︎ Concurrency, scale and when to choose Auto Loader instead“If set to true, idempotency is disabled and files are loaded regardless of whether they've been loaded before.”
↩︎ Exam trap 2“a single COPY INTO command with multiple files typically performs better than running concurrent COPY INTO commands with a single file each”
↩︎ Exam trap 3“This parameter is only supported for headerless CSV file format.”
↩︎ Checkpoint“If set to true, idempotency is disabled and files are loaded regardless of whether they've been loaded before.”
↩︎ Checkpoint - 2.
“Support for multiple source file formats: CSV, JSON, XML, Avro, ORC, Parquet, text, and binary files.”
↩︎ Choosing which files load and how their columns map“For a more scalable and robust file ingestion experience, Databricks recommends that SQL users use streaming tables.”
↩︎ Concurrency, scale and when to choose Auto Loader instead - 3.
“mergeSchema: Whether to infer the schema across multiple source files and to merge the schema of each source file.”
↩︎ Schema options, validation, corrupt files and force“You can see which files are corrupt by running COPY INTO in VALIDATE mode.”
↩︎ Schema options, validation, corrupt files and force“ignoreCorruptFiles is available in Databricks Runtime 11.3 LTS and above.”
↩︎ Schema options, validation, corrupt files and force“mergeSchema: Whether to evolve the schema of the target Delta table based on the input schema.”
↩︎ Exam trap 1“Corrupt files aren't tracked by COPY INTO, so they can be reloaded in a subsequent run if the corruption is fixed.”
↩︎ Prediction - 4.
“If you're going to ingest files in the order of thousands over time, you can use COPY INTO.”
↩︎ Concurrency, scale and when to choose Auto Loader instead“Auto Loader provides better primitive data types around schema inference and evolution.”
↩︎ Concurrency, scale and when to choose Auto Loader instead“you can use COPY INTO to reload the subset of files while an Auto Loader stream is running simultaneously.”
↩︎ Concurrency, scale and when to choose Auto Loader instead