Published November 14, 2026 · By Henry Palma

Databricks Data Engineer Associate Exam Prep, Lesson 3: COPY INTO Incremental Loading

Databricks Data Engineer Associate exam prep: how COPY INTO loads only new files, why reruns insert zero rows, format vs copy options, and when Auto Loader wins.

Exam-style question: the same COPY INTO command runs every hour, and no new files arrive between runs. How many rows does the next run load? Zero. COPY INTO remembers which files it has already loaded into a table and skips them. This post follows Lesson 3 of the Databricks Data Engineer Associate exam prep series, in Section 2 of the August 2026 exam guide, Data Ingestion and Loading (21% of the exam).

Watch on YouTube

The notebook and cheat sheet are in the A03 folder on GitHub. This is independent study material, not official Databricks training.

The exam objective

Use the COPY INTO command to incrementally load files from cloud object storage, such as Amazon S3, Azure Data Lake Storage or Google Cloud Storage, into Unity Catalog governed tables. In Free Edition, a Unity Catalog volume plays the part of cloud storage.

What COPY INTO does

COPY INTO is a SQL command that loads files from a folder into an existing Delta table. The key word is incremental: Databricks remembers which files have already been loaded into that table, and the next run skips them. That makes it idempotent and retryable. You can rerun it after a failure or on a schedule without loading the same file twice. It runs in notebooks, on SQL warehouses and in Lakeflow Jobs, and reads CSV, JSON, Parquet, Avro and other formats.

Set up a schema, a volume and the first file

In a Python notebook on Serverless, cell 1 creates a new schema (btcopyinto plus 12 random hex characters) and a volume called landing, then switches to that schema with USE so table names can stay short. Cell 2 writes orders_day1.csv with three fictional orders into /Volumes/.../landing/orders.

Create the target table first

spark.sql("""
CREATE TABLE orders_copy (
  order_id    INT,
  customer    STRING,
  amount      DOUBLE,
  order_date  DATE,
  source_file STRING
)""")

COPY INTO needs the target table to exist before it runs. Because the table lives in a Unity Catalog schema, it is governed like any other table.

Run COPY INTO

COPY INTO orders_copy
FROM (SELECT CAST(order_id AS INT) AS order_id,
             customer,
             CAST(amount AS DOUBLE) AS amount,
             CAST(order_date AS DATE) AS order_date,
             _metadata.file_name AS source_file
      FROM '/Volumes/your_catalog/your_schema/landing/orders')
FILEFORMAT = CSV
FORMAT_OPTIONS ('header' = 'true')

The SELECT casts each CSV column to the right type and adds the file name from the hidden metadata column. FORMATOPTIONS passes settings to the file reader. In the notebook, the command is kept in a Python variable, copy_sql, so it can run again.

Expected first-run result: 3 affected rows, 3 inserted rows and 0 skipped corrupt files.

Rerun it: zero rows

Running the exact same command again with no new files inserts 0 rows, and the table still has 3. COPY INTO skips every file this table has already loaded. It does not append duplicates. Even if someone edits an already-loaded file and keeps the same name, it is still skipped.

A new file arrives

Cell 6 drops ordersday2.csv with two more orders and runs the same command. Only the new file loads: 2 inserted rows, 5 in total. Grouping by sourcefile shows 3 rows from day 1 and 2 from day 2, each file loaded exactly once.

Format options vs copy options

This distinction comes up on the exam:

FORMAT_OPTIONS go to the file reader. Common ones are header, inferSchema, mergeSchema (across the source files) and ignoreCorruptFiles.
COPY_OPTIONS control the copy itself, and there are two. mergeSchema lets the target table's schema evolve. force = true turns idempotency off, so every file loads again, duplicates included.

More clauses worth knowing

PATTERN takes a glob pattern, so only matching files load.
FILES takes an explicit list of up to 1,000 file names. Use one of PATTERN or FILES, not both.
VALIDATE checks that the data parses and fits the table's schema without writing anything.
The source can be a volume path, as in this lesson, or a cloud storage path that Unity Catalog lets you read through an external location.

Let COPY INTO create and evolve the schema

CREATE TABLE IF NOT EXISTS orders_auto;

COPY INTO ordersauto FROM '/Volumes/yourcatalog/yourschema/landing/orders' FILEFORMAT = CSV FORMATOPTIONS ('header' = 'true', 'inferSchema' = 'true', 'mergeSchema' = 'true') COPY_OPTIONS ('mergeSchema' = 'true') ```

orders_auto starts as an empty placeholder with no columns, and COPY INTO fills in the schema from the files. It inserts 5 rows, because a new table has its own load history, so both existing files load. Until that first COPY INTO, the empty table cannot be used by INSERT or MERGE.

Then a day 3 file lands with a new column, channel. The same command loads 2 rows, and because mergeSchema is on in COPY_OPTIONS, the table gains a channel column. The five older rows show null for it, and the table has 7 rows.

COPY INTO or Auto Loader?

For files in the order of thousands over time, COPY INTO is fine.
For millions of files or more, use Auto Loader. It needs fewer operations to discover files and can split work into batches, so it is cheaper and more efficient at scale.
If the schema changes often, Auto Loader has richer schema inference and evolution.
Reloading a chosen subset of files is easier with COPY INTO.

For hands-on Auto Loader, see Lesson 2 and the Auto Loader tutorial.

Five exam traps

A rerun with no new files loads zero rows, not duplicates.
An edited file with the same name is still skipped.
force = true loads every file again, with duplicates.
A new target table has its own load history, so it loads all the files.
To evolve the target table's schema, set mergeSchema in COPY_OPTIONS.

Practice challenge

After cell 8, orders_copy has 5 rows and the day 3 file (with its extra channel column) is in the source folder.

You run copy_sql from cell 4 again. How many rows are inserted, what is the total, and does channel appear?
Then you add COPY_OPTIONS ('force' = 'true') and run it. How many rows are inserted, and what is the total?
Two million new files land every day. COPY INTO or Auto Loader?

Answers: (1) 2 rows, because only the day 3 file is new for orders_copy. The total is 7, and there is no channel column, because the SELECT lists exactly which columns to load. (2) Force reloads all three files, 3 + 2 + 2 = 7 more rows, for 14 in total with duplicates. (3) Auto Loader. The solution cell is commented out in the notebook.

Key takeaways

COPY INTO loads files into an existing Delta table and skips files already loaded into that table.
Format options go to the reader. Copy options hold mergeSchema and force.
Thousands of files: COPY INTO. Millions: Auto Loader.