Ingestion Patterns and COPY INTO

Introduction

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:

/Volumes/<catalog>/<schema>/<volume>/<path>

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. They also appear in lineage.

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.

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

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).
FILES or PATTERN: restrict to specific files or a glob such as PATTERN = '2026-10-*.csv'.

Loading with a transformation

COPY INTO prod.bronze.orders
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 records every file it has loaded (by path and modification information) in the target table's metadata. When re-executed:

Files already loaded are skipped, so a retry after a failed run does not produce duplicates.
Only new files in the source are loaded.
If you set COPY_OPTIONS ('force' = 'true'), the tracking is ignored and all files are loaded again, which can create duplicate rows. Use force only for deliberate reloads, such as after truncating the table.

This tracking has a scale limit. COPY INTO works well for thousands of files; for millions of files or when low latency matters, Auto Loader (lesson 4) is the recommended tool because it scales file discovery more efficiently.

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.

Without force, COPY INTO skips files it already loaded, so a retry is harmless.
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.
Fix: remove force. To clean existing duplicates, restore the table to the version before the bad runs (DESCRIBE HISTORY, then RESTORE TABLE ... TO VERSION AS OF n) and rerun once.

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.

Lesson 5 shows how to choose between them and other methods.

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:

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 has no types: every column is a string unless you set inferSchema or cast. Always set header when the first row has names, and set delimiter when it is not a comma.
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.

The reader assumed a comma delimiter, so the whole line became one column.
Provide 'delimiter' = ';' and 'header' = 'true' in FORMAT_OPTIONS.
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').
Because this is a recurring feed, use COPY INTO so each file loads once.

COPY INTO prod.bronze.partner_sales
FROM (
  SELECT order_id::BIGINT, customer, amount::DECIMAL(12,2),
         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. The source is a message bus or a landing folder processed by Auto Loader in continuous mode.
Monthly ledger: batch ingestion on a schedule, because latency is not critical and a once-a-month job on jobs compute is the cheapest.

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. Malformed rows should not stop the whole load. Options include mode = 'PERMISSIVE' with a corrupt record column, or Auto Loader's rescued data column (lesson 4).
Make it observable. The COPY INTO result returns the number of rows inserted and skipped, which you can check in the job output or store for monitoring.
Make it safe to rerun. Prefer tracked, idempotent methods so a retry never creates duplicates.

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 the READ FILES privilege on the external location and wants the simplest, safe solution.

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:

CREATE TABLE IF NOT EXISTS prod.bronze.baggage;

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

Idempotency makes retries safe, mergeSchema lets new fields appear as 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

Idempotent: running it again gives the same result without duplicates.
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.