Skip to main content

Transform Databricks Iceberg via a MotherDuck Staging Table

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 into a MotherDuck table I can query and share, and then publishes that table back to Iceberg. Help me adapt the "Transform Databricks Iceberg via a MotherDuck Staging Table" recipe to my own data and use case, using it as a guide: https://motherduck.com/docs/cookbook/flight-iceberg-databricks-stage

OpenAIOpen in ChatGPTAnthropicOpen in ClaudeView on GitHub

A single-file Flight that reads an Iceberg table in Databricks Unity Catalog (UC), transforms and materializes the result in a MotherDuck table, then publishes that table back to Iceberg. You get the curated result in two places: a MotherDuck table you can query, share, and build Dives on, and the open Iceberg lake as the system of record.

This is the staging counterpart to the direct recipe. Use this one when the result should be usable inside MotherDuck (fast reads, sharing, Dives, BI) or when you want to inspect or validate the rollup before it reaches the shared lake; use the direct one when the output only needs to live in Iceberg and you would rather not keep a copy.

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.

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. Stage: CREATE OR REPLACE TABLE the transform result into a MotherDuck table (reading the source Iceberg table, transforming on MotherDuck's engine).
  3. Publish: replace the Iceberg target rows in one transaction using DELETE and INSERT ... SELECT from the staging table. A failed publish rolls back the Iceberg snapshot. Set PUBLISH_TO_ICEBERG=false to stage only.
  4. Log staged and published row counts.

The transform is a plain SELECT, so adapt it by replacing the SELECT in the stage step and the matching CREATE TABLE column list in the publish step.

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)?
  • Where should the MotherDuck copy live (MD_DATABASE, MD_SCHEMA, MD_TABLE)?
  • What is the transform, and does the Iceberg 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 staging database must also differ from the Iceberg catalog.
  • 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.
  • The staged MotherDuck table is fully replaced each run (CREATE OR REPLACE). Iceberg rows are refreshed in a separate transaction. A failed publish preserves the old Iceberg output but leaves the new MotherDuck staging table available. The two destinations do not commit atomically together.
  • Reads only? Staging still needs vending. Reading data files from UC also needs vending, so the same external-location setup applies even if you set PUBLISH_TO_ICEBERG=false.
  • 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 when publishing). Validated as an identifier.
MD_DATABASEflights_demoMotherDuck database for the staged table. Created if missing. Validated as an identifier.
MD_SCHEMAmainMotherDuck schema for the staged table. Validated as an identifier.
MD_TABLEusage_daily_rollupMotherDuck staging table name. Validated as an identifier.
PUBLISH_TO_ICEBERGtrueSet false to stage only. Only true and false are accepted.
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

Include PUBLISH_TO_ICEBERG in the Flight config at creation if you want to override it per run. Run overrides can only change keys already defined in config.

Set PUBLISH_TO_ICEBERG=false on the first run to build the MotherDuck staging table and inspect it before writing anything to Iceberg.

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, schemas, 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​