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.
Connection
Section titled “Connection”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.
How rows arrive
Section titled “How rows arrive”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 in S3
Section titled “Parquet files in S3”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.
Write strategies
Section titled “Write strategies”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.
Tested live
Section titled “Tested live”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.