faucet-sink-databricks 1.2.2

Databricks SQL warehouse sink (COPY INTO, MERGE upsert, overwrite, exactly-once) for the faucet-stream ecosystem
Documentation

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 VALUES for small pages, or a staged Parquet file loaded with COPY INTO for 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 the staging feature.
  • 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')) for STRUCT/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) — and parse_json over the value's JSON text for VARIANT, so a JSON string stays a string; to_binary(…, 'BASE64') for BINARY, 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 / delete via MERGE (with delete_marker), and overwrite via a staging table and an atomic swap.
  • Exactly-once — a _faucet_commit_token watermark plus a data write that is idempotent per page (see below).
  • Schema drift — current_schema from information_schema.columns; evolve adds columns and widens where Delta allows. Tables are created from the first page when create_table: true.
  • Warm-up tolerant — submits refused with 429/503 while a serverless warehouse starts are retried with backoff; long statements are polled up to statement_timeout_secs and 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: { type: pat, config: { token: "${env:DATABRICKS_TOKEN}" } }
      staging:
        location: /Volumes/main/sales/faucet_stage
      write_mode: upsert
      key: [order_id]

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: [all-apis]
# …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 INTO records 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.k per 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 the MERGE, so it never sees two source rows for one key. Rows missing a key fail the batch, or go to the DLQ per row via write_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's DEFAULT (else NULL) for one it did not, so an upsert never overwrites a stored value with NULL. The exactly-once append path likewise fills a column the page lacks with its DEFAULT.
  • overwrite — begin drops any leftover staging and creates it LIKE the 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 tokened begin also 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 the faucet status --probe check also reports any run's tokened staging (from information_schema.tables); every page lands in it; commit runs one INSERT OVERWRITE TABLE t SELECT * FROM <staging> (with an explicit column list leaving out GENERATED ALWAYS identity / 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; abort drops it. On a first run (no target yet, create_table: true) the first page creates the staging table and commit renames 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:

  1. 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 matching DELETE.
  2. upsert / delete — the MERGE is keyed, so re-applying it converges.
  3. The watermark row (scope, token) is upserted into <catalog>.<schema>._faucet_commit_token with a MERGE.

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.