Azure Databricks: load your first CSV from a volume with read_files and COPY INTO

#The goal

Someone has given you a CSV file and wants it in a table you can query. You'll put it in a volume, look at it before loading and load it in a way that's safe to run twice.

If catalogs and volumes are new to you, read Unity Catalog basics first.

#Step 1: check that you have a place to work

Run this in a SQL editor or notebook:

SELECT current_user(), current_catalog(), current_schema();
SHOW CATALOGS;

Create a schema and a volume. You need USE CATALOG and CREATE SCHEMA on the catalog, or ask an admin to run it for you:

CREATE SCHEMA IF NOT EXISTS main.ops_demo;
CREATE VOLUME IF NOT EXISTS main.ops_demo.landing;

Replace main with your own catalog name if it's different.

#Step 2: upload the file

There are three simple ways:

In the UI: open Catalog, find the volume, then choose Upload to this volume.

With the Databricks CLI:

databricks fs cp ./orders.csv dbfs:/Volumes/main/ops_demo/landing/orders/orders.csv
databricks fs ls dbfs:/Volumes/main/ops_demo/landing/orders/

From a notebook:

display(dbutils.fs.ls("/Volumes/main/ops_demo/landing/orders/"))

Keep each table's files in its own folder. It makes reloading and troubleshooting easier later. Setup help for the CLI is in the CLI cheat sheet.

#Step 3: look at the data first

read_files reads a file as a table without loading it:

SELECT *
FROM read_files(
  '/Volumes/main/ops_demo/landing/orders/',
  format => 'csv',
  header => true
)
LIMIT 20;

Check these points before you go on:

• Do the columns line up, or has the delimiter split them wrongly?
• Are the dates and numbers in the types you expected?
• Is there a _rescued_data column with values? That holds data that didn't fit the columns.

SELECT count(*) AS total_rows, count(_rescued_data) AS rows_with_problems
FROM read_files('/Volumes/main/ops_demo/landing/orders/', format => 'csv', header => true);

Use options for awkward files:

SELECT * FROM read_files(
  '/Volumes/main/ops_demo/landing/orders/',
  format => 'csv',
  header => true,
  sep => ';',
  dateFormat => 'dd/MM/yyyy'
) LIMIT 20;

#Step 4: create the table and load it

Choose your column types, rather than leaving it to inference, for anything that'll be used for real:

CREATE TABLE IF NOT EXISTS main.ops_demo.orders (
  order_id    BIGINT,
  customer    STRING,
  amount      DECIMAL(10,2),
  order_date  DATE,
  source_file STRING
);

Load it with COPY INTO:

COPY INTO main.ops_demo.orders
FROM (
  SELECT
    CAST(order_id AS BIGINT)      AS order_id,
    customer,
    CAST(amount AS DECIMAL(10,2)) AS amount,
    CAST(order_date AS DATE)      AS order_date,
    _metadata.file_path           AS source_file
  FROM '/Volumes/main/ops_demo/landing/orders/'
)
FILEFORMAT = CSV
FORMAT_OPTIONS ('header' = 'true');

The output shows how many rows were inserted. Run the same command again: the count will be 0.

That's the key benefit. COPY INTO remembers which files it has already loaded, so a rerun doesn't create duplicates, and new files in the folder are picked up automatically.

SELECT count(*), min(order_date), max(order_date) FROM main.ops_demo.orders;
SELECT source_file, count(*) FROM main.ops_demo.orders GROUP BY 1;

Avoid CREATE TABLE ... AS SELECT for files that keep arriving: it's a single load, and doing it again replaces or duplicates data.

Warning

If a file with the same name is changed and uploaded again, COPY INTO may skip it as already loaded. Give each new file a unique name, such as orders_2026-11-06.csv. COPY_OPTIONS ('force' = 'true') reloads everything, and that creates duplicates.

#Which method to use

#Common first errors

Grant access for a teammate:

GRANT USE CATALOG ON CATALOG main TO `data-readers`;
GRANT USE SCHEMA ON SCHEMA main.ops_demo TO `data-readers`;
GRANT SELECT ON TABLE main.ops_demo.orders TO `data-readers`;

See the grants cheat sheet for more.

#Clean up

DROP TABLE main.ops_demo.orders;
DROP VOLUME main.ops_demo.landing;

Caution

Dropping a managed volume deletes its files. For a managed table, the data goes too, though it can be recovered for a limited time with UNDROP. Check that you have the right name, especially outside a demo.

#Prevent it next time

• Store the source file name in the table (_metadata.file_path), so you can trace bad rows.
• Schedule the load as a job, rather than running it by hand.
• Check _rescued_data on each load, and alert if it isn't empty.
• Read more about volumes.