# Migrate your data warehouse
> Plan a warehouse migration to MotherDuck, from inventorying what you run today to validating and cutting over one workload at a time.
A warehouse migration can be broken down into a series of smaller steps: inventory what the current warehouse runs, split it into workloads that don't depend on each other, then move one workload at a time and validate it before the next. This page covers the parts of that process that are the same whichever warehouse you leave. The guide for your source covers the parts that aren't: how to inventory it, how its SQL and features translate, and what to check before you switch it off.

| Source | Migration guide | Tuning after the port |
|---|---|---|
| Snowflake | [Migrate from Snowflake](/key-tasks/data-warehousing/migrating-your-data-warehouse/migrating-from-snowflake/) | [Tune a migrated Snowflake workload](/key-tasks/data-warehousing/migrating-your-data-warehouse/migrating-from-snowflake/tuning-a-migrated-snowflake-workload/) |
| BigQuery | [Migrate from BigQuery](/key-tasks/data-warehousing/migrating-your-data-warehouse/migrating-from-bigquery/) | [Tune a migrated BigQuery workload](/key-tasks/data-warehousing/migrating-your-data-warehouse/migrating-from-bigquery/tuning-a-migrated-bigquery-workload/) |
| Redshift | [Migrate from Redshift](/key-tasks/data-warehousing/migrating-your-data-warehouse/migrating-from-redshift/) | [Tune a migrated Redshift workload](/key-tasks/data-warehousing/migrating-your-data-warehouse/migrating-from-redshift/tuning-a-migrated-redshift-workload/) |

You'll know a workload is done when its consumers read from MotherDuck, its pipeline writes to MotherDuck on a schedule, and nothing in the old warehouse has to run for it to stay current.

The process, in order:

1. [Decide what to move](#what-to-move).
2. Inventory what the source warehouse runs, using the queries in your source guide.
3. [Sort the inventory into four buckets](#sort-the-inventory-into-four-buckets).
4. [Group what's left into independent workloads](#group-whats-left-into-independent-workloads).
5. [Set up the shared infrastructure](#set-up-the-infrastructure-for-migration).
6. [Move one workload](#move-a-workload), translating the source-specific pieces as you go.
7. [Validate it against the source](#validate-before-you-cut-over).
8. [Cut over and decommission](#cut-over-and-decommission), then go back to step 6 for the next workload.

## What to move

A data platform has a lot of moving parts. You probably don't need every one of the areas below, so treat this as the raw material for your own migration plan rather than a checklist to complete. The areas to account for:

- **Ingestion**: the triggers, connections, and service accounts that get source data into the warehouse in the first place. List the tools that write *directly* to the source warehouse early. Some of them, for example Segment, Stitch, and Braze, have native integrations with your current warehouse but not with MotherDuck. Nearly all of them can write Parquet or JSON to a data lake instead, and MotherDuck reads from the data lake, which removes the need for a direct integration.
- **Transformations and metrics**: the dbt models, stored procedures, scheduled queries, and other compute that turn raw data into something your users or downstream tools consume.
- **Data quality checks**: whatever you monitor for source freshness, accuracy, and completeness.
- **Archiving, retention, and backups**: look for processes that perform backups or expire data for regulatory reasons.
- **Monitoring and alerting**: how the platform tells you about data issues, billing, and usage, wherever you happen to be.
- **Governance**: access control and ownership, for both people and machines. MotherDuck grants access per Share rather than per row or column, so row and column policies need a replacement designed before cutover. Your source guide lists which features this affects.
- **Reverse ETL and signals**: the tools that consume the data once it's modelled, for marketing activation or connected data apps.
- **Data apps**: anything interactive connected to, or hosted on, the platform.

## Sort the inventory into four buckets

Once you've inventoried the source, decide for every object which of these it is.

**What needs to be copied?** When you replicate user data from a Postgres database that's frequently updated but keeps no history, you might be storing that history in the warehouse, often as a slowly changing dimension (SCD) type II. The warehouse is the system of record for it: drop the table and the history goes with it.

**What can be rebuilt from source?** Web analytics data stored per day in your data lake can be read directly from that data lake in MotherDuck. You apply the same transformations and rebuild the full pipeline from the source. That workflow is *idempotent*: running it multiple times, from whichever warehouse, produces the same result each time. Rebuilding is better than copying where you have the choice, because it proves the new pipeline works instead of asking you to trust a one-time transfer.

**What can be queried in place?** Data in S3, GCS, or an Iceberg catalog doesn't need loading at all. MotherDuck can read it where it sits, so you can defer the decision about whether a table is hot enough to be worth storing.

**What can be left behind?** Building a data pipeline generates a lot of development data that can be safely discarded, and there are always tables whose purpose can no longer be identified. Use the source warehouse's query and access history to determine what falls in this category.

Record the answer as a column on your inventory table. Everything after this point works off that column.

## Group what's left into independent workloads

Two sets of models belong to the same workload when anything downstream joins their data together. Until that join exists, they can move on separate days without coordination.

```mermaid
flowchart LR
    S3[("Event files<br/>in S3")]:::db
    PG[("Users in<br/>Postgres")]:::db

    subgraph B["Workload B: two sources, one consumer, moves as one unit"]
        MA["Session models"]
        MB["Account history models"]
        DASH{{"Revenue dashboard"}}:::green
    end

    API[("Weather<br/>API")]:::db

    subgraph A["Workload A: moves on its own"]
        MC["Forecast models"]
        APP{{"Delivery data app"}}:::green
    end

    S3 --> MA
    PG --> MB
    MA --> DASH
    MB --> DASH
    API --> MC
    MC --> APP
```

Shared tables are what make this hard. An `orders` table referenced by nine downstream models pulls all nine into the same workload, which can make the first migration much larger than you wanted. Two ways out: move the shared table first and let both warehouses read their own copy during the overlap, or accept the larger workload and move it as one.

Order the workloads by risk rather than by size. The first one to move should have a small number of consumers, a tolerant audience, and a source you can replay, because it's the one that will teach you what your migration actually costs.

## Set up the infrastructure for migration

Before you migrate individual workloads, get the shared infrastructure in place: everything needed to run the workloads and to provision more of them later. Use an infrastructure-as-code tool, which keeps your environments identical to each other. The [MotherDuck Terraform provider](/integrations/dev-tools/terraform/) manages databases, schemas, shares, roles, secrets, and service accounts as code, and works with OpenTofu too.

To get started you'll need:

- Credentials for provisioning in MotherDuck, and in your object storage (S3, GCS, and so on) where you use it.
- Git repositories, for example for a new dbt project or for Python transformation pipelines.
- Access to an existing ingestion system such as MotherDuck Flights, dltHub, Fivetran, or similar.

Then set up the MotherDuck side, which is shared across every workload:

| Set up | Why it comes first |
|---|---|
| A service account per pipeline stage, each owning one database | Each MotherDuck database has a single write user, and each account gets its own compute. One account per stage isolates both. See [designing your warehouse](/key-tasks/data-warehousing/#designing-your-motherduck-data-warehouse). |
| Dev, staging, and production accounts | Transformations read production data in every phase and write to isolated outputs. [Environment management](/key-tasks/data-warehousing/environment-management/) covers the pattern. |
| Secrets for object storage and source systems, created by the account that uses them | Secrets are scoped to the account that creates them, so one created under your personal account is invisible to the pipeline. |

Three properties of MotherDuck shape this setup, and each source guide explains how they differ from the warehouse you're leaving:

- A database belongs to the user or service account that created it, and stays invisible to the rest of the organization until you [share](/key-tasks/sharing-data/) it.
- Ownership can't be transferred, so create production databases, Dives, and Flights with a service account from the start.
- Each database takes one writer at a time, so concurrent writers need separate databases. Databases are cheap, so make more of them.

## Move a workload

Decide how much you're changing on the way across before you open the first file.

**Lift and shift** moves the logic as-is and changes only what's needed to make it run. It's fast, and validation is unambiguous: the correct result is whatever the old warehouse produced. It also carries every piece of accumulated debt across with it.

**Greenfield** goes back to the stakeholders, re-gathers requirements, and rebuilds. No debt, no dialect translation, but you depend on other people's calendars and you've lost the ability to validate against the old output.

Most migrations end up in between, usually under time pressure. This is the business equivalent of moving house: when you pack something in a box and think "I haven't touched this in five years", you throw it out there and then, rather than believing you'll go through all your belongings one by one once you're settled in the new place. You won't.

So instead of packing everything and moving the boxes, apply a level of cleanup on the way. Delete what the inventory says is unused. Add comments where the SQL made you stop and think, and find the stakeholder who can explain any logic you don't understand. Where you spot a bigger improvement, write a note so you can come back to it after the migration rather than starting it mid-move.

:::tip
Budget time after the port, not only for it. A direct translation of your warehouse's SQL runs on MotherDuck, but it may not run efficiently. Each source has a tuning guide, linked in the table at the top of this page, for the patterns that need optimization.
:::

With that settled, move in dependency order:

1. **Land the raw data.** Point ingestion at MotherDuck, or copy the tables you bucketed as "copy". Keep the old ingestion running in parallel.
2. **Port the transformations.** Repoint the dbt or SQLMesh project at a MotherDuck target and work through the dialect differences in your source guide. Transformations can also [run from a Flight](/key-tasks/flights/run-dbt-transformations-from-a-flight/) if you'd rather not keep a separate runner.
3. **Rebuild the quality checks.** Source freshness tests, row-count assertions, and null checks move with the models they guard. Don't defer them: they're what tells you the next step went wrong.
4. **Repoint the consumers.** Dashboards, reverse ETL jobs, and data apps connect as service accounts with read access. Change them one at a time.

## Validate before you cut over

Run the same aggregate on both sides, per table, and compare:

```sql
SELECT
    COUNT(*) AS row_count,
    COUNT(DISTINCT order_id) AS distinct_keys,
    SUM(amount) AS amount_total,
    MIN(ordered_at) AS first_row,
    MAX(ordered_at) AS last_row
FROM analytics.main.orders;
```

:::warning
Hash-based checksums don't carry across engines. Every warehouse's hash function is different from DuckDB's `hash()`, so the two sides disagree on `SUM(HASH(col))` even when the data is identical. Compare counts, distinct counts, numeric sums, and min/max of keys and timestamps.
:::

Where the aggregates disagree, export the source copy to object storage, load it into a staging table in MotherDuck, and anti-join to find the rows:

```sql
CREATE TABLE staging.orders_source AS
SELECT * FROM read_parquet('s3://<bucket>/validation/orders/*.parquet');

-- Keys the source has that MotherDuck doesn't. Swap the two sides for the reverse.
SELECT order_id
FROM staging.orders_source
EXCEPT
SELECT order_id
FROM marts.main.orders;
```

Three causes account for most mismatches: a timezone difference at a date boundary, a rounding difference from a numeric type mapping, and a load that ran while the source was still changing. Check the source watermark on both sides before you go looking for a logic bug. Your source guide lists the specific type and timezone differences to check.

Also compare at the level your users care about. A dashboard whose numbers match to the cent is more convincing evidence than a table-level row count, and it's the comparison someone will make anyway.

## Cut over and decommission

1. **Freeze the source side of the workload.** Stop the scheduled jobs that write to it so the two warehouses can't diverge while you switch.
2. **Repoint the consumers** and leave the source objects in place, readable.
3. **Run read-only in parallel** for a window you agree in advance, usually one full reporting cycle. This is what catches a month-end model nobody tested.
4. **Stop the source compute** rather than dropping anything. Spend stops, and you can resume if something surfaces.
5. **Record the date you stopped writing.** It's the point any later "the numbers changed" question resolves against.

Drop the source objects only after the parallel window closes and the source's query history shows nothing reading them.

## Related

- [Data warehousing guide](/key-tasks/data-warehousing/)
- [Environment management](/key-tasks/data-warehousing/environment-management/)
- [Data loading patterns](/key-tasks/loading-data-into-motherduck/loading-patterns/)
- [Query performance](/key-tasks/query-performance/)


---

## 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%2Fmigrating-your-data-warehouse%2F&page_title=Migrate%20your%20data%20warehouse&text=<url-encoded user feedback, max 2000 characters>
```

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

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