Skip to content

Databricks COPY INTO: Incremental File Ingestion and Safe Retries

Step 5 of 30

Databricks COPY INTO: Incremental File Ingestion and Safe Retries

Introduction

Databricks COPY INTO provides incremental file ingestion into Delta tables without maintaining a streaming checkpoint. This lesson opens the Data Ingestion and Loading section of the official exam outline, which carries about 21% of the scored questions (roughly nine or ten of 45). Together with lessons 4 and 5, it covers how data gets into Delta tables: batch, streaming and incremental patterns, uploads, Unity Catalog volumes, the COPY INTO command, and an overview of Lakeflow Connect.

After this lesson you will be able to choose between batch, streaming and incremental loading, load files from cloud storage with COPY INTO, explain why COPY INTO is idempotent, and recognize when another method fits better.

Learning objectives

  • Distinguish batch, streaming and incremental ingestion and pick one for a scenario.
  • Upload local files and read files from Unity Catalog volumes.
  • Write COPY INTO statements for CSV, JSON and Parquet from ADLS, S3 and GCS, using options such as FORMAT_OPTIONS and COPY_OPTIONS.
  • Explain how COPY INTO tracks loaded files and what force does.
  • Describe standard connectors and managed connectors in Lakeflow Connect at overview level.

1. Ingestion patterns

Ingestion is the first step of the medallion flow: raw data lands in bronze tables with as little change as possible. Three patterns appear on the exam.

Pattern Idea Latency Typical tool
Batch Load a full or periodic extract at a scheduled time Hours INSERT, CREATE TABLE AS SELECT, jobs
Incremental batch Each run loads only new or changed data since the last run Minutes to hours COPY INTO, Auto Loader with availableNow
Streaming Continuously process data as it arrives Seconds to minutes Structured Streaming, Auto Loader, streaming tables

Choosing factors are latency requirement, data volume, number of files, cost and operational simplicity. A daily report does not need streaming; a fraud alert does. Incremental loading is often the best compromise: it avoids reprocessing everything and does not need an always-on cluster.

Example. A logistics firm receives about 3,000 small CSV files per day in cloud storage from scanners. Managers need the data in the warehouse every morning. Reloading the whole history nightly wastes compute as it grows; an incremental batch that loads only the new files each night (COPY INTO or Auto Loader in triggered mode) is the efficient choice.

2. Getting files into Databricks

Upload local files

For small, one-off files (a lookup CSV from a colleague, a sample for testing), the Create or modify table upload UI lets you drag a file, preview the schema and create a Unity Catalog managed table. This is convenient but manual and not repeatable, so it is not a production ingestion method.

Unity Catalog volumes

A volume is a governed storage location for files. You can upload files into it from the UI or the CLI, or write to it from jobs, and read them using the path pattern:

Text
/Volumes/<catalog>/<schema>/<volume>/<path>
SQL
CREATE VOLUME IF NOT EXISTS prod.raw.landing;
-- Files placed in /Volumes/prod/raw/landing/orders/ can now be read in SQL or Spark
SELECT * FROM read_files('/Volumes/prod/raw/landing/orders/', format => 'csv', header => true);

Volumes are the recommended landing zone for raw files because access is controlled with READ VOLUME and WRITE VOLUME privileges, rather than cloud credentials spread across notebooks. Supported file reads can contribute file-path lineage; do not assume every file operation is captured.

Cloud object storage with external locations

For ADLS Gen2 (abfss://container@account.dfs.core.windows.net/path), Amazon S3 (s3://bucket/path) and Google Cloud Storage (gs://bucket/path), Unity Catalog uses an external location: a storage path linked to a storage credential. Users need READ FILES on the external location to read files from it directly. Granting access to the location is safer than giving users the credential or keys.

Before running the examples, create the catalogs and schemas and obtain the required grants. Reading a volume needs USE CATALOG, USE SCHEMA and READ VOLUME; creating a table needs CREATE TABLE on its schema, and loading an existing target needs MODIFY plus parent usage privileges. The sample cloud paths are placeholders to replace with authorized locations.

3. COPY INTO

COPY INTO is a SQL command that loads files from a location into an existing Delta table. It is idempotent and incremental: it remembers which files it already loaded, so running it again loads only new files.

Basic syntax

SQL
CREATE TABLE IF NOT EXISTS prod.bronze.orders;   -- schemaless table, schema inferred on first load

COPY INTO prod.bronze.orders
FROM 's3://acme-landing/orders/'
FILEFORMAT = CSV
FORMAT_OPTIONS ('header' = 'true', 'inferSchema' = 'true')
COPY_OPTIONS ('mergeSchema' = 'true');

Key parts:

  • Target table: must exist (it can be created without columns, in which case the schema is inferred and evolved when mergeSchema is true).
  • FROM: a path (directory or file pattern) in a volume or external location, or a subquery with a SELECT transformation on the files.
  • FILEFORMAT: CSV, JSON, AVRO, ORC, PARQUET, TEXT or BINARYFILE.
  • FORMAT_OPTIONS: reader options (header, delimiter, multiLine, inferSchema).
  • COPY_OPTIONS: copy behavior (force, mergeSchema). Source schema merging in FORMAT_OPTIONS combines schemas discovered across input files; target merging in COPY_OPTIONS evolves the Delta table. These are separate settings.
  • FILES or PATTERN: restrict to specific files or a glob such as PATTERN = '2026-10-*.csv'.

Loading with a transformation

SQL
CREATE TABLE IF NOT EXISTS prod.bronze.orders_typed (
  order_id BIGINT, customer_id BIGINT, amount DECIMAL(10,2),
  order_ts TIMESTAMP, source_file STRING
);

COPY INTO prod.bronze.orders_typed
FROM (
  SELECT order_id::BIGINT AS order_id,
         customer_id::BIGINT AS customer_id,
         CAST(amount AS DECIMAL(10,2)) AS amount,
         to_timestamp(order_ts) AS order_ts,
         _metadata.file_path AS source_file
  FROM 'abfss://landing@acmedata.dfs.core.windows.net/orders/'
)
FILEFORMAT = CSV
FORMAT_OPTIONS ('header' = 'true');

The _metadata column exposes file information (file_path, file_name, file_modification_time), useful for lineage inside bronze tables.

Idempotency and file tracking

COPY INTO keeps file-loading state for the target table. A previously loaded path is skipped even when its contents have subsequently changed. Use immutable landing files; this is not row-level deduplication or change data capture. When re-executed:

  • Already loaded files are skipped by default. Identical rows delivered under a new file name can still be appended.
  • Only new files in the source are loaded.
  • force = true reloads the selected files regardless of prior loading. Restrict the reload scope and reconcile the target; deleting target rows alone is not a reliable reset of file-loading state.

COPY INTO is useful for modest scheduled file loads. For large file populations or scalable incremental discovery, study Auto Loader ingestion. Databricks now recommends streaming tables for a more scalable SQL ingestion experience; these are workload choices rather than a universal hard file-count limit.

Worked example: fixing duplicates after a retry

Scenario. A nightly job runs COPY INTO prod.bronze.orders FROM '/Volumes/prod/raw/landing/orders/' FILEFORMAT = CSV with 'force' = 'true' added by a developer "to be safe". After a network failure the job is retried and the daily revenue is doubled. What is wrong, and what is the fix?

Reasoning.

  1. Without force, retrying the same selected files against the same target skips successful prior loads. Business duplicates from differently named files still need separate handling.
  2. With force = true, tracking is bypassed and every file in the folder is re-read on every run, adding duplicates each time, not only on retry.
  3. Stop the schedule and remove force. Identify the affected commits and preserve valid later writes. Reconcile a staged copy of the affected batch against business keys, or use a reviewed snapshot recovery plan. Do not assume that a blanket reload after RESTORE or row deletion will reset file-loading state.

Why other fixes are weaker. Adding DISTINCT in downstream queries hides the symptom and costs compute. Deleting the source files loses the raw data. Switching to Auto Loader would also work for future loads, but the cause of the duplicates is the force option, not the command.

4. Choosing between COPY INTO and other methods

Need Better choice Reason
Simple scheduled load of a few thousand files with SQL COPY INTO Idempotent, simple SQL, no checkpoint management
Millions of files, continuous arrival, schema evolution Auto Loader Scalable file discovery, checkpoints, rescued data
Database or SaaS source with change capture Lakeflow Connect managed connector No code, incremental
One-time load of a small file Upload UI or CREATE TABLE AS SELECT from read_files Simple
Declarative end-to-end pipeline Streaming table in Lakeflow Spark Declarative Pipelines (formerly DLT) Managed, includes data quality

CREATE TABLE AS SELECT and INSERT INTO ... SELECT from read_files do not track loaded files; running them again reads everything again. That difference (tracking versus no tracking) is a frequent exam point.

5. Lakeflow Connect overview

Lakeflow Connect is the Databricks family of ingestion connectors. At overview level, the exam expects you to know two kinds:

  • Standard connectors: connect to cloud object storage and message buses (for example Auto Loader, Kafka and Kinesis-style sources) and are configured in code or pipelines. You control the pipeline logic.
  • Managed connectors: no-code or low-code connectors for enterprise applications and databases (such as Salesforce, Workday, SQL Server, or Google Analytics) that Databricks manages, with incremental ingestion into Delta tables through the UI or API. They remove the need to write extraction code and handle change tracking.

The current connector catalog is organized by database, SaaS, file and streaming sources, with additional connector categories. Connector availability and incremental semantics vary by source and release state. Use the guide's standard-versus-managed distinction for the exam, then verify the actual connector. Continue with Lakeflow Connect and external ingestion sources.

6. Reading files with read_files and loading options

The read_files table-valued function reads files from a volume or external location in SQL and infers the schema. It is the easiest way to preview data before choosing a loading method:

SQL
SELECT * FROM read_files(
  '/Volumes/prod/raw/landing/orders/',
  format => 'csv',
  header => true,
  delimiter => ';'
) LIMIT 10;

Once the preview looks right, you have several ways to persist it. They differ in how they treat repeated runs:

Statement Creates table? Tracks loaded files? Re-running it
CREATE TABLE AS SELECT ... FROM read_files(...) Yes No Fails if the table exists, or rewrites it with CREATE OR REPLACE
INSERT INTO ... SELECT ... FROM read_files(...) No No Appends everything again (duplicates)
COPY INTO No (target must exist) Yes Loads only new files
Auto Loader / streaming table Yes or no Yes (checkpoint) Loads only new files

File formats and what to expect

  • CSV does not carry a typed schema. For COPY INTO, use explicit casts or deliberate inferSchema settings. read_files instead infers CSV column types by default. Set header and delimiter explicitly for predictable parsing.
  • JSON with one object per line works by default. A file containing a single multi-line object or an array needs multiLine = true.
  • Parquet and Avro carry their schema, so no inference options are needed and types are preserved.
  • BINARYFILE loads each file as a row with its path, length, modification time and raw content, which is useful for images and documents.
  • TEXT loads each line as a value string, useful for logs that you parse later.

Worked example: loading a semicolon-delimited export

Scenario. A European partner sends sales_2026-10.csv with ; as delimiter, a header row, and dates like 24/10/2026. The first load into prod.bronze.partner_sales yields a single column called order_id;customer;amount;sale_date. What is wrong and how should the load be written?

Reasoning.

  1. The reader assumed a comma delimiter, so the whole line became one column.
  2. Provide 'delimiter' = ';' and 'header' = 'true' in FORMAT_OPTIONS.
  3. Dates in dd/MM/yyyy are not parsed automatically as a proper date; convert them in a SELECT with to_date(sale_date, 'dd/MM/yyyy').
  4. Because this is a recurring feed, use COPY INTO so each file loads once.
SQL
CREATE TABLE IF NOT EXISTS prod.bronze.partner_sales (
  order_id BIGINT, customer STRING, amount DECIMAL(12,2), sale_date DATE
);

COPY INTO prod.bronze.partner_sales
FROM (
  SELECT order_id::BIGINT AS order_id, customer, amount::DECIMAL(12,2) AS amount,
         to_date(sale_date, 'dd/MM/yyyy') AS sale_date
  FROM '/Volumes/prod/raw/landing/partner/'
)
FILEFORMAT = CSV
FORMAT_OPTIONS ('header' = 'true', 'delimiter' = ';');

Why other choices are weaker. Leaving everything as strings pushes cleaning work to every consumer. Opening the file in a spreadsheet and re-saving changes the format and cannot be repeated automatically. INSERT ... SELECT would append the same files again at the next run.

7. Choosing a pattern: a second example

Scenario. A bank must ingest card transactions so that a fraud model sees them within 30 seconds. Separately, a monthly regulatory report needs the full month's ledger.

  • Fraud feed: streaming ingestion, because the latency target is seconds. Choose a suitable message-bus stream or continuously running Auto Loader micro-batches, and measure the end-to-end latency; a landing-folder design does not guarantee 30 seconds. Do not confuse a continuous pipeline with Spark's Trigger.Continuous.
  • Monthly ledger: batch ingestion on a schedule, because latency is not critical; choose compatible compute after estimating and measuring total cost.

The point to remember: the freshness requirement, not personal preference, selects the pattern, and a streaming design costs more to run, so do not use it when a schedule is enough.

8. Managing ingestion in production

A good ingestion job does more than copy files:

  • Keep raw data. Bronze tables should preserve the source as received, with extra columns for the source file and load time, so you can reprocess after a bug.
  • Add audit columns. _metadata.file_path and current_timestamp() let you trace each row to a file and a run.
  • Handle bad data deliberately. Quarantine malformed records or fail the load when correctness requires it. PERMISSIVE parsing or rescued data requires monitoring; silently discarding corrupt files can make financial reports incomplete.
  • Make it observable. Review command output, table history and source-to-target counts. When skipping corrupt files is explicitly allowed, monitor num_skipped_corrupt_files and repair them; successful completion alone does not prove completeness.
  • Make it safe to rerun. Separate file-level retry safety from business-key uniqueness; both need an ingestion contract.

Common pitfalls and exam tips

  • Pitfall: believing COPY INTO needs a checkpoint. It does not; it tracks loaded files in the table metadata.
  • Pitfall: using force as a default. It reloads everything and creates duplicates.
  • Pitfall: missing header in FORMAT_OPTIONS for CSV with column names, which produces _c0, _c1 columns.
  • Pitfall: expecting schema evolution without mergeSchema. New columns in later files are not added unless mergeSchema is true.
  • Pitfall: cloud credentials in code. Use volumes or external locations with Unity Catalog privileges.
  • Pitfall: using COPY INTO for millions of files. Auto Loader scales better.
  • Exam tip. If the question stresses "loads only new files, safe to rerun", the answer is COPY INTO or Auto Loader, not INSERT ... SELECT.
  • Exam tip. "Code-free ingestion from Salesforce or a database" points to a Lakeflow Connect managed connector.

Mini case study

Case. Tailwind Airlines receives hourly JSON files with baggage scans in s3://tw-landing/baggage/, about 400 files per day. Analysts want the data in prod.bronze.baggage each hour with no duplicates, even if the job is retried. The schema occasionally gains new fields. The data engineer has READ FILES on the external location and the parent usage, table-creation and target-write privileges needed for the load. Landing files are immutable and uniquely named per delivered batch.

Tasks. Propose the ingestion method and write the statement.

Solution. With about 400 files per day, COPY INTO is enough and avoids checkpoint management. Run it hourly from a Lakeflow Job:

SQL
CREATE TABLE IF NOT EXISTS prod.bronze.baggage;

COPY INTO prod.bronze.baggage
FROM 's3://tw-landing/baggage/'
FILEFORMAT = JSON
FORMAT_OPTIONS ('mergeSchema' = 'true')
COPY_OPTIONS ('mergeSchema' = 'true');

File tracking makes retries of the same files safe. Source-schema merging discovers fields across the JSON inputs, target-schema merging permits new columns, and no force option is set. If the volume grew to millions of files or the latency fell to seconds, they would move to Auto Loader.

Key terms

  • File-level idempotency: repeating the same load against the same target skips previously loaded files; it does not enforce unique business records.
  • Bronze table: raw landing table in the medallion architecture.
  • External location: Unity Catalog object linking a storage path to a storage credential.
  • Volume: governed file location, /Volumes/catalog/schema/volume.
  • force: COPY INTO option that ignores file tracking.
  • Managed connector: Lakeflow Connect connector that Databricks operates for enterprise sources.

Summary checklist

  • I can choose between batch, incremental and streaming ingestion.
  • I know volumes and external locations are governed by privileges.
  • I can write a COPY INTO statement with FILEFORMAT, FORMAT_OPTIONS and COPY_OPTIONS.
  • I know COPY INTO skips already loaded files and force disables that.
  • I know mergeSchema allows new columns.
  • I can say when Auto Loader or a managed connector is better.

For Delta history and recovery, revisit Delta Lake transactions and Unity Catalog architecture.

Official sources