# Databricks on AWS
How to export results from Materialize to Apache Iceberg tables registered in Databricks Unity Catalog on AWS.
> **Public Preview:** This feature is in public preview.


*Available starting in v26.40*

This guide walks you through the steps required to set up Iceberg sinks in
Materialize Cloud against [Databricks Unity
Catalog](https://docs.databricks.com/aws/en/data-governance/unity-catalog/) on AWS.
Materialize reaches Unity Catalog through its [Iceberg REST catalog
endpoint](https://docs.databricks.com/aws/en/external-access/iceberg),
authenticating with the OAuth2 credentials of a Databricks service principal.

This guide covers Databricks workspaces on AWS, whose URLs have the form
`https://<workspace>.cloud.databricks.com`. Databricks on Azure and Google Cloud
are not yet covered.

## Prerequisites

### Allow network access from Materialize

Materialize reaches Unity Catalog and the storage behind it over the public
internet. Iceberg catalog connections do not support tunneling through [AWS
PrivateLink](/sql/create-connection/#aws-privatelink) or [SSH bastion
hosts](/sql/create-connection/#ssh-tunnel). If your workspace restricts access
by IP, allow traffic from the [static egress IP
addresses](/ingest-data/network-security/static-ips/) associated with your
Materialize region.

### A Unity Catalog metastore with external data access enabled

Your Databricks workspace must be attached to a Unity Catalog metastore, and
that metastore must have **External data access** enabled. Unity Catalog rejects
every Iceberg REST request until it is, including the ones Materialize makes to
discover the table.

The toggle is in **Catalog Explorer** on the metastore's **Details** tab, listed
as **External data access**. Editing metastore details requires an account
administrator, who can also set it from the account console. See [Databricks:
Enable external data access to Unity
Catalog](https://docs.databricks.com/aws/en/external-access/admin).

### A catalog and a schema to write into

Materialize creates the Iceberg *table* a sink writes to, but it does not create
the catalog or the schema containing it. Both must exist first.

In a Databricks SQL editor or notebook:

```sql
CREATE CATALOG <catalog_name> MANAGED LOCATION '<s3 bucket URI plus optional path>';
CREATE SCHEMA <catalog_name>.<schema_name>;
```

The two names map onto the Materialize objects you create below:

- The catalog name becomes the connection's `WAREHOUSE`. For Unity Catalog this
  is the name of a catalog, not a storage location.
- The schema name becomes the sink's `NAMESPACE`.

Materialize can only sink into Unity Catalog **managed** tables, so use a
standard catalog with managed storage. Foreign catalogs and Delta tables are
read-only through the Iceberg REST catalog.

Additionally, Materialize currently only supports sinking to Unity Catalog catalogs/schemas using external storage locations rather than Databricks default storage.

### A service principal with credentials and privileges

Materialize authenticates as a [Databricks service
principal](https://docs.databricks.com/aws/en/admin/users-groups/service-principals).

1. Create the service principal at the account level, and assign it to the
   workspace whose URL you use for the connection. Materialize exchanges its
   credentials at that workspace's token endpoint, so a service principal
   without access to the workspace cannot authenticate.

2. Generate an **OAuth secret** for the service principal. This gives you a
   client ID (its application ID) and a client secret. Record the secret when it
   is shown; Databricks does not display it again.

3. Grant the service principal `READ METADATA` **on the metastore**. In
   **Catalog Explorer**, open the metastore, go to its **Permissions** tab, and
   grant `READ METADATA`, which lets Materialize read the metadata of the tables
   the sink commits against.

    This is a metastore-level grant, separate from the catalog and schema grants
    below. Materialize needs `READ METADATA` at both levels.

4. Grant the service principal the privileges Materialize needs **on the catalog
   and schema**. In a Databricks SQL editor, using the service principal's
   application ID as the grantee:

    ```sql
    GRANT USE CATALOG, READ METADATA ON CATALOG <catalog_name>
      TO `<application_id>`;

    GRANT USE SCHEMA, CREATE TABLE, MODIFY, SELECT, EXTERNAL USE SCHEMA
      ON SCHEMA <catalog_name>.<schema_name> TO `<application_id>`;
    ```

    | Privilege | Why Materialize needs it |
    | --- | --- |
    | `USE CATALOG`, `USE SCHEMA` | Allows access to the catalog and schema. |
    | `READ METADATA` | Read the metadata of the tables the sink commits against. |
    | `EXTERNAL USE SCHEMA` | Read and write the schema's tables from an Iceberg REST client. Without it, every catalog request is rejected. |
    | `CREATE TABLE` | Create the Iceberg table the first time the sink runs. |
    | `MODIFY` | Commit new snapshots as data changes. |
    | `SELECT` | Read the table the sink writes to. |

    Databricks restricts who may grant `EXTERNAL USE SCHEMA`. If the grant is
    rejected, ask the catalog owner or a metastore admin to run it. See
    [Databricks: Access Databricks tables from Apache Iceberg
    clients](https://docs.databricks.com/aws/en/external-access/iceberg).

5. If the catalog or schema stores its managed tables in an [external
   location](https://docs.databricks.com/aws/en/connect/unity-catalog/cloud-storage/external-locations),
   grant the service principal `EXTERNAL USE LOCATION` on that location as well:

    ```sql
    GRANT EXTERNAL USE LOCATION ON EXTERNAL LOCATION <location_name>
      TO `<application_id>`;
    ```

    Without it, Unity Catalog refuses to vend storage credentials for the
    table's data files, and the sink fails even though every catalog request
    succeeds. Catalogs using the metastore's own managed storage need no such
    grant. Like `EXTERNAL USE SCHEMA`, this grant is restricted to the location
    owner and metastore admins.

## Create the Iceberg catalog connection in Materialize

The following example creates an [Iceberg catalog connection](/sql/create-connection/#iceberg-catalog) for Databricks Unity Catalog:
```mzsql
-- Store the service principal's OAuth credentials as `<client_id>:<client_secret>`.
CREATE SECRET databricks_oauth
  AS '<client_id>:<client_secret>';

-- Create the Iceberg catalog connection pointing to Unity Catalog.
CREATE CONNECTION iceberg_catalog_connection TO ICEBERG CATALOG (
    CATALOG TYPE = 'rest',
    URL = 'https://<workspace>.cloud.databricks.com/api/2.1/unity-catalog/iceberg-rest',
    WAREHOUSE = '<catalog_name>',
    CREDENTIAL = SECRET databricks_oauth,
    OAUTH2 SERVER URL = 'https://<workspace>.cloud.databricks.com/oidc/v1/token',
    SCOPE = 'all-apis',
    ACCESS DELEGATION = 'vended-credentials'
);

```

Fill in the syntax elements as follows:

| Syntax element | Value for Unity Catalog |
| --- | --- |
| `URL` | `https://<workspace>.cloud.databricks.com/api/2.1/unity-catalog/iceberg-rest` |
| `WAREHOUSE` | The name of the Unity Catalog catalog holding your tables. Unlike other catalogs, this is not a storage location. |
| `CREDENTIAL` | A secret holding the service principal's `<client_id>:<client_secret>`. |
| `OAUTH2 SERVER URL` | `https://<workspace>.cloud.databricks.com/oidc/v1/token` |
| `SCOPE` | `all-apis` |
| `ACCESS DELEGATION` | `'vended-credentials'` |

`OAUTH2 SERVER URL`, `SCOPE`, and `ACCESS DELEGATION` are all required for Unity
Catalog. It serves its token endpoint on a path unrelated to the catalog URL,
does not grant the specification's `catalog` scope, and manages the storage
behind its tables without handing out long-lived credentials for it. See
[Storage access
delegation](/sql/create-connection/#iceberg-catalog-access-delegation) for what
`ACCESS DELEGATION` changes.

For the full syntax reference, see [`CREATE CONNECTION`: Iceberg
catalog](/sql/create-connection/#iceberg-catalog).

## Create the Iceberg sink in Materialize

Set the sink's `NAMESPACE` to the Unity Catalog schema you granted privileges
on.

In Materialize, you can sink from a materialized view, table, or source. Use
[`CREATE SINK`](/sql/create-sink/iceberg) to create an Iceberg sink, replacing:

- `<sink_name>` with a name for your sink.
- `<sink_cluster>` with the name of your sink cluster.
- `<my_materialize_object>` with the name of your materialized view, table, or source.
- `<my_iceberg_namespace>` with your catalog namespace.
- `<my_iceberg_table>` with the name of your Iceberg table. If the Iceberg table
  does not exist, Materialize creates the table. For details, see [`CREATE SINK`
  reference page](/sql/create-sink/iceberg/#iceberg-table-creation).
- `<commit_interval>` with your commit interval (e.g., `1m`). The commit
  interval specifies how frequently Materialize commits snapshots to Iceberg.
  The minimum commit interval is `1s`. See [Commit interval
  tradeoffs](#commit-interval-tradeoffs) below.

For the full list of syntax options, see the [`CREATE SINK` reference](/sql/create-sink/iceberg).


### Append-only sinks {#append-only-sinks}

Unity Catalog tables accept only `MODE APPEND`, so the sink appends a row per
change rather than updating rows in place: `_mz_diff` is `+1` for an insertion
and `-1` for a deletion, and an update arrives as both. To recover the current
contents of the relation, group by the data columns and keep the groups whose
`_mz_diff` sums above zero. See [Append
mode](/sql/create-sink/iceberg/#append-mode) for the full change encoding.

In append mode, no `KEY` clause is used. The Iceberg table includes all source
columns plus `_mz_diff` (`int`) and `_mz_timestamp` (`long`).
```mzsql
CREATE SINK <sink_name>
  IN CLUSTER <sink_cluster>
  FROM <my_materialize_object>
  INTO ICEBERG CATALOG CONNECTION iceberg_catalog_connection (
    NAMESPACE = '<my_iceberg_namespace>',
    TABLE = '<my_iceberg_table>'
  )
  MODE APPEND
  WITH (COMMIT INTERVAL = '<commit_interval>');

```

## Considerations

### Commit interval tradeoffs {#commit-interval-tradeoffs}

The `COMMIT INTERVAL` setting controls how frequently Materialize commits
snapshots to your Iceberg table, making the data available to downstream query
engines. This setting involves tradeoffs:

| Shorter intervals (e.g., < `1m`) | Longer intervals (e.g., `5m`) |
|---------------------------------|-------------------------------|
| Lower latency - data visible sooner in downstream systems | Higher latency - data takes longer to appear |
| More small files - can degrade query performance over time | Fewer, larger files - better query performance |
| More frequent snapshot commits - higher catalog overhead | Less catalog overhead |
| Lower throughput efficiency | Higher throughput efficiency |

**Recommendations:**
- For production, use intervals of `1m` or longer
- For batch analytics, use longer intervals (`5m` to `15m`)

Starting in v26.34, you can change the commit interval of an existing sink with
[`ALTER SINK`](/sql/alter-sink/).

> **Note:** Outside of development environments, commit intervals should be at least `1m`.
> Short commit intervals increase catalog overhead and produce many small files.
> Small files will result in degraded query performance. It also increases load on
> the Iceberg metadata, which can result in a degraded catalog, and non-responsive
> queries.

### Exactly-once delivery

Iceberg sinks provide **exactly-once delivery**. After a restart,
Materialize resumes from the last committed snapshot without duplicating
data.

Materialize stores progress information in Iceberg snapshot metadata
properties (`mz-frontier` and `mz-sink-version`).


### Credential refresh

The OAuth2 token Materialize exchanges its credentials for is short-lived, as
are the storage credentials Unity Catalog vends. Materialize refreshes both while
the sink runs, so a long-running sink needs no intervention. Rotating the service
principal's OAuth secret in Databricks does require updating the Materialize
secret:

```mzsql
ALTER SECRET databricks_oauth AS '<client_id>:<new_client_secret>';
```

### Type mapping

Materialize converts SQL types to Iceberg/Parquet types:

| SQL type | Iceberg type |
|----------|--------------|
| `boolean` | `boolean` |
| `smallint`, `integer` | `int` |
| `uint2` | `int` |
| `bigint` | `long` |
| `uint4` | `long` |
| `uint8` | `decimal(20, 0)` |
| `real` | `float` |
| `double precision` | `double` |
| `numeric` | `decimal(38, scale)` |
| `date` | `date` |
| `time` | `time` (microsecond) |
| `timestamp` | `timestamp` (microsecond) |
| `timestamptz` | `timestamptz` (microsecond) |
| `text`, `varchar` | `string` |
| `bytea` | `binary` |
| `uuid` | `fixed(16)` |
| `jsonb` | `string` |
| `interval` | `string` |
| `int4range`, `int8range`, `numrange`, `daterange`, `tsrange`, `tstzrange` | `struct` (fields: `lower`, `upper`, `lower_inclusive`, `upper_inclusive`, `empty`) |
| `record` | `struct` |
| `list` | `list` |
| `map` | `map` |

### Limitations

- Materialize does not create schemas. The Unity Catalog schema named by the
  sink's `NAMESPACE` must already exist.

- Materialize can only sink into *managed* Iceberg tables. Foreign Iceberg
  tables and Delta tables are read-only through the Iceberg REST catalog.

- Only `MODE APPEND` sinks are supported. `MODE UPSERT` expresses retractions as
  Iceberg equality delete files, which Unity Catalog managed tables do not
  accept.

- Partitioned tables are not supported.


- Schema evolution of an Iceberg table is not supported. If the `SINK FROM` object's schema changes, you must drop and recreate the sink.

## Troubleshooting

If the sink reports an error, start with the sink's own status:

```mzsql
SELECT name, error FROM mz_internal.mz_sink_statuses WHERE name = '<sink_name>';
```

| Error | Cause |
| --- | --- |
| Token exchange failures | `OAUTH2 SERVER URL` or `SCOPE` does not match what the workspace expects, the service principal is not assigned to the workspace, or its OAuth secret has been rotated or revoked. |
| Authentication failures on every catalog request | External data access is not enabled on the metastore, or the service principal is missing `READ METADATA` on the metastore or `EXTERNAL USE SCHEMA` on the schema. |
| A namespace-not-found error when the sink starts | The schema named by `NAMESPACE` does not exist, or the service principal cannot see it. |
| Storage errors once the sink is running | `ACCESS DELEGATION = 'vended-credentials'` is not set on the connection, or the catalog uses an external location the service principal lacks `EXTERNAL USE LOCATION` on. Unity Catalog vends credentials as the only way to reach its storage. |

### Sink creation fails with "input compacted past resume upper"

This error occurs when the source data has been compacted beyond the point where
the sink last committed. This can happen after a Materialize backup/restore
operation. You may need to drop and recreate the sink, which will re-snapshot
the entire source relation.

### Commit conflicts

If another process modifies the Iceberg table while Materialize is committing,
you may see commit conflict errors. Materialize will automatically retry, but
if conflicts persist, ensure no other writers are modifying the same table.

## Related pages

- [`CREATE SINK`](/sql/create-sink/iceberg)
- [`CREATE CONNECTION`](/sql/create-connection)
- [Storage access delegation](/sql/create-connection/#iceberg-catalog-access-delegation)
- [Databricks: Access Databricks tables from Apache Iceberg clients](https://docs.databricks.com/aws/en/external-access/iceberg)
- [Apache Iceberg documentation](https://iceberg.apache.org/docs/latest/)