What you will be able to do
- Explain why rerunning the same COPY INTO statement loads only files it has not loaded before
- Prepare a target Delta table for COPY INTO, including a schemaless placeholder table whose schema is inferred at load time
- Name the Unity Catalog privileges needed to load from a volume or an external location on S3, ADLS or GCS
- Predict whether a COPY INTO source path is covered by an external location's permissions
Key concept
Idempotent file loading — COPY INTO keeps track of which source files it has already loaded into the target table. When you run the same statement again, it loads only files it has not seen before. That is what makes a single repeated command an incremental loader.
1.One statement, run many times, loads only new files
COPY INTO is a SQL command that loads data from a file location into a Delta table. It reads from cloud object storage, and the documentation lists "S3, ADLS, ABFS, GCS, and Unity Catalog volumes" as places it can filter files from. You can run it in Databricks SQL, in notebooks, and in Lakeflow Jobs. The reference describes it as "a retryable and idempotent operation". The rest of this lesson builds on that property.
Idempotent means you do not need to work out which files are new before you run it. COPY INTO tracks what it has already loaded and skips those files. The documentation draws the practical conclusion: you can schedule the statement to run repeatedly, and each run loads only new data. The reference also calls the operation retryable, so a failed run can be run again with the same statement.
COPY INTO my_json_data
FROM 's3://my-bucket/jsonData'
FILEFORMAT = JSON
FILES = ('f1.json', 'f2.json', 'f3.json', 'f4.json', 'f5.json')
-- The second execution will not copy any data since the first command already loaded the data
COPY INTO my_json_data
FROM 's3://my-bucket/jsonData'
FILEFORMAT = JSON
FILES = ('f1.json', 'f2.json', 'f3.json', 'f4.json', 'f5.json')In the documentation's tutorial, new data arrives as new files written into the same directory, and rerunning the unchanged statement picks up only those files: "Only the new files are loaded."
There is one deliberate way to switch the tracking off. The force copy option, set in COPY_OPTIONS, defaults to false. According to the reference, if you set it to true, "idempotency is disabled and files are loaded regardless of whether they've been loaded before." With force = true, files that were already loaded are loaded again, so a repeated statement can load the same files a second time.
Checkpoint 1 of 5· Check yourself
A team wants to load new JSON files that land in a volume every hour. Which approach matches how COPY INTO is designed to be used?
COPY INTO already skips files it has loaded, so the same statement can be scheduled repeatedly. Each run picks up only the new files.
“you can schedule it to run repeatedly, and it will only load new data into your Delta table”Source: docs.databricks.com
Checkpoint 2 of 5· Exam question
A data engineer runs the same `COPY INTO` command nightly against a Unity Catalog table, pointing at an S3 prefix where new Parquet files land daily alongside files ingested on prior runs. The `force` COPY_OPTION has not been set. What happens to the files that were already loaded in a previous run when the command executes again?
Correct answer: A — The command skips those files entirely because `COPY INTO` tracks which source files were already loaded into the target table and only ingests newly arrived files by default.
- A. This is correct. `COPY INTO` is a retryable, idempotent operation that internally tracks which source files have already been loaded into the target table, so on subsequent runs it skips those files and only ingests files it has not seen before.
- B. This is incorrect. Delta tables do not enforce primary key uniqueness constraints that would drop duplicate rows automatically, and `COPY INTO` does not blindly reprocess every file on each run in the first place.
- C. This is incorrect. Load tracking is based on which files were previously loaded, not on comparing file modification timestamps, and a previously loaded file is skipped even if it has since been modified.
- D. This is incorrect. Re-running `COPY INTO` against a source path that was used before does not raise an error; the command simply skips files it has already loaded and completes successfully.
2.The target table: existing, and optionally schemaless
COPY INTO does not create its target. The reference defines target_table as something that "Identifies an existing Delta table", and the patterns page says it plainly: "COPY INTO must target an existing Delta table." You therefore always run a CREATE TABLE before the first load.
That table does not need columns. In Databricks Runtime 11.3 LTS and above, you can create an empty placeholder table and let COPY INTO infer the schema from the files. To do that, set mergeSchema to true in COPY_OPTIONS. The tutorial's CREATE TABLE statement contains nothing but a table name.
CREATE TABLE IF NOT EXISTS <catalog>.copy_into_tutorial.bookings_target;
COPY INTO <catalog>.copy_into_tutorial.bookings_target
FROM '/Volumes/<catalog>/copy_into_tutorial/copy_into_source/bookings'
FILEFORMAT = JSON
FORMAT_OPTIONS ('mergeSchema' = 'true')
COPY_OPTIONS ('mergeSchema' = 'true')A placeholder table has a restriction. Until COPY INTO has written to it, nothing else can use it. INSERT INTO and MERGE INTO cannot write to a schemaless Delta table, and the table only becomes queryable after COPY INTO has inserted data. If you prefer a fixed schema, declare the columns in CREATE TABLE as usual. The getting-started example declares booking_id BIGINT, user_id BIGINT, status STRING and total_amount DOUBLE, then loads JSON into them without any schema options.
Checkpoint 3 of 5· Fill the gap
Which clause completes this sample so that COPY INTO can evolve the schema of the empty placeholder table?
CREATE TABLE IF NOT EXISTS <catalog>.copy_into_tutorial.bookings_target;
COPY INTO <catalog>.copy_into_tutorial.bookings_target
FROM '/Volumes/<catalog>/copy_into_tutorial/copy_into_source/bookings'
FILEFORMAT = JSON
FORMAT_OPTIONS ('mergeSchema' = 'true')
? ('mergeSchema' = 'true')Evolving the target table's schema is controlled by mergeSchema in COPY_OPTIONS. The FORMAT_OPTIONS mergeSchema line only affects how the source files are read.
Source: docs.databricks.comA schemaless Delta table can only be written by COPY INTO. INSERT INTO and MERGE INTO are not supported on it, and it becomes queryable only after COPY INTO has loaded data into it.
3.Governed access: volumes, external locations and privileges
In a Unity Catalog workspace, the source path is governed too. Before you load, you need read access to the source and access to the target's namespace:
- READ VOLUME on a volume, or READ FILES on an external location - USE SCHEMA on the schema that contains the target table - USE CATALOG on the parent catalog
You can name the target with a three-level namespace (catalog.schema.table), or set defaults first with USE CATALOG and USE SCHEMA.
Databricks recommends volumes for COPY INTO ingestion. External locations are supported but are not the recommended approach. If the path is already defined as an external location you have permission to use, you do not pass any credential in the statement. For paths outside any external location, the reference accepts a named storage credential or inline temporary credentials. AWS_ACCESS_KEY, AWS_SECRET_KEY and AWS_SESSION_TOKEN are accepted for S3, and AZURE_SAS_TOKEN for ADLS and Azure Blob Storage.
The named credential goes in the WITH (CREDENTIAL ...) part of the source clause. The reference says you use it "only if the file location is not included in an external location". A named storage credential used for reading needs READ FILES.
Governance also reaches the target when you name it as a location, such as delta./path/to/table. You can write to an external location by having WRITE FILES permissions on that external location. Alternatively, you can have WRITE FILES permissions on a named storage credential and pass it with WITH (CREDENTIAL ...), as in the statement below.
COPY INTO delta.`/some/location` WITH (CREDENTIAL <named-credential>)| Aspect | Unity Catalog volume | External location |
|---|---|---|
| Privilege required | READ VOLUME | READ FILES |
| Example source path | /Volumes/quickstart_catalog/quickstart_schema/quickstart_volume/raw_data | s3://landing-bucket/raw-data |
| Databricks recommendation | Recommended | Supported, but not the recommended approach |
| Scope of the privilege | All nested directories under the volume | Nested directories under the location; nothing above it or parallel to it |
COPY INTO quickstart_catalog.quickstart_schema.landing_table
FROM '/Volumes/quickstart_catalog/quickstart_schema/quickstart_volume/raw_data'
FILEFORMAT = PARQUET;Checkpoint 4 of 5· Check yourself
You have READ FILES on an external location defined as s3://landing-bucket/raw-data. Which COPY INTO source path is valid?
External location privileges cover nested directories only. The bucket root is above the location and json-data sits beside it, so neither is covered.
“Permissions on this external location do not grant any privileges on directories above or parallel to the location specified.”Source: docs.databricks.com
Checkpoint 5 of 5· Exam question
After investigating a data quality bug, a team corrects several Parquet files at their original paths in cloud storage and needs `COPY INTO` to reprocess every file in the source location, including files it loaded successfully in earlier runs. Which setting should they add to the statement to force this full reload?
Correct answer: A — Set `COPY_OPTIONS ('force' = 'true')` so the command bypasses its file-tracking history and reloads every file in the source location regardless of prior load status.
- A. This is correct. The `force` COPY_OPTION explicitly disables idempotency for that run, so every file in the source location is loaded regardless of whether `COPY INTO` had already loaded it previously.
- B. This is incorrect. `mergeSchema` controls whether the target table's schema can evolve to include new columns found in the incoming data; it has no effect on whether already-loaded files are reprocessed.
- C. This is incorrect. Recursive directory traversal only changes which files are discovered under nested folders; it does not override the file-tracking history that causes previously loaded files to be skipped.
- D. This is incorrect. `VALIDATE ALL` performs a dry-run check of file parseability and schema compatibility without loading any rows, so it cannot be used to force a full reload of already-loaded files.
Exam traps
Each one states something that sounds right. Open it to see what is actually true.
1.If a source file is corrected and overwritten, the next COPY INTO run reloads it.Why is that wrong?
COPY INTO skips files it has already loaded, even if they have been modified since. Without changing options, the corrected content is not picked up.
Covered in One statement, run many times, loads only new files
2.READ FILES on an external location lets you load from anywhere in the same bucket.Why is that wrong?
The privilege applies only to the location and its nested directories. Parent and sibling paths are not covered.
Covered in Governed access: volumes, external locations and privileges
Sources
Every claim above is drawn from one of these pages, quoted as it was written on the date shown.
- 1.
“This is a retryable and idempotent operation.”
↩︎ One statement, run many times, loads only new files“If set to true, idempotency is disabled and files are loaded regardless of whether they've been loaded before.”
↩︎ One statement, run many times, loads only new files“if the path is already defined as an external location that you have permissions to use”
↩︎ Governed access: volumes, external locations and privileges“You use this credential only if the file location is not included in an external location.”
↩︎ Governed access: volumes, external locations and privileges“Defining the location as an external location and having WRITE FILES permissions on that external location.”
↩︎ Governed access: volumes, external locations and privileges“AZURE_SAS_TOKEN for ADLS and Azure Blob Storage”
↩︎ Governed access: volumes, external locations and privileges“This is true even if the files have been modified since they were loaded.”
↩︎ Exam trap 1“This is true even if the files have been modified since they were loaded.”
↩︎ Prediction - 2.
“Easily configurable file or folder filters from cloud storage, including S3, ADLS, ABFS, GCS, and Unity Catalog volumes.”
↩︎ One statement, run many times, loads only new files“create empty placeholder Delta tables so that the schema is inferred during a COPY INTO command”
↩︎ The target table: existing, and optionally schemaless“INSERT INTO and MERGE INTO are not supported to write data into schemaless Delta tables.”
↩︎ The target table: existing, and optionally schemaless“COPY INTO is retriable and idempotent — files in the source location that have already been loaded are skipped on subsequent runs.”
↩︎ Key concept“you can schedule it to run repeatedly, and it will only load new data into your Delta table”
↩︎ Checkpoint - 3.
“You can use COPY INTO in Databricks SQL, notebooks, and Lakeflow Jobs.”
↩︎ One statement, run many times, loads only new files - 4.https://docs.databricks.com/aws/en/ingestion/cloud-object-storage/copy-into/tutorial-notebookOfficial docs
“Then run the COPY INTO command from Step 3 again and preview the table to confirm the new records.”
↩︎ One statement, run many times, loads only new files - 5.
“COPY INTO must target an existing Delta table.”
↩︎ The target table: existing, and optionally schemaless - 6.https://docs.databricks.com/aws/en/ingestion/cloud-object-storage/copy-into/unity-catalogOfficial docs
“The READ VOLUME privilege on a volume or the READ FILES privilege on an external location.”
↩︎ Governed access: volumes, external locations and privileges“External locations are supported but are not the recommended approach.”
↩︎ Governed access: volumes, external locations and privileges“Permissions on this external location do not grant any privileges on directories above or parallel to the location specified.”
↩︎ Exam trap 2“Permissions on this external location do not grant any privileges on directories above or parallel to the location specified.”
↩︎ Checkpoint