# Data Warehousing Overview > Learn to use MotherDuck as a Data Warehouse ## Introduction to MotherDuck for data warehousing MotherDuck is a serverless cloud data warehouse built on [DuckDB](https://duckdb.org/docs/sql/introduction). Its hypertenancy architecture gives every user, service account, or agent a dedicated compute instance that starts in under a second and bills per second, so your whole team, humans and agents alike, gets sub-second answers without the legacy warehouse bill or cluster tuning. On top of DuckDB's fast analytical engine, MotherDuck adds cloud storage, sharing, and collaboration, and fits the tools data and analytics engineers already use: AI-assisted SQL, dbt for transformations, and hybrid local-cloud processing. ![img_duck_stack](img/bi_tool.svg) MotherDuck integrates with popular data tools including [Estuary](https://docs.estuary.dev/reference/Connectors/materialization-connectors/motherduck/), [Fivetran](https://fivetran.com/docs/destinations/motherduck#motherduck), and [Airbyte](https://docs.airbyte.com/integrations/destinations/motherduck) for data ingestion, [dbt](/integrations/transformation/dbt) and [dbt Cloud](/integrations/transformation/dbt-cloud/) for transformations, [Tableau](/integrations/bi-tools/tableau/), [Power BI](/integrations/bi-tools/powerbi/), and [Looker](/integrations/bi-tools/looker/) for visualization, and [Airflow](https://airflow.apache.org/docs/) and [Dagster](https://docs.dagster.io/integrations/libraries/duckdb/using-duckdb-with-dagster) for orchestration. This enables teams to build data warehousing solutions using their existing tools. ## Data ingestion A quick way to get data into MotherDuck is using [ecosystem partners](/integrations/ingestion/) like [Estuary](https://docs.estuary.dev/reference/Connectors/materialization-connectors/motherduck/), [Fivetran](https://fivetran.com/docs/destinations/motherduck), [dlthub](https://dlthub.com/docs/dlt-ecosystem/destinations/motherduck), and [Airbyte](https://docs.airbyte.com/integrations/destinations/motherduck). For scheduled ingest without external infrastructure, [Flights](/key-tasks/flights/) run Python pipelines natively on MotherDuck: see the guides on [ingesting S3 parquet files on a schedule](/key-tasks/flights/ingest-s3-parquet-files-on-a-schedule/) and [running a dlt ingest pipeline from a Flight](/key-tasks/flights/run-dlt-ingest-pipeline/). MotherDuck is very flexible with how to load your data: - **From data you have on your filesystem:** If you have CSVs, JSON files, or DuckDB databases sitting around, you can load them straight into your MotherDuck data warehouse. - **From a lakehouse on a cloud object store:** MotherDuck works with open table formats. Create and query [DuckLake](/concepts/ducklake) tables backed by your own object storage, or [attach an Iceberg REST catalog](/integrations/file-formats/apache-iceberg/) such as Databricks-managed Iceberg or Cloudflare R2 Data Catalog to read from and write back to Iceberg tables. DuckDB's abstractions for Secrets, Object Storage, and file formats also let you query raw parquet, CSV, and JSON files from object storage with only SQL. Though not as performant as MotherDuck's native storage layer, this lets you query your infrequently-accessed data directly from your data lake. - **Using Native APIs in many languages:** DuckDB supports numerous languages such as C++, Python, and Java, in addition to its own mostly Postgres-compatible SQL dialect. Using these languages, Data Engineers and Developers can integrate with MotherDuck without having to pick up yet-another-language. ### Best practices for programmatic loading The fastest way to load data is to load single tables in large batches, saturating the network connection between MotherDuck and the source data. DuckDB is incredibly good at handling both files and some kinds of in-memory objects, like Arrow dataframes. As an aside, Parquet files compress at 5-10x compared to CSV, which means you can get 5-10x more throughput by using Parquet files. Similarly, open table formats like Delta & Iceberg share those performance gains. On the other hand, small writes on multiple tables will lead to suboptimal performance. While MotherDuck does indeed offer [ACID compliance](https://duckdb.org/2024/09/25/changing-data-with-confidence-and-acid.html), it is not an OLTP system like Postgres! Significantly better performance can be achieved by using queues to batch writes to tables. While some latency is introduced with this methodology, the improvement in throughput should far outweigh the cost of doing small writes. Streaming workloads are better suited to be handled with queues in front of MotherDuck. ## Transforming data Once data is loaded into MotherDuck, it must be transformed into a model that matches the business purpose and needs. This can be done directly in MotherDuck using the powerful library of SQL functions offered by [DuckDB](https://duckdb.org/docs/sql/introduction.html). Many data engineers prefer to use data transformation tools like the open source [dbt Core](https://github.com/dbt-labs/dbt-core). You can connect [dbt Cloud](/integrations/transformation/dbt-cloud/) to MotherDuck through the Postgres endpoint using dbt's built-in Postgres adapter, or [run dbt transformations from a Flight](/key-tasks/flights/run-dbt-transformations-from-a-flight/) to schedule them inside MotherDuck. More details specifically about using dbt with MotherDuck can be read in the [blog on this topic](https://motherduck.com/blog/duckdb-dbt-e2e-data-engineering-project-part-2/). For more in-depth reading, the free **[DuckDB in Action eBook](https://motherduck.com/duckdb-book-brief/)** explores these concepts with real-world examples. ## Sharing data Once your data is loaded into MotherDuck and appropriately transformed for use by your analysts, you can make that data available using MotherDuck's [sharing capabilities](/key-tasks/sharing-data/sharing-overview/). Grant the Share to the Explorer role to make it available to every preset role in your organization. Queries run by Explorers use isolated compute and do not affect the data pipelines managed by Builders or Admins. ## Serving data analytics Do you want to serve reports or dashboards for your users? Connect [popular BI tools](/integrations/bi-tools/) like [Power BI](/integrations/bi-tools/powerbi/), [Tableau Cloud](/integrations/bi-tools/tableau/tableau-cloud/), and [Looker](/integrations/bi-tools/looker/) through the [Postgres endpoint](/getting-started/interfaces/postgres-endpoint/), which works with any client that speaks the PostgreSQL wire protocol. For internal dashboards, [Dives](/key-tasks/dives/) let you build interactive visualizations from natural language with an AI agent and share them with your team, no separate BI tool required. Agents are data consumers too. Connect AI assistants like Claude or Cursor through the [MotherDuck MCP Server](/sql-reference/mcp/), and use [Guides](/key-tasks/guides/), markdown documents with your metric definitions and query conventions, to keep agent answers accurate and consistent. ### Ducks all the way down: building data apps Because DuckDB is an extremely efficient SQL engine inside a ~20MB executable, you can also run it inside the web browser through [WASM](/sql-reference/wasm-client) to build highly interactive data apps and visualizations with near-zero latency. ## Scaling up & out for DWH use cases Furthermore, MotherDuck has a unique scaling model, of which there are four key concepts relevant for Data Warehousing. ### Vertical scaling Compute can scale up with larger DuckDB compute instances called Ducklings. MotherDuck offers 5 sizes: [Pulse, Standard, Jumbo, Mega, and Giga](/about-motherduck/billing/duckling-sizes/). Unlike other data warehouses, every Duckling (compute instance) is isolated from each other: one user's queries will not impact another user's from completing. This [hypertenancy](/concepts/hypertenancy) model assures you can size your warehouse correctly and use your resources very efficiently. To keep compute costs in check, [configure the Duckling cooldown period](/about-motherduck/billing/duckling-sizes/#configuring-the-cooldown-period) per user or service account, and end batch pipelines with [`SHUTDOWN`](/sql-reference/motherduck-sql-reference/shutdown-terminate/) to stop the Duckling without waiting for the cooldown. ### Horizontal scaling For serving data to BI tools or other spiky consumers, [Read Scaling Replicas](/key-tasks/authenticating-and-connecting-to-motherduck/read-scaling/) can absorb the loads and maintain low latency on user interactivity. These should be owned by the same user or service accounts that run production jobs, although they can also leverage [`SHARES`](/key-tasks/sharing-data/sharing-overview/) depending on preferences. ### Hypertenancy Especially for production runs, use separate user accounts or [service accounts](/key-tasks/service-accounts-guide/create-and-configure-service-accounts/) with dedicated compute for updating and maintaining core tables. ### Distributed DuckDB DuckDB and MotherDuck work together as a distributed system that automatically optimizes query execution between local and cloud resources through Dual Execution, enabling efficient data access regardless of location. ## Orchestration To keep data up to date inside MotherDuck, start with [Flights](/key-tasks/flights/): scheduled Python jobs that run natively on MotherDuck, so you can load and transform data on a cron schedule without external infrastructure. You manage Flights through SQL, the UI, or the [MCP Server](/sql-reference/mcp/), which means an AI agent can build and maintain them for you. For more complex data engineering pipelines that span multiple systems, an external orchestrator like [Airflow](https://airflow.apache.org/) or [Dagster](https://dagster.io/) runs jobs in specific orders to load and transform data, as well as managing workflow and observability. If this is your first data warehouse, you might consider starting with [GitHub actions](https://github.com/features/actions) or cron jobs to orchestrate your data pipelines. :::info For a more in-depth guide, check out the [Data Warehousing Guide](/key-tasks/data-warehousing/) ::: ### Try MotherDuck for free Sign up and start building your data warehouse in minutes. No credit card required. [Get Started Free](https://app.motherduck.com/?auth_flow=signup) --- ## 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=%2Fgetting-started%2Fdata-warehouse%2F&page_title=Data%20Warehousing%20Overview&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.