# Transform Databricks Iceberg via a MotherDuck Staging Table > A reusable Flight that reads an Iceberg table from Databricks Unity Catalog, transforms and materializes the result in a MotherDuck table, then publishes that table back to Iceberg. Use when you want the curated result to live in MotherDuck (to query, share, or build Dives on) as well as in the open lake. [View source on GitHub](https://github.com/motherduckdb/motherduck-cookbook/tree/main/flight-plans/flight-iceberg-databricks-stage) Category: integrations Features: flights Tags: databricks AI assistant prompt: ```text 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 ``` 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](https://motherduck.com/docs/cookbook/flight-iceberg-databricks-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 key | Default | Purpose | |---|---|---| | `ICEBERG_ENDPOINT` | (required) | UC Iceberg REST endpoint, e.g. `https:///api/2.1/unity-catalog/iceberg-rest`. | | `ICEBERG_WAREHOUSE` | `workspace` | UC catalog/warehouse name. | | `ICEBERG_SECRET` | `databricks_token` | Name of the MotherDuck secret holding the `all-apis` token. Validated as an identifier. | | `ICEBERG_CATALOG` | `databricks_iceberg` | Local attach name for the catalog. Validated as an identifier. | | `ICEBERG_DEFAULT_SCHEMA` | `default` | `default_schema` for the attach. | | `ICEBERG_SCHEMA` | `md_iceberg_demo` | Working schema in the catalog. Validated as an identifier. | | `SOURCE_TABLE` | `usage_events_raw` | Source Iceberg table. Validated as an identifier. | | `TARGET_TABLE` | `usage_daily_rollup` | Target Iceberg table (rows refreshed when publishing). Validated as an identifier. | | `MD_DATABASE` | `flights_demo` | MotherDuck database for the staged table. Created if missing. Validated as an identifier. | | `MD_SCHEMA` | `main` | MotherDuck schema for the staged table. Validated as an identifier. | | `MD_TABLE` | `usage_daily_rollup` | MotherDuck staging table name. Validated as an identifier. | | `PUBLISH_TO_ICEBERG` | `true` | Set `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: ```bash export MOTHERDUCK_TOKEN=your_token_here ICEBERG_ENDPOINT='https:///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`](https://github.com/motherduckdb/motherduck-cookbook/blob/main/flight-plans/flight-iceberg-databricks-stage/flight.py), `requirements_txt` from [`requirements.txt`](https://github.com/motherduckdb/motherduck-cookbook/blob/main/flight-plans/flight-iceberg-databricks-stage/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](https://github.com/motherduckdb/motherduck-cookbook/blob/main/flight-plans/flight-iceberg-databricks-stage/../../tests/iceberg_databricks/README.md). Databricks authentication and credential vending require a separate UC workspace and are not validated by the local catalog test. ## Learn more - [MotherDuck Iceberg integration](https://motherduck.com/docs/integrations/file-formats/apache-iceberg/). - [DuckDB Iceberg writes and limitations](https://duckdb.org/docs/current/core_extensions/iceberg/writing_to_iceberg). - Flight mechanics (creating, running, scheduling): use the MotherDuck MCP `get_flight_guide` tool. - Deeper MotherDuck or DuckDB questions (server-side Iceberg, UC credential vending): use the `ask_docs_question` MCP tool. - Files in this template: [`flight.py`](https://github.com/motherduckdb/motherduck-cookbook/blob/main/flight-plans/flight-iceberg-databricks-stage/flight.py) and [`requirements.txt`](https://github.com/motherduckdb/motherduck-cookbook/blob/main/flight-plans/flight-iceberg-databricks-stage/requirements.txt). --- ## Docs feedback MotherDuck accepts optional user-submitted feedback about this page at `GET https://motherduck.com/docs/api/feedback/agent`. For agents and automated tools, feedback submission should be user-confirmed before sending. URL-encode query parameter values and send a GET request: ```text GET https://motherduck.com/docs/api/feedback/agent?page_path=%2Fcookbook%2Fflight-iceberg-databricks-stage%2F&page_title=Transform%20Databricks%20Iceberg%20via%20a%20MotherDuck%20Staging%20Table&text= ``` Optionally append `&source=` such as `claude.ai` or `chatgpt`. `page_path` and `text` are required; `page_title` and `source` are optional. Responses: `200 {"feedback_id": ""}`, `400` for malformed query parameters, and `429` when rate-limited.