Query-based connector limitations
This page lists limitations and considerations for query-based connectors in Databricks Lakeflow Connect, including source table requirements, partitioning and indexing recommendations, and features that are available only through the API.
General limitations
The following limitations apply to all query-based connectors, regardless of source.
- Query-based connectors query the source on a schedule. They don't provide continuous ingestion. If you need lower latency, use a managed CDC database connector.
- The cursor column must be a single column. Composite cursors (multiple columns combined) aren't supported. The column value must be monotonically increasing. Rows with cursor values at or below the stored high-water mark aren't reingested.
- Rows with a NULL cursor column are not ingested.
- Views aren't supported. The connector can ingest a view from some sources, but features such as column selection aren't available for views.
Source table requirements and recommendations
Consider the following source table characteristics when you design a query-based ingestion workload. They affect how a table is ingested and how well the workload performs.
Primary key, unique key, and cursor
Query-based connectors use a primary key (PK) or unique key (UK) to identify rows and a cursor column to detect changes. The ingestion behavior depends on which of these are available on the source table:
If you specify the primary key manually, ensure it consists of unique, non-nullable columns. If the history tracking setting, known as slowly changing dimensions (SCD), is set to SCD type 1 or SCD type 2, the connector uses the primary key as the merge key. Duplicate or null key values can result in silent data loss. APPEND_ONLY mode is not affected because it does not perform a merge.
Source table | Ingestion behavior |
|---|---|
PK or UK with a cursor | Regular snapshot and incremental ingestion. |
PK or UK without a cursor | Batch snapshot mode ( |
No PK or UK, cursor available | The connector generates a synthetic key from all columns. This works for tables with no duplicate rows. For tables with fully duplicate rows, use |
No PK or UK, no cursor | The connector generates a synthetic key from all columns. Use |
Partitioning and indexing recommendations
Follow these recommendations so the connector can parallelize reads and run incremental queries efficiently.
-
The connector automatically selects a column to partition the source query for parallel reads. It first tries the leading primary key column, provided it's a numeric, string, date, or timestamp type. If that column isn't suitable, it selects a unique key column instead. For balanced partitioning, the column should have:
- High cardinality: At least 1,000 distinct values is recommended for tables approaching 1 TB.
- Low data skew: Avoid columns whose values cluster heavily around a few values. Skewed columns create oversized partitions that can cause query timeouts and lose progress when a run restarts.
If the automatically selected column doesn't meet these criteria, contact your Databricks account team to request a partitioning column override.
-
Index the cursor column on the source database with a B-tree index. During the incremental phase, the connector filters on the cursor column (
cursor_column > last_value). Without a B-tree index on the cursor column, each incremental read scans the full table, which degrades performance as the table grows.
API-only features
The following features are supported for query-based connectors, but only using the API:
- Row filtering
- Soft-deletion tracking (
deletion_condition) - Hard-deletion tracking (Beta)
- Append-only ingestion (
APPEND_ONLYSCD mode) - Multi-destination catalog and schema