What you will be able to do
- Choose between Auto Loader and COPY INTO based on file volume, schema drift and reprocessing needs
- Pick an ingestion path from the data types and the ingestion frequency (triggered or continuous) a scenario requires
- Explain what Unity Catalog requires when Auto Loader reads from cloud storage, and where checkpoints must live
1.File volume and schema drift: Auto Loader or COPY INTO
Cloud object storage (S3, ADLS, GCS and Unity Catalog volumes) is the most common ingestion source. It has two incremental options. Auto Loader is a Structured Streaming source called cloudFiles that processes new files as they arrive. COPY INTO is a SQL command that is "retriable and idempotent": files it has already loaded are skipped on later runs. Both are designed to avoid loading the same file twice, so the choice rests on other requirements.
In Lakeflow Connect terms, both of these are standard file connectors. The overview lists cloud object storage under standard connectors, ingested "using Auto Loader or COPY INTO", while the managed file connectors cover enterprise file services such as Google Drive and SharePoint. So Auto Loader in a custom Structured Streaming job is a standard, highly customizable choice, not a managed connector.
Volume is the main deciding factor. If you expect files in the order of thousands over time, COPY INTO is fine. If you expect millions or more, use Auto Loader. Auto Loader needs fewer total operations to discover files and can split processing into multiple batches, so it "is less expensive and more efficient at scale." For scale, Auto Loader can process billions of files to migrate or backfill a table, and it "scales to support near real-time ingestion of millions of files per hour."
| Requirement | Auto Loader | COPY INTO |
|---|---|---|
| Files over time | Millions or more | Thousands |
| Frequently changing schema | Better schema inference and evolution | Can evolve the schema with mergeSchema, but Auto Loader has better primitive types for inference and evolution |
| Reprocessing a chosen subset of re-uploaded files | Harder | Easier |
| Recommended SQL path | Streaming tables use Auto Loader | Still supported, but streaming tables are recommended instead |
Schema drift also favors Auto Loader. It "can detect schema drifts, notify you when schema changes happen, and rescue data that would have been otherwise ignored or lost." COPY INTO can also evolve a table schema when you set mergeSchema to true in COPY_OPTIONS, so evolution alone doesn't rule it out. Auto Loader can also cost less: its file notification mode can avoid directory listing altogether. COPY INTO is better in one case, which is reloading a selected subset of re-uploaded files. You can even run COPY INTO to reload that subset while an Auto Loader stream keeps running on the same data.
Checkpoint 1 of 5· Check yourself
A landing bucket receives sensor files continuously, and you expect several million files a month. The JSON schema gains new fields every few weeks. Which method fits best?
Millions of files and a frequently changing schema both point to Auto Loader, which discovers files more cheaply at scale and handles schema inference and evolution better. COPY INTO with mergeSchema can evolve a schema, but it is the better fit for thousands of files, not millions.
“If you are expecting files in the order of millions or more over time, use Auto Loader.”Source: docs.databricks.com
Checkpoint 2 of 5· Exam question
An engineering team ingests semi-structured JSON files that land continuously in a cloud storage bucket. They need full control over custom parsing and transformation logic written directly in their own Structured Streaming code, beyond what any packaged connector exposes. Which ingestion method should they use?
Correct answer: A — Read the files directly with Auto Loader's `cloudFiles` source inside a custom Structured Streaming job, giving full control over transformation logic.
- A. The `cloudFiles` source gives direct, code-level access to incremental file ingestion inside a custom Structured Streaming job, which is exactly the level of control this scenario requires.
- B. A standard connector packages ingestion inside a declarative Lakeflow pipeline template, which reduces the amount of raw, low-level custom code control the team is asking for.
- C. Managed connectors for enterprise file sources such as SharePoint or Google Drive target a different category of source than a generic cloud object storage bucket receiving JSON files.
- D. A scheduled COPY INTO command reloads the full bucket contents on each run rather than incrementally tracking and processing only new files, which does not match the continuous ingestion need.
2.Data types and ingestion frequency
Both file methods support a wide range of formats. Auto Loader "can ingest JSON, CSV, XML, PARQUET, AVRO, ORC, TEXT, and BINARYFILE file formats," including pre-compressed files. COPY INTO supports CSV, JSON, XML, Avro, ORC, Parquet, text and binary files. If the files are in Google Drive or SharePoint rather than object storage, look for a Lakeflow Connect file connector instead. Databricks recommends Auto Loader in Lakeflow pipelines for incremental ingestion, where the schema and checkpoint location are managed for you.
Event data that arrives on a message bus isn't a file problem. Kafka, Kinesis, Pub/Sub and Pulsar each have standard connectors at all three levels of customization, the same as cloud object storage does. As with the connector layers, you choose how much of the pipeline you build yourself.
| 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 |
| Amazon Kinesis | Structured Streaming with Kinesis source | Lakeflow pipelines with Kinesis source | Databricks SQL with Kinesis source |
| SFTP servers | Ingest files from SFTP servers | N/A | N/A |
Frequency decides the pipeline mode. Ingestion pipelines run either on a recurring schedule or continuously. Batch ingestion uses triggered mode, which processes new data on a schedule or when triggered manually. Streaming ingestion uses continuous mode, which processes new data as it arrives. COPY INTO is a batch command that you can run in Databricks SQL, notebooks and Lakeflow Jobs.
Other ingestion methods fill the gaps where no managed connector exists. Community connectors are open source, built and maintained by the community, and "not backed by Databricks SLAs". Custom connectors let you build your own connector for a source that has no managed connector, and you run it in your own workspace. Query-based connectors ingest from a database by querying the source directly on a schedule, without change data capture, a gateway or staging storage. When a foreign catalog is used for ingestion, the connection uses Lakehouse Federation. Validated technology partner integrations, some of them in Partner Connect, give low-code ingestion from many sources. Hand-written code, such as a notebook that pulls from a source, can run as a notebook task in a Lakeflow Job on a schedule or trigger. When one of these options is the right fit, remember that you take on more of the build and maintenance work than with a managed connector.
Checkpoint 3 of 5· Check yourself
A fraud team needs new Kafka events to land in a streaming table as soon as they arrive, not at the next scheduled run. Which pipeline mode fits?
Streaming ingestion uses continuous mode, which processes data as it arrives. Triggered mode runs on a schedule or when someone starts it manually.
“Continuous: Processes new data as it arrives in the source.”Source: docs.databricks.com
3.Unity Catalog governance for file ingestion
When governance is a requirement, check that the method keeps every path under Unity Catalog. Auto Loader "can securely ingest data from external locations configured with Unity Catalog." The Unity Catalog security model assumes that Unity Catalog manages every storage location a workload touches. This includes the checkpoint and schema files, not only the source data. The documented example uses these grants:
| Storage location | Grant | Used for |
|---|---|---|
| s3://autoloader-source/json-data | READ FILES | Reading the source files |
| s3://dev-bucket | READ FILES, WRITE FILES, CREATE TABLE | Checkpoint and schema location |
Two rules follow. Store checkpoint and schema evolution information in locations that Unity Catalog manages. And don't nest those files under the table directory, because Unity Catalog doesn't allow it. To keep table data at a specific path, for example to share it with non-Databricks clients, use a Unity Catalog external table instead of a managed table. The simplest governed option is the SQL form inside Lakeflow pipelines, where checkpoint and schema locations are managed for you:
CREATE OR REFRESH STREAMING TABLE dev_catalog.dev_database.dev_table
AS SELECT * FROM STREAM read_files(
's3://autoloader-source/json-data',
format => 'json'
);Managed and standard connectors differ in how much you build. Managed connectors are fully managed and minimize long-term maintenance. Standard connectors "trade some automation for broader source support and customization." Databricks recommends starting with the most managed layer that supports your source, and dropping down a layer only when it doesn't meet your requirements. The same logic applies to message buses: Kafka, Kinesis, Pub/Sub and Pulsar are ingested through the standard connectors in the table above, where you pick the layer. Managed streaming connectors also exist, and they use a Unity Catalog connection so that credentials stay out of your pipeline configuration. The sources don't list which message buses have a managed connector, so check the Lakeflow Connect connector list for your source before choosing.
Checkpoint 4 of 5· Fill the gap
In the Python version you manage the checkpoint yourself. Which value selects the Auto Loader source?
checkpoint_path = "s3://dev-bucket/_checkpoint/dev_table"
(spark.readStream
.format(" ? ")
.option("cloudFiles.format", "json")
.option("cloudFiles.schemaLocation", checkpoint_path)
.load("s3://autoloader-source/json-data")
.writeStream
.option("checkpointLocation", checkpoint_path)
.trigger(availableNow=True)
.toTable("dev_catalog.dev_database.dev_table"))Auto Loader is the Structured Streaming source named cloudFiles. The file format goes in the separate cloudFiles.format option, and the checkpoint path is on s3://dev-bucket, a Unity Catalog-governed location.
Source: docs.databricks.comCheckpoint 5 of 5· Exam question
A team streams events from an existing Apache Kafka cluster and needs custom filtering and enrichment logic applied while the ingestion remains part of an orchestrated, governed Lakeflow pipeline. Which option is the best fit?
Correct answer: A — Configure a Lakeflow Connect standard connector for the Kafka source, which supports streaming ingestion with custom filtering logic inside a governed Lakeflow pipeline.
- A. A standard connector is designed for sources like message buses where teams need customization, letting the team add filtering and enrichment logic while still running inside an orchestrated Lakeflow pipeline.
- B. A fully managed connector automates ingestion end to end but is built for minimal customization, which conflicts with the requirement for custom filtering and enrichment logic.
- C. A one-time COPY INTO load only captures a static snapshot of exported files and does not provide the continuous, governed streaming pipeline the scenario calls for.
- D. Treating manual Kafka exports as files for Auto Loader abandons the live streaming connection to the cluster and turns a continuous event source into an ad hoc batch process.
Unity Catalog does not allow checkpoint or schema inference and evolution files to be nested under the table directory. Put the checkpoint in a separate location that Unity Catalog manages, such as s3://dev-bucket/_checkpoint/dev_table in the example.
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 still works, but Databricks recommends streaming tables (CREATE STREAMING TABLE) as the scalable and robust alternative.
Covered in File volume and schema drift: Auto Loader or COPY INTO
2.Auto Loader always beats COPY INTO, including when you need to reload a few re-uploaded files.Why is that wrong?
Reprocessing a chosen subset of files is easier with COPY INTO, and you can run it while an Auto Loader stream keeps running.
Covered in File volume and schema drift: Auto Loader or COPY INTO
Sources
Every claim above is drawn from one of these pages, quoted as it was written on the date shown.
- 1.
“COPY INTO is retriable and idempotent — files in the source location that have already been loaded are skipped on subsequent runs.”
↩︎ File volume and schema drift: Auto Loader or COPY INTO“Support for multiple source file formats: CSV, JSON, XML, Avro, ORC, Parquet, text, and binary files.”
↩︎ Data types and ingestion frequency“For a more scalable and robust file ingestion experience, Databricks recommends that SQL users use streaming tables.”
↩︎ Prediction - 2.
“If you're going to ingest files in the order of thousands over time, you can use COPY INTO.”
↩︎ File volume and schema drift: Auto Loader or COPY INTO“If your data schema is going to evolve frequently, Auto Loader provides better primitive data types around schema inference and evolution.”
↩︎ File volume and schema drift: Auto Loader or COPY INTO“You can use COPY INTO in Databricks SQL, notebooks, and Lakeflow Jobs.”
↩︎ Data types and ingestion frequency“Databricks validates technology partner integrations that enable you to ingest from various sources, including cloud object storage.”
↩︎ Data types and ingestion frequency“Loading a subset of re-uploaded files can be a bit easier to manage with COPY INTO.”
↩︎ Exam trap 2“If you are expecting files in the order of millions or more over time, use Auto Loader.”
↩︎ Checkpoint - 3.
“Auto Loader scales to support near real-time ingestion of millions of files per hour.”
↩︎ File volume and schema drift: Auto Loader or COPY INTO“Auto Loader's file notification mode can help reduce your cloud costs further by avoiding directory listing altogether.”
↩︎ File volume and schema drift: Auto Loader or COPY INTO“Auto Loader can ingest JSON, CSV, XML, PARQUET, AVRO, ORC, TEXT, and BINARYFILE file formats.”
↩︎ Data types and ingestion frequency“Databricks recommends Auto Loader in Lakeflow pipelines for incremental data ingestion.”
↩︎ Data types and ingestion frequency - 4.
“Ingest files from S3, ADLS, or GCS using Auto Loader or COPY INTO.”
↩︎ File volume and schema drift: Auto Loader or COPY INTO - 5.
“mergeSchema: boolean, default false. If set to true, the schema can be evolved according to the incoming data.”
↩︎ File volume and schema drift: Auto Loader or COPY INTO - 6.https://docs.databricks.com/aws/en/ingestionOfficial docs
“Triggered: Processes new data on a schedule or when manually triggered.”
↩︎ Data types and ingestion frequency“Databricks recommends starting with the most managed layer.”
↩︎ Unity Catalog governance for file ingestion“it's the recommended alternative to COPY INTO.”
↩︎ Exam trap 1“Continuous: Processes new data as it arrives in the source.”
↩︎ Checkpoint - 7.
“Custom connectors let you build your own connector for a source that has no managed connector.”
↩︎ Data types and ingestion frequency“They're open-source, built and maintained by the community, and not backed by Databricks SLAs.”
↩︎ Data types and ingestion frequency - 8.
“A query-based connector queries the source database directly on a schedule, without a gateway or staging storage.”
↩︎ Data types and ingestion frequency“standard connectors, which trade some automation for broader source support and customization”
↩︎ Unity Catalog governance for file ingestion“The managed connector uses this connection to authenticate without requiring credentials in your pipeline configuration.”
↩︎ Unity Catalog governance for file ingestion - 9.https://docs.databricks.com/aws/en/data-engineeringOfficial docs
“A job can consist of one or more tasks that run notebooks, pipelines, managed connectors, SQL queries”
↩︎ Data types and ingestion frequency - 10.https://docs.databricks.com/aws/en/ingestion/cloud-object-storage/auto-loader/unity-catalogOfficial docs
“Databricks recommends always storing checkpoint and schema evolution information in storage locations managed by Unity Catalog.”
↩︎ Unity Catalog governance for file ingestion“Unity Catalog does not allow you to nest checkpoint or schema inference and evolution files under the table directory.”
↩︎ Unity Catalog governance for file ingestion“When you use read_files in a CREATE STREAMING TABLE statement inside Lakeflow pipelines, checkpoint and schema locations are managed automatically.”
↩︎ Unity Catalog governance for file ingestion“To keep data in a specific storage location, use a Unity Catalog external table instead of a managed table.”
↩︎ Unity Catalog governance for file ingestion