` database.
After you establish the connection, either the default database or the one you specify becomes the current database.
You can run the `USE` command to switch the current database, as shown in the following example.
```python
#list the current database
con.sql("SELECT current_database()").show()
# ('database1')
#switch the current database to database2
con.sql("USE database2")
```
To query a table in the current database, you can specify just the table name. To query a table in a different database, you can include the database name when you specify the table. You don't need to switch the current database. The following examples demonstrate each method.
```sql
#querying a table in the current database
con.sql("SELECT count(*) FROM mytable").show()
#querying a table in another database
con.sql("SELECT count(*) FROM another_db.another_table").show()
```
---
Source: https://motherduck.com/docs/getting-started/interfaces/client-apis/python/index
# Python
> Connect and query MotherDuck from Python
Learn how to connect to MotherDuck and query your data using Python.
## Included pages
- [DuckDB Python installation and authentication](https://motherduck.com/docs/getting-started/interfaces/client-apis/python/installation-authentication): How to install DuckDB and connect to MotherDuck
- [Specify MotherDuck database](https://motherduck.com/docs/getting-started/interfaces/client-apis/python/choose-database): Specify MotherDuck database
- [Loading data into MotherDuck with Python](https://motherduck.com/docs/getting-started/interfaces/client-apis/python/loading-data-into-md): Load CSV, Parquet, and JSON files into MotherDuck from local, S3, or HTTPS sources using Python.
- [Query data](https://motherduck.com/docs/getting-started/interfaces/client-apis/python/query-data): Execute SQL queries against MotherDuck using Python with hybrid local and cloud execution.
---
Source: https://motherduck.com/docs/getting-started/interfaces/client-apis/python/installation-authentication
# Installation & authentication
> How to install DuckDB and connect to MotherDuck
## Prerequisites
MotherDuck Python supports the following operating systems:
- Linux (x64, glibc v2.31+, equivalent to ubuntu v20.04+)
- Mac OSX 11+ (M1/ARM or x64)
- Python 3.4 or later
Please let us know if your configuration is unsupported.
## Installing DuckDB
:::note
MotherDuck supports DuckDB client versions 1.4.1 through 1.5.5 in all regions. For the range each region supports, see [client version support](/about-motherduck/cloud-regions/#client-version-support).
:::
Use the following `pip` command to install the supported version of DuckDB:
{`pip install duckdb==${ duckdbVersionRanges["us-east-1"].max }`}
## Connect to MotherDuck
You can connect to and work with multiple local and MotherDuck-hosted DuckDB databases at the same time. The connection syntax varies depending on how you’re opening local DuckDB and MotherDuck.
### Authenticating to MotherDuck
You can authenticate to MotherDuck using either browser-based authentication or an access token. Here are examples of both methods:
#### Using browser-based authentication
```python
import duckdb
# connect to MotherDuck using 'md:' or 'motherduck:'
con = duckdb.connect('md:')
```
When you run this code:
1. A URL and a code will be displayed in your terminal.
2. Your default web browser will automatically open to the URL.
3. You'll see a confirmation request to approve the connection.
4. Once, approved, if you're not already logged in to MotherDuck, you'll be prompted to do so.
5. Finally, you can close the browser tab and return to your Python environment.
This method is convenient for interactive sessions and doesn't require managing access tokens.
#### Using an access token
For automated scripts or environments where browser-based auth isn't suitable, you can use an access token:
```python
import duckdb
# Initiate a MotherDuck connection using an access token
con = duckdb.connect('md:?motherduck_token=')
```
Replace `` with an actual token generated from the MotherDuck UI.
To learn more about creating and managing access tokens, as well as other authentication options, see our guide on [Authenticating to MotherDuck](/key-tasks/authenticating-and-connecting-to-motherduck/authenticating-to-motherduck/authenticating-to-motherduck.md).
### Connecting to MotherDuck
Once you've authenticated, you can connect to MotherDuck and start working with your data. Let's look at a few common scenarios.
#### Connecting directly to MotherDuck
Here's how to connect to MotherDuck and run a simple query:
```python
import duckdb
# Connect to MotherDuck via browser-based authentication
con = duckdb.connect('md:my_db')
# Run a query to verify the connection
con.sql("SHOW DATABASES").show()
```
:::tip
When connecting to MotherDuck, you need to specify a database name (like `my_db` in the example). If you're a new user, a default database called `my_db` is automatically created when your account is first set up. You can query any table in your connected database by just using its name. To switch databases, use the `USE` command.
:::
#### Working with both MotherDuck and local databases
MotherDuck lets you work with both cloud and local databases simultaneously. Here's how:
````python
import duckdb
# Connect to MotherDuck first, specifying a database
con = duckdb.connect('md:my_db')
# Then attach local DuckDB databases
con.sql("ATTACH 'local_database1.duckdb'")
con.sql("ATTACH 'local_database2.duckdb'")
# List all connected databases
con.sql("SHOW DATABASES").show()
````
#### Adding MotherDuck to an existing local connection
If you're already working with a local DuckDB database, you can add a MotherDuck connection:
````python
import duckdb
# Start with a local DuckDB database
local_con = duckdb.connect('local_database.duckdb')
# Add a MotherDuck connection, specifying a database
local_con.sql("ATTACH 'md:my_db'")
````
This is another approach to give you the flexibility to work with both local and cloud data in the same session.
---
Source: https://motherduck.com/docs/getting-started/interfaces/client-apis/python/loading-data-into-md
# Loading data into MotherDuck with Python
> Load CSV, Parquet, and JSON files into MotherDuck from local, S3, or HTTPS sources using Python.
## Copying a table from a local DuckDB database into MotherDuck
You can use `CREATE TABLE AS SELECT` to load CSV, Parquet, and JSON files into MotherDuck from either local, Amazon S3, or https sources as shown in the following examples.
```python
# load from local machine into table mytable of the current/active used database
con.sql("CREATE TABLE mytable AS SELECT * FROM '~/filepath.csv'");
# load from an S3 bucket into table mytable of the current/active database
con.sql("CREATE TABLE mytable AS SELECT * FROM 's3://bucket/path/*.parquet'")
```
If the source data matches the table’s schema exactly you can also use `INSERT INTO ... SELECT` to append data, as shown in the following example.
```python
# append to table mytable in the currently selected database from S3
con.sql("INSERT INTO mytable SELECT * FROM ‘s3://bucket/path/*.parquet’")
```
:::tip
Use `INSERT INTO ... SELECT` to load data from files as shown above. Do not use single-row `INSERT INTO ... VALUES` statements in a loop — this is significantly slower because each statement incurs separate network overhead. See [Loading data best practices](/key-tasks/loading-data-into-motherduck/considerations-for-loading-data/) for more detail.
:::
## Copying an entire local DuckDB database to MotherDuck
MotherDuck supports copying your opened DuckDB database into a MotherDuck database. The following example copies a local DuckDB database named `localdb` into a MotherDuck-hosted database named `clouddb`.
```python
# open the local db
local_con = duckdb.connect("localdb.ddb")
# connect to MotherDuck
local_con.sql("ATTACH 'md:'")
# The from indicates the file to upload. An empty path indicates the current database
local_con.sql("CREATE DATABASE clouddb FROM CURRENT_DATABASE()")
```
A local DuckDB database can also be copied by its file path:
```sql
local_con = duckdb.connect("md:")
local_con.sql("CREATE DATABASE clouddb FROM 'localdb.ddb'")
```
See [Loading Data into MotherDuck](/key-tasks/loading-data-into-motherduck/loading-data-into-motherduck.mdx) for more detail.
---
Source: https://motherduck.com/docs/getting-started/interfaces/client-apis/python/query-data
# Query data
> Execute SQL queries against MotherDuck using Python with hybrid local and cloud execution.
For more information about database manipulation, see [MotherDuck SQL reference](/docs/sql-reference/motherduck-sql-reference/).
MotherDuck uses DuckDB under the hood, so nearly all [DuckDB SQL](https://duckdb.org/docs/) works in MotherDuck without differences.
MotherDuck uses [Dual Execution](/concepts/architecture-and-capabilities/#dual-execution) to decide where each part of a query runs, including across more than one location at once. If your data lives on your laptop, MotherDuck runs the query against that data on your laptop. If you are joining data on your laptop to data on Amazon S3, MotherDuck runs each part of the query where the data lives before bringing the results together locally.
## Querying data in MotherDuck
You can query data loaded into MotherDuck the same way you query data in your DuckDB databases. MotherDuck executes these queries using resources in the cloud.
```sql
# table table_name is in MotherDuck storage
con.sql("SELECT * FROM table_name").show();
```
## Querying data on your machine
You can use MotherDuck to query files on your local machine. These queries execute using your machine's resources.
```sql
# query a Parquet file on your local machine
con.sql("SELECT * FROM '~/file.parquet'").show();
# query a table in a local DuckDB database
con.sql("SELECT * FROM local_table").show();
```
## Joining data across multiple locations
You can use MotherDuck to join data:
- In MotherDuck
- On S3 or other cloud object stores (Azure, GCS, R2, etc)
- On your local machine
## What's next ?
Ready to share your DuckDB data with your colleagues? Read up on [Sharing In MotherDuck](/key-tasks/sharing-data/sharing-data.mdx).
---
Source: https://motherduck.com/docs/getting-started/interfaces/connect-query-from-duckdb-cli
# DuckDB CLI
> Learn to connect and query databases using MotherDuck from the DuckDB CLI
## Installation
:::note
MotherDuck supports DuckDB client versions 1.4.1 through 1.5.5 in all regions. For the range each region supports, see [client version support](/about-motherduck/cloud-regions/#client-version-support).
:::
Download and install the DuckDB binary, depending on your operating system.
### Windows
The recommended way to install the CLI is with the MotherDuck install script:
### Install with PowerShell
```powershell
powershell -c "irm https://install.motherduck.com | iex"
```
The script installs a MotherDuck-supported DuckDB version to `%LOCALAPPDATA%\duckdb\cli`, installs the `motherduck` extension, and can fetch and persist a MotherDuck token.
If your PowerShell execution policy blocks the command above, use the `cmd.exe` fallback:
```bat
curl -sfL -o install.bat https://install.motherduck.com/install.bat && install.bat
```
The `cmd.exe` script installs the `windows-amd64` build only and cannot run the interactive token flow. On ARM64, or to use the token flow, use the PowerShell script.
### Download the binary
To install manually instead:
1. Download the 64-bit Windows binary [duckdb_cli-windows-amd64.zip](https://github.com/duckdb/duckdb/releases/download/v1.5.5/duckdb_cli-windows-amd64.zip)
2. Extract the zip file.
### macOS
The recommended way to install the CLI is with the MotherDuck install script:
### Install with bash
```bash
curl -s https://install.motherduck.com | sh
```
### Linux
The recommended way to install the CLI is with the MotherDuck install script:
### Install with sh
```bash
curl -s https://install.motherduck.com | sh
```
The script detects your architecture, installs the matching `linux-amd64` or `linux-arm64` binary, and pins a MotherDuck-supported DuckDB version.
### Download the binary
To install manually instead:
1. Download the Linux binary:
- For 64-bit, download the binary [duckdb_cli-linux-amd64.zip](https://github.com/duckdb/duckdb/releases/download/v1.5.5/duckdb_cli-linux-amd64.zip)
- For arm64/aarch64, download the binary [duckdb_cli-linux-aarch64.zip](https://github.com/duckdb/duckdb/releases/download/v1.5.5/duckdb_cli-linux-aarch64.zip)
2. Extract the zip file.
For more information, see the [DuckDB installation documentation](https://duckdb.org/docs/installation/).
## Try it
Walk through starting DuckDB, attaching MotherDuck, and running your first query in the playground below. Each step explains what happens before you press Enter, so you can preview the full flow before running it on your machine.
Interactive CLI demo omitted from generated Markdown.
Static walkthrough:
```bash
duckdb
ATTACH 'md:';
SHOW DATABASES;
FROM duckdb_tables() WHERE database_name = 'sample_data';
```
## Step by step
### Start the DuckDB CLI
After installing, start DuckDB from your terminal:
```sh
duckdb
```
DuckDB opens an in-memory database by default, so any tables you create won't persist when you exit. Pass a filename to open or create a persistent local database:
```sh
duckdb mydatabase.duckdb
```
### Connect to MotherDuck
From inside the DuckDB CLI, attach MotherDuck:
```sql
ATTACH 'md:';
```
DuckDB downloads the signed MotherDuck extension and opens your default browser to authenticate. Follow the instructions in the terminal.
To list your MotherDuck databases and confirm the connection, run:
```sql
SHOW DATABASES;
```
You can query local DuckDB data and MotherDuck databases from the same session.
For more on persisting your authentication credentials, see [Authenticating to MotherDuck](/key-tasks/authenticating-and-connecting-to-motherduck/authenticating-to-motherduck/authenticating-to-motherduck.md).
:::tip
You can also connect to MotherDuck directly when starting DuckDB:
```bash
duckdb "md:"
```
:::
:::note[Manual extension update]
When MotherDuck releases a new extension version you can force-reinstall the extension from the CLI.
```sh
FORCE INSTALL motherduck;
```
:::
### Open the MotherDuck UI from the CLI
Launch the MotherDuck UI from your terminal:
```bash
duckdb -ui
```
If you're already in a DuckDB session, run `CALL start_ui();` instead.
---
Source: https://motherduck.com/docs/getting-started/interfaces/interfaces
# MotherDuck Interfaces
> MotherDuck Offers a variety of interfaces (APIs) for integration
## Client interfaces
## Included pages
- [Client APIs](https://motherduck.com/docs/getting-started/interfaces/client-apis): Client APIs for MotherDuck
- [MotherDuck CLI](https://motherduck.com/docs/getting-started/interfaces/motherduck-cli): Drive MotherDuck from your terminal: run queries, build Dives and Flights, and script it all with JSON output.
- [Install and connect with the DuckDB CLI](https://motherduck.com/docs/getting-started/interfaces/connect-query-from-duckdb-cli): Learn to connect and query databases using MotherDuck from the DuckDB CLI
- [MotherDuck Web UI](https://motherduck.com/docs/getting-started/interfaces/motherduck-quick-tour): A guide to the MotherDuck Web UI — write SQL with Instant SQL, use AI to fix and edit queries, and explore your data interactively.
- [Postgres endpoint](https://motherduck.com/docs/getting-started/interfaces/postgres-endpoint): Query MotherDuck from any Postgres-compatible client without installing DuckDB
- [Third-Party Tools with PostgreSQL](https://motherduck.com/docs/getting-started/interfaces/third-party-tools): Connect third-party tools and IDEs to MotherDuck using the Postgres wire protocol endpoint
---
Source: https://motherduck.com/docs/getting-started/interfaces/motherduck-cli/agents
# Work with agents
> Let an AI agent author Dives and Flights through the MotherDuck CLI, using the built-in authoring guides and JSON output.
The MotherDuck CLI is designed for both AI agents and people.
An agent can read authoring guides to learn how to best build Dives and Flights with the CLI.
Because the CLI works through files and stdout rather than tool results, it
handles large files and multi-step automation with far less context than the
[MCP server](../../../key-tasks/ai-and-motherduck/mcp-setup.mdx), which is the
better fit for exploring data from a chat client. See
[choosing between the CLI and MCP](#choosing-between-the-cli-and-mcp).
Whether through an agent, in your local development environment, or in CI, the
CLI lets you create, publish, and automate your MotherDuck workflows with output
both humans and machines can understand.
## Point the agent at the built-in guides
`motherduck dive guide` and `motherduck flight guide` print the authoring
guide for each. They cover the shape the runtime requires, the query APIs, the
libraries you can import, and the patterns that don't work.
```bash
motherduck dive guide
motherduck flight guide
```
These guides are long and specific, which is what an agent needs. Have the agent
run the relevant one before it writes any code, and you avoid the usual failure
where a model invents a component or an import the runtime doesn't have.
:::tip
Put the instruction in your project's agent memory file, such as `CLAUDE.md` or
`AGENTS.md`, so it applies to every session:
```markdown
Before writing or editing a Dive or a Flight, get the latest instructions from
the output of running `motherduck [dive | flight] guide`.
```
:::
## Give the agent a task
With the guides available, the prompt can stay short. Ask for the outcome and
let the agent discover the rest:
```text
Build a Dive that charts daily taxi trip counts and average fare for
November 2022 from sample_data.nyc.taxi, with a day-of-week filter.
Preview it locally, and once it renders, publish it.
```
A capable agent works through something close to this:
```bash
motherduck dive guide # read the authoring guide
motherduck query "DESCRIBE sample_data.nyc.taxi" --output json
motherduck dive init taxi_trips --title "Taxi trips" # scaffold the directory
# ... writes index.tsx ...
motherduck dive watch taxi_trips --no-open # render it, read the events
motherduck dive push taxi_trips --output json # publish, capture the URL
```
`--no-open` keeps the preview from stealing focus, and `--log-file` writes
render and query outcomes as NDJSON so the agent can read whether its component
compiled instead of asking you to look:
```bash
motherduck dive watch taxi_trips --no-open --log-file preview.ndjson
```
## JSON output everywhere for programmatic use
The `--output json` option makes the CLI's output easy to parse
programmatically. Commands that act on a Dive or a Flight return it under a key
named for the resource, described under
[result shape](/sql-reference/motherduck-cli/#result-shape):
```bash
motherduck dive push taxi_trips --output json
```
```json
{
"success": true,
"dive": {
"id": "123e4567-e89b-12d3-a456-426614174000",
"title": "Taxi trips",
"version": 2,
"url": "https://app.motherduck.com/dives/taxi-trips-123e4567-e89b-12d3-a456-426614174000"
}
}
```
So a script reads one field instead of the whole message:
```bash
motherduck dive push taxi_trips --output json | jq -r '.dive.url'
```
A failure prints `{"success": false, "error": "..."}` and exits non-zero, so an
agent checks one field rather than reading prose.
:::note
The `success` field doesn't appear in the output of
[`query`](/sql-reference/motherduck-cli/query/), which returns its rows as a
bare JSON array. See
[output formats](/sql-reference/motherduck-cli/#output-formats).
:::
That's what lets an agent chain steps in a script rather than in its context
window. Each command hands the next one a single field, so a multi-step
workflow costs a few tokens instead of a transcript of full outputs:
```bash
#!/usr/bin/env bash
set -euo pipefail
# Trigger a Flight, then wait for the run to settle.
RUN=$(motherduck flight run nightly_load --output json | jq -r '.run.run_number')
while :; do
STATUS=$(motherduck flight list-runs nightly_load --limit 1 --output json \
| jq -r '.runs[0].status')
[[ "$STATUS" == "PENDING" || "$STATUS" == "RUNNING" ]] || break
sleep 10
done
# On failure, surface the reason and stop.
if [[ "$STATUS" != "SUCCEEDED" ]]; then
motherduck flight logs nightly_load --run "$RUN" | tail -20 >&2
exit 1
fi
# The data landed, so publish a Dive over it.
motherduck dive push daily_totals --output json | jq -r '.dive.url'
```
The agent writes that once and reads one URL back, instead of holding every
intermediate result in its context.
## Give the run its own credentials
Pass a token rather than running the browser flow, and point the CLI at a
directory of its own:
```bash
export MOTHERDUCK_TOKEN=
export MOTHERDUCK_HOME=/workspace/.motherduck
```
`MOTHERDUCK_HOME` gives the run its own credentials and asset directory, which
keeps parallel agents from sharing state. It has to be an absolute path.
Where there's no account to get a token from,
[`motherduck new`](/sql-reference/motherduck-cli/new/) creates one from the terminal without a
browser or a signup form.
:::warning
An agent with a MotherDuck token can read and write whatever that token can.
Scope it to what the task needs, and prefer a read-only token for agents that
only query. See [securing read-only access](../../../key-tasks/ai-and-motherduck/securing-read-only-access.mdx).
:::
## Choosing between the CLI and MCP
Both let an agent work with MotherDuck. The deciding question is whether the
agent has a shell and a filesystem:
- **The CLI** fits agents that run commands and write files: a coding agent
building a Dive or a Flight in a repository, a CI job, or a shell script.
- **[The MCP server](../../../key-tasks/ai-and-motherduck/mcp-setup.mdx)** fits
agents in a chat client with no shell, such as Claude or ChatGPT on the web.
Use it to explore data, answer a question, and render a Dive inline in the
conversation.
They work together: an agent can explore through MCP, then use the CLI to build
and publish what it found.
### Why the CLI costs fewer tokens for file-shaped work
An MCP tool result is a message. Whatever the server returns, a Dive's component
code, a Flight's source, a list of every Dive in the workspace, or a thousand
query rows, is serialized into the model's context. It takes up the context
window and gets resent on every turn that follows.
The CLI writes to stdout or to files on disk, and the agent picks what to read
back. It can filter a listing through `jq`, read only the function it's changing
out of a Dive it pulled, or hand a file straight to the next command. Only what
the agent reads reaches the context window.
So for anything file-shaped, prefer the CLI:
| Task | Through MCP | Through the CLI |
|---|---|---|
| Read a Dive or a Flight | `read_dive` or `get_flight` returns the whole source in the response | `dive pull` or `flight pull` writes the files to disk, and the agent reads the part it needs |
| Save an edit | The agent sends the changed content back as a tool argument | The agent edits the file in place, and `dive push` or `flight push` reads it from disk |
| List Dives or Flights | `list_dives` or `list_flights` returns every field of every result | `dive list --output json` piped through `jq` returns the IDs alone |
| Return a large result set | Every row lands in the context window | Redirect it: `motherduck query "..." --output csv > result.csv` |
| Chain several steps | Each intermediate result passes through the model | One shell script hands each command's output to the next |
The gap widens the more you iterate. Pull a Dive once and the local file carries
every revision after that, so the agent patches a few lines instead of moving
the whole component through the conversation twice per round.
---
Source: https://motherduck.com/docs/getting-started/interfaces/motherduck-cli/authentication
# Authentication
> Sign the MotherDuck CLI in through your browser, on a headless machine, or with a token in CI.
The CLI needs a credential before it can do anything but print help. There are
two ways to give it one, and which fits depends on who's at the keyboard.
| Approach | Use it when |
|---|---|
| [Sign in](#signing-in) with `motherduck login` | You have an account, or you're about to [sign up](https://app.motherduck.com/) for one |
| [Set a token](#using-access-tokens-in-unattended-environments) | An unattended run needs credentials: CI, a container, a scheduled job |
| [`motherduck new`](/sql-reference/motherduck-cli/new/) | There's no account to sign in to yet, and you want one from the terminal |
## Signing in
```bash
motherduck login
```
This opens your browser, completes an OAuth device flow, and saves the token to
`~/.motherduck/credentials.json`, in plain text. Later commands read it from
there, so you sign in once per machine, and
[`motherduck logout`](#signing-out) deletes the file.
:::tip
Set `MOTHERDUCK_HOME` to override where the credential files and the asset
cache are stored. This gives parallel runs in CI and sandboxes an isolated
environment each.
```bash
export MOTHERDUCK_HOME=/workspace/.motherduck
```
:::
On a machine with no browser, start the headless login flow. Open the printed
sign in URL on any other device, then resume:
```bash
motherduck login --headless
motherduck login --device-code
```
The first command prints a device code and returns rather than polling. Pass
that code to the second command to complete the sign in.
Check the result at any time:
```bash
motherduck status
```
## Using access tokens in unattended environments
For CI and other unattended runs, set a token rather than signing in. The CLI
reads `MOTHERDUCK_TOKEN` before it looks at the saved credentials, so it wins
wherever both exist:
```bash
export MOTHERDUCK_TOKEN=
```
`motherduck status` reports which credential is active, under **Token source**. When
a command touches an account you didn't expect, read that row first.
## Signing out
```bash
motherduck logout
```
This removes the saved token. It has no effect on `MOTHERDUCK_TOKEN`, so unset
that variable too if you set it.
## Related
- [`login`](/sql-reference/motherduck-cli/login/), [`logout`](/sql-reference/motherduck-cli/logout/), and [`status`](/sql-reference/motherduck-cli/status/) in the command reference
- [`new`](/sql-reference/motherduck-cli/new/) creates an account and organization when there isn't one to sign in to
- [Securing read-only access](../../../key-tasks/ai-and-motherduck/securing-read-only-access.mdx)
---
Source: https://motherduck.com/docs/getting-started/interfaces/motherduck-cli/index
# MotherDuck CLI
> Drive MotherDuck from your terminal: run queries, build Dives and Flights, and script it all with JSON output.
The MotherDuck CLI drives MotherDuck from your terminal. Use it to sign in, run
queries, and build [Dives](/key-tasks/dives/) and
[Flights](/key-tasks/flights/) without leaving your editor.
It's built for people and for AI agents alike. Every command that returns
structured results takes `--output json`. Agents can create and publish a Dive
or a Flight from local files, and read the built-in guides for working with
Dives and Flights.
```bash
curl -s https://install.motherduck.com | SKIP_DUCKDB_CLI=1 sh
motherduck login
motherduck query "SELECT count(*) FROM sample_data.nyc.taxi"
```
## Where to start
| Page | What it covers |
|---|---|
| [Install and upgrade](./install.md) | Getting the CLI onto macOS, Linux, or Windows, and keeping it current |
| [Authentication](./authentication.md) | Signing in, and using tokens in CI |
| [Quickstart](./quickstart.md) | A full workflow: query your data, build a Dive, publish it, and script it with JSON output |
| [Working with agents](./agents.md) | Letting an AI agent author Dives and Flights through the CLI |
| [Command reference](/sql-reference/motherduck-cli/) | Every command, argument, and option |
## Commands
| Command | What it does |
|---|---|
| [`dive`](/sql-reference/motherduck-cli/dive/) | Build, preview, and publish Dives |
| [`flight`](/sql-reference/motherduck-cli/flight/) | Build Flights, then schedule and operate their runs |
| [`login`](/sql-reference/motherduck-cli/login/), [`logout`](/sql-reference/motherduck-cli/logout/) | Sign in through your browser, or remove the saved token |
| [`new`](/sql-reference/motherduck-cli/new/) | Create a MotherDuck account and organization, and sign in with it |
| [`query`](/sql-reference/motherduck-cli/query/) | Run SQL and write the results to stdout |
| [`status`](/sql-reference/motherduck-cli/status/) | Show who you're signed in as and what you're connected to |
| [`upgrade`](/sql-reference/motherduck-cli/upgrade/) | Move to the latest CLI release |
Run `motherduck --help`, or `motherduck --help`, to get the same
information at the terminal. The deepest level carries the examples.
---
Source: https://motherduck.com/docs/getting-started/interfaces/motherduck-cli/install
# Install and upgrade
> Install the MotherDuck CLI on macOS, Linux, or Windows, keep it current with motherduck upgrade, and control where it stores its files.
## Quick install
### macOS
```bash
curl -s https://install.motherduck.com | SKIP_DUCKDB_CLI=1 sh
```
Runs on Apple silicon (aarch64) and Intel (x86_64).
### Linux
```bash
curl -s https://install.motherduck.com | SKIP_DUCKDB_CLI=1 sh
```
Runs on aarch64 and x86_64, and needs glibc. Only glibc builds are published,
so musl-based distributions such as Alpine stop with an error rather than a
failed exec. 32-bit hosts do the same.
### Windows
```powershell
powershell -c "$env:SKIP_DUCKDB_CLI=1; irm https://install.motherduck.com | iex"
```
Runs on aarch64 and x86_64. Where PowerShell's execution policy blocks this,
see [Windows without PowerShell](#windows-without-powershell).
The installer downloads the build for your platform, installs it under
`~/.motherduck/`, and puts it on your `PATH`.
Open a new shell so the `PATH` change applies, then check the install:
```bash
motherduck --version
```
:::note
The MotherDuck CLI bundles the DuckDB library. The minimal version of DuckDB bundled is the supported DuckDB version.
:::
## Windows without PowerShell
On hosts where PowerShell's execution policy blocks the quick install,
`install.bat` installs the DuckDB CLI only, then points at the PowerShell
installer for the MotherDuck CLI:
```bat
curl -sfL -o install.bat https://install.motherduck.com/install.bat && install.bat
```
It can't detect ARM64 or install the MotherDuck CLI, so use the PowerShell
script wherever you can.
## Upgrade
```bash
motherduck upgrade
```
Replaces the MotherDuck CLI binary on your `PATH`. It does not upgrade the
DuckDB CLI in `~/.duckdb/`, which has its own version: update that through
[DuckDB's own installation](/getting-started/interfaces/connect-query-from-duckdb-cli.mdx#installation).
On a CLI that's already current, `upgrade` says so rather than downloading
again.
## Next steps
- [Sign in](./authentication.md), or create an account with `motherduck new`
- [Quickstart](./quickstart.md)
- [Command reference](/sql-reference/motherduck-cli/)
---
Source: https://motherduck.com/docs/getting-started/interfaces/motherduck-cli/quickstart
# Quickstart
> Query MotherDuck from the terminal, build a Dive from the result, publish it, and script the whole thing with JSON output.
This walkthrough goes from an empty terminal to a published Dive: you'll
explore data with `motherduck query`, save a result as a table, build a small
React app on top of it, and publish it. The last section shows how to drive the
same commands from a script with `--output json`.
It takes about ten minutes.
## Before you begin
[Install the CLI](./install.md) and sign in:
```bash
motherduck login
```
Without a MotherDuck account, [`motherduck new`](/sql-reference/motherduck-cli/new/) creates
one from the terminal and leaves you signed in to it.
Confirm which account you're working in:
```bash
motherduck status
```
This walkthrough uses `sample_data`, which is attached to every account, and
writes one table into your default database, `my_db`.
## Step 1: Explore the data
`motherduck query` runs SQL and writes the result to stdout. Start by looking
at what's in the sample taxi table:
```bash
motherduck query "DESCRIBE sample_data.nyc.taxi"
```
Then shape the numbers you want to chart, daily trip counts and average fares
for one month:
```bash
motherduck query "
SELECT strftime(tpep_pickup_datetime, '%Y-%m-%d') AS trip_day,
count(*) AS trips,
round(avg(fare_amount), 2) AS avg_fare
FROM sample_data.nyc.taxi
WHERE tpep_pickup_datetime >= '2022-11-01'
AND tpep_pickup_datetime < '2022-12-01'
GROUP BY ALL
ORDER BY trip_day
LIMIT 5
"
```
That prints one row per day, with the trip count and average fare.
Long statements are easier to keep in a file. `--file` reads one, and
`--timeout` raises the 120-second default when a statement needs it:
```bash
motherduck query --file daily_trips.sql --timeout 600
```
## Step 2: Save the result as a table
A Dive queries MotherDuck live, so give it something to read. Drop the `LIMIT`
and write the result into `my_db`:
```bash
motherduck query "
CREATE OR REPLACE TABLE my_db.main.taxi_daily AS
SELECT strftime(tpep_pickup_datetime, '%Y-%m-%d') AS trip_day,
count(*) AS trips,
round(avg(fare_amount), 2) AS avg_fare
FROM sample_data.nyc.taxi
WHERE tpep_pickup_datetime >= '2022-11-01'
AND tpep_pickup_datetime < '2022-12-01'
GROUP BY ALL
"
```
## Step 3: Scaffold the Dive
```bash
motherduck dive init taxi_trips --title "Taxi trips"
```
That creates `taxi_trips/`, holding the component and its metadata file.
Nothing has reached MotherDuck yet.
## Step 4: Write the component
Replace `taxi_trips/index.tsx` with a chart over the table you created:
```tsx
import { useSQLQuery } from '@motherduck/react-sql-query';
import { Bar, BarChart, ResponsiveContainer, Tooltip, XAxis, YAxis } from 'recharts';
export const REQUIRED_DATABASES = [
{ type: 'database', path: 'md:my_db', alias: 'my_db' },
];
const N = (value: unknown): number => (value == null ? 0 : Number(value));
export default function TaxiTrips() {
const dailyQuery = useSQLQuery(`
SELECT trip_day, trips, avg_fare
FROM "my_db"."main"."taxi_daily"
ORDER BY trip_day
`);
const rows = Array.isArray(dailyQuery.data) ? dailyQuery.data : [];
const chartData = rows.map((row) => ({
day: String(row.trip_day),
trips: N(row.trips),
}));
return (
NYC taxi trips, November 2022
{dailyQuery.isLoading ? (
Loading trips...
) : (
)}
);
}
```
`REQUIRED_DATABASES` is the part `push` reads. It takes the Dive's dependency
list from that export, so there's nothing to keep in step by hand.
The rest — the query API, the numeric conversion, the quoted table name —
follows the Dive authoring guide. Run `motherduck dive guide` before writing or
editing a Dive. It ships with the CLI, so it describes the runtime you actually
have.
## Step 5: Preview it locally
```bash
motherduck dive watch taxi_trips
```
This serves the Dive at `http://127.0.0.1:5173` and re-renders it on every
save, against your live MotherDuck data. Edit `index.tsx` and watch the chart
change. `--port` picks another port, and `--no-open` leaves the browser alone.
## Step 6: Publish it
```bash
motherduck dive push taxi_trips
```
The first push creates the Dive, records its ID in `dive.metadata.json`, and
prints the URL to open. Every later push adds a version:
```bash
motherduck dive push taxi_trips --version-description "add the fare axis"
motherduck dive list-versions taxi_trips
```
## Step 7: Read the output as JSON
Everything above also works unattended. `-o json` names the resource a command
acted on, so a script can pull one value out with `jq`:
```bash
DIVE_URL=$(motherduck dive push taxi_trips -o json | jq -r '.dive.url')
echo "Published to $DIVE_URL"
```
`query` is the exception, returning rows as a bare array. Failures exit
non-zero across every command, with an error object in place of the result. See
[output formats](/sql-reference/motherduck-cli/#output-formats) for the shapes.
Because the exit code is meaningful, a query can gate the rest of a script:
```bash
if ! motherduck query --file checks.sql -o json > result.json; then
echo "checks failed" >&2
exit 1
fi
```
`csv` suits results that are naturally tabular:
```bash
motherduck query "SELECT * FROM my_db.main.taxi_daily" -o csv > taxi_daily.csv
motherduck dive list -o csv > dives.csv
```
In CI, skip `motherduck login` and pass a token instead. See
[authentication](./authentication.md#using-access-tokens-in-unattended-environments).
## Clean up
```bash
motherduck dive delete --dive
motherduck query "DROP TABLE my_db.main.taxi_daily"
```
`dive delete` asks you to confirm. Your local `taxi_trips/` directory stays
where it is.
## Next steps
- [Command reference](/sql-reference/motherduck-cli/) for every command and option
- [`flight`](/sql-reference/motherduck-cli/flight/) to run a Python pipeline on a schedule
- [Working with agents](./agents.md) to let an AI agent do all of this
- [Dives](/key-tasks/dives/) for theming, embedding, and governance
---
Source: https://motherduck.com/docs/getting-started/interfaces/motherduck-quick-tour
# MotherDuck Web UI
> A guide to the MotherDuck Web UI — write SQL with Instant SQL, use AI to fix and edit queries, and explore your data interactively.
## Getting started
To log in to the MotherDuck UI, go to [app.motherduck.com](https://app.motherduck.com/).
:::info
You can also open the web UI directly from the DuckDB CLI:
```bash
duckdb "md:" -ui
```
:::
### Main window
The MotherDuck UI is organized around a notebook-style editor with a database browser on the left and results inspection on the right.
## Instant SQL: write SQL with real time feedback
**Instant SQL** gives you keystroke-fast query previews — results update as you type, with no run button needed.
Under the hood, MotherDuck uses [Dual Execution](/concepts/architecture-and-capabilities/#dual-execution) to parse and run your query locally first, giving you immediate feedback while full cloud results load in the background. A caching indicator in the cell header shows when results are served from local cache.
### Enabling Instant SQL
Toggle Instant SQL on or off per cell using:
- The **Instant SQL toggle** in the cell header
- The keyboard shortcut `Ctrl`/`⌘` + `Shift` + `.`
### What works with Instant SQL
- **Filtering in real time:** Add or change a `WHERE` clause and watch results narrow instantly.
- **Multi-statement cells:** Click on any individual statement within a multi-statement cell to preview just that one.
- **Window functions:** Window functions are fully supported in Instant SQL previews.
## Fix errors and edit queries with AI
MotherDuck's AI features help you fix broken queries, rewrite SQL in plain English, and generate queries from scratch — all without leaving the editor.
### "Help me fix this broken query" — FixIt
When you run a query that has an error, **FixIt** automatically analyzes the error and suggests an inline fix. Click to accept and re-run in one step.
By default, FixIt auto-suggests fixes whenever an error occurs. You can turn off auto-suggest and still trigger FixIt manually by clicking **Suggest fix** at the bottom of any error message.
Toggle auto-suggest in **Settings → Preferences → Enable inline SQL error fix suggestions**.
:::tip[Free for all users]
FixIt is available on all plans, including the Lite plan (with limits).
:::
### "Modify my SQL using plain english" — edit
Select text in your query (or place your cursor anywhere) and press `Ctrl`/`⌘` + `Shift` + `E` to open the **Edit** dialog. Describe what you want to change in natural language:
Review the suggestion, then iterate with follow-up prompts if needed:
When you're happy with the result, click **Apply edit** to update your query.
### Going further with SQL assistant functions
For programmatic AI access (text-to-SQL, query explanation, schema understanding), see the [SQL Assistant functions](/sql-reference/motherduck-sql-reference/ai-functions/sql-assistant/) reference. These are available in any DuckDB client connected to MotherDuck, not just the web UI.
## Explore your results
### Interactive data grid
Query results load into an interactive data grid where you can sort, filter, and pivot without writing more SQL.
Click the **Expand** button at the top right of any cell to go full-screen on the editor and results.
### Column Explorer
The Column Explorer shows statistics for every column in a table or result set — value frequencies, NULL percentages, histograms for numeric columns, and time-series charts for timestamp columns.
Toggle the Column Explorer with `Ctrl`/`⌘` + `I` or the toggle button at the top right of the results panel.
### Cell content pane
Click any cell in the results grid to see its full contents in the Cell Content Pane.
For JSON columns, you can expand and collapse nodes, copy the value, or copy the key path to any nested field.
## Write queries faster
### Autocomplete
Autocomplete suggests SQL syntax, table names, column names, and functions as you type. Turn it off in **Settings → Preferences → Enable autocomplete when typing**.
### Inline docs
Hover over any SQL function in the editor to see its description, parameter types, and return type. Click the **Docs** link in the tooltip to open the full reference.
)
Turn off Inline Docs in **Settings → Preferences → Enable Inline Docs**.
### Format SQL
Press `Ctrl`/`⌘` + `Alt`/`⌥` + `O` to auto-format the SQL in your current cell. When text is selected, only the selection is formatted.
## Navigate the workspace
### Object explorer & Favorites
.src)
Browse your databases, schemas, and tables in the left-hand panel. Toggle it with `Ctrl`/`⌘` + `B`. Each section collapses on its own, so you can keep the tree focused on what you are working on.
Pin the objects you use most to a **Favorites** section at the top of the Object explorer. Hover a database, [share](/key-tasks/sharing-data/), notebook, or [Dive](/key-tasks/dives/) and click the star. Click the star again, or choose **Remove from favorites** in the row menu, to unpin it.
Use the new-folder button in the **Favorites** header to group related items, then drag rows into a folder or into the order you want. Favorites are personal to your account, so each member of an organization keeps their own set.
### Command menu
Press `Ctrl`/`⌘` + `K` to open the command menu for quick access to actions, notebooks, and settings.
### Notebook and worksheet views
Toggle between notebook view (multiple cells) and worksheet view (single expanded cell) with `Ctrl`/`⌘` + `E`.
### Running queries
The Running Queries page, found under **Settings** → **Running Queries**, lets you monitor and manage long-running queries on your Duckling. For each query, you can see:
- **Query**: The SQL text of the query (click to expand the full statement).
- **Status**: Whether the query is active or has completed.
- **Start time**: When the query started executing.
- **Elapsed time**: How long the query has been running.
This is useful for identifying queries that are taking longer than expected. You can cancel a running query directly from this page.
For programmatic access to active connections and query cancellation through SQL, see [`md_active_server_connections()`](/sql-reference/motherduck-sql-reference/connection-management/monitor-connections/) and [`md_interrupt_server_connection()`](/sql-reference/motherduck-sql-reference/connection-management/interrupt-connections/). For a broader view of query activity across your organization, see the [`RECENT_QUERIES`](/sql-reference/motherduck-sql-reference/md_information_schema/recent_queries/) and [`QUERY_HISTORY`](/sql-reference/motherduck-sql-reference/md_information_schema/query_history/) views.
### Duckling overview
The Duckling overview page, found under **Settings** → **Duckling overview**, gives you an at-a-glance view of activity across every Duckling in the organization over the last 24 hours. Viewing it requires permission to view organization-wide Duckling activity, which the Admin and Builder preset roles include by default. For each Duckling, you can see:
- **Account**: The MotherDuck user or service account the Duckling belongs to.
- **Status**: Whether the Duckling is running normally or has encountered errors.
- **Spills**: Whether queries on this Duckling spilled to disk, which indicates memory pressure from larger-than-memory workloads.
- **Active minutes**: How long the Duckling was actively running queries over the last 24 hours.
Click a Duckling row to drill in. A bar chart visualizes query activity over time, and a table below lists individual queries. Click a query to open a side panel with the full SQL text, or open a dedicated focus page for a single query.
Use the timezone toggle in the page header to switch between UTC and your local time.
This page requires permission to view organization-wide Duckling activity and is built on the [`QUERY_HISTORY`](/sql-reference/motherduck-sql-reference/md_information_schema/query_history/) view, so it has the same ingestion delay — queries from the last few seconds may not appear yet. The Admin and Builder preset roles include this permission by default. For a programmable view of the same data, or a more real-time view of ongoing queries, see the [`QUERY_HISTORY`](/sql-reference/motherduck-sql-reference/md_information_schema/query_history/) and [`RECENT_QUERIES`](/sql-reference/motherduck-sql-reference/md_information_schema/recent_queries/) views.
## Keyboard shortcuts
Use `Ctrl` for Windows/Linux and `⌘` (Command) for Mac. Use `Alt` for Windows/Linux and `⌥` (Option) for Mac.
### Running queries
| Command | Action |
|---------|--------|
| `Ctrl`/`⌘` + `Enter` | Run the current cell. |
| `Ctrl`/`⌘` + `Shift` + `Enter` | Run selected text in the current cell. If no text is selected, run the whole cell. |
| `Shift` + `Enter` or `Alt`/`⌥` + `Enter` | Run the current cell, then advance to the next cell (creates a new one if needed). |
### Editing
| Command | Action |
|---------|--------|
| `Ctrl`/`⌘` + `z` | Undo within current cell. |
| `Ctrl`/`⌘` + `Shift` + `z` | Redo within current cell. |
| `Ctrl`/`⌘` + `Alt`/`⌥` + `o` | Format SQL in the current cell (or selection). |
| `Ctrl`/`⌘` + `/` | Toggle line comments (`--`). |
| `Tab` | Indent current line (in editor). |
| `Shift` + `Tab` | De-indent current line (in editor). |
### AI features
| Command | Action |
|---------|--------|
| `Ctrl`/`⌘` + `Shift` + `.` | Toggle [Instant SQL](#instant-sql-write-sql-with-real-time-feedback) on/off for the active cell. |
| `Ctrl`/`⌘` + `Shift` + `e` | Open [Edit](#modify-my-sql-using-plain-english--edit) for your current cell or selected text. |
### Navigation and layout
| Command | Action |
|---------|--------|
| `Ctrl`/`⌘` + `k` | Open the command menu. |
| `Ctrl`/`⌘` + `/` | Search notebooks, databases and more. |
| `Ctrl`/`⌘` + `b` | Toggle the Object Explorer (left panel). |
| `Ctrl`/`⌘` + `i` | Toggle the Column Explorer (right panel). |
| `Ctrl`/`⌘` + `e` | Toggle notebook/worksheet view for the active cell. |
| `Ctrl`/`⌘` + `↑` | Move current cell up. |
| `Ctrl`/`⌘` + `↓` | Move current cell down. |
| `Esc` | Switch `Tab` to UI navigation mode (reverts on next cell selection). |
## Settings
Settings are found by clicking your profile at the top-left.
| Section | Setting | Description |
|---------|---------|-------------|
| **Organization** | Details | Changing the organization display name requires permission to update it, included in Admin by default. See [Managing organizations](/key-tasks/managing-organizations). |
| | Plans | Viewing invoices and selecting a plan each require the corresponding permission, included in Admin by default. |
| | Members | Viewing members and roles requires the corresponding permission, included in every preset role. Managing them requires separate permissions included in Admin. Invitations for Builder and Explorer depend on the invite policy. Members include human users and [service accounts](/key-tasks/service-accounts-guide/). |
| **My Account** | Preferences | Enable [autocomplete](#autocomplete), inline [SQL error fix suggestions](#help-me-fix-this-broken-query--fixit) (FixIt), and [Inline Docs](#inline-docs). |
| | Notifications | Configure notification preferences. |
| | Ducklings | Manage [Duckling sizes](/about-motherduck/billing/duckling-sizes/#duckling-sizes), [Read Scaling](/key-tasks/authenticating-and-connecting-to-motherduck/read-scaling/) pool size, version information, and Duckling reset for troubleshooting. |
| **Integrations** | Access Tokens | Create tokens for programmatically [authenticating to MotherDuck](/key-tasks/authenticating-and-connecting-to-motherduck/authenticating-to-motherduck). Tokens can have expiry dates. |
| | Secrets | Storing credentials requires permission to create secrets, while removing them requires permission to delete secrets. Admin and Builder include both permissions by default. See [AWS S3](/integrations/cloud-storage/amazon-s3), [Azure Blob Storage](/integrations/cloud-storage/azure-blob-storage), and [Google Cloud Storage](/integrations/cloud-storage/google-cloud-storage). |
| **Monitor** | Running Queries | View and manage active queries. |
| | Duckling overview | Viewing organization-wide Duckling activity requires the corresponding permission, included in Admin and Builder by default. See [Duckling overview](#duckling-overview). |
| **Data** | Databases | Browse and manage your databases. |
| | Shares | View and manage [shared databases](/key-tasks/sharing-data/). |
| **Content** | Dives | Manage your saved [Dives](/key-tasks/dives/). |
### Databases
Under **Data** → **Databases**, viewing every database in the organization requires permission to view all organization databases, which Admin includes by default. Without it, you see databases you own and shared databases you can access. Per-database [storage breakdowns](/concepts/storage-lifecycle#breaking-down-storage-usage) require a separate permission to view organization-wide storage information, also included in Admin by default. Click a row to view its lifecycle stages.
### Shares
Under **Data** → **Shares**, view and manage the databases you've [shared](/key-tasks/sharing-data/) and the ones shared with you.
### Access tokens
Under **Integrations** → **Access Tokens**, create and revoke tokens for [authenticating to MotherDuck](/key-tasks/authenticating-and-connecting-to-motherduck/authenticating-to-motherduck) from the CLI, Python, or other clients.
### Dives
Under **Content** → **Dives**, find every [Dive](/key-tasks/dives/) in your organization, including those created by teammates.
---
Source: https://motherduck.com/docs/getting-started/interfaces/postgres-endpoint
# Postgres endpoint
> Query MotherDuck from any Postgres-compatible client without installing DuckDB
MotherDuck's Postgres endpoint lets you query your databases using any client that speaks the PostgreSQL wire protocol, no DuckDB installation required. This is ideal for serverless environments, BI tools, or languages without a DuckDB SDK.
## Quick start with psql
Set your access token and connect:
```bash
export MOTHERDUCK_TOKEN="your_token_here"
PGPASSWORD=$MOTHERDUCK_TOKEN psql \
-h pg.us-east-1-aws.motherduck.com \
-p 5432 \
-U postgres \
"dbname=sample_data sslmode=verify-full sslrootcert=system"
```
Run a query:
```sql
SELECT title, score
FROM sample_data.hn.hacker_news
WHERE type = 'story'
ORDER BY score DESC
LIMIT 5;
```
## Quick start with Python
```python
# /// script
# dependencies = ["psycopg"]
# ///
import psycopg, os
conn = psycopg.connect(
host="pg.us-east-1-aws.motherduck.com",
port=5432,
dbname="sample_data",
user="postgres",
password=os.environ["MOTHERDUCK_TOKEN"],
sslmode="verify-full",
sslrootcert="system",
)
with conn.cursor() as cur:
cur.execute("SELECT title, score FROM sample_data.hn.hacker_news WHERE type='story' LIMIT 5")
for row in cur:
print(row)
conn.close()
```
## Key things to know
- You're writing **DuckDB SQL**, not PostgreSQL SQL. Queries and MotherDuck SQL that run entirely inside MotherDuck generally work, but the Postgres endpoint is not a full DuckDB client.
- Commands that depend on **local files, local attachments, or extension management** are not supported over the Postgres endpoint.
- The Postgres endpoint is best for query execution, DDL and DML on MotherDuck tables, metadata inspection, and server-side reads from remote storage.
- Features that depend on DuckDB client session state, such as temporary tables or result creation, require a DuckDB client path instead.
- Always connect with **SSL enabled** (`sslmode=verify-full` recommended).
- Use your [MotherDuck access token](/key-tasks/authenticating-and-connecting-to-motherduck/authenticating-to-motherduck) as the password.
## Next steps
- [Postgres Endpoint reference](/sql-reference/postgres-endpoint) — connection parameters, SSL options, session options, and known limitations
- [Connect from Python](/key-tasks/authenticating-and-connecting-to-motherduck/postgres-endpoint/python) — psycopg2 and psycopg3 setup
- [Connect from Java](/key-tasks/authenticating-and-connecting-to-motherduck/postgres-endpoint/java) — PostgreSQL JDBC driver setup
- [Connect from Node.js](/key-tasks/authenticating-and-connecting-to-motherduck/postgres-endpoint/nodejs) — node-postgres setup
- [Connect from Cloudflare Workers](/key-tasks/authenticating-and-connecting-to-motherduck/postgres-endpoint/cloudflare-workers) — serverless edge deployment
---
Source: https://motherduck.com/docs/getting-started/interfaces/third-party-tools
# Third-Party Tools with PostgreSQL
> Connect third-party tools and IDEs to MotherDuck using the Postgres wire protocol endpoint
:::info[Preview feature]
The Postgres endpoint is in preview. Functionality and compatibility may change as we expand support.
:::
MotherDuck's [Postgres endpoint](/key-tasks/authenticating-and-connecting-to-motherduck/postgres-endpoint) lets you connect third-party tools and database IDEs that do not support DuckDB or MotherDuck directly, but do support PostgreSQL data sources.
## Compatibility
| Tool | Status | Notes |
|------|--------|-------|
| psql | Supported | Full support through the CLI. |
| DBeaver | Basic querying | Querying works. Schema browser may show extra objects from other databases. Use `attach_mode=single` (see below). |
| Tableau | Planned | Tracking internally. |
| Looker | Planned | Under evaluation. |
| Metabase | Supported | See [Metabase integration guide](/integrations/bi-tools/metabase). |
| Qlik | Supported | |
## General connection guidance
When connecting any Postgres-compatible tool, use the following connection parameters:
| Parameter | Value |
|-----------|-------|
| **Host** | `pg.-aws.motherduck.com` |
| **Port** | `5432` |
| **Database** | Your MotherDuck database name |
| **User** | postgres |
| **Password** | Your [MotherDuck access token](/key-tasks/authenticating-and-connecting-to-motherduck/authenticating-to-motherduck) |
### Use single attach mode
For the best experience with IDEs and BI tools, set `attach_mode=single` so the tool only sees objects from your target database. Without this, schema browsers may display tables from all attached databases.
### Setting connection options
If the tool supports `PGOPTIONS` or connection options you can also set these:
```bash
PGOPTIONS="--attach_mode=single"
```
See [Attach Modes](/key-tasks/authenticating-and-connecting-to-motherduck/attach-modes/) for more details.
### Remember: you're writing DuckDB SQL
The Postgres endpoint delivers DuckDB SQL over the PostgreSQL wire protocol. Use [DuckDB SQL syntax](https://duckdb.org/docs/sql/introduction) in your queries. PostgreSQL-specific functions and features are not available.
### Use a secure connection
Use your own (system) SSL certificate to make sure you connect securely to the Postgres endpoint. This is done by setting sslmode="verify-full" and sslrootcert="system, which is available since Postgres version >=16. If you do not have a certificate available, you can also use a certificate from a certificate authority like Let's Encrypt at `https://letsencrypt.org/certs/isrgrootx1.pem`. You can download and use this certificate instead: `sslmode=verify-ca sslrootcert=isrgrootx1.pem`. If none of these options work you can fall back to the less secure `sslmode=require`.
## Request support for a tool
Want BI tool support for a tool not listed above? Reach out to [support@motherduck.com](mailto:support@motherduck.com).
---
Source: https://motherduck.com/docs/getting-started/mcp-getting-started
# Talk to Your Data with AI
> Get started with the MotherDuck MCP Server to analyze your data using natural language with Claude, ChatGPT, and other AI assistants
The MotherDuck **remote** MCP Server lets you analyze your data using natural language and generate interactive visualizations, all without writing SQL. Connect your favorite AI assistant (Claude, ChatGPT, Cursor, or others) and start asking questions about your databases, then turn insights into shareable [Dives](/key-tasks/dives) with a single prompt.
:::info[Connection URL]
The remote MCP server is hosted at `https://api.motherduck.com/mcp`. Claude Desktop's connector uses this URL automatically; for clients that need manual configuration, see the [setup guide](/key-tasks/ai-and-motherduck/mcp-setup/).
:::
:::note
This guide covers the **remote MCP server** (fully managed by MotherDuck). If you need to work with local DuckDB files or want full control over the server, see the [local MCP server](/key-tasks/ai-and-motherduck/mcp-setup/#remote-vs-local-mcp-server).
:::
In this guide, you'll connect the MCP server in Claude Desktop, query your data, and create a Dive visualization, all in under 5 minutes.
## What you'll learn
- Connect the MotherDuck MCP Server to Claude Desktop
- List your databases
- Ask analytical questions about your data
- Create an interactive Dive visualization from your analysis
## Prerequisites
- A MotherDuck account ([sign up free](https://app.motherduck.com/))
- Claude Desktop installed ([download](https://claude.ai/download))
:::tip[Using a different AI client?]
This guide uses Claude Desktop, but the remote MCP Server works with ChatGPT, Cursor, Claude Code, and other MCP-compatible clients. See the [full setup guide](/key-tasks/ai-and-motherduck/mcp-setup/) for instructions for your preferred client.
:::
## Step 1: Add the MCP server to Claude Desktop
Open Claude Desktop settings and add the MotherDuck remote MCP Server:
1. Open **Claude Desktop** → **Settings** → **Connectors**
2. Click **Browse Connectors** and search for "MotherDuck"
3. Click **Add** to install the MotherDuck connector
4. A browser window opens for authentication with your MotherDuck account
## Step 2: Verify the connection and permissions
After adding the connector, confirm Claude has access to the MotherDuck tools:
1. Open **Claude Desktop** → **Settings** → **Connectors**
2. Select **MotherDuck** and click on **Configure**
You should see tools like `query`, `list_databases`, and `ask_docs_question` available. You can configure tool permissions to control how Claude uses each tool. See [Configuring tool permissions](/key-tasks/ai-and-motherduck/mcp-setup/#configuring-tool-permissions) for details.
## Step 3: List your databases
Test the connection by asking Claude to list your databases:
**Try this prompt:**
```text
List all my databases on MotherDuck.
```
Claude will use the MCP tools to connect to MotherDuck and return your database list.
## Step 4: Analyze your data
Now let's run an actual analysis. If you don't have data yet, you can attach the sample Hacker News database:
**Attach the sample database:**
```text
Attach this db 'md:_share/hacker_news/de11a0e3-9d68-48d2-ac44-40e07a1d496b' give me some analytics.
```
The `hacker_news` database contains Hacker News stories, comments, and metadata from 2016 to 2025. You'll see that even with a minimal prompt, you get great results for a first data exploration. For more tips on effective prompting and workflow patterns, check out the [MCP Workflows Guide](/key-tasks/ai-and-motherduck/mcp-workflows/).
:::info[Sample databases]
The `hacker_news` database is one of several sample datasets available. See [Sample Data & Queries](/getting-started/sample-data-queries/datasets) for more datasets to explore.
:::
## Step 5: Create visualizations with Dives
Now that you've explored your data, turn your insights into a persistent, interactive visualization. [Dives](/key-tasks/dives) are shareable visualizations that live in your MotherDuck workspace and stay up to date with your data.
**Try this prompt:**
```text
Create a Dive based on these insights.
```
Claude renders the Dive inline in the conversation with the Dive Viewer MCP App, using the same components as the MotherDuck UI and running against live data. Iterate conversationally: *"add a filter for the last 30 days"*, *"switch to a bar chart"*. Each edit saves as a separate version.
```text
Save it to MotherDuck.
```
The Dive is saved to your workspace. You can open it in the MotherDuck UI, share it with your team, and it will always query live data.
## Next steps
You're now ready to analyze your data and create visualizations with AI. Here are some ways to go deeper:
- **[MCP Workflows Guide](/key-tasks/ai-and-motherduck/mcp-workflows/)**: Best practices and workflow patterns, including [how it works under the hood](/key-tasks/ai-and-motherduck/mcp-workflows/#how-it-works)
- **[Creating Visualizations with Dives](/key-tasks/dives/)**: Go deeper into Dives by iterating on visualizations, sharing with your team, and managing version history
- **[Connect to MCP Server](/key-tasks/ai-and-motherduck/mcp-setup/)**: Setup instructions for ChatGPT, Cursor, Claude Code, and other clients
- **[MCP Server Reference](/sql-reference/mcp/)**: Server capabilities, available tools, and regional availability
- **[Building Analytics Agents](/key-tasks/ai-and-motherduck/building-analytics-agents/)**: Build custom AI agents that programmatically query your data
- **[Work with agents through the CLI](/getting-started/interfaces/motherduck-cli/agents/)**: For coding agents with a terminal, when the MotherDuck CLI beats MCP on tokens and why
---
Source: https://motherduck.com/docs/getting-started/sample-data-queries/air-quality
# Air Quality
> Sample data from the WHO Ambient Air Quality Database to use with DuckDB and MotherDuck
## Explore the data
Interactive dashboard built on the WHO air quality dataset. Use it as a starting point for your own [Dives](/key-tasks/dives/).
Embedded Dive: **WHO Ambient Air Quality**.
Dive ID: `dd4b9615-d668-4755-b564-880d2509f6b5`.
## About the dataset
The [WHO Ambient Air Quality Database](https://www.who.int/publications/m/item/who-ambient-air-quality-database-(update-2023)) (6th edition, released in **May 2023**) compiles annual mean concentrations of nitrogen dioxide (NO2) and particulate matter (PM10, PM2.5) from ground measurements across over 8600 human settlements in more than 120 countries. This data, updated every 2-3 years since **2011**, primarily represents city or town averages and is used to monitor the Sustainable Development Goal Indicator 11.6.2, Air quality in cities.
To read from the `sample_data` database, please refer to [attach the sample datasets database](./datasets.mdx)
## Example queries
### Annual city air quality rating
This query assesses the average annual air quality in different cities per year based on WHO guidelines. It calculates the average concentrations of PM2.5, PM10, and NO2, then assigns an air quality rating of 'Good', 'Moderate', or 'Poor'. 'Good' indicates all pollutants are within WHO recommended levels, 'Poor' indicates all pollutants exceed WHO recommended levels, and 'Moderate' refers to any other scenario. The results are grouped and ordered by city and year.
```sql
SELECT
city,
year,
CASE
WHEN
AVG(pm25_concentration) <= 10
AND AVG(pm10_concentration) <= 20
AND AVG(no2_concentration) <= 40
THEN 'Good'
WHEN
AVG(pm25_concentration) > 10
AND AVG(pm10_concentration) > 20
AND AVG(no2_concentration) > 40
THEN 'Poor'
ELSE 'Moderate'
END AS airqualityrating
FROM
sample_data.who.ambient_air_quality
GROUP BY
city,
year
ORDER BY
city,
year;
```
### Yearly average pollutant concentrations of a city
This query calculates the yearly average concentrations of PM2.5, PM10, and NO2 in a given city, here `Berlin`.
```sql
SELECT
year,
AVG(pm25_concentration) AS avg_pm25,
AVG(pm10_concentration) AS avg_pm10,
AVG(no2_concentration) AS avg_no2
FROM sample_data.who.ambient_air_quality
WHERE city = 'Berlin'
GROUP BY year
ORDER BY year DESC;
```
## Schema
| column_name | column_type | null | key | default | extra |
|--------------------|-------------|------|-----|---------|-------|
| who_region | VARCHAR | YES | | | |
| iso3 | VARCHAR | YES | | | |
| country_name | VARCHAR | YES | | | |
| city | VARCHAR | YES | | | |
| year | BIGINT | YES | | | |
| version | VARCHAR | YES | | | |
| pm10_concentration | BIGINT | YES | | | |
| pm25_concentration | BIGINT | YES | | | |
| no2_concentration | BIGINT | YES | | | |
| pm10_tempcov | BIGINT | YES | | | |
| pm25_tempcov | BIGINT | YES | | | |
| no2_tempcov | BIGINT | YES | | | |
| type_of_stations | VARCHAR | YES | | | |
| reference | VARCHAR | YES | | | |
| web_link | VARCHAR | YES | | | |
| population | VARCHAR | YES | | | |
| population_source | VARCHAR | YES | | | |
| latitude | FLOAT | YES | | | |
| longitude | FLOAT | YES | | | |
| who_ms | BIGINT | YES | | | |
---
Source: https://motherduck.com/docs/getting-started/sample-data-queries/datasets
# Example Datasets
> A collections of open datasets and queries to get you started with DuckDB and MotherDuck
We have prepared a series of datasets for you to [dive](/key-tasks/dives/) into MotherDuck!
## sample_data
The `sample_data` database is automatically attached to every MotherDuck account regardless of your region. You can start querying the following tables right away:
| `schema.table` | Description |
|--------------------------------------------------------------------|----------------------------------------------------------------------------------------------------------------|
| [`who.ambient_air_quality`](air-quality.md) | Historical air quality data from the World Health Organization. |
| [`nyc.taxi`](nyc-311-data.md) | Taxi ride data from November 2020 |
| [`nyc.rideshare`](nyc-311-data.md) | Ride share trips (Lyft, Uber etc) in NYC |
| [`nyc.service_requests`](nyc-311-data.md) | Requests to NYC's 311 complaint hotline through phone and web |
| [`hn.hacker_news`](hacker-news.md) | Sample of comments from [Hacker News](https://news.ycombinator.com/) |
| [`kaggle.movies`](kaggle-movies.md) | Movie titles and overviews with pre-computed embeddings from [Kaggle](https://www.kaggle.com/datasets/rounakbanik/the-movies-dataset) |
| [`stackoverflow_survey.survey_results`](stackoverflow-survey.md) | Survey results from 2017 to 2024 |
| [`stackoverflow_survey.survey_schemas`](stackoverflow-survey.md) | Survey schemas (questions from the survey) from 2017 to 2024 |
## Additional datasets
The following datasets are available as separate shared databases. See each dataset's page for instructions on how to attach them.
:::note[`aws-us-east-1` region only]
These additional databases are only available for accounts in the `aws-us-east-1` region.
:::
| Dataset | Description |
|--------------------------------------------|---------------------------------------------------------------------------------------|
| [StackOverflow](stackoverflow.md) | Full StackOverflow data dump up to May 2023 |
| [PyPi / DuckDB Stats](pypi.md) | Python package download data for the `duckdb` package, refreshed weekly |
| [Hacker News (full)](hacker-news.md) | Full [Hacker News](https://news.ycombinator.com/) dataset from 2016 to 2025 |
| [Foursquare](foursquare.md) | Global dataset of over 100 million points of interest (POIs) with location and business information |
## FAQ
### How do I re-attach the sample_data database?
The `sample_data` database is attached automatically, but if you have accidentally removed it, you can re-attach it. The `sample_data` share is [region-scoped](/concepts/architecture-and-capabilities/#the-motherduck-cloud-service), so use the share URL that matches your Organization's cloud region:
| Tier | AWS Region | Share URL |
|------|--------|-----------|
| **Tier 1** | **US East (N. Virginia)** `us-east-1` | `md:_share/sample_data/23b0d623-1361-421d-ae77-62d701d471e6` |
| **Tier 1** | **US West (Oregon)** `us-west-2` | `md:_share/sample_data/6b2babf0-bd16-465e-9243-f137a2e5b763` |
| **Tier 2** | **Europe (Frankfurt)** `eu-central-1` | `md:_share/sample_data/ca7ad3fa-8709-4f9f-b7ec-b227b09d4ef2` |
| **Tier 2** | **Europe (Dublin)** `eu-west-1` | `md:_share/sample_data/cec44d04-1b52-425d-9bcb-9be943d4c7b8` |
| **Tier 3** | **Asia Pacific (Sydney)** `ap-southeast-2` | `md:_share/sample_data/0a065d32-d2ab-4662-8bcf-1f587d9d5916` |
| **Tier 3** | **Asia Pacific (Tokyo)** `ap-northeast-1` | `md:_share/sample_data/6ae6172b-e9c8-4145-9f40-b19e36c97e4e` |
For example, for an Organization in `eu-west-1`:
```sql
ATTACH 'md:_share/sample_data/cec44d04-1b52-425d-9bcb-9be943d4c7b8' AS sample_data;
```
---
Source: https://motherduck.com/docs/getting-started/sample-data-queries/foursquare
# Foursquare
> Foursquare Open Source Places (FSQ OS Places) is a global, open-source dataset of over 100 million points of interest (POI)
## Explore the data
Interactive dashboard built on the Foursquare Open Source Places dataset. Use it as a starting point for your own [Dives](/key-tasks/dives/).
Embedded Dive: **Foursquare Open Source Places**.
Dive ID: `d080c8fa-76c4-4720-9d5b-bdc6a6edda6f`.
## About the dataset
[Foursquare](https://docs.foursquare.com/data-products/docs/fsq-places-open-source) Open Source Places (FSQ OS Places) is a global, open-source dataset of over 100 million points of interest (POI), featuring 22 core attributes, updated monthly, and designed to support geospatial applications with a collaborative, AI- and human-powered data curation system.
This database is updated monthly, we host however a snapshot of 2025-01-10.
You have two tables :
- `fsq_os_places` (Places) : a global dataset of over 100 million points of interest (POIs) with detailed location, business, and contact information.
- `fsq_os_categories` (Categories) : a hierarchical classification of POIs with up to six levels, detailing category names and IDs.
:::note[`aws-us-east-1` region only]
This database is only available for accounts in the `aws-us-east-1` region.
:::
You can attach the `foursquare` database to your account by running the following command:
```sql
ATTACH 'md:_share/foursquare/0cbf467d-03b0-449e-863a-ce17975d2c0b' AS foursquare;
```
## Example queries
The following queries assume that the current database connected is `foursquare`. Run `use foursquare` to switch to it.
### Countries with the most places
```sql
SELECT
country,
COUNT(*) AS places
FROM fsq_os_places
GROUP BY country
ORDER BY places DESC
LIMIT 10;
```
## Schema
### fsq_os_places - places dataset
| Column Name | Type | Description |
|--------------------|------------------|---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
| fsq_place_id | String | The unique identifier of a Foursquare POI. Use this ID to view a venue at: `foursquare.com/v/{fsq_place_id}ud` |
| name | String | Business name of a POI |
| latitude/longitude | Decimal | Decimal coordinates (WGS84 datum) up to 6 decimal places. Derived from third-party sources, user input, and corrections. Default geocode type: front door or rooftop. |
| address | String | User-entered street address of the venue |
| locality | String | City, town, or equivalent where the POI is located |
| region | String | State, province, or territory. Abbreviations used in US, CA, AU, BR; full names elsewhere |
| postcode | String | Postal code or equivalent, formatted based on country (e.g., 5-digit US ZIP code) |
| admin_region | String | Additional sub-division (e.g., Scotland) |
| post_town | String | Town/place used in postal addressing (may differ from geographic location) |
| po_box | String | Post Office Box |
| country | String | 2-letter ISO Country Code |
| date_created | Date | Date the POI entered the database (not necessarily the opening date) |
| date_refreshed | Date | Last date any reference was refreshed through crawl, users, or validation |
| date_closed | Date | Date the POI was marked closed in the database (not necessarily actual closure date) |
| tel | String | Telephone number with local formatting |
| website | String | URL to the POI’s (or chain’s) website |
| email | String | Primary contact email address, if available |
| facebook_id | String | POI's Facebook ID, if available |
| instagram | String | POI's Instagram handle, if available |
| twitter | String | POI's Twitter handle, if available |
| fsq_category_ids | Array (String) | ID(s) of the most granular category(ies). See the Categories page for details |
| fsq_category_labels| Array (String) | Label(s) of the most granular category(ies). See the Categories page for details |
| placemaker_url | String | Link to the POI’s review page in PlaceMaker Tools for suggesting edits or reviewing pending changes |
| geom | wkb | Geometry of the POI in WKB format for visualization through the vector tiling service |
| bbox | struct | An area defined by two longitudes and two latitudes: latitude is a decimal number between -90.0 and 90.0; longitude is a decimal number between -180.0 and 180.0.
`bbox:struct xmin:double ymin:double xmax:double ymax:double` |
---
### fsq_os_categories - category dataset
| Column Name | Type | Description |
|----------------------|---------|-----------------------------------------------------------------------------------------------------|
| category_id | String | Unique identifier of the Foursquare category (BSON format) |
| category_level | Integer | Hierarchy depth of the category (1-6) |
| category_name | String | Name of the most granular category |
| category_label | String | Full category hierarchy separated by `>` |
| level1_category_id | String | Unique ID of the first-level category |
| level1_category_name | String | Name of the first-level category |
| level2_category_id | String | Unique ID of the second-level category |
| level2_category_name | String | Name of the second-level category |
| level3_category_id | String | Unique ID of the third-level category |
| level3_category_name | String | Name of the third-level category |
| level4_category_id | String | Unique ID of the fourth-level category |
| level4_category_name | String | Name of the fourth-level category |
| level5_category_id | String | Unique ID of the fifth-level category |
| level5_category_name | String | Name of the fifth-level category |
| level6_category_id | String | Unique ID of the sixth-level category |
| level6_category_name | String | Name of the sixth-level category |
---
Source: https://motherduck.com/docs/getting-started/sample-data-queries/hacker-news
# Hacker News
> Sample data from Hacker News stories to use for SQL querying of DuckDB and MotherDuck databases.
## Explore the data
Interactive dashboard built on the Hacker News sample dataset. Use it as a starting point for your own [Dives](/key-tasks/dives/).
Embedded Dive: **Hacker News activity**.
Dive ID: `813e3d2d-5e19-4925-b1e4-28d6777b620d`.
## About the dataset
[Hacker News](https://news.ycombinator.com/) is a social news website focusing on computer science and entrepreneurship. It is run by Y Combinator, a startup accelerator, and it's known for its minimalist interface. Users can post stories (such as links to articles), comment on them, and vote them up or down, affecting their visibility.
There are two ways to access the dataset:
- Through the `sample_data` database, which contains a sample of the data (from **January 2022** to **November 2022**). This database is automatically attached to every MotherDuck account.
- Through the `hacker_news` database, which contains the full dataset (from **2016** to **2025**).
To attach the full `hacker_news` database, you can use the following command:
:::note[`aws-us-east-1` region only]
The `hacker_news` database is only available for accounts in the `aws-us-east-1` region.
:::
```sql
ATTACH 'md:_share/hacker_news/de11a0e3-9d68-48d2-ac44-40e07a1d496b' AS hacker_news;
```
To read from the `sample_data` database, please refer to [attach the sample datasets database](./datasets.mdx)
## Example queries
### Most shared websites
This query returns the top domains being shared on Hacker News.
```sql
SELECT
regexp_extract(url, 'http[s]?://([^/]+)/', 1) AS domain,
count(*) AS count
FROM sample_data.hn.hacker_news
WHERE url IS NOT NULL AND regexp_extract(url, 'http[s]?://([^/]+)/', 1) != ''
GROUP BY domain
ORDER BY count DESC
LIMIT 20;
```
### Most commented stories each month
This query calculates the total number of comments for each story and identifies the most commented story of each month.
```sql
WITH ranked_stories AS (
SELECT
title,
'https://news.ycombinator.com/item?id=' || id AS hn_url,
descendants AS nb_comments,
YEAR(timestamp) AS year,
MONTH(timestamp) AS month,
ROW_NUMBER()
OVER (
PARTITION BY YEAR(timestamp), MONTH(timestamp)
ORDER BY descendants DESC
)
AS rn
FROM sample_data.hn.hacker_news
WHERE type = 'story'
)
SELECT
year,
month,
title,
hn_url,
nb_comments
FROM ranked_stories
WHERE rn = 1
ORDER BY year, month;
```
### Most monthly voted stories
This query determines the most voted story for each month.
```sql
WITH ranked_stories AS (
SELECT
title,
'https://news.ycombinator.com/item?id=' || id AS hn_url,
score,
YEAR(timestamp) AS year,
MONTH(timestamp) AS month,
ROW_NUMBER()
OVER (PARTITION BY YEAR(timestamp), MONTH(timestamp) ORDER BY score DESC)
AS rn
FROM sample_data.hn.hacker_news
WHERE type = 'story'
)
SELECT
year,
month,
title,
hn_url,
score
FROM ranked_stories
WHERE rn = 1
ORDER BY year, month;
```
### Keyword analysis
This query counts the monthly mentions a the keyword (here `duckdb`) in the title or text of Hacker News posts, organized by year and month.
```sql
SELECT
YEAR(timestamp) AS year,
MONTH(timestamp) AS month,
COUNT(*) AS keyword_mentions
FROM sample_data.hn.hacker_news
WHERE
(title LIKE '%duckdb%' OR text LIKE '%duckdb%')
GROUP BY year, month
ORDER BY year ASC, month ASC;
```
## Schema
| column_name | column_type | null | key | default | extra |
|-------------|-------------|------|-----|---------|-------|
| title | VARCHAR | YES | | | |
| url | VARCHAR | YES | | | |
| text | VARCHAR | YES | | | |
| dead | BOOLEAN | YES | | | |
| by | VARCHAR | YES | | | |
| score | BIGINT | YES | | | |
| time | BIGINT | YES | | | |
| timestamp | TIMESTAMP | YES | | | |
| type | VARCHAR | YES | | | |
| id | BIGINT | YES | | | |
| parent | BIGINT | YES | | | |
| descendants | BIGINT | YES | | | |
| ranking | BIGINT | YES | | | |
| deleted | BOOLEAN | YES | | | |
---
Source: https://motherduck.com/docs/getting-started/sample-data-queries/kaggle-movies
# Kaggle Movies
> A dataset of over 40,000 movies with titles, overviews, and pre-computed embeddings for semantic search.
## Explore the data
Interactive dashboard with semantic search on the Kaggle Movies sample dataset. Use it as a starting point for your own [Dives](/key-tasks/dives/).
Embedded Dive: **Kaggle Movies**.
Dive ID: `3428c1b0-3805-488c-85fd-a707ed818cf1`.
## About the dataset
This dataset is a subset of the [Kaggle Movies Dataset](https://www.kaggle.com/datasets/rounakbanik/the-movies-dataset), containing over 40,000 movie titles and overviews. It also includes pre-computed 512-dimensional vector embeddings (generated with OpenAI's `text-embedding-3-small` model) for both the title and overview fields, making it useful for experimenting with [semantic search](/key-tasks/ai-and-motherduck/text-search-in-motherduck/) in MotherDuck.
## How to query the dataset
This dataset is available as part of the `sample_data` database, which is automatically attached to every MotherDuck account.
## Example queries
### Browse movies
```sql
SELECT title, overview
FROM sample_data.kaggle.movies
LIMIT 10;
```
### Find similar movies using vector search
Use the pre-computed embeddings together with the [`embedding`](/sql-reference/motherduck-sql-reference/ai-functions/embedding/) function to find movies similar to a search query:
```sql
SELECT
title,
overview,
array_cosine_similarity(
overview_embeddings,
embedding('a space adventure with aliens')
) AS similarity
FROM sample_data.kaggle.movies
WHERE overview IS NOT NULL
ORDER BY similarity DESC
LIMIT 10;
```
### Find movies similar to another movie
```sql
WITH target AS (
SELECT overview_embeddings
FROM sample_data.kaggle.movies
WHERE title = 'The Matrix'
LIMIT 1
)
SELECT
m.title,
m.overview,
array_cosine_similarity(m.overview_embeddings, t.overview_embeddings) AS similarity
FROM sample_data.kaggle.movies m, target t
WHERE m.title != 'The Matrix'
ORDER BY similarity DESC
LIMIT 10;
```
## Schema
| Column Name | Column Type | Description |
|-----------------------|-------------|-----------------------------------------------------------------|
| title | VARCHAR | Movie title |
| overview | VARCHAR | Short description or synopsis of the movie |
| title_embeddings | FLOAT[512] | Pre-computed vector embedding of the title |
| overview_embeddings | FLOAT[512] | Pre-computed vector embedding of the overview |
---
Source: https://motherduck.com/docs/getting-started/sample-data-queries/nyc-311-data
# NYC 311 Complaint Data
> New York City provides data from 311 call service requests. This data can be used as sample data for DuckDB and MotherDuck SQL queries.
## Explore the data
Interactive dashboards built on the NYC sample datasets. Use them as a starting point for your own [Dives](/key-tasks/dives/).
Embedded Dive: **NYC 311 service requests**.
Dive ID: `1b14654c-0ad0-4ada-9b89-1394302e3b30`.
Embedded Dive: **NYC taxi operations**.
Dive ID: `1ac766f5-d5cb-4d31-a87d-e0920a500fd3`.
## About the dataset
The [New York City 311 Service Requests Data](https://data.cityofnewyork.us/Social-Services/311-Service-Requests-from-2010-to-Present/erm2-nwe9) provides information on requests to the city's complaint service from 2010 to the present.
NYC311 responds to thousands of inquiries, comments and requests from customers every single day. This dataset represents only service requests that can be directed to specific agencies. This dataset is updated daily and expected values for many fields will change over time. The lists of expected values associated with each column are not exhaustive. Each row of data contains information about the service request, including complaint type, responding agency, and geographic location. However the data does not reveal any personally identifying information about the customer who made the request.
This dataset describes site-specific non-emergency complaints (also known as “service requests”) made by customers across New York City about a variety of topics, including noise, sanitation, and street quality.
To read from the `sample_data` database, please refer to [attach the sample datasets database](./datasets.mdx)
## Example queries
### The most common complaints in 2018
```sql
SELECT
UPPER(complaint_type),
COUNT(1)
FROM sample_data.nyc.service_requests
WHERE DATE_PART('year', created_date) = 2018
GROUP BY 1
HAVING COUNT(*) > 1000
ORDER BY 2 DESC;
```
## Schema
The columns have been renamed to `lower_case_underscore` format for ease of typing. For more details on column data than below, see the associated data dictionary at that link above, in an Excel file.
| column_name | column_type | null | description |
|--------------------------------|---------------|--------|-------------|
| unique_key | BIGINT | YES | Unique identifier of a Service Request (SR) in the open data set. Each 311 service request is assigned a number that distinguishes it as a separate case incident. |
| created_date | TIMESTAMP | YES | The date and time that a Customer submits a Service Request. |
| closed_date | TIMESTAMP | YES | The date and time that an Agency closes a Service Request. |
| agency | VARCHAR | YES | Acronym of responding City Government Agency or entity responding to 311 Service Request. |
| agency_name | VARCHAR | YES | Full agency name of responding City Government Agency, or entity responding to 311 service request. |
| complaint_type | VARCHAR | YES | This is the first level of a hierarchy identifying the topic of the incident or condition. Complaint Type broadly describes the topic of the incident or condition and are defined by the responding agencies. |
| descriptor | VARCHAR | YES | This is associated to the Complaint Type, and provides further detail on the incident or condition. Descriptor values are dependent on the Complaint Type, and are not always required in the service request. |
| location_type | VARCHAR | YES | Describes the type of location used in the address information |
| incident_zip | VARCHAR | YES | Zip code of the incident address |
| incident_address | VARCHAR | YES | House number and street name of incident address |
| street_name | VARCHAR | YES | Street name of incident address |
| cross_street_1 | VARCHAR | YES | First Cross street based on the geo validated incident location.|
| cross_street_2 | VARCHAR | YES | Second Cross Street based on the geo validated incident location |
| intersection_street_1 | VARCHAR | YES | First intersecting street based on geo validated incident location |
| intersection_street_2 | VARCHAR | YES | Second intersecting street based on geo validated incident location |
| address_type | VARCHAR | YES | Type of information available about the incident location: Address; Block face; Intersection; LatLong; Placename |
| city | VARCHAR | YES | In this dataset, City can refer to a borough or neighborhood. MANHATTAN, BROOKLYN, BRONX, STATEN ISLAND, or in QUEENS, specific neighborhood name |
| landmark | VARCHAR | YES | If the incident location is identified as a Landmark the name of the landmark will display here. Can refer to any noteworthy location, including but not limited to, parks, hospitals, airports, sports facilities, performance spaces, etc. |
| facility_type | VARCHAR | YES | If applicable, this field describes the type of city facility associated to the service request: DSNY Garage, Precinct, School, School District, N/A |
| status | VARCHAR | YES | Current status of the service request submitted: Assigned, Canceled, Closed, Pending |
| due_date | TIMESTAMP | YES | Date when responding agency is expected to update the SR. This is based on the Complaint Type and internal Service Level Agreements (SLAs) |
| resolution_description | VARCHAR | YES | Describes the last action taken on the service request by the responding agency. May describe next or future steps. |
| resolution_action_updated_date | TIMESTAMP | YES | Date when responding agency last updated the service request. |
| bbl | VARCHAR | YES | Parcel number that identifies the location of the building or property associated with the service request. The block is a subset of a borough. The lot is a subset of a block unique within a borough and block. |
| borough | VARCHAR | YES | The borough number is: 1. Manhattan (New York County) 2. Bronx (Bronx County) 3. Brooklyn (Kings County) 4. Queens (Queens County) 5. Staten Island (Richmond County) |
| x_coordinate_state_plane | VARCHAR | YES | Geo validated, X coordinate of the incident location. X coordinate of the incident location. For more information about NY State Plane Coordinate Zones: https://data.gis.ny.gov/datasets/ny-state-plane-coordinate-system-zones/explore |
| y_coordinate_state_plane | VARCHAR | YES | Geo validated, Y coordinate of the incident location. Y coordinate of the incident location. For more information about NY State Plane Coordinate Zones: https://data.gis.ny.gov/datasets/ny-state-plane-coordinate-system-zones/explore |
| open_data_channel_type | VARCHAR | YES | Indicates how the service request was submitted to 311: Phone, Online, Other (submitted by other agency) |
| park_facility_name | VARCHAR | YES | If the incident location is a Parks Dept facility and service requests pertains to a facility managed by NYC Parks (DPR), the name of the facility will appear here |
| park_borough | VARCHAR | YES | The borough of incident if the service request is pertaining to a NYC Parks Dept facility (DPR) |
| vehicle_type | VARCHAR | YES | Data provided if service request pertains to a vehicle managed by the Taxi and Limousine Commission (TLC): Ambulette / Paratransit; Car Service; Commuter Van; Green Taxi |
| taxi_company_borough | VARCHAR | YES | Data provided if service request pertains to a vehicle managed by the Taxi and Limousine Commission (TLC). |
| taxi_pick_up_location | VARCHAR | YES | If the incident pertains a vehicle managed by the Taxi and Limousine Commission (TLC), this field displays the taxi pick up location |
| bridge_highway_name | VARCHAR | YES | If the incident is identified as a Bridge/Highway, the name will be displayed here |
| bridge_highway_direction | VARCHAR | YES | If the incident is identified as a Bridge/Highway, the direction where the issue took place would be displayed here. |
| road_ramp | VARCHAR | YES | If the incident location was Bridge/Highway this column differentiates if the issue was on the Road or the Ramp. |
| bridge_highway_segment | VARCHAR | YES | Additional information on the section of the Bridge/Highway were the incident took place. |
| latitude | DOUBLE | YES | Geo based Latitude of the incident location in decimal degrees |
| longitude | DOUBLE | YES | Geo based Longitude of the incident location in decimal degrees |
| community_board | VARCHAR | YES | Community boards are local representative bodies. There are 59 community boards throughout the City. For more information on Community Boards: [NYC government website](https://www.nyc.gov/site/cau/community-boards/community-boards.page) |
---
Source: https://motherduck.com/docs/getting-started/sample-data-queries/pypi
# PyPi Data
> Want to know how users find and install software you've developed for the Python Community? This DuckDB and MotherDuck database allows you to use SQL to perform data analysis on PyPi data.
## Explore the data
Interactive dashboard built on the DuckDB PyPI download stats. Use it as a starting point for your own [Dives](/key-tasks/dives/).
Embedded Dive: **DuckDB PyPI downloads**.
Dive ID: `c75e16cc-64ed-4960-a2ba-470f47ccf605`.
## About the dataset
PyPi is the Python Package Index, a repository of software packages for the Python programming language. It is a central repository that allows users to find and install software developed and shared by the Python community.
The dataset includes information about packages, releases, and downloads on the `duckdb` python package.
It's refreshed **weekly** and you can visit the [DuckDB Stats dashboard](https://duckdbstats.com).
## How to query the dataset
A dedicated shared database is maintained to query the dataset.
:::note[`aws-us-east-1` region only]
This database is only available for accounts in the `aws-us-east-1` region.
:::
To attach it to your workspace, you can use the following command:
```sql
ATTACH 'md:_share/duckdb_stats/1eb684bf-faff-4860-8e7d-92af4ff9a410' AS duckdb_stats;
```
## Example queries
The following queries assume that the current database connected is `duckdb_stats`. Run `use duckdb_stats` to switch to it.
### Get weekly download stats
```sql
SELECT
DATE_TRUNC('week', download_date) AS week_start_date,
version,
country_code,
python_version,
SUM(daily_download_sum) AS weekly_download_sum
FROM
duckdb_stats.main.pypi_daily_stats
GROUP BY
ALL
ORDER BY
week_start_date
```
## Schema
### pypi_file_downloads
This table contains the raw data. Each row represents a download from PyPi.
| column_name | column_type | null |
|--------------|----------------------------------------------------------------------------------------------------------------|------|
| timestamp | TIMESTAMP | YES |
| country_code | VARCHAR | YES |
| url | VARCHAR | YES |
| project | VARCHAR | YES |
| file | STRUCT(filename VARCHAR, project VARCHAR, "version" VARCHAR, "type" VARCHAR) | YES |
| details | STRUCT("installer" STRUCT("name" VARCHAR, "version" VARCHAR), "python" VARCHAR, "implementation" STRUCT("name" VARCHAR, "version" VARCHAR), "distro" STRUCT("name" VARCHAR, "version" VARCHAR, "id" VARCHAR, "libc" STRUCT("lib" VARCHAR, "version" VARCHAR)), "system" STRUCT("name" VARCHAR, "release" VARCHAR), "cpu" VARCHAR, "openssl_version" VARCHAR, "setuptools_version" VARCHAR, "rustc_version" VARCHAR, "ci" BOOLEAN) | YES |
| tls_protocol | VARCHAR | YES |
| tls_cipher | VARCHAR | YES |
### pypi_daily_stats
This table is a daily aggregation of the raw data. It contains the following columns:
| column_name | column_type | null |
|-------------------|-------------|------|
| load_id | VARCHAR | YES |
| download_date | DATE | YES |
| system_name | VARCHAR | YES |
| system_release | VARCHAR | YES |
| version | VARCHAR | YES |
| project | VARCHAR | YES |
| country_code | VARCHAR | YES |
| cpu | VARCHAR | YES |
| python_version | VARCHAR | YES |
| daily_download_sum| BIGINT | YES |
---
Source: https://motherduck.com/docs/getting-started/sample-data-queries/stackoverflow-survey
# StackOverflow Survey Data
> Data from the StackOverflow Developer Survey from 2017 to 2024.
## Explore the data
Interactive dashboard built on the survey data. Use it as a starting point for your own [Dives](/key-tasks/dives/).
Embedded Dive: **Stack Overflow Developer Survey**.
Dive ID: `9ee6c071-d467-4018-a819-a5f2e1a0586d`.
## About the dataset
Each year, [Stack Overflow conducts a survey](https://survey.stackoverflow.co/) of developers to understand the trends in the developer community. The survey covers a wide range of topics, including programming languages, frameworks, databases, and platforms, as well as developer demographics, education, and career satisfaction.
Starting from 2017, StackOverflow provided consistent schema and data format for the survey data, making it a great dataset to analyze trends in the developer community over the years.
The source is data are a series of CSV files that has been merged into a single schema with two tables for easy querying.
## How to query the dataset
This dataset is available as part of the `sample_data` database, which is automatically attached to every MotherDuck account.
## Example queries
### List the most popular programming languages in 2024
```sql
SELECT
language,
COUNT(*) AS count
FROM (
SELECT UNNEST(STRING_SPLIT(LanguageHaveWorkedWith, ';')) AS language
FROM sample_data.stackoverflow_survey.survey_results
where year='2024'
) AS languages
GROUP BY language
ORDER BY count DESC;
```
### Top 10 countries with the most respondents in 2024
```sql
SELECT
Country,
COUNT(*) AS Respondents
FROM sample_data.stackoverflow_survey.survey_results
WHERE year = '2024'
GROUP BY Country
ORDER BY Respondents DESC
LIMIT 10;
```
### Correlation between remote work and job satisfaction in 2024
```sql
SELECT RemoteWork,
AVG(CAST(JobSat AS DOUBLE)) AS AvgJobSatisfaction,
COUNT(*) AS RespondentCount
FROM sample_data.stackoverflow_survey.survey_results
WHERE JobSat NOT IN ('NA',
'Slightly satisfied',
'Neither satisfied nor dissatisfied',
'Very dissatisfied',
'Very satisfied',
'Slightly dissatisfied')
AND RemoteWork NOT IN ('NA')
AND YEAR='2024'
GROUP BY ALL
```
## Schema
### stackoverflow_survey.survey_results
This table contains all the survey results from 2017 to 2024. Each column represents a question from the survey. As questions change from year to year, the columns may vary a bit and the table is quite large.
### stackoverflow_survey.survey_schema
This table contains the schema of the survey results. `qname` is the name of the question, which is also the column name in the `survey_results` table. `question` is the full question text.
| Column Name | Column Type |
|---------------|-------------|
| qname | VARCHAR |
| question | VARCHAR |
| qid | VARCHAR |
| force_resp | VARCHAR |
| type | VARCHAR |
| selector | VARCHAR |
| year | VARCHAR |
---
Source: https://motherduck.com/docs/getting-started/sample-data-queries/stackoverflow
# StackOverflow Data
> Sample data from StackOverflow to use with DuckDB and MotherDuck to understand SQL-based data analytics.
## Explore the data
Interactive dashboard built on the full Stack Overflow archive. Use it as a starting point for your own [Dives](/key-tasks/dives/).
Embedded Dive: **Stack Overflow Archive**.
Dive ID: `eb4c2b4e-5b0c-4c13-833c-6d97989ea746`.
## About the dataset
[Stack Overflow](https://stackoverflow.com/) is a website dedicated to providing professional and enthusiast programmers a platform to learn and share knowledge. It features questions and answers on a wide range of topics in computer programming and is renowned for its community-driven approach. Users can ask questions, provide answers, vote on questions and answers, and earn reputation points and badges for their contributions.
The dataset includes a complete **data dump up to May 2023**, covering posts, comments, users, badges, and related metrics.
You can read more about the dataset in our blog series [part 1](https://motherduck.com/blog/exploring-stackoverflow-with-duckdb-on-motherduck-1/) and [part 2](https://motherduck.com/blog/exploring-stackoverflow-with-duckdb-on-motherduck-2/).
## How to query the dataset
As this dataset is quite large, it's not part of the `sample_data` database. Instead, you can find it as a dedicated shared database.
:::note[`aws-us-east-1` region only]
This database is only available for accounts in the `aws-us-east-1` region.
:::
To attach it to your workspace, you can use the following command:
```sql
ATTACH 'md:_share/stackoverflow/6c318917-6888-425a-bea1-5860c29947e5' AS stackoverflow;
```
## Example queries
The following queries assume that the current database connected is `stackoverflow`. Run `use stackoverflow` to switch to it.
### List the top 5 posts that received the most votes
```sql
SELECT posts.Title, COUNT(votes.Id) AS VoteCount
FROM posts
JOIN votes ON posts.Id = votes.PostId
GROUP BY posts.Title
ORDER BY VoteCount DESC
LIMIT 5;
```
### Find the top 5 posts with the highest view count:
```sql
SELECT Title, ViewCount
FROM posts
ORDER BY ViewCount DESC
LIMIT 5;
```
## Schema
### Badges
| column_name | column_type | null | key | default | extra |
|---|---|---|---|---|---|
| Id | BIGINT | YES | | | |
| UserId | BIGINT | YES | | | |
| Name | VARCHAR | YES | | | |
| Date | TIMESTAMP | YES | | | |
| Class | BIGINT | YES | | | |
| TagBased | BOOLEAN | YES | | | |
### Comments
| column_name | column_type | null | key | default | extra |
|---|---|---|---|---|---|
| Id | BIGINT | YES | | | |
| PostId | BIGINT | YES | | | |
| Score | BIGINT | YES | | | |
| Text | VARCHAR | YES | | | |
| CreationDate | TIMESTAMP | YES | | | |
| UserId | BIGINT | YES | | | |
| ContentLicense | VARCHAR | YES | | | |
### Post links
| column_name | column_type | null | key | default | extra |
|---|---|---|---|---|---|
| Id | BIGINT | YES | | | |
| CreationDate | TIMESTAMP | YES | | | |
| PostId | BIGINT | YES | | | |
| RelatedPostId | BIGINT | YES | | | |
| LinkTypeId | BIGINT | YES | | | |
### Posts
| column_name | column_type | null | key | default | extra |
|---|---|---|---|---|---|
| Id | BIGINT | YES | | | |
| PostTypeId | BIGINT | YES | | | |
| AcceptedAnswerId | BIGINT | YES | | | |
| CreationDate | TIMESTAMP | YES | | | |
| Score | BIGINT | YES | | | |
| ViewCount | BIGINT | YES | | | |
| Body | VARCHAR | YES | | | |
| OwnerUserId | BIGINT | YES | | | |
| LastEditorUserId | BIGINT | YES | | | |
| LastEditorDisplayName | VARCHAR | YES | | | |
| LastEditDate | TIMESTAMP | YES | | | |
| LastActivityDate | TIMESTAMP | YES | | | |
| Title | VARCHAR | YES | | | |
| Tags | VARCHAR | YES | | | |
| AnswerCount | BIGINT | YES | | | |
| CommentCount | BIGINT | YES | | | |
| FavoriteCount | BIGINT | YES | | | |
| CommunityOwnedDate | TIMESTAMP | YES | | | |
| ContentLicense | VARCHAR | YES | | | |
### Tags
| column_name | column_type | null | key | default | extra |
|---|---|---|---|---|---|
| Id | BIGINT | YES | | | |
| TagName | VARCHAR | YES | | | |
| Count | BIGINT | YES | | | |
| ExcerptPostId | BIGINT | YES | | | |
| WikiPostId | BIGINT | YES | | | |
### Votes
| column_name | column_type | null | key | default | extra |
|---|---|---|---|---|---|
| Id | BIGINT | YES | | | |
| PostId | BIGINT | YES | | | |
| VoteTypeId | BIGINT | YES | | | |
| CreationDate | TIMESTAMP | YES | | | |
### Users
| column_name | column_type | null | key | default | extra |
|---|---|---|---|---|---|
| Id | BIGINT | YES | | | |
| Reputation | BIGINT | YES | | | |
| CreationDate | TIMESTAMP | YES | | | |
| DisplayName | VARCHAR | YES | | | |
| LastAccessDate | TIMESTAMP | YES | | | |
| AboutMe | VARCHAR | YES | | | |
| Views | BIGINT | YES | | | |
| UpVotes | BIGINT | YES | | | |
| DownVotes | BIGINT | YES | | | |
---
Source: https://motherduck.com/docs/integrations/bi-tools/cube
# Cube
> Cube is a semantic layer for building and visualizing data. It integrates with MotherDuck for dashboards, semantic models, and embedded analytics workflows.
## How it works with MotherDuck
Cube connects to MotherDuck through Cube's DuckDB data source. Use this setup when you want Cube's semantic layer, APIs, dashboards, or embedded analytics to query data that lives in MotherDuck.
## Prerequisites
- A Cube project, either self-hosted or in Cube Cloud.
- A MotherDuck service token or access token. For production deployments, use a dedicated service account token.
- The MotherDuck database and schema Cube should use for its models.
## Setup
### Manual setup
In a self-hosted Cube project, configure the DuckDB data source and pass the MotherDuck token to Cube:
```bash
CUBEJS_DB_TYPE=duckdb
CUBEJS_DB_DUCKDB_MOTHERDUCK_TOKEN=
```
Keep the token in your deployment secret manager rather than committing it to `.env`.
### Cube Cloud setup
In Cube Cloud, choose **DuckDB** when creating the database connection, then paste your MotherDuck token into the **MotherDuck Token** field.
Leave the MotherDuck token blank only when you are connecting Cube to a local DuckDB database instead of MotherDuck.
## Authentication and configuration
- Use a read/write token if Cube needs to create or refresh objects in MotherDuck. Use a read token for read-only dashboard workloads.
- Configure Cube's DuckDB schema setting if your Cube models should default to a specific MotherDuck schema.
- If your Cube deployment reads private files from object storage through DuckDB, configure those storage credentials in Cube separately from the MotherDuck token.
## Important notes
- Cube's DuckDB documentation includes S3, extension, and pre-aggregation settings. Those settings are Cube/DuckDB deployment details, not required for a basic MotherDuck connection.
- For production, keep the MotherDuck token out of connection strings and application logs.
- If you use Cube Cloud, allowlist the Cube Cloud IPs shown in the connection screen if your network policy requires it.
## Use cases
- Build a governed semantic layer on top of MotherDuck tables.
- Serve embedded analytics from Cube APIs while querying MotherDuck.
- Prototype dashboard models locally and move the same Cube project to Cube Cloud.
## Related content
- [Read the Cube blog on DuckDB and MotherDuck integrations](https://cube.dev/blog/introducing-duckdb-and-motherduck-integrations)
- [View the full Cube DuckDB and MotherDuck setup guide](https://cube.dev/docs/product/configuration/data-sources/duckdb)
- [MotherDuck authentication](/key-tasks/authenticating-and-connecting-to-motherduck/authenticating-to-motherduck)
---
Source: https://motherduck.com/docs/integrations/bi-tools/evidence
# Evidence
> Evidence is an open source, code-based alternative to drag-and-drop BI tools. Build polished data products with just SQL and markdown.
## Getting started
Head over to [their installation page](https://docs.evidence.dev/getting-started/install-evidence) and start with their template to get you started.
## Authenticate to MotherDuck
When using development, you can go manually through the UI, pick "settings". If you are running Evidence locally, typically at [http://localhost:3000/settings](http://localhost:3000/settings).
Then select 'DuckDB' as a connection type, and as the filename, use `'md:?motherduck_token=xxxx'` where `xxx` is your [access token](/key-tasks/authenticating-and-connecting-to-motherduck/authenticating-to-motherduck#authentication-using-an-access-token). Finally as extension, select "No extension". Click on `Save`.
In production, you can set [some global environments](https://docs.evidence.dev/deployment/environments#prod-environment), you would have to set two environments variables:
- `EVIDENCE_DUCKDB_FILENAME='md:?motherduck_token=xxxx'`
- `EVIDENCE_DATABASE=duckdb`
## Displaying some data through SQL and markdown
Once done, you can add a new page in the `pages` folder and add the following code blocks to `stackoverflow.md` file:
First, add some Markdown headers.
```md
---
title: Evidence & MotherDuck
---
# Stories with most score
```
Then, we query our data from the [HackerNews sample_data database](/getting-started/sample-data-queries/hacker-news.md) in MotherDuck. The query is fetching the top stories (posts) from HackerNews.
SELECT id,
title,
score,
"by",
strftime('%Y-%m-%d', to_timestamp(time)) AS date
FROM sample_data.hn.hacker_news
WHERE type = 'story'
ORDER BY score DESC
LIMIT 20;
Finally, we use the reference of that query result `new_items` to create a list that would be generated in Markdown. The list contains the title (with the url of the story), the date, the score and the author of the story.
```md
{#each new_items as item}
* [{item.title}](https://news.ycombinator.com/item?id={item.id}) {item.date} ⬆ {item.score} by [{item.by}](https://news.ycombinator.com/user?id={item.by})
{/each}
```
Head over then to this page you created and you should see the final result that looks like this:
---
Source: https://motherduck.com/docs/integrations/bi-tools/excel
# Connect MotherDuck to Excel
> Use Excel's 'Get Data' flow with the DuckDB ODBC driver to load MotherDuck data into Excel. This setup works well for recurring reporting, analysis, ad hoc SQL exploration, finance models, and operational dashboards without relying on exported CSVs.
### Windows
## Before you start
To get started you'll need the following.
- Windows + Excel (ODBC is Windows-only for this flow)
- A MotherDuck access token (create one in the [MotherDuck token page](/key-tasks/authenticating-and-connecting-to-motherduck/authenticating-to-motherduck/#creating-an-access-token))
- Admin rights on your computer to install the ODBC driver
## Installation steps
### 1. Install the DuckDB ODBC driver
1. Download the latest MotherDuck-supported DuckDB ODBC driver that matches your Excel architecture:
- AMD64: [duckdb_odbc-windows-amd64.zip](https://github.com/duckdb/duckdb-odbc/releases/download/v1.5.5.0/duckdb_odbc-windows-amd64.zip)
- ARM64: [duckdb_odbc-windows-arm64.zip](https://github.com/duckdb/duckdb-odbc/releases/download/v1.5.5.0/duckdb_odbc-windows-arm64.zip)
2. Extract the `.zip` file and run `odbc_install.exe` as Administrator (right click -> Run as administrator).
### 2. Configure the DuckDB system DSN
1. Open the ODBC Data Source Administrator:
- 64-bit Excel: Start menu -> ODBC Data Sources (64-bit)
- 32-bit Excel: Start menu -> ODBC Data Sources (32-bit)
2. Go to System DSN, select DuckDB, and click Configure. 
3. Set Database to one of the following:
- Recommended (scoped): `md:your_database_name`
- Open scope: `md:` (allows access to any database)
4. Click OK to save.
Excel supplies the token on its own credentials screen, covered in the next step. Other ODBC tools may not offer that screen, in which case you can authenticate the DSN itself by embedding the token in the Database field. See [ODBC](/getting-started/interfaces/client-apis/other/odbc/#authenticating-with-an-access-token).
### 3. Connect from the data menu
1. In Excel, go to Data -> Get Data -> From Other Sources -> From ODBC.
2. Choose DuckDB from the DSN dropdown and click OK.
3. On the credentials screen, choose Default or Custom and add this to the Connection string properties field:
```text
motherduck_token=
```
4. Click Connect.
### 4. Load or transform data
Use the Navigator window to select tables and choose Load to bring data into Excel, or Transform Data to shape it in Power Query before loading.
### macOS
## Excel ODBC on macOS
Direct ODBC connectivity between Excel and MotherDuck is **not supported on macOS** due to a driver incompatibility.
### Why it doesn't work
Excel on macOS uses the **iODBC** driver manager, but the DuckDB ODBC driver is built for **unixODBC**. These drivers are incompatible at the binary level. This is a [known issue](https://github.com/duckdb/duckdb-odbc/issues/40) being tracked by the DuckDB team.
If necessary, you can build this driver yourself.
### Alternatives for macOS users
#### Option 1: Export directly with DuckDB (CLI and drivers)
DuckDB has an [Excel extension](https://duckdb.org/docs/stable/core_extensions/excel) that can write `.xlsx` files directly. This works with DuckDB CLI or any DuckDB driver, but cannot be used in the MotherDuck UI because the UI cannot export `.xlsx` files to your local file system.
```sql
-- Connect to MotherDuck and export to Excel
ATTACH 'md:';
COPY (SELECT * FROM my_database.my_table) TO 'output.xlsx' WITH (FORMAT xlsx, HEADER true);
```
Or from the command line:
```bash
duckdb -c "ATTACH 'md:'; COPY (SELECT * FROM my_database.my_table) TO 'output.xlsx' WITH (FORMAT xlsx, HEADER true);"
```
#### Option 2: Use the MotherDuck Web UI
Query your data in the [MotherDuck Web UI](https://app.motherduck.com) and export results:
1. Run your query in the MotherDuck UI
2. Click the download button to export as CSV
3. Open the CSV in Excel
#### Option 3: Export to CSV via DuckDB CLI
Use the DuckDB CLI to export query results to CSV:
```bash
duckdb -c "ATTACH 'md:'; COPY (SELECT * FROM my_database.my_table) TO 'output.csv' (HEADER, DELIMITER ',');"
```
### Linux
## Excel workflows on Linux
Direct ODBC connectivity between desktop Excel and MotherDuck is Windows-only for this flow. On Linux, use DuckDB CLI or a DuckDB client to export query results, then open the exported file in Excel, Excel for the web, or another spreadsheet tool.
### Option 1: Export directly with DuckDB (CLI and drivers)
DuckDB has an [Excel extension](https://duckdb.org/docs/stable/core_extensions/excel) that can write `.xlsx` files directly. This works with DuckDB CLI or any DuckDB driver, but cannot be used in the MotherDuck UI because the UI cannot export `.xlsx` files to your local file system.
```sql
-- Connect to MotherDuck and export to Excel
ATTACH 'md:';
COPY (SELECT * FROM my_database.my_table) TO 'output.xlsx' WITH (FORMAT xlsx, HEADER true);
```
Or from the command line:
```bash
duckdb -c "ATTACH 'md:'; COPY (SELECT * FROM my_database.my_table) TO 'output.xlsx' WITH (FORMAT xlsx, HEADER true);"
```
### Option 2: Export to CSV via DuckDB CLI
Use the DuckDB CLI to export query results to CSV:
```bash
duckdb -c "ATTACH 'md:'; COPY (SELECT * FROM my_database.my_table) TO 'output.csv' (HEADER, DELIMITER ',');"
```
## Tips
- If you change your MotherDuck token, update the connection string properties in Excel.
- If you use multiple databases, create separate DSNs (e.g., `DuckDB - analytics`, `DuckDB - finance`) with different `md:database` values.
## Troubleshooting
### How do I delete an existing MotherDuck connection?
1. In Excel, go to Data -> Queries & Connections.
2. Find the connection you want to remove, right click it, and choose Delete.
### How do I modify an existing MotherDuck connection?
1. In Excel, go to Data -> Queries & Connections.
2. Right click the connection and choose Properties.
3. Open the Definition tab and update the connection string (for example, update `motherduck_token=...`) and save.
If you don't see the Definition tab, use Data -> Get Data -> Data Source Settings, select your DuckDB connection, then choose Change Source or Edit Permissions as needed.
---
Source: https://motherduck.com/docs/integrations/bi-tools/explo
# Explo
> Explo is a platform for embedded analytics, AI analytics, and data sharing in customer-facing products. It integrates with MotherDuck as a data source.
## How it works with MotherDuck
Explo connects to MotherDuck as a data source for embedded analytics and customer-facing dashboards.
## Prerequisites
- A MotherDuck database for Explo to query.
- A MotherDuck access token provisioned for the Explo workspace.
- The database name and any schema names you plan to expose in Explo.
## Setup
1. In MotherDuck, create an access token for Explo.
2. In Explo, create a new data source and select **MotherDuck**.
3. Enter the MotherDuck database name.
4. Choose the authentication option that uses an access token.
5. Paste the token and save the data source.
## Authentication and configuration
- Use a dedicated token for each Explo environment or workspace.
- Prefer read-only access for embedded analytics workloads.
- Configure schema access in Explo so customer-facing dashboards only expose the intended data model.
## Important notes
- Explo's MotherDuck documentation lists the required credentials but does not require a platform-specific environment variable list.
- Keep the token in Explo's credential store and rotate it like any other production credential.
## Use cases
- Power embedded dashboards from MotherDuck tables.
- Build customer-facing analytics over per-customer or shared schemas.
- Let Explo query curated datasets without moving data into another warehouse.
## Related content
- [View the full Explo MotherDuck setup guide](https://docs.explo.co/data-sources/connecting-to-data-sources/data-source-types/motherduck#motherduck)
- [MotherDuck authentication](/key-tasks/authenticating-and-connecting-to-motherduck/authenticating-to-motherduck)
---
Source: https://motherduck.com/docs/integrations/bi-tools/gooddata
# Gooddata
> Enterprise analytics platform for building data products and embedded analytics. It integrates with MotherDuck for dashboards, semantic models, and embedded analytics workflows.
## How it works with MotherDuck
GoodData Cloud connects to MotherDuck as a data source for semantic models, dashboards, and embedded analytics.
## Prerequisites
- A GoodData Cloud workspace with permission to create data sources.
- A MotherDuck service token.
- The MotherDuck database name and schema GoodData should use.
## Setup
1. In GoodData Cloud, open **Data sources** and select **Connect data**.
2. Select **MotherDuck**.
3. Enter a data source display name.
4. Paste the MotherDuck service token.
5. Enter the database name and schema.
6. Select **Connect**.
GoodData also supports creating the data source through its API. When using the API, encode the MotherDuck service token as required by the GoodData request body and use a JDBC URL such as `jdbc:duckdb:md:`.
## Authentication and configuration
- Use a MotherDuck service token dedicated to the GoodData data source.
- Enter a schema so GoodData can build its logical data model from the intended tables.
- Keep the service token in GoodData's credential handling or your deployment secret store if you create the data source through the API.
## Important notes
- GoodData's guide includes both UI and API setup. Start with the UI unless you need repeatable provisioning.
- GoodData's API examples include GoodData API authentication details; those are separate from the MotherDuck service token.
## Use cases
- Build governed BI workspaces on top of MotherDuck.
- Create embedded analytics backed by MotherDuck tables.
- Provision MotherDuck data sources with GoodData's API for repeatable environments.
## Related content
- [View the full GoodData MotherDuck setup guide](https://www.gooddata.com/docs/cloud/connect-data/create-data-sources/motherduck/)
- [MotherDuck authentication](/key-tasks/authenticating-and-connecting-to-motherduck/authenticating-to-motherduck)
---
Source: https://motherduck.com/docs/integrations/bi-tools/grafana
# Grafana
> Grafana is an observability and dashboarding platform for building dashboards, alerts, and exploratory views. The MotherDuck-maintained DuckDB data source plugin lets Grafana query local DuckDB files and MotherDuck databases.
## How it works with MotherDuck
1. Install Grafana 10.4.0 or later on a glibc-based Linux environment. If you use Docker, use an Ubuntu-based Grafana image instead of the default Alpine-based image.
2. Download the DuckDB data source plugin from the GitHub releases page.
3. Because the plugin is unsigned, allow `motherduck-duckdb-datasource` in Grafana's unsigned plugin configuration.
4. Add a DuckDB data source in Grafana and provide a MotherDuck token.
5. If `md:` does not work as the database path in a Docker deployment, leave the path blank and add `ATTACH IF NOT EXISTS 'md:';` in the initialization SQL.
## Related content
- [View the full process in the Grafana DuckDB data source plugin documentation](https://github.com/motherduckdb/grafana-duckdb-datasource)
- [Grafana data source documentation](https://grafana.com/docs/grafana/latest/features/datasources/)
- [MotherDuck authentication](/key-tasks/authenticating-and-connecting-to-motherduck/authenticating-to-motherduck)
---
Source: https://motherduck.com/docs/integrations/bi-tools/hex
# Hex
> Connect Hex notebooks to MotherDuck using SQL data connections or Python cells for interactive analytics.
[Hex](https://hex.tech/) is a software platform for collaborative data science and analytics using Python, SQL and no-code.
You have two ways to connect to MotherDuck using Hex:
- **Using SQL cells with a data connection**: MotherDuck is a supported [data connection in Hex](https://learn.hex.tech/docs/connect-to-data/data-connections/data-connections-introduction#supported-data-sources).
- **Using Python cells**: You can use Python cells to connect to MotherDuck and query data using DuckDB.
## Using SQL cells with a data connection
:::tip
When many human users query through the same MotherDuck data connection, consider using a [read scaling token](/key-tasks/authenticating-and-connecting-to-motherduck/read-scaling/).
Hex will then route the queries to a dedicated Duckling per Hex kernel, up to the maximum pool size configured for the account that owns the token. Every preset role can configure its own Duckling and read scaling pool.
What this means in practice:
* Each workbook will get a stable backend for each unique data connection.
Multiple users collaborating on the same workbook will share the Duckling to query faster on warm data caches.
* In a published app, each user will get a stable backend for each data connection to power their own unique exploration.
:::
To add a new data connection, head over the Data browser in a new notebook and click on `Add data connection`.
Select `MotherDuck` as the data source and fill in the required fields. The most important is the MotherDuck token, which you can find in the [MotherDuck UI](/key-tasks/authenticating-and-connecting-to-motherduck/authenticating-to-motherduck/#creating-an-access-token).
Once done, you can use the data browser to explore the tables and columns and directly specify your data connection in your SQL cell.
### Query some data
Add another cell and run the same query we ran in a Python cell :
```sql
SELECT dayname(tpep_pickup_datetime) AS day_of_week, strftime('%H', tpep_pickup_datetime) AS hour_of_day, COUNT(*) AS trip_count
FROM sample_data.nyc.taxi
GROUP BY day_of_week, hour_of_day
ORDER BY day_of_week, hour_of_day;
```
This produces both a table and a Dataframe, which you can utilize in the same manner as we previously demonstrated with Python to generate data visualizations.
## Using Python cells
:::tip[Use Python 3.12 or later]
When using Python cells in your environment to connect to MotherDuck, set your Hex project's Python version to 3.12 or later to ensure you have a compatible version of DuckDB pre-installed in your Hex environment.
To change your Python version, go to **Settings** --> **Environment** and select **Python 3.12** or **Latest**.
:::
If you prefer programming in Python, you can use Python cells to connect to MotherDuck and start query data. You can jump directly on the [Hex notebook](https://app.hex.tech/c0083b53-a04f-47b1-bff7-a9ff12590a9f/hex/5c85b3e2-3df7-4011-87a0-1fff63787d03/draft/logic) for a quickstart.
The notebook highlight how you can query data using Python or SQL cells and display charts!
### Storing your MotherDuck token
The first step is to safely store your MotherDuck token. You can do this by [creating a new secret in Hex.](https://learn.hex.tech/docs/environment-configuration/environment-views#secrets)
Let's add your [MotherDuck access token](/key-tasks/authenticating-and-connecting-to-motherduck/authenticating-to-motherduck/authenticating-to-motherduck.md#authentication-using-an-access-token) under the name `motherduck_token`.
Once done, add the next Python cell to export as environment variable your `motherduck_token`. This will be detected by SQL/Python processes when authenticating to MotherDuck.
```python
# Passing the secrets as environment variable for Python/SQL cell auth
# Fill in your token as a Hex project secret https://learn.hex.tech/docs/environment-configuration/environment-views#secret
import os
os.environ["motherduck_token"] = motherduck_token
```
### Connecting to MotherDuck
DuckDB is already pre-installed in the Hex environment, so you can connect to MotherDuck directly.
Add a Python cell and run the following code:
```python
import duckdb
# Connect to MotherDuck using Python
conn = duckdb.connect(f'md:')
```
### Query some data and display a chart
You can query data from the [sample_data database](/getting-started/sample-data-queries/datasets.mdx). The following example runs a query and returns the result as a pandas dataframe to display as a chart.
This database is auto-attached to any MotherDuck user, so you can query it directly.
Add another Python cell and run the following code:
```python
# Query sample_data database and convert it to a pandas dataframe for dataviz
peak_hours = conn.sql("""
SELECT dayname(tpep_pickup_datetime) AS day_of_week, strftime('%H', tpep_pickup_datetime) AS hour_of_day, COUNT(*) AS trip_count
FROM sample_data.nyc.taxi
GROUP BY day_of_week, hour_of_day
ORDER BY day_of_week, hour_of_day;""").to_df()
```
Now we can display the chart using the Visualization cell. Add a new Visualization cell, type `Chart` and select the dataframe we just created `peak_hours`.
Finally, play with the parameters to obtain the following chart which gives you a weekly view of the peak hours in New York City for the yellow cabs.
---
Source: https://motherduck.com/docs/integrations/bi-tools/holistics
# Holistics
> Holistics helps data teams set up self-service BIs that are reliable and easy to maintain. Everyone can now self-serve data with confidence by applying software's best practices. It integrates with MotherDuck for dashboards, semantic models, and embedded analytics workflows.
## How it works with MotherDuck
Holistics connects to MotherDuck as a data source for semantic modeling, self-service BI, dashboards, and analytics-as-code workflows.
## Prerequisites
- A Holistics workspace with permission to create data sources.
- A MotherDuck access token.
- The MotherDuck database and schemas Holistics should query.
## Setup
1. In MotherDuck, create an access token for Holistics.
2. In Holistics, open **Organization Settings** > **Data Sources**.
3. Select **New Data Source** and choose **MotherDuck**.
4. Enter a display name.
5. Paste the MotherDuck token.
6. Test and save the data source.
## Authentication and configuration
- Use a dedicated MotherDuck token for Holistics.
- Select the token type based on the work Holistics needs to run. Read-only access is enough for dashboard queries.
- Share the Holistics data source only with the analysts or teams that should model and query the connected data.
## Important notes
- Holistics queries MotherDuck directly; data remains in MotherDuck.
- If you model data from multiple MotherDuck schemas, confirm the token can access all of them before saving the data source.
## Use cases
- Model MotherDuck data in Holistics' semantic layer.
- Build governed self-service dashboards.
- Manage BI content through Holistics analytics-as-code workflows.
## Related content
- [View the full Holistics MotherDuck setup guide](https://docs.holistics.io/docs/connect/databases/motherduck)
- [MotherDuck authentication](/key-tasks/authenticating-and-connecting-to-motherduck/authenticating-to-motherduck)
---
Source: https://motherduck.com/docs/integrations/bi-tools/index
# Business Intelligence Tools
> Use MotherDuck as a data source in tools for interactive data analysis and presentation
MotherDuck integrates with popular business intelligence tools to help you analyze and visualize your data.
## Included pages
- [Hex](https://motherduck.com/docs/integrations/bi-tools/hex): Connect Hex notebooks to MotherDuck using SQL data connections or Python cells for interactive analytics.
- [Evidence](https://motherduck.com/docs/integrations/bi-tools/evidence): Evidence is an open source, code-based alternative to drag-and-drop BI tools. Build polished data products with just SQL and markdown.
- [Superset & Preset](https://motherduck.com/docs/integrations/bi-tools/superset-preset): Apache Superset is a powerful, open-source data exploration and visualization platform designed to be intuitive and interactive. It allows data professionals to quickly integrate and analyze data from various sources, creating insightful dashboards and charts for better decision making.
- [Metabase](https://motherduck.com/docs/integrations/bi-tools/metabase): Connect Metabase to MotherDuck through the Postgres endpoint on Metabase Cloud, or the DuckDB driver plugin on self-hosted instances.
- [Tableau](https://motherduck.com/docs/integrations/bi-tools/tableau): Tableau is a widely-used business intelligence and data visualization platform that enables data analysts to build interactive dashboards and reports. You can connect Tableau Cloud to MotherDuck through the built-in PostgreSQL connector using MotherDuck's Postgres endpoint. For Tableau Desktop and Server, use the DuckDB JDBC connector.
- [Looker with MotherDuck](https://motherduck.com/docs/integrations/bi-tools/looker): Connect Looker (Google Cloud core) to MotherDuck using the Postgres endpoint, including the required compatibility-mode parameter and recommended pooling and token settings.
- [Connect MotherDuck to Excel](https://motherduck.com/docs/integrations/bi-tools/excel): Use Excel's 'Get Data' flow with the DuckDB ODBC driver to load MotherDuck data into Excel. This setup works well for recurring reporting, analysis, ad hoc SQL exploration, finance models, and operational dashboards without relying on exported CSVs.
- [Microsoft Power BI](https://motherduck.com/docs/integrations/bi-tools/powerbi): Power BI is an interactive data visualization product developed by Microsoft. You can connect Power BI to MotherDuck through the built-in PostgreSQL database connector using MotherDuck's Postgres endpoint.
- [Cube](https://motherduck.com/docs/integrations/bi-tools/cube): Cube is a semantic layer for building and visualizing data. It integrates with MotherDuck for dashboards, semantic models, and embedded analytics workflows.
- [Explo](https://motherduck.com/docs/integrations/bi-tools/explo): Explo is a platform for embedded analytics, AI analytics, and data sharing in customer-facing products. It integrates with MotherDuck as a data source.
- [Gooddata](https://motherduck.com/docs/integrations/bi-tools/gooddata): Enterprise analytics platform for building data products and embedded analytics. It integrates with MotherDuck for dashboards, semantic models, and embedded analytics workflows.
- [Grafana](https://motherduck.com/docs/integrations/bi-tools/grafana): Grafana is an observability and dashboarding platform for building dashboards, alerts, and exploratory views. The MotherDuck-maintained DuckDB data source plugin lets Grafana query local DuckDB files and MotherDuck databases.
- [Holistics](https://motherduck.com/docs/integrations/bi-tools/holistics): Holistics helps data teams set up self-service BIs that are reliable and easy to maintain. Everyone can now self-serve data with confidence by applying software's best practices. It integrates with MotherDuck for dashboards, semantic models, and embedded analytics workflows.
- [Lightdash](https://motherduck.com/docs/integrations/bi-tools/lightdash): Lightdash is an open-source BI platform that turns your dbt project into a governed metrics and dashboarding layer. It connects to MotherDuck as a DuckDB warehouse.
- [Omni](https://motherduck.com/docs/integrations/bi-tools/omni): Modern business intelligence platform for creating interactive dashboards and data visualizations. It integrates with MotherDuck for dashboards, semantic models, and embedded analytics workflows.
- [Rill Data](https://motherduck.com/docs/integrations/bi-tools/rill-data): Rill Data is a data platform for building and visualizing data. It integrates with MotherDuck for dashboards, semantic models, and embedded analytics workflows.
- [Zenlytic](https://motherduck.com/docs/integrations/bi-tools/zenlytic): Zenlytic is a data visualization platform for building and visualizing data. It integrates with MotherDuck for dashboards, semantic models, and embedded analytics workflows.
---
Source: https://motherduck.com/docs/integrations/bi-tools/lightdash
# Lightdash
> Lightdash is an open-source BI platform that turns your dbt project into a governed metrics and dashboarding layer. It connects to MotherDuck as a DuckDB warehouse.
## How it works with MotherDuck
Lightdash builds its semantic layer from your dbt project and queries the warehouse directly. When you connect a project, select **DuckDB** as the warehouse type and choose the MotherDuck option so Lightdash runs queries against your MotherDuck database.
## Prerequisites
- A MotherDuck database with the tables or views your dbt project models.
- A dbt project (dbt v1.8 or later) whose profile targets MotherDuck.
- A MotherDuck access token for Lightdash to use.
## Setup
1. In MotherDuck, create an access token for Lightdash.
2. In Lightdash, create a project and select **DuckDB** as the warehouse type, then choose the **MotherDuck** option at the top of the connection form.
3. Fill in the connection fields:
- **Database**: the MotherDuck database name. If your dbt profile uses `path: "md:analytics"`, enter `analytics`.
- **Schema**: the schema Lightdash should use, for example `main`.
- **Access token**: the MotherDuck token you created.
- **Threads**: start with `1` and increase as needed.
4. Save the connection and let Lightdash compile your dbt project.
## Authentication and configuration
- Use a dedicated token for Lightdash, scoped to only the database(s) you want it to query.
- Enter the database name without the `md:` prefix.
- Keep the schema in the connection form aligned with the schema your dbt models write to.
Your dbt `profiles.yml` should target MotherDuck through the DuckDB adapter with the `motherduck` extension:
```yaml
my-motherduck-db:
target: prod
outputs:
prod:
type: duckdb
path: "md:analytics"
schema: main
threads: 4
extensions:
- motherduck
settings:
motherduck_token: "{{ env_var('MOTHERDUCK_TOKEN') }}"
```
## Important notes
- Lightdash reads its metrics and dimensions from your dbt project, so keep the dbt models and Lightdash connection pointed at the same MotherDuck database and schema.
- The "Start of week" setting controls which day begins the week in charts. "Auto" uses the warehouse default.
## Use cases
- Expose a governed metrics layer over MotherDuck data built from your dbt models.
- Build dashboards and explores for business users on top of curated MotherDuck schemas.
- Reuse an existing dbt-on-MotherDuck project as the semantic layer for self-serve BI.
## Related content
- [View the full Lightdash MotherDuck setup guide](https://docs.lightdash.com/get-started/setup-lightdash/connect-project#motherduck)
- [dbt integration](/integrations/transformation/dbt/)
- [MotherDuck authentication](/key-tasks/authenticating-and-connecting-to-motherduck/authenticating-to-motherduck)
---
Source: https://motherduck.com/docs/integrations/bi-tools/looker
# Looker with MotherDuck
> Connect Looker (Google Cloud core) to MotherDuck using the Postgres endpoint, including the required compatibility-mode parameter and recommended pooling and token settings.
:::info[Preview]
The Postgres endpoint is in [preview](/about-motherduck/feature-stages/). Features and behavior may change.
:::
[Looker (Google Cloud core)](https://cloud.google.com/looker) connects to MotherDuck through the [Postgres endpoint](/key-tasks/authenticating-and-connecting-to-motherduck/postgres-endpoint/) using its standard PostgreSQL dialect. A Looker-specific compatibility mode is required so that symmetric aggregates and Persistent Derived Tables (PDTs) work correctly.
## Before you start
You'll need:
- A [Looker](https://cloud.google.com/looker) instance and admin access to create database connections
- A [MotherDuck access token](/key-tasks/authenticating-and-connecting-to-motherduck/authenticating-to-motherduck) (see [Choose the right token](#choose-the-right-token) below)
- Your Postgres host, which you can find at [MotherDuck Postgres settings](https://app.motherduck.com/settings/postgres) (for example, `pg.us-east-1-aws.motherduck.com`)
## Connect to MotherDuck
In Looker, go to **Admin → Connections → Add Connection** and configure:
| Parameter | Value |
|---|---|
| **Dialect** | PostgreSQL 9.5+ |
| **Host** | Your MotherDuck Postgres host (for example, `pg.us-east-1-aws.motherduck.com`) |
| **Port** | `5432` |
| **Database** | Your MotherDuck database name |
| **Username** | `postgres` |
| **Password** | Your [MotherDuck access token](/key-tasks/authenticating-and-connecting-to-motherduck/authenticating-to-motherduck) (see [Choose the right token](#choose-the-right-token)) |
| **SSL** | Enabled |
| **Verify SSL** | Disabled, unless your Looker instance is configured for certificate verification |
| **Additional JDBC Parameters** | `options=--compatibility-mode=looker` (see [Maintain user-duckling affinity](#maintain-user-duckling-affinity-with-session_name) to also pin users to read scaling ducklings) |
In **Optional Settings**, enable **SSL** and **Database Connection Pooling**. Leave **Verify SSL** disabled unless your Looker instance is configured to verify the server certificate.
After configuring the connection, click **Test these settings**. The test runs against MotherDuck and produces a known cancellation warning that is safe to ignore (see [Connection test behavior](#connection-test-behavior)). Click **Add Connection** to save.
## Required: compatibility mode parameter
You **must** set the following in the **Additional JDBC Parameters** field:
```text
options=--compatibility-mode=looker
```
This parameter does two important things:
- Enables symmetric aggregates
- Enables Persistent Derived Table (PDT) support
Without it, symmetric aggregate queries return incorrect results and PDT builds fail.
## Enable connection pooling
Enable **database connection pooling** in Looker's connection settings. DuckDB is optimized for large analytical queries rather than high volumes of short concurrent connections, so pooling reduces connection overhead and improves overall stability. This is the recommended configuration for MotherDuck.
## Maintain user-duckling affinity with session_name
When you connect Looker with a [read scaling token](#choose-the-right-token), each new connection is assigned to one of the read scaling replicas ("ducklings") in your pool. By default Looker does not identify individual end users to MotherDuck, so a given user's queries can land on different ducklings and miss the warm cache.
You can pin each Looker user to a consistent read scaling duckling by passing the [`session_name` parameter](/key-tasks/authenticating-and-connecting-to-motherduck/read-scaling/#session-affinity-with-session-name). MotherDuck routes all connections that share the same `session_name` value to the same replica, which improves cache reuse and gives that user a more consistent view of the data — while still letting the read scaling fleet scale out across many users.
Looker can substitute a [user attribute](https://cloud.google.com/looker/docs/admin-panel-users-user-attributes) into the connection's **Additional JDBC Parameters** at connect time. Pass the user attribute as `session_name` under `options=`, alongside the required compatibility-mode flag:
```text
options=--session_name={{ _user_attributes['email'] }} --compatibility-mode=looker
```
In this example, `email` is the user attribute used as the passthrough identifier, so each Looker user's queries are routed to a single read scaling duckling. Any configured Looker user attribute can be used instead of `email` — pick a value that is stable and unique per user (for example, a user ID or a hashed identifier for privacy).
:::note
Set up the passthrough user attribute in **Admin → Users → User Attributes** in Looker before referencing it in the connection. See the [Looker user attributes documentation](https://cloud.google.com/looker/docs/admin-panel-users-user-attributes) for details. This is most useful for customer-facing / embedded analytics, where each end user should reuse their own duckling's warm cache.
:::
## Choose the right token
MotherDuck supports two token types. Choose based on how your Looker deployment will use the connection:
| Token type | Use when | Notes |
|---|---|---|
| **Read scaling token** | Reporting / BI usage with many concurrent users (reads only) | Recommended for the main Looker connection when PDT writes are not needed on this connection. |
| **Read/write token** | PDT builds, or any connection that needs to write tables | Looker supports configuring a separate PDT connection — you can use a read/write token there while keeping a read scaling token on the main connection. |
## Connection test behavior
When you run Looker's built-in connection test, you may see a warning that query cancellation does not work. This is expected and can be safely ignored. The warning is produced because the test cancellation query itself fails due to memory consumption — not because the cancellation mechanism is broken. Production query cancellation is unaffected.
## Troubleshooting
| Symptom | Resolution |
|---|---|
| Symmetric aggregate queries fail or return incorrect results | Ensure `options=--compatibility-mode=looker` is set in Additional JDBC Parameters. |
| PDT build fails or Explore intermittently errors | Check **Admin → PDT → PDT Details** for build status and last SQL. Confirm the table exists in your MotherDuck scratch schema. |
| Connection test shows cancellation warning | Expected behavior. The warning appears only during the test query and does not affect production query cancellation. |
| Read scaling users aren't reusing a warm cache / land on different ducklings | Add `--session_name={{ _user_attributes[''] }}` under `options=` in Additional JDBC Parameters so each user is pinned to one duckling (see [Maintain user-duckling affinity](#maintain-user-duckling-affinity-with-session_name)). |
## Additional information
- [Postgres endpoint reference](/sql-reference/postgres-endpoint) for connection parameters, SSL options, and limitations
- [Connect through the Postgres endpoint](/key-tasks/authenticating-and-connecting-to-motherduck/postgres-endpoint/) for a general how-to guide
- [Read scaling and session_name](/key-tasks/authenticating-and-connecting-to-motherduck/read-scaling/#session-affinity-with-session-name)
- [Looker documentation: Connecting Looker to your database](https://cloud.google.com/looker/docs/db-config-postgresql)
- [Looker documentation: User attributes](https://cloud.google.com/looker/docs/admin-panel-users-user-attributes)
---
Source: https://motherduck.com/docs/integrations/bi-tools/metabase
# Metabase
> Connect Metabase to MotherDuck through the Postgres endpoint on Metabase Cloud, or the DuckDB driver plugin on self-hosted instances.
[Metabase](https://www.metabase.com/) is an open source analytics and BI platform for data visualization and exploration. Connect it to MotherDuck in one of two ways:
- **[Metabase Cloud](#metabase-cloud)**: connect through the MotherDuck [Postgres endpoint](/key-tasks/authenticating-and-connecting-to-motherduck/postgres-endpoint).
- **[Self-hosted Metabase](#self-hosted-metabase)**: install the DuckDB driver plugin, or connect through the Postgres endpoint.
## Metabase Cloud
:::info[Preview]
The [Postgres endpoint](/key-tasks/authenticating-and-connecting-to-motherduck/postgres-endpoint) is in [preview](/about-motherduck/feature-stages/). Features and behavior may change.
:::
Metabase Cloud does not support installing custom drivers like the DuckDB plugin. Instead, connect Metabase Cloud to MotherDuck using the [Postgres endpoint](/key-tasks/authenticating-and-connecting-to-motherduck/postgres-endpoint). You must set two connection options so that Metabase syncs your schema correctly — see [Required connection options](#required-connection-options) below.
### Prerequisites
- A Metabase Cloud instance with admin access
- A [MotherDuck access token](/key-tasks/authenticating-and-connecting-to-motherduck/authenticating-to-motherduck)
### Connect to MotherDuck
1. In Metabase, go to **Admin** > **Databases** and click **Add database** (or edit an existing connection).
2. Set the **Database type** to **PostgreSQL** and fill in the connection details:
| Field | Value |
|-------|-------|
| **Display name** | MotherDuck (or any name you prefer) |
| **Host** | Your MotherDuck Postgres host (for example, `pg.us-east-1-aws.motherduck.com`). Find yours at [MotherDuck Postgres settings](https://app.motherduck.com/settings/postgres). |
| **Port** | `5432` |
| **Database name** | Your MotherDuck database name |
| **Username** | `postgres` |
| **Password** | Your [MotherDuck access token](/key-tasks/authenticating-and-connecting-to-motherduck/authenticating-to-motherduck) |
3. Scroll down and enable **Use a secure connection (SSL)**.
4. If you have a company or your own SSL certificate you can set **SSL Mode** to `verify-full` and use this. Otherwise, we'll use the Let's Encrypt certificate authority and set **SSL Mode** to `verify-ca`.
5. For the **SSL Root Certificate (PEM)**, select **Uploaded file path** and upload the [Let's Encrypt ISRG Root X1](https://letsencrypt.org/certs/isrgrootx1.pem) certificate (`isrgrootx1.pem`).
.src)
.src)
6. Click **Show advanced options**, then:
- In **Additional JDBC connection string options**, paste the [required connection options](#required-connection-options) string.
- Turn **off** **Allow unfolding of JSON columns** (see [JSON columns](#json-columns)).
7. Click **Save changes**.
)
### Required connection options
In the **Additional JDBC connection string options** field, set:
```text
options=-c%20attach_mode%3Dsingle%20-c%20compatibility_mode%3Dmetabase
```
This is a single `options=` value that passes two Postgres startup options to MotherDuck. It decodes to `-c attach_mode=single -c compatibility_mode=metabase`, where:
- **`compatibility_mode=metabase`** makes MotherDuck return catalog metadata in the exact shape Metabase expects. Without it, Metabase's field-sync query fails on recent Metabase versions and tables sync with **no fields** ("Table has no Fields associated with it").
- **`attach_mode=single`** scopes the connection to the database you connect to, instead of every database in your account. This keeps schema-sync queries light and lets Metabase populate table row-count estimates.
:::warning
Enter the value **exactly as shown**, fully URL-encoded: `%20` for each space and `%3D` for each `=`. If you separate the two options with a literal space or `&`, or leave the inner `=` un-encoded, Metabase keeps only the first option and silently drops `attach_mode=single` during sync.
:::
### JSON columns
Turn **off** **Allow unfolding of JSON columns** in the connection's advanced options. When unfolding is enabled, Metabase expands JSON columns into virtual fields and queries them with the PostgreSQL `#>>` path operator, which the Postgres endpoint does not support — those questions fail. With unfolding off, JSON columns sync as regular fields and everything else works.
### Known limitations
Foreign-key relationships and indexes do not sync automatically over the Postgres endpoint, so the query builder's implicit (automatic) joins are unavailable. Tables, fields, types, primary keys, comments, and row counts all sync normally, and explicit joins in native SQL or the query builder work. To use relationships in the query builder, define them manually under **Admin** > **Table Metadata** by setting a field's type to **Foreign Key** and choosing its target.
### Troubleshooting
| Symptom | Resolution |
|---|---|
| Tables sync but have **no fields** ("Table has no Fields associated with it") | Confirm `compatibility_mode=metabase` is present in **Additional JDBC connection string options**, and that the value is fully URL-encoded (`%20`/`%3D`) exactly as shown. Then re-sync the database schema. |
| Schema browsing shows tables from other databases, or row counts are missing | Confirm `attach_mode=single` is present in the options string. It is silently dropped if the value uses a literal space or `&` between the options, or leaves the inner `=` un-encoded. |
| A question on a JSON field fails to run | Turn off **Allow unfolding of JSON columns** and re-sync (see [JSON columns](#json-columns)). |
| No relationships / implicit joins in the query builder | Expected — define foreign keys manually under **Admin** > **Table Metadata** (see [Known limitations](#known-limitations)). |
## Self-hosted Metabase
Self-hosted Metabase can connect to MotherDuck in two ways: install the DuckDB driver plugin (described below), or connect through the [Postgres endpoint](/key-tasks/authenticating-and-connecting-to-motherduck/postgres-endpoint) using the same steps as [Metabase Cloud](#metabase-cloud). The DuckDB driver runs queries in Metabase's embedded DuckDB and also supports local DuckDB files and DuckLake; the Postgres endpoint routes queries to MotherDuck without a plugin.
### Prerequisites
- Metabase installed (self-hosted)
- Admin access to your Metabase instance
- A [MotherDuck access token](/key-tasks/authenticating-and-connecting-to-motherduck/authenticating-to-motherduck)
### Install the DuckDB driver
### Dockerfile (bundled plugin)
1. Create a `Dockerfile` that includes the latest Metabase plus the DuckDB driver:
```dockerfile
FROM eclipse-temurin:21-jre
ENV MB_PLUGINS_DIR=/plugins
RUN mkdir -p ${MB_PLUGINS_DIR} /app
# Latest Metabase
ADD https://downloads.metabase.com/latest/metabase.jar /app/metabase.jar
# Latest DuckDB driver
ADD https://github.com/MotherDuck-Open-Source/metabase_duckdb_driver/releases/latest/download/duckdb.metabase-driver.jar ${MB_PLUGINS_DIR}/
EXPOSE 3000
CMD ["java", "-jar", "/app/metabase.jar"]
```
2. Build and run:
```bash
docker build -t metabase-duckdb:latest .
docker run -d --name metaduck -p 3000:3000 -e MB_PLUGINS_DIR=/plugins metabase-duckdb:latest
```
Tip: For reproducible builds, pin versions instead of `latest`:
```dockerfile
# Example of pinning versions (replace X.Y.Z)
ADD https://downloads.metabase.com/vX.Y.Z/metabase.jar /app/metabase.jar
ADD https://github.com/MotherDuck-Open-Source/metabase_duckdb_driver/releases/download/1.X.Y/duckdb.metabase-driver.jar ${MB_PLUGINS_DIR}/
```
Note: Use a Debian/Ubuntu-based JRE image (not Alpine) to avoid glibc issues with the DuckDB driver.
### Manual
1. Download the latest DuckDB driver `.jar`:
```bash
curl -L -o duckdb.metabase-driver.jar \
https://github.com/MotherDuck-Open-Source/metabase_duckdb_driver/releases/latest/download/duckdb.metabase-driver.jar
```
1. Copy it to the Metabase plugins directory:
- Standard installation (example): If your `metabase.jar` is at `~/app/metabase.jar`, place the driver in `~/app/plugins/`
```bash
mkdir -p ~/app/plugins
mv duckdb.metabase-driver.jar ~/app/plugins/
```
- On Mac: The plugins directory is `~/Library/Application Support/Metabase/Plugins/` (if you are using a Mac)
```bash
mkdir -p "${HOME}/Library/Application Support/Metabase/Plugins/"
mv duckdb.metabase-driver.jar "${HOME}/Library/Application Support/Metabase/Plugins/"
```
- Custom location or Docker: set `MB_PLUGINS_DIR` to point Metabase at your plugins directory and place the `.jar` there (if you are using a custom location or Docker).
1. Restart Metabase so it picks up the new plugin.
### Remote (SSH)
1. SSH to the host and download to the plugins directory. Replace user/host and adjust `MB_PLUGINS_DIR` as needed.
```bash
ssh user@your-host "bash -lc '
set -euo pipefail
MB_PLUGINS_DIR=${MB_PLUGINS_DIR:-/app/plugins}
mkdir -p "$MB_PLUGINS_DIR"
if command -v wget >/dev/null; then
wget -qO "$MB_PLUGINS_DIR/duckdb.metabase-driver.jar" \
https://github.com/MotherDuck-Open-Source/metabase_duckdb_driver/releases/latest/download/duckdb.metabase-driver.jar
else
curl -L -o "$MB_PLUGINS_DIR/duckdb.metabase-driver.jar" \
https://github.com/MotherDuck-Open-Source/metabase_duckdb_driver/releases/latest/download/duckdb.metabase-driver.jar
fi
'"
```
2. Restart Metabase on the remote host:
- systemd: `ssh user@your-host 'sudo systemctl restart metabase'`
- Docker: `ssh user@your-host 'docker restart '`
:::important
Restart required: Metabase must be restarted after adding or upgrading plugins. Hot-reload of drivers is not supported.
:::
:::tip
Compatibility and upgrades: New DuckDB driver releases are designed to be backward compatible with recent Metabase versions. Upgrading to the latest driver is recommended for bug fixes and stability. If you run a significantly older Metabase version, validate in staging first.
:::
### Add your database connection
After installing the driver, you can add MotherDuck as a data source in Metabase.
1. Log in to Metabase with admin credentials
2. Navigate to **Admin Settings** > **Databases** > **Add Database**
3. Select **DuckDB** as the database type
:::note
Since DuckDB does not do implicit casting by default, the `old_implicit_casting` config is necessary for datetime filtering in Metabase to function. It's recommended to keep it set.
:::
#### Connecting to MotherDuck
To connect to MotherDuck:
1. **Database name**: In the Database file field, enter `md:[database_name]` where `[database_name]` is your MotherDuck database name
2. **MotherDuck token**: Paste your MotherDuck token (retrieve from the [MotherDuck UI](/key-tasks/authenticating-and-connecting-to-motherduck/authenticating-to-motherduck/authenticating-to-motherduck.md))
3. **Configuration**: Enable `old_implicit_casting` (recommended) for proper datetime handling
### DuckLake on Metabase
DuckLake is supported with the DuckDB driver in Metabase. Use the latest DuckDB driver release and a DuckDB version that supports DuckLake (DuckDB v1.3.2 or newer is recommended).
#### MotherDuck-managed DuckLake
If your DuckLake database is managed by MotherDuck, you can connect the same way you connect to any MotherDuck database:
1. Select DuckDB as the database type
2. Database file: `md:[ducklake_database_name]`
3. MotherDuck token: paste your token
4. Keep `old_implicit_casting` enabled (recommended)
No extra Init SQL is required. Query your tables normally in Metabase.
#### Own compute + DuckLake catalog (attach in init SQL)
If you want Metabase’s embedded DuckDB to query a DuckLake stored externally, attach the DuckLake catalog in the connection’s Init SQL. This works for both MotherDuck-managed catalogs and self-managed catalogs.
- Init SQL for a MotherDuck-managed DuckLake catalog:
```sql
-- Attaches the DuckLake metadata catalog hosted in MotherDuck
ATTACH 'ducklake:md:__ducklake_metadata_[database_name]' AS dl1;
```
- Init SQL for a self-managed DuckLake catalog (local metadata DB) with S3 data path:
```sql
-- Replace the path to your DuckLake metadata DB and bucket prefix
ATTACH 'ducklake:/duckdb/my_ducklake_metadata.ducklake' AS dl1 (
DATA_PATH 's3://my_bucket/lake/'
);
```
Once attached, reference tables with the alias, for example: `FROM dl1.my_table`.
### Connecting to a local DuckDB database
To connect to a local DuckDB database:
1. Database file: enter the full path to your DuckDB file (e.g., `/path/to/database.db`)
2. Configuration: enable `old_implicit_casting` (recommended) to ensure proper datetime filtering
:::note
DuckDB's concurrency model supports either one process with read/write permissions, or multiple processes with read permissions, but not both at the same time. This means you will not be able to open a local DuckDB in read-only mode, then the same DuckDB in read-write mode in a different process.
:::
## Configuration best practices
- **Connection pooling**: For production instances, set an appropriate connection pool size based on expected concurrent users
- **Query timeouts**: Configure timeouts in Metabase settings to prevent long-running queries from affecting system performance
- **Data access**: Use database-level permissions in Metabase to control who can access which data sources
## Troubleshooting
| Issue | Solution |
|-------|----------|
| Driver not detected | Ensure driver is in the correct plugins directory and Metabase has been restarted |
| Connection failures | Verify database path (local) or database name and token (MotherDuck) |
| Permission errors | Check file permissions for local databases |
| Datetime filtering issues | Enable `old_implicit_casting` in the connection settings |
| Add MotherDuck token in the connection string | Specify a correct MotherDuck token or MotherDuck database name after the `md:` prefix |
### Updating the MotherDuck token
Metabase keeps long-lived database connections alive. When you update only the MotherDuck token while an existing connection is still cached, Metabase raises `Connection error: Can't open a connection to same database file with a different configuration than existing connections`.
Use one of the following approaches to refresh the token successfully:
1. **Add a cache buster while editing the database.** Edit the connection under **Admin Settings** > **Databases**, then update both the **Database file** field and the **MotherDuck Token** field with a small cache-busting change (for example, append `?refresh=20250917`). Updating both values at the same time forces Metabase to treat the configuration as new. Save the connection, then optionally revert the fields to their clean values once the change is persisted.
2. **Restart Metabase before updating the token.** Restart the Metabase service and, immediately after it starts, go straight to `/admin/databases` to update the token field. Do not open the Metabase home screen before editing the database connection, or the previous connection (with the old token) will be re-established.
### Connecting to a local DuckDB database
To connect to a local DuckDB database:
1. **Database file**: Enter the full path to your DuckDB file (e.g., `/path/to/database.db`)
2. **Configuration**: Enable `old_implicit_casting` (recommended) to ensure proper datetime filtering
3. **Additional settings**:
- **Read only**: Toggle as appropriate for your use case
- **Naming strategy**: Choose your preferred table/field naming strategy
:::note
DuckDB's concurrency model supports either one process with read/write permissions, or multiple processes with read permissions, but not both at the same time. This means you will not be able to open a local DuckDB in read-only mode, then the same DuckDB in read-write mode in a different process.
:::
---
Source: https://motherduck.com/docs/integrations/bi-tools/omni
# Omni
> Modern business intelligence platform for creating interactive dashboards and data visualizations. It integrates with MotherDuck for dashboards, semantic models, and embedded analytics workflows.
## How it works with MotherDuck
Omni connects to MotherDuck as a database connection for modeling, dashboards, AI-assisted exploration, and embedded analytics.
## Prerequisites
- Organization Admin permissions in Omni.
- A MotherDuck database available on MotherDuck v0.10.2 or later.
- A MotherDuck [read scaling token](/key-tasks/authenticating-and-connecting-to-motherduck/read-scaling/) for the Omni connection. This is the recommended default for querying and dashboards.
- A Read/Write token only if you plan to use Omni's table uploads.
## Setup
1. In MotherDuck, create a read scaling token for Omni and copy it before closing the dialog. Omni's own setup documentation suggests a Read/Write token; that only applies if you need table uploads.
2. Optional: create a dedicated schema for Omni table uploads if users need to upload CSVs and join them to modeled data.
3. In Omni, open **Settings** > **Connections**.
4. Select **MotherDuck**.
5. Paste the MotherDuck token and complete the connection form.
6. Create the connection.
## Authentication and configuration
- Use a dedicated token for the Omni connection.
- Configure schema filters to limit what Omni imports into its model.
- Use a separate upload schema if Omni users will upload files. Do not reuse that schema for modeled tables.
- Review timezone settings during setup so dashboard results match your reporting conventions.
:::note
A read scaling token is the recommended default. Only use a Read/Write token if you need Omni's optional table uploads.
::::
## Use cases
- Generate an Omni model from MotherDuck schemas.
- Build BI dashboards and topics on top of MotherDuck.
- Combine user-uploaded files with governed MotherDuck data in Omni.
## Related content
- [Read the Omni announcement for MotherDuck support](https://omni.co/blog/announcing-support-for-motherduck)
- [View the full Omni MotherDuck setup guide](https://docs.omni.co/connect-data/setup/motherduck#connecting-motherduck-to-omni)
- [MotherDuck authentication](/key-tasks/authenticating-and-connecting-to-motherduck/authenticating-to-motherduck)
---
Source: https://motherduck.com/docs/integrations/bi-tools/powerbi/index
# Microsoft Power BI
> Power BI is an interactive data visualization product developed by Microsoft. You can connect Power BI to MotherDuck through the built-in PostgreSQL database connector using MotherDuck's Postgres endpoint.
## Included pages
- [Power BI Desktop with MotherDuck](https://motherduck.com/docs/integrations/bi-tools/powerbi/powerbi-desktop): Connect Power BI Desktop to MotherDuck using the Postgres endpoint for dashboards and reports.
- [Power BI Service with MotherDuck](https://motherduck.com/docs/integrations/bi-tools/powerbi/powerbi-service): Publish Power BI reports to the cloud using the On-Premises Data Gateway and MotherDuck's Postgres endpoint.
- [Power BI custom connector (legacy)](https://motherduck.com/docs/integrations/bi-tools/powerbi/powerbi-custom-connector): Connect Power BI to MotherDuck using the DuckDB ODBC driver and Power Query custom connector.
---
Source: https://motherduck.com/docs/integrations/bi-tools/powerbi/powerbi-custom-connector
# Power BI custom connector (legacy)
> Connect Power BI to MotherDuck using the DuckDB ODBC driver and Power Query custom connector.
:::warning[Legacy]
The custom connector is a legacy approach. Use the [Postgres endpoint setup](./powerbi-desktop.mdx) instead for a simpler connection that doesn't require installing drivers or custom extensions.
:::
The open-source [DuckDB Power Query Connector](https://github.com/motherduckdb/duckdb-power-query-connector/) lets you connect Power BI to DuckDB and MotherDuck using the DuckDB ODBC driver.
## Installing
1. Download the latest MotherDuck-supported DuckDB ODBC driver that matches your Power BI architecture:
- [Windows AMD64](https://github.com/duckdb/duckdb-odbc/releases/download/v1.5.5.0/duckdb_odbc-windows-amd64.zip)
- [Windows ARM64](https://github.com/duckdb/duckdb-odbc/releases/download/v1.5.5.0/duckdb_odbc-windows-arm64.zip)
See [the releases page](https://github.com/duckdb/duckdb-odbc/releases) for other versions and architectures.
For more information about the Windows ODBC Driver, see the [DuckDB Docs page on DuckDB ODBC API on Windows](https://duckdb.org/docs/stable/clients/odbc/windows).
2. Extract the `.zip` archive. Run `odbc_install.exe`. If Windows displays a security warning, click "More information" then "Run Anyway".
3. Optionally, verify the installation in the Registry Editor:
- Open Registry Editor by running `regedit`
- Navigate to `HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\DuckDB`
- Confirm the Driver field shows your installed version
- If incorrect, delete the `DuckDB` registry key and reinstall
4. Configure Power BI security settings to allow loading of custom extensions:
- Go to File -> Options and settings -> Options -> Security -> Data Extensions
- Enable "Allow any extensions to load without validation or warning"
- 
5. Download the latest version of the DuckDB Power Query extension:
- [duckdb-power-query-connector.mez](https://github.com/MotherDuck-Open-Source/duckdb-power-query-connector/releases/latest/download/duckdb-power-query-connector.mez)
6. Create the Custom Connectors directory if it does not yet exist:
- Navigate to `[Documents]\Power BI Desktop\Custom Connectors`
- Create this folder, if it doesn't exist
- Note: If this location does not work you may need to place this in your OneDrive Documents folder instead
7. Copy the `duckdb-power-query-connector.mez` file into the Custom Connectors folder
8. Restart Power BI Desktop
## How to use with Power BI
1. In Power BI Desktop, click "Get Data" -> "More..."
2. Search for "DuckDB" in the connector search box and select the DuckDB connector
3. For MotherDuck connections, you'll need to provide:
- Database Location: Use the `md:` prefix followed by your database name (for example, `md:my_database`). This can also be a local file path (for example, `~\my_database.db`) or an in-memory database (`:memory:`).
- MotherDuck Token: Get your token from [MotherDuck's token page](/key-tasks/authenticating-and-connecting-to-motherduck/authenticating-to-motherduck/#creating-an-access-token).
*For local DuckDB connections:* Enter "localtoken" to enable the connection.
- Read Only (Optional): Set to `true` if you only need read access.
- Saas_mode (Optional): Set to `true` to disable [DuckDB extensions](../../../concepts/duckdb-extensions.md).
- Attach_mode (Optional): Set to `single` to scope the connection to one database (recommended for BI-tool catalog browsers, which can be confused by multiple attached databases). Leave blank to use the default workspace mode and see all databases in your workspace. See [Attach modes](/key-tasks/authenticating-and-connecting-to-motherduck/attach-modes/).
4. Click "OK".
5. Click "Connect".
6. Select the table(s) you want to import. Click "Load".
7. You can query your data and create visualizations.
8. After connecting, you can:
- Browse and select tables from your MotherDuck or DuckDB database
- Use "Transform Data" to modify your queries before loading
- Write custom SQL queries using the "Advanced Editor"
- Import multiple tables in one go
9. Power BI maintains the connection to your MotherDuck or DuckDB database, letting you:
- Refresh data automatically or on-demand
- Create relationships between tables
- Build visualizations and dashboards
- Share reports with other users (requires proper gateway setup)
## Use custom data connectors with an on-premises data gateway
You can use custom data connectors with an on-premises data gateway to connect to data sources that are not supported by default. To do this, you need to install the on-premises data gateway and configure it to use the custom data connector. For more information, see [Use custom data connectors with an on-premises data gateway in Power BI](https://learn.microsoft.com/en-us/power-bi/connect-data/service-gateway-custom-connectors).
There are some limitations with using a custom connector with an on-premises data gateway:
- The folder you create must be accessible to the background gateway service. Folders under user Windows folders or system folders typically aren't accessible. The on-premises data gateway app shows a message if the folder isn't accessible. This limitation doesn't apply to the on-premises data gateway (personal mode).
- If your custom connector is on a network drive, include the fully qualified path in the on-premises data gateway app.
- You can only use one custom connector data source when working in DirectQuery mode. Multiple custom connector data sources don't work with DirectQuery.
## Additional information
- [Power BI documentation](https://learn.microsoft.com/en-us/power-bi/connect-data/)
- [DuckDB Power Query Connector](https://github.com/motherduckdb/duckdb-power-query-connector/)
- [ODBC](/getting-started/interfaces/client-apis/other/odbc/#authenticating-with-an-access-token), for connecting through a DuckDB DSN instead of the connector and authenticating that DSN with an access token
## Troubleshooting
### Missing VCRUNTIME140.dll
If you receive an error about missing `VCRUNTIME140.dll`, you need to install the Microsoft Visual C++ Redistributable. You can download it from [Microsoft's download page](https://www.microsoft.com/en-us/download/details.aspx?id=52685).
### Visual C++ and ODBC issues
:::note
These steps are particularly relevant for Windows Server environments, especially for Windows Server 2019, but may also help resolve issues on other Windows versions.
:::
If you encounter issues with ODBC connectivity or receive errors related to Visual C++ libraries, try these troubleshooting steps:
1. Reinstall the Microsoft Visual C++ Redistributable:
- Download the latest version from [Microsoft's official website](https://learn.microsoft.com/en-us/cpp/windows/latest-supported-vc-redist?view=msvc-170) for your architecture
- Run the installer with administrator privileges
- Restart your computer after installation
- Try connecting to MotherDuck again
2. If you're still experiencing issues, you can use the ODBC Test tool to diagnose the connection:
- Open the ODBC Test tool (typically available in Windows SDK)
- Look for a dropdown menu labeled "hstmt 1: ..."
- Select this option to run test queries
- If queries work in the ODBC Test tool but not in Power BI, this indicates a Power BI-specific configuration issue
If you continue to experience problems after trying these steps:
- Verify that your MotherDuck token is valid and hasn't expired
- Check that your network allows connections to MotherDuck's services
- Confirm you have the latest version of the DuckDB Power Query Connector installed
If you're still experiencing issues, reach out to us at [support@motherduck.com](mailto:support@motherduck.com) and we'll be happy to help you troubleshoot the issue.
---
Source: https://motherduck.com/docs/integrations/bi-tools/powerbi/powerbi-desktop
# Power BI Desktop with MotherDuck
> Connect Power BI Desktop to MotherDuck using the Postgres endpoint for dashboards and reports.
:::info[Preview]
The Postgres endpoint is in [preview](/about-motherduck/feature-stages/). Features and behavior may change.
:::
:::warning[Looking for the custom connector?]
The DuckDB custom connector is a legacy approach. If you still need it, see the [legacy custom connector guide](./powerbi-custom-connector.md).
:::
## Before you start
You'll need:
- [Power BI Desktop](https://www.microsoft.com/en-us/power-platform/products/power-bi/desktop) installed on Windows
- A [MotherDuck access token](/key-tasks/authenticating-and-connecting-to-motherduck/authenticating-to-motherduck)
- Your Postgres host, which you can find at [MotherDuck Postgres settings](https://app.motherduck.com/settings/postgres) (for example, `pg.us-east-1-aws.motherduck.com`)
## Connect to MotherDuck
1. In Power BI Desktop, click **Get data**.
2. Search for **PostgreSQL database** in the connector list and select it.
3. Fill in the connection details:
- **Server**: Your Postgres host (for example, `pg.us-east-1-aws.motherduck.com`). You can find this at [MotherDuck Postgres settings](https://app.motherduck.com/settings/postgres).
- **Database**: Your database or share name in MotherDuck (for example, `sample_data`).
4. Select a data connectivity mode:
- **DirectQuery**: Queries run against MotherDuck in real time. Best for dashboards that need up-to-date data.
- **Import**: Loads a snapshot of the data into Power BI's in-memory model. Best when you want fast local interactions and can refresh on a schedule.
5. Click **OK**.
6. When prompted for credentials, select **Database** on the left and enter:
- **User name**: `postgres`
- **Password**: Your [MotherDuck access token](/key-tasks/authenticating-and-connecting-to-motherduck/authenticating-to-motherduck)
7. Click **Connect**. In the Navigator, select the tables you want to use and click **Load**.
8. You can build visualizations with your MotherDuck data.
## Connection parameters
| Parameter | Value |
|-----------|-------|
| **Server** | `pg.-aws.motherduck.com` (find yours at [Postgres settings](https://app.motherduck.com/settings/postgres) or with [`md_user_info()`](/sql-reference/motherduck-sql-reference/md-user-info)) |
| **Database** | Your database name or share name |
| **User name** | `postgres` |
| **Password** | Your [MotherDuck access token](/key-tasks/authenticating-and-connecting-to-motherduck/authenticating-to-motherduck) |
## Additional information
- [Postgres endpoint reference](/sql-reference/postgres-endpoint) for connection parameters, SSL options, and limitations
- [Connect through the Postgres endpoint](/key-tasks/authenticating-and-connecting-to-motherduck/postgres-endpoint) for a general how-to guide
- [Power BI documentation](https://learn.microsoft.com/en-us/power-bi/connect-data/)
---
Source: https://motherduck.com/docs/integrations/bi-tools/powerbi/powerbi-service
# Power BI Service with MotherDuck
> Publish Power BI reports to the cloud using the On-Premises Data Gateway and MotherDuck's Postgres endpoint.
:::info[Preview]
The Postgres endpoint is in [preview](/about-motherduck/feature-stages/). Features and behavior may change.
:::
Power BI Service is the cloud-based version of Power BI that lets you publish, share, and schedule refreshes for reports and dashboards. To connect Power BI Service to MotherDuck, you need a Microsoft On-Premises Data Gateway that bridges the cloud service to MotherDuck's Postgres endpoint.
Both **Import** and **DirectQuery** modes work through the gateway.
## Before you start
You'll need:
- A published `.pbix` report connected to MotherDuck through the [Power BI Desktop setup](./powerbi-desktop.mdx)
- A [Power BI Pro or Premium Per User](https://www.microsoft.com/en-us/power-platform/products/power-bi/pricing) license (required for sharing reports and using the standard gateway)
- A [MotherDuck access token](/key-tasks/authenticating-and-connecting-to-motherduck/authenticating-to-motherduck)
- A Windows machine to host the gateway (see [Microsoft's gateway requirements](https://learn.microsoft.com/en-us/data-integration/gateway/service-gateway-install#requirements))
## Install the gateway
1. Download the standard gateway installer from [Microsoft's gateway download page](https://aka.ms/on-premises-data-gateway-installer). Download the **standard (enterprise) gateway**, not the personal mode gateway.
2. Run the installer and accept the default installation path.
3. After installation, the configuration wizard opens. Sign in with your **Microsoft work or school account** (the one associated with your Power BI tenant).
4. Select **Register a new gateway on this computer**.
5. Enter a gateway name (for example, `MD-PG-Gateway`) and a recovery key. Store the recovery key securely.
6. Click **Configure** and wait for registration to complete.
**Verify:** The configuration wizard shows "The gateway is online and ready to be used." The Windows service `On-premises data gateway service` should be running in `services.msc`.
## Add a MotherDuck data source
1. In [Power BI Service](https://app.powerbi.com), click the **Settings gear** and select **Manage connections and gateways**.
2. Verify your gateway shows **Online**.
3. Click **+ New** and select **On-premises**.
4. Fill in the connection details:
| Field | Value |
|-------|-------|
| **Gateway cluster name** | Select your gateway |
| **Connection name** | A descriptive name (for example, `MotherDuck-PG-sample_data`) |
| **Data Source Type** | **PostgreSQL** |
| **Server** | Your Postgres host (for example, `pg.us-east-1-aws.motherduck.com`) |
| **Database** | Your MotherDuck database name |
| **Authentication method** | **Basic** |
| **Username** | `postgres` |
| **Password** | Your [MotherDuck access token](/key-tasks/authenticating-and-connecting-to-motherduck/authenticating-to-motherduck) |
| **Encrypted Connection** | Checked |
| **Privacy Level** | Organizational |
5. Click **Create**.
:::warning
The **Server** and **Database** values must match your `.pbix` file character-for-character. If they differ, the published dataset won't find the gateway data source.
:::
## Publish and connect a report
1. In Power BI Desktop, publish your report: **File > Publish > Publish to Power BI** and select a workspace.
2. In Power BI Service, go to the workspace and find the semantic model (dataset).
3. Open **Settings** for the semantic model and expand **Gateway and cloud connections**.
4. Map the connection to your gateway data source.
5. Under **Data source credentials**, click **Edit credentials** and enter:
- Authentication method: **Basic**
- User name: `postgres`
- Password: Your MotherDuck access token
- Encrypted connection: Checked
6. Click **Sign in**.
## Set up scheduled refresh
For reports using **Import** mode, you can configure automatic data refreshes.
1. In the semantic model settings, expand **Refresh**.
2. Toggle **Keep your data up to date** to **On**.
3. Set your refresh frequency and time zone.
4. Click **Apply**.
To verify, trigger a manual refresh: open the semantic model's three-dot menu and select **Refresh now**. All steps should complete with green check marks.
## DirectQuery through the gateway
For reports using **DirectQuery** mode, queries run against MotherDuck in real time through the gateway. No scheduled refresh is needed since data is always live.
After publishing and mapping the gateway data source (steps above), DirectQuery reports work automatically in Power BI Service.
## Connection parameters
| Parameter | Value |
|-----------|-------|
| **Server** | `pg.-aws.motherduck.com` (find yours at [Postgres settings](https://app.motherduck.com/settings/postgres) or with [`md_user_info()`](/sql-reference/motherduck-sql-reference/md-user-info)) |
| **Database** | Your database name |
| **Username** | `postgres` |
| **Password** | Your [MotherDuck access token](/key-tasks/authenticating-and-connecting-to-motherduck/authenticating-to-motherduck) |
| **Encrypted Connection** | Checked |
## Troubleshooting
### Gateway shows offline
Check the gateway machine is on, connected to the network, and the `On-premises data gateway service` Windows service is running. Restart the service if needed.
### Firewall blocking port 5432
If `Test-NetConnection -ComputerName pg.us-east-1-aws.motherduck.com -Port 5432` returns `TcpTestSucceeded: False`, add an outbound firewall rule allowing TCP 5432 to the MotherDuck Postgres host.
### SSL/TLS handshake failure
MotherDuck uses certificates from a publicly trusted CA, so the gateway should trust them by default. If you see "The remote certificate is invalid," run Windows Update to refresh the root CA store, or manually import the ISRG Root X1 certificate into the machine-level Trusted Root Certification Authorities store. After importing, restart the gateway service.
### Credential errors
- The username must be `postgres`.
- The password is your **MotherDuck access token** (starting with `md_`), not your web UI password.
- Check for trailing whitespace in the token.
### Published dataset doesn't see the gateway
The **Server** and **Database** values in the gateway data source must match the `.pbix` file exactly, including case. Recreate the data source with the correct values if they differ.
## Additional information
- [Postgres endpoint reference](/sql-reference/postgres-endpoint) for connection parameters, SSL options, and limitations
- [Connect through the Postgres endpoint](/key-tasks/authenticating-and-connecting-to-motherduck/postgres-endpoint) for a general how-to guide
- [Microsoft gateway documentation](https://learn.microsoft.com/en-us/power-bi/connect-data/service-gateway-onprem)
- [Power BI Service documentation](https://learn.microsoft.com/en-us/power-bi/fundamentals/power-bi-service-overview)
---
Source: https://motherduck.com/docs/integrations/bi-tools/rill-data
# Rill Data
> Rill Data is a data platform for building and visualizing data. It integrates with MotherDuck for dashboards, semantic models, and embedded analytics workflows.
## How it works with MotherDuck
Rill can use MotherDuck as the OLAP engine that powers Rill dashboards. This is useful when your dashboard data already lives in MotherDuck and you do not want to ingest it into a separate Rill-managed engine.
## Prerequisites
- Rill Developer or Rill Cloud.
- A MotherDuck access token.
- The MotherDuck database path and schema Rill should use.
## Setup
1. In MotherDuck, create an access token for Rill.
2. In Rill Developer, add MotherDuck as an OLAP connection through **Add Data**.
3. Rill creates a connector file such as `motherduck.yaml` and stores `MOTHERDUCK_TOKEN` in `.env`.
4. Configure the connector with an `md:` path and schema:
```yaml
type: connector
driver: duckdb
token: "{{ .env.MOTHERDUCK_TOKEN }}"
path: "md:my_database"
schema_name: "my_schema"
```
5. Set the project's `olap_connector` to the MotherDuck connector.
## Authentication and configuration
- Keep `MOTHERDUCK_TOKEN` in `.env` or your Rill Cloud environment variables.
- Use `rill env push` when deploying a project that already has the token in the local project environment.
- Use the Rill connector YAML reference for optional connector parameters.
## Important notes
- Creating a MotherDuck OLAP connection changes the project's default OLAP engine to MotherDuck.
- Metrics view SQL should use DuckDB-compatible syntax because Rill sends dashboard queries to MotherDuck.
## Use cases
- Build fast dashboards on existing MotherDuck tables.
- Use MotherDuck as a bring-your-own OLAP engine for Rill.
- Deploy the same Rill project locally and in Rill Cloud with environment-managed credentials.
## Related content
- [View the full Rill MotherDuck setup guide](https://docs.rilldata.com/developers/build/connectors/olap/motherduck)
- [MotherDuck authentication](/key-tasks/authenticating-and-connecting-to-motherduck/authenticating-to-motherduck)
---
Source: https://motherduck.com/docs/integrations/bi-tools/superset-preset
# Superset & Preset
> Apache Superset is a powerful, open-source data exploration and visualization platform designed to be intuitive and interactive. It allows data professionals to quickly integrate and analyze data from various sources, creating insightful dashboards and charts for better decision making.
[Preset](https://preset.io/) is a cloud-native, user-friendly platform built on Apache Superset. It offers enhanced capabilities and managed services to leverage the power of Superset without needing to handle installation and maintenance.
In this guide, we'll cover how you can use MotherDuck with either Superset or Preset.
## Self-hosted Superset
### Setup
The easy way to get started locally with Superset is to use their [docker-compose configurations.](https://superset.apache.org/docs/installation/installing-superset-using-docker-compose/)
### Adding a database connection to MotherDuck
To make it work with DuckDB & MotherDuck, you will have to install two extra Python packages in your local Superset environment:
- DuckDB SQLAlchemy driver [duckdb-engine](https://github.com/Mause/duckdb_engine)
- DuckDB [duckdb](https://github.com/duckdb/duckdb)
1. Clone the [Superset repository](https://github.com/apache/superset):
```bash
git clone https://github.com/apache/superset.git
```
2. Create a new file in `superset/docker/requirements-local.txt` and add the following packages:
```text
duckdb-engine
duckdb
```
3. Build or run the docker container, depending whether this is the first time you run it or not, with the following command:
```bash
# First time running it
docker-compose up --build
# Subsequent runs
docker-compose up
```
4. Once the container is running, you can access the Superset UI at [http://localhost:8088](http://localhost:8088) or at the address you specified in the `docker-compose.yml` file.
5. Once you are logged in, head over to "Settings" and click on "Database Connections", then click on "+ Database".
6. In the Dropdown, pick "MotherDuck", then enter the database name that you want to connect to and the MotherDuck token of the user or service account.
:::note
If MotherDuck isn't listed, there's probably an error in the installation of the `duckdb-engine`. Review the installation steps under (2) to install this extra python package.
:::
:::info
`Database name` is **optional**. Instead of specifying a database name, you can leave it empty to connect to all databases.
:::
7. Finally, you can test your token/connection is valid by clicking "Test connection" and click "Connect".
Now your MotherDuck database is available in Superset and you can start querying data and making some dashboards!
## Preset
### Setup
You can register a Preset account for [free](https://preset.io/pricing/) (up to 5 users).
Upon your account creation, you will need to create a workspace and be prompted to connect to your data source.
### Adding your first database connection to MotherDuck
When you first setup Preset, you will be offered to create a connection to a database. Preset has a direct integration with MotherDuck, making the connection process simpler.
1. In the Database Connection Dropdown in "Connect your first database", select "MotherDuck" and enter your MotherDuck credentials and database information.
:::note
The Database Name needs to be prefixed with `md:` to connect to MotherDuck.
The Access Token is the token you created in the [MotherDuck dashboard](https://app.motherduck.com).
:::
2. Click "Connect" to verify your connection is valid.
Now your MotherDuck database is available in Preset and you can start creating dashboards immediately!
:::info
You can connect to multiple databases using a single MotherDuck connection.
:::
### Adding additional database connections
When adding more database connections to Preset, you can choose the option of "Get MotherDuck token". This generates a new token from the MotherDuck account you are logged into.
1. Add a database connection by going to "Settings", then "Database Connections". In the Database Connections page, click on "+ Database" in the top right corner.
2. In the dropdown, select "MotherDuck" (see above).
3. Enter your MotherDuck credentials and database information. Here you have the option to generate a new token using the `Get MotherDuck token` button or use a token you previously created.
:::caution
Given that usually BI tools such as Preset and Superset are connected to service accounts, we do not recommend the "Get MotherDuck token" option for production systems but only for testing.
For production systems the recommended approach is to generate an access token for the dedicated service account using the MotherDuck REST API and connect this account to Preset instead.
:::
## Related content
- [SQLAlchemy with DuckDB and MotherDuck](/docs/integrations/language-apis-and-drivers/python/sqlalchemy/)
- [Authenticating to MotherDuck](/docs/key-tasks/authenticating-and-connecting-to-motherduck/authenticating-to-motherduck/)
- [Managing Service Accounts](/docs/key-tasks/authenticating-and-connecting-to-motherduck/authenticating-to-motherduck/)
---
Source: https://motherduck.com/docs/integrations/bi-tools/tableau/index
# Tableau
> Tableau is a widely-used business intelligence and data visualization platform that enables data analysts to build interactive dashboards and reports. You can connect Tableau Cloud to MotherDuck through the built-in PostgreSQL connector using MotherDuck's Postgres endpoint. For Tableau Desktop and Server, use the DuckDB JDBC connector.
## Included pages
- [Tableau Cloud with MotherDuck](https://motherduck.com/docs/integrations/bi-tools/tableau/tableau-cloud): Connect Tableau Cloud to MotherDuck using the Postgres endpoint for dashboards and reports.
- [Tableau Desktop and Server with MotherDuck](https://motherduck.com/docs/integrations/bi-tools/tableau/tableau-desktop): Connect Tableau Desktop or Server to MotherDuck using the DuckDB JDBC driver and Tableau connector.
- [Tableau Bridge (legacy)](https://motherduck.com/docs/integrations/bi-tools/tableau/tableau-bridge): Connect Tableau Cloud to MotherDuck using Tableau Bridge and the DuckDB JDBC connector.
---
Source: https://motherduck.com/docs/integrations/bi-tools/tableau/tableau-bridge
# Tableau Bridge (legacy)
> Connect Tableau Cloud to MotherDuck using Tableau Bridge and the DuckDB JDBC connector.
:::warning[Deprecated]
Connecting through Tableau Bridge is a legacy approach. Use the [Postgres endpoint setup](./tableau-cloud.mdx) instead for a simpler connection that doesn't require Bridge infrastructure.
:::
## How to use Tableau Cloud with MotherDuck through Tableau Bridge
### Setup
This guide assumes you have:
- a [Tableau account](https://www.tableau.com/)
- a Tableau Cloud Site
- a Tableau Desktop installation (with the same version as the Tableau Cloud Server Version) set up with the DuckDB JDBC Driver and Tableau Connector.
If you don't, sign up or ask your organization to purchase a plan, or sign up for a free trial.
### Obtain a PAT token
Follow [Tableau's instructions on creating a PAT token.](https://help.tableau.com/current/server/en-us/security_personal_access_tokens.htm) This token must belong to a site admin.
### Set up Bridge client
Use the [Tableau Bridge client setup instructions](https://help.tableau.com/current/online/en-us/to_bridge_client.htm) to install and set up Bridge client.
1. Make sure the machine where the Bridge client is installed has access to the Database used in the above steps.
Important notes:
> Network access - Because Bridge facilitates connections between your private network data and Tableau Cloud, it requires the ability to make outbound connections through the internet. After the initial outbound connection, communication is bidirectional.
> Required ports - Tableau Bridge uses port 443 to make outbound internet requests to Tableau Cloud and port 80 for certificate validation.
2. Install Bridge client and make sure the Bridge client is signed in to the Tableau Cloud site. You can download the installer from the [Tableau Bridge releases page](https://www.tableau.com/support/releases/bridge).
3. Install the driver and taco files as outlined in the [Tableau connector setup guide](https://help.tableau.com/current/online/en-us/to_sync_local_data.htm#connectors-and-data-types).
- [Windows Server] The driver also needs to be installed here: `C:\Program Files\Tableau\Tableau Bridge\Drivers`
- [Windows Server] The connector also needs to be installed here: `C:\Program Files\Tableau\Connectors`
> Note: Tableau Bridge can be deployed on both Windows or Linux.
### Running Bridge on Linux using Docker (advanced)
If you want to run Bridge centrally on a Linux host, the official guidance recommends running it inside a Docker container, as described in Tableau's documentation on [installing Bridge for Linux in containers](https://help.tableau.com/current/online/en-us/to_bridge_linux_install.htm).
Below is an **example Dockerfile** you can use as a starting point—this includes where to add JDBC drivers and the **DuckDB/MotherDuck** `.taco` file. It's provided for inspiration and may require updates to match your environment or newer versions of the software.
Example Dockerfile
```dockerfile
FROM registry.access.redhat.com/ubi8/ubi:latest
RUN yum update -y
RUN yum install -y glibc-langpack-en
# This is the latest version of Tableau Bridge that is known working with the MotherDuck connector
RUN curl -o /tmp/TableauBridge.rpm -L \
https://downloads.tableau.com/tssoftware/TableauBridge-20243.25.0114.1153.x86_64.rpm && \
ACCEPT_EULA=y yum install -y /tmp/TableauBridge.rpm && \
rm /tmp/TableauBridge.rpm
# Drivers
RUN mkdir -p /opt/tableau/tableau_driver/jdbc
# Connectors (tacos)
RUN mkdir -p /root/Documents/My_Tableau_Bridge_Repository/Connectors
# Download DuckDB JDBC driver and signed taco
RUN curl -o /opt/tableau/tableau_driver/jdbc/duckdb_jdbc-1.3.0.0.jar \
-L https://repo1.maven.org/maven2/org/duckdb/duckdb_jdbc/1.3.0.0/duckdb_jdbc-1.3.0.0.jar && \
curl -o /root/Documents/My_Tableau_Bridge_Repository/Connectors/duckdb_jdbc-v1.1.1-signed.taco \
-L https://github.com/motherduckdb/duckdb-tableau-connector/releases/download/v1.1.1/duckdb_jdbc-v1.1.1-signed.taco
ENV TZ=Europe/Berlin
ENV LC_ALL=en_US.UTF-8
# ----- user specific settings -----
ENV USER_EMAIL=""
ENV PAT_ID=BridgeToken
ENV CLIENT_NAME=""
ENV SITE_NAME=""
ENV POOL_ID=""
# -----------------------------------
CMD /opt/tableau/tableau_bridge/bin/run-bridge.sh -e \
--patTokenId=$PAT_ID \
--userEmail=$USER_EMAIL \
--client=$CLIENT_NAME \
--site=$SITE_NAME \
--patTokenFile="/home/documents/token.txt" \
--poolId=$POOL_ID
```
Key points:
* Build an image that **installs the Bridge RPM** and then copies the DuckDB JDBC driver to `/opt/tableau/tableau_bridge/Drivers` and the connector to `/root/Documents/My_Tableau_Bridge_Repository/Connectors`.
* Start the bridge by calling `run-bridge.sh` and pass the following flags:
* `--patTokenFile /run/secrets/pat.json`
* `--patTokenId `
* `--site `
* `--poolId ` (optional – see note on pools below)
* **PAT naming rule** – the *name* you give the Personal-Access-Token in Tableau **must** be a valid JSON key and must be used **verbatim**
1. as the key in `pat.json` → `{"": ""}`
2. in the `--patTokenId` flag.
A mismatch will result in a silent authentication failure.
* The latest Bridge **2025.1** builds contain a regression that prevents the MotherDuck connector (and several others) from loading. Until Tableau fixes this, pin the image to the **20243.25.0114.1153** release (see discussion in [GitHub issue #22](https://github.com/MotherDuck-Open-Source/duckdb-tableau-connector/issues/22)).
* Bridge listens only on outbound **443/tcp**, so you do **not** need to publish any container ports. If you run a host firewall (for example, `ufw`) remember that Docker bypasses it [[Docker docs](https://docs.docker.com/engine/network/packet-filtering-firewalls/#docker-and-ufw)]. Restrict egress traffic to Tableau Cloud CIDR blocks if your security policy requires it.
* Logs written to `stdout` are useful, but the *detailed* logs live in `/root/Documents/My_Tableau_Bridge_Repository/Log`. Mount this path as a volume or use a side-car to ship the logs to your observability stack.
### Tableau Cloud Bridge pool setup
By default, Tableau places the Bridge in the default pool.
1. In Settings → Bridge page, make sure the Bridge client is connected in the connection Status.
2. In the "Private Network Allowlist" add the domain of the database and select the pool.
)
> **Pool Gotcha**: Some users report that a Linux containerised Bridge never shows up under a custom site pool. If that happens, leave `POOL_ID` blank when starting the client – it will join the legacy **Default** pool and still work with live connections.
### Create embedded data source (live) and workbook
1. Open Tableau desktop and sign in to a Tableau Cloud site.
> Note: Make sure the Tableau Desktop and [Tableau Cloud version](https://help.tableau.com/current/server/en-us/version_server_view.htm) match.
2. Create new Workbook and select the database connector.
3. Connect to the database.
)
4. Set up Datasource to use live connectivity.
5. Create a worksheet with the data.
)
### Publish the workbook to Tableau Cloud
1. Click on "Server > Publish Workbook".
)
2. Select "Publish Separately" under Publish Type and "Embedded password" under Authentication. Select "Maintain connection to a live data source".
)
)
3. Click "Publish Workbook & 1 Data Source".
)
### (Important step!) update Tableau Bridge client in data source
1. Navigate to the newly published data source in Tableau Cloud (in your browser) and click on the "i" icon to open Data Source Details.
)
2. Click on "Change Bridge Client..."
)
3. Change the bridge client from "Site client pool" to your bridge client (the one you set up in the previous section). Click "Save" and close the dialog.
)
4. Check that the data source shows up in your Tableau Bridge status dialog. This dialog is located in the Windows Start bar (in the Icon panel).
)
5. You can access your Published Workbook on your Tableau Cloud Site, or you can create a new Tableau Workbook using the Published Data Source.
)
## Additional information
- [Tableau Documentation](https://help.tableau.com/current/pro/desktop/en-us/gettingstarted_overview.htm)
- [Tableau Exchange Connector DuckDB/MotherDuck](https://exchange.tableau.com/en-gb/products/1021)
- [DuckDB Tableau Connector](https://github.com/MotherDuck-Open-Source/duckdb-tableau-connector/)
---
Source: https://motherduck.com/docs/integrations/bi-tools/tableau/tableau-cloud
# Tableau Cloud with MotherDuck
> Connect Tableau Cloud to MotherDuck using the Postgres endpoint for dashboards and reports.
:::info[Preview]
The Postgres endpoint is in [preview](/about-motherduck/feature-stages/). Features and behavior may change.
:::
:::warning[Looking for the Tableau Bridge setup?]
Connecting through Tableau Bridge is a legacy approach. If you still need it, refer to the [legacy Tableau Bridge guide](./tableau-bridge.md).
:::
## Before you start
You'll need:
- A [Tableau Cloud](https://www.tableau.com/) account
- A [MotherDuck access token](/key-tasks/authenticating-and-connecting-to-motherduck/authenticating-to-motherduck)
- Your Postgres host and port, which you can find at [MotherDuck Postgres settings](https://app.motherduck.com/settings/postgres) (for example, `pg.us-east-1-aws.motherduck.com`)
## Connect to MotherDuck
1. In a Tableau Cloud workbook, click **Connect to Data**.
2. Under the **Connectors** tab, select **PostgreSQL**.
3. Fill in the connection details:
- **Server**: Your Postgres host (for example, `pg.us-east-1-aws.motherduck.com`). Find this at [MotherDuck Postgres settings](https://app.motherduck.com/settings/postgres).
- **Port**: The port from your Postgres settings (for example, `5432`).
- **Database**: Your database name in MotherDuck (for example, `sample_data`).
- **Username**: `postgres`
- **Password**: Your [MotherDuck access token](/key-tasks/authenticating-and-connecting-to-motherduck/authenticating-to-motherduck)
- Check **Require SSL**.
4. Click **Sign In**. Tableau connects to MotherDuck and shows your tables.
5. Select your tables and build visualizations with your MotherDuck data.
## Connection parameters
| Parameter | Value |
|-----------|-------|
| **Server** | `pg.-aws.motherduck.com` (find yours at [Postgres settings](https://app.motherduck.com/settings/postgres) or with [`md_user_info()`](/sql-reference/motherduck-sql-reference/md-user-info)) |
| **Port** | `5432` (find yours at [Postgres settings](https://app.motherduck.com/settings/postgres)) |
| **Database** | Your database name |
| **Username** | `postgres` |
| **Password** | Your [MotherDuck access token](/key-tasks/authenticating-and-connecting-to-motherduck/authenticating-to-motherduck) |
| **Require SSL** | Checked |
## Additional information
- [Postgres endpoint reference](/sql-reference/postgres-endpoint) for connection parameters, SSL options, and limitations
- [Connect through the Postgres endpoint](/key-tasks/authenticating-and-connecting-to-motherduck/postgres-endpoint) for a general how-to guide
- [Tableau documentation](https://help.tableau.com/current/online/en-us/to_connect_live_sql.htm)
---
Source: https://motherduck.com/docs/integrations/bi-tools/tableau/tableau-desktop
# Tableau Desktop and Server with MotherDuck
> Connect Tableau Desktop or Server to MotherDuck using the DuckDB JDBC driver and Tableau connector.
## Tableau Desktop setup for DuckDB and MotherDuck
1. Download a [recent version of the DuckDB JDBC driver](https://repo1.maven.org/maven2/org/duckdb/duckdb_jdbc/) and copy it into the Tableau Drivers directory:
* MacOS: `~/Library/Tableau/Drivers/`
* Windows: `C:\Program Files\Tableau\Drivers`
* Linux: `/opt/tableau/tableau_driver/jdbc`
2. Download the signed tableau connector (aka "Taco file") file from the [latest available release](https://github.com/MotherDuck-Open-Source/duckdb-tableau-connector/releases) and copy it into the Connectors directory:
* Desktop Windows: `C:\Users\[YourUser]\Documents\My Tableau Repository\Connectors`
* Desktop MacOS: `/Users/[YourUser]/Documents/My Tableau Repository/Connectors`
* Server Windows: `C:\ProgramData\Tableau\Tableau Server\data\tabsvc\vizqlserver\Connectors`
* Server Linux: `[Your Tableau Server Install Directory]/data/tabsvc/vizqlserver/Connectors`
## Connecting
Once the Taco is installed, and you have launched Tableau, you can create a new connection by choosing "DuckDB by MotherDuck":
### Local DuckDB database
If you wish to connect to a local DuckDB database, select "Local file" as DuckDB Server option, and use the file picker:
### In-memory database
The driver can be used with an in-memory database by selecting the `In-memory database` DuckDB Server option.
The data will then need to be provided by an Initial SQL string, for example:
```sql
CREATE VIEW my_parquet AS
SELECT *
FROM read_parquet('/path/to/file/my_file.parquet');
```
You can then access it by using the Tableau Data Source editing controls.
### MotherDuck
To connect to MotherDuck, you have two authentication options:
* Token -- provide the value that you [get from MotherDuck UI](/key-tasks/authenticating-and-connecting-to-motherduck/authenticating-to-motherduck/#creating-an-access-token).
* No Authentication -- unless `motherduck_token` environment variable is available to Tableau at startup, you will then be prompted to authenticate when at connection time.
To work with a MotherDuck database in Tableau, you have to provide the database to use when issuing queries.
In `MotherDuck Database` field, provide the name of your database. You don't have to prefix it with `md:`:
## Additional information
* [Tableau Documentation](https://help.tableau.com/current/pro/desktop/en-us/gettingstarted_overview.htm)
* [Tableau Exchange Connector DuckDB/MotherDuck](https://exchange.tableau.com/en-gb/products/1021)
* [DuckDB Tableau Connector](https://github.com/MotherDuck-Open-Source/duckdb-tableau-connector/)
---
Source: https://motherduck.com/docs/integrations/bi-tools/zenlytic
# Zenlytic
> Zenlytic is a data visualization platform for building and visualizing data. It integrates with MotherDuck for dashboards, semantic models, and embedded analytics workflows.
## How it works with MotherDuck
Zenlytic connects to MotherDuck as a data source for governed metrics, dashboards, and AI-assisted analysis.
## Prerequisites
- A Zenlytic workspace with permission to add data sources.
- A MotherDuck service token with access to the databases Zenlytic should use.
- The database name, if you want Zenlytic to connect to one database by default.
## Setup
1. In MotherDuck, create a service token for Zenlytic and copy it.
2. In Zenlytic, open **Settings** > **Data Sources**.
3. Select **Add Data Source** and choose **MotherDuck**.
4. Paste the service token.
5. Optionally enter a database name.
6. Test the connection, then save it.
## Authentication and configuration
- Use a token with read access to the data Zenlytic should model.
- If you omit the database name, configure the target database later in Zenlytic.
- Rotate the token from MotherDuck if a Zenlytic workspace or project no longer needs access.
## Important notes
- Zenlytic's setup guide calls out token permissions as the first troubleshooting check. If the connection fails, verify the token and database name first.
- Keep the token scoped to analytics workloads rather than reusing a broad personal token.
## Use cases
- Build a metrics layer over MotherDuck data.
- Let teams ask governed analytics questions in Zenlytic.
- Connect a specific MotherDuck database to a Zenlytic workspace.
## Related content
- [View the full Zenlytic MotherDuck setup guide](https://docs.zenlytic.com/data-sources/motherduck_setup)
- [MotherDuck authentication](/key-tasks/authenticating-and-connecting-to-motherduck/authenticating-to-motherduck)
---
Source: https://motherduck.com/docs/integrations/cloud-storage/amazon-s3
# Amazon S3
> Amazon S3 is a Data Sources/Sinks service for storing and retrieving data.
## Configure S3 credentials
You can safely store your Amazon S3 credentials in MotherDuck for convenience by creating a `SECRET` object using the [CREATE SECRET](/sql-reference/motherduck-sql-reference/create-secret.md) command. Secrets are scoped to your user account and are not shared with other users in your organization.
### Create a SECRET object
### SQL
```sql
-- to configure a secret manually:
CREATE SECRET IN MOTHERDUCK (
TYPE S3,
KEY_ID 'access_key',
SECRET 'secret_key',
REGION 'us-east-1',
SCOPE 'my-bucket-path'
)
```
:::note
When creating a secret using the `CONFIG` (default) provider, be aware that the credential might be temporary. If so, a `SESSION_TOKEN` field also needs to be set for the secret to work correctly.
:::
```sql
-- to store a secret using your local AWS credentials (from `aws configure` or SSO):
-- if you use AWS SSO, run `aws sso login --profile ` first
CREATE SECRET aws_secret IN MOTHERDUCK (
TYPE S3,
PROVIDER credential_chain,
-- optional: add CHAIN and PROFILE for SSO credentials
CHAIN 'sso',
PROFILE ''
)
```
:::note[Secret validation]
Starting with DuckDB v1.4.0, credentials are validated at secret creation time. If your credentials are not resolvable locally (for example, expired SSO tokens or missing `~/.aws/credentials`), the `CREATE SECRET` command will fail with a `Secret Validation Failure` error. The recommended fix is to use the correct `CHAIN` and `PROFILE` for your credential type (see the SSO example above). If you need to bypass local validation, you can add `VALIDATION 'none'`, but keep in mind that this skips the local check that confirms your credentials are valid before storing them in MotherDuck.
:::
```sql
-- test the s3 credentials
SELECT count(*) FROM 's3:///'
-- browse objects in a bucket or prefix
FROM md_list_files('s3:///')
```
### Python
```python
import duckdb
con = duckdb.connect('md:')
con.sql("CREATE SECRET IN MOTHERDUCK (TYPE S3, KEY_ID 'access_key', SECRET 'secret_key', REGION 'your_bucket_region')")
# testing that our s3 credentials work
con.sql("SELECT count(*) FROM 's3:///'").show()
# 42
```
### UI
Click on your profile to access the `Settings` panel and click on `Secrets` menu.
Then click on `Add secret` in the secrets section.
You will then be prompted to enter your Amazon S3 credentials.
You can update your secret by executing [CREATE OR REPLACE SECRET](/sql-reference/motherduck-sql-reference/create-secret.md) command to overwrite your secret.
### Delete a SECRET object
### SQL
You can use the same method above, using the [DROP SECRET](/sql-reference/motherduck-sql-reference/delete-secret.md) command.
```sql
DROP SECRET
```
### UI
Click on your profile and access the `Settings` menu. Click on the bin icon to delete your current secrets.
### Amazon S3 credentials as **temporary** secrets
MotherDuck supports DuckDB syntax for providing S3 credentials.
```sql
CREATE SECRET (
TYPE S3,
KEY_ID 's3_access_key',
SECRET 's3_secret_key',
REGION 'us-east-1'
)
```
:::note
Local/In-memory secrets are not persisted across sessions.
:::
### Use your local IAM role or SSO session
If you authenticate to AWS with an IAM role, SSO, or instance profile instead of long-lived access keys, use a local DuckDB session with the `credential_chain` provider. DuckDB uses your local AWS setup to get credentials, and MotherDuck's cloud execution engine uses those credentials to read from S3. Grant your AWS identity permission to list the bucket, get its location, and read its objects. Buckets encrypted with AWS KMS also require `kms:Decrypt` permission on the key. MotherDuck doesn't need standing access. For an example policy, see the [AWS S3 secrets troubleshooting guide](/troubleshooting/aws-s3-secrets/).
This pattern is a good fit for one-off or ad hoc loads when you already have a local AWS identity and don't want to store long-lived access keys in MotherDuck. The credentials usually expire with your AWS SSO or STS session.
If your credentials are in a named AWS profile, start DuckDB with that profile after you sign in to AWS:
```bash
AWS_PROFILE= duckdb
```
```sql
-- Connect to MotherDuck
ATTACH 'md:'
-- Use your local AWS identity (IAM role, SSO, or instance profile)
CREATE SECRET my_s3 (
TYPE S3,
PROVIDER credential_chain,
REGION 'us-east-1'
)
-- Read from S3 and write into a MotherDuck table
CREATE TABLE my_db.main.events AS
SELECT * FROM read_parquet('s3:////*.parquet')
-- Verify the data loaded
SELECT count(*) FROM my_db.main.events
```
The `my_s3` secret in this example lives only for the DuckDB session. Run the `CREATE SECRET` statement again after your AWS credentials expire or when you start a new DuckDB session. To check which secret a path uses, run `SELECT * FROM which_secret('s3:///', 's3')`.
:::info
MotherDuck's cloud execution engine makes the request to S3, not your local machine. If your bucket is only reachable from your local network (for example, restricted to a VPC without a public endpoint), the read fails. In that case, set [`MD_RUN = LOCAL`](/sql-reference/motherduck-sql-reference/md-run-parameter/) on the initial S3 read to force it to run in your local DuckDB session. Load the result into a local table, then insert it into MotherDuck:
```sql
CREATE TEMP TABLE local_events AS
SELECT *
FROM read_parquet(
's3:////*.parquet',
MD_RUN = LOCAL
)
CREATE TABLE my_db.main.events AS
SELECT * FROM local_events
```
:::
:::info
Even temporary, in-memory secrets are available to MotherDuck's cloud execution engine when you connect your
local DuckDB instance to MotherDuck. When you query S3, the query runs on MotherDuck's servers, not your local machine,
and MotherDuck uses the best-matching secret to authenticate, whether it is stored locally or in MotherDuck.
For more details, see [CREATE SECRET](/sql-reference/motherduck-sql-reference/create-secret/#querying-with-secrets).
:::
## Troubleshooting
For detailed troubleshooting steps, see the [AWS S3 secrets troubleshooting guide](/troubleshooting/aws-s3-secrets/).
## Browse buckets and files
To inspect storage from SQL before querying specific files:
```sql
FROM md_list_buckets_for_secret('__default_s3')
FROM md_list_files('s3:///')
FROM md_list_files('s3:////')
```
See [`MD_LIST_BUCKETS_FOR_SECRET()`](/sql-reference/motherduck-sql-reference/md-list-buckets-for-secret) and [`MD_LIST_FILES()`](/sql-reference/motherduck-sql-reference/md-list-files) for details.
---
Source: https://motherduck.com/docs/integrations/cloud-storage/azure-blob-storage
# Azure Blob Storage
> Azure Blob is a Data Sources/Sinks service for storing and retrieving data.
## Configure Azure Blob Storage credentials
You can safely store your Azure Blob Storage credentials in MotherDuck for convenience by creating a `SECRET` object using the [CREATE SECRET](/sql-reference/motherduck-sql-reference/create-secret.md) command.
:::note
See [Azure docs](https://learn.microsoft.com/en-gb/azure/storage/common/storage-configure-connection-string#configure-a-connection-string-for-an-azure-storage-account) to find the correct connection string format.
:::
### Create a SECRET object
### SQL
```sql
-- to configure a secret manually:
CREATE SECRET IN MOTHERDUCK (
TYPE AZURE,
CONNECTION_STRING '[your_connection_string]'
);
```
```sql
-- to store a secret configured through `az configure`:
CREATE SECRET az_secret IN MOTHERDUCK (
TYPE AZURE,
PROVIDER credential_chain,
ACCOUNT_NAME 'some-account'
);
```
```sql
-- test the azure credentials
SELECT count(*) FROM 'azure://[container]/[file]'
SELECT * FROM 'azure://[container]/*.csv';
-- browse objects in a container
FROM md_list_files('azure://[container]/', limit := 50);
```
### Python
```python
import duckdb
con = duckdb.connect('md:')
con.sql("CREATE SECRET IN MOTHERDUCK (TYPE AZURE, CONNECTION_STRING '[your_connection_string]')");
# testing that our Azure credentials work
con.sql("SELECT count(*) FROM 'azure://[container]/[file]'").show()
con.sql("SELECT * FROM 'azure://[container]/*.csv'").show()
```
### UI
Click on your profile to access the `Settings` panel and click on `Secrets` menu.
Then click on `Add secret` in the secrets section.
You will then be prompted to enter your Amazon S3 credentials.
### Delete a SECRET object
### SQL
You can use the same method above, using the [DROP SECRET](/sql-reference/motherduck-sql-reference/delete-secret.md) command.
```sql
DROP SECRET ;
```
### UI
Click on your profile and access the `Settings` menu. Click on the bin icon to delete the secret.
### Azure credentials as **temporary** secrets
MotherDuck supports DuckDB syntax for providing Azure credentials.
```sql
CREATE SECRET (
TYPE AZURE,
CONNECTION_STRING '[your_connection_string]'
);
```
or if you use the `az configure` command to store your credentials in the `az` CLI.
```sql
CREATE SECRET az_secret (
TYPE AZURE,
PROVIDER credential_chain,
ACCOUNT_NAME 'some-account'
);
```
:::note
Local/In-memory secrets are not persisted across sessions.
:::
:::info
Even temporary, in-memory secrets are available to MotherDuck's cloud execution engine when you connect your
local DuckDB instance to MotherDuck. When you query Azure Blob Storage, the query runs on MotherDuck's servers, not your local machine,
and MotherDuck uses the best-matching secret to authenticate, whether it is stored locally or in MotherDuck.
For more details, see [CREATE SECRET](/sql-reference/motherduck-sql-reference/create-secret/#querying-with-secrets).
:::
## Browse files in Azure Blob Storage
To inspect a container before querying individual files, use [`MD_LIST_FILES()`](/sql-reference/motherduck-sql-reference/md-list-files):
```sql
FROM md_list_files('azure://[container]/');
FROM md_list_files('az://[container]/path/');
```
---
Source: https://motherduck.com/docs/integrations/cloud-storage/cloudflare-r2
# Cloudflare R2
> Cloudflare R2 is a Data Sources/Sinks service for storing and retrieving data.
## Configure Cloudflare R2 credentials
You can safely store your Cloudflare R2 credentials in MotherDuck for convenience by creating a `SECRET` object using the [CREATE SECRET](/sql-reference/motherduck-sql-reference/create-secret.md) command.
:::note
See [Cloudflare docs](https://developers.cloudflare.com/r2/api/s3/tokens/) to create a Cloudflare access token.
:::
### Create a SECRET object
### SQL
```sql
CREATE SECRET IN MOTHERDUCK (
TYPE R2,
KEY_ID 'your_key_id',
SECRET 'your_secret_key',
ACCOUNT_ID 'your_account_id'
);
```
:::note
The `ACCOUNT_ID` can be found when generating the API token on the endpoint URL `https://.r2.cloudflarestorage.com`.
:::
:::note
R2 buckets are regionless, so you do not need to specify a `REGION` parameter. If provided, it defaults to `auto`.
:::
```sql
-- test the R2 credentials
SELECT count(*) FROM 'r2://[bucket]/[file]'
```
### Python
```python
import duckdb
con = duckdb.connect('md:')
con.sql("CREATE SECRET IN MOTHERDUCK ( TYPE R2, KEY_ID 'your_key_id', SECRET 'your_secret_key', ACCOUNT_ID 'your_account_id' )");
# testing that our R2 credentials work
con.sql("SELECT count(*) FROM 'r2://[bucket]/[file]'").show()
```
### UI
Click on your profile to access the `Settings` panel and click on `Secrets` menu.
Then click on `Add secret` in the secrets section.
Select the Secret Type `R2` and fill in the required fields.
### Delete a SECRET object
### SQL
You can use the same method above, using the [DROP SECRET](/sql-reference/motherduck-sql-reference/delete-secret.md) command.
```sql
DROP SECRET ;
```
### UI
Click on your profile and access the `Settings` menu. Click on the bin icon to delete the secret.
### R2 credentials as **temporary** secrets
MotherDuck supports DuckDB syntax for providing R2 credentials.
```sql
CREATE SECRET (
TYPE R2,
KEY_ID 'your_key_id',
SECRET 'your_secret_key',
ACCOUNT_ID 'your_account_id'
);
```
:::note
Local/In-memory secrets are not persisted across sessions.
:::
:::info
Even temporary, in-memory secrets are available to MotherDuck's cloud execution engine when you connect your
local DuckDB instance to MotherDuck. When you query R2, the query runs on MotherDuck's servers, not your local machine,
and MotherDuck uses the best-matching secret to authenticate, whether it is stored locally or in MotherDuck.
For more details, see [CREATE SECRET](/sql-reference/motherduck-sql-reference/create-secret/#querying-with-secrets).
:::
---
Source: https://motherduck.com/docs/integrations/cloud-storage/google-cloud-storage
# Google Cloud Storage
> With MotherDuck, you can access files in a private Google Cloud Storage (GCS) bucket. This leverages the GCS S3 compatible connection.
## Google Cloud Storage connection process
1. Create an [HMAC key](https://docs.cloud.google.com/storage/docs/authentication/hmackeys) for the service account: Cloud Storage → Settings → Interoperability → Create a key for a service account
2. Save the Access ID and Secret (shown once)
3. Create the DuckDB secret using the HMAC credentials as described below
## Configure Google Cloud Storage credentials
You can safely store your Google Cloud Storage credentials in MotherDuck for convenience by creating a `SECRET` object using the [CREATE SECRET](/sql-reference/motherduck-sql-reference/create-secret.md) command.
### Create a SECRET object
You can safely store your Google Cloud Storage credentials in MotherDuck for convenience by creating a `SECRET` object using the [CREATE SECRET](/sql-reference/motherduck-sql-reference/create-secret.md) command.
### SQL
```sql
CREATE SECRET IN MOTHERDUCK (
TYPE GCS,
KEY_ID 'HMAC_ACCESS_ID',
SECRET 'HMAC_SECRET'
);
-- test GCS credentials
SELECT count(*) FROM 'gcs:///';
```
### Python
```python
import duckdb
con = duckdb.connect('md:')
con.sql("CREATE SECRET IN MOTHERDUCK (TYPE GCS, KEY_ID 'access_key', SECRET 'secret_key')");
# test GCS
con.sql("SELECT count(*) FROM 'gcs:///'").show()
# 42
```
### UI
Click on your profile to access the `Settings` panel and click on `Secrets` menu.
Then click on `Add secret` in the secrets section.
You will then be prompted to enter your Amazon S3 credentials.
You can update your secret by executing [CREATE OR REPLACE SECRET](/sql-reference/motherduck-sql-reference/create-secret.md) command to overwrite your secret.
### Delete a SECRET object
### SQL
You can use the same method above, using the [DROP SECRET](/sql-reference/motherduck-sql-reference/delete-secret.md) command.
```sql
DROP SECRET ;
```
### UI
Click on your profile and access the `Settings` menu. Click on the bin icon to delete your current secrets.
### Google Cloud Storage credentials as **temporary** secrets
MotherDuck supports DuckDB syntax for providing GCS credentials.
```sql
CREATE SECRET (
TYPE GCS,
KEY_ID 's3_access_key',
SECRET 's3_secret_key'
);
```
:::note
Local/In-memory secrets are not persisted across sessions.
:::
:::info
Even temporary, in-memory secrets are available to MotherDuck's cloud execution engine when you connect your
local DuckDB instance to MotherDuck. When you query GCS, the query runs on MotherDuck's servers, not your local machine,
and MotherDuck uses the best-matching secret to authenticate, whether it is stored locally or in MotherDuck.
For more details, see [CREATE SECRET](/sql-reference/motherduck-sql-reference/create-secret/#querying-with-secrets).
:::
## Additional resources
- [Using the S3 compatible connection in GCS](https://docs.cloud.google.com/storage/docs/aws-simple-migration)
- [HMAC Keys in Google Cloud](https://docs.cloud.google.com/storage/docs/authentication/hmackeys)
---
Source: https://motherduck.com/docs/integrations/cloud-storage/hetzner-object-storage
# Hetzner Object Storage
> Hetzner Object Storage is a S3-compatible object storage service.
## Configure Hetzner Object Storage credentials
You can safely store your Hetzner Object Storage credentials in MotherDuck for convenience by creating a `SECRET` object using the [CREATE SECRET](/sql-reference/motherduck-sql-reference/create-secret.md) command.
:::note
See [Hetzner docs](https://docs.hetzner.com/storage/object-storage/getting-started/generating-s3-keys/) to create S3 access keys. Save your secret key immediately as it cannot be viewed again after creation.
:::
### Create a SECRET object
### SQL
```sql
CREATE SECRET IN MOTHERDUCK (
TYPE S3,
KEY_ID 'your_access_key', # provided by Hetzner
SECRET 'your_secret_key', # provided by Hetzner
ENDPOINT 'fsn1.your-objectstorage.com', # provided by Hetzner
SCOPE 'your_bucket_scope' # Example: s3://test-bucket
);
```
:::note
The endpoint must include the location (e.g., fsn1, nbg1, or hel1). Available endpoints:
- `fsn1.your-objectstorage.com` (Falkenstein)
- `nbg1.your-objectstorage.com` (Nuremberg)
- `hel1.your-objectstorage.com` (Helsinki)
:::
```sql
-- test the Hetzner Object Storage credentials
SELECT count(*) FROM 's3://[bucket]/[file]'
```
### Python
```python
import duckdb
con = duckdb.connect('md:')
con.sql("CREATE SECRET IN MOTHERDUCK ( TYPE S3, KEY_ID 'your_access_key', SECRET 'your_secret_key', ENDPOINT 'fsn1.your-objectstorage.com', SCOPE 'your_bucket_scope' )");
# testing that our Hetzner credentials work
con.sql("SELECT count(*) FROM 's3://[bucket]/[file]'").show()
```
### UI
Click on your profile to access the `Settings` panel and click on `Secrets` menu.
Then click on `Add secret` in the secrets section.
Select the Secret Type `S3` and fill in the required fields. Ensure you add the endpoint URL (e.g., `fsn1.your-objectstorage.com`) in the endpoint field.
### Delete a SECRET object
### SQL
You can use the same method above, using the [DROP SECRET](/sql-reference/motherduck-sql-reference/delete-secret.md) command.
```sql
DROP SECRET ;
```
### UI
Click on your profile and access the `Settings` menu. Click on the bin icon to delete the secret.
### Hetzner Object Storage credentials as temporary secrets
MotherDuck supports DuckDB syntax for providing Hetzner Object Storage credentials.
```sql
CREATE SECRET (
TYPE S3,
KEY_ID 'your_access_key',
SECRET 'your_secret_key',
ENDPOINT 'fsn1.your-objectstorage.com',
SCOPE 'your_bucket_scope'
);
```
:::note
Local/In-memory secrets are not persisted across sessions.
:::
:::info
Even temporary, in-memory secrets are available to MotherDuck's cloud execution engine when you connect your
local DuckDB instance to MotherDuck. When you query Hetzner Object Storage, the query runs on MotherDuck's servers, not your local machine,
and MotherDuck uses the best-matching secret to authenticate, whether it is stored locally or in MotherDuck.
For more details, see [CREATE SECRET](/sql-reference/motherduck-sql-reference/create-secret/#querying-with-secrets).
:::
### Multiple locations configuration
If you have buckets in different Hetzner locations, you should be creating scoped secrets:
```sql
-- Secret for Falkenstein location
CREATE SECRET hetzner_fsn1 IN MOTHERDUCK (
TYPE S3,
KEY_ID 'access_key_1',
SECRET 'secret_key_1',
ENDPOINT 'fsn1.your-objectstorage.com',
SCOPE 's3://my-bucket-fsn1'
);
-- Secret for Nuremberg location
CREATE SECRET hetzner_nbg1 IN MOTHERDUCK (
TYPE S3,
KEY_ID 'access_key_2',
SECRET 'secret_key_2',
ENDPOINT 'nbg1.your-objectstorage.com',
SCOPE 's3://my-bucket-nbg1'
);
```
:::tip
By default, each key pair is automatically valid for every bucket within the same Hetzner project. Use bucket policies to restrict access if needed.
:::
---
Source: https://motherduck.com/docs/integrations/cloud-storage/index
# Cloud Storage
> Use MotherDuck with your favorite cloud storage services
MotherDuck integrates with popular cloud storage services to help you manage and store your data.
## Included pages
- [Amazon S3](https://motherduck.com/docs/integrations/cloud-storage/amazon-s3): Amazon S3 is a Data Sources/Sinks service for storing and retrieving data.
- [Azure Blob Storage](https://motherduck.com/docs/integrations/cloud-storage/azure-blob-storage): Azure Blob is a Data Sources/Sinks service for storing and retrieving data.
- [Cloudflare R2](https://motherduck.com/docs/integrations/cloud-storage/cloudflare-r2): Cloudflare R2 is a Data Sources/Sinks service for storing and retrieving data.
- [Google Cloud Storage](https://motherduck.com/docs/integrations/cloud-storage/google-cloud-storage): With MotherDuck, you can access files in a private Google Cloud Storage (GCS) bucket. This leverages the GCS S3 compatible connection.
- [Hetzner Object Storage](https://motherduck.com/docs/integrations/cloud-storage/hetzner-object-storage): Hetzner Object Storage is a S3-compatible object storage service.
- [Tigris](https://motherduck.com/docs/integrations/cloud-storage/tigris): With MotherDuck, you can access files in a private Tigris bucket. Tigris is a globally distributed S3-compatible object storage service that provides low latency anywhere in the world.
---
Source: https://motherduck.com/docs/integrations/cloud-storage/tigris
# Tigris
> With MotherDuck, you can access files in a private Tigris bucket. Tigris is a globally distributed S3-compatible object storage service that provides low latency anywhere in the world.
## Tigris requirements
To get started using Tigris with MotherDuck, you need to:
1. Create a new bucket at [storage.new](https://storage.new) if you don't have one
2. Create an access keypair for that bucket at [storage.new/accesskey](https://storage.new/accesskey)
3. Configure MotherDuck to use Tigris
4. Query files in Tigris
When creating a bucket, you can select from different storage tiers:
- Standard (default) - Best for general use cases
- Infrequent Access - Cheaper than Standard, but charges per gigabyte of retrieval
- Instant Retrieval Archive - For long-term storage with urgent access needs
- Archive - For long-term storage where retrieval time is not critical
## Configure Tigris credentials
### Create a SECRET object
:::note
If you are using multiple secrets, the `SCOPE` parameter will make sure MotherDuck knows which one to use. You can validate which secret to use with [`which_secret`](https://duckdb.org/docs/stable/configuration/secrets_manager).
As an example, see below:
```sql
FROM which_secret('s3://my-other-bucket/file.parquet', 's3');
```
:::
### SQL
```sql
CREATE OR REPLACE PERSISTENT SECRET tigris (
TYPE s3,
PROVIDER config,
KEY_ID 'tid_access_key_id',
SECRET 'tsec_secret_access_key',
REGION 'auto',
ENDPOINT 't3.storage.dev',
URL_STYLE 'vhost',
SCOPE 's3://my_bucket'
);
-- test Tigris credentials
SELECT count(*) FROM 's3:///';
```
### Python
```python
import duckdb
con = duckdb.connect('md:')
con.sql("""
CREATE OR REPLACE PERSISTENT SECRET tigris (
TYPE s3,
PROVIDER config,
KEY_ID 'tid_access_key_id',
SECRET 'tsec_secret_access_key',
REGION 'auto',
ENDPOINT 't3.storage.dev',
URL_STYLE 'vhost',
SCOPE 's3://my_bucket'
)
""")
# test Tigris
con.sql("SELECT count(*) FROM 's3:///'").show()
```
### UI
Adding Tigris secrets through the UI is not supported. Please add them using SQL statements.
### Delete a SECRET object
### SQL
```sql
DROP SECRET tigris;
```
### Tigris credentials as **temporary** secrets
You can also create temporary secrets that are not persisted across sessions:
```sql
CREATE OR REPLACE SECRET (
TYPE s3,
PROVIDER config,
KEY_ID 'tid_access_key_id',
SECRET 'tsec_secret_access_key',
REGION 'auto',
ENDPOINT 't3.storage.dev',
URL_STYLE 'vhost'
);
```
:::note
Local/In-memory secrets are not persisted across sessions.
:::
:::info
Even temporary, in-memory secrets are available to MotherDuck's cloud execution engine when you connect your
local DuckDB instance to MotherDuck. When you query Tigris, the query runs on MotherDuck's servers, not your local machine,
and MotherDuck uses the best-matching secret to authenticate, whether it is stored locally or in MotherDuck.
For more details, see [CREATE SECRET](/sql-reference/motherduck-sql-reference/create-secret/#querying-with-secrets).
:::
---
Source: https://motherduck.com/docs/integrations/data-quality/great-expectations
# Great Expectations
> Great Expectations is a data quality management platform combining data quality and data governance. It integrates with MotherDuck for table monitoring as part of data quality and observability workflows.
## How it works with MotherDuck
1. Create a connection or data source in Great Expectations for the MotherDuck database you want to monitor.
2. Provide a MotherDuck access token or supported connection string in the tool's secret manager.
3. Start with a narrow set of schemas or tables, then expand checks and monitoring after the connection is validated.
## Related content
- [View the full process in the Great Expectations documentation](https://docs.greatexpectations.io/docs/guides/connecting_to_your_data/database/duckdb)
- [MotherDuck authentication](/key-tasks/authenticating-and-connecting-to-motherduck/authenticating-to-motherduck)
- [Connecting to MotherDuck](/key-tasks/authenticating-and-connecting-to-motherduck/connecting-to-motherduck)
---
Source: https://motherduck.com/docs/integrations/data-quality/index
# Data Quality Tools
> Monitor and maintain data quality in MotherDuck
Ensure data quality and reliability in MotherDuck using these integrated tools.
## Included pages
- [Great Expectations](https://motherduck.com/docs/integrations/data-quality/great-expectations): Great Expectations is a data quality management platform combining data quality and data governance. It integrates with MotherDuck for table monitoring as part of data quality and observability workflows.
- [Monte Carlo](https://motherduck.com/docs/integrations/data-quality/monte-carlo): End-to-end data observability platform for monitoring data quality and reliability. It integrates with MotherDuck for table monitoring as part of data quality and observability workflows.
- [Secoda](https://motherduck.com/docs/integrations/data-quality/secoda): Data discovery and documentation platform for managing data quality and governance. It integrates with MotherDuck for table monitoring as part of data quality and observability workflows.
- [Soda](https://motherduck.com/docs/integrations/data-quality/soda): Data quality platform for monitoring and managing data quality in your pipelines. It integrates with MotherDuck for table monitoring as part of data quality and observability workflows.
---
Source: https://motherduck.com/docs/integrations/data-quality/monte-carlo
# Monte Carlo
> End-to-end data observability platform for monitoring data quality and reliability. It integrates with MotherDuck for table monitoring as part of data quality and observability workflows.
## How it works with MotherDuck
Monte Carlo connects to MotherDuck for data observability workflows, including custom SQL monitors over MotherDuck tables.
## Prerequisites
- A Monte Carlo account with access to the MotherDuck integration.
- A MotherDuck account and database access for the objects you want to monitor.
- A MotherDuck service token that can run the monitor queries.
## Setup
1. In MotherDuck, create a service token for Monte Carlo.
2. In Monte Carlo, add MotherDuck as a data source.
3. Enter the MotherDuck connection details requested by Monte Carlo.
4. Validate the connection.
5. Create custom SQL monitors for the tables, freshness checks, or metrics you need to observe.
## Authentication and configuration
- Use a dedicated token for Monte Carlo monitoring.
- Grant access to the databases and schemas where monitor queries run.
- Keep monitor queries scoped to the smallest useful result set.
## Important notes
- Monte Carlo lists the MotherDuck integration as public preview in its documentation. Confirm current availability and support requirements with Monte Carlo before relying on it for production alerting.
- Query complexity and result size affect monitor performance.
## Use cases
- Monitor freshness or row-count expectations for MotherDuck tables.
- Run custom SQL checks against curated analytics models.
- Route MotherDuck data quality incidents into existing Monte Carlo notification workflows.
## Related content
- [View the full Monte Carlo MotherDuck setup guide](https://docs.getmontecarlo.com/docs/motherduck)
- [MotherDuck authentication](/key-tasks/authenticating-and-connecting-to-motherduck/authenticating-to-motherduck)
- [Connecting to MotherDuck](/key-tasks/authenticating-and-connecting-to-motherduck/connecting-to-motherduck)
---
Source: https://motherduck.com/docs/integrations/data-quality/secoda
# Secoda
> Data discovery and documentation platform for managing data quality and governance. It integrates with MotherDuck for table monitoring as part of data quality and observability workflows.
## How it works with MotherDuck
Secoda connects to MotherDuck as a data warehouse integration for metadata extraction, catalog search, documentation, lineage, and governance workflows.
## Prerequisites
- A Secoda workspace with permission to add integrations.
- A MotherDuck service token.
- Access to the MotherDuck databases and schemas Secoda should catalog.
## Setup
1. In MotherDuck, create or copy a service token.
2. In Secoda, open the **Integrations** tab.
3. Select **Add Integration**.
4. Search for and select **MotherDuck**.
5. Paste the MotherDuck service token.
6. Connect the integration and let Secoda extract metadata.
## Authentication and configuration
- Use a dedicated service token for Secoda.
- Limit the token to the data assets Secoda should discover and document.
- Configure ownership, documentation, and governance rules in Secoda after the metadata sync completes.
## Important notes
- Secoda's setup requires only the MotherDuck token from the MotherDuck side.
- If assets do not appear after connecting, first verify the token and database access for the account that created it.
## Use cases
- Catalog MotherDuck tables, views, schemas, and columns.
- Generate and maintain table documentation in Secoda.
- Add governance context, lineage, and quality monitoring around MotherDuck assets.
## Related content
- [View the full Secoda MotherDuck setup guide](https://docs.secoda.co/integrations/data-warehouses/motherduck)
- [MotherDuck authentication](/key-tasks/authenticating-and-connecting-to-motherduck/authenticating-to-motherduck)
- [Connecting to MotherDuck](/key-tasks/authenticating-and-connecting-to-motherduck/connecting-to-motherduck)
---
Source: https://motherduck.com/docs/integrations/data-quality/soda
# Soda
> Data quality platform for monitoring and managing data quality in your pipelines. It integrates with MotherDuck for table monitoring as part of data quality and observability workflows.
## How it works with MotherDuck
Soda connects to MotherDuck through the `soda-duckdb` package and runs quality scans against a MotherDuck `md:` database connection.
## Prerequisites
- Soda installed in the environment that will run scans.
- The `soda-duckdb` package.
- A MotherDuck access token and database path.
## Setup
1. Install the Soda DuckDB package:
```bash
pip install soda-duckdb
```
2. Add a MotherDuck data source to your Soda configuration:
```yaml
data_source motherduck:
type: duckdb
database: "md:sample_data?motherduck_token="
read_only: true
```
3. Test the connection:
```bash
soda test-connection -d motherduck -c configuration.yml -V
```
## Authentication and configuration
- The MotherDuck token can be passed in the `md:` connection string shown in Soda's reference configuration.
- Store the token through your deployment secret manager or CI secret store before rendering the Soda configuration.
- Set `read_only: true` for scan-only workflows.
## Important notes
- Some Soda users report using `path` instead of `database` successfully. If `database` does not work in your environment, test `path` with the same `md:` value.
- Keep Soda checks focused on the tables and columns you need to monitor so scans remain predictable.
## Use cases
- Run SodaCL data quality checks against MotherDuck tables.
- Validate pipeline outputs after loading data into MotherDuck.
- Add MotherDuck quality scans to CI or scheduled data checks.
## Related content
- [View the full Soda MotherDuck setup guide](https://docs.soda.io/data-source-reference/connect-motherduck)
- [MotherDuck authentication](/key-tasks/authenticating-and-connecting-to-motherduck/authenticating-to-motherduck)
- [Connecting to MotherDuck](/key-tasks/authenticating-and-connecting-to-motherduck/connecting-to-motherduck)
---
Source: https://motherduck.com/docs/integrations/data-science-ai/datalab
# Datalab
> Interactive data science platform for exploring and analyzing data with MotherDuck. It integrates with MotherDuck for notebooks, assistants, and AI-powered analysis workflows.
## How it works with MotherDuck
DataLab connects to MotherDuck from a workbook so SQL cells can query MotherDuck data directly.
## Prerequisites
- A DataLab workbook.
- A MotherDuck service token.
- Optional: a default MotherDuck database name for the connection.
## Setup
1. In MotherDuck, create or copy a service token.
2. In DataLab, open a workbook.
3. Select **View** > **Databases**.
4. Select the **+** icon and choose **MotherDuck**.
5. Enter a connection name, paste the service token, and optionally enter a database name.
6. Connect the data source.
## Authentication and configuration
- The service token is required.
- The database name is optional. When set, DataLab connects to that database by default, but other accessible databases can still be queried.
- If your environment requires network allowlisting, use the DataCamp IP addresses shown in the DataLab connection dialog.
## Important notes
- Store the token only in the DataLab connection configuration.
- Use SQL cells to query the connected MotherDuck source after setup.
## Use cases
- Explore MotherDuck tables in notebook-style analysis.
- Combine SQL query results with Python or chart cells in DataLab.
- Share a workbook that uses a managed MotherDuck data connection.
## Related content
- [View the full DataLab MotherDuck setup guide](https://datalab-docs.datacamp.com/connect-to-data/motherduck)
- [MotherDuck Python overview](/integrations/language-apis-and-drivers/python/python-overview)
- [MotherDuck authentication](/key-tasks/authenticating-and-connecting-to-motherduck/authenticating-to-motherduck)
---
Source: https://motherduck.com/docs/integrations/data-science-ai/fabi-ai
# Fabi.ai
> Fabi.ai is an AI-native notebook and BI workspace for Python, SQL, dashboards, and workflows. It integrates with MotherDuck for exploration and analysis.
## How it works with MotherDuck
Fabi.ai connects to MotherDuck as a database source for notebooks, BI workflows, dashboards, and AI-assisted analysis.
## Prerequisites
- A Fabi.ai workspace.
- A MotherDuck service token.
- Network allowlisting if your security policy restricts inbound connections.
## Setup
1. In MotherDuck, create a service token for Fabi.ai.
2. In Fabi.ai, start the data source connection flow and choose **MotherDuck**.
3. Paste the service token into the MotherDuck connection.
4. Save the data source and validate it from a notebook or workflow.
## Authentication and configuration
- Use a dedicated service token for the Fabi.ai workspace.
- Fabi.ai documents the IP addresses to allowlist for MotherDuck connections. Add them if your environment enforces firewall rules.
- Keep the token in Fabi.ai's connection settings or secret manager.
## Important notes
- Fabi.ai's MotherDuck setup is token-based; no local DuckDB file path is needed.
- If a connection fails, verify both the token and any IP allowlist configuration.
## Use cases
- Analyze MotherDuck data in Fabi.ai notebooks.
- Build dashboards and data apps backed by MotherDuck.
- Use Fabi.ai AI workflows against curated MotherDuck datasets.
## Related content
- [View the full Fabi.ai MotherDuck setup guide](https://docs.fabi.ai/integrations_and_connectors/motherduck)
- [MotherDuck Python overview](/integrations/language-apis-and-drivers/python/python-overview)
- [MotherDuck authentication](/key-tasks/authenticating-and-connecting-to-motherduck/authenticating-to-motherduck)
---
Source: https://motherduck.com/docs/integrations/data-science-ai/google-colab
# Google Colab
> Google Colab notebooks can query MotherDuck by installing DuckDB and opening an md: connection from Python.
## How it works with MotherDuck
1. Install DuckDB in the Colab notebook.
2. Store your MotherDuck token in Colab secrets or another notebook-safe secret store.
3. Connect with `duckdb.connect("md:...")` and run SQL from notebook cells.
## Example
```python
%pip install duckdb
import duckdb
con = duckdb.connect('md:my_db')
con.sql('SELECT current_database()').show()
```
## Related content
- [Google Colab](https://colab.research.google.com/)
- [MotherDuck Python overview](/integrations/language-apis-and-drivers/python/python-overview)
- [MotherDuck authentication](/key-tasks/authenticating-and-connecting-to-motherduck/authenticating-to-motherduck)
---
Source: https://motherduck.com/docs/integrations/data-science-ai/index
# Data Science & AI Tools
> Use MotherDuck with your favorite data science and AI tools
MotherDuck integrates with popular data science and AI tools to help you build powerful machine learning and AI applications.
## Included pages
- [Marimo](https://motherduck.com/docs/integrations/data-science-ai/marimo): marimo is a reactive notebook for Python and SQL that models notebooks as dataflow graphs. When you run a cell or interact with a UI element, marimo automatically runs affected cells (or marks them as stale), keeping code and outputs consistent and preventing bugs before they happen. Every marimo notebook is stored as pure Python, executable as a script, and deployable as an app.
- [Datalab](https://motherduck.com/docs/integrations/data-science-ai/datalab): Interactive data science platform for exploring and analyzing data with MotherDuck. It integrates with MotherDuck for notebooks, assistants, and AI-powered analysis workflows.
- [Fabi.ai](https://motherduck.com/docs/integrations/data-science-ai/fabi-ai): Fabi.ai is an AI-native notebook and BI workspace for Python, SQL, dashboards, and workflows. It integrates with MotherDuck for exploration and analysis.
- [Google Colab](https://motherduck.com/docs/integrations/data-science-ai/google-colab): Google Colab notebooks can query MotherDuck by installing DuckDB and opening an md: connection from Python.
- [Jupyter](https://motherduck.com/docs/integrations/data-science-ai/jupyter): Jupyter notebooks can query MotherDuck through the DuckDB Python package and an md: connection string.
- [LangChain](https://motherduck.com/docs/integrations/data-science-ai/langchain): LangChain is a framework for building and deploying language models. It integrates with MotherDuck for notebooks, assistants, and AI-powered analysis workflows.
- [LlamaIndex](https://motherduck.com/docs/integrations/data-science-ai/llamaindex): LlamaIndex is a framework for building and deploying language models. It integrates with MotherDuck for notebooks, assistants, and AI-powered analysis workflows.
- [Wobby](https://motherduck.com/docs/integrations/data-science-ai/wobby): Wobby provides AI analysts for delivering business-ready insights in Slack or Teams. It integrates with MotherDuck for connecting those analysis workflows to your data.
---
Source: https://motherduck.com/docs/integrations/data-science-ai/jupyter
# Jupyter
> Jupyter notebooks can query MotherDuck through the DuckDB Python package and an md: connection string.
## How it works with MotherDuck
1. Install DuckDB in the notebook environment.
2. Provide a MotherDuck access token with an environment variable or connection parameter.
3. Use DuckDB SQL from Python cells to explore or transform MotherDuck data.
## Example
```python
import duckdb
con = duckdb.connect('md:my_db')
con.sql('SELECT current_database()').show()
```
## Related content
- [DuckDB Jupyter documentation](https://duckdb.org/docs/current/guides/python/jupyter.html)
- [MotherDuck Python overview](/integrations/language-apis-and-drivers/python/python-overview)
- [MotherDuck authentication](/key-tasks/authenticating-and-connecting-to-motherduck/authenticating-to-motherduck)
---
Source: https://motherduck.com/docs/integrations/data-science-ai/langchain
# LangChain
> LangChain is a framework for building and deploying language models. It integrates with MotherDuck for notebooks, assistants, and AI-powered analysis workflows.
## How it works with MotherDuck
1. Create a workspace, notebook, or assistant configuration in LangChain.
2. Use the MotherDuck token, service token, or connection string required by the integration.
3. Run a small query such as `SELECT current_database()` before adding larger analytical workflows.
## Related content
- [View the full process in the LangChain documentation](https://python.langchain.com/docs/integrations/providers/duckdb)
- [MotherDuck Python overview](/integrations/language-apis-and-drivers/python/python-overview)
- [MotherDuck authentication](/key-tasks/authenticating-and-connecting-to-motherduck/authenticating-to-motherduck)
---
Source: https://motherduck.com/docs/integrations/data-science-ai/llamaindex
# LlamaIndex
> LlamaIndex is a framework for building and deploying language models. It integrates with MotherDuck for notebooks, assistants, and AI-powered analysis workflows.
## How it works with MotherDuck
1. Create a workspace, notebook, or assistant configuration in LlamaIndex.
2. Use the MotherDuck token, service token, or connection string required by the integration.
3. Run a small query such as `SELECT current_database()` before adding larger analytical workflows.
## Related content
- [View the full process in the LlamaIndex documentation](https://docs.llamaindex.ai/en/stable/api_reference/storage/vector_store/duckdb/)
- [MotherDuck Python overview](/integrations/language-apis-and-drivers/python/python-overview)
- [MotherDuck authentication](/key-tasks/authenticating-and-connecting-to-motherduck/authenticating-to-motherduck)
---
Source: https://motherduck.com/docs/integrations/data-science-ai/marimo
# Marimo
> marimo is a reactive notebook for Python and SQL that models notebooks as dataflow graphs. When you run a cell or interact with a UI element, marimo automatically runs affected cells (or marks them as stale), keeping code and outputs consistent and preventing bugs before they happen. Every marimo notebook is stored as pure Python, executable as a script, and deployable as an app.
## Getting started
### Installation
First, install marimo with SQL support:
### pip
```bash
pip install "marimo[sql]"
```
### uv
```bash
uv pip install "marimo[sql]"
```
### conda
```bash
conda install -c conda-forge marimo duckdb polars
```
### Authentication
There are two ways to authenticate:
1. **Interactive Authentication**: When you first connect to MotherDuck (e.g. `ATTACH 'md:my_db'`), marimo will open a browser window for authentication.
2. **Token-based Authentication**: Set your MotherDuck token as an environment variable:
```bash
export motherduck_token="your_token"
```
You can find your token in the MotherDuck UI under Account Settings.
## Using MotherDuck
First, open your first notebook:
```bash
marimo edit my_notebook.py
```
### 1. Connecting and database discovery
### SQL
```sql
ATTACH IF NOT EXISTS 'md:my_db'
```
### Python
```python
import duckdb
# Connect to MotherDuck
duckdb.sql("ATTACH IF NOT EXISTS 'md:my_db'")
```
You will be prompted to authenticate with MotherDuck when you run the above cell. This will open a browser window where you can log in and authorize your marimo notebook to access your MotherDuck database. To avoid being prompted each time you open a notebook, you can set the `motherduck_token` environment variable:
```bash
export motherduck_token="your_token"
marimo edit my_notebook.py
```
Once connected, your MotherDuck tables are automatically discovered in the Datasources Panel:
_Browse your MotherDuck databases_
### 2. Writing SQL queries
You can query your MotherDuck db using SQL cells in marimo. Here's an example of how to query a table and display the results using marimo:
_Query a MotherDuck table_
marimo's reactive execution model extends into SQL queries, so changes to your SQL will automatically trigger downstream computations for dependent cells (or optionally mark cells as stale for expensive computations).
### 3. Mixing SQL and Python
marimo lets you combine SQL queries with Python code:
_Mixing SQL and Python_
## Example notebook
For a full example of using MotherDuck with marimo, check out this [example notebook](https://github.com/marimo-team/marimo/blob/main/examples/sql/connect_to_motherduck.py).
---
Source: https://motherduck.com/docs/integrations/data-science-ai/wobby
# Wobby
> Wobby provides AI analysts for delivering business-ready insights in Slack or Teams. It integrates with MotherDuck for connecting those analysis workflows to your data.
## How it works with MotherDuck
Wobby connects to MotherDuck as a data source for AI analyst workflows.
## Prerequisites
- A Wobby workspace.
- A valid MotherDuck access token.
- The MotherDuck database name and schemas Wobby should query.
## Setup
1. In MotherDuck, create an access token and copy it.
2. In Wobby, open **Connections**.
3. Select the plus button and choose **MotherDuck**.
4. Enter a display name.
5. Enter the database name. If your database path is `md:my_database`, enter `my_database`.
6. Paste the access token and select the schemas to connect.
7. Test and save the connection.
## Authentication and configuration
- Use a dedicated token for Wobby.
- Select only the schemas Wobby should use for AI analysis.
- Treat the access token like a password and rotate it if access changes.
## Important notes
- Wobby expects the database name, not the full `md:` connection string.
- If the test fails, check the token, database name, and selected schemas first.
## Use cases
- Let Wobby agents answer questions over MotherDuck data.
- Connect specific schemas to a business-facing AI analyst workflow.
- Use MotherDuck as the analytical source for Slack or Teams insights.
## Related content
- [View the full Wobby MotherDuck setup guide](https://docs.wobby.ai/connections/connect-a-data-source/motherduck)
- [MotherDuck Python overview](/integrations/language-apis-and-drivers/python/python-overview)
- [MotherDuck authentication](/key-tasks/authenticating-and-connecting-to-motherduck/authenticating-to-motherduck)
---
Source: https://motherduck.com/docs/integrations/databases/bigquery
# BigQuery
> Load data from Google BigQuery into MotherDuck using the duckdb-bigquery community extension.
BigQuery is Google Cloud's fully-managed, serverless data warehouse that lets you run SQL queries on the processing power of Google's infrastructure.
To load data from BigQuery into MotherDuck, use the [`duckdb-bigquery` community extension](https://github.com/hafenkran/duckdb-bigquery). It reads through the BigQuery Storage Read API with parallel streams, filter pushdown, and Arrow compression — and loads results straight into DuckDB or MotherDuck without any glue code.
## Prerequisites
- DuckDB installed (using the CLI or Python).
- Access to a GCP project with BigQuery enabled.
- Valid Google Cloud credentials, provided through one of:
- the `GOOGLE_APPLICATION_CREDENTIALS` environment variable, or
- `gcloud auth application-default login`.
Minimum required IAM roles:
- `BigQuery Data Editor`
- `BigQuery Job User`
## Loading data from BigQuery into MotherDuck
The following examples use the [DuckDB CLI](/getting-started/interfaces/connect-query-from-duckdb-cli.mdx), but you can use any [DuckDB or MotherDuck client](/getting-started/interfaces/interfaces.mdx).
### Install and load the extension
```sql
INSTALL bigquery FROM community;
LOAD bigquery;
```
### Attach a BigQuery project
To read data from your project, attach it like you would attach a DuckDB database:
```sql
ATTACH 'project=my-gcp-project' AS bq (TYPE bigquery, READ_ONLY);
```
To read from a public dataset, use the following syntax:
```sql
ATTACH 'project=bigquery-public-data dataset=pypi billing_project=my-gcp-project'
AS bq_public (TYPE bigquery, READ_ONLY);
```
### Query a table
Once attached, you can query BigQuery tables directly using standard SQL syntax:
```sql
SELECT * FROM bq.dataset_name.table_name LIMIT 10;
```
Behind the scenes, this uses `bigquery_scan`. The extension also exposes two functions you can call directly:
**`bigquery_scan`** — for direct reads from a single table:
```sql
SELECT * FROM bigquery_scan('my_gcp_project.my_dataset.my_table');
```
**`bigquery_query`** — for custom [GoogleSQL](https://cloud.google.com/bigquery/docs/introduction-sql), including views and external tables that the Storage Read API can't access on its own:
```sql
SELECT * FROM bigquery_query(
'my_gcp_project',
'SELECT * FROM `my_gcp_project.my_dataset.my_table` WHERE column = "value"'
);
```
Both functions share the same Arrow scan engine. For very large reads, you can enable parallel read streams by relaxing DuckDB's default ordering guarantee:
```sql
SET preserve_insertion_order = FALSE;
```
### Load data into MotherDuck
Verify the `motherduck_token` environment variable is set, then attach MotherDuck:
```sql
ATTACH 'md:';
```
Use `CREATE TABLE ... AS` to create a new table, or `INSERT INTO ... SELECT` to append data to an existing one:
```sql
CREATE DATABASE IF NOT EXISTS pypi_playground;
USE pypi_playground;
CREATE TABLE IF NOT EXISTS duckdb_sample AS
SELECT *
FROM bq_public.pypi.file_downloads
WHERE project = 'duckdb'
AND timestamp = TIMESTAMP '2025-05-26 00:00:00'
LIMIT 100;
```
---
Source: https://motherduck.com/docs/integrations/databases/index
# Databases
> Use MotherDuck with your favorite databases
MotherDuck integrates directly with popular databases to help you build data pipelines and applications.
## Included pages
- [BigQuery](https://motherduck.com/docs/integrations/databases/bigquery): Load data from Google BigQuery into MotherDuck using the duckdb-bigquery community extension.
- [PostgreSQL](https://motherduck.com/docs/integrations/databases/postgres): Advanced open-source relational database with powerful features and extensibility.
- [PlanetScale](https://motherduck.com/docs/integrations/databases/planetscale): PlanetScale offers hosted PostgreSQL and MySQL Vitess Databases. MotherDuck supports PlanetScale Postgres via the pg_duckdb extension, as well as the Postgres Connector. In our internal benchmarking, pg_duckdb offers 100x or greater query acceleration for analytical queries when compared to vanilla Postgres.
- [SQL Server](https://motherduck.com/docs/integrations/databases/sql-server): Use the SQL Server replication guide when you need to read tables or queries from SQL Server and write the results to MotherDuck. The guide covers Python, pyodbc, SQL Server authentication, and loading dataframe results into MotherDuck.
- [MySQL](https://motherduck.com/docs/integrations/databases/mysql): MySQL is a relational database commonly used for application data. DuckDB's MySQL extension can read from MySQL-compatible databases, which lets you copy selected data into MotherDuck from a DuckDB client.
- [Supabase](https://motherduck.com/docs/integrations/databases/supabase): Supabase is a Postgres platform for building applications with a managed database, APIs, authentication, storage, and realtime features. Supabase's documented DuckDB Wrapper can query MotherDuck from a Supabase Postgres database through a foreign data wrapper.
---
Source: https://motherduck.com/docs/integrations/databases/mysql
# MySQL
> MySQL is a relational database commonly used for application data. DuckDB's MySQL extension can read from MySQL-compatible databases, which lets you copy selected data into MotherDuck from a DuckDB client.
## How it works with MotherDuck
1. Connect to MotherDuck from the DuckDB CLI, Python, or another DuckDB client.
2. Install and load DuckDB's MySQL extension in that session.
3. Attach the MySQL database, then create MotherDuck tables from selected MySQL tables or queries.
## Example
```sql
INSTALL mysql;
LOAD mysql;
ATTACH 'host=localhost port=3306 user=my_user password=my_password database=my_database'
AS mysql_db (TYPE mysql);
CREATE TABLE my_table AS
SELECT *
FROM mysql_db.my_schema.my_table;
```
## Related content
- [DuckDB MySQL extension documentation](https://duckdb.org/docs/current/core_extensions/mysql.html)
- [Loading data from PostgreSQL-compatible sources](/key-tasks/loading-data-into-motherduck/loading-data-from-postgres)
- [Running hybrid queries](/key-tasks/running-hybrid-queries)
---
Source: https://motherduck.com/docs/integrations/databases/planetscale
# PlanetScale
> PlanetScale offers hosted PostgreSQL and MySQL Vitess Databases. MotherDuck supports PlanetScale Postgres via the pg_duckdb extension, as well as the Postgres Connector. In our internal benchmarking, pg_duckdb offers 100x or greater query acceleration for analytical queries when compared to vanilla Postgres.
## Prerequisites
Before connecting PlanetScale to MotherDuck, ensure you have:
- A PlanetScale account with a Postgres database created
- The `pg_duckdb` extension enabled in your PlanetScale database (see [PlanetScale extension documentation](https://planetscale.com/docs/postgres/extensions/pg_duckdb))
- A MotherDuck account and authentication token (get your token from the [MotherDuck dashboard](https://app.motherduck.com))
- Database connection credentials from your PlanetScale dashboard (host, port, username, password, database name)
## Connecting pg_duckdb to MotherDuck
To run pg_duckdb, ensure you add it to your [extensions in PlanetScale](https://planetscale.com/docs/postgres/extensions/pg_duckdb).
:::tip
Review the configuration parameters before deploying the extension. Once deployed, you can connect to MotherDuck with the following SQL statements.
:::
```sql
-- Grant necessary permissions to the PlanetScale superuser
GRANT CREATE ON SCHEMA public to pscale_superuser;
-- Create the pg_duckdb extension in your Postgres database
CREATE EXTENSION pg_duckdb;
-- Enable a MotherDuck connection with your authentication token
CALL duckdb.enable_motherduck();
```
To swap tokens, you can drop the MotherDuck connection and then re-add with:
```sql
-- Remove the existing MotherDuck server connection
DROP SERVER motherduck CASCADE;
-- Re-enable MotherDuck with a new authentication token
CALL duckdb.enable_motherduck();
```
### Using read replicas with PlanetScale
:::info
Pg_duckdb will automatically round-robin between your replicas when you use a read-only token. When switching between a read-write and a read-only token, you will want to snapshot your database and then force sync as part of the hand-off.
:::
Switching from read-write to read-only is done with the following SQL statement in Postgres:
```sql
-- Create a snapshot of your MotherDuck database to ensure consistency
SELECT * FROM duckdb.raw_query('CREATE SNAPSHOT OF ');
-- Drop the existing MotherDuck connection
DROP SERVER motherduck CASCADE;
-- Re-enable MotherDuck with your read-only token
CALL duckdb.enable_motherduck();
-- Refresh the database to sync with the snapshot
SELECT * FROM duckdb.raw_query('REFRESH DATABASE ');
```
### Reading from MotherDuck
:::info
By default, data in [MotherDuck is mapped to Postgres in two different ways](https://github.com/duckdb/pg_duckdb/blob/main/docs/motherduck.md#schema-mapping). This is because MotherDuck is designed to hold many databases in its global catalog, while Postgres traditionally has a single database in its catalog.
- For data in `my_db.main`, it is mapped directly to the `public` schema in the Postgres database.
- For data in any other database & schema, it is mapped to `ddb$database$schema` in the Postgres database.
:::
Once the catalog is in sync between MotherDuck and Postgres, the data can be queried directly from Postgres. If it is out of sync for any reason, it can be re-sync'd with the following SQL command:
```sql
-- Terminate the pg_duckdb sync worker to force a re-sync
SELECT * FROM pg_terminate_backend((
SELECT pid FROM pg_stat_activity WHERE backend_type = 'pg_duckdb sync worker'
));
```
#### Sample MotherDuck queries
Once the catalog is synchronized to Postgres, we can query the data as if it was normal data in Postgres.
```sql
-- Query data from a MotherDuck database and schema
-- Note: Non-main schemas use the ddb$database$schema naming convention
SELECT *
FROM "ddb$sample_data$nyc".taxi
ORDER BY tpep_dropoff_datetime DESC
LIMIT 10;
```
You can also join with data in Postgres.
```sql
-- Join MotherDuck data with local Postgres tables
SELECT a.col1, b.col2
-- MotherDuck table from a non-main schema
FROM "ddb$my_database$my_schema".my_table AS a
-- Local Postgres table in the public schema
LEFT JOIN public.another_table AS b on a.key = b.key
```
The DuckDB `iceberg_scan` function also works as well:
```sql
-- Use DuckDB's iceberg_scan function to query Iceberg tables
SELECT COUNT(*)
FROM iceberg_scan('https://motherduck-demo.s3.amazonaws.com/iceberg/lineitem_iceberg', allow_moved_paths := true)
```
:::info
Two special helper functions exist to run queries directly with DuckDB:
- **`duckdb.query`**: Returns tabular data, use for SELECT queries
- **`duckdb.raw_query`**: Returns void, use for DDL queries such as Snapshot Creation and Database Refresh. This function keeps the database in-sync when handing off between read and write nodes.
:::
```sql
-- Use duckdb.query for SELECT queries that return tabular data
-- This example lists all databases in MotherDuck
SELECT * FROM duckdb.query('FROM md_databases()')
```
```sql
-- Use duckdb.raw_query for DDL queries that return void
-- This example drops a table in MotherDuck
SELECT * FROM duckdb.raw_query('DROP TABLE my_database.my_schema.some_table')
```
### Replicating data to MotherDuck
:::tip
For smaller tables, data can be replicated using simple SQL statements.
:::
```sql
-- Create a table in MotherDuck and populate it with data from Postgres
-- Replace my_database and my_schema with your target database and schema names
CREATE TABLE "ddb$my_database$my_schema".my_table USING duckdb AS
SELECT * FROM public.my_table
```
:::tip
For larger tables, state management, and tighter SLAs & requirements, MotherDuck offers [integrations to various other ingestion partners](/integrations/ingestion/).
:::
### Further reading
The [pg_duckdb github repo](https://github.com/duckdb/pg_duckdb) contains [further documentation](https://github.com/duckdb/pg_duckdb/blob/main/docs/README.md) of all available functions.
For ease of finding the documentation, a table of the documentation sections is below:
| Topic | Description |
|-------|-------------|
| [**Functions**](https://github.com/duckdb/pg_duckdb/blob/main/docs/functions.md) | Complete reference for all available functions |
| [**Syntax Guide & Gotchas**](https://github.com/duckdb/pg_duckdb/blob/main/docs/gotchas_and_syntax.md) | Quick reference for common SQL patterns and things to know |
| [**Types**](https://github.com/duckdb/pg_duckdb/blob/main/docs/types.md) | Supported data types and type mappings |
| [**Extensions**](https://github.com/duckdb/pg_duckdb/blob/main/docs/extensions.md) | DuckDB extension installation and usage |
| [**Settings**](https://github.com/duckdb/pg_duckdb/blob/main/docs/settings.md) | Configuration options and parameters |
| [**Transactions**](https://github.com/duckdb/pg_duckdb/blob/main/docs/transactions.md) | Transaction behavior and limitations |
## Connecting with the Postgres extension
You can also connect to PlanetScale Postgres with the DuckDB Postgres extension. This approach lets you query PlanetScale data directly from DuckDB or MotherDuck.
### Install and load the extension
```sql
-- Install the Postgres extension from DuckDB's extension registry
INSTALL postgres;
-- Load the extension to enable Postgres connectivity
LOAD postgres;
-- Attach your PlanetScale database using a connection string
ATTACH '' AS postgres_db (TYPE postgres);
```
### Connection string format
The connection string format follows PostgreSQL's standard connection parameters. Here's an example with explanations:
```sql
ATTACH 'host= port= user= password= dbname= sslmode=require'
AS planetscale (TYPE postgres);
```
**Connection Parameters:**
- `host`: Your PlanetScale database hostname (found in your PlanetScale dashboard)
- `port`: The database port (typically 3306 for MySQL or 5432 for Postgres)
- `user`: Your PlanetScale database username
- `password`: Your PlanetScale database password
- `dbname`: The name of your database in PlanetScale
- `sslmode=require`: Ensures SSL encryption is used (required for PlanetScale)
:::info
The above connection string works with DuckDB. PlanetScale suggests also using the `sslnegotiation` and `sslrootcert` keys when connecting to Postgres, but these keys are not supported by the `libpq` version that is included in DuckDB. The `sslmode=require` parameter is sufficient for secure connections.
:::
---
Source: https://motherduck.com/docs/integrations/databases/postgres
# PostgreSQL
> Advanced open-source relational database with powerful features and extensibility.
:::tip[Looking for a Postgres-compatible connection to MotherDuck?]
Use the **[Postgres endpoint](/key-tasks/authenticating-and-connecting-to-motherduck/postgres-endpoint/)** to connect any Postgres-wire-compatible client — BI tools, ORMs, serverless runtimes, or languages without a DuckDB SDK — directly to MotherDuck. No extension required.
:::
[PostgreSQL](https://www.postgresql.org) is an object-relational database management system (ORDBMS) based on POSTGRES, Version 4.2, developed at the University of California at Berkeley Computer Science Department. POSTGRES pioneered many concepts that only became available in some commercial database systems much later.
As explained by DuckDB Lab's Hannes Mühleisen in the [explainer blog post](https://duckdb.org/2022/09/30/postgres-scanner.html):
> PostgreSQL is designed for traditional transactional use cases, "OLTP", where rows in tables are created, updated and removed concurrently, and it excels at this. But this design decision makes PostgreSQL far less suitable for analytical use cases, "OLAP", where large chunks of tables are read to create summaries of the stored data. Yet there are many use cases where both transactional and analytical use cases are important, for example when trying to gain the latest business intelligence insights into transactional data.
Choose the PostgreSQL workflow based on where your query needs to run.
## Query MotherDuck from PostgreSQL-compatible clients
Use the [Postgres endpoint](/key-tasks/authenticating-and-connecting-to-motherduck/postgres-endpoint) when an application, BI tool, or serverless runtime needs to connect to MotherDuck through the PostgreSQL wire protocol. This is the preferred path for PostgreSQL-compatible clients because it does not require installing or operating a PostgreSQL extension.
## Load PostgreSQL data into MotherDuck
Use [DuckDB's PostgreSQL extension](/key-tasks/loading-data-into-motherduck/loading-data-from-postgres) when a DuckDB client needs to read from PostgreSQL and copy data into MotherDuck. This workflow is best for one-time loads, backfills, and controlled client-side movement between PostgreSQL, DuckDB, and MotherDuck.
## Run DuckDB from inside PostgreSQL
Use [pg_duckdb](/concepts/pgduckdb) when queries need to run inside a PostgreSQL server with DuckDB or MotherDuck access. This is useful when PostgreSQL-local tables need to be joined with DuckDB or MotherDuck data from the PostgreSQL environment itself.
---
Source: https://motherduck.com/docs/integrations/databases/sql-server
# SQL Server
> Use the SQL Server replication guide when you need to read tables or queries from SQL Server and write the results to MotherDuck. The guide covers Python, pyodbc, SQL Server authentication, and loading dataframe results into MotherDuck.
## How it works with MotherDuck
1. Connect to SQL Server with the Microsoft ODBC driver and `pyodbc`.
2. Read a SQL Server table or query result into a dataframe.
3. Connect to MotherDuck from Python and persist the dataframe as a MotherDuck table.
## Related content
- [Replicating SQL Server tables to MotherDuck](/key-tasks/data-warehousing/replication/sql-server)
- [Loading data into MotherDuck](/key-tasks/loading-data-into-motherduck/)
- [MotherDuck authentication](/key-tasks/authenticating-and-connecting-to-motherduck/authenticating-to-motherduck)
---
Source: https://motherduck.com/docs/integrations/databases/supabase
# Supabase
> Supabase is a Postgres platform for building applications with a managed database, APIs, authentication, storage, and realtime features. Supabase's documented DuckDB Wrapper can query MotherDuck from a Supabase Postgres database through a foreign data wrapper.
## How it works with MotherDuck
1. Enable the Supabase Wrappers extension.
2. Create the `duckdb_wrapper` foreign data wrapper.
3. Store a MotherDuck token in Supabase Vault, then create a foreign server with `type 'md'`, the MotherDuck database name, and the Vault-backed token option.
4. Create a schema for the foreign tables.
5. Import a MotherDuck schema, such as `main`, into Supabase and query the imported foreign tables from Postgres.
```sql
create extension if not exists wrappers with schema extensions;
create foreign data wrapper duckdb_wrapper
handler duckdb_fdw_handler
validator duckdb_fdw_validator;
create server duckdb_server_md
foreign data wrapper duckdb_wrapper
options (
type 'md',
database 'my_db',
vault_motherduck_token ''
);
create schema if not exists duckdb;
import foreign schema "main"
from server duckdb_server_md into duckdb;
select *
from duckdb.my_table
limit 10;
```
The Supabase DuckDB Wrapper is a read path into MotherDuck: it supports querying foreign tables, including `where`, `order by`, and `limit` pushdown, but does not support inserts, updates, deletes, or truncates through the foreign tables.
## Related content
- [View the full process in the Supabase DuckDB Wrapper documentation](https://supabase.com/docs/guides/database/extensions/wrappers/duckdb)
- [MotherDuck authentication](/key-tasks/authenticating-and-connecting-to-motherduck/authenticating-to-motherduck)
- [PostgreSQL and MotherDuck](/integrations/databases/postgres)
---
Source: https://motherduck.com/docs/integrations/dev-tools/index
# Development Tools
> Developer tools and utilities that work with MotherDuck
Use MotherDuck with various development tools and utilities to enhance your workflow.
## Included pages
- [Retool](https://motherduck.com/docs/integrations/dev-tools/retool): Low-code platform for building internal tools and custom business applications with drag-and-drop UI components.
- [Obsidian](https://motherduck.com/docs/integrations/dev-tools/obsidian): Use the DuckDB & MotherDuck Obsidian plugin to query external data from your notes and freeze the results as markdown tables.
- [Puppygraph](https://motherduck.com/docs/integrations/dev-tools/puppygraph): Graph visualization tool for exploring and analyzing data relationships in DuckDB. It integrates with MotherDuck for development workflows that read from or write to MotherDuck.
- [ShadowTraffic](https://motherduck.com/docs/integrations/dev-tools/shadowtraffic): ShadowTraffic is a synthetic data generation tool for simulating production traffic to your backend. It integrates with MotherDuck for development workflows that read from or write to MotherDuck.
- [Vanna](https://motherduck.com/docs/integrations/dev-tools/vanna): Vanna is a data science and AI framework for building and sharing data applications. It integrates with MotherDuck for development workflows that read from or write to MotherDuck.
---
Source: https://motherduck.com/docs/integrations/dev-tools/obsidian
# Obsidian
> Use the DuckDB & MotherDuck Obsidian plugin to query external data from your notes and freeze the results as markdown tables.
The [DuckDB & MotherDuck plugin](https://community.obsidian.md/plugins/duckdb-motherduck) lets you run DuckDB SQL from inside an Obsidian note and freeze the results as a markdown table directly below the query. Local queries run in WASM with no account required. Add a MotherDuck token to query cloud databases or push heavier compute off your laptop.
Both backends can coexist in the same note — each code block picks its connection through the fence type.
## Install
1. In Obsidian, open **Settings → Community plugins → Browse**.
2. Search for **DuckDB & MotherDuck** and select **Install**, then **Enable**.
To use the cloud backend, add a [MotherDuck access token](/key-tasks/authenticating-and-connecting-to-motherduck/authenticating-to-motherduck/#creating-an-access-token) under **Settings → DuckDB & MotherDuck → MotherDuck token**. For shared vaults or scoped access, prefer a [service account token](/key-tasks/service-accounts-guide/create-and-configure-service-accounts/).
:::warning
The MotherDuck token is stored in plaintext in `/.obsidian/plugins/duckdb-motherduck/data.json`. Don't commit or publicly sync a vault that contains it.
:::
## Running queries
Each fenced code block picks its backend from the fence language:
| Fence | Backend | Reaches cloud |
|-------|---------|---------------|
| ` ```duckdb ` | Local DuckDB WASM | No |
| ` ```motherduck ` | MotherDuck WASM client | Yes |
### Local DuckDB
Use a `duckdb` block to query any file format DuckDB reads — Parquet, CSV, JSON, Excel, Iceberg, Delta, or geospatial — from a local path or URL:
````markdown
```duckdb
SELECT
o_orderpriority AS priority,
count(*) AS orders,
round(sum(o_totalprice), 2) AS revenue
FROM read_parquet('https://shell.duckdb.org/data/tpch/0_01/parquet/orders.parquet')
GROUP BY 1
ORDER BY revenue DESC
```
````
In reading mode the block becomes a panel with **Run**, **Freeze**, and **Clear freeze** buttons.
The **Path to local DuckDB file** setting has three modes:
- `:memory:` (default) — ephemeral, reset each time Obsidian restarts.
- A bare filename like `notes.duckdb` — persistent storage in the browser's Origin Private File System. Survives restart, lives outside your vault.
- An absolute path like `/Users/you/data.duckdb` — read an existing `.duckdb` file from disk. Read-only: writes succeed in the worker but don't persist back to the file.
### MotherDuck
Use a `motherduck` block to query your cloud databases:
````markdown
```motherduck
SELECT
type,
count(*) AS items,
round(avg(score), 1) AS avg_score,
round(avg(descendants), 1) AS avg_comments
FROM sample_data.hn.hacker_news
WHERE type IS NOT NULL
GROUP BY 1
ORDER BY items DESC
```
````
Any DuckDB SQL that runs in MotherDuck works here — joins across databases, AI functions, shared datasets, and so on.
## Freezing results
Selecting **Freeze** inserts the query result as a markdown table directly under the SQL block, wrapped in sentinel comments so the next refresh knows what to replace:
````markdown
```motherduck
SELECT brand, sum(revenue) FROM sales GROUP BY 1 ORDER BY 2 DESC LIMIT 10
```
| brand | sum(revenue) |
| ----- | ------------ |
| acme | 42000 |
````
Frozen tables are regular markdown — they diff cleanly in git, render in any editor, and stay readable to agents that scan the vault.
## Scheduled refresh
Pick a cadence in the **Refresh** dropdown above any SQL block to opt that note into auto-refresh. The plugin adds a frontmatter property:
```yaml
---
duckdb-motherduck-refresh: daily
---
```
While Obsidian is running, the plugin sweeps once an hour and re-materializes the frozen tables for any note whose cadence has elapsed. The active editor is skipped to avoid stomping in-progress edits.
Scheduled refresh runs only while Obsidian is open. To refresh while it's closed, trigger the plugin's API from the [Obsidian CLI](https://obsidian.md/help/cli):
```bash
obsidian eval code="app.plugins.getPlugin('duckdb-motherduck').api.refreshFile('path/to/note.md')"
```
Drop that into a cron job, a Claude Code skill, or any agent with shell access.
## Commands
From the command palette:
- **Refresh all queries in this note** — re-runs every block in the current note.
- **Refresh query at cursor** — re-runs and re-freezes only the block at the cursor. Bind a hotkey under **Settings → Hotkeys** for fast iteration.
- **Clear freeze at cursor** — removes the frozen result below the SQL block.
- **Reset DuckDB / MotherDuck connections** — drops both connections. Use after changing the path or token.
## Settings
- **Row cap** — maximum rows rendered inline or written into a frozen table. The runtime stops scanning at `rowCap + 1` rows so heavy queries don't materialize unnecessary data in WASM heap.
- **Cell character cap** — maximum characters per cell in rendered and frozen tables. Default `80`. Longer values are truncated with an ellipsis; hover a truncated cell in the live result to see the full value.
- **Auto-refresh scheduled notes** — global toggle for the hourly sweep.
- **Reset connections after each scheduled refresh** — terminates the WASM workers after each sweep to free memory. Default on.
## Known limitations
- Pointing at an on-disk `.duckdb` file is read-only — writes don't persist back to the file.
- Scheduled refresh runs only while Obsidian is open; use the plugin API plus the Obsidian CLI for external scheduling.
- The MotherDuck token is stored in plaintext in `data.json`. There's no keychain integration.
- Absolute-path mode requires Node integration that isn't available on mobile.
## Source
The plugin is open source under the MIT license at [motherduckdb/obsidian-duckdb-motherduck](https://github.com/motherduckdb/obsidian-duckdb-motherduck).
---
Source: https://motherduck.com/docs/integrations/dev-tools/puppygraph
# Puppygraph
> Graph visualization tool for exploring and analyzing data relationships in DuckDB. It integrates with MotherDuck for development workflows that read from or write to MotherDuck.
## How it works with MotherDuck
1. Create a connection in Puppygraph that targets MotherDuck or DuckDB.
2. Store the MotherDuck token as a secret rather than hard-coding it in project files.
3. Validate the connection with a small query, then build the tool-specific workflow on top of that connection.
## Related content
- [View the full process in the Puppygraph documentation](https://docs.puppygraph.com/getting-started/querying-duckdb-data-as-a-graph)
- [MotherDuck authentication](/key-tasks/authenticating-and-connecting-to-motherduck/authenticating-to-motherduck)
- [Connecting to MotherDuck](/key-tasks/authenticating-and-connecting-to-motherduck/connecting-to-motherduck)
---
Source: https://motherduck.com/docs/integrations/dev-tools/retool
# Retool
> Low-code platform for building internal tools and custom business applications with drag-and-drop UI components.
There are two ways to connect Retool to MotherDuck, depending on whether you use Retool Cloud or self-hosted Retool.
## Retool Cloud (native connector)
Retool Cloud has a native MotherDuck resource type. To connect:
1. Go to **Resources** and select **Create new** > **Resource**.
2. Search for **MotherDuck** and select it.
3. Give the resource a descriptive name (for example, "MotherDuck analytics").
4. Under **Resource credentials**, enter your [MotherDuck access token](/key-tasks/authenticating-and-connecting-to-motherduck/authenticating-to-motherduck/#creating-an-access-token).
5. Optionally enter a **Database name**. Leave it empty to use workspace mode, which lets you query across multiple databases.
6. Click **Test connection**, then **Create resource**.
You can use this resource in your Retool apps to run SQL queries against your MotherDuck databases. The resource supports both SQL mode for reading data and GUI mode for write operations (insert, update, delete, upsert).
### Connection options
You can pass optional key-value pairs under **Connection options** to customize behavior:
| Option | Values | Description |
|--------|--------|-------------|
| `access_mode` | `READ_WRITE`, `READ_ONLY` | Controls whether the connection can write data |
| `attach_mode` | `single`, `workspace` | Sets the [attach mode](/key-tasks/authenticating-and-connecting-to-motherduck/attach-modes/). `single` scopes the connection to one database (useful when querying a specific tenant or to avoid catalog clutter); `workspace` (default) attaches every database in your saved workspace. |
| `TimeZone` | For example, `UTC`, `America/New_York` | Sets the session time zone |
| `default_null_order` | `NULLS_FIRST`, `NULLS_LAST` | Default null ordering for queries |
| `default_order` | `ASC`, `DESC` | Default sort order for queries |
For more details, see the [Retool MotherDuck documentation](https://docs.retool.com/data-sources/guides/connect/motherduck).
### Known limitations
- `BLOB` and `ARRAY` column types are not supported by the native connector. Queries that return these types will fail. Cast these columns to a supported type (for example, using `CAST` or `list_string_agg`) or exclude them from your result set.
## Self-hosted (JDBC)
If you run a self-hosted Retool instance, you can connect to MotherDuck through the [DuckDB JDBC driver](/integrations/language-apis-and-drivers/jdbc-driver/). Your instance needs network access to `motherduck.com` over HTTPS (port 443).
1. In your Retool instance, go to **Resources** and select **Create new**.
2. Choose **JDBC** as the resource type.
3. Use the following JDBC connection string:
```text
jdbc:duckdb:md:?motherduck_token=
```
Replace `` with your MotherDuck database and `` with your [access token](/key-tasks/authenticating-and-connecting-to-motherduck/authenticating-to-motherduck/#creating-an-access-token).
4. Test the connection and save.
For more details on the JDBC driver, see [JDBC driver](/integrations/language-apis-and-drivers/jdbc-driver/).
---
Source: https://motherduck.com/docs/integrations/dev-tools/shadowtraffic
# ShadowTraffic
> ShadowTraffic is a synthetic data generation tool for simulating production traffic to your backend. It integrates with MotherDuck for development workflows that read from or write to MotherDuck.
## How it works with MotherDuck
ShadowTraffic can generate read and write traffic against a MotherDuck database for development, testing, and load-shaping workflows.
## Prerequisites
- ShadowTraffic 1.10.0 or later.
- A MotherDuck token.
- A target MotherDuck database.
## Setup
1. Create a MotherDuck token and store it in an environment variable such as `MOTHERDUCK_TOKEN`.
2. Add a ShadowTraffic connection with `kind: motherduck`:
```json
{
"connections": {
"md": {
"kind": "motherduck",
"connectionConfigs": {
"token": {
"_gen": "env",
"var": "MOTHERDUCK_TOKEN"
},
"db": "mydb"
}
}
}
}
```
3. Add generators that write to tables or run read queries through that connection.
## Authentication and configuration
- Use `token` and `db` for the standard MotherDuck connection.
- Use `jdbcUrl` only when you need to control the full JDBC connection string.
- Use `queryParams` for MotherDuck connection parameters such as `attach_mode`.
- Use `batchConfigs` to tune write batch timing and row count.
## Important notes
- ShadowTraffic writes asynchronously. By default it commits when 1000 ms pass or 10000 rows accumulate, whichever happens first.
- Automatic table creation is enabled by default. Set `tablePolicy: manual` if you want to manage tables yourself.
- For `UPDATE` and `DELETE` operations, generators need a `where` map so ShadowTraffic can identify rows.
## Use cases
- Generate synthetic write traffic into MotherDuck tables.
- Simulate reads and writes while testing downstream systems.
- Use automatic table creation for quick generator iteration, then switch to manual table control for production-like tests.
## Related content
- [View the full ShadowTraffic MotherDuck setup guide](https://docs.shadowtraffic.io/connections/motherduck/)
- [MotherDuck authentication](/key-tasks/authenticating-and-connecting-to-motherduck/authenticating-to-motherduck)
- [Connecting to MotherDuck](/key-tasks/authenticating-and-connecting-to-motherduck/connecting-to-motherduck)
---
Source: https://motherduck.com/docs/integrations/dev-tools/vanna
# Vanna
> Vanna is a data science and AI framework for building and sharing data applications. It integrates with MotherDuck for development workflows that read from or write to MotherDuck.
## How it works with MotherDuck
1. Create a connection in Vanna that targets MotherDuck or DuckDB.
2. Store the MotherDuck token as a secret rather than hard-coding it in project files.
3. Validate the connection with a small query, then build the tool-specific workflow on top of that connection.
## Related content
- [View the full process in the Vanna documentation](https://vanna.ai/docs/)
- [MotherDuck authentication](/key-tasks/authenticating-and-connecting-to-motherduck/authenticating-to-motherduck)
- [Connecting to MotherDuck](/key-tasks/authenticating-and-connecting-to-motherduck/connecting-to-motherduck)
---
Source: https://motherduck.com/docs/integrations/file-formats/apache-iceberg
# Apache Iceberg
> Attach an Iceberg REST catalog as a MotherDuck database to read from and write back to Iceberg tables, or scan individual tables by path.
MotherDuck supports the Apache Iceberg format through the [DuckDB Iceberg extension](https://duckdb.org/docs/current/core_extensions/iceberg/overview).
There are two ways to work with Iceberg in MotherDuck:
- **[Persisted Iceberg catalogs](#persisted-iceberg-catalogs)** — attach an Iceberg REST catalog as a MotherDuck database with `CREATE DATABASE`. The attachment lives in your workspace, so it survives across sessions, and reads and writes run on MotherDuck's compute.
- **[Scanning individual tables](#scanning-individual-iceberg-tables)** — query a single Iceberg table by path with `iceberg_scan`, without attaching a catalog.
## Set up the Iceberg extension
In a fresh local DuckDB environment, install and load the Iceberg extension before connecting to MotherDuck. Do this once per environment, such as a local machine, container, or VM.
```sql
INSTALL iceberg;
LOAD iceberg;
ATTACH 'md:';
```
In Python, install and load the extension before opening the MotherDuck connection:
```python
import duckdb
duckdb.sql("INSTALL iceberg")
duckdb.sql("LOAD iceberg")
conn = duckdb.connect("md:")
```
## Persisted Iceberg catalogs
Attach an [Iceberg REST catalog](https://duckdb.org/docs/stable/core_extensions/iceberg/iceberg_rest_catalogs) as a MotherDuck database. The database persists in your workspace: you attach it once, it appears alongside your other databases, and you don't re-attach it in each new session. Reads and writes run on MotherDuck's [cloud execution engine](/concepts/architecture-and-capabilities#dual-execution).
:::note
Persisted Iceberg catalogs require DuckDB 1.5.2 or later.
:::
MotherDuck works with any Iceberg REST catalog endpoint.
| Catalog | Read | Write |
| :-- | :-- | :-- |
| Amazon S3 Tables | ✅ Yes | ✅ Yes |
| Apache Polaris | ✅ Yes | ✅ Yes |
| AWS Glue | ✅ Yes | ✅ Yes |
| Cloudflare R2 | ✅ Yes | ✅ Yes |
| Databricks Unity Catalog | ✅ Yes | ✅ Yes *[(tables must be backed by external storage)](https://docs.databricks.com/aws/en/iceberg/#access-iceberg-tables-using-external-systems)* |
- Write operations use Iceberg's merge-on-read model and are subject to the [Limitations](#limitations) below.
- **AWS Glue:** `CREATE TABLE` requires an explicit `location`. See [AWS Glue](#aws-glue) for details.
- **AWS Lake Formation:** Access is validated for **reads**. Grants must cover whole tables, so writes through Lake Formation credential vending are not validated. See [Lake Formation permissions](#lake-formation-permissions).
- **Databricks Unity Catalog:** Only for tables stored on external locations. Writes apply to Unity Catalog-managed Iceberg tables. Delta tables exposed through the Iceberg REST endpoint are read-only. See [Databricks](#databricks) for details.
- **Cloudflare R2 Data Catalog:** Table data lives in R2 object storage (S3-compatible). Authenticate with a Cloudflare API token that has R2 Data Catalog permission. See [Cloudflare R2](#cloudflare-r2-data-catalog) for details.
:::warning
Iceberg REST catalog reads and writes run on MotherDuck's cloud compute. Attaching an Iceberg REST catalog directly in a local DuckDB session without MotherDuck is not recommended. Attach the catalog as a MotherDuck database instead.
:::
### Authentication
Store your catalog credentials in a MotherDuck secret. Credentials must live in a secret — the database options accept catalog settings only, not credentials.
```sql
-- OAuth2 client credentials
CREATE SECRET my_iceberg_secret IN MOTHERDUCK (
TYPE ICEBERG,
CLIENT_ID 'my_client_id',
CLIENT_SECRET 'my_client_secret',
OAUTH2_SERVER_URI 'https://my-catalog.example.com/v1/oauth/tokens'
);
-- Bearer token
CREATE SECRET my_iceberg_secret IN MOTHERDUCK (
TYPE ICEBERG,
TOKEN 'my_bearer_token'
);
```
See [`CREATE SECRET`](/sql-reference/motherduck-sql-reference/create-secret#iceberg-secrets) for the full list of Iceberg secret parameters.
### Creating the database
:::note
`CREATE DATABASE ... TYPE ICEBERG` does not create a new Iceberg catalog. It connects to an existing REST catalog and registers it as a MotherDuck database, behaving like an attach. The catalog must already exist at the endpoint you point to.
:::
Create the database with `TYPE ICEBERG`, referencing the secret and the catalog endpoint. A `default_schema` that exists in the catalog is required:
```sql
CREATE DATABASE my_datalake (
TYPE ICEBERG,
"secret" my_iceberg_secret,
endpoint 'https://my-catalog.example.com',
warehouse 'my_warehouse',
default_schema 'default'
);
```
Once attached, browse and query the catalog with standard SQL:
```sql
-- List schemas
SELECT schema_name FROM information_schema.schemata
WHERE catalog_name = 'my_datalake';
-- List tables in a schema
SHOW TABLES FROM my_datalake.my_schema;
SHOW SCHEMAS IN my_datalake;
-- Inspect a table's columns (duckdb_columns() does not list them)
DESCRIBE my_datalake.default.my_table;
-- Query a table
SELECT * FROM my_datalake.default.my_table;
```
Set the database as the active catalog to use unqualified names:
```sql
USE my_datalake;
SELECT * FROM my_table;
```
### Database options
Pass these options in the `CREATE DATABASE` options list. Credentials (`CLIENT_ID`, `CLIENT_SECRET`, `OAUTH2_*`, `TOKEN`) belong in the [secret](#authentication), not here.
| Option | Description |
| :----------------------- | :------------------------------------------------------------------------------------------------------------------ |
| `secret` | Name of the MotherDuck Iceberg or S3 secret holding catalog credentials. Quote as `"secret"`. |
| `endpoint` | URL of the Iceberg REST catalog. Required unless the endpoint is set in the secret or derived from `endpoint_type`. |
| `warehouse` | Catalog warehouse identifier. For S3 Tables, this is the bucket ARN. For Cloudflare R2, this is `_`, a *mandatory input*. |
| `default_schema` | Required. Schema used to resolve unqualified table names. Must exist in the catalog. |
| `endpoint_type` | Selects a well-known catalog flavor, for example `'s3_tables'` or `'glue'`. |
| `default_region` | Per-catalog region override. Defaults to your MotherDuck org region. |
| `read_only` | Attach the catalog as read-only. |
| `access_delegation_mode` | Whether to request vended credentials from the catalog. `'vended_credentials'` (default) requests short-lived, table-scoped credentials when the catalog supports them; `'none'` uses the secret's credentials directly. |
For the full set of catalog options, see the [DuckDB Iceberg REST catalog documentation](https://duckdb.org/docs/stable/core_extensions/iceberg/iceberg_rest_catalogs).
### Changing database options
Use [`ALTER DATABASE`](/sql-reference/motherduck-sql-reference/alter-database#iceberg-databases) to update an attached catalog's configuration. MotherDuck reattaches the catalog right away, so the next query uses the new settings:
```sql
-- Resolve unqualified table names against a different namespace
ALTER DATABASE my_datalake SET default_schema = 'analytics';
-- Point the database at a rotated secret
ALTER DATABASE my_datalake SET secret = 'my_new_iceberg_secret';
```
`secret`, `default_schema`, `default_region`, `access_delegation_mode`, and the catalog behavior toggles can be altered; `secret` and `default_schema` can't be cleared once set.
The options that identify the catalog itself - `endpoint`, `warehouse`, `endpoint_type`, and `read_only` - can't be altered, because changing them points the database at a different catalog: that's a different database, not a reconfigured one. To change one of those, drop the database and create it again. Refer to [`ALTER DATABASE`](/sql-reference/motherduck-sql-reference/alter-database#iceberg-databases) for the full list.
### Amazon S3 Tables
For [Amazon S3 Tables](https://docs.aws.amazon.com/AmazonS3/latest/userguide/s3-tables.html), authenticate with an S3 secret (SigV4) and set `endpoint_type` to `'s3_tables'`. The `warehouse` is the table bucket ARN, and the endpoint is derived from it.
```sql
CREATE SECRET s3_tables_secret IN MOTHERDUCK (
TYPE S3,
KEY_ID '',
SECRET '',
REGION 'us-east-1'
);
CREATE DATABASE my_s3_tables (
TYPE ICEBERG,
endpoint_type 's3_tables',
warehouse 'arn:aws:s3tables:us-east-1::bucket/',
"secret" s3_tables_secret,
default_schema 'default'
);
```
### AWS Glue
For the [AWS Glue Data Catalog](https://docs.aws.amazon.com/glue/latest/dg/connect-glu-iceberg-rest.html), authenticate with an S3 secret (SigV4) and set `endpoint_type` to `'glue'`. The `warehouse` is your AWS account ID, and the endpoint is derived from the secret's `REGION`. Each Glue database becomes a schema; pass one that exists as `default_schema`.
```sql
CREATE SECRET glue_secret IN MOTHERDUCK (
TYPE S3,
KEY_ID '',
SECRET '',
REGION ''
);
CREATE DATABASE my_glue_catalog (
TYPE ICEBERG,
endpoint_type 'glue',
warehouse '',
"secret" glue_secret,
default_schema ''
);
```
If your lake doesn't use AWS Lake Formation, this is the complete setup: the IAM principal in the secret needs the Glue catalog read actions (`glue:GetCatalog`, `glue:GetDatabase`, `glue:GetDatabases`, `glue:GetTable`, `glue:GetTables`) plus `s3:GetObject` on the table locations, and `kms:Decrypt` if the bucket uses SSE-KMS. The rest of this section covers Lake Formation–governed lakes.
#### Lake Formation prerequisites
When your S3 locations are registered with [AWS Lake Formation](https://docs.aws.amazon.com/lake-formation/latest/dg/what-is-lake-formation.html), data access is handled by Lake Formation's credential vending: at query time, AWS issues short-lived, table-scoped S3 credentials to MotherDuck as an external engine. MotherDuck does not vend credentials itself; it presents the secret's IAM principal, and Lake Formation decides what it can read. For tables in registered locations, that principal needs **no S3 permissions**: access is granted per table by your existing Lake Formation permissions, including tag-based access control.
Four one-time settings enable credential vending for external engines:
1. **Allow full table access for external engines.** In the Lake Formation console under **Administration → Application integration settings**, enable *Allow external engines to access data in Amazon S3 locations with full table access*. From the CLI, `put-data-lake-settings` replaces the entire settings object, so retrieve the current settings first:
```bash
aws lakeformation get-data-lake-settings --query DataLakeSettings > settings.json
# add "AllowFullTableExternalDataAccess": true to settings.json
aws lakeformation put-data-lake-settings --data-lake-settings file://settings.json
```
2. **Register the S3 location with a custom IAM role.** Credential vending doesn't work for locations registered with the service-linked role. Register (or re-register) the location with a role that Lake Formation can assume and that has S3 access to the bucket, plus `kms:Decrypt` if the bucket uses SSE-KMS:
```bash
aws lakeformation register-resource \
--resource-arn arn:aws:s3::: \
--role-arn arn:aws:iam:::role/
```
3. **Create the IAM principal for MotherDuck.** Its policy contains only the Glue catalog read actions listed above and `lakeformation:GetDataAccess`. Leave S3 permissions out — Lake Formation vends data access per query:
```json
{
"Version": "2012-10-17",
"Statement": [
{
"Effect": "Allow",
"Action": [
"glue:GetCatalog",
"glue:GetDatabase",
"glue:GetDatabases",
"glue:GetTable",
"glue:GetTables"
],
"Resource": "*"
},
{
"Effect": "Allow",
"Action": ["lakeformation:GetDataAccess"],
"Resource": "*"
}
]
}
```
4. **Remove the `IAMAllowedPrincipals` defaults.** By default, Lake Formation grants `IAMAllowedPrincipals` — every IAM principal in the account — on new databases and tables. That default satisfies Lake Formation for any principal that can request vended credentials, bypassing your per-principal grants. In the console under **Data Catalog settings**, clear the *Use only IAM access control* defaults for new databases and tables, and revoke existing `IAMAllowedPrincipals` grants on the databases and tables you serve.
#### Lake Formation permissions
In Lake Formation, grant the principal `DESCRIBE` on the Glue database and `SELECT` on the tables it should read, either directly or through [LF-tags](https://docs.aws.amazon.com/lake-formation/latest/dg/tag-based-access-control.html). These grants are managed entirely on the AWS side; MotherDuck presents the principal's identity and Lake Formation decides what it can access. Read-only grants are sufficient. With the `IAMAllowedPrincipals` defaults removed (prerequisite 4), tables the principal isn't granted don't appear in the attached catalog.
Lake Formation grants must apply to whole tables: Lake Formation can't vend credentials for grants that carry column-level permissions or row and cell filters, so queries against tables with such grants fail instead of returning unfiltered data. This [AWS constraint](https://docs.aws.amazon.com/lake-formation/latest/dg/full-table-credential-vending.html) applies to all external engines. To serve filtered data, grant access to a pre-filtered table or view instead.
#### Troubleshooting Lake Formation errors
Error reference: Lake Formation and Glue REST errors
| Error message | Cause | Fix |
| :--- | :--- | :--- |
| `Insufficient Lake Formation permissions. Verify the data lake settings for account` | Application integration isn't enabled | Enable `AllowFullTableExternalDataAccess` (prerequisite 1) |
| `Access is not allowed.` | The S3 location is registered with the service-linked role, which doesn't support credential vending | Re-register the location with a custom role (prerequisite 2) |
| `FULL SELECT or SUPER privileges required on the table.` | The principal's `SELECT` grant is missing, limited to specific columns, or has a row filter | Grant `SELECT` on the whole table with no filters (see [Lake Formation permissions](#lake-formation-permissions)) |
| `Insufficient Lake Formation permission(s): Required Describe on ` | The principal has no Lake Formation grant on that table | Grant `DESCRIBE` and `SELECT` if the principal should have access |
| `not authorized to perform: s3:GetObject` | The location isn't registered with Lake Formation, so no credentials are vended | Register the location (prerequisite 2), or for lakes without Lake Formation, grant the principal `s3:GetObject` |
Newly created IAM users, roles, and access keys can take a minute to propagate. If you get a `403 Forbidden` right after creating one, retry before changing any settings.
### Databricks
Databricks Unity Catalog provides an Iceberg REST catalog endpoint at `https:///api/2.1/unity-catalog/iceberg-rest`. You can use that endpoint to attach Unity Catalog as an Iceberg catalog in MotherDuck. This can include Unity Catalog Iceberg tables and Delta tables that are configured for Iceberg reads. Databricks only supports credential vending for tables stored on external locations.
```sql
CREATE SECRET databricks_uc_secret IN MOTHERDUCK (
TYPE ICEBERG,
TOKEN ''
);
CREATE DATABASE databricks_uc (
TYPE ICEBERG,
"secret" databricks_uc_secret,
endpoint 'https:///api/2.1/unity-catalog/iceberg-rest',
warehouse '',
default_schema '',
read_only false
);
```
Delta tables exposed through Unity Catalog's Iceberg REST catalog have two limitations:
- They are read-only.
- Iceberg reads need to be enabled, which means using `IcebergCompatV2` and disabling deletion vectors.
```sql
CREATE OR REPLACE TABLE () TBLPROPERTIES
(
'delta.columnMapping.mode' = 'name',
'delta.enableDeletionVectors' = 'false',
'delta.enableIcebergCompatV2' = 'true',
'delta.universalFormat.enabledFormats' = 'iceberg'
);
```
For a complete overview of setup requirements see the [Databricks Iceberg client access documentation](https://docs.databricks.com/aws/en/external-access/iceberg).
#### Troubleshooting Databricks Iceberg reads