Skip to main content

Transform Databricks Iceberg on MotherDuck, Write Straight Back to Iceberg

IntegrationsFlightsdatabricks
Build this flight plan with your AI assistant

I keep my lakehouse in Databricks Unity Catalog and want a scheduled Flight that reads an Iceberg table, transforms it on MotherDuck, and writes the result straight back to Iceberg without staging a copy in MotherDuck. Help me adapt the "Transform Databricks Iceberg on MotherDuck, Write Straight Back to Iceberg" recipe to my own data and use case, using it as a guide: https://motherduck.com/docs/cookbook/flight-iceberg-databricks-direct

OpenAIOpen in ChatGPTAnthropicOpen in ClaudeView on GitHub

A single-file Flight that reads an Iceberg table in Databricks Unity Catalog (UC), transforms it on MotherDuck's engine, and writes the result straight back to Iceberg in the same catalog. Nothing is persisted in MotherDuck: MotherDuck is the transform compute, and the open lake stays the system of record. The story is "keep your Databricks lakehouse, run the transform compute on MotherDuck, write back to the same open tables."

The default transform is a daily usage rollup (usage_events_raw to usage_daily_rollup), so a deploy against that schema produces a successful run you can then point at your own tables and transform.

Staging variant. If you also want the result to live in a MotherDuck table (to query, share, or build Dives on) before it reaches Iceberg, use the companion Transform Databricks Iceberg using a MotherDuck Staging Table recipe instead. This one is the leaner, no-copy path.

How it works​

flight.py runs a fixed sequence; config values change its inputs:

  1. Connect to MotherDuck (md:), INSTALL/LOAD iceberg, and idempotently attach the Databricks UC Iceberg REST catalog using a MotherDuck secret.
  2. Begin a transaction and create the target table if missing.
  3. Replace its rows with DELETE and INSERT ... SELECT, then commit. A failed transform rolls back to the previous Iceberg snapshot.
  4. Log source and output row counts.

The transform is a plain SELECT, so adapt it by replacing the SELECT and the matching CREATE TABLE column list (keep the two in sync).

Questions to answer​

  • What is your UC Iceberg REST endpoint (ICEBERG_ENDPOINT) and warehouse (ICEBERG_WAREHOUSE)?
  • Which secret holds the Databricks token (ICEBERG_SECRET)?
  • Which schema and tables (ICEBERG_SCHEMA, SOURCE_TABLE, TARGET_TABLE)?
  • What is the transform, and does the target's explicit column list match it?
  • What schedule (cron) should it run on?

Caveats​

These are the setup gotchas that make Databricks UC Iceberg writes work from an external engine. They cost hours the first time; get them right before deploying.

  • The token needs all-apis scope. A scope-limited OAuth token 403s at /v1/config. A classic Databricks PAT with all-apis works. Store it as a MotherDuck Iceberg-catalog secret and pass the secret name using ICEBERG_SECRET.
  • Writes need credential vending, and the managed catalog does not vend. The default managed workspace catalog will not vend credentials to external engines. You need a UC storage credential + external location (an S3 bucket with a Databricks-provisioned IAM role using the AWS delegation flow), EXTERNAL USE SCHEMA, and external data access enabled. Create native Iceberg tables in a schema on that external location: those vend. Pre-existing Delta-as-Iceberg tables are read-only and will not vend, so you cannot write to them.
  • The AWS "60-minute temporary access" delegation screen provisions a persistent IAM role. Vending keeps working after the 60 minutes; that screen is a one-time setup step, not a time limit on the integration.
  • Pin DuckDB. Server-side Iceberg needs DuckDB >= 1.5.2, and the Flights runtime otherwise pulls the latest. This template pins duckdb==1.5.5.
  • Use distinct source and target names. Matching names, including case-only differences, fail before connecting.
  • The rollup uses UTC days and BIGINT customer IDs. event_ts must be castable to a date and customer_id to BIGINT. Null keys are retained as groups.
  • Targets must support merge-on-read deletes. Use an unsorted native Iceberg table with a compatible schema. Unsupported delete modes fail the transaction without dropping the previous result. Existing schemas are not migrated.
  • An existing catalog keeps its saved connection settings. ICEBERG_ENDPOINT, warehouse, secret, and default schema configure initial creation. Use a new catalog name or explicitly update its settings when changing the connection. Reusing a native MotherDuck database name is rejected.
  • Full refresh with rollback. Each run replaces the target rows in one transaction. The table identity and existing schema are retained. Adapt the write step for incremental or append semantics.
  • Keep the token out of config. The Databricks credential lives in the secret; a MotherDuck token is injected as MOTHERDUCK_TOKEN at runtime. Never put either in config.

What you'll adjust​

Config keyDefaultPurpose
ICEBERG_ENDPOINT(required)UC Iceberg REST endpoint, e.g. https://<host>/api/2.1/unity-catalog/iceberg-rest.
ICEBERG_WAREHOUSEworkspaceUC catalog/warehouse name.
ICEBERG_SECRETdatabricks_tokenName of the MotherDuck secret holding the all-apis token. Validated as an identifier.
ICEBERG_CATALOGdatabricks_icebergLocal attach name for the catalog. Validated as an identifier.
ICEBERG_DEFAULT_SCHEMAdefaultdefault_schema for the attach.
ICEBERG_SCHEMAmd_iceberg_demoWorking schema in the catalog. Validated as an identifier.
SOURCE_TABLEusage_events_rawSource Iceberg table. Validated as an identifier.
TARGET_TABLEusage_daily_rollupTarget Iceberg table (rows refreshed each run). Validated as an identifier.
MOTHERDUCK_TOKEN(Flight-injected)Auth. Select a token on the Flight; never put it in config.

Run it​

You need a MotherDuck account and token, a Databricks UC catalog reachable over the Iceberg REST endpoint, and a MotherDuck Iceberg-catalog secret with an all-apis token. To smoke-test before deploying:

export MOTHERDUCK_TOKEN=your_token_here
ICEBERG_ENDPOINT='https://<host>/api/2.1/unity-catalog/iceberg-rest' \
  uv run --with duckdb==1.5.5 flight.py

Deploy as a Flight​

Create the Flight with MD_CREATE_FLIGHT, passing source_code from flight.py, requirements_txt from requirements.txt, and the config keys you want to override. A MotherDuck token is attached automatically and injected as MOTHERDUCK_TOKEN; no token argument is needed. Run once manually with MD_RUN_FLIGHT, confirm it succeeds, then add a schedule with MD_UPDATE_FLIGHT (schedule updates are metadata-only).

Security​

  • Identifier validation. The catalog, secret name, schema, and table names flow into ATTACH/CREATE/INSERT statements that cannot be parameterized, so each is checked against ^[A-Za-z_][A-Za-z0-9_]*$ and double-quoted before SQL runs. The endpoint, warehouse, and default schema are inlined as escaped string literals.
  • Credential in a secret. The Databricks token never appears in code or config; it is referenced by secret name and read by the engine at attach time.

Validation​

The local development test uses a real Iceberg REST catalog and S3-compatible storage, with MotherDuck compute and on-demand managed Flights. It covers exact rollup rows, repeat runs, large IDs, nulls, UTC boundaries, staging-only mode, and rollback after a failed publish. See the integration test guide. Databricks authentication and credential vending require a separate UC workspace and are not validated by the local catalog test.

Learn more​