What you will be able to do
- Describe the five components of a Lakeflow Connect database connector and what each one costs and does
- Size and place the ingestion gateway so that it can reach SQL Server, MySQL or PostgreSQL
- Prepare each source database for CDC: SQL Server change tracking or CDC, the MySQL binlog, PostgreSQL logical replication
- Wire a Unity Catalog connection, an ingestion gateway and an ingestion pipeline together in the right order
Key concept
Gateway / pipeline split — A managed database connector splits the work in two. An always-on ingestion gateway on classic compute captures snapshots and change logs into a staging volume. A separate ingestion pipeline on serverless compute applies the staged changes to streaming tables on whatever schedule you choose.
1.Five moving parts of a database connector
Lakeflow Connect provides fully managed connectors that read from relational databases with change data capture (CDC) and apply the changes incrementally to Delta tables. The documentation lists connectors for MySQL, PostgreSQL and Microsoft SQL Server. The SQL Server connector can also take a full snapshot instead of CDC. All of these connectors are built from the same components, and most exam questions about them come down to knowing which component does what and where it runs.
| Component | What it is | Compute and lifecycle |
|---|---|---|
| Connection | Unity Catalog securable object that stores the database authentication details | No compute; it is reused by gateways |
| Ingestion gateway | Pipeline that extracts snapshots, change logs and metadata from the source | Classic compute, runs continuously |
| Staging storage | Unity Catalog volume that holds extracted data until it is applied | Created automatically when the gateway is deployed; data purged after 30 days |
| Ingestion pipeline | Pipeline that moves data from staging into destination tables | Serverless compute, runs on your schedule |
| Destination tables | Streaming tables: Delta tables with extra support for incremental processing | Written by the ingestion pipeline |
The staging volume is what decouples the two pipelines. The gateway writes into it all the time, and the ingestion pipeline drains it whenever it is triggered. Staging also helps with failure recovery. You can choose which catalog and schema the volume lives in, but its contents are temporary: data is automatically purged from staging after 30 days. This design has a cost: because the ingestion gateway runs continuously, you provision and pay for its classic compute even when your ingestion pipeline is idle.
Checkpoint 1 of 5· Match them up
Match each connector component to its role
Tap a term, then the definition that fits it.
The gateway extracts, the volume buffers, the pipeline applies, and the connection holds the credentials. Only the gateway uses classic compute.
“Because the ingestion gateway runs continuously, you provision and pay for its classic compute even when your ingestion pipeline is idle.”Source: docs.databricks.com
Checkpoint 2 of 5· Exam question
A team building a Lakeflow Connect ingestion pipeline from an on-premises SQL Server database needs to explain the architecture to auditors. They ask where extracted snapshot and change-log data is held after the ingestion gateway pulls it from the source, but before the ingestion pipeline applies it to the destination streaming tables. Which component fills that role?
Correct answer: B — A Unity Catalog volume configured as staging storage, which temporarily holds the extracted snapshot and change-log data until the pipeline applies it.
- A. This directory tracks the gateway's progress through the source's logs, but it does not hold the extracted rows themselves, so it does not answer where the data sits before landing in destination tables.
- B. The staging volume is exactly the intermediate holding area: the gateway writes extracted snapshots and change records there, and the serverless ingestion pipeline reads from it to populate destination tables.
- C. A connection object only stores how to authenticate to the source system; it never holds extracted data, whether in transit or at rest.
- D. The destination catalog and schema hold the final streaming tables produced by the pipeline, not the intermediate data that is still being staged.
Sources1
2.Sizing and placing the gateway
The gateway runs in the classic compute plane, inside your workspace VPC or VNet, so it needs a network route to the database. The documentation accepts any route that works: VPN, Azure ExpressRoute, AWS Direct Connect, VPC or VNet peering, or public endpoints. The route can also cross clouds. An Azure Databricks workspace can ingest from an AWS Aurora PostgreSQL database, provided the two environments can reach each other.
Yes. Cross-cloud connectivity is supported as long as a network path exists between the gateway's classic compute and the database.
Getting the size right matters. Size the gateway compute for your workload, because the initial snapshot can fail on undersized compute. The PostgreSQL pipeline page sets a minimum of 8 cores and recommends the smallest possible worker nodes, since workers do not affect gateway performance. To create the gateway, you need either unrestricted cluster creation or a custom policy (API only) in the Job Compute family with these overrides:
{ "cluster_type": { "type": "fixed", "value": "dlt" }, "num_workers": { "type": "unlimited", "defaultValue": 1, "isOptional": true }, "runtime_engine": { "type": "fixed", "value": "STANDARD", "hidden": true }}Checkpoint 3 of 5· Check yourself
A team's first PostgreSQL snapshot keeps failing. Their custom gateway policy uses a 4-core driver and large worker nodes. Which change follows Databricks guidance?
The minimum for efficient extraction is 8 cores. Worker size does not affect gateway performance, so the smallest workers are recommended. The cluster_type override must stay fixed to dlt.
“The minimum requirement is 8 cores to enable efficient and performant data extraction from your source database.”Source: docs.databricks.com
3.Preparing SQL Server, MySQL and PostgreSQL for CDC
No connector can capture changes the database is not recording. Each engine therefore needs some setup on the source before you create anything in Databricks. SQL Server gives you two tracking methods to choose from:
| Method | What it records | Primary key | Source load |
|---|---|---|---|
| Change tracking | That rows changed, not the actual operations | Required | Lightweight |
| Change data capture | Every operation, with a historical view over time | Not required | Can have more impact on performance |
Databricks recommends change tracking for every table that has a primary key, because it keeps the load on the source low. That leaves CDC for tables without a primary key. If both methods are enabled on the same table, the connector uses change tracking. Databricks provides a utility objects script that enables each method for a set of tables and grants the ingestion user the permissions it needs:
-- Enable CT on transactional schemas
EXEC dbo.lakeflowSetupChangeTracking
@Tables = 'SCHEMAS:Sales,HR',
@User = 'lakeflow_user',
@Retention = '3 DAYS';
-- Enable CDC on specific staging tables without primary keys
EXEC dbo.lakeflowSetupChangeDataCapture
@Tables = 'Staging.ImportData,Logs.AuditTrail',
@User = 'lakeflow_user';Checkpoint 4 of 5· Check yourself
A SQL Server table has both change tracking and CDC enabled. Which mechanism does the Lakeflow Connect connector use for it?
When both methods are on, the connector uses change tracking. Leaving CDC enabled does not make the connector read the full operation history.
“If change tracking and CDC are both enabled, the SQL Server connector uses change tracking.”Source: docs.databricks.com
MySQL captures incremental inserts, updates and deletes from the binary log (binlog). Source setup depends on where MySQL is deployed: Amazon RDS, Aurora MySQL, Azure Database for MySQL, Google Cloud SQL for MySQL, or MySQL on EC2. PostgreSQL has the most hands-on setup. Inside PostgreSQL you enable logical replication, create a replication user, and create publications and replication slots. You need a separate publication in every database you replicate. Logical replication runs only on the primary instance, never on a read replica, so the gateway has to point at the primary.
4.Connections, personas and wiring the gateway to the pipeline
With the source prepared, the next object is the Unity Catalog connection. The documentation describes two personas. A single admin user can configure the source database and then create the connection, gateway and pipeline in one pass of the data ingestion UI. In a multi-user setup, an admin configures the source and creates the connection in Catalog Explorer, and non-admin users then build their own gateway-pipeline pairs on top of it. If you author pipelines through the API, the connection has to be created in Catalog Explorer first. The user creating the pipeline needs USE CONNECTION (or ALL PRIVILEGES) on the connection and USE CATALOG on the target catalog. They also need USE SCHEMA, CREATE TABLE and CREATE VOLUME on an existing schema, or CREATE SCHEMA on the catalog.
In the gateway-based architecture you create two pipeline objects. The gateway is a pipeline of type INGESTION_GATEWAY. It references the connection and names the catalog and schema for its staging storage. The ingestion pipeline is of type MANAGED_INGESTION and refers to the gateway by its pipeline ID:
gateway_pipeline_spec = {
"pipeline_type": "INGESTION_GATEWAY",
"name": <gateway-name>,
"catalog": <destination-catalog>,
"target": <destination-schema>,
"gateway_definition": {
"connection_id": <connection-id>,
"gateway_storage_catalog": <destination-catalog>,
"gateway_storage_schema": <destination-schema>,
"gateway_storage_name": <destination-schema>
}
}
ingestion_pipeline_spec = {
"pipeline_type": "MANAGED_INGESTION",
"name": "<pipeline-name>",
"ingestion_definition": {
"ingestion_gateway_id": <gateway-pipeline-id>,
"source_type": "MYSQL",Checkpoint 5 of 5· Put it in order
Put the multi-user, API-based setup steps in order
- 1.Admin configures the source database for change capture
- 2.Admin creates the Unity Catalog connection in Catalog Explorer
- 3.Non-admin creates the ingestion pipeline that references the gateway ID
- 4.Non-admin creates the ingestion gateway that uses the connection
Source setup comes first. API authoring needs an existing connection. The pipeline is defined by the gateway's ID, so the gateway has to exist before the pipeline.
“Create a gateway, then create an ingestion pipeline that references the gateway ID”Source: docs.databricks.com
Exam traps
Each one states something that sounds right. Open it to see what is actually true.
1.Scheduling the ingestion pipeline infrequently also stops the gateway's compute bill between runs.Why is that wrong?
The gateway runs continuously on classic compute no matter how the pipeline is scheduled, so you pay for it while the pipeline is idle.
Covered in Five moving parts of a database connector
2.Enabling SQL Server CDC alongside change tracking makes the connector switch to the richer CDC feed.Why is that wrong?
When both are enabled, the connector uses change tracking. Databricks recommends change tracking for tables with primary keys and CDC for tables without them.
Covered in Preparing SQL Server, MySQL and PostgreSQL for CDC
3.A PostgreSQL read replica is a safe ingestion source that keeps load off the primary.Why is that wrong?
Logical replication works only on primary instances, so the connector must point at the primary.
Covered in Preparing SQL Server, MySQL and PostgreSQL for CDC
Sources
Every claim above is drawn from one of these pages, quoted as it was written on the date shown.
- 1.
“Data is automatically purged from staging after 30 days.”
↩︎ Five moving parts of a database connector“These are streaming tables, which are Delta tables with extra support for incremental data processing.”
↩︎ Five moving parts of a database connector“Any network path that allows the gateway to reach the database is supported”
↩︎ Sizing and placing the gateway“Size this compute for your workload, because the initial snapshot can fail on undersized compute.”
↩︎ Sizing and placing the gateway“This allows you to run your ingestion pipeline at whatever schedule you'd like, even as the gateway continuously captures changes.”
↩︎ Key concept“Because the ingestion gateway runs continuously, you provision and pay for its classic compute even when your ingestion pipeline is idle.”
↩︎ Exam trap 1“it runs continuously to capture changes before change logs can be truncated in the source.”
↩︎ Prediction“Because the ingestion gateway runs continuously, you provision and pay for its classic compute even when your ingestion pipeline is idle.”
↩︎ Checkpoint - 2.
“Databricks recommends specifying the smallest possible worker nodes for ingestion gateways because they do not impact the gateway performance.”
↩︎ Sizing and placing the gateway“you must create the connection in Catalog Explorer”
↩︎ Connections, personas and wiring the gateway to the pipeline“Logical replication is only supported on primary instances and not on read replicas.”
↩︎ Exam trap 3“The minimum requirement is 8 cores to enable efficient and performant data extraction from your source database.”
↩︎ Checkpoint - 3.
“Databricks recommends using change tracking for any table that has a primary key to minimize the load on the source database.”
↩︎ Preparing SQL Server, MySQL and PostgreSQL for CDC“If change tracking and CDC are both enabled, the SQL Server connector uses change tracking.”
↩︎ Exam trap 2“If change tracking and CDC are both enabled, the SQL Server connector uses change tracking.”
↩︎ Checkpoint - 4.https://docs.databricks.com/aws/en/ingestion/lakeflow-connect/mysql-integrated-pipelineOfficial docs
“captures incremental changes (inserts, updates, and deletes) using the binary log (binlog).”
↩︎ Preparing SQL Server, MySQL and PostgreSQL for CDC - 5.
“enable logical replication, create a replication user, and create publications and replication slots.”
↩︎ Preparing SQL Server, MySQL and PostgreSQL for CDC - 6.
“You must create a separate publication in each PostgreSQL database that you want to replicate.”
↩︎ Preparing SQL Server, MySQL and PostgreSQL for CDC - 7.
“Multi-user: An admin user configures the source database and creates a connection for non-admin users to create gateway-pipeline pairs with.”
↩︎ Connections, personas and wiring the gateway to the pipeline“OAuth M2M | Supported”
↩︎ Connections, personas and wiring the gateway to the pipeline“Create a gateway, then create an ingestion pipeline that references the gateway ID”
↩︎ Checkpoint - 8.
“The MySQL connector is in Public Preview.”
↩︎ Connections, personas and wiring the gateway to the pipeline