What you will be able to do
- Create a managed Delta table from local files with the file upload page, within its limits
- Load files incrementally and idempotently from cloud storage or volumes with COPY INTO, with or without a predefined schema
- Explain how Auto Loader's cloudFiles source finds new files and guarantees exactly-once processing
- Choose an Auto Loader schema evolution mode for a given tolerance for schema drift
1.Small local files: the Create or modify a table page
The simplest way to ingest data is from your own machine. The Create or modify a table using file upload page creates a managed Delta table, or overwrites one, in Unity Catalog or the legacy Hive metastore from small CSV, TSV, JSON, Avro, Parquet or text files. You can upload up to 10 files at once, and their combined size must be under 2 GB. Uploads are staged in a secure internal location that is cleaned up daily. Staging doesn't need compute, but previewing and configuring the table does: a SQL warehouse, serverless or dedicated compute works, and group clusters don't. You also need permission to create tables in the target schema, and workspace admins can turn the page off.
When you upload several files, their rows are appended into one table. Records aren't joined or merged, and the header setting applies to every file. JSON support covers nested data: multi-line rows are enabled by default, STRUCT and ARRAY are supported types, and nested types can't be edited in the preview.
Checkpoint 1 of 6· Put it in order
Put the file-upload steps in order.
- 1.Click New > Add or upload data
- 2.Click Create or modify a table
- 3.Click Create at the bottom of the page
- 4.Select the catalog and schema, and optionally edit the table name
- 5.Browse for or drag and drop the files onto the drop zone
You open the upload page from New, stage the files, choose where the table goes, and then create it.
“Click New > Add or upload data.”Source: docs.databricks.com
Sources1
2.COPY INTO: idempotent SQL loads from storage
Files that already sit in S3, ADLS, GCS or a Unity Catalog volume can be loaded with the COPY INTO SQL command. It is retriable and idempotent: files that were loaded before are skipped. That makes a scheduled COPY INTO an incremental loader. It reads CSV, JSON, XML, Avro, ORC, Parquet, text and binary files, and it can infer, merge and evolve the target table's schema. An account administrator has to configure access to the cloud storage first. The typical pattern creates a typed table and then copies JSON into it.
CREATE TABLE <catalog>.<schema>.booking_updates_upload (
booking_id BIGINT,
user_id BIGINT,
status STRING,
total_amount DOUBLE
);
COPY INTO <catalog>.<schema>.booking_updates_upload
FROM '/Volumes/<catalog>/<schema>/<volume>/wanderbricks/booking_updates'
FILEFORMAT = JSON
FORMAT_OPTIONS ('multiLine' = 'true');You can also skip defining columns. On Databricks Runtime 11.3 LTS and above, you can create an empty placeholder table and let COPY INTO infer the schema by setting mergeSchema to true. Until COPY INTO writes to that table, nothing else can use it: INSERT INTO and MERGE INTO aren't supported against it, and it can only be queried after the first load.
Checkpoint 2 of 6· Fill the gap
Which clause lets COPY INTO infer and set the schema of an empty placeholder table?
CREATE TABLE IF NOT EXISTS <catalog>.<schema>.booking_updates_schemaless;
COPY INTO <catalog>.<schema>.booking_updates_schemaless
FROM '/Volumes/<catalog>/<schema>/<volume>/wanderbricks/booking_updates'
FILEFORMAT = JSON
FORMAT_OPTIONS ('mergeSchema' = 'true', 'multiLine' = 'true')
? ('mergeSchema' = 'true');Setting mergeSchema in COPY_OPTIONS lets COPY INTO evolve the target table's schema. In FORMAT_OPTIONS, mergeSchema controls how the source files are read.
Source: docs.databricks.comCheckpoint 3 of 6· Check yourself
An engineer runs CREATE TABLE IF NOT EXISTS for a schemaless table and then tries INSERT INTO before any COPY INTO. What happens?
A schemaless placeholder table can only be written by COPY INTO. It can be queried only after COPY INTO has inserted data.
“INSERT INTO and MERGE INTO are not supported to write data into schemaless Delta tables.”Source: docs.databricks.com
Sources2
3.Auto Loader: the cloudFiles streaming source
Auto Loader is the standard connector for cloud object storage. It provides a Structured Streaming source called cloudFiles that processes new files in S3, ADLS, GCS or /Volumes/ paths as they arrive, and can optionally process the files already there. It reads JSON, CSV, XML, Parquet, Avro, ORC, text and binary files, and it scales to billions of files for backfills. Metadata for every discovered file is stored in RocksDB inside the checkpoint location. That gives exactly-once processing and lets a failed stream resume where it stopped, without you managing any state. Databricks recommends Auto Loader in Lakeflow pipelines, which handle the schema and checkpoint locations for you.
| Source | More customization | Some customization | More automation |
|---|---|---|---|
| Cloud object storage | Auto Loader with Structured Streaming | Auto Loader with Lakeflow pipelines | Auto Loader with Databricks SQL |
| Apache Kafka | Structured Streaming with Kafka source | Lakeflow pipelines with Kafka source | Databricks SQL with Kafka source |
| SFTP servers | Ingest files from SFTP servers | N/A | N/A |
The same ingestion pipeline can run as batch or streaming. A triggered pipeline processes new data on a schedule or when manually triggered (batch ingestion), while a continuous pipeline processes new data as it arrives (streaming ingestion). In both cases only new files are processed, which is what makes the load incremental. For SQL users, the Auto Loader path is CREATE STREAMING TABLE, which Databricks recommends over COPY INTO. In Structured Streaming, the same source is written in code, as below: the schema location tracks the schema over time and the checkpoint location tracks progress.
spark.readStream.format("cloudFiles")
.option("cloudFiles.format", "parquet")
// The schema location directory keeps track of your data schema over time
.option("cloudFiles.schemaLocation", "<path-to-schema>")
.load("<path-to-source-data>")
.writeStream
.option("checkpointLocation", "<path-to-checkpoint>")
.start("<path-to-target>")Directory listing mode finds new files by listing the input directory, so a stream starts quickly with only data access. File notification mode instead uses notification and queue services in your cloud account, which Auto Loader can set up for you. File notification mode with file events is more performant and scalable than directory listing, and Databricks recommends it instead of directory listing for most workloads. You turn it on by enabling file events on the external location and setting cloudFiles.useManagedFileEvents to true. You can switch modes when restarting a stream without losing exactly-once guarantees, but in either mode Auto Loader doesn't guarantee the order in which files are processed.
Checkpoint 4 of 6· Check yourself
Which statement about Auto Loader file detection is correct?
File notification subscribes to file events through cloud queues. Modes can be switched across restarts with exactly-once intact, neither mode guarantees ordering, and directory listing is the default.
“File notification mode leverages file notification and queue services in your cloud infrastructure account.”Source: docs.databricks.com
4.Auto Loader schema inference and evolution
Semi-structured files such as JSON change shape over time, and Auto Loader can detect this schema drift. When a column has different types across Parquet files, it picks the widest type. When it finds a new column, it infers the schema from the latest micro-batch, writes the merged schema to the schema location with the new columns at the end, and then stops the stream with an UnknownFieldException. The types of existing columns stay the same. Databricks recommends running these streams under Lakeflow Jobs so they restart automatically with the updated schema. The cloudFiles.schemaEvolutionMode option controls this behaviour, and the table below comes from the Auto Loader schema documentation.
| Mode | Behavior on reading new column |
|---|---|
| addNewColumns (default) | Stream fails with an UnknownFieldException after Auto Loader adds the new columns to the schema. Restarting the stream resumes processing with the updated schema. Existing columns do not evolve data types. |
| addNewColumnsWithTypeWidening | Same behavior as addNewColumns, but Auto Loader also widens supported data types (such as int to long). Unsupported type changes (for example, int to string) are added to the rescued data column. |
| rescue | Auto Loader never evolves the schema and the stream does not fail due to schema changes. Auto Loader records all new columns in the rescued data column. |
| failOnNewColumns | Stream fails and does not restart unless you update the provided schema or remove the offending data file. The schema is not updated automatically. |
| none | Does not evolve the schema, new columns are ignored, and data is not rescued unless the rescuedDataColumn option is set. Stream does not fail due to schema changes. |
The default depends on whether you provide a schema. addNewColumns is the default when a schema is not provided, but none is the default when you provide a schema. addNewColumns is not allowed when the schema of the stream is provided, but does work if you provide your schema as a schema hint. Note that none only rescues data if the rescuedDataColumn option is set. addNewColumnsWithTypeWidening needs Databricks Runtime 16.4 or above, and a Delta target needs type widening enabled.
df = (spark.readStream
.format("cloudFiles")
.option("cloudFiles.format", "parquet")
.option("cloudFiles.schemaEvolutionMode", "addNewColumnsWithTypeWidening")
.load("/path/to/data/dir"))Checkpoint 5 of 6· Match them up
Match each schemaEvolutionMode to its behaviour.
Tap a term, then the definition that fits it.
Only addNewColumns changes the schema. rescue keeps the data without failing, failOnNewColumns stops until someone intervenes, and none drops the new columns.
“Auto Loader never evolves the schema and the stream does not fail due to schema changes.”Source: docs.databricks.com
Checkpoint 6 of 6· Exam question
A team ingesting semi-structured JSON logs with Auto Loader needs the stream to keep running without interruption even when the source occasionally introduces unexpected new fields, while still preserving those unexpected fields for later inspection rather than dropping them. Which `cloudFiles.schemaEvolutionMode` setting satisfies this requirement?
Correct answer: A — `rescue`, which never updates the table schema and instead redirects any new or mismatched columns into the `_rescued_data` column so the stream keeps running.
- A. This setting never modifies the table schema and instead captures any unexpected or mismatched columns into the rescued data column, letting the stream continue uninterrupted while still preserving the unexpected values for later review.
- B. The default evolution mode halts the stream with an exception when it encounters a new field, which is the opposite of the uninterrupted operation this scenario requires.
- C. This setting is meant for streams with an explicitly defined schema and simply ignores any columns outside that schema, discarding rather than preserving the unexpected fields.
- D. This setting stops the stream permanently and blocks it from resuming until the offending files are removed, which also fails the requirement that the stream keep running.
Exam traps
Each one states something that sounds right. Open it to see what is actually true.
1.COPY INTO is Databricks' recommended way for SQL users to ingest files incrementally.Why is that wrong?
COPY INTO is idempotent and supported, but for a more scalable and robust experience Databricks recommends streaming tables for SQL users.
Covered in COPY INTO: idempotent SQL loads from storage
2.With the default addNewColumns mode, Auto Loader adds new columns and keeps the stream running.Why is that wrong?
The stream stops with an UnknownFieldException after the schema is updated. It continues only after a restart, which is why Databricks recommends Lakeflow Jobs with automatic restart.
Covered in Auto Loader schema inference and evolution
Practise it for real
Load JSON files from a Unity Catalog volume into a Delta table with COPY INTO and check that rerunning it is idempotent.
1.Run CREATE TABLE for <catalog>.<schema>.booking_updates_upload with the booking_id, user_id, status and total_amount columns, replacing the placeholders with a catalog and schema where you have CREATE TABLE permission.
Why: A typed target table lets COPY INTO load into known columns.
You should see: The table exists and is empty.
2.Run the COPY INTO statement from the volume path with FILEFORMAT = JSON and FORMAT_OPTIONS ('multiLine' = 'true').
Why: multiLine is needed for JSON records that span several lines.
You should see: The rows from the booking_updates files are loaded.
3.Run the same COPY INTO statement again, then SELECT * FROM the table.
Why: COPY INTO skips files it has already loaded, so it is safe to schedule.
You should see: The second run loads no new rows, and the row count matches the first run.
4.Run DROP TABLE <catalog>.<schema>.booking_updates_upload.
Why: Removes the example table.
You should see: The table no longer exists.
Stuck? Get a nudge
If the second run added rows, check whether the source files changed between runs.
Sources
Every claim above is drawn from one of these pages, quoted as it was written on the date shown.
- 1.
“The total size of uploaded files must be under 2 gigabytes.”
↩︎ Small local files: the Create or modify a table page“Uploaded files combine by appending all data as rows in the target table.”
↩︎ Small local files: the Create or modify a table page“Compressed files such as zip and tar files are not supported.”
↩︎ Prediction“Click New > Add or upload data.”
↩︎ Checkpoint - 2.
“files in the source location that have already been loaded are skipped on subsequent runs.”
↩︎ COPY INTO: idempotent SQL loads from storage“Exactly-once (idempotent) file processing by default.”
↩︎ COPY INTO: idempotent SQL loads from storage“For a more scalable and robust file ingestion experience, Databricks recommends that SQL users use streaming tables.”
↩︎ Exam trap 1“INSERT INTO and MERGE INTO are not supported to write data into schemaless Delta tables.”
↩︎ Checkpoint - 3.
“It provides a Structured Streaming source called cloudFiles.”
↩︎ Auto Loader: the cloudFiles streaming source“You do not need to provide a schema or checkpoint location because Lakeflow pipelines automatically manage these settings for your pipelines.”
↩︎ Auto Loader: the cloudFiles streaming source - 4.https://docs.databricks.com/aws/en/ingestion/cloud-object-storage/auto-loader/file-detection-modesOfficial docs
“Databricks recommends file notification mode using file events instead of directory listing mode for most workloads.”
↩︎ Auto Loader: the cloudFiles streaming source“File notification mode with file events is more performant and scalable than directory listing.”
↩︎ Auto Loader: the cloudFiles streaming source“Auto Loader uses directory listing mode by default.”
↩︎ Prediction“File notification mode leverages file notification and queue services in your cloud infrastructure account.”
↩︎ Checkpoint - 5.https://docs.databricks.com/aws/en/ingestionOfficial docs
“Streaming ingestion | Continuous: Processes new data as it arrives in the source.”
↩︎ Auto Loader: the cloudFiles streaming source“it's the recommended alternative to COPY INTO.”
↩︎ Auto Loader: the cloudFiles streaming source - 6.
“Auto Loader supports the following modes for schema evolution, which you set in the cloudFiles.schemaEvolutionMode option:”
↩︎ Auto Loader schema inference and evolution“addNewColumns mode is the default when a schema is not provided, but none is the default when you provide a schema.”
↩︎ Auto Loader schema inference and evolution“When Auto Loader detects a new column, the stream stops with an UnknownFieldException.”
↩︎ Exam trap 2“Auto Loader never evolves the schema and the stream does not fail due to schema changes.”
↩︎ Checkpoint - 7.https://docs.databricks.com/aws/en/ingestion/cloud-object-storage/auto-loader/best-practicesOfficial docs
“The addNewColumnsWithTypeWidening schema evolution mode automatically widens compatible types”
↩︎ Auto Loader schema inference and evolution