# MotherDuck Documentation - Data Warehousing > Data Warehousing How-to guides 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. ## Child contexts - [Orchestration full context](https://motherduck.com/docs/key-tasks/data-warehousing/orchestration/llms-full.txt) (2 pages; 21,726 bytes; ~5,432 tokens). [Index](https://motherduck.com/docs/key-tasks/data-warehousing/orchestration/llms.txt). - [Replication full context](https://motherduck.com/docs/key-tasks/data-warehousing/replication/llms-full.txt) (4 pages; 29,029 bytes; ~7,257 tokens). [Index](https://motherduck.com/docs/key-tasks/data-warehousing/replication/llms.txt). ## Included documentation Source: https://motherduck.com/docs/key-tasks/data-warehousing/environment-management # Environment management > Set up development, staging, and production data environments in MotherDuck with workload isolation, read-only access to production data, and dbt. This guide shows how to set up development, staging, and production environments in MotherDuck. Data environments behave differently from application environments, so it starts with how they relate, then walks through the setup: databases split by responsibility, a service account and Duckling per workload, read-only access to production data, and dbt targets for promotion and rollback. ## How data environments work In application software, each environment is a self-contained copy: development code runs against development data, staging code against staging data, and so on. Data stacks don't line up that way. Environments are staggered across the stack. - **Ingestion** runs real development, staging, and production environments. Each one tests the extract-and-load code against its own small or sampled dataset. - **Transformation** (dbt and similar tools) reads production data in place in every phase. Its development and CI runs read the same production tables that production does, and write to isolated outputs. There's no separate "transformation staging data". - **Consumption** (analysts, dashboards, and data apps) reads the production models the same way. The reason is correctness. Your models and dashboards have to hold up against real production data and all of its edge cases. A clean, synthetic dataset hides the cases that break in production, so testing transformations against production data is the goal, not a compromise. Diagram summary: Data environments are staggered across ingestion, transformation, and consumption. - Ingestion code moves through development, staging, and production environments. - Transformation development and CI read production data and write isolated outputs. - Consumption workloads read production models so dashboards and data apps match production behavior. :::note Regulated or sensitive data is the exception. When developers can't read production data directly, create a sanitized or masked copy of the production source and grant access to that copy instead. The rest of this guide still applies, with the sanitized database in place of raw production data. ::: ## Isolate by workload, not only by stage MotherDuck gives each user and service account its own [Duckling](/concepts/hypertenancy/) for compute. That's finer-grained than one shared warehouse per stage: a developer iterating on models, a CI job validating a pull request, and a production dashboard each run on separate compute, so none of them slows the others down. This is what makes reading production data in every phase practical, because a heavy development query can't degrade production serving. For sizing, read scaling, and routing, see [workload scaling patterns](/concepts/scaling-patterns/). ## Separate databases by responsibility MotherDuck grants access at the database level, so split your data by responsibility into separate databases: | Database | Holds | Access | |---|---|---| | `raw` | Source data landed by ingestion | Read-only to transformation | | `transform` | Models built by transformation | Write for the transformation workload | | `marts` | Curated tables for consumers | Read-only to analysts, dashboards, and apps | ## Give each workload its own service account and token Create a [service account](/key-tasks/service-accounts-guide/create-and-configure-service-accounts/) for each workload not tied to a person, and give each one its own token. Every service account gets its own Duckling, secrets, and optional read scaling pool, so compute and credentials stay isolated. | Secret | Token owner | Used by | |---|---|---| | `MOTHERDUCK_TOKEN_DEV` | Developer or `transform_dev` service account | Local development | | `MOTHERDUCK_TOKEN_CI` | `transform_ci` service account | Pull request and merge jobs | | `MOTHERDUCK_TOKEN_PROD` | `transform_prod` service account | Scheduled production builds and serving | Store each token in your secret manager or CI/CD environment. Keep the production token out of local `.env` files and lower-environment CI jobs. ## Grant read-only access to production data Transformation reads production raw data in place. Publish the production `raw` database as a read-only [share](/sql-reference/motherduck-sql-reference/create-share/) and grant it to the accounts that build models. Run this as the account that owns `raw`: ```sql CREATE OR REPLACE SHARE raw_prod FROM raw ( ACCESS RESTRICTED, UPDATE AUTOMATIC ); GRANT READ ON SHARE raw_prod TO transform_dev, transform_ci, transform_prod; ``` `UPDATE AUTOMATIC` keeps the share in sync, so every phase reads the latest production data. Each transformation account attaches the share once. Because MotherDuck saves attachments to your workspace, later connections reuse it: ```sql ATTACH 'md:_share/raw/' AS raw; ``` Publish `marts` the same way and grant it to the accounts that power your dashboards and apps. ## Configure dbt targets Map dbt targets to your transformation workloads. Each target writes models to its own database and reads sources from the attached `raw` share. `profiles.yml`: ```yaml transform: target: dev outputs: dev: type: duckdb path: "md:transform_dev" schema: "{{ env_var('DBT_SCHEMA', 'dev') }}" ci: type: duckdb path: "md:transform_ci" schema: "{{ env_var('DBT_SCHEMA', 'ci') }}" prod: type: duckdb path: "md:transform" schema: prod ``` These paths use MotherDuck's default workspace [attach mode](/key-tasks/authenticating-and-connecting-to-motherduck/attach-modes/), not single mode, so each dbt run sees both its write database and the read-only `raw` share. Point your dbt sources at `raw` so every target reads the same production data: ```yaml sources: - name: raw database: raw schema: main tables: - name: orders - name: customers ``` Then set your MotherDuck token to the correct environment, for example `MOTHERDUCK_TOKEN="$MOTHERDUCK_TOKEN_CI"` and build against the intended target: ```bash dbt build --target dev dbt build --target ci dbt build --target prod ``` The `dev` and `ci` targets also set their schema from the `DBT_SCHEMA` environment variable, so it's easy to recognize what profile the data was written from. Each CI run can have its own schema: ```bash export DBT_SCHEMA=PR123 dbt build --target ci ``` So the models for PR 123 build into `transform_ci.ci_pr123`. In CI/CD, give each job only the token for the environment it runs. A pull request job receives `MOTHERDUCK_TOKEN_CI`, and a release job receives `MOTHERDUCK_TOKEN_PROD` behind your deployment system's approval controls. ## Promote and roll back Promote transformation logic by running the same dbt project against the next target, not by copying data between environments. The code moves forward while production data stays in place. Before a release, take a named [snapshot](/concepts/snapshots/) of the production database so you can roll back: ```sql CREATE SNAPSHOT transform_before_release_2026_06_01 OF transform; ``` To restore the production database to that snapshot: ```sql ALTER DATABASE transform SET SNAPSHOT TO (SNAPSHOT_NAME 'transform_before_release_2026_06_01'); ``` Named snapshots are durable recovery points available on the Business plan. When a lower environment must not read production data, share a sanitized copy instead: build a masked version of `raw` in a separate database, share that database, and grant it to the development accounts. ## Related content - [Workload scaling patterns](/concepts/scaling-patterns/) for choosing Duckling sizes, read scaling, and workload isolation - [Resource management](/concepts/resource-management/) for account, token, secret, Duckling, and database isolation boundaries - [Create and configure service accounts](/key-tasks/service-accounts-guide/create-and-configure-service-accounts/) for service account setup and token creation - [CREATE SHARE](/sql-reference/motherduck-sql-reference/create-share/) and [GRANT READ ON SHARE](/sql-reference/motherduck-sql-reference/grant-access/) for read-only cross-account access - [Database snapshots](/concepts/snapshots/) and [Data recovery](/concepts/data-recovery/) for snapshot retention and rollback --- Source: https://motherduck.com/docs/key-tasks/data-warehousing/data-warehousing # Data Warehousing How-to > Data Warehousing How-to guides ## Introduction to MotherDuck for data warehousing MotherDuck is a serverless cloud data warehouse built on [DuckDB](https://duckdb.org/docs/sql/introduction), a fast, in-process analytical database. While DuckDB provides the core analytical engine capabilities, MotherDuck adds cloud storage, sharing, and collaboration features, as well as built-in data pipeline and visualization tools. Key advantages include a hypertenancy architecture that gives every user, service, or agent its own dedicated compute, a serverless model that eliminates infrastructure management, and Dual Execution that intelligently processes queries across local and cloud resources. MotherDuck is an ideal choice for organizations seeking a modern data warehouse solution. It excels at ad-hoc analytics by providing instant compute resources for each user, serves well as a departmental data mart with its simplified sharing model, and enables powerful embedded analytics through its WASM capabilities. Different personas benefit uniquely - data analysts get an intuitive SQL interface with AI assistance, engineers can leverage familiar APIs and tools like dbt, and data scientists can seamlessly combine local and cloud data processing. ![img_duck_stack](./img/md-diagram.svg) The modern data stack with MotherDuck integrates seamlessly with popular tools across the ecosystem. As shown in the ecosystem diagram, this includes ingestion tools like [Fivetran](https://fivetran.com/docs/destinations/motherduck#motherduck) and [Airbyte](https://docs.airbyte.com/integrations/destinations/motherduck) for loading data, transformation tools like [dbt](/docs/integrations/transformation/dbt) for modeling, BI tools like [Tableau](/integrations/bi-tools/tableau/) and [PowerBI](/integrations/bi-tools/powerbi/) for visualization, and orchestration tools like [Airflow](https://airflow.apache.org/docs/) and [Dagster](https://docs.dagster.io/integrations/libraries/duckdb/using-duckdb-with-dagster) for pipeline management. This comprehensive integration enables teams to build complete data warehousing solutions while leveraging their existing tooling investments. ## MotherDuck basics: concepts to understand before you start ![Architecture](./img/the-md-dwh.png) MotherDuck's core architecture is built on a serverless foundation that eliminates infrastructure management overhead. The platform handles data storage with enterprise-grade durability and security, while optimizing performance through intelligent data organization. Each user, service account, or agent gets their own isolated compute resource called a "Duckling" that sits on top of the storage layer — a model called [hypertenancy](/concepts/hypertenancy/) — and the separation of storage and compute enables independent scaling of these resources based on workload demands. The [Dual Execution model](/concepts/architecture-and-capabilities/#dual-execution) is a unique capability that allows MotherDuck to seamlessly query both local and cloud data. The query planner intelligently determines the optimal execution path, deciding whether to process data locally, in the cloud, or using a hybrid approach. This enables efficient querying across data sources while minimizing data movement and optimizing for performance. MotherDuck follows a familiar hierarchical structure with databases containing schemas and tables. Databases serve as the primary unit of organization and access control, while schemas help logically group related tables together. This structure provides a clean way to organize data while maintaining compatibility with common [SQL patterns](https://duckdb.org/docs/sql/introduction) and tools. Authentication in MotherDuck is handled through secure [token-based access](/key-tasks/authenticating-and-connecting-to-motherduck/authenticating-to-motherduck/#creating-an-access-token), with comprehensive user and organization management capabilities. The platform uses a simplified access model where users either have full access to a database or none at all. The [SHARES](/key-tasks/sharing-data/managing-shares/) feature enables secure data sharing within organizations and with external parties through zero-copy clones that maintain data consistency and security. The [MotherDuck user interface](/getting-started/interfaces/motherduck-quick-tour/) provides a modern notebook-style environment for data interaction. The SQL IDE includes powerful features like intelligent autocomplete, AI-powered query suggestions and fixes, and an interactive Column Explorer that helps users understand and analyze their data structure. These features combine to create an intuitive and productive environment for data analysis. While MotherDuck is designed for analytical workloads, it's important to note that it's not optimized for high-frequency small transactions like traditional OLTP databases. The platform works best with batch operations and [analytical queries](https://duckdb.org/docs/sql/introduction), and users should consider using queues for streaming workloads to achieve optimal performance. Additionally, the database-level security model means access cannot be controlled at the schema or table level. ## Data ingestion: getting your data in MotherDuck provides multiple strategies for ingesting data into your data warehouse. The platform leverages DuckDB's powerful data loading capabilities while adding cloud-native features for seamless data ingestion at scale. You can load data through direct file imports, cloud storage connections, database migrations, or specialized ETL tools like [Fivetran](https://fivetran.com/docs/destinations/motherduck#motherduck) and [Airbyte](https://docs.airbyte.com/integrations/destinations/motherduck) depending on your needs. The [MotherDuck Web UI](/getting-started/interfaces/motherduck-quick-tour/) provides an intuitive interface for data loading and exploration. ### Loading local data Loading data from local files supports common formats like CSV, Parquet, and JSON. The [MotherDuck UI](/getting-started/interfaces/motherduck-quick-tour/) provides an intuitive interface for uploading files directly, while the [Python client](https://duckdb.org/docs/api/python/overview) enables programmatic loading using DuckDB's native functions. For example, you can use [read_csv()](https://duckdb.org/docs/data/csv), [read_parquet()](https://duckdb.org/docs/data/parquet), or [read_json()](https://duckdb.org/docs/data/json) to efficiently load data files while taking advantage of DuckDB's parallel processing capabilities. ### Interacting with cloud storage (S3, GCS, etc) Cloud storage integration lets you directly query and load data from major providers including [AWS S3](https://duckdb.org/docs/guides/import/s3_import), [Google Cloud Storage](https://duckdb.org/docs/guides/import/gcs_import), [Azure Blob Storage](https://duckdb.org/docs/stable/extensions/azure), and [Cloudflare R2](https://duckdb.org/docs/guides/import/s3_import). Using SQL commands like SELECT FROM read_parquet('s3://bucket/file.parquet'), you can seamlessly access cloud data. MotherDuck handles credential management securely through [environment variables](/key-tasks/authenticating-and-connecting-to-motherduck/authenticating-to-motherduck) or configuration settings. ### Database-to-database data loading For database migrations, MotherDuck supports importing data from other databases like [PostgreSQL](https://duckdb.org/docs/guides/import/query_postgres.html) and [MySQL](https://duckdb.org/docs/guides/import/query_mysql). You can directly connect to these sources using database connectors and execute queries to extract and load data. Existing [DuckDB databases](https://duckdb.org/docs/stable/data/multiple_files/overview) can be imported efficiently since MotherDuck is built on DuckDB's core engine. ### Fetching data from APIs [Data ingestion](/integrations/ingestion/) tools like Fivetran, Airbyte, dltHub and Estuary integrate with MotherDuck to provide automated, reliable data pipelines. These tools handle complex ETL workflows, data validation, and transformation while offering features like scheduling, monitoring and error handling that simplify ongoing data operations. For real-time data needs, MotherDuck works with streaming partners like [Estuary](https://docs.estuary.dev/reference/Connectors/materialization-connectors/motherduck/) to enable continuous data ingestion. While DuckDB is optimized for batch operations, these integrations allow you to build streaming pipelines that buffer and load data in micro-batches for near real-time analytics. ### Unstructured data integrations When working with unstructured data like documents, emails or images, tools like [Unstructured.io](https://motherduck.com/blog/effortless-etl-unstructured-data-unstructuredio-motherduck/) can pre-process and structure the data before loading into MotherDuck. This lets you analyze unstructured data alongside your structured data warehouse tables. ### Loading performance notes For optimal performance, follow DuckDB's recommended practices around batch sizes and data types. Load data in reasonably sized batches (at leasts 122k rows) to balance memory usage and throughput. Use appropriate data types like TIMESTAMP for datetime values and avoid unnecessary type conversions. Sort data by columns that are frequently queried together such as TIMESTAMPs. Monitor [recent queries](/sql-reference/motherduck-sql-reference/md_information_schema/recent_queries/) during large loads and adjust batch sizes accordingly. ## Data transformation: shaping your data for analysis Data transformation is a critical step in the data warehousing process that converts raw data into analysis-ready formats. MotherDuck provides powerful SQL capabilities inherited from DuckDB for transforming data directly within the warehouse. You can leverage DuckDB's rich library of SQL functions to clean, reshape, and model your data through operations like filtering, joining, aggregating and pivoting. ### Transformation tools - **[dbt (data build tool)](/integrations/transformation/dbt/)** * Native MotherDuck adapter for seamless integration to dbt core * Enables version controlled, modular SQL transformations * Supports testing, documentation and lineage tracking * Recommended for complex transformation workflows * See our [blog post](https://motherduck.com/blog/duckdb-dbt-e2e-data-engineering-project-part-2/) for detailed examples - **[SQLMesh](https://sqlmesh.readthedocs.io/en/stable/integrations/engines/motherduck/)** * Compatible with MotherDuck through DuckDB support * Provides data pipeline and transformation management * Enables incremental processing and scheduling * - **[Paradime](https://docs.paradime.io/app-help/documentation/settings/connections/scheduler-environment/duckdb)** * Modern data transformation platform built for DuckDB/MotherDuck * Offers collaborative development environment * Includes version control and deployment tools ## Orchestration: automating your data pipelines Orchestration is essential for keeping data up to date with MotherDuck. Scheduling data loads and transformations ensures your data warehouse stays current by running ingestion jobs at appropriate intervals to capture new data from your sources. Managing dependencies between tasks lets you create reliable pipelines where transformations only run after their prerequisite data loads complete successfully. Monitoring and alerting capabilities help you track pipeline health and quickly address any issues that arise. For orchestrating MotherDuck workflows, you have several options: Popular workflow orchestration platforms like [Airflow, Dagster, Kestra, Prefect and Bacalhau](/integrations/orchestration/) provide robust scheduling, dependency management and monitoring capabilities. For simpler use cases, basic scheduling tools like cron jobs or [GitHub Actions](/key-tasks/data-warehousing/orchestration/github-action-cron/) can effectively orchestrate data pipelines. Many ingestion & transformation tools also come with built-in orchestration features, allowing you to schedule and monitor data loads without additional tooling. When orchestrating MotherDuck pipelines, follow these best practices: - Design idempotent jobs that can safely re-run without duplicating or corrupting data. - Implement proper error handling and retries to gracefully handle temporary failures. - Set up logging and monitoring to maintain visibility into pipeline health and performance. ## Connecting BI tools and data applications MotherDuck provides robust support for business intelligence and reporting through its cloud data warehouse capabilities. The platform enables organizations to build scalable analytics solutions by connecting their data warehouse to popular visualization and reporting tools. With isolated compute tenancy per user, analysts can run complex queries without impacting other users' performance. For connecting popular BI tools, MotherDuck offers several integration options. Tableau users can connect through the [cloud and server connectors](/integrations/bi-tools/tableau/), with support for both token-based and environment variable authentication methods. The platform works with both live and extracted connections, and Tableau Bridge enables cloud connectivity. [Microsoft Power BI](/integrations/bi-tools/powerbi/) integration is achieved through the DuckDB ODBC driver and Power Query connector, supporting both import and DirectQuery modes. Other supported BI tools include Omni, Metabase, Preset/Superset, and Rill, typically connecting through standard JDBC/ODBC interfaces. MotherDuck seamlessly integrates with data science and AI tools through its native APIs and connectors. Python users can leverage the DuckDB SDK and Pandas integration for data analysis workflows. The platform supports R for statistical computing, while AI applications can be built using LangChain or LlamaIndex integrations. Notebook tools like Hex and Jupyter provide both hosted and on-prem environments for data exploration. For building [custom data applications](/getting-started/customer-facing-analytics/), MotherDuck's unique architecture enables novel approaches through its WASM-powered 1.5-tier architecture. The platform runs DuckDB in the browser through WebAssembly, allowing for highly interactive visualizations with near-zero latency. Developers can use MotherDuck's APIs and SDKs in languages like Python and Go to create custom data applications that leverage both local and cloud-based data processing. ## Advanced topics & best practices ### Performance tuning and optimization in MotherDuck MotherDuck inherits DuckDB's powerful query optimization capabilities. You can analyze query performance using the `EXPLAIN` command to view execution plans and identify bottlenecks. While DuckDB doesn't use traditional indexes, it automatically creates statistics and metadata to optimize query execution with row groups. As a result, [sorting the data on insert](https://duckdb.org/2025/05/14/sorting-for-fast-selective-queries.html) is very effective way to improve query performance. ### Data sharing and collaboration MotherDuck implements a data sharing model through SHARES, which provide read-only access to specific databases. To create a share, use the [`CREATE SHARE`](/sql-reference/motherduck-sql-reference/create-share/) command and specify the database you want to share. Recipients can then access the shared data through their own MotherDuck account while maintaining data isolation. ### Monitoring and logging MotherDuck usage DuckDB's meta-queries like `EXPLAIN ANALYZE` provide detailed query execution statistics. You can also use the platform's built-in profiling capabilities to monitor query performance and resource utilization, helping identify optimization opportunities and troubleshoot performance issues. [Recent queries](/sql-reference/motherduck-sql-reference/md_information_schema/recent_queries/) and [historical queries](/sql-reference/motherduck-sql-reference/md_information_schema/query_history/) can be observed as well, to further optimize the warehouse load. ### Cost management While MotherDuck's pricing model is still evolving, you can optimize costs by efficiently managing compute resources. Consider implementing data lifecycle policies to archive or delete old data. Monitor query patterns to identify opportunities for optimization and avoid unnecessary data processing. ### Security best practices for your MotherDuck warehouse - Implement robust security practices by following MotherDuck's database-level security model. - Use token-based authentication for all connections and avoid sharing credentials. - When integrating with tools, leverage environment variables for secure credential management. - Regularly audit database access and maintain an inventory of active shares. ### Leveraging AI features within MotherDuck MotherDuck enhances DuckDB with AI-powered features to improve productivity. The platform includes a [SQL AI fixer](/getting-started/interfaces/motherduck-quick-tour/#fix-errors-and-edit-queries-with-ai) that helps identify and correct query syntax issues. The `prompt()` function enables natural language interactions with your data warehouse, allowing users to generate SQL queries from plain English descriptions. These are just a few of the AI capabilities that help make data analysis more accessible while maintaining the power and flexibility of SQL. ## Further guides: ## Included pages - [GitHub Actions](https://motherduck.com/docs/key-tasks/data-warehousing/orchestration/github-action-cron): Schedule MotherDuck SQL and dbt jobs with GitHub Actions as a lightweight cron-based orchestrator. - [PostgreSQL](https://motherduck.com/docs/key-tasks/data-warehousing/replication/postgres): Replicate PostgreSQL tables to MotherDuck using DuckDB and the PostgreSQL extension. - [Dagster](https://motherduck.com/docs/key-tasks/data-warehousing/orchestration/dagster): Orchestrate an incremental S3-to-MotherDuck data loading pipeline with Dagster and Python. - [SQL Server](https://motherduck.com/docs/key-tasks/data-warehousing/replication/sql-server): Replicate SQL Server tables to MotherDuck using Python and dataframes. - [Environment management](https://motherduck.com/docs/key-tasks/data-warehousing/environment-management): Set up development, staging, and production data environments in MotherDuck with workload isolation, read-only access to production data, and dbt. - [Flat Files](https://motherduck.com/docs/key-tasks/data-warehousing/replication/flat-files): Load CSV, Parquet, and JSON files into MotherDuck from local storage or cloud sources. - [Excel and Google Sheets](https://motherduck.com/docs/key-tasks/data-warehousing/replication/spreadsheets): Load Excel and Google Sheets data into MotherDuck using the DuckDB CLI or HTTPS CSV export URLs. ## Appendix ### Troubleshooting common issues When working with MotherDuck, you may encounter challenges around data loading, query performance, or connectivity. For data loading issues, refer to our [best practices for programmatic loading](/key-tasks/data-warehousing/) which covers optimizing batch sizes and file formats. For query performance, review our [Dual Execution capabilities](/concepts/architecture-and-capabilities/#dual-execution) to understand how MotherDuck optimizes query execution across local and cloud resources. For connectivity problems, check our [authentication guides](/key-tasks/authenticating-and-connecting-to-motherduck/connecting-to-motherduck) and ensure you're following the recommended connection patterns. ### Useful SQL snippets for MotherDuck MotherDuck supports a wide range of SQL functionality inherited from DuckDB. For data ingestion, refer to our [PostgreSQL replication examples](/key-tasks/data-warehousing/replication/postgres) which demonstrate common patterns for loading data. For building customer facing analytics, check our [guide](/getting-started/customer-facing-analytics) which includes examples of data processing and visualization queries. The [DuckDB SQL documentation](https://duckdb.org/docs/sql/introduction.html) provides comprehensive reference for the SQL dialect. ### Links to further resources (MotherDuck docs, community) To deepen your understanding of data warehousing with MotherDuck, explore our [data warehousing concepts guide](/key-tasks/data-warehousing/) which covers architectural principles and best practices. For hands-on examples, the free [DuckDB in Action eBook](https://motherduck.com/duckdb-book-brief/) provides real-world scenarios and solutions. If you need help, don't hesitate to [contact our support team](https://motherduck.com/customer-support/) or explore our [ecosystem integrations](/integrations/) for additional tools and capabilities. Please do not hesitate to **[contact us](https://motherduck.com/customer-support/)** if you need help along your journey. --- Source: https://motherduck.com/docs/key-tasks/data-warehousing/orchestration/github-action-cron # GitHub Actions > Schedule MotherDuck SQL and dbt jobs with GitHub Actions as a lightweight cron-based orchestrator. GitHub Actions works well as a lightweight orchestrator for simple MotherDuck jobs: nightly SQL scripts, small ELT steps, dbt builds, smoke tests, and periodic exports. It is not a full data orchestrator, but it is often enough when a pipeline has one or two steps and can tolerate GitHub's scheduler behavior. ## When to use this pattern | Use GitHub Actions when | Use a dedicated orchestrator when | |-------------------------|-----------------------------------| | The job has a small number of steps | Jobs have complex dependencies or branching | | A missed or delayed run can be retried manually | Every run needs strict service-level guarantees | | The pipeline can run from repository files | State, retries, and backfills need first-class tracking | | GitHub is already where you review pipeline changes | Multiple teams need a shared orchestration UI | For larger workflows, use a tool from the [MotherDuck orchestration ecosystem](https://motherduck.com/ecosystem/?category=Orchestration). ## Set up authentication Create a [MotherDuck access token](/key-tasks/authenticating-and-connecting-to-motherduck/authenticating-to-motherduck/#creating-an-access-token), preferably from a service account dedicated to the pipeline. Store it as a GitHub repository secret named `MOTHERDUCK_TOKEN`: ```bash gh secret set MOTHERDUCK_TOKEN ``` Use the token as an environment variable in workflow steps. Avoid putting tokens directly into SQL files, command arguments, artifacts, or logs. ## Choose the trigger Most MotherDuck cron jobs should support both manual and scheduled runs with GitHub Actions [`workflow_dispatch`](https://docs.github.com/en/actions/reference/workflows-and-actions/workflow-syntax#onworkflow_dispatch) and [`schedule`](https://docs.github.com/en/actions/reference/workflows-and-actions/events-that-trigger-workflows#schedule) triggers: ```yaml on: workflow_dispatch: schedule: - cron: "17 2 * * *" ``` Keep these GitHub Actions scheduling details in mind: - Scheduled workflows run from the latest commit on the default branch. - Cron schedules use UTC by default. - The shortest supported interval is every 5 minutes. - Jobs scheduled at the top of the hour can be delayed or dropped during periods of high GitHub Actions load. Pick a non-zero minute such as `17` or `43`. - `workflow_dispatch` lets you test the same workflow manually and rerun failed jobs after a fix. ## Example: run a SQL file on a schedule This example runs a checked-in SQL script every night and on demand. It uses: - Least-privilege repository permissions - A timeout so failed jobs do not burn runner minutes indefinitely - A concurrency group so two runs do not write to the same target at once - The MotherDuck install script for a compatible DuckDB CLI Create `.github/workflows/motherduck-nightly-sql.yml`: ```yaml name: motherduck nightly sql on: workflow_dispatch: schedule: - cron: "17 2 * * *" permissions: contents: read concurrency: group: motherduck-nightly-sql cancel-in-progress: false jobs: run-sql: runs-on: ubuntu-24.04 timeout-minutes: 15 env: motherduck_token: ${{ secrets.MOTHERDUCK_TOKEN }} steps: - name: Check out repository uses: actions/checkout@v6 - name: Install DuckDB CLI run: | install_home="$RUNNER_TEMP/motherduck" mkdir -p "$install_home" curl -s https://install.motherduck.com | env -u motherduck_token HOME="$install_home" sh echo "$install_home/.duckdb/cli/latest" >> "$GITHUB_PATH" - name: Run nightly SQL run: duckdb "md:" < sql/nightly_orders.sql ``` Create `sql/nightly_orders.sql`: ```sql CREATE DATABASE IF NOT EXISTS analytics; USE analytics; CREATE SCHEMA IF NOT EXISTS orchestration; CREATE TABLE IF NOT EXISTS orchestration.github_action_runs ( run_id VARCHAR, workflow_name VARCHAR, run_started_at TIMESTAMP ); DELETE FROM orchestration.github_action_runs WHERE run_id = getenv('GITHUB_RUN_ID'); INSERT INTO orchestration.github_action_runs VALUES ( getenv('GITHUB_RUN_ID'), getenv('GITHUB_WORKFLOW'), current_timestamp ); ``` Replace `analytics` with the MotherDuck database your pipeline should write to. The example creates the database if it does not already exist so a new repository can run without extra setup. The GitHub secret is named `MOTHERDUCK_TOKEN`, while the workflow exposes it as `motherduck_token`. The DuckDB CLI can use that environment variable to connect to MotherDuck non-interactively in GitHub Actions. The install step uses `RUNNER_TEMP` as `HOME` and unsets `motherduck_token` for the installer process so the install script does not try to update the runner's shell profile or validate the connection before the SQL step runs. ## Example: run dbt on a schedule For dbt projects, keep the dbt profile in the repository and read the MotherDuck token from the GitHub secret. Create `.github/workflows/motherduck-dbt.yml`: ```yaml name: motherduck dbt on: workflow_dispatch: schedule: - cron: "43 3 * * *" permissions: contents: read concurrency: group: motherduck-dbt-prod cancel-in-progress: false jobs: dbt-build: runs-on: ubuntu-24.04 timeout-minutes: 30 env: MOTHERDUCK_TOKEN: ${{ secrets.MOTHERDUCK_TOKEN }} steps: - name: Check out repository uses: actions/checkout@v6 - name: Set up Python uses: actions/setup-python@v6 with: python-version: "3.12" cache: pip - name: Install dbt run: python -m pip install -r requirements.txt - name: Install dbt packages run: dbt deps - name: Build dbt project run: dbt build --profiles-dir .github/dbt --target prod ``` Create `requirements.txt`: ```text dbt-duckdb>=1.9,<2.0 ``` Create `.github/dbt/profiles.yml`: ```yaml motherduck: target: prod outputs: prod: type: duckdb path: "md:analytics?motherduck_token={{ env_var('MOTHERDUCK_TOKEN') }}" threads: 4 ``` In `dbt_project.yml`, set the same profile name: ```yaml profile: motherduck ``` ## Production checklist | Area | Recommendation | |------|----------------| | Authentication | Use a service account token stored as `MOTHERDUCK_TOKEN`. Rotate it on the same cadence as other production secrets. | | Permissions | Set `permissions: contents: read` unless the workflow must write to the repository or call GitHub APIs. | | Scheduling | Use non-zero cron minutes and keep `workflow_dispatch` enabled for manual retries. | | Concurrency | Use a `concurrency` group for jobs that write to the same tables. | | Idempotency | Make SQL safe to rerun. Prefer `CREATE TABLE IF NOT EXISTS`, `CREATE OR REPLACE TABLE`, `MERGE`, or delete-and-insert patterns keyed by the run or partition. | | Timeouts | Set `timeout-minutes` on every job. | | Dependencies | Pin dependencies in `requirements.txt` or an equivalent lock file. Use dependency caching for Python/dbt jobs. | | Environments | Use separate service accounts and databases for development, staging, and production. | | Observability | Write a run record to a small audit table and rely on GitHub Actions notifications for failures. | ## Related content - [Authenticating to MotherDuck](/key-tasks/authenticating-and-connecting-to-motherduck/authenticating-to-motherduck/) - [dbt with DuckDB and MotherDuck](/integrations/transformation/dbt/) - [DuckDB CLI](/getting-started/interfaces/connect-query-from-duckdb-cli/) - [Orchestration integrations](https://motherduck.com/ecosystem/?category=Orchestration) --- Source: https://motherduck.com/docs/key-tasks/data-warehousing/orchestration/dagster # Dagster > Orchestrate an incremental S3-to-MotherDuck data loading pipeline with Dagster and Python. Use Dagster when you want asset lineage, schedules, retries, and run history around a Python data loading job. This guide builds a minimum viable Dagster asset that reads Parquet data from S3, loads rows newer than the last successful run, upserts them into MotherDuck, and stores a watermark for the next run. The example uses a public S3 Parquet file from the MotherDuck sample data bucket. Replace the S3 path and column mapping with your own bucket layout when you move from the demo to your pipeline. ## How the pipeline works ```mermaid graph LR S3[("S3 Parquet file")]:::yellow A["Dagster asset
taxi_trips"]:::watermelon W[("ingestion_watermarks")]:::yellow T[("taxi_trips")]:::yellow W --> A S3 --> A A --> T A --> W ``` The asset keeps the state in MotherDuck: - `taxi_trips` is the target table. - `ingestion_watermarks` stores the latest `pickup_at` value loaded by this pipeline. - Each run reads only rows where `tpep_pickup_datetime` is greater than the stored watermark. - The target table has a primary key, so reprocessing the same row updates the existing row instead of creating a duplicate. ## Prerequisites Before you start, ensure you have: - Python 3.10 or later. - `uv` for Python project and dependency management. - A MotherDuck access token in `MOTHERDUCK_TOKEN`. - A MotherDuck database name for the pipeline. The example creates the database if it doesn't exist. - For private S3 buckets, a MotherDuck S3 secret. See [Amazon S3 credentials](/integrations/cloud-storage/amazon-s3/) for setup. :::tip Use a dedicated MotherDuck service account for scheduled ingestion jobs. This keeps ingestion compute, permissions, and cost attribution separate from analyst and application workloads. See [Hypertenancy](/concepts/hypertenancy/) for the compute isolation model. ::: ## Create the Dagster project Create a small Python project and add Dagster with DuckDB: ```bash > uv init dagster-motherduck-s3 > cd dagster-motherduck-s3 > uv add dagster dagster-webserver duckdb ``` Create `definitions.py`: ```python import os import re import dagster as dg import duckdb S3_URI = os.getenv( "S3_URI", "s3://us-prd-motherduck-open-datasets/nyc_taxi/parquet/yellow_cab_nyc_2022_11.parquet", ) MOTHERDUCK_DATABASE = os.getenv("MOTHERDUCK_DATABASE", "dagster_s3_demo") PIPELINE_NAME = "dagster_s3_taxi_trips" # Optional cap for running the demo quickly. Leave unset for a real pipeline. INGESTION_END_TS = os.getenv("MOTHERDUCK_INGESTION_END_TS") PUBLIC_DEMO_SCOPE = "s3://us-prd-motherduck-open-datasets/" def database_identifier(name: str) -> str: if not re.fullmatch(r"[A-Za-z_][A-Za-z0-9_]*", name): raise ValueError("Use a database name with letters, numbers, and underscores.") return name def open_motherduck_connection() -> duckdb.DuckDBPyConnection: database = database_identifier(MOTHERDUCK_DATABASE) con = duckdb.connect("md:") con.execute(f"CREATE DATABASE IF NOT EXISTS {database}") con.execute(f"USE {database}") if S3_URI.startswith(PUBLIC_DEMO_SCOPE): con.execute(""" CREATE OR REPLACE TEMPORARY SECRET public_motherduck_open_data ( TYPE S3, PROVIDER config, REGION 'us-east-1', SCOPE 's3://us-prd-motherduck-open-datasets/' ) """) return con @dg.asset def taxi_trips(context: dg.AssetExecutionContext) -> dg.MaterializeResult: con = open_motherduck_connection() try: con.execute(""" CREATE TABLE IF NOT EXISTS taxi_trips ( trip_id VARCHAR PRIMARY KEY, pickup_at TIMESTAMP, dropoff_at TIMESTAMP, passenger_count DOUBLE, trip_distance DOUBLE, total_amount DOUBLE, source_file VARCHAR, loaded_at TIMESTAMP DEFAULT now() ) """) con.execute(""" CREATE TABLE IF NOT EXISTS ingestion_watermarks ( pipeline_name VARCHAR PRIMARY KEY, last_pickup_at TIMESTAMP ) """) con.execute(""" INSERT INTO ingestion_watermarks VALUES (?, TIMESTAMP '1970-01-01') ON CONFLICT (pipeline_name) DO NOTHING """, [PIPELINE_NAME]) last_pickup_at = con.execute( "SELECT last_pickup_at FROM ingestion_watermarks WHERE pipeline_name = ?", [PIPELINE_NAME], ).fetchone()[0] con.execute(""" CREATE OR REPLACE TEMP TABLE new_taxi_trips AS SELECT md5(concat_ws('|', VendorID::VARCHAR, tpep_pickup_datetime::VARCHAR, tpep_dropoff_datetime::VARCHAR, PULocationID::VARCHAR, DOLocationID::VARCHAR, total_amount::VARCHAR )) AS trip_id, tpep_pickup_datetime AS pickup_at, tpep_dropoff_datetime AS dropoff_at, passenger_count, trip_distance, total_amount, filename AS source_file, now() AS loaded_at FROM read_parquet(?, filename = true) WHERE tpep_pickup_datetime > ? AND (? IS NULL OR tpep_pickup_datetime < ?::TIMESTAMP) """, [S3_URI, last_pickup_at, INGESTION_END_TS, INGESTION_END_TS]) rows_loaded = con.execute("SELECT count(*) FROM new_taxi_trips").fetchone()[0] con.execute(""" INSERT INTO taxi_trips BY NAME SELECT * FROM new_taxi_trips ON CONFLICT (trip_id) DO UPDATE SET pickup_at = excluded.pickup_at, dropoff_at = excluded.dropoff_at, passenger_count = excluded.passenger_count, trip_distance = excluded.trip_distance, total_amount = excluded.total_amount, source_file = excluded.source_file, loaded_at = excluded.loaded_at """) max_pickup_at = con.execute( "SELECT max(pickup_at) FROM new_taxi_trips" ).fetchone()[0] if max_pickup_at is not None: con.execute( "UPDATE ingestion_watermarks SET last_pickup_at = ? WHERE pipeline_name = ?", [max_pickup_at, PIPELINE_NAME], ) total_rows = con.execute("SELECT count(*) FROM taxi_trips").fetchone()[0] context.log.info("Loaded %s rows into taxi_trips", rows_loaded) return dg.MaterializeResult( metadata={ "rows_loaded": rows_loaded, "total_rows": total_rows, "last_pickup_at": str(max_pickup_at or last_pickup_at), } ) finally: con.close() daily_s3_ingestion = dg.ScheduleDefinition( name="daily_s3_taxi_trips", cron_schedule="0 2 * * *", target=[taxi_trips], ) defs = dg.Definitions( assets=[taxi_trips], schedules=[daily_s3_ingestion], ) if __name__ == "__main__": result = dg.materialize([taxi_trips]) if not result.success: raise RuntimeError("Dagster materialization failed.") ``` ## Run the ingestion Set the MotherDuck token and database name: ```bash > export MOTHERDUCK_TOKEN="" > export MOTHERDUCK_DATABASE="dagster_s3_demo" ``` For the public demo file, you can cap the first run to one day of taxi trips so the example finishes quickly: ```bash > export MOTHERDUCK_INGESTION_END_TS="2022-11-02" ``` Run the asset once from Python: ```bash > uv run python definitions.py ``` Run the same command again. The second run should load `0` rows because the first run advanced the watermark. Verify the loaded rows in MotherDuck: ```sql SELECT count(*) FROM taxi_trips; SELECT pipeline_name, last_pickup_at FROM ingestion_watermarks; ``` When you use your own S3 data, remove `MOTHERDUCK_INGESTION_END_TS` and replace: - `S3_URI` with your `s3:////*.parquet` path. - The `SELECT` list in `new_taxi_trips` with your source columns. - The watermark column with a stable source timestamp, such as `updated_at` or `created_at`. - The primary key expression with the source system's durable row key. ## Run it in Dagster Start the Dagster UI from the same directory: ```bash > uv run dagster dev -f definitions.py ``` Open `http://localhost:3000`, select the `taxi_trips` asset, and materialize it. Dagster records the asset materialization, metadata, logs, and schedule definition. To use the schedule in a long-running Dagster deployment, keep the `daily_s3_taxi_trips` schedule enabled and run a Dagster daemon. For local one-off testing, `uv run python definitions.py` is enough. ## Production considerations This example is intentionally small. Before using the pattern in production: - Use a dedicated service account token with only the permissions needed for ingestion. - Store private bucket credentials as a MotherDuck S3 secret instead of embedding AWS keys in code. - Keep S3 files in Parquet and avoid very small files. See [S3 import best practices](/key-tasks/cloud-storage/s3-import-best-practices/). - Use a source-provided primary key for upserts. Hashing source fields is useful for demos but less stable than a real key. - Use a source timestamp that only moves forward for watermarking. If your source sends late-arriving records, add a small overlap window and deduplicate by primary key. ## Related content - [Amazon S3 credentials](/integrations/cloud-storage/amazon-s3/) - [S3 import best practices](/key-tasks/cloud-storage/s3-import-best-practices/) - [Connecting to MotherDuck](/key-tasks/authenticating-and-connecting-to-motherduck/connecting-to-motherduck/) - [Hypertenancy](/concepts/hypertenancy/) --- Source: https://motherduck.com/docs/key-tasks/data-warehousing/replication/postgres # PostgreSQL > Replicate PostgreSQL tables to MotherDuck using DuckDB and the PostgreSQL extension. This page shows SQL patterns for connecting DuckDB to PostgreSQL, connecting to MotherDuck, and writing data from PostgreSQL into MotherDuck. For more complex replication scenarios, use one of our [ingestion partners](https://motherduck.com/ecosystem/?category=Ingestion). If you are looking for the [pg_duckdb extension](https://github.com/duckdb/pg_duckdb), see the [pg_duckdb explainer page](/concepts/pgduckdb). To skip the documentation and look at the entire script, expand the element below:
SQL script ```sql -- install the PostgreSQL extension in DuckDB INSTALL postgres; LOAD postgres; -- tune the local DuckDB client for a larger initial load SET threads = 4; SET memory_limit = '4GB'; SET pg_connection_limit = 4; SET pg_pages_per_task = 250; -- attach PostgreSQL as pg_db ATTACH 'dbname=postgres user=postgres host=127.0.0.1' AS pg_db (TYPE POSTGRES, READ_ONLY); -- connect to MotherDuck ATTACH 'md:'; USE my_db; -- copy a PostgreSQL table into MotherDuck CREATE OR REPLACE TABLE main.postgres_table AS SELECT * FROM pg_db.public.some_table ```
## Loading the PostgreSQL extension and authenticating :::info MotherDuck does not yet support the PostgreSQL and MySQL extensions, so you need to perform the following steps on your own computer or cloud computing resource. We are working on supporting the PostgreSQL extension on the server side so that this can happen within the MotherDuck app in the future with improved performance. ::: The first step is to install and load the PostgreSQL extension using the [DuckDB CLI](/getting-started/interfaces/connect-query-from-duckdb-cli): ```sql INSTALL postgres; LOAD postgres; ``` Once this is completed, you can connect to PostgreSQL by attaching it to your DuckDB session: ```sql ATTACH 'dbname=postgres user=postgres host=127.0.0.1' AS pg_db (TYPE POSTGRES, READ_ONLY); ``` More detailed information can be found on the [DuckDB documentation](https://duckdb.org/docs/extensions/postgres.html#connecting). For larger initial loads, tune the DuckDB client explicitly instead of relying on defaults: ```sql SET threads = 8; SET memory_limit = '8GB'; SET pg_connection_limit = 8; SET pg_pages_per_task = 250; ``` `pg_connection_limit` controls how many PostgreSQL connections DuckDB may open for the scan, while `pg_pages_per_task` controls how much table work is grouped into each scan task. ## Connecting to MotherDuck and inserting the table Once you are connected to your PostgreSQL database, you need to connect to MotherDuck. To learn more, see [Connecting to MotherDuck](/key-tasks/authenticating-and-connecting-to-motherduck/connecting-to-motherduck). ```sql ATTACH 'md:'; USE my_db; ``` Once you have authenticated, you can use `CREATE TABLE AS SELECT` to replicate data from PostgreSQL into MotherDuck. ```sql CREATE OR REPLACE TABLE main.postgres_table AS SELECT * FROM pg_db.public.some_table ``` Congratulations! You have now replicated data from PostgreSQL into MotherDuck. ## Choosing the right PostgreSQL workflow ### Use DuckDB's PostgreSQL extension for client-side movement Use DuckDB's PostgreSQL extension when you want to copy a PostgreSQL table into MotherDuck for analytics, backfill a MotherDuck table from PostgreSQL, or export a DuckDB or MotherDuck result set back into PostgreSQL from a controlled DuckDB client. Keep the client close to both systems, use `READ_ONLY` for PostgreSQL sources, and chunk large writes when the destination is PostgreSQL so you do not overload an OLTP database. ### Use the Postgres endpoint for 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. It is the preferred path for PostgreSQL-compatible clients because it does not require installing or operating a PostgreSQL extension. ### Use pg_duckdb when the query must run inside PostgreSQL Use `pg_duckdb` only when you specifically need PostgreSQL itself to host the integration. This is useful when queries must run inside an existing PostgreSQL database, when PostgreSQL-local tables need to be joined with DuckDB or MotherDuck data from that PostgreSQL environment, or when a tool must connect to a PostgreSQL server that you control. For ongoing production replication from PostgreSQL into MotherDuck, prefer an ingestion or CDC partner. Those tools handle scheduling, retries, incremental state, schema changes, and operational monitoring better than a one-off SQL script. ## Best practices Here are a few tips to keep large PostgreSQL replication jobs predictable. ### Run DuckDB close to both systems The DuckDB client is the data mover in this workflow. Run it on a machine with a good network path to both PostgreSQL and MotherDuck, and avoid running large backfills on the same host as a production PostgreSQL instance when possible. ### Tune scan parallelism explicitly Start with `threads` set to the available CPU count on the client and `memory_limit` set below total system memory. For larger tables, start with `pg_connection_limit` in the `4-8` range and `pg_pages_per_task` in the `250-1000` range, then tune after observing the source database. ::::warning[Watch Out] Increasing `pg_connection_limit` can increase pressure on the source PostgreSQL instance. If PostgreSQL memory or connection pressure climbs, reduce `pg_connection_limit` before reducing DuckDB `threads`. :::: ### Keep PostgreSQL sources read-only Use `READ_ONLY` when attaching PostgreSQL for an initial replication job. For long-lived scripts, use PostgreSQL environment variables, the PostgreSQL password file, or DuckDB secrets instead of embedding credentials directly in the connection string. ### Reduce each statement's working set The DuckDB side of this workflow is usually streaming, so out-of-memory risk is often driven by the source PostgreSQL instance and total host headroom rather than DuckDB buffering the full table. Project only the columns you need when source rows are wide, and replicate very large tables in smaller primary key or time ranges. ### Load in chunks For a very large initial backfill, create the target table once and then insert one range at a time. ```sql INSTALL postgres; LOAD postgres; SET threads = 4; SET memory_limit = '4GB'; SET pg_connection_limit = 4; SET pg_pages_per_task = 250; ATTACH 'dbname=postgres user=postgres host=127.0.0.1' AS pg_db (TYPE POSTGRES, READ_ONLY); ATTACH 'md:'; USE my_db; CREATE TABLE IF NOT EXISTS main.postgres_table AS SELECT * FROM pg_db.public.some_table WHERE 1 = 0; INSERT INTO main.postgres_table SELECT * FROM pg_db.public.some_table WHERE updated_at >= TIMESTAMP '2026-01-01' AND updated_at < TIMESTAMP '2026-02-01'; ``` Repeat the `INSERT` statement for each chunk until the backfill is complete. ## Handling more complex workflows Production use cases tend to be much more complex and include things like incremental builds and state management. In those scenarios, please take a look at our [ingestion partners](https://motherduck.com/ecosystem/?category=Ingestion), which includes many options including some that offer native Python. An overview of the MotherDuck Ecosystem is shown below. ![Diagram](../../../img/md-diagram.svg) --- Source: https://motherduck.com/docs/key-tasks/data-warehousing/replication/sql-server # Replicating SQL Server tables to MotherDuck > Replicate SQL Server tables to MotherDuck using Python and dataframes. This page will serve to show basic patterns for using Python to connect to SQL Server, read data into a dataframe, connect to MotherDuck, and then writing the data from the dataframe into MotherDuck. For more complex replication scenarios, please take a look at our [ingestion partners](https://motherduck.com/ecosystem/?category=Ingestion). To skip the documentation and look at the entire script, expand the element below:
Python script ```py import pyodbc # Define your connection parameters server = 'ip_address' database = 'master' # or use your database name username = 'your_username' password = 'your_password' # consider using a secret manager or .env port = 1433 # default SQL Server port # Define the connection string for ODBC Driver 17 connection_string = ( f"DRIVER={{ODBC Driver 17 for SQL Server}};" f"SERVER={server},{port};" f"DATABASE={database};" f"UID={username};" f"PWD={password};" ) # Connect to SQL Server try: connection = pyodbc.connect(connection_string) print("Connection successful.") except pyodbc.Error as e: print(f"Error: {e}") finally: connection.close() import pandas as pd try: connection = pyodbc.connect(connection_string) query = "SELECT * FROM AdventureWorks2022.Production.BillOfMaterials" # Execute the query using pyodbc cursor = connection.cursor() cursor.execute(query) # Fetch the column names and data columns = [column[0] for column in cursor.description] data = cursor.fetchall() # Convert the data into a DataFrame df = pd.DataFrame.from_records(data, columns=columns) finally: connection.close() import duckdb motherduck_token = 'your_token' # Attach using the MOTHERDUCK_TOKEN duckdb.sql(f"ATTACH 'md:my_db?MOTHERDUCK_TOKEN={motherduck_token}'") # Create or replace table in the attached database duckdb.sql( """ CREATE OR REPLACE TABLE my_db.main.BillOfMaterials AS SELECT * FROM df """ ) ```
## SQL Server Authentication SQL Server supports [multiple methods of authentication](https://learn.microsoft.com/en-us/sql/relational-databases/security/choose-an-authentication-mode?view=sql-server-ver16) - for the purpose of this example, we will use username/password authentication and [pyodbc](https://github.com/mkleehammer/pyodbc/), along with [ODBC Driver 17 for SQL Server](https://learn.microsoft.com/en-us/sql/connect/odbc/download-odbc-driver-for-sql-server?view=sql-server-ver16). It should be noted that 'ODBC Driver 18 for SQL Server' is also available and includes support for some newer SQL Server features, but for the sake of compatibility, this example will use 17. Consider the following authentication example: ```py import pyodbc # Define your connection parameters server = 'ip_address' database = 'master' # or use your database name username = 'your_username' password = 'your_password' # consider using a secret manager or .env port = 1433 # default SQL Server port # Define the connection string for ODBC Driver 17 connection_string = ( f"DRIVER={{ODBC Driver 17 for SQL Server}};" f"SERVER={server},{port};" f"DATABASE={database};" f"UID={username};" f"PWD={password};" ) # Connect to SQL Server try: connection = pyodbc.connect(connection_string) print("Connection successful.") except pyodbc.Error as e: print(f"Error: {e}") finally: connection.close() ``` This will set your credentials, and then attempt to connect to your server with `pyodbc.connect`, and return an error if it fails. ## Reading a SQL Server table into a dataframe Once you have authenticated, you can define arbitrary queries and then execute them with `pd.read_sql`, using the `query` and `connection` objects. For the purpose of this example, we are using SQL Server 2022 along with the AdventureWorks OLTP database. :::note While `pandas` is a great library, it is not particularly well-suited for very large tables. To learn more about using buffers and alternative libraries, check out [Loading data with Python](/key-tasks/loading-data-into-motherduck/loading-data-md-python/). ::: ```py import pandas as pd try: connection = pyodbc.connect(connection_string) query = "SELECT * FROM AdventureWorks2022.Production.BillOfMaterials" # Execute the query using pyodbc cursor = connection.cursor() cursor.execute(query) # Fetch the column names and data columns = [column[0] for column in cursor.description] data = cursor.fetchall() # Convert the data into a DataFrame df = pd.DataFrame.from_records(data, columns=columns) finally: connection.close() ``` ## Inserting the table into MotherDuck Now that the data has been loaded into a dataframe object, we can connect to MotherDuck and insert the table. :::note You will need to [generate a token](/key-tasks/authenticating-and-connecting-to-motherduck/authenticating-to-motherduck/#creating-an-access-token) in your MotherDuck account. For production use cases, make sure to use a secret manager and never commit your token to your codebase. ::: ```py import duckdb motherduck_token = 'your_token' # Attach using the MOTHERDUCK_TOKEN duckdb.sql(f"ATTACH 'md:my_db?MOTHERDUCK_TOKEN={motherduck_token}'") # Create or replace table in the attached database duckdb.sql( """ CREATE OR REPLACE TABLE my_db.main.BillOfMaterials AS SELECT * FROM df """ ) ``` This will create the table, or replace it for the table already exists. ## Handling More Complex Workflows Production use cases tend to be much more complex and include things like incremental builds & state management. In those scenarios, please take a look at our [ingestion partners](https://motherduck.com/ecosystem/?category=Ingestion), which includes many options including some that offer native python. An overview of the MotherDuck Ecosystem is shown below. ![Diagram](../../../img/md-diagram.svg) --- Source: https://motherduck.com/docs/key-tasks/data-warehousing/replication/flat-files # Replicating flat files to MotherDuck > Load CSV, Parquet, and JSON files into MotherDuck from local storage or cloud sources. The goal of this guide is to show users simple examples of loading data from flat file sources into MotherDuck. Examples are shown for both the MotherDuck Web UI and the DuckDB CLI. To install the DuckDB CLI, [check out the instructions first.](/getting-started/interfaces/connect-query-from-duckdb-cli) ## CSV ### MotherDuck UI From the UI, follow these steps: 1. Navigate to the **Add Data** section. 2. Select the file. This file will be uploaded into your browser so that it can be queried by DuckDB. 3. Execute the generated query which will create a table for you. 1. Modify the query as needed to suit the correct Database / Schema / Table name. ### DuckDB CLI In the CLI, you can load a CSV file using the `read_csv` function. For example: ### Local file ```sql CREATE TABLE my_table AS SELECT * FROM read_csv('path/to/local_file.csv'); ``` ### S3 file To load from S3, ensure your DuckDB instance is configured with [S3 secrets](/documentation/integrations/cloud-storage/amazon-s3.mdx). Then: ```sql CREATE TABLE my_table AS SELECT * FROM read_csv('s3://bucket-name/path-to-file.csv'); ``` ## JSON ### MotherDuck UI From the UI, follow these steps: 1. Navigate to the **Add Data** section. 2. Select the file. This file will be uploaded into your browser so that it can be queried by DuckDB. 3. Execute the generated query which will create a table for you. 1. Modify the query as needed to suit the correct Database / Schema / Table name. ### DuckDB CLI In the CLI, use the `read_json` function to load JSON files. ### Local file ```sql CREATE TABLE my_table AS SELECT * FROM read_json('path/to/local_file.json'); ``` ### S3 file Make sure S3 support is enabled as described in the [S3 secrets documentation](/documentation/integrations/cloud-storage/amazon-s3.mdx). ```sql CREATE TABLE my_table AS SELECT * FROM read_json('s3://bucket-name/path-to-file.json'); ``` :::tip[Provide a schema for large or deeply nested JSON] When loading large JSON files, DuckDB scans the data to discover the schema during query planning. For deeply nested or complex JSON, this can add significant time. To speed things up, provide the schema directly with the `columns` parameter: ```sql CREATE TABLE my_table AS SELECT * FROM read_json( 'path/to/local_file.json', columns={ id: 'BIGINT', name: 'VARCHAR', amount: 'DECIMAL(10,2)' } ); ``` If you already have a table with the right schema, use `INSERT INTO` instead of `CREATE TABLE AS` — DuckDB skips schema discovery when the target schema is known: ```sql INSERT INTO my_table SELECT * FROM read_json('path/to/local_file.json'); ``` You can also limit how deep DuckDB looks into nested structures with `maximum_depth`, or reduce the number of sampled objects with `sample_size` (default: 20480). See the [DuckDB JSON documentation](https://duckdb.org/docs/stable/data/json/loading_json) for all available options. ::: ## Parquet ### MotherDuck UI From the UI, follow these steps: 1. Navigate to the **Add Data** section. 2. Select the file. This file will be uploaded into your browser so that it can be queried by DuckDB. 3. Execute the generated query which will create a table for you. 1. Modify the query as needed to suit the correct Database / Schema / Table name. ### DuckDB CLI In the CLI, use the `read_parquet` function to load Parquet files. ### Local file ```sql CREATE TABLE my_table AS SELECT * FROM read_parquet('path/to/local_file.parquet'); ``` ### S3 file Ensure S3 support is enabled as described in the [S3 secrets documentation](/documentation/integrations/cloud-storage/amazon-s3.mdx). ```sql CREATE TABLE my_table AS SELECT * FROM read_parquet('s3://bucket-name/path-to-file.parquet'); ``` ## Handling more complex workflows Production use cases tend to be much more complex and include things like incremental builds & state management. In those scenarios, please take a look at our [ingestion partners](https://motherduck.com/ecosystem/?category=Ingestion), which includes many options including some that offer native python. An overview of the MotherDuck Ecosystem is shown below. ![Diagram](../../../img/md-diagram.svg) --- Source: https://motherduck.com/docs/key-tasks/data-warehousing/replication/spreadsheets # Using Excel and Google Sheets data in MotherDuck > Load Excel and Google Sheets data into MotherDuck using the DuckDB CLI or HTTPS CSV export URLs. Key bits of data and side schedules often exist in spreadsheets like Excel and Google Sheets. It is useful to add that data to your data warehouse and query it. This guide shows how to perform this workflow using the DuckDB CLI for both [Excel](#microsoft-excel) and [Google Sheets](#google-sheets). :::tip To use these extensions, you will need to first install the DuckDB CLI. [Instructions can be found here.](/getting-started/interfaces/connect-query-from-duckdb-cli). ::: ## Microsoft Excel :::note The purpose of this guide is to show you how to _load_ data from Excel into MotherDuck. If you'd like to _retrieve_ MotherDuck data in Excel, you can [follow this guide](/integrations/bi-tools/excel/). ::: To read from an Excel spreadsheet, open the DuckDB CLI by typing `duckdb 'md:'` in your terminal. This will ask you for access to your MotherDuck account if you haven't already provided it. You can read Excel files directly with `SELECT * FROM 'movies.xlsx'`, which will automatically load the DuckDB Excel extension. If you want to get more control you can use [the `read_xlsx` function](https://duckdb.org/docs/stable/core_extensions/excel) directly. ```sql SELECT * FROM read_xlsx('movies.xlsx', sheet = 'Action Movies'); ``` The previous query returns the data set to the terminal, but the query can be modified to write the data into MotherDuck with "Create Table As Select" (CTAS). ```sql CREATE OR REPLACE TABLE my_db.main.my_movies AS -- use fully qualified table name SELECT * FROM 'C:\users\documents\movies.xlsx'; ``` Sometimes there is data in multiple tabs. In that case, you can use the `sheet` parameter to pass the tab names, and depending on the context, even union multiple tabs into a single table. ```sql CREATE OR REPLACE TABLE my_db.main.my_movies AS -- use fully qualified table name SELECT * FROM read_xlsx('C:\users\documents\movies.xlsx', sheet = 'Action Movies') UNION ALL SELECT * FROM read_xlsx('C:\users\documents\movies.xlsx', sheet = 'Romance Movies'); ``` ## Google Sheets ### Query Google Sheets as CSV over HTTPS If a Google Sheet is publicly accessible, or can be accessed with HTTP authentication, query it from MotherDuck with DuckDB's `read_csv()` function and the Google Sheets CSV export URL: ```sql SELECT * FROM read_csv( 'https://docs.google.com/spreadsheets/d//export?format=csv&gid=', MD_RUN = REMOTE ); ``` The `sheet_id` is the value between `/d/` and `/edit` in the Google Sheet URL. The `gid` identifies the worksheet tab. When you run this while connected to MotherDuck, the HTTPS read can execute server side in MotherDuck. To keep the spreadsheet queryable as live source data, create a view: ```sql CREATE OR REPLACE VIEW my_db.main.sheet_source AS SELECT * FROM read_csv( 'https://docs.google.com/spreadsheets/d//export?format=csv&gid=', MD_RUN = REMOTE ); ``` To snapshot the current spreadsheet data into MotherDuck, create a table instead: ```sql CREATE OR REPLACE TABLE my_db.main.sheet_snapshot AS SELECT * FROM read_csv( 'https://docs.google.com/spreadsheets/d//export?format=csv&gid=', MD_RUN = REMOTE ); ``` For private sheets, create an HTTP secret with an OAuth bearer token and scope it to Google Sheets: ```sql CREATE SECRET google_sheets_http IN MOTHERDUCK ( TYPE HTTP, SCOPE 'https://docs.google.com', EXTRA_HTTP_HEADERS MAP { 'Authorization': 'Bearer ' } ); ``` See the [DuckDB HTTP authentication documentation](https://duckdb.org/docs/current/core_extensions/httpfs/https#authenticating) for more `httpfs` authentication options. For more detail on this Google Sheets URL pattern, see [Swimming in Google Sheets with MotherDuck](https://motherduck.com/blog/google-sheets-motherduck/). ### Query with the Google Sheets extension ::::info While the Excel extension is a core DuckDB extension, the Google Sheets extension is a community extension maintained by Evidence. :::: The first step to handle Google Sheets is to install the [duckdb-gsheets](https://duckdb-gsheets.com/) extension. That is done with these commands after starting the DuckDB CLI with `duckdb 'md:'` ```sql INSTALL gsheets FROM community; LOAD gsheets; ``` Since Google Sheets is a hosted application, we need to use [DuckDB Secrets](https://duckdb.org/docs/configuration/secrets_manager.html) to handle authentication. This is as simple as: ```sql CREATE SECRET (TYPE gsheet); ``` :::note Using this workflow will require interactivity with a browser, so if you need to run it from a job (i.e. Airflow or similar), consider setting up a [Google API access token](https://duckdb-gsheets.com/#getting-a-google-api-access-token). ::: To read from a Google Sheet, we need at minimum the sheet id, which is found in the URL, for example `https://docs.google.com/spreadsheets/d/11QdEasMWbETbFVxry-SsD8jVcdYIT1zBQszcF84MdE8/edit`. The string between `d/` and `/edit` represents the spreadsheet id. It can therefore be queried with: ```sql SELECT * FROM read_gsheet('https://docs.google.com/spreadsheets/d/11QdEasMWbETbFVxry-SsD8jVcdYIT1zBQszcF84MdE8/edit'); ``` The previous query returns the data set to the terminal, but the query can be modified to write the data into MotherDuck with "Create Table As Select" (CTAS). ```sql CREATE OR REPLACE TABLE my_db.main.my_table AS -- use fully qualified table name SELECT * FROM read_gsheet('https://docs.google.com/spreadsheets/d/11QdEasMWbETbFVxry-SsD8jVcdYIT1zBQszcF84MdE8/edit'); ``` For convenience, the spreadsheet id itself can be queried as well. ```sql SELECT * FROM read_gsheet('11QdEasMWbETbFVxry-SsD8jVcdYIT1zBQszcF84MdE8'); ``` To query data from multiple tabs, the tab name can be passed as parameter using `sheet` to select the preferred tab. ```sql SELECT * FROM read_gsheet('11QdEasMWbETbFVxry-SsD8jVcdYIT1zBQszcF84MdE8', sheet='Sheet2'); ``` For more detailed documentation, including writing to Google Sheets, review the [duckdb-gsheets documentation](https://duckdb-gsheets.com/#getting-a-google-api-access-token). ## Handling more complex workflows Production use cases tend to be much more complex and include things like incremental builds & state management. In those scenarios, please take a look at our [ingestion partners](https://motherduck.com/ecosystem/?category=Ingestion), which includes many options including some that offer native python. An overview of the MotherDuck Ecosystem is shown below. ![Diagram](../../../img/md-diagram.svg) --- ## 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=%2Fkey-tasks%2Fdata-warehousing%2F&page_title=MotherDuck%20Documentation%20-%20Data%20Warehousing&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.