Skip to content

Targets

Databricks

Load into Databricks (beta), a SQL warehouse with Unity Catalog: Parquet in S3 copied where it lies through an external location, everything else staged in a volume, then COPY INTO, one statement per write.

This target 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 target loads into a Unity Catalog SQL warehouse with COPY INTO: from Parquet files in S3 where they lie, through an external location, and from Parquet SQLLoad writes to a Unity Catalog volume for everything else. There is no INSERT fallback.

SQLLoad signs in with a service principal’s OAuth machine-to-machine credentials:

[connections.lakehouse]
kind = "databricks"
host = "${ENV:DATABRICKS_HOST}"
client_id = "${ENV:DATABRICKS_CLIENT_ID}"
client_secret = "${ENV:DATABRICKS_CLIENT_SECRET}"
warehouse_id = "${ENV:DATABRICKS_WAREHOUSE_ID}"
catalog = "main"
volume = "main.raw.sqlload_stage" # catalog.schema.volume
[targets.lakehouse]
connection = "lakehouse"
loader_schema = "raw"
Key Meaning
host The workspace URL, such as https://dbc-1234.cloud.databricks.com.
client_id, client_secret The service principal’s OAuth credentials.
warehouse_id The SQL warehouse statements run on.
catalog The Unity Catalog catalog tables load into.
volume An existing volume load files are staged in, as catalog.schema.volume. Anything else fails plan. Only a connection a target loads into needs one; a source doesn’t.

The service principal needs USE CATALOG, USE SCHEMA and CREATE TABLE where it loads (CREATE SCHEMA too when the schema doesn’t exist yet), ownership of or MANAGE on the tables it replaces or rewrites, and READ VOLUME and WRITE VOLUME on the volume. sqlload debug checks that the volume lists.

Each batch is written as Parquet to <volume>/sqlload/<load id>/<batch>/ and loaded with one COPY INTO. The batch’s folder is removed once the COPY commits, and also when it fails. The next load removes folders a failed run left once they are over a day old; a folder a running load is still filling is kept.

Parquet files of a files source in S3 load straight from the bucket, with no row passing through SQLLoad, when a Unity Catalog external location covers their folder. plan lists the external locations the service principal can see, and checks with LIST that it can read a folder holding files to load, which needs READ FILES on the location it reads through:

CREATE EXTERNAL LOCATION lake URL 's3://example-bucket/events/' WITH (STORAGE CREDENTIAL lake_reader);
GRANT READ FILES ON EXTERNAL LOCATION lake TO `<service principal application id>`;
route Databricks COPYs directly from s3://example-bucket/events/ (nothing through the loader)

Each batch is one COPY INTO over its files, and each COPY is one transaction. A batch needs more than one only when it holds over 1,000 files (the most FILES takes, as one partition of a delete_insert on a partition cursor can) or files holding one column as different types, such as a 32-bit integer in one file and a 64-bit one in another, which Databricks won’t merge in one COPY. Every strategy but append lands its batches in a staging or build table, so a batch publishes whole. An append copies straight into the table and commits each COPY; when a later one fails, or the rows Databricks reports inserting differ from what the files’ footers hold, it deletes the rows this load wrote from the batch’s files before the load fails. The columns SQLLoad adds (_sqlload_source_file, _sqlload_source_version, _sqlload_source_row, the load id and time) and partition values from the path are filled in the same statement.

Parquet in S3 that no external location covers fails plan, naming the folder and the fix; such files never go through SQLLoad instead. Files go through SQLLoad, staged in the volume as below, when Databricks can’t load them exactly by reading them itself, and plan says why, such as for CSV and JSON files, a table that masks columns, has checks or flattens objects, a column held in a way a cast wouldn’t keep exactly (nanoseconds, a type other than the one it loads as), a time, a UUID or a list, and files on an S3-compatible endpoint (set on the connection or in AWS_ENDPOINT or AWS_ENDPOINT_URL), which no external location reaches. Files in a local folder or over SFTP always go through SQLLoad. copy_to_internal_stage is for Snowflake and is refused here.

A Snowflake source with a stage url in S3 that an external location covers loads the same way: Databricks COPYs the files Snowflake unloads, into a staging or build table even for append, and the batch publishes only when it loads as many rows as Snowflake reported unloading. A url no external location covers fails plan. See Databricks and ClickHouse read the files themselves.

Databricks’ Statement Execution API runs every statement in its own transaction, so each write is one statement. The run record commits after it.

Strategy Statements
append COPY INTO the table. A logged table goes through a staging table, then one INSERT.
merge COPY INTO a staging table, then one MERGE on the key.
delete_insert COPY INTO a staging table, then one MERGE that inserts the batch and deletes the rows it replaces.
replace COPY INTO a build table, then CREATE OR REPLACE TABLE ... AS SELECT from it.
postgres_cdc One MERGE per table (a truncate in the batch is its WHEN NOT MATCHED BY SOURCE clause), or one INSERT for a changelog. See Postgres CDC.
deletes, deletes_from A key scan is one UPDATE (soft) or DELETE (hard) of the rows whose key the source no longer lists. Tombstones are one MERGE (soft) or DELETE (hard). See Rows deleted at the source.

replace keeps the table’s history and grants. Delta has no metadata-only swap, so the rows are copied once more by the CREATE OR REPLACE; that extra copy is the expected cost of replace on Databricks.

Type Databricks
bool BOOLEAN
smallint SMALLINT
int BIGINT
decimal(p,s) DECIMAL(p,s)
float DOUBLE
text STRING
varchar(n) STRING ¹
date DATE
time STRING ¹
timestamp_tz TIMESTAMP ¹
timestamp_ntz TIMESTAMP_NTZ
json VARIANT
uuid STRING ¹
binary BINARY

¹ Held with a loss, which plan notes: the length limit isn’t enforced, times and UUIDs are stored as text, and TIMESTAMP keeps the instant but not the offset.

Added columns use one ADD COLUMNS. Widening a type uses Delta’s type widening where it applies, and rewrites the table where it doesn’t, such as an integer becoming a float.

Every pathway plan accepts was run against a real Databricks SQL warehouse (2X-Small) before Databricks was marked supported. The same project ran five rounds: a first load, a re-run, a re-run with an exclusive cursor, source and schema changes, then the contract commands, with row counts and values checked against each source after every round.

Source append replace delete_insert merge
Postgres live live live live
Local files live live live refused at plan
Files in S3 live live live refused at plan
Python rows live live live live
Python Arrow data live live live live
sl.http (paginated JSON) live live live live
ODBC (SQL Server, MySQL) not run live not run live
BigQuery not run live not run live
Files on an SFTP server (gzip CSV, Parquet) live not run not run refused at plan

Parquet in S3 copied through an external location was also run live with append, replace and delete_insert (a staging table), including files mixing 32-bit and 64-bit integers, each table compared row for row, lineage columns included, with the same files loaded through SQLLoad. A folder under an external location the service principal can’t read failed plan with the grant to add.

merge is refused for files sources on every target. The live rounds also covered incremental loads on a cursor, rows with a NULL cursor under delete_insert, an added column, widening a decimal in place, contract diff, contract diff --source and contract generate --write, and a load that failed mid-way, which committed no rows and left the volume’s sqlload/ folder empty.

A later round loaded SQL Server and MySQL through ODBC and a BigQuery project under merge and replace, and gzip CSV and Parquet files from an SFTP server under append, into one schema. It ran twice, a first load and then source changes (updated and new rows, new files), with row counts, keys and sums checked against each source after both.