Overview
Section 2 is about the first hop of every pipeline: moving data from files, databases, SaaS applications and APIs into Unity Catalog-governed Delta tables. Its seven objectives cover ingestion patterns (batch, streaming and incremental), the COPY INTO command for idempotent file loads, Auto Loader with schema inference, enforcement and evolution, and Lakeflow Connect with standard and managed connectors. Two objectives look beyond Databricks-native tools: pulling data with JDBC, ODBC or REST clients from notebooks, and choosing between Auto Loader, Lakeflow Connect, partner connectors and other methods.
The final objective deals with semi-structured and unstructured data, such as nested JSON arriving through connectors. The common thread is incremental processing: each method has its own way of tracking what has already been loaded, and the exam expects you to know which one fits a stated volume, frequency and governance need.
How it's tested
Most items describe a source, a file volume, an arrival pattern and a constraint such as schema drift or minimal maintenance, then ask which ingestion tool or option to use. Some show a COPY INTO statement or an Auto Loader readStream call and ask what happens on a rerun or when a new column appears.
-
Choose the ingestion tool
Given a source type and volume, pick Auto Loader, COPY INTO, a Lakeflow Connect managed connector, a partner tool or a notebook JDBC pull.
-
Predict schema behaviour
A new or mistyped column arrives and you predict what Auto Loader does under a given schemaEvolutionMode or with the rescued data column.
-
Rerun safety
A load is run twice or restarted, and you decide whether files are skipped, duplicated or reprocessed.
A folder in S3 receives millions of small JSON files a day and new fields appear every few weeks. COPY INTO would work for a few thousand files but tracks loaded files less efficiently at this scale. Auto Loader with a checkpoint, a schema location and addNewColumns evolution scales to millions of files and adapts to new fields, so it is the better answer.
Objectives 7
Official objectives from the Databricks outline, in outline order.
2.1 Enable and detail data ingestion patterns, including batch, streaming, and incremental loading, and import data from sources such as local files, Lakeflow Connect standard connectors, and Lakeflow Connect managed connectorsThe main ingestion patterns and where each source type enters the platform.
- What it means
- Batch ingestion reads a full or bounded set of data each run. Streaming ingestion processes records continuously as they arrive. Incremental loading sits between them: each run picks up only what is new since the last run, often with a streaming engine run on a trigger. Sources range from files uploaded from a laptop to cloud storage, message buses and SaaS applications.
- What to understand
- Local files can be uploaded through the Add data UI to create a table or placed in a Unity Catalog volume. Lakeflow Connect standard connectors cover sources such as cloud object storage, Kafka and similar systems that you configure with Auto Loader or Structured Streaming. Managed connectors handle SaaS applications and databases such as Salesforce, Workday or SQL Server with a guided setup and serverless ingestion pipelines. Incremental patterns depend on stored state, such as checkpoints or a load history, so that reruns skip data already processed.
- How it shows up
- Expect questions that describe arrival frequency and latency, then ask whether batch, streaming or triggered incremental loading fits.
- Common confusion
- Treating incremental loading as the same as streaming; an availableNow trigger processes new data and then stops.
2.2 Use the COPY INTO command to incrementally load files from cloud object storage (ADLS/S3/GCS) into Unity Catalog-governed tablesIdempotent, SQL-based file loading into Delta tables.
- What it means
- COPY INTO is a SQL command that loads files from a cloud storage path or volume into a Delta table. It remembers which files it has already loaded, so running the same statement again skips those files. It suits scheduled loads of thousands of files rather than millions.
- What to understand
- The statement names the target table, the source location, FILEFORMAT, and optional FORMAT_OPTIONS and COPY_OPTIONS. Setting mergeSchema to true in COPY_OPTIONS lets new columns be added to the target. A target can be created as an empty table first and the schema inferred on the first load. To reload files that were already ingested, set force to true, which disables the skip behaviour. Access to the source path is governed through an external location or volume in Unity Catalog rather than embedded keys.
- How it shows up
- Questions test rerun behaviour, schema merging and when COPY INTO is preferred over Auto Loader.
- Common confusion
- Expecting COPY INTO to reload a file after it has been modified; files already loaded are skipped unless force is used.
2.3 Use Auto Loader with schema enforcement and schema evolution in batch modes (for example, directory listing or file notification) to land data into Unity Catalog-governed tablesIncremental file ingestion with cloudFiles, schema inference and evolution.
- What it means
- Auto Loader is the cloudFiles source for Structured Streaming. It discovers new files in a path, tracks them in a checkpoint and loads each one exactly once. Run with an availableNow trigger, it behaves like a batch job that processes the backlog and stops.
- What to understand
- Set cloudFiles.format and cloudFiles.schemaLocation so the inferred schema is stored and reused. By default JSON and CSV columns are inferred as strings unless cloudFiles.inferColumnTypes is true. Schema evolution modes are addNewColumns (the default: the stream stops, updates the schema and succeeds on restart), rescue (new fields go to the _rescued_data column), failOnNewColumns and none. Directory listing mode scans the path and needs no extra setup. File notification mode uses cloud queue services, or managed file events, to scale to very large directories. Schema hints let you fix types for specific columns.
- How it shows up
- Expect questions on what happens when a new column appears, where unparseable values go, and which discovery mode to use for large folders.
- Common confusion
- Assuming addNewColumns evolves without interruption; the stream fails once and picks up the new schema on restart.
2.4 Configure Lakeflow Connect to reliably ingest data from diverse enterprise sources into Unity Catalog-governed tablesSetting up managed ingestion from SaaS applications and databases.
- What it means
- Lakeflow Connect provides managed connectors that pull data from enterprise systems into streaming tables in Unity Catalog. You create a connection that stores credentials, choose source objects, pick a destination catalog and schema, and Databricks runs the ingestion on serverless compute.
- What to understand
- SaaS connectors such as Salesforce or Workday use an ingestion pipeline that reads incrementally through the source API. Database connectors such as SQL Server add an ingestion gateway that captures changes continuously, paired with a pipeline that applies them. Connections are Unity Catalog securables, so access to credentials is controlled by grants. Ingestion pipelines are scheduled with Lakeflow Jobs or run on their own schedule, and can track history with SCD type settings. Source prerequisites, such as change tracking on a database, must be enabled first.
- How it shows up
- Scenario items ask how to bring a supported SaaS or database source in with the least custom code.
- Common confusion
- Writing a custom API notebook for a source that already has a managed connector.
2.5 Use JDBC/ODBC or REST clients in notebooks to land data into cloud storage or directly into Unity Catalog-governed tables, usually orchestrated and scheduled with Lakeflow JobsCustom pulls from databases and APIs when no connector fits.
- What it means
- When no managed connector exists, a notebook can read a database over JDBC with spark.read.format("jdbc") or call a REST API with a Python HTTP client. The results are written either as files to a volume or cloud path, or straight into a Delta table, and the notebook runs as a task in a scheduled job.
- What to understand
- JDBC reads take url, dbtable or query, user and password options. Credentials belong in secret scopes and are read with dbutils.secrets.get rather than written in code. Options such as partitionColumn, lowerBound, upperBound and numPartitions split a large read across parallel connections. For incremental pulls, filter on a watermark column such as a last-modified timestamp stored from the previous run. REST responses are often paged JSON: write raw pages to a volume for Auto Loader to pick up, or parse them into a DataFrame and append to a bronze table. A Lakeflow Jobs schedule then runs the notebook.
- How it shows up
- Expect questions on secure credential handling and parallel JDBC reads.
- Common confusion
- Hard-coding passwords in the JDBC URL instead of using a secret scope.
2.6 Prioritize between Auto Loader, Lakeflow Connect (standard and managed connectors), partner connectors, and other ingestion methods based on technical requirements such as data volume, ingestion frequency, data types, and governance needs with Unity CatalogChoosing an ingestion method from stated requirements.
- What it means
- No single tool fits every source. This objective asks you to rank options by volume, how often data arrives, the types of data involved and how much governance you need in Unity Catalog. The preferred answer is usually the most managed option that supports the source.
- What to understand
- Files arriving in cloud storage at high volume point to Auto Loader. A one-off or modest scheduled file load can use COPY INTO. A supported SaaS application or database points to a Lakeflow Connect managed connector. Sources with no native connector may be covered by partner tools from Partner Connect, such as Fivetran, or by a custom JDBC or REST notebook as a last resort. Governance favours methods that write to Unity Catalog tables with connections and external locations, so credentials and lineage stay tracked.
- How it shows up
- Questions list several requirements and four methods; one requirement usually rules out each wrong answer.
- Common confusion
- Picking the most flexible custom option when a managed connector covers the source.
2.7 Ingest semi-structured and unstructured data (for example, JSON and nested data) via Lakeflow Connect and other managed connectors into Unity Catalog-governed Delta tablesLanding nested JSON and similar data and making it queryable.
- What it means
- Semi-structured data has a flexible shape: JSON objects with nested fields, arrays and keys that change between records. Unstructured data such as PDFs or images has no tabular shape at all. Both can be landed in Unity Catalog-governed storage, but they are queried differently.
- What to understand
- JSON can be stored as a STRING column and queried with colon syntax such as raw:customer.id, parsed into a STRUCT with from_json and a schema from schema_of_json, or stored as the VARIANT type with parse_json for flexible, efficient access. Struct fields are read with dot notation and arrays are expanded with explode. Auto Loader infers nested JSON into structs and places mismatched values in _rescued_data. Unstructured files are often stored in volumes and read with the binaryFile format, which returns path, length and content columns.
- How it shows up
- Expect syntax-reading items on colon versus dot notation and on which type to use for changing JSON.
- Common confusion
- Using dot notation on a JSON string column; dot works on structs, colon works on JSON strings.
High-yield concepts
The terms, rules and formulas this domain turns into questions most often.
-
COPY INTO idempotency
Already loaded files are skipped on rerun.
- Remember
- Use force = true to reload.
- Tested as
- Rerun and retry items.
- Trap
- Expecting duplicates after a retry.
-
cloudFiles.schemaLocation
Path where Auto Loader stores inferred schemas.
- Remember
- Required for inference and evolution.
- Tested as
- Auto Loader configuration questions.
- Trap
- Omitting it and expecting evolution to work.
-
_rescued_data
Column holding values that do not fit the schema.
- Remember
- Keeps data instead of dropping it.
- Tested as
- Schema drift scenarios.
- Trap
- Assuming bad values are silently lost.
-
addNewColumns
Default Auto Loader evolution mode.
- Remember
- Stream stops once, then restarts with the new schema.
- Tested as
- New column scenarios.
- Trap
- Thinking the stream never fails.
-
availableNow trigger
Processes all available data then stops.
- Remember
- Incremental batch with streaming state.
- Tested as
- Scheduled incremental loads.
- Trap
- Confusing it with a continuous stream.
-
File notification mode
Uses cloud events or queues to find new files.
- Remember
- Scales to very large directories.
- Tested as
- Discovery mode choice.
- Trap
- Using listing on a huge folder.
-
Managed connector
Lakeflow Connect ingestion for SaaS and databases.
- Remember
- Runs on serverless with UC connections.
- Tested as
- Least-effort ingestion items.
- Trap
- Building custom API code instead.
-
JDBC partitioned read
partitionColumn, bounds and numPartitions.
- Remember
- Parallelizes reads from a database.
- Tested as
- Large table pull questions.
- Trap
- Reading a big table over one connection.
-
VARIANT type
Native type for semi-structured data.
- Remember
- Created with parse_json; flexible and efficient.
- Tested as
- Changing JSON shape items.
- Trap
- Forcing a rigid struct on drifting data.
-
Colon notation
Extracts fields from JSON strings, e.g. raw:a.b.
- Remember
- Dot notation is for structs.
- Tested as
- Syntax interpretation items.
- Trap
- Mixing up the two notations.
Side by side
Concepts candidates mix up, compared on the point that decides the answer.
COPY INTO vs Auto Loader
| Concept | Meaning | Key difference | Common trap |
|---|---|---|---|
| COPY INTO | SQL command for loading files into a table. | Best for thousands of files on a schedule. | Using it for millions of files arriving continuously. |
| Auto Loader | Structured Streaming source cloudFiles. | Scales to millions of files with checkpoints. | Forgetting the schema location and checkpoint. |
| Auto Loader with availableNow | Runs like a batch over the backlog. | Keeps incremental state between runs. | Thinking it needs an always-on cluster. |
Connector choices
| Concept | Meaning | Key difference | Common trap |
|---|---|---|---|
| Lakeflow Connect managed connector | Databricks-built ingestion for SaaS and databases. | Minimal code, serverless, UC-governed. | Ignoring source prerequisites like change tracking. |
| Partner connector | Third-party tool linked through Partner Connect. | Covers sources without native connectors. | Choosing it when a native connector exists. |
| Custom JDBC or REST notebook | Your own code pulling data. | Most flexible, most maintenance. | Storing credentials in plain text. |
Exam traps
Each trap is an answer that looks right. Learn the clue that should change your mind.
-
COPY INTO at massive scale
- What candidates pick
- COPY INTO for millions of continuously arriving files
- Why it looks right
- It is simple SQL and idempotent.
- Why it's wrong
- Auto Loader is built to discover and track files at this scale efficiently, while COPY INTO suits smaller batches.
Clue to watch for Millions of files or continuous arrival.
-
Silent loss of bad values
- What candidates pick
- Data that does not match the schema is dropped
- Why it looks right
- Many tools discard mismatched records.
- Why it's wrong
- Auto Loader keeps such values in the _rescued_data column so they can be inspected.
Clue to watch for Questions about where malformed values end up.
-
Custom code over a connector
- What candidates pick
- A Python notebook calling the Salesforce API
- Why it looks right
- It gives full control over requests.
- Why it's wrong
- A managed Lakeflow Connect connector provides incremental, governed ingestion with far less code to maintain.
Clue to watch for Least maintenance or fastest setup.
-
Dot notation on strings
- What candidates pick
- raw.customer.id on a JSON string column
- Why it looks right
- Dot notation works on nested data elsewhere.
- Why it's wrong
- Dot notation applies to STRUCT columns; a JSON string needs colon syntax or parsing first.
Clue to watch for The column type is STRING holding JSON.
-
Rerun creates duplicates
- What candidates pick
- Add a dedup step after every COPY INTO rerun
- Why it looks right
- Many loaders duplicate on retry.
- Why it's wrong
- COPY INTO tracks loaded files and skips them, so a rerun does not duplicate data by itself.
Clue to watch for A retry or rerun of the same statement.
-
Credentials in code
- What candidates pick
- Password in the JDBC URL string
- Why it looks right
- It is the quickest way to connect.
- Why it's wrong
- Secrets belong in a secret scope or a Unity Catalog connection, not in notebook source that others can read.
Clue to watch for Security or best practice wording.
Answer framework
Work through these checks when a question asks how to ingest a source into Unity Catalog.
- Identify the source type first: files in cloud storage, a supported SaaS application or database, or a system with no native connector.
- For files, weigh volume and arrival rate, choosing Auto Loader for large or continuous arrivals and COPY INTO for modest scheduled batches.
- For applications and databases, prefer a Lakeflow Connect managed connector, then a partner connector, and only then a custom JDBC or REST notebook.
- Check schema needs: decide how new columns are handled, where mismatched values go, and whether VARIANT or a struct fits nested data.
- Confirm the answer writes to a Unity Catalog-governed table, keeps credentials in connections or secrets, and tracks progress so reruns skip loaded data.
Practice question
Want more like this? The Databricks DE Assoc practice test has questions for every domain.
Checklist
0 of 12
Tick each one you can do without notes. Your ticks stay in this browser.
Study strategy
Give this section twelve to sixteen hours and spend most of it in a workspace. Create a volume, drop a few CSV and JSON files into it, and load them with COPY INTO. Rerun the statement and confirm nothing is reloaded, then try force. Next, point Auto Loader at the same volume with an availableNow trigger, add a file with an extra field, and watch the stream stop and recover under addNewColumns. Repeat in rescue mode and query _rescued_data.
Then move to sources outside storage. Read the Lakeflow Connect documentation for one SaaS connector and one database connector and note the setup steps and prerequisites. Write a small notebook that reads a public REST API and appends results to a bronze table, with any keys stored in a secret scope. Practise a parallel JDBC read if you have a test database.
Finally, build a one-page decision table of sources and methods. Most questions in this section are choices between tools, so being able to state why each wrong option fails is worth more than memorising every option name.
FAQ
How much of the exam is Data Ingestion and Loading?
Data Ingestion and Loading is Section 2 and carries 21% of the exam, about 9 of the 45 scored questions. It has seven official objectives, the joint highest count with Section 3.
Is COPY INTO still relevant if Auto Loader exists?
Yes. The guide names it explicitly. It is simple, idempotent SQL for scheduled loads of thousands of files, and it appears in questions comparing it with Auto Loader.
Why does the guide say Auto Loader in batch modes?
Auto Loader is a streaming source, but it is often run with an availableNow trigger so each job run processes new files and stops. That incremental batch style is what the objective emphasises.
Do I need to memorise every managed connector?
No. Know the main categories, SaaS applications and databases, and how setup works: a connection, source objects, a destination schema and a serverless ingestion pipeline.
Where do I store JDBC passwords?
Use a Databricks secret scope and read values with dbutils.secrets.get, or use a Unity Catalog connection where supported. Never place them in notebook code or job parameters.
When should I use VARIANT instead of a struct?
Use VARIANT when the JSON shape changes often or is deeply nested. A struct is fine when the schema is stable and you want fixed, typed columns.
Does Auto Loader handle unstructured files?
Auto Loader can read files with the binaryFile format, producing path, modification time, length and content columns. Images and PDFs are usually stored in volumes and processed later.
Other Data & Analytics certifications
Where the same subject shows up in other exams. Cards marked "Same subject" open the matching domain.
Test your Domain 2 knowledge
Practise choosing between COPY INTO, Auto Loader, Lakeflow Connect and custom JDBC pulls in timed ingestion scenarios before exam day.
Keep going
- Training Training Built on your Weak areas
- Practice test Questions by domain, with explanations
- Mock exam Full length, timed like the real exam
- Readiness test Find your weakest domain in minutes
- Cheat sheet Key terms and formulas on one page
- Ask the Databricks DE Assoc AI tutor Get any concept explained your way