Skip to content

Sources

Databricks

Load Databricks (beta) tables, views and query results as Arrow through the Statement Execution API: connections, choosing tables, query tables, cursors, types, cost and permissions.

This source is in beta: it is tested, but gets less everyday use than the other connectors, so expect rough edges, and please report them in GitHub issues.

A databricks source loads tables, views and query results from a Databricks SQL warehouse with Unity Catalog. SQLLoad runs one SELECT per read on the warehouse and downloads its result as Arrow files, several at once, through the short-lived links the Statement Execution API gives. Nothing is written to a volume or a bucket, and the source only needs read access.

[connections.dbx]
kind = "databricks"
host = "https://dbc-1234.cloud.databricks.com"
client_id = "${ENV:DATABRICKS_CLIENT_ID}"
client_secret = "${ENV:DATABRICKS_CLIENT_SECRET}"
warehouse_id = "${ENV:DATABRICKS_WAREHOUSE_ID}"
catalog = "main"
[sources.marts]
kind = "databricks"
connection = "dbx"
description = "Tables of the marts schema."
include = ["marts.*"]
exclude = ["marts.tmp_*"]
write_strategy = "replace"
[sources.marts.tables."marts.events"]
description = "Events, one row per event."
cursor_column = "event_at"
write_strategy = "append"
[sources.marts.tables.recent]
description = "Orders of the last week, with their customer."
sql = "SELECT o.*, c.name FROM main.marts.orders o JOIN main.marts.customers c USING (customer_id)"
cursor_column = "ordered_at"
write_strategy = "append"

A source uses a databricks connection, the same kind a Databricks target uses. Its statements run on the connection’s warehouse_id, and its tables are read from the connection’s catalog. A connection that only a source uses needs no volume; a connection a target loads into still does.

include and exclude are schema.table patterns in the connection’s catalog, such as marts.*. Unity Catalog stores names in lower case, so write the patterns in lower case. SQLLoad lists the tables, views and their columns from the catalog’s information_schema, in three queries however many tables match. A [sources.marts.tables."schema.table"] entry sets options for one table; on its own, it doesn’t add the table.

A source needs at least one include pattern or one query table. A merge table merges on the primary key the table declares with unique_key = "{primary_key}", or on the columns you name. Unity Catalog doesn’t enforce primary keys. A table’s comment becomes the table’s description.

Each read is one SELECT of the read columns, with the cursor window and any where in its WHERE, so the warehouse sends only the rows in it. One statement sees one version of each table, so a read is consistent on its own; different reads of one run, such as batch_size windows or different tables, can see the data moments apart.

The result comes back as Arrow files behind links that expire within 15 minutes. SQLLoad downloads up to eight at a time, without the Databricks token, and holds at most a few batches ahead of the target. A download that fails is tried again through a fresh link, up to three times. A result the warehouse cuts short at its byte limit fails the read rather than loading fewer rows.

A table entry with sql is a query table: its rows are the query’s result, and the entry’s key is its name. At plan, SQLLoad runs the query with LIMIT 0 to learn its columns, which reads no rows.

cursor_column names a column of the query’s result. SQLLoad wraps the query and filters on the cursor outside it, so the query needs no placeholder. Write the query in Databricks SQL, naming tables as catalog.schema.table.

where narrows a table to the rows that match a condition, written in Databricks SQL:

[sources.marts.tables."marts.orders"]
where = "region = 'EU' AND status != 'void'"

It works as for the other database sources: SQLLoad ANDs it into every SELECT it reads the table with, plan checks it by counting the rows it selects, and a query table refuses it (put the condition in its sql). See BigQuery: Filtering rows for the details they share.

Databricks type Loads as
TINYINT, SMALLINT smallint
INT, BIGINT int
DECIMAL(p,s), p up to 38 decimal(p,s); a DECIMAL with no precision is decimal(10,0)
FLOAT, DOUBLE float
BOOLEAN bool
STRING text
VARCHAR(n), CHAR(n) varchar(n)
BINARY binary
DATE date
TIMESTAMP timestamp_tz, in UTC
TIMESTAMP_NTZ timestamp_ntz
TIMESTAMP(p), TIMESTAMP_NTZ(p), p over 6 none: microseconds are the most a timestamp keeps, so pin text, or exclude the column
ARRAY, MAP, STRUCT, VARIANT json
INTERVAL and others none: pin text, or exclude the column

Pin a type to load a column another way, such as columns = { total = "decimal(18,2)" }. A value that doesn’t convert to the pinned type fails the read, naming the column, rather than loading as NULL. An array(T) pin isn’t available: arrays load as json. See Types.

A STRING with a collation other than UTF8_BINARY, such as UTF8_LCASE, loads as text. As a text cursor it is compared in byte order (COLLATE UTF8_BINARY), the order the destination’s maximum is found in, so a case-insensitive collation doesn’t skip rows.

Reads run on the connection’s SQL warehouse and bill its compute while they run; a stopped warehouse starts for the first read. Listing tables and describing query tables run small queries on it too. A table read from a nullable cursor column with null_cursor = "fail" (the default) first counts its rows with a NULL cursor. See NULL cursor values.

The service principal needs CAN USE on the SQL warehouse, USE CATALOG on the catalog, USE SCHEMA on the schemas it reads, and SELECT on the tables and views. It needs no volume or storage access: the result links carry their own short-lived credential. A workspace that has external links turned off can’t be read this way.

A local fake of the Statement Execution API covers discovery from information_schema, result files in several chunks, an expired link fetched again, cursor reads of only new rows, and that no download carries the Databricks token.

A live round on a real serverless SQL warehouse loaded tables, a view and a sql query table read in daily cursor windows into DuckDB, Parquet files, Postgres and ClickHouse, twice: a first load, then updated and new rows merged by primary key. Row counts and sums matched on every target, as did a table of every shape: STRUCT, ARRAY, MAP, BINARY, DECIMAL(38,10), TIMESTAMP_NTZ, NULLs and dates from year 1 to 9999.

Reading a table through the result links took about 1 s for 100,000 rows and 10 s for 10 million rows (1.2 GB of Arrow). Unloading the same table to Parquet in a volume first is no faster below a million rows and about 3 s faster at ten million, so SQLLoad always reads through the result links.