Skip to main content

Ingest data from databases and SaaS applications

Bring data from your operational databases and software as a service (SaaS) applications into Unity Catalog tables with Lakeflow Connect managed connectors. Each managed connector creates an ingestion pipeline, and Unity Catalog governs it. Ingestion pipelines run on serverless compute by default and ingest data incrementally to keep your tables up to date. Standard change data capture (CDC) database connectors also use an ingestion gateway, which runs on classic compute.

Every managed connector uses a connection and an ingestion pipeline. The connection is a Unity Catalog securable object that stores the authentication details for the source, so that users with the USE CONNECTION privilege can build pipelines without access to the source credentials. The ingestion pipeline uses the connection to move data into destination tables.

How managed connectors work​

The components of a managed connector depend on the connector type.

SaaS, file, query-based, and streaming connectors​

These connectors use a connection, an ingestion pipeline, and destination tables. The ingestion pipeline reads directly from the source and runs on serverless compute by default. For SaaS connectors, see Managed SaaS connectors.

CDC database connectors​

In the standard architecture, database connectors use CDC to ingest changes from relational databases such as SQL Server, MySQL, and PostgreSQL, and include two additional components:

  • Ingestion gateway: Extracts snapshots, change logs, and metadata from the source database. The gateway runs continuously on classic compute in your workspace's virtual private cloud (VPC) or virtual network (VNet), so it must be able to reach the source database over the network. You pay for the gateway's compute even when the ingestion pipeline is idle.
  • Staging storage: A Unity Catalog volume that stores the extracted data until the ingestion pipeline applies it to the destination tables. Databricks purges staged data after 30 days.

The ingestion pipeline moves the data from staging storage into the destination tables, and runs on serverless compute. Because the gateway captures changes continuously, you can run the ingestion pipeline on any schedule.

Integrated CDC pipelines for MySQL, Oracle, and SQL Server run extraction and application in one pipeline, so they don't use a separate gateway. For SQL Server and Oracle, your account team must enable integrated CDC for the workspace.

For the network paths that the gateway supports, such as VPC or VNet peering, virtual private network (VPN), and private connectivity, see Managed database connectors.

Choose CDC or query-based ingestion​

For a database source, choose CDC when the source supports it and you need fresh data. Choose query-based ingestion when the source doesn't support CDC, or when the extra load on the source is acceptable for the latency you need.

The following table compares the two connector types:

Feature

Query-based connectors

CDC database connectors

Gateway

No ingestion gateway

An ingestion gateway for the standard CDC architecture

Staging

No staging volume

Captured changes staged in a Unity Catalog volume

Ingestion timing

Scheduled source queries

Continuous source change capture, with changes applied on the ingestion pipeline schedule

Change history

Latest row state at query time, without intermediate states between runs

Intermediate changes recorded in the source change log

Source compatibility

A broader set of database sources, including sources without CDC support

A supported CDC source with source-side CDC configuration

Source impact

Direct source-table queries, with added source load and potentially slower queries

Source change-log reads instead of repeated source-table queries

Feature

Query-based connectors

CDC database connectors

Gateway

No ingestion gateway

An ingestion gateway for the standard CDC architecture

Staging

No staging volume

Captured changes staged in a Unity Catalog volume

Ingestion timing

Scheduled source queries

Continuous source change capture, with changes applied on the ingestion pipeline schedule

Change history

Latest row state at query time, without intermediate states between runs

Intermediate changes recorded in the source change log

Source compatibility

A broader set of database sources, including sources without CDC support

A supported CDC source with source-side CDC configuration

Source impact

Direct source-table queries, with added source load and potentially slower queries

Source change-log reads instead of repeated source-table queries

CDC connectors are available for MySQL, PostgreSQL, SQL Server, and Oracle. Query-based connectors support Oracle, Teradata, SQL Server, MySQL, MariaDB, and PostgreSQL directly. Foreign catalog ingestion supports every Lakehouse Federation data source. See Managed database connectors and Query-based connectors.

Video walkthroughs​

The following video demonstrates ingestion from databases with Lakeflow Connect:

The following video demonstrates ingestion from Salesforce with Lakeflow Connect:

Deploy pipelines as code​

You can create managed ingestion pipelines in the UI, or define them in Declarative Automation Bundles so that you version control each pipeline and deploy it the same way to development, staging, and production. See Deploy a SQL Server ingestion pipeline as a bundle.

Next steps​