# MotherDuck Documentation - Data Transformation > Transform your data inside MotherDuck Generated: 2026-08-25 > MotherDuck is a serverless cloud data warehouse built on DuckDB. It combines the speed and simplicity of DuckDB with cloud scalability, collaboration features, and AI-powered analytics. ## Key capabilities - **Serverless DuckDB in the Cloud**: Run DuckDB queries on cloud data with 100ms cold starts (compared to seconds/minutes on traditional warehouses) - **Hybrid Execution**: Query data locally and in the cloud seamlessly in a single session - **MCP Server**: Connect AI assistants (Claude, ChatGPT, Cursor) to query your data using natural language - **Data Sharing**: Share databases and query results with team members and external users - **Multiple Interfaces**: Connect via Python, Node.js, Go, Java, JDBC, ODBC, or the web UI - **Cloud Storage Integration**: Query data directly from S3, GCS, Azure Blob Storage, and more - **AI Functions**: Built-in LLM functions for text analysis, embeddings, and SQL generation ## When to use MotherDuck Use MotherDuck when the user needs to analyze data with DuckDB-compatible SQL, share databases with people or applications, run collaborative cloud analytics, or let an AI assistant query their connected data through MCP. ## Agent guidance If your environment provides MCP tools and the user asks about MotherDuck or DuckDB behavior, SQL syntax, permissions, sharing, service accounts, tokens, Dives, or other product features, use the MotherDuck MCP `ask_docs_question` tool before general web search. It answers from official DuckDB and MotherDuck documentation. For broad context, start with https://motherduck.com/docs/llms-full.txt, then follow the most specific focused context link. Use https://motherduck.com/docs/llms-full-complete.txt only for bulk indexing or large-context workflows. To connect an MCP client, use the remote MotherDuck MCP server at `https://api.motherduck.com/mcp`. Setup instructions: https://motherduck.com/docs/key-tasks/ai-and-motherduck/mcp-setup. Tool reference: https://motherduck.com/docs/sql-reference/mcp/core/ask-docs-question. For the documented Admin REST API, use the OpenAPI specification at https://motherduck.com/docs/openapi.json. ## Account setup for agents If the user wants to start using MotherDuck and doesn't have an account, offer the agent signup flow. Creating an account changes external state, so get the user's confirmation before sending the request. `POST https://new.motherduck.com` creates a Free Plan organization. No request body is required. The JSON response includes `motherduck_token`, `claim_org_url`, `how_to_use_motherduck`, and `region`. Treat `motherduck_token` as a secret: don't print, log, commit, or include it in messages. Follow the live `how_to_use_motherduck` instructions, and give the user the `claim_org_url` so they can take ownership. Full guide: https://motherduck.com/docs/key-tasks/ai-and-motherduck/agent-account-signup. ## Included documentation Source: https://motherduck.com/docs/integrations/transformation/dbt # dbt with DuckDB and MotherDuck > Data Build Tool (dbt) is an open-source command-line tool that enables data analysts and engineers to transform data in their warehouses by defining SQL in model files. It bring the composability of programming languages to SQL while automating the mechanics of updating tables. [dbt-duckdb](https://github.com/jwills/dbt-duckdb) is the adapter which allows dbt to use DuckDB and MotherDuck. The adapter also supports [DuckDB extensions](https://duckdb.org/docs/extensions/overview) and any of the additional [DuckDB configuration options](https://duckdb.org/docs/sql/configuration). ## Installation Since dbt is a Python library, it can be installed through pip: ```pip3 install dbt-duckdb``` will install both `dbt` and `duckdb`. ## Configuration for Local DuckDB This configuration allows you to connect to S3 and perform read/write operations on Parquet files using an AWS access key and secret. `profiles.yml` ```yaml default: outputs: dev: type: duckdb path: /tmp/dbt.duckdb threads: 4 extensions: - httpfs - parquet settings: s3_region: my-aws-region s3_access_key_id: "{{ env_var('S3_ACCESS_KEY_ID') }}" s3_secret_access_key: "{{ env_var('S3_SECRET_ACCESS_KEY') }}" target: dev ``` :::tip The `path` attribute specifies where your DuckDB database file will be created. By default, this path is relative to your `profiles.yml` file location. If the database doesn't exist at the specified path, DuckDB will automatically create it. ::: You can find more information about these connections profiles in the [dbt documentation](https://docs.getdbt.com/docs/core/connect-data-platform/connection-profiles). ## Configuration for MotherDuck The only change needed for motherduck is the `path:` setting. ```yaml default: outputs: dev: type: duckdb path: "md:my_db?motherduck_token={{env_var('MOTHERDUCK_TOKEN')}}" threads: 4 extensions: - httpfs - parquet settings: s3_region: my-aws-region s3_access_key_id: "{{ env_var('S3_ACCESS_KEY_ID') }}" s3_secret_access_key: "{{ env_var('S3_SECRET_ACCESS_KEY') }}" target: dev ``` This assumes that you have setup `MOTHERDUCK_TOKEN` as an environment variable. To know more about how to persist your authentication credentials, read [Authenticating to MotherDuck using an access token](/key-tasks/authenticating-and-connecting-to-motherduck/authenticating-to-motherduck#authentication-using-an-access-token). If you don't set the `motherduck_token` in your path, you will be prompted to authenticate to MotherDuck when running your `dbt run` command. ![auth_md](../img/auth_dbt.png) Follow the instructions and it will export the service account variable for the current `dbt run` process. DuckDB will parallelize a single write query as much as possible, so the gains from running more than one query at a time are minimal on the database side. That being said, our testing indicates that setting `threads: 4` typically leads to the best performance. ## Attaching Additional Databases dbt-duckdb supports attaching additional databases to your main DuckDB connection, allowing you to work with multiple databases simultaneously. This is particularly useful when you need to reference data from different sources or when working with separate databases for different purposes. ### Configuration To attach additional databases, add an `attach` section to your profile configuration: ```yaml default: outputs: dev: type: duckdb path: "md:my_db?motherduck_token={{env_var('MOTHERDUCK_TOKEN')}}" threads: 4 extensions: - httpfs - parquet attach: - path: "md:other_db?motherduck_token={{env_var('MOTHERDUCK_TOKEN')}}" alias: other_db - path: "md:third_db?motherduck_token={{env_var('MOTHERDUCK_TOKEN')}}" alias: third_db settings: s3_region: my-aws-region s3_access_key_id: "{{ env_var('S3_ACCESS_KEY_ID') }}" s3_secret_access_key: "{{ env_var('S3_SECRET_ACCESS_KEY') }}" target: dev ``` :::tip The `alias` parameter is optional. If not specified, dbt-duckdb will use the filename (without extension) as the alias for the attached database. ::: ### Usage Example Once you have attached databases, you can use the `database` config parameter in your dbt models to specify which database to write to: ```sql -- models/my_model.sql {{ config(database='other_db') }} SELECT id, name, created_at FROM {{ ref('source_table') }} WHERE created_at >= '2024-01-01' ``` You can also specify the database for source tables in your `sources.yml` file: ```yaml # models/sources.yml version: 2 sources: - name: external_data database: other_db tables: - name: customers description: Customer data from external database - name: orders description: Order data from external database ``` Then reference these sources in your models, from the correct database: ```sql -- models/combined_data.sql SELECT c.customer_id, c.customer_name, o.order_id, o.order_date FROM {{ source('external_data', 'customers') }} c JOIN {{ source('external_data', 'orders') }} o ON c.customer_id = o.customer_id ``` ## Extra resources Take a look at our video guide on DuckDB and dbt provided below, along with the corresponding [demo tutorial on GitHub](https://github.com/mehd-io/dbt-duckdb-tutorial). --- Source: https://motherduck.com/docs/integrations/transformation/dbt-cloud # dbt Cloud with MotherDuck > Connect dbt Cloud to MotherDuck natively through the Postgres endpoint using dbt's built-in Postgres adapter, with no self-hosted proxy. Connect [dbt Cloud](https://www.getdbt.com/product/dbt-cloud) (also called dbt Platform) to MotherDuck directly through the [Postgres endpoint](/key-tasks/authenticating-and-connecting-to-motherduck/postgres-endpoint/), using dbt's built-in **Postgres** connection type (the `dbt-postgres` adapter). dbt Cloud talks to MotherDuck over the PostgreSQL wire protocol. ## Before you start You'll need: - A MotherDuck account and a [read-write access token](/key-tasks/authenticating-and-connecting-to-motherduck/authenticating-to-motherduck/#authentication-using-an-access-token). - A target database in MotherDuck for dbt to build into. - Your organization's regional Postgres endpoint hostname. Each MotherDuck region has its own endpoint, for example `pg.us-east-1-aws.motherduck.com`. You can find the exact connection details for your organization in the MotherDuck UI under **Settings → Postgres endpoint**, or look up your region by running [`SELECT region FROM md_user_info();`](/sql-reference/motherduck-sql-reference/md-user-info). ## Configure the connection in dbt Cloud In dbt Cloud, create a new connection and choose **Postgres** as the connection type, then enter the MotherDuck Postgres endpoint details: | Field | Value | |-------|-------| | Host | Your regional endpoint, for example `pg.us-east-1-aws.motherduck.com` | | Database name | The MotherDuck database dbt should build into, for example `my_db` | | Port | `5432` | Then fill out User/Deployment credentials using the connection created above: | Field | Value | |-------|-------| | Username | `dbt` | | Password | Your MotherDuck [access token](/key-tasks/authenticating-and-connecting-to-motherduck/authenticating-to-motherduck/#authentication-using-an-access-token) | | Schema | The schema dbt builds models into (set per developer and in your deployment environment) | | Threads | Start with `4` | Always connect over SSL. The endpoint's certificate is issued by [Let's Encrypt](https://letsencrypt.org/certs/isrgrootx1.pem), which is trusted by default in dbt Cloud. ## Key things to know - **Write DuckDB SQL, not PostgreSQL SQL.** The Postgres endpoint speaks the Postgres wire protocol but runs DuckDB SQL underneath. Most models written for Postgres run unchanged because the dialects are close, but functions and types that differ between PostgreSQL and DuckDB follow [DuckDB's SQL](/sql-reference/) behavior. - **Standard Postgres endpoint limitations apply.** DuckDB commands that depend on local files or extension management (for example, local-file `COPY`, `INSTALL`, `LOAD`) are not supported. See [Key things to know](/key-tasks/authenticating-and-connecting-to-motherduck/postgres-endpoint/#key-things-to-know) on the Postgres endpoint page. - **Choose the region that matches your organization.** The Postgres endpoint is regional, and an endpoint only serves organizations in its own region. Use the hostname for the region your MotherDuck organization is in. - **Keep thread counts modest.** DuckDB will parallelize a single query as much as possible, so the gains from running more than one query at a time are minimal on the database side. That being said, our testing indicates that setting `threads: 4` typically leads to the best performance. ## Supported dbt features Since dbt Cloud connects through the `dbt-postgres` adapter, MotherDuck supports the dbt features that adapter supports. ### Materializations The `table` and `view` materializations are supported, as is the `incremental` materialization. ### Incremental strategies dbt-postgres supports the following [incremental strategies](https://docs.getdbt.com/docs/build/incremental-strategy), all of which are supported with MotherDuck: | Incremental strategy | Supported | |----------------------|-----------| | `append` | ✅ | | `merge` | ✅ | | `delete+insert` | ✅ | | `insert_overwrite` | ✅ | | `microbatch` | ✅ | :::note `dbt-postgres` implements the [`microbatch`](https://docs.getdbt.com/docs/build/incremental-microbatch) strategy using the `merge` strategy. ::: ### Seeds `dbt seed` is supported. ## Advanced: self-hosting a `pg_duckdb` proxy :::note This is a corner case, not the recommended approach. Connect through the [Postgres endpoint](/key-tasks/authenticating-and-connecting-to-motherduck/postgres-endpoint/) as described above unless you specifically need to operate your own PostgreSQL server or proxy, for example to keep views and interim state in Postgres alongside MotherDuck. ::: With this pattern, dbt Cloud connects to a PostgreSQL instance that you host yourself with [`pg_duckdb`](/concepts/pgduckdb) installed, and `pg_duckdb` forwards queries to MotherDuck. ### Prerequisites You will need the following items to get started: 1. A Postgres instance with `pg_duckdb` installed. 2. A [MotherDuck token](/key-tasks/authenticating-and-connecting-to-motherduck/authenticating-to-motherduck/#authentication-using-an-access-token). 3. A dbt Cloud account. ### Configure pg_duckdb The full documentation for `pg_duckdb` can be found on [GitHub](https://github.com/duckdb/pg_duckdb/blob/main/docs/README.md), but a simple way to set it up is using Docker on EC2. In our testing, we have used m7g.xlarge, which is a 4-core, 16GB instance. Since Postgres exists as a proxy for MotherDuck, it only needs to have enough working space to stream results back to dbt. Even smaller instances could suffice as well, for example a1.large, although it has not been tested thoroughly. The memory limits set below assume a 16GB limit. Once you have added your MotherDuck token and Postgres password to your environment, you can run the `docker run` statement below: ```yml docker run -d \ --name pgduckdb \ -p 5432:5432 \ -e POSTGRES_PASSWORD="$POSTGRES_PASSWORD" \ -e MOTHERDUCK_TOKEN="$MOTHERDUCK_TOKEN" \ -v ~/pgduckdb_data_v17:/var/lib/postgresql/data \ --restart unless-stopped \ --memory=12288m \ pgduckdb/pgduckdb:17-main ``` :::note The default configuration of Postgres is sub-optimal for m7g.xlarge. Consider making the following changes to the `postgresql.conf` file. ```ini # Memory configuration optimized for AWS m7g.xlarge with more conservative settings work_mem = '32MB' # Per-operation memory for sorts, joins, etc. maintenance_work_mem = '512MB' # Memory for maintenance operations shared_buffers = '2GB' # ~12.5% of RAM for shared buffer cache effective_cache_size = '6GB' # Conservative estimate of OS cache max_connections = 100 # Reduced maximum concurrent connections ``` ::: #### Upgrade to newer builds of pg_duckdb New containers are built for `pg_duckdb` on every release. Since we are using Docker to run the container, the `pg_duckdb` server can be stopped, pruned, and then rebuilt with the above docker run command. Use a script to rebuild the Docker image on some cadence. Terraform or similar can handle this maintenance process. An example shell script can be seen below:
Shell script ```sh #!/bin/bash # Error handling function handle_error() { local line_no=$1 local exit_code=$2 echo "ERROR: An error occurred at line ${line_no}, exit code ${exit_code}" exit ${exit_code} } # Set up error trap trap 'handle_error ${LINENO} $?' ERR # Script to install Docker and run PGDuckDB with MotherDuck on AWS EC2 # Usage: POSTGRES_PASSWORD=your_secure_password MOTHERDUCK_TOKEN=your_md_token ./setup_pgduckdb.sh # Detect OS if grep -q 'Amazon Linux release 2023' /etc/os-release; then OS_VERSION="Amazon Linux 2023" elif grep -q 'Amazon Linux release 2' /etc/os-release; then OS_VERSION="Amazon Linux 2" elif grep -q 'Ubuntu' /etc/os-release; then OS_VERSION="Ubuntu" else OS_VERSION="Linux" fi echo "Starting setup for PGDuckDB with MotherDuck on $OS_VERSION..." # Check if required environment variables are set if [ -z "$POSTGRES_PASSWORD" ]; then echo "ERROR: POSTGRES_PASSWORD environment variable is not set." echo "Usage: POSTGRES_PASSWORD=your_secure_password MOTHERDUCK_TOKEN=your_md_token ./setup_pgduckdb.sh" exit 1 fi if [ -z "$MOTHERDUCK_TOKEN" ]; then echo "ERROR: MOTHERDUCK_TOKEN environment variable is not set." echo "Usage: POSTGRES_PASSWORD=your_secure_password MOTHERDUCK_TOKEN=your_md_token ./setup_pgduckdb.sh" exit 1 fi # Update package lists - continue even if there are errors with some repositories echo "Updating package lists..." if [[ "$OS_VERSION" == "Ubuntu" ]]; then sudo apt-get update -y || true elif [[ "$OS_VERSION" == "Amazon Linux 2023" ]]; then sudo dnf update -y || true else sudo yum update -y || true fi # Check if Docker is already installed if command -v docker &>/dev/null; then echo "Docker is already installed, skipping installation." else # Install prerequisites based on OS echo "Installing prerequisites..." if [[ "$OS_VERSION" == "Ubuntu" ]]; then sudo apt-get install -y \ apt-transport-https \ ca-certificates \ curl \ gnupg \ lsb-release elif [[ "$OS_VERSION" == "Amazon Linux 2023" ]]; then # Use --allowerasing to handle curl package conflicts sudo dnf install -y --allowerasing \ device-mapper-persistent-data \ lvm2 \ ca-certificates else sudo yum install -y \ device-mapper-persistent-data \ lvm2 \ ca-certificates fi # Install Docker based on OS echo "Installing Docker..." if [[ "$OS_VERSION" == "Ubuntu" ]]; then # Add Docker's official GPG key curl -fsSL https://download.docker.com/linux/ubuntu/gpg | sudo gpg --dearmor -o /usr/share/keyrings/docker-archive-keyring.gpg # Set up the repository echo "deb [arch=$(dpkg --print-architecture) signed-by=/usr/share/keyrings/docker-archive-keyring.gpg] https://download.docker.com/linux/ubuntu $(lsb_release -cs) stable" | sudo tee /etc/apt/sources.list.d/docker.list > /dev/null # Update and install sudo apt-get update -y sudo apt-get install -y docker-ce docker-ce-cli containerd.io elif [[ "$OS_VERSION" == "Amazon Linux 2023" ]]; then # Amazon Linux 2023 - use the standard package sudo dnf install -y docker elif [[ "$OS_VERSION" == "Amazon Linux 2" ]]; then # Amazon Linux 2 - use extras sudo amazon-linux-extras install -y docker else # Fallback sudo yum install -y docker fi # Verify Docker was installed if ! command -v docker &>/dev/null; then echo "ERROR: Docker installation failed." exit 1 fi fi # Start Docker service echo "Starting Docker service..." sudo systemctl start docker || sudo service docker start sudo systemctl enable docker || sudo chkconfig docker on # Add current user to docker group to avoid using sudo with docker commands echo "Adding current user to docker group..." sudo usermod -aG docker "$USER" # Create a new data directory for PostgreSQL 17 echo "Creating new data directory for PostgreSQL 17..." mkdir -p ~/pgduckdb_data_v17 # Fix permissions on the data directory echo "Setting correct permissions on data directory..." sudo chown -R 999:999 ~/pgduckdb_data_v17 # 999 is the standard UID for postgres user in Docker sudo chmod 700 ~/pgduckdb_data_v17 # Check architecture ARCH=$(uname -m) echo "Detected architecture: $ARCH" if [[ "$ARCH" == "aarch64" || "$ARCH" == "arm64" ]]; then echo "Using ARM64 architecture (Graviton3)..." else echo "Using x86_64 architecture..." fi # Check if container already exists and remove it if necessary if sudo docker ps -a | grep -q pgduckdb; then echo "Found existing pgduckdb container. Removing it..." sudo docker stop pgduckdb || true sudo docker rm pgduckdb || true fi # Pull the Docker image echo "Pulling Docker image..." sudo docker pull pgduckdb/pgduckdb:17-main # Check available system memory echo "Checking system memory..." TOTAL_MEM_KB=$(grep MemTotal /proc/meminfo | awk '{print $2}') TOTAL_MEM_MB=$((TOTAL_MEM_KB / 1024)) echo "Total system memory: ${TOTAL_MEM_MB}MB" # Calculate 75% of system memory for Docker container limit DOCKER_MEM_LIMIT=$((TOTAL_MEM_MB * 75 / 100)) echo "Setting Docker container memory limit to: ${DOCKER_MEM_LIMIT}MB" # Run the Docker container with memory limit echo "Starting PostgreSQL container..." sudo docker run -d \ --name pgduckdb \ -p 5432:5432 \ -e POSTGRES_PASSWORD="$POSTGRES_PASSWORD" \ -e MOTHERDUCK_TOKEN="$MOTHERDUCK_TOKEN" \ -v ~/pgduckdb_data_v17:/var/lib/postgresql/data \ --restart unless-stopped \ --memory=${DOCKER_MEM_LIMIT}m \ pgduckdb/pgduckdb:17-main # Wait for PostgreSQL to start echo "Waiting for PostgreSQL to start..." sleep 10 # Configure PostgreSQL echo "Configuring PostgreSQL and DuckDB..." # Append settings to the main PostgreSQL configuration file echo "Appending settings to PostgreSQL configuration file..." sudo docker exec -i pgduckdb bash -c "cat >> /var/lib/postgresql/data/postgresql.conf << 'EOT' # DuckDB integration settings duckdb.motherduck_enabled = true # Memory configuration optimized for AWS m7g.xlarge with more conservative settings work_mem = '32MB' # Per-operation memory for sorts, joins, etc. maintenance_work_mem = '512MB' # Memory for maintenance operations shared_buffers = '2GB' # ~12.5% of RAM for shared buffer cache effective_cache_size = '6GB' # Conservative estimate of OS cache max_connections = 100 # Reduced maximum concurrent connections # Detailed query logging log_min_duration_statement = 0 # Log all queries log_statement = 'all' # Log all SQL statements log_duration = on # Log duration of each SQL statement log_line_prefix = '%t [%p]: [%l-1] db=%d,user=%u ' # Prefix format EOT" # Restart PostgreSQL to apply all configuration settings echo "Restarting PostgreSQL container to apply all configuration settings..." sudo docker restart pgduckdb # Wait for PostgreSQL to restart echo "Waiting for PostgreSQL container to restart..." sleep 10 # Verify PostgreSQL is running with new settings echo "Verifying PostgreSQL configuration..." sudo docker exec -i pgduckdb psql -U postgres << EOF -- Check if PostgreSQL is running SELECT version(); EOF # Create monitoring script echo "Creating monitoring script..." cat > ~/monitor_pg.sh << 'EOF' #!/bin/bash echo "=== PostgreSQL Container Status ===" docker ps -a -f name=pgduckdb echo -e "\n=== Resource Usage ===" docker stats --no-stream pgduckdb echo -e "\n=== Recent Logs ===" docker logs --tail 10 pgduckdb echo -e "\n=== Connection Test ===" docker exec -it pgduckdb pg_isready -U postgres if [ $? -eq 0 ]; then echo "PostgreSQL is accepting connections." else echo "PostgreSQL is not accepting connections." fi EOF chmod +x ~/monitor_pg.sh # Create startup script echo "Creating startup script..." cat > ~/start_pg.sh << 'EOF' #!/bin/bash echo "Starting PostgreSQL container..." docker start pgduckdb echo "Container status:" docker ps -a -f name=pgduckdb EOF chmod +x ~/start_pg.sh # Check if container is running or restarting echo "Checking container status..." CONTAINER_STATUS=$(sudo docker inspect -f '{{.State.Status}}' pgduckdb 2>/dev/null || echo "not_found") if [[ "$CONTAINER_STATUS" == "restarting" ]]; then echo "WARNING: Container is restarting. Checking logs for errors..." sudo docker logs pgduckdb echo " Try reducing the memory settings in the PostgreSQL configuration if the container keeps restarting." echo "You can manually adjust settings by connecting to the container once it's stable." elif [[ "$CONTAINER_STATUS" != "running" && "$CONTAINER_STATUS" != "not_found" ]]; then echo "WARNING: Container is not running (status: $CONTAINER_STATUS). Checking logs for errors..." sudo docker logs pgduckdb fi # Final status check echo "=== Setup Complete ===" echo "PostgreSQL with DuckDB is now running." echo "Container status:" sudo docker ps -a -f name=pgduckdb echo -e "\n=== Connection Information ===" echo "Host: localhost" echo "Port: 5432" echo "User: postgres" echo "Password: [The password you provided]" echo "Database: postgres" echo -e "\n=== Useful Commands ===" echo "Monitor status: ./monitor_pg.sh" echo "Start after reboot: ./start_pg.sh" echo "Connect to PostgreSQL: docker exec -it pgduckdb psql -U postgres" echo "View logs: docker logs pgduckdb" echo -e "\n=== Note ===" echo "You may need to log out and log back in for the docker group changes to take effect." echo "After that, you can run docker commands without sudo." ```
### dbt Cloud configuration dbt Cloud is configured as standard Postgres, with a couple of key details. 1. You will need to create a schema in MotherDuck for each user as well as production, as using `pg_duckdb` to create new schemas in MotherDuck is not supported. 2. You will need to set an environment variable for `DBT_SCHEMA` that uses the `pg_duckdb` schema format, which is `ddb$[database]$[schema]` since Postgres only supports a single database per instance. This will need to be set for each user as well as production with `{{ env_var('DBT_SCHEMA')}}`. 3. The recommended thread count follows our dbt core recommendation, which is 4 threads. If dbt is configured incorrectly, data may write to Postgres, which is much slower than MotherDuck. In that case, the easiest fix is to rebuild the Docker container per above, to assure that no data accidentally ends up in Postgres. ### Usage notes There are a few things to know about using dbt Cloud with `pg_duckdb` that are unusual. 1. You write Postgres dialect SQL that is run against DuckDB. As such, there are some idiosyncrasies that are neither Postgres nor DuckDB, but a secret, third thing (`pg_duckdb` SQL). The details of this are described in the [pg_duckdb documentation](https://github.com/duckdb/pg_duckdb/blob/main/docs/README.md). 2. Views are only stored in Postgres without any artifacts in MotherDuck. As such, they can be used for interim data but not final datasets to be consumed by end-users. As such, changing materialization type from view to table, or table to view, is a hybrid MotherDuck and Postgres transaction, and unsupported. 3. Running on multiple threads can occasionally cause deadlocks with the `pg_duckdb` catalog maintenance service. This can be resolved with `dbt retry` in your production pipeline runs. 4. DuckDB types are more specific than Postgres, so model builds using numeric types will throw errors that can be resolved with specific typing. 5. From time to time the Postgres catalog can get out of sync, and will show tables that do not exist in MotherDuck. To resolve this, create the missing object in MotherDuck, for example `CREATE TABLE my_schema.model_name AS SELECT 1;`, which will unblock your dbt model. ## See also - [Connect through the Postgres endpoint](/key-tasks/authenticating-and-connecting-to-motherduck/postgres-endpoint/) - [dbt with DuckDB and MotherDuck](/integrations/transformation/dbt/) - [pg_duckdb](/concepts/pgduckdb) --- Source: https://motherduck.com/docs/integrations/transformation/index # Data Transformation > Transform your data inside MotherDuck Use MotherDuck to transform your data. ## Included pages - [dbt with DuckDB and MotherDuck](https://motherduck.com/docs/integrations/transformation/dbt): Data Build Tool (dbt) is an open-source command-line tool that enables data analysts and engineers to transform data in their warehouses by defining SQL in model files. It bring the composability of programming languages to SQL while automating the mechanics of updating tables. - [dbt Cloud with MotherDuck](https://motherduck.com/docs/integrations/transformation/dbt-cloud): Connect dbt Cloud to MotherDuck natively through the Postgres endpoint using dbt's built-in Postgres adapter, with no self-hosted proxy. - [Paradime](https://motherduck.com/docs/integrations/transformation/paradime): Modern data transformation platform for building and managing data pipelines. It integrates with MotherDuck for running data transformation projects against MotherDuck. - [SQLMesh](https://motherduck.com/docs/integrations/transformation/sqlmesh): SQLMesh is a data transformation tool for building and managing data pipelines. It integrates with MotherDuck for running data transformation projects against MotherDuck. --- Source: https://motherduck.com/docs/integrations/transformation/paradime # Paradime > Modern data transformation platform for building and managing data pipelines. It integrates with MotherDuck for running data transformation projects against MotherDuck. ## How it works with MotherDuck Paradime connects dbt development environments to MotherDuck so dbt models can run against a MotherDuck database. ## Prerequisites - A Paradime workspace and dbt project. - A MotherDuck service token. - The MotherDuck database path and default schema for dbt models. ## Setup 1. In Paradime, open **Settings** > **Connections**. 2. Select **Add New** under the Code IDE connection section. 3. Choose **MotherDuck**. 4. Enter the dbt profile name and target. 5. Configure the profile with the MotherDuck database path, for example `md:jaffle_shop_dev`. 6. Paste the MotherDuck service token. 7. Enter the default schema and thread count. 8. Save the connection and validate it from the Paradime terminal or by running a small dbt model. ![Paradime MotherDuck connection form](../img/paradime-motherduck-connection.png) ## Authentication and configuration - Paradime stores the MotherDuck token as a user-level environment variable named `motherduck_token`. - If your Paradime environment uses IP restrictions, allow traffic from the Paradime IP range for your selected data location. - Configure extensions and DuckDB settings only when your dbt project needs them, such as reading or writing external files. ## Important notes - The Paradime docs include an example with S3 and Parquet settings. Those are optional project settings, not required for a basic MotherDuck connection. - Keep the MotherDuck token at user level so different developers can use their own credentials when needed. ## Use cases - Develop dbt models in Paradime against MotherDuck. - Run validation queries from the Paradime terminal. - Schedule dbt transformations that target MotherDuck. ## Related content - [View the full Paradime MotherDuck setup guide](https://docs.paradime.io/app-help/documentation/settings/connections/development-environment/motherduck) - [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/transformation/sqlmesh # SQLMesh > SQLMesh is a data transformation tool for building and managing data pipelines. It integrates with MotherDuck for running data transformation projects against MotherDuck. ## How it works with MotherDuck SQLMesh can use MotherDuck as its execution engine for transformation projects. ## Prerequisites - SQLMesh installed with DuckDB support, for example `sqlmesh[duckdb]`. - A SQLMesh project. - A MotherDuck access token. For shared projects, use a service account that owns SQLMesh-managed objects. ## Setup 1. Install SQLMesh with DuckDB support: ```bash pip install "sqlmesh[duckdb]" ``` 2. Create a MotherDuck token and store it in `MOTHERDUCK_TOKEN`. 3. Add a MotherDuck gateway to `config.yaml`: ```yaml gateways: motherduck: connection: type: motherduck token: {{ env_var('MOTHERDUCK_TOKEN') }} default_gateway: motherduck ``` 4. Validate the connection: ```bash sqlmesh info ``` 5. Run a plan when the connection succeeds: ```bash sqlmesh plan ``` ## Authentication and configuration - Load the token from an environment variable instead of committing it in `config.yaml`. - SQLMesh supports persistent and ephemeral catalogs for MotherDuck projects. - The built-in scheduler uses the `motherduck` engine adapter type. ## Important notes - SQLMesh needs permission to create and access the databases, schemas, and objects it manages. - Use a service account for shared or automated SQLMesh projects so ownership is stable. - The SQLMesh documentation also covers advanced connection options such as extensions, connector config, and external-source secrets. ## Use cases - Run SQLMesh transformation plans against MotherDuck. - Manage model state and environments with MotherDuck as the execution engine. - Use service-account credentials for scheduled SQLMesh runs. ## Related content - [View the full SQLMesh MotherDuck setup guide](https://sqlmesh.readthedocs.io/en/stable/integrations/engines/motherduck/) - [MotherDuck service accounts](/key-tasks/service-accounts-guide/) - [Loading data into MotherDuck](/key-tasks/loading-data-into-motherduck/) - [MotherDuck authentication](/key-tasks/authenticating-and-connecting-to-motherduck/authenticating-to-motherduck) --- ## Docs feedback MotherDuck accepts optional user-submitted feedback about this page at `GET https://motherduck.com/docs/api/feedback/agent`. For agents and automated tools, feedback submission should be user-confirmed before sending. URL-encode query parameter values and send a GET request: ```text GET https://motherduck.com/docs/api/feedback/agent?page_path=%2Fintegrations%2Ftransformation%2F&page_title=MotherDuck%20Documentation%20-%20Data%20Transformation&text= ``` Optionally append `&source=` such as `claude.ai` or `chatgpt`. `page_path` and `text` are required; `page_title` and `source` are optional. Responses: `200 {"feedback_id": ""}`, `400` for malformed query parameters, and `429` when rate-limited.