← Data Ingestion and Loading

SQL ingestion: read_files, streaming tables, COPY INTO

Everything in lessons 2 and 3 works from SQL too — and for most teams, SQL is where ingestion should live, because more people can maintain it.

read_files()

A table-valued function that reads files from cloud storage (read_files, 2026-07-10):

read_files(path [, option_key => option_value ] [...])

Options use named-parameter syntax with =>. This is why you write format => rather than cloudFiles.format — dots would need backticks.

-- Auto-detect format and schema
SELECT * FROM read_files('abfss://container@storageAccount.dfs.core.windows.net/base/path');

-- Explicit format and schema
SELECT * FROM read_files(
    's3://bucket/path',
    format => 'csv',
    schema => 'id int, ts timestamp, event string');

-- Schema hints
SELECT * FROM read_files(
    's3://bucket/path',
    format => 'json',
    schemaHints => 'id int');

-- Time-bounded
SELECT * FROM read_files(
    'gs://my-bucket/avroData',
    modifiedAfter => date_sub(current_date(), 1),
    modifiedBefore => current_date());

Paths support abfss:// (Azure), s3:// (AWS), gs:// (GCP), /Volumes/..., and globs.

The STREAM keyword flips the semantics

This is the single most important idea in the lesson.

-- BATCH: reads everything, every time
CREATE TABLE my_avro_data
  AS SELECT *, _metadata.file_path
  FROM read_files('gs://my-bucket/avroData');

-- INCREMENTAL: this is Auto Loader
CREATE OR REFRESH STREAMING TABLE avro_data
  AS SELECT * FROM STREAM read_files(
    'gs://my-bucket/avroData',
    includeExistingFiles => false);

Same function. Add STREAM, and you get incremental Auto Loader ingestion instead of a full re-read.

Note _metadata.file_path in the batch example — a hidden column giving you file provenance, useful for debugging and lineage.

CREATE STREAMING TABLE

The full grammar (CREATE STREAMING TABLE, 2026-07-22) is large; here are the parts that earn their keep.

CREATE OR REFRESH STREAMING TABLE sales
SCHEDULE EVERY 1 hour
AS SELECT product, price FROM STREAM raw_data;

Expectations — data quality in the DDL

CREATE OR REFRESH STREAMING TABLE clean_orders (
  CONSTRAINT valid_amount EXPECT (amount > 0) ON VIOLATION DROP ROW,
  CONSTRAINT has_customer EXPECT (customer_id IS NOT NULL) ON VIOLATION FAIL UPDATE
)
AS SELECT * FROM STREAM read_files('/Volumes/cat/sch/vol/orders', format => 'json');

Three behaviors:

Expectations are part of streaming table DDL, not a Python-only feature — a common misconception.

Scheduling

SCHEDULE EVERY 1 HOUR
SCHEDULE CRON '0 0 * * *' AT TIME ZONE 'UTC'
TRIGGER ON UPDATE [ AT MOST EVERY <interval> ]

Clustering and constraints

CLUSTER BY (per E0 — prefer it over PARTITIONED BY), NOT NULL, GENERATED ALWAYS AS, identity columns, DEFAULT, COMMENT, MASK, and WITH ROW FILTER are all available in the table spec.

Standalone vs in-pipeline

"A standalone streaming table is a table registered to Unity Catalog with extra support for streaming or incremental data processing, defined outside of a Lakeflow pipeline."

So streaming tables work in plain Databricks SQL — you don't need a pipeline.

COPY INTO

COPY INTO target_table [ BY POSITION | ( col_name [, ...] ) ]
FROM { source_clause | ( SELECT expression_list FROM source_clause ) }
FILEFORMAT = data_source
[ VALIDATE [ ALL | num_rows ROWS ] ]
[ FILES = ( file_name [, ...] ) | PATTERN = glob_pattern ]
[ FORMAT_OPTIONS ( { data_source_reader_option = value } [, ...] ) ]
[ COPY_OPTIONS ( { copy_option = value } [, ...] ) ]

CREATE TABLE IF NOT EXISTS <catalog>.<schema>.booking_updates;

COPY INTO <catalog>.<schema>.booking_updates
FROM '/Volumes/<catalog>/<schema>/<volume>/booking_updates'
FILEFORMAT = JSON
FORMAT_OPTIONS ('mergeSchema' = 'true', 'multiLine' = 'true')
COPY_OPTIONS ('mergeSchema' = 'true');

FILES caps at 1,000 files; PATTERN takes a glob; use one or the other.

Idempotency

"Loads data from a file location into a Delta table. This is a retryable and idempotent operation."

"Files in the source location that have already been loaded are skipped. This is true even if the files have been modified since they were loaded."

That bolded clause is exam bait. A file that's already been loaded stays skipped even after it changes. If you need a reload, COPY_OPTIONS ('force' = 'true').

VALIDATE [ ALL | n ROWS ] previews without loading — Auto Loader has no equivalent.

⚠️ The recommendation that changed

Older material teaches a file-count threshold: COPY INTO for thousands of files, Auto Loader for millions. That guidance is no longer what the docs say.

"For a more scalable and robust file ingestion experience, Databricks recommends that SQL users use streaming tables." — COPY INTO (2026-06-11)

"SQL examples for incremental ingestion from cloud object storage use CREATE STREAMING TABLE syntax. It offers SQL users a scalable and robust ingestion experience, therefore it's the recommended alternative to COPY INTO." — Ingest data (2026-07-10)

Honest caveat: I could not find the "thousands vs millions" threshold on any current page I checked. It may still exist somewhere, or it may have been removed. Either way, don't cite it as current guidance.

Exam strategy: a question written from older material expects the file-count answer; one written from 2026 docs expects streaming tables. If a question offers CREATE STREAMING TABLE as an option for scalable SQL ingestion, that's the current answer.

Where state lives — the real distinction

This is why COPY INTO skips already-loaded files even after modification: the record of what was loaded lives with the target table, keyed by file identity, not content.

Check yourself

1. What does STREAM change in FROM STREAM read_files(...)?
2. COPY INTO loaded orders.json yesterday. The file changed. What happens today, and how do you force it?
3. Where does each of Auto Loader and COPY INTO track processed files?
4. Which ON VIOLATION keeps bad rows but records them?
5. Current recommendation for scalable SQL ingestion?
6. Why format => and not cloudFiles.format?

Teaching this section

• Demo the STREAM keyword by toggling it on one query and showing the row counts diverge on a second run. That single edit is the whole lesson.
• Flag the changed recommendation explicitly. Anyone who studied from 2023–24 material has the file-count rule memorized, and they'll argue. Show them the quote.
• Expectations are a good place to pause — most people don't know data quality is expressible in DDL. It reframes streaming tables as more than syntax sugar.
• Timing: 30 min presented, 15 for the STREAM demo.