Databricks Data Engineer Professional
Section 2 · 12%Data Ingestion & Acquisition

Ingestion and acquisition

Formats and sources, CDC from databases with Lakeflow Connect, OpenSharing, Clean Rooms and Lakehouse Federation.

This section tests whether you can pick the right way to get data into the lakehouse: files, message buses, database change feeds, shared data, or data you leave in place. The biggest way people lose marks is choosing a copy-everything pipeline when the stem's deciding fact (changes only, no copy allowed, recipient has no Databricks) points to a different tool.

PracticeS2 practice bank →

7 items, practice or timed exam mode.

Walk the guide, line by line

Each row is one line of the official guide, in its own words on the left. Read across: plain words, what a question looks like, the answer, and the trap.

Guide lineIn plain wordsA question looks likeThe answerThe trap
2.1 Develop data ingestion pipelines to ingest a variety of data formats, including Delta Lake, Parquet, JSON, Iceberg, CSV, and Binary from diverse sources such as message buses (Kafka, Kinesis, Pub/Sub) and cloud storage.Read many file formats (Delta, Parquet, JSON, Iceberg, CSV, binary files) from cloud storage, and read events from message buses such as Kafka, Kinesis and Google Pub/Sub, into tables. Auto Loader is the Databricks reader that picks up only new files from cloud storage.A retailer's partners drop new JSON files into cloud storage all day, and a second team reads clickstream events from a Kafka topic. The question asks which reader fits each source, or how to cope with a column that suddenly appears.For files arriving in storage, use Auto Loader (format cloudFiles), which remembers which files it has processed so each file is read once. For a message bus, use the built-in streaming source named for it: format kafka needs kafka.bootstrap.servers plus one of subscribe, subscribePattern or assign; format kinesis takes streamName or streamARN; format pubsub needs projectId, topicId and subscriptionId. Kafka keys and values arrive as binary and must be cast or decoded. For schema drift, Auto Loader's default mode with no supplied schema is addNewColumns: the stream stops once after adding the new column and continues on restart, while rescue keeps the schema fixed and puts surprises in the _rescued_data column.
Try it: On Free Edition, upload two small JSON files to a volume, run an Auto Loader stream with trigger availableNow, add a third file with a new field, and watch the schema behavior.
Re-listing a whole folder with a plain batch spark.read on every run works for small folders but rereads or misses files as the folder grows, because it has no memory of what it already processed. People also forget that addNewColumns deliberately fails the stream once, so the job must be restarted automatically.
Runner-up wins when: A plain batch read of the directory wins when the data is a one-off load or a small static folder where tracking processed files adds no value.
2.2 Configure incremental CDC pipelines using Lakeflow Pipelines with Delta or Iceberg as the target table format.Capture changes (inserts, updates, deletes) arriving as a feed and apply them to a target table inside a Lakeflow Pipeline, so the target always shows the right current state. AUTO CDC is the pipeline command for this; it was called APPLY CHANGES before, with the same syntax.A change feed arrives with a key, an operation column and a sequence number, and events can show up out of order. The question asks how to keep the target correct, or whether to keep history.Use AUTO CDC (SQL: CREATE FLOW ... AS AUTO CDC INTO ...; Python: create_auto_cdc_flow) with KEYS to identify the row, SEQUENCE BY to order the changes, and APPLY AS DELETE WHEN to turn delete events into deletions. The sequence column decides the final state, so a late event with an older sequence number does not overwrite a newer one. SCD type 1 keeps only the latest row; SCD type 2 keeps history with __START_AT and __END_AT, and a null __END_AT marks the current row. Break sequence ties by combining columns in a STRUCT. The page I checked did not mention Iceberg as a target, so check the docs for the Iceberg target-format details.
Try it: In a Free Edition pipeline, feed a small table of events with a sequence column into AUTO CDC type 1, then switch to type 2 and compare the rows.
Hand-writing a MERGE INTO in a streaming batch function seems equivalent, but you must then code out-of-order handling, deletes and history yourself, and a wrong arrival-order assumption silently corrupts the target. Another trap is using SCD type 2 when only the latest value is needed, which grows the table for no benefit.
Runner-up wins when: A hand-written MERGE wins when the logic is not plain upsert-and-delete, for example when you need custom conflict rules between several sources.
2.3 Configure CDC ingestion pipelines from relational database sources, including SQL Server, MySQL, and PostgreSQL, using Lakeflow Connect.Copy a relational database (SQL Server, MySQL, PostgreSQL) into the lakehouse and keep it up to date from the database's own change log, using Lakeflow Connect, the managed connector service.A bank wants its operational SQL Server tables in Unity Catalog, refreshed incrementally, with no custom extraction code. The question asks which setup components are involved or why the source database needs preparation.Lakeflow Connect gives you a managed ingestion pipeline that reads changes and writes streaming tables in Unity Catalog, orchestrated by Lakeflow Jobs. For SQL Server there are two architectures I verified: the standard one with an ingestion gateway plus an ingestion pipeline, and an integrated one with a single pipeline using a Unity Catalog connection. The source database must be set up for change capture first (CDC or change tracking), and the connection holds the credentials. Limits I verified for SQL Server: up to 250 tables per pipeline, column renames need a full refresh, and new tables are picked up automatically only when you ingest a whole schema.Using Lakehouse Federation to query the source live is tempting because it needs no pipeline, but it reads the operational database on every query and keeps no history or copy. Writing a custom JDBC loop fails the stated no-custom-code requirement and cannot see deletes.
Runner-up wins when: Federation wins when the need is occasional ad hoc reads of a small amount of operational data and nobody wants a stored copy.
2.4 Implement OpenSharing (D2D and Databricks-to-Open) and Clean Rooms for privacy-preserving collaboration.Share data with outside parties without copying it, and let several parties analyze combined sensitive data without seeing each other's rows. OpenSharing is the Databricks sharing protocol; Clean Rooms is the feature built on it for privacy-preserving joint analysis.A supplier wants to give a partner read access to a table. In another scenario two advertisers want a joint overlap analysis but neither may see the other's customer rows. The question asks which mechanism fits the recipient or the privacy rule.If the recipient has a Unity Catalog workspace, use Databricks-to-Databricks (D2D) sharing: no token is needed and it can share tables, views, volumes, notebooks and models. If the recipient has no Databricks, use Databricks-to-Open sharing: the provider issues a bearer token, or you use OIDC federation, and the data is tabular only. When parties must compute together without exposing raw data, use a Clean Room: each side shares data and notebooks into a central environment, every collaborator must approve a notebook before it runs, and collaborators see only column names and types of others' data. A clean room is limited to ten collaborators.Open sharing with a token is tempting for everyone because it works with any tool, but it gives the recipient direct read access to the rows, which breaks a privacy rule, and D2D is simpler and better governed when both sides use Databricks. Plain sharing also cannot stop a partner reading raw rows.
Runner-up wins when: Plain open sharing wins over a clean room when the partner is allowed to see the rows and may not use Databricks.
2.5 Configure Lakehouse Federation with governance across supported source systems, applying UC permissions and connection-level credentials.Query data that stays in an outside system, such as another database, through Unity Catalog, with the same permission model and with credentials kept on a connection object rather than given to users. This is Lakehouse Federation.Analysts need to query a PostgreSQL reporting database from Databricks but must not receive its password. The question asks what to create and who is granted what.Create a Unity Catalog connection that stores the credentials and JDBC address, create a foreign catalog on that connection, then grant users privileges on the tables in the foreign catalog. Users never see the password, and access is audited through Unity Catalog. Federation is read-only. Query federation pushes queries down to the external database; catalog federation reads the tables directly in object storage using a storage credential and external location. The exact privilege names such as CREATE CONNECTION are not on the overview page, so check the privileges docs.Handing out the database password in a notebook secret works but bypasses Unity Catalog permissions and auditing. Expecting to write back through the foreign catalog fails because federation does not support writes.
Runner-up wins when: Ingesting a copy wins when queries are heavy and frequent, since federation pushes load onto the source system each time.

Mind map: Which acquisition tool

People assume every source needs a pipeline that copies the data; the deciding fact is often whether you need a copy at all, who the recipient is, or whether you need only changes.

What you observeWhat it points toWhat to do (and when the runner-up wins)
New files keep landing in cloud storageAuto Loader (cloudFiles) with a checkpointUse trigger availableNow for scheduled catch-up; a plain batch read wins only for a one-off static folder.
Events from a topic, stream or subscriptionKafka, Kinesis or Pub/Sub streaming sourceCast Kafka's binary key and value; choose the source by the bus, not by the file format.
Operational database, incremental, no custom codeLakeflow Connect managed pipelinePrepare the source for change capture; federation wins if you need occasional live reads and no copy.
Data must stay where it is, queried through governanceLakehouse Federation connection and foreign catalogIngest instead if queries are heavy and frequent against the source.
Partner outside Databricks needs read accessOpen sharing with token or OIDCD2D wins the moment the recipient has a Unity Catalog workspace.

Mind map: Applying a change feed

People assume the latest row to arrive is the latest change; with out-of-order feeds the sequence column decides.

What you observeWhat it points toWhat to do (and when the runner-up wins)
Events arrive late or out of orderSEQUENCE BY column in AUTO CDCTrust the sequence value, not arrival time; use a STRUCT to break ties.
Feed marks removals with an operation valueAPPLY AS DELETE WHENWithout it deleted source rows stay in the target.
Only the current value mattersSCD type 1Switch to type 2 when the stem asks for history or point-in-time answers.
Stem asks what the customer's address was last MarchSCD type 2 with __START_AT and __END_ATA null __END_AT is the current row.