What you will be able to do
- List the Unity Catalog privileges and workspace prerequisites needed to create a managed database ingestion pipeline
- Walk through creating a SQL Server gateway and ingestion pipeline in the ingestion wizard, and deploy one with Declarative Automation Bundles
- Explain how schedules, jobs and incremental ingestion keep destination tables fresh
- Predict how a managed connector reacts to source schema changes and deleted tables, and when a full refresh is required
1.Prerequisites: Unity Catalog privileges and network paths
A reliable managed pipeline depends on the right setup before you create it. The SQL Server connector is a good worked example, because database connectors have the most components. Your workspace must be enabled for Unity Catalog and have serverless compute turned on. You also need privileges on the objects the pipeline will create or use:
| Privilege | On | Why it's needed |
|---|---|---|
| CREATE CONNECTION | Metastore | Create a new connection that stores the source credentials |
| USE CONNECTION (or ALL PRIVILEGES) | Existing connection | Reuse a connection someone else created |
| USE CATALOG | Target catalog | Write into the destination catalog |
| USE SCHEMA, CREATE TABLE, CREATE VOLUME | Existing schema | Create destination streaming tables and the staging volume |
| CREATE SCHEMA | Target catalog | Alternative when no suitable schema exists yet |
Two source-side requirements catch people out. You need a primary SQL Server instance, because change tracking and CDC are not supported on read replicas or secondary instances. The gateway also needs a network path to the database. It runs on classic compute in your workspace VPC or VNet. Any route that reaches the database works, including VPN, VPC/VNet peering, public endpoints, and AWS Direct Connect or Azure ExpressRoute for on-premises databases. Cross-cloud connections are supported too. SaaS connectors don't need any of this, because they call the source's APIs.
Some connections can't be scripted. Connectors whose only authentication option is browser-based OAuth (U2M), such as HubSpot, Jira and Slack, need an interactive sign-in. All other connectors can have their connections created with databricks connections create --json, from a notebook, or from a bundle.
Checkpoint 1 of 6· Check yourself
A team points a new SQL Server managed connector at a read replica to protect the primary from load. What happens?
The connector needs a primary instance. Change tracking and CDC are not available on replicas or secondaries.
“Change tracking and change data capture features are not supported on read replicas or secondary instances.”Source: docs.databricks.com
2.Creating the gateway and ingestion pipeline
In the UI, open Data Ingestion in the sidebar and choose SQL Server under Databricks connectors. The wizard then takes you through these pages:
1. Connection: pick the connection that holds the credentials. 2. Ingestion setup: name the ingestion pipeline, choose a catalog and schema for the event log (audit logs, data quality checks, progress and errors), name the gateway, and choose the staging catalog and schema where the volume will be created. 3. Source: select tables. You can also set destination names and history tracking (SCD type 2). 4. Destination: choose the catalog and schema for the output tables. 5. Database setup: click Validate to check that the source is configured correctly. 6. Schedules and notifications: optionally add a refresh schedule and success/failure emails.
The Ingestion setup page also has an optional Auto full refresh toggle. When it's on, the pipeline tries to fix problems such as log cleanup events and some schema changes by fully refreshing the affected table. If history tracking is enabled, that refresh erases the history.
Checkpoint 2 of 6· Put it in order
Put the SQL Server ingestion wizard pages in order
- 1.Schedules and notifications: set refresh frequency and alerts
- 2.Destination: choose the catalog and schema to load into
- 3.Connection: select the connection with SQL Server credentials
- 4.Ingestion setup: name the pipeline and gateway, choose event-log and staging locations
- 5.Source: select the tables to ingest
- 6.Database setup: validate the source configuration
The wizard collects credentials first, then pipeline and gateway setup, then the source tables and destination. It finishes with source validation and the optional scheduling.
“On the Database setup page, click Validate to confirm that your source is properly configured for Databricks ingestion.”Source: docs.databricks.com
Pipelines can also be created as code with Declarative Automation Bundles, which support source control, code review and CI/CD across development, staging and production. Notebooks and Terraform work too. With bundles, you set the staging location in the gateway_definition section of the pipeline YAML. The staging catalog can be the same as the destination catalog, but it can't be a foreign catalog. Run databricks bundle init to create the bundle and databricks bundle deploy to deploy it.
Size the gateway carefully, because an undersized gateway can fail during the initial snapshot. Workers don't affect gateway performance, so Databricks recommends the smallest possible worker nodes. The driver needs at least 8 cores. Databricks gives an example compute policy:
{ "driver_node_type_id": { "type": "fixed", "value": "r5n.16xlarge" }, "node_type_id": { "type": "fixed", "value": "m5n.large" }}After deployment there is one firm rule: do not manually stop the gateway. If it stops, changes can be lost when the source's log retention removes them, and every affected table then needs a full refresh. Restarting also re-provisions the VM, which slows startup.
Checkpoint 3 of 6· Exam question
A data engineering team needs to bring Salesforce CRM records into Unity Catalog governed Delta tables. The team wants minimal ongoing maintenance, automatic scheduling, and does not need custom transformation logic during ingestion. Which approach best fits this requirement?
Correct answer: A — Configure a Lakeflow Connect managed connector for Salesforce, which handles scheduling, incremental reads, and governance automatically once the connection is set up.
- A. A managed connector is the correct fit here because it automates scheduling, incremental ingestion, and Unity Catalog governance for supported SaaS sources like Salesforce without requiring custom pipeline code.
- B. Hand-rolled Structured Streaming against the Salesforce API duplicates functionality the managed connector already provides and adds exactly the maintenance burden the team wants to avoid.
- C. Manual CSV exports and ad hoc `COPY INTO` runs introduce operational overhead and break the automatic scheduling requirement, and this path bypasses Salesforce's native API entirely.
- D. Standard connectors are the more customizable, higher-maintenance option meant for cases needing custom transformation logic, which the team explicitly said it does not need.
Sources1
3.Schedules, jobs and incremental ingestion
Lakeflow Connect creates a Lakeflow Job for each schedule you add, and the ingestion pipeline runs as a task inside that job. You can add further tasks to the job, such as downstream transformations. For database connectors, the gateway runs separately in its own job as a continuous task. Schedules can't be changed with an ALTER statement on the destination tables. You change them through the pipeline's schedule settings.
What makes each scheduled run cheap is incremental ingestion. The first run ingests all of the selected data and starts tracking changes at the same time. Later runs use that change tracking to ingest only what changed since the previous run, where possible. The method depends on the source. SQL Server supports both change tracking and CDC, while the Salesforce connector picks a cursor column from a fixed list of options. Some sources or tables don't support incremental ingestion yet, so check your connector's page.
If a run takes longer than the schedule interval, Databricks skips update N+1 and picks up again at update N+2, provided update N has finished in time.
The continuous gateway keeps writing captured changes into the Unity Catalog staging volume. The scheduled ingestion pipeline later applies whatever has built up there. Staged data is purged after 30 days, so the pipeline must run more often than that.
Checkpoint 4 of 6· Check yourself
What does a managed ingestion pipeline ingest on its second and later runs, when the source supports incremental ingestion?
The first run loads everything and starts tracking changes. Later runs ingest only the changes, where the source supports it.
“it uses that change tracking to ingest only the data that's changed from the prior run, when possible.”Source: docs.databricks.com
4.Schema changes, inactive tables and full refreshes
Many managed connectors handle schema changes automatically unless you opt out by listing the exact columns to ingest. Some connectors use a fixed, connector-defined schema and don't evolve at all. Check the Feature availability section for your connector. When a new source column appears, the next run ingests it, and rows loaded before the change have no value in that column. A deleted column is marked inactive rather than dropped. If a different column later arrives with the same name, the pipeline fails, and you need a full refresh of the table or a manual drop of the inactive column.
Tables work the same way. If you ingest a whole schema, new source tables are picked up automatically, and deleted source tables are marked inactive. Writing to one of those causes CANNOT_WRITE_TO_INACTIVE_TABLES. If the source table was recreated, full-refresh the affected table. If it was deleted on purpose, drop the destination table and remove it from the pipeline.
Other changes depend on the connector. Salesforce treats a column rename as a deletion plus an addition. SQL Server needs a full refresh of the affected tables to keep ingesting. For SaaS and CDC connectors in general, type changes need a full refresh.
| Source change | Connector behaviour | Your action |
|---|---|---|
| New column | Ingested on the next run; earlier rows left empty | None, unless you opted out of new columns |
| Deleted column | Marked inactive in the destination | Full refresh or drop the inactive column if the name is reused |
| Deleted table | Destination table marked inactive | Full refresh if recreated; otherwise drop it and remove it from the pipeline |
| Column type change | Not handled automatically | Full refresh |
Recovery has its own limits. Deleting the ingestion pipeline drops its destination tables. UNDROP TABLE restores a streaming table's metadata and retained data, but the pipeline may not be able to resume from its earlier source position. Before falling back to a full refresh, confirm that the source still holds all the history you need. Also note that if a bundle manages the pipeline, any edits made in the UI are overwritten on the next bundle deploy, so edit the bundle's definition file instead.
Checkpoint 5 of 6· Exam question
An operations database on SQL Server has rows frequently updated and deleted, and the target Unity Catalog table must reflect these changes rather than just newly appended rows. Which Lakeflow Connect capability addresses this requirement?
Correct answer: A — The managed SQL Server connector's change data capture support, which propagates inserts, updates, and deletes into the destination table.
- A. Change data capture on the managed database connectors is exactly what lets updates and deletes in the source flow through to the destination table, not just newly inserted rows.
- B. Schema evolution mode governs how column changes in the source schema are applied to the destination table structure, not how row-level updates and deletes are captured.
- C. `COPY INTO` is a file-based batch loader for cloud object storage and tracks which files were already ingested; it is not the mechanism used for SQL Server change data capture.
- D. A nightly truncate-and-reload job could work but is an operationally expensive workaround that Lakeflow Connect's CDC-aware managed connector is specifically designed to avoid.
Checkpoint 6 of 6· Check yourself
A pipeline fails with CANNOT_WRITE_TO_INACTIVE_TABLES after the source team recreated a table they had dropped. What resolves it while keeping ingestion going?
A table becomes inactive when its source table is deleted. If the source was recreated and you want to keep ingesting, fully refresh that table.
“If you want to resume ingesting from the source table (for example, it was recreated), run a full refresh of the affected table.”Source: docs.databricks.com
Exam traps
Each one states something that sounds right. Open it to see what is actually true.
1.Stop the ingestion gateway while the ingestion pipeline is idle to save classic compute cost.Why is that wrong?
The gateway must run continuously. If it stops, the source's log retention can drop changes, and every affected table then needs a full refresh.
Covered in Creating the gateway and ingestion pipeline
2.A column renamed or re-added under the same name in the source simply evolves the destination table.Why is that wrong?
A deleted column is kept as inactive. If a new column then arrives with the same name, the pipeline fails until you run a full refresh or drop the inactive column.
Covered in Schema changes, inactive tables and full refreshes
3.UNDROP TABLE on a destination streaming table restores it and the pipeline continues where it left off.Why is that wrong?
UNDROP brings back metadata and retained data, but the connector may not be able to resume from its earlier source offset. A full refresh may be needed.
Covered in Schema changes, inactive tables and full refreshes
Sources
Every claim above is drawn from one of these pages, quoted as it was written on the date shown.
- 1.
“You have USE SCHEMA, CREATE TABLE, and CREATE VOLUME privileges on an existing schema or CREATE SCHEMA privileges on the target catalog.”
↩︎ Prerequisites: Unity Catalog privileges and network paths“the pipeline automatically tries to fix issues like log cleanup events and certain types of schema evolution by fully refreshing the impacted table.”
↩︎ Creating the gateway and ingestion pipeline“The staging catalog can't be a foreign catalog.”
↩︎ Creating the gateway and ingestion pipeline“Databricks recommends specifying the smallest possible worker nodes for ingestion gateways because they do not impact gateway performance.”
↩︎ Creating the gateway and ingestion pipeline“If the gateway is stopped, changes can be dropped due to log retention, requiring a full refresh of all affected tables.”
↩︎ Exam trap 1“Change tracking and change data capture features are not supported on read replicas or secondary instances.”
↩︎ Checkpoint“On the Database setup page, click Validate to confirm that your source is properly configured for Databricks ingestion.”
↩︎ Checkpoint - 2.
“Any network path that allows the gateway to reach the database is supported”
↩︎ Prerequisites: Unity Catalog privileges and network paths - 3.
“Connectors that use browser-based OAuth (OAuth U2M) as their only authentication option cannot be created programmatically.”
↩︎ Prerequisites: Unity Catalog privileges and network paths“For each schedule that you add to a pipeline, Lakeflow Connect automatically creates a job for it.”
↩︎ Schedules, jobs and incremental ingestion“For database connectors, the ingestion gateway runs in its own job as a continuous task.”
↩︎ Schedules, jobs and incremental ingestion“the Salesforce connector selects a cursor column from a set list of options.”
↩︎ Schedules, jobs and incremental ingestion“it uses that change tracking to ingest only the data that's changed from the prior run, when possible.”
↩︎ Checkpoint - 4.
“You cannot modify the schedule or trigger of a managed ingestion pipeline using an ALTER statement.”
↩︎ Schedules, jobs and incremental ingestion“For any row in the column that appeared prior to the schema change, Databricks leaves the value empty.”
↩︎ Schema changes, inactive tables and full refreshes“the SQL Server connector requires a full refresh of the affected tables to continue ingestion.”
↩︎ Schema changes, inactive tables and full refreshes“The destination tables are dropped when the ingestion pipeline is deleted.”
↩︎ Schema changes, inactive tables and full refreshes“If another column later appears that has the same name, then the pipeline fails.”
↩︎ Exam trap 2“When a column is deleted from the source, Databricks doesn't delete it automatically.”
↩︎ Prediction - 5.
“The SaaS and CDC connectors evolve the schema automatically when columns change.”
↩︎ Schema changes, inactive tables and full refreshes - 6.
“A table becomes inactive when the corresponding source table is deleted.”
↩︎ Schema changes, inactive tables and full refreshes“Edits made in the UI are overwritten the next time you run bundle deploy.”
↩︎ Schema changes, inactive tables and full refreshes“restoring the table does not guarantee that the managed ingestion pipeline can resume from its previous source position.”
↩︎ Exam trap 3“If you want to resume ingesting from the source table (for example, it was recreated), run a full refresh of the affected table.”
↩︎ Checkpoint