Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Use a query-based connector to ingest rows from a database on a schedule. Each run queries the source for rows that changed since the last run, using a cursor column that you choose, and writes them to a Unity Catalog streaming table. This page uses the SQL Server connector as an example. Other supported databases follow the same workflow, but their connection and source setup differ, so follow the source documentation for your database.
How it works
A query-based connector runs a source query on the schedule you set. After each successful run, it stores the highest cursor value that it saw. The next run requests rows above that high-water mark and writes them to the destination table.
For slowly changing dimension (SCD) type 1 and SCD type 2 with a timestamp cursor, each run also re-reads a short window of rows immediately below the high-water mark. This catches rows that a long-running source transaction commits after the mark has advanced. Because these modes merge by primary key, re-read rows that haven't changed don't create duplicates or extra versions.
There's no gateway and no staging volume, so the setup is shorter than for a change data capture (CDC) connector. The connector finds changes only through the cursor column. If the cursor value doesn't change when a row changes, the connector can't find the update.
Requirements
You need the following:
- A Unity Catalog-enabled workspace and a source database that serverless compute can reach. See Serverless network connectivity.
CREATE CONNECTIONon the metastore to create a connection, orUSE CONNECTIONon an existing connection.CREATEandUSE SCHEMAprivileges on the destination catalog and schema.- A single cursor column per table. Its values must increase monotonically, and they must change whenever the row changes. A timestamp column works best. Integer IDs work for append-only sources.
- For SCD type 1 or SCD type 2, a primary key (or unique key) whose columns are unique and non-null. The connector uses it as the merge key. Duplicate or null key values can cause silent data loss.
Create the query-based pipeline
The Azure Databricks UI creates query-based pipelines that run on serverless compute.
Open Data Ingestion
In the sidebar of the Azure Databricks workspace, click Data Ingestion. On the Add data page, under Databricks connectors, click your source. This example uses SQL Server.
Configure the ingestion pipeline
On the Ingestion setup page, enter a name for the pipeline and select a Destination catalog. Select the Unity Catalog connection that stores the source credentials. If none exists and you have CREATE CONNECTION on the metastore, click Create connection first. Click Create pipeline and continue.
Select tables and cursor columns
On the Source page, select the schemas and tables to ingest. For each table, select a cursor column with values that increase monotonically. Without one, the connector runs a full load on every run. Optionally, change the History tracking setting, and then click Next.
Select the destination
On the Destination page, select the catalog and schema to write to, and then click Save and continue. To write to a new schema, click Create schema, which requires USE CATALOG and CREATE SCHEMA on the parent catalog.
Set a schedule and run the pipeline
On the Schedules and notifications page, optionally click Create schedule and set how often to refresh. Optionally, set email notifications for success or failure. Click Save and run pipeline.
Verify the pipeline
Open the pipeline in Jobs & Pipelines. Confirm that the run completed and that the destination tables contain the rows that you expect. Then change a row in the source table, run the pipeline again, and confirm that the change appears. If it doesn't, check the cursor column first. See Troubleshooting.
Troubleshooting
Most query-based problems come from the cursor column or from the source load. Check these entries before you change the pipeline settings.
Updates never arrive
Check whether the cursor column changes when a source row changes. A creation timestamp or an auto-incrementing ID doesn't change when an existing row is updated, so the connector misses those updates. Choose a column that changes whenever a row changes.
Changing the cursor column requires a full refresh.
Rows with a NULL cursor value are missing
The connector doesn't ingest rows whose cursor column is NULL. Fix the source values, or choose a cursor column that every row populates.
The newest rows at the maximum cursor value are delayed
The connector tracks timestamp cursors with microsecond precision, which is six fractional-second digits. If the source column stores more digits, such as a SQL Server datetime2(7) column, the connector doesn't ingest rows at the maximum cursor value with a non-zero seventh digit until the source receives a newer write. Use a cursor column with at most six fractional-second digits, or a numeric cursor column. See General limitations.
The pipeline can't reach the source database
Query-based pipelines run on serverless compute, so serverless compute must reach the source database over the network. For a source that's reachable only over a private network, see Serverless network connectivity.
The source database is under unexpected load
Every run queries the source tables directly. Check the schedule, and add a B-tree index on the cursor column in the source database. Without that index, each incremental query scans the whole table. If the frequent queries put too much load on the source, or you need lower latency, use a CDC connector instead. See Ingest database changes with change data capture.
Serverless network connectivity
Serverless compute reaches the source through serverless network connectivity. If the source has a public endpoint, allow the outbound IP addresses that Databricks publishes for serverless compute on the source's firewall. For a private source, create a network connectivity configuration (NCC) with a private endpoint rule for the source. For the setup steps, see Serverless compute plane networking. For how query-based connections reach sources, see Networking recommendations for Lakehouse Federation.
From serverless compute, the SQL Server connector supports Private Link. See Networking recommendations for Lakehouse Federation.
Next steps
| Resource | Description |
|---|---|
| Create a query-based ingestion pipeline | Create a query-based ingestion pipeline in the Azure Databricks UI or as a bundle |
| Query-based connectors | Learn about query-based connectors, supported sources, and SCD modes in Lakeflow Connect |