faucet-sink-databricks
Databricks SQL warehouse sink for the
faucet-stream ecosystem. Loads pages
into a Delta table through a SQL warehouse's
Statement Execution API
(plain REST — no JDBC/ODBC driver), under the warehouse's Unity Catalog
permissions. Completes the pair with
faucet-source-databricks;
both share connection and auth types through
faucet-common-databricks.
For landing files in object storage without a warehouse, use
faucet-sink-delta.
Highlights
- Two load paths — a multi-row
INSERT … SELECT … FROM VALUESfor small pages, or a staged Parquet file loaded withCOPY INTOfor large ones. Staging goes to a Unity Catalog volume (uploaded through the Files API with the sink's own token) or to S3 / GCS / ADLS behind thestagingfeature. - One typing rule on both paths — every value travels as a string cell and
is cast to the column's declared type on the server (
CAST(… AS <type>),from_json(…, map('mode', 'FAILFAST'))forSTRUCT/ARRAY/MAP— a value that does not fit fails the statement instead of being nulled field by field; Spark ignores keys the type has no field for in every mode, so the sink checks nested keys against the declared type first and fails the page naming any it would drop (payload.b) — andparse_jsonover the value's JSON text forVARIANT, so a JSON string stays a string;to_binary(…, 'BASE64')forBINARY, decoding the base64 text binary values travel as and failing on anything else), so the insert and staged paths can never land a value differently. - Write modes —
append,upsert/deleteviaMERGE(withdelete_marker), andoverwritevia a staging table and an atomic swap. - Exactly-once — a
_faucet_commit_tokenwatermark plus a data write that is idempotent per page (see below). - Schema drift —
current_schemafrominformation_schema.columns;evolveadds columns and widens where Delta allows. Tables are created from the first page whencreate_table: true. - Warm-up tolerant — submits refused with
429/503while a serverless warehouse starts are retried with backoff; long statements are polled up tostatement_timeout_secsand then cancelled; Delta concurrent-write conflicts are retried.
Configuration
| Field | Type | Default | Notes |
|---|---|---|---|
workspace_url |
string | — (required) | https://<host>.cloud.databricks.com |
warehouse_id |
string | — (required) | target SQL warehouse id |
auth |
{ type, config } / { ref } |
— (required) | pat or token bearer, or a shared provider (OAuth M2M — see below) |
catalog |
string? | warehouse default | Unity Catalog catalog |
schema |
string | — (required) | target schema (must exist) |
table |
string | — (required) | target table |
create_table |
bool | true |
create the table from the first page's inferred schema |
load_method |
auto | insert | copy_into |
auto |
auto stages when staging is set and the page is ≥ copy_threshold_bytes |
staging.location |
string | — | /Volumes/<catalog>/<schema>/<volume>[/prefix], s3://…, gs://…, abfss://<container>@<account>.dfs.core.windows.net/… |
staging.cleanup |
always | on_success | never |
always |
when staged files are deleted (best-effort, logged) |
staging.copy_options |
string? | — | extra COPY_OPTIONS entries, verbatim |
copy_threshold_bytes |
int | 1048576 |
auto staging threshold |
batch_size |
int | 1000 |
rows per INSERT / MERGE statement on the insert path; 0 = bounded by bytes only |
max_statement_bytes |
int | 8388608 |
statement-text bound on the insert path (API limit 16 MiB) |
wait_timeout_secs |
int | 50 |
server wait before async (0 or 5–50) |
poll_interval_ms |
int | 1000 |
poll cadence while queued / running; must be > 0 |
statement_timeout_secs |
int | 3600 |
client deadline per statement, then cancel (0 = none) |
max_retries |
int | 5 |
429/503 and Delta conflict retries |
retry_backoff_ms |
int | 1000 |
exponential backoff base |
write_mode |
append | upsert | delete | overwrite |
append |
|
key |
[string] |
[] |
required for upsert / delete |
delete_marker |
{ field, values } |
— | upsert only: matching rows become deletes |
pipeline:
sink:
type: databricks
config:
workspace_url: https://dbc-xxxx.cloud.databricks.com
warehouse_id: 0123456789abcdef
catalog: main
schema: sales
table: orders
auth:
staging:
location: /Volumes/main/sales/faucet_stage
write_mode: upsert
key:
Columns in a record that the table does not have are an error (never silently
dropped); add a schema: { on_drift: evolve } (or ignore) policy to handle
them.
OAuth M2M (service principal)
Configure a client-credentials provider in the top-level auth: catalog and
reference it — the token is refreshed before expiry and shared by every
connector that references it:
auth:
databricks_sp:
type: oauth2
config:
token_url: https://dbc-xxxx.cloud.databricks.com/oidc/v1/token
client_id: "${env:DATABRICKS_CLIENT_ID}"
client_secret: "${env:DATABRICKS_CLIENT_SECRET}"
scopes:
# …then in the sink config: auth: { ref: databricks_sp }
Load paths
| Path | Append | Upsert / delete | Exactly-once append |
|---|---|---|---|
| insert | INSERT INTO t (cols) SELECT <casts> FROM VALUES … (split by batch_size / max_statement_bytes) |
one MERGE per chunk |
first chunk INSERT INTO t REPLACE WHERE …, later chunks INSERT |
| staged | COPY INTO t FROM (SELECT <casts> FROM '<dir>') FILEFORMAT = PARQUET FILES = ('<file>') |
MERGE … USING (SELECT <casts> FROM read_files('<file>')) |
INSERT INTO t REPLACE WHERE … SELECT … FROM read_files('<file>') |
Staged files are all-STRING Parquet (typing happens in the SELECT). File
names:
- exactly-once pages —
_faucet/<table>/<scope-hash>/<seq>.parquet, derived from the page token only, so every attempt of a page writes and reads the same file; - other pages —
_faucet/<table>/<run>/part-<n>-<content-hash>.parquet.COPY INTOrecords every file it loaded into a table and skips it on a repeat, so re-submitting the load after an ambiguous failure cannot load it twice, while two identical pages still land as two files.
The warehouse must be able to read the staging location: a volume it has
READ VOLUME on, or a cloud path covered by a Unity Catalog external location.
Cloud uploads use ambient credentials (the object_store default chains) and
need the crate's staging feature.
Write modes
- append — the load paths above.
- upsert / delete — one
MERGE INTO t USING (…) AS s ON t.k = s.kper statement:WHEN MATCHED AND op = 'd' THEN DELETE,WHEN MATCHED THEN UPDATE,WHEN NOT MATCHED AND op = 'u' THEN INSERT. Rows are deduplicated per page (last write wins) before theMERGE, so it never sees two source rows for one key. Rows missing a key fail the batch, or go to the DLQ per row viawrite_batch_partial. Each row carries a flag per non-key column saying whether its record had that column: a matched row updates only the columns its record carried, and an inserted row takes the column'sDEFAULT(elseNULL) for one it did not, so an upsert never overwrites a stored value withNULL. The exactly-once append path likewise fills a column the page lacks with itsDEFAULT. - overwrite —
begindrops any leftover staging and creates itLIKEthe target. Under the CLI the staging is<table>__faucet_ovw_<token>, with a run token shared by the run's begin, writer and commit instances, so a concurrent overwrite of the same table neither drops nor shares it; a tokenedbeginalso drops the untokened<table>__faucet_ovw, and another run's staging only once its token is more than 7 days old (an abandoned crash). Without a token the staging is<table>__faucet_ovw, and thefaucet status --probecheck also reports any run's tokened staging (frominformation_schema.tables); every page lands in it;commitruns oneINSERT OVERWRITE TABLE t SELECT * FROM <staging>(with an explicit column list leaving outGENERATED ALWAYSidentity / generated columns, which the target computes) (a single Delta commit — readers see the old or the new contents, never a mix) and drops the staging table;abortdrops it. On a first run (no target yet,create_table: true) the first page creates the staging table andcommitrenames it into place. Every step derives state from the warehouse, not the sink instance.
Exactly-once delivery
Databricks SQL has no multi-table transaction, so the data and the watermark cannot commit in one statement. The sink makes the data write idempotent per page token instead, then advances the watermark:
- append — the table carries two bookkeeping columns,
_faucet_scope(the pipeline state key) and_faucet_seq(the page sequence), added automatically (ALTER TABLE ADD COLUMNS) the first time exactly-once runs. The page is written with one atomic Delta commit,INSERT INTO t REPLACE WHERE _faucet_scope = '<scope>' AND _faucet_seq >= <seq> …, which removes whatever an earlier attempt of this page (or any later one) left under the scope and inserts the page. An empty page runs the matchingDELETE. - upsert / delete — the
MERGEis keyed, so re-applying it converges. - The watermark row
(scope, token)is upserted into<catalog>.<schema>._faucet_commit_tokenwith aMERGE.
A crash between steps 1–2 and step 3 leaves the watermark one page behind; on
resume the pipeline re-sends that page under the same sequence and step 1
replaces the earlier attempt's rows — regardless of whether the replayed page
has the same boundaries. The guarantee is effectively-once per scope; the
bookkeeping columns are visible in the table (they are hidden from
current_schema). write_mode: overwrite cannot be combined with
exactly-once. REPLACE WHERE takes no column list, so every table column is
projected (missing ones as typed NULL); tables with generated or identity
columns are not supported on the exactly-once append path.
Schema drift
current_schema() reads information_schema.columns (full_data_type,
is_nullable). evolve_schema is idempotent (existing columns are skipped):
| Change | DDL |
|---|---|
| new column | ALTER TABLE t ADD COLUMNS (…) (BIGINT / DOUBLE / BOOLEAN / STRING) |
tinyint/smallint/int/float → number |
SET TBLPROPERTIES ('delta.enableTypeWidening' = 'true') + ALTER COLUMN … TYPE DOUBLE |
| gained nullability | ALTER COLUMN … DROP NOT NULL |
Delta cannot widen BIGINT (the type auto-created integer columns get) to
DOUBLE in place, so that change fails evolve with an explanatory error —
cast the field upstream (e.g. a cast transform) or migrate the table.
Auto-created tables use BIGINT / DOUBLE / BOOLEAN / STRING (nested values
as JSON text) with every column nullable.
Preflight (faucet doctor)
check() is read-only: a SELECT 1 on the warehouse, then a column listing of
the target (Skip when it is missing and will be created).
Features
| Feature | Enables |
|---|---|
staging |
S3 / GCS / ADLS staging locations via object_store (volume staging needs no feature) |
Capabilities
Declares write modes append / upsert / delete / overwrite, idempotent writes (exactly-once), keyed upsert, staged load and schema evolution (schema_report: reported); a write_mode: upsert|delete config with a key dedups by key. faucet list --json prints these declared capabilities.
Timeouts, cancellation and retries
Every request — statement submit and polls, Files API uploads, cloud staging
uploads — runs through the pipeline's CallContext (#880): any caller deadline
ends it, and each is metered as a round trip (submit, poll, files). The
HTTP client's connect / idle-read timeouts, statement_timeout_secs, the
429 / 503 retry and the concurrent-commit re-submit (max_retries,
retry_backoff_ms) are unchanged; their pauses wait through the same context.
How it is built
The sink runs on the family-neutral sink driver (RFC 0014): DatabricksDriver
implements faucet_core::driver::SinkDriver (the inline / staged appends and
the presence-flag MERGE) with its lifecycle groups — StagingDriver (the
CREATE TABLE … LIKE staging table, INSERT OVERWRITE swap and first-run
rename), CommitTokenDriver (the idempotent REPLACE WHERE page write and the
_faucet_commit_token table), SchemaDriver (describe and evolve) and a
RollbackDriver that only rewinds the watermark (rollback itself stays off).
DatabricksSink wraps it in a DriverSink, which drives those lifecycles and
derives the capabilities from the groups present. It does not use the SQL
family driver (faucet_common_sql::SqlSinkDriver): the Statement Execution
API has no client-held transaction to open and commit, rows travel as literal
VALUES or staged Parquet, and exactly-once rests on idempotent page writes
instead of rows and token in one transaction. The public type and config are
unchanged; faucet status --probe now reports whether the overwrite staging
table exists.
Shared SQL layer
This sink takes its identifier quoting, literal escaping and column types from the Databricks dialect of
faucet-common-sql (RFC 0013, #884), the same
place every SQL sink gets them. Rows travel inline (FROM VALUES of literals with per-column casts) or from a staged file, upserts use a presence-flag MERGE over nested types, and exactly-once uses idempotent page writes — none of which is the engine's bind-per-cell model.
Declared column types
When the run's source declares its column types (a SQL query source, #955 — see Declared column types), a table this sink creates types each declared column from the declaration instead of from the first page: BIGINT, DOUBLE, BOOLEAN, DECIMAL(p,s) (up to 38 digits), DATE, TIMESTAMP / TIMESTAMP_NTZ, BINARY; the insert casts each value to its column type. A declared column whose first-page values do not fit its type keeps the inferred type; an existing table is never retyped. With a complete declaration a run that writes nothing creates the empty, typed table.
Caller configs under faucet serve
The staging upload authenticates with staging.credentials (default: the store's default chain, i.e. the server's own identity). A config submitted to faucet serve by a caller may use the server's identity only when the server runs with --allow-host-credentials (never for a tenant), so a caller names access_key (s3://), service_account_json_file / service_account_json_inline (gs://), or account_key / sas_token (az://). A /Volumes/... location uploads with the sink's own credentials and takes no staging.credentials. The same check runs when the upload store is built.