# Microsoft Fabric OneLake
> Read and write Microsoft Fabric Lakehouse tables in OneLake from MotherDuck, using the OneLake Iceberg REST catalog or Delta tables.
[Microsoft Fabric OneLake](https://learn.microsoft.com/en-us/fabric/onelake/onelake-overview) is the storage layer behind every Fabric workspace. Fabric stores Lakehouse tables in Delta Lake format and also publishes Iceberg metadata for each one. This lets you read the same tables from MotherDuck in two ways:

- **Iceberg REST catalog:** attach the Lakehouse as a MotherDuck database with `CREATE DATABASE … (TYPE ICEBERG)`, then query its tables by name.
- **Delta tables by path:** read a table folder directly with `delta_scan('abfss://…')`.

You can also [write tables back to OneLake](#write-tables-to-onelake) through the Iceberg catalog, and Fabric picks them up as regular Lakehouse tables.

All of this runs on MotherDuck's compute and authenticates with a Microsoft Entra ID service principal. This page walks through the one-time setup in Azure and Fabric, then the SQL to connect, query, and write.

## Prerequisites

- A [MotherDuck account](https://app.motherduck.com) and a [MotherDuck access token](/key-tasks/authenticating-and-connecting-to-motherduck/authenticating-to-motherduck/authenticating-to-motherduck.md#creating-an-access-token)
- A Microsoft Fabric capacity, such as a [Fabric trial](https://learn.microsoft.com/en-us/fabric/fundamentals/fabric-trial), with a workspace that contains a Lakehouse
- Permission to register applications in Microsoft Entra ID, and a Fabric administrator who can change tenant settings
- The [DuckDB CLI](/getting-started/interfaces/connect-query-from-duckdb-cli.mdx) or the [Python client](/getting-started/interfaces/client-apis/python/installation-authentication.md), version 1.5 or later, connected to MotherDuck. You need a client to create the secrets in [step 5](#5-store-the-credentials-in-motherduck), because the MotherDuck UI can't create them (see [Known limitations](#known-limitations)).

## How the connection works

MotherDuck uses up to two secrets, both backed by the same service principal:

| Secret | Type | Needed for |
|---|---|---|
| Catalog secret | `ICEBERG` | Everything that goes through the OneLake Iceberg REST catalog at `https://onelake.table.fabric.microsoft.com/iceberg`: listing tables, reading them, and writing to them |
| Storage secret | `AZURE` | Reading files from OneLake storage at `onelake.dfs.fabric.microsoft.com` by path, with `delta_scan` or `iceberg_scan`, and `CREATE TABLE … AS` into a new OneLake table |

When MotherDuck reads or writes a table through the catalog, OneLake hands out short-lived storage credentials for that table, and MotherDuck uses them. If you only query and write tables through the catalog, the catalog secret is all you need.

## 1. Register an application in Microsoft Entra ID

The service principal is the identity MotherDuck uses to sign in to OneLake.

1. In the [Azure portal](https://portal.azure.com), go to **Microsoft Entra ID** > **App registrations** > **New registration**.
1. Enter a name, such as `motherduck-onelake`, leave **Supported account types** at **Accounts in this organizational directory only**, and select **Register**. You don't need a redirect URI.
1. On the app's **Overview** page, copy the **Application (client) ID** and the **Directory (tenant) ID**.

1. Go to **Certificates & secrets** > **Client secrets** > **New client secret**, choose an expiry, and select **Add**.
1. Copy the secret's **Value** right away. Azure shows it only once.

The app doesn't need any API permissions. Access to OneLake comes from the workspace role you assign in [step 3](#3-give-the-service-principal-access-to-the-workspace).

<details>
<summary>Do the same with the Azure CLI</summary>

```bash
az login
az ad app create --display-name motherduck-onelake --query appId -o tsv   # prints <client_id>
az ad sp create --id <client_id>
az ad app credential reset --id <client_id> --append --years 1 --query password -o tsv   # prints <client_secret>
az account show --query tenantId -o tsv   # prints <tenant_id>
```

</details>

## 2. Enable the Fabric tenant settings

A Fabric administrator needs to allow service principals and external apps to reach OneLake.

1. In [Fabric](https://app.fabric.microsoft.com), select the **Settings** gear > **Admin portal** > **Tenant settings**.
1. Use the search box to find each setting and make sure it's enabled, either for the whole organization or for a security group that contains your service principal:

   | Setting | Section |
   |---|---|
   | **Service principals can call Fabric public APIs** | Developer settings |
   | **Users can access data stored in OneLake with apps external to Fabric** | OneLake settings |

Tenant setting changes can take up to 15 minutes to apply.

## 3. Give the service principal access to the workspace

1. Open the Fabric workspace that contains your Lakehouse and select **Manage access**.
1. Select **Add people or groups**, search for your app's name, choose the **Contributor** role, and select **Add**.

The **Contributor** role lets MotherDuck both read and [write](#write-tables-to-onelake) Lakehouse tables. To grant narrower access, see the Fabric documentation on [workspace roles](https://learn.microsoft.com/en-us/fabric/fundamentals/roles-workspaces) and [OneLake security](https://learn.microsoft.com/en-us/fabric/onelake/security/get-started-security).

## 4. Find the workspace and Lakehouse IDs

Open the Lakehouse in Fabric. Its URL contains both IDs:

```text
https://app.fabric.microsoft.com/groups/<workspace_id>/lakehouses/<lakehouse_id>
```

You also need the Lakehouse's schema. Lakehouses created with **Lakehouse schemas** enabled, which is the default, keep tables under `dbo` unless you create other schemas. Only tables under the Lakehouse's **Tables** folder are available through the catalog; files under **Files** aren't.

## 5. Store the credentials in MotherDuck

Create the secrets from the DuckDB CLI or Python while connected to MotherDuck. `IN MOTHERDUCK` stores them in your MotherDuck account, so you create them once and then use them from any client, including the MotherDuck UI. Skip the storage secret if you don't need the features listed in [How the connection works](#how-the-connection-works).

### SQL (DuckDB CLI)

```sql
-- Catalog secret: MotherDuck requests tokens from Entra ID with the service principal
CREATE SECRET onelake_iceberg_catalog IN MOTHERDUCK (
    TYPE ICEBERG,
    CLIENT_ID '<client_id>',
    CLIENT_SECRET '<client_secret>',
    OAUTH2_SERVER_URI 'https://login.microsoftonline.com/<tenant_id>/oauth2/v2.0/token',
    OAUTH2_SCOPE 'https://storage.azure.com/.default'
);

-- Optional storage secret: for delta_scan, iceberg_scan, and CREATE TABLE ... AS
CREATE SECRET onelake_azure IN MOTHERDUCK (
    TYPE AZURE,
    PROVIDER SERVICE_PRINCIPAL,
    TENANT_ID '<tenant_id>',
    CLIENT_ID '<client_id>',
    CLIENT_SECRET '<client_secret>',
    ACCOUNT_NAME 'onelake'
);
```

### Python

```python
import os
import duckdb

con = duckdb.connect("md:")  # uses the MOTHERDUCK_TOKEN environment variable
tenant_id = os.environ["AZURE_TENANT_ID"]
client_id = os.environ["AZURE_CLIENT_ID"]
client_secret = os.environ["AZURE_CLIENT_SECRET"]

con.sql(f"""
    CREATE SECRET onelake_iceberg_catalog IN MOTHERDUCK (
        TYPE ICEBERG,
        CLIENT_ID '{client_id}',
        CLIENT_SECRET '{client_secret}',
        OAUTH2_SERVER_URI 'https://login.microsoftonline.com/{tenant_id}/oauth2/v2.0/token',
        OAUTH2_SCOPE 'https://storage.azure.com/.default'
    )
""")

con.sql(f"""
    CREATE SECRET onelake_azure IN MOTHERDUCK (
        TYPE AZURE,
        PROVIDER SERVICE_PRINCIPAL,
        TENANT_ID '{tenant_id}',
        CLIENT_ID '{client_id}',
        CLIENT_SECRET '{client_secret}',
        ACCOUNT_NAME 'onelake'
    )
""")
```

Creating the catalog secret requests a token from Entra ID straight away, so a wrong client ID, tenant ID, or client secret fails at this step with an error from Entra ID.

:::info
Keep the client secret out of scripts and version control. Store it in an environment variable or a secrets manager.
:::

Check that the secrets exist:

```sql
SELECT name, type, storage FROM duckdb_secrets();
```

## 6. Attach the Lakehouse as a MotherDuck database

```sql
CREATE DATABASE onelake_lakehouse (
    TYPE ICEBERG,
    "secret" onelake_iceberg_catalog,
    endpoint 'https://onelake.table.fabric.microsoft.com/iceberg',
    warehouse '<workspace_id>/<lakehouse_id>',
    default_schema 'dbo',
    access_delegation_mode 'vended_credentials',
    read_only true
);
```

| Option | Value for OneLake |
|---|---|
| `secret` | The `ICEBERG` secret from step 5 |
| `endpoint` | Always `https://onelake.table.fabric.microsoft.com/iceberg` |
| `warehouse` | `<workspace_id>/<lakehouse_id>`, separated by a slash |
| `default_schema` | The Lakehouse schema, usually `dbo` |
| `access_delegation_mode` | `vended_credentials` |
| `read_only` | `true` for reading. To write, attach a separate database as shown in [Write tables to OneLake](#write-tables-to-onelake). |

The database stays in your MotherDuck account, so you only attach it once. For the full list of options, see [Apache Iceberg](/integrations/file-formats/apache-iceberg.mdx#database-options).

## Query OneLake tables

List the tables MotherDuck sees in the Lakehouse:

```sql
SELECT schema_name, table_name
FROM duckdb_tables()
WHERE database_name = 'onelake_lakehouse'
ORDER BY ALL;
```

Then query them like any other MotherDuck table, using `database.schema.table`:

```sql
SELECT
    l_returnflag,
    l_linestatus,
    sum(l_quantity) AS sum_qty,
    count(*) AS count_order
FROM onelake_lakehouse.dbo.lineitem
WHERE l_shipdate <= DATE '1998-12-01' - INTERVAL 90 DAY
GROUP BY ALL
ORDER BY ALL;
```

Joins work across OneLake tables, and between OneLake tables and MotherDuck tables:

```sql
SELECT n.n_name AS nation, count(*) AS customers
FROM onelake_lakehouse.dbo.customer AS c
JOIN onelake_lakehouse.dbo.nation AS n ON c.c_nationkey = n.n_nationkey
GROUP BY ALL
ORDER BY customers DESC;
```

Once the secrets and database exist, you can run these queries from the MotherDuck UI as well.

![A MotherDuck notebook running TPC-H Q1 against onelake_lakehouse.dbo.lineitem, with the onelake_lakehouse database and its tables in the sidebar](img/onelake-query-results.png)

## Read Delta tables directly

To read a table without the catalog, pass its folder to `delta_scan`. This needs the `AZURE` storage secret from step 5, and doesn't use the catalog secret.

```sql
SELECT count(*)
FROM delta_scan('abfss://<workspace_id>@onelake.dfs.fabric.microsoft.com/<lakehouse_id>/Tables/dbo/<table_name>');
```

The path format is `abfss://<workspace_id>@onelake.dfs.fabric.microsoft.com/<lakehouse_id>/Tables/<schema>/<table_name>`.

You can also read the Iceberg metadata of a single table with `iceberg_scan` on the same folder. Don't set `allow_moved_paths`: Fabric writes absolute paths into the metadata, and the option makes MotherDuck resolve them incorrectly.

```sql
SELECT count(*)
FROM iceberg_scan('abfss://<workspace_id>@onelake.dfs.fabric.microsoft.com/<lakehouse_id>/Tables/dbo/<table_name>');
```

## Copy OneLake data into MotherDuck

For repeated or latency-sensitive queries, copy a table into MotherDuck storage:

```sql
CREATE DATABASE IF NOT EXISTS analytics;
CREATE OR REPLACE TABLE analytics.main.lineitem AS
FROM onelake_lakehouse.dbo.lineitem;
```

## Write tables to OneLake

To write, attach the Lakehouse a second time as a writable database. Set `stage_create_tables` to `false`: OneLake doesn't support staged table creation and rejects `CREATE TABLE` with `400 Malformed request` otherwise.

```sql
CREATE DATABASE onelake_lakehouse_rw (
    TYPE ICEBERG,
    "secret" onelake_iceberg_catalog,
    endpoint 'https://onelake.table.fabric.microsoft.com/iceberg',
    warehouse '<workspace_id>/<lakehouse_id>',
    default_schema 'dbo',
    access_delegation_mode 'vended_credentials',
    stage_create_tables false,
    read_only false
);
```

Keeping a separate writable database lets you leave the one from step 6 read-only for analysts and dashboards.

Then create a table and load it with standard SQL. Create the table first, then load it with `INSERT … SELECT`. This works with the catalog secret alone; `CREATE TABLE … AS` into a new table also needs the [storage secret](#5-store-the-credentials-in-motherduck).

```sql
CREATE TABLE onelake_lakehouse_rw.dbo.nation_snapshot (
    n_nationkey INTEGER,
    n_name VARCHAR,
    loaded_at TIMESTAMPTZ
);

INSERT INTO onelake_lakehouse_rw.dbo.nation_snapshot
SELECT n_nationkey, n_name, now()
FROM onelake_lakehouse_rw.dbo.nation;

SELECT count(*) FROM onelake_lakehouse_rw.dbo.nation_snapshot;
```

`UPDATE`, `DELETE`, and `MERGE INTO` work too:

```sql
UPDATE onelake_lakehouse_rw.dbo.nation_snapshot
SET n_name = n_name || ' (updated)', loaded_at = now()
WHERE n_nationkey = 1;

SELECT * FROM onelake_lakehouse_rw.dbo.nation_snapshot WHERE n_nationkey = 1;

DELETE FROM onelake_lakehouse_rw.dbo.nation_snapshot
WHERE n_nationkey = 24;

SELECT count(*) FROM onelake_lakehouse_rw.dbo.nation_snapshot;

MERGE INTO onelake_lakehouse_rw.dbo.nation_snapshot AS t
USING (SELECT 100 AS n_nationkey, 'NEW NATION' AS n_name) AS s
ON t.n_nationkey = s.n_nationkey
WHEN MATCHED THEN UPDATE SET n_name = s.n_name, loaded_at = now()
WHEN NOT MATCHED THEN INSERT (n_nationkey, n_name, loaded_at) VALUES (s.n_nationkey, s.n_name, now());

SELECT * FROM onelake_lakehouse_rw.dbo.nation_snapshot ORDER BY n_nationkey DESC LIMIT 3;
```

MotherDuck records updates and deletes as Iceberg position deletes, which Fabric converts to Delta deletion vectors, so Fabric sees the same rows as MotherDuck.

MotherDuck writes an Iceberg table into the Lakehouse's `Tables/dbo/nation_snapshot` folder. Fabric then generates Delta metadata for it, so the table appears in the Lakehouse explorer and the SQL analytics endpoint like any other Lakehouse table.

![The Fabric Lakehouse explorer showing nation_snapshot under Tables > dbo, with a data preview of the rows MotherDuck wrote](img/onelake-fabric-table-preview.png)

Keep these in mind when writing:

- **Use time zone-aware timestamps.** Write `TIMESTAMPTZ` columns, such as `now()`, not `TIMESTAMP`. Fabric doesn't support `TIMESTAMP_NTZ` columns and shows *"Columns of the specified data types are not supported"* for them.
- **Allow time for Fabric to pick up changes.** Fabric's conversion takes from a few seconds to about 2 minutes after each write. Until it finishes, the SQL analytics endpoint can report `DeltaTableNotFound` for a new table. It recovers on its next sync, or when you reload the Lakehouse page.
- **Write through the catalog, not as Delta.** MotherDuck can't write Delta tables, so `COPY … (FORMAT delta)` isn't available. Iceberg tables written through the catalog are the way to get data into a Lakehouse.

To delete a table from OneLake, drop it through the writable database:

```sql
DROP TABLE onelake_lakehouse_rw.dbo.nation_snapshot;
```

## Rotate or remove credentials

Entra ID client secrets expire. Before the expiry date, create a new client secret, then replace your MotherDuck secrets from the CLI with `CREATE OR REPLACE SECRET`, using the same names.

After you replace the catalog secret, drop and recreate the Iceberg database so it picks up the new secret:

```sql
DROP DATABASE onelake_lakehouse;
-- then run the CREATE DATABASE statement from step 6 again
```

To remove the integration:

```sql
DROP DATABASE IF EXISTS onelake_lakehouse;
DROP DATABASE IF EXISTS onelake_lakehouse_rw;
DROP SECRET IF EXISTS onelake_iceberg_catalog FROM motherduck;
DROP SECRET IF EXISTS onelake_azure FROM motherduck;
```

## Troubleshooting

| Error | Cause and fix |
|---|---|
| `Could not get token from https://login.microsoftonline.com/…: HTTP NotFound_404` when creating the catalog secret in the MotherDuck UI | The UI can't request tokens from Entra ID. Create the secret from the DuckDB CLI or Python. |
| `Extension …/wasm_eh/azure.duckdb_extension.wasm is not available` when creating the storage secret in the MotherDuck UI | The UI can't create `AZURE` secrets. Create the secret from the DuckDB CLI or Python. |
| `AADSTS7000215: Invalid client secret provided` | The client secret is wrong or expired. Use the secret's **Value**, not its **Secret ID**. |
| `AADSTS700016` or `AADSTS90002` | The client ID or tenant ID is wrong, or the app is registered in a different tenant. |
| `Error performing token request` with `169.254.169.254` and `401 Unauthorized` from `delta_scan` or `iceberg_scan` | The `AZURE` storage secret is missing. Create it as shown in step 5. |
| `AzureStorageFileSystem open file '…' failed with code 'Unauthorized'` on `CREATE TABLE … AS` | `CREATE TABLE … AS` into a new OneLake table needs the `AZURE` storage secret. Create the secret, or create the table first and then load it with `INSERT … SELECT`. |
| `403` or `Forbidden` from OneLake | The service principal has no role on the workspace, or a tenant setting from step 2 is off. |
| A table is missing from `duckdb_tables()` | Fabric hasn't published Iceberg metadata for it yet, which can take a few minutes after a write, or the table isn't under the Lakehouse's **Tables** folder. Check the `default_schema` too. |
| `No ICEBERG secret by the name of '…' could be found` | The catalog secret was dropped or replaced after the database was created. Recreate the database. |
| `CREATE TABLE` fails with `BadRequest_400` and `Malformed request` | The writable database doesn't set `stage_create_tables false`. Recreate it with that option. |
| Fabric shows *"Columns of the specified data types are not supported"* with `TIMESTAMP_NTZ` | The table has a `TIMESTAMP` column. Recreate it with `TIMESTAMPTZ` columns. |
| Fabric shows `DeltaTableNotFound` for a table MotherDuck just wrote | Fabric hasn't finished generating the table's Delta metadata yet. Wait up to 2 minutes, then reload the Lakehouse page. |

## Known limitations

- **Secrets must be created from a client.** In the MotherDuck UI, creating the OAuth2 `ICEBERG` secret and any `AZURE` secret fails. Create them from the DuckDB CLI or Python; the UI can then query OneLake. In the UI, you can create an `ICEBERG` secret with a bearer token instead (`TYPE ICEBERG, TOKEN '<access_token>'`), but the token expires after about an hour and isn't refreshed.
- **Writes go through the Iceberg catalog only.** They need `stage_create_tables false`. MotherDuck can't write Delta tables.

## Related

- [Apache Iceberg in MotherDuck](/integrations/file-formats/apache-iceberg.mdx)
- [Delta Lake in MotherDuck](/integrations/file-formats/delta-lake.mdx)
- [Azure Blob Storage in MotherDuck](/integrations/cloud-storage/azure-blob-storage.mdx)
- [CREATE SECRET](/sql-reference/motherduck-sql-reference/create-secret.md)
- [Microsoft: Use Iceberg REST catalog clients with OneLake](https://learn.microsoft.com/en-us/fabric/onelake/table-apis/iceberg-table-apis-get-started)
- [Microsoft: OneLake table APIs overview](https://learn.microsoft.com/en-us/fabric/onelake/table-apis/table-apis-overview)


---

## 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=%2Fintegrations%2Fcloud-storage%2Fmicrosoft-fabric-onelake%2F&page_title=Microsoft%20Fabric%20OneLake&text=<url-encoded user feedback, max 2000 characters>
```

Optionally append `&source=<url-encoded interface identifier>` such as `claude.ai` or `chatgpt`.

`page_path` and `text` are required; `page_title` and `source` are optional. Responses: `200 {"feedback_id": "<uuid>"}`, `400` for malformed query parameters, and `429` when rate-limited.
