# Migrate from Snowflake > Plan and run a migration from Snowflake to MotherDuck, from inventorying what you run today to validating and cutting over one workload at a time. This guide covers the Snowflake-specific steps of a migration: inventorying what the account runs, translating Snowflake SQL and features, and validating against Snowflake before you cut over. It follows the process in [Migrate your data warehouse](/key-tasks/data-warehousing/migrating-your-data-warehouse/), which covers sorting the inventory, grouping it into workloads, and moving one workload at a time. Read that page first. For the mechanics of copying a single table, see the [Snowflake integration page](/integrations/databases/snowflake/). :::info Prerequisites - A [MotherDuck organization](/key-tasks/managing-organizations/), with the region and plan already chosen. - A Snowflake role that can read the `SNOWFLAKE.ACCOUNT_USAGE` schema, and a running warehouse. By default only `ACCOUNTADMIN` can read `ACCOUNT_USAGE`, so grant the `OBJECT_VIEWER` and `USAGE_VIEWER` database roles for the catalog and spend history, plus `GOVERNANCE_VIEWER` for usage information and access policies. ::: ## Inventory what Snowflake runs The fastest path is the [MotherDuck migration assessment collector](https://github.com/motherduckdb/md-migration-assessment), a Python CLI that inventories a Snowflake account into a local DuckDB file: catalog, feature usage, workload profile, and spend, with provenance for each number. It runs locally and uploads nothing unless you ask it to. It's in Public Preview, so pin a release rather than tracking `main`. The migration assessment collector runs queries like the following: **What objects exist, and how big are they?** ```sql SELECT table_catalog, table_schema, table_name, table_type, row_count, bytes, last_altered FROM snowflake.account_usage.tables WHERE deleted IS NULL ORDER BY bytes DESC; ``` **Which of them does anyone actually read?** ```sql SELECT accessed.value:objectName::string AS object_name, COUNT(DISTINCT query_id) AS query_count, MAX(query_start_time) AS last_read FROM snowflake.account_usage.access_history, LATERAL FLATTEN(input => base_objects_accessed) AS accessed WHERE query_start_time >= DATEADD(day, -90, CURRENT_TIMESTAMP()) GROUP BY ALL ORDER BY query_count DESC; ``` `ACCESS_HISTORY` needs Enterprise Edition or higher. Without it, fall back to pattern matching table names against `query_text` in `SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY`, which is coarser but still separates live tables from dead ones. **Who and what connects?** ```sql SELECT user_name, role_name, warehouse_name, COUNT(*) AS queries, MAX(start_time) AS last_query FROM snowflake.account_usage.query_history WHERE start_time >= DATEADD(day, -90, CURRENT_TIMESTAMP()) GROUP BY ALL ORDER BY queries DESC; ``` Service accounts show up here next to people. Each one is a connection you'll have to repoint later, so treat this list as your cutover checklist. **What shape is the compute?** ```sql SELECT warehouse_name, DATE_TRUNC('hour', start_time) AS hour, SUM(credits_used) AS credits FROM snowflake.account_usage.warehouse_metering_history WHERE start_time >= DATEADD(day, -30, CURRENT_TIMESTAMP()) GROUP BY ALL ORDER BY hour; ``` A warehouse that burns credits in two spikes a day is a batch pipeline. One that burns them evenly through business hours is serving dashboards. The two need different Duckling sizing on the other side, so it's worth knowing which is which before you size anything. :::note `ACCOUNT_USAGE` views lag live activity by 45 minutes to 3 hours and retain one year of history. Pull them once at the start of the migration and refresh before cutover rather than reading them live. ::: :::warning A query against `INFORMATION_SCHEMA.TABLES` without a running warehouse returns zero tables and no error. If you receive an empty result, check the warehouse before you conclude a database is unused. ::: To automate the catalog side of this, the [Snowflake ingest Flight recipe](/cookbook/flight-snowflake-ingest/) enumerates source tables into an editable MotherDuck control table with a `selected` flag, then copies the ones you flag. With the inventory in hand, [sort it into four buckets](/key-tasks/data-warehousing/migrating-your-data-warehouse/#sort-the-inventory-into-four-buckets) and [group it into workloads](/key-tasks/data-warehousing/migrating-your-data-warehouse/#group-whats-left-into-independent-workloads). ## Three habits to unlearn Snowflake and MotherDuck disagree about what a database *is*, and all three of the mistakes below come from carrying the Snowflake model across unchanged. Keep them in mind when you [set up the infrastructure](/key-tasks/data-warehousing/migrating-your-data-warehouse/#set-up-the-infrastructure-for-migration). **A database belongs to whoever created it, not to the account.** In Snowflake, a database sits in the account and anyone whose role grants access can see it. In MotherDuck it belongs to the user or service account that created it, and the rest of the organization can't see it at all until you share it. A colleague querying an unshared database gets `Catalog Error: schema "x" does not exist`, which reads like a typo rather than a permissions problem. Publish it with a [share](/key-tasks/sharing-data/): ```sql CREATE SHARE IF NOT EXISTS models FROM models (ACCESS RESTRICTED); GRANT READ ON SHARE models TO ROLE explorer; ``` Granting to `explorer` reaches every preset role, because they're concentric. Grant to a [custom role](/concepts/roles-and-access-control/#custom-roles) instead when only one team should see it. **Ownership can't be transferred, so create production objects with a service account from the start.** A database, Dive, or Flight created under a personal account stays with that account. When the person who built that pipeline leaves, the objects are stranded. Service accounts prevent this situation. **Databases are cheap, so make more of them.** Each MotherDuck database takes one writer at a time. In Snowflake you'd absorb write concurrency by sizing a warehouse up or running a multi-cluster one; here you absorb it by splitting across databases. A pipeline fanning 20 concurrent writers at a single database (Dagster, Airflow, and Kinesis consumers all do this) needs a database per pipeline, or writes serialized into one. For the same reason, dev and production dbt targets must never point at the same database: Snowflake's separate databases or warehouses usually prevent the collision for you, and MotherDuck doesn't. :::tip Budget time after the port, not only for it. A direct translation of Snowflake SQL runs on MotherDuck, but it may not run efficiently. See [Tune a migrated Snowflake workload](/key-tasks/data-warehousing/migrating-your-data-warehouse/migrating-from-snowflake/tuning-a-migrated-snowflake-workload/) for the workload patterns that need optimization. ::: ## Translate the Snowflake-specific pieces | Snowflake | In MotherDuck | |---|---| | Unquoted identifiers stored uppercase | DuckDB keeps the case it receives and resolves lookups case-insensitively, so queries keep working. Rename columns during the load if you want lowercase names. | | `NUMBER(38,0)` | Snowflake's default `NUMBER` is `NUMBER(38,0)`, and transpilers carry it across as `DECIMAL(38,0)`, which DuckDB stores as a 128-bit integer: bigger on disk and slower to compute, hash, and join on. Cast integer-like columns to `BIGINT` (or the equivalent `DECIMAL(18,0)`) and keep 38 digits only where the values need them, which is almost nowhere. Go looking for this, it won't announce itself. | | `TIMESTAMP_NTZ`, `TIMESTAMP_LTZ`, `TIMESTAMP_TZ` | DuckDB has two types, `TIMESTAMP` and `TIMESTAMPTZ`, and MotherDuck has no account-wide default timezone. In Snowflake an admin can change the account `TIMEZONE` parameter, which silently moves what `TIMESTAMP_LTZ` columns and `CURRENT_TIMESTAMP` mean for everyone, so a model can be correct against one account setting and wrong against another without its SQL changing. Pin the timezone explicitly in the models themselves rather than inheriting it. | | `VARIANT`, `OBJECT`, `ARRAY` | Arrive as JSON strings. Cast to `JSON`, `STRUCT`, or `LIST` to query them natively. | | Clustering keys | DuckDB has no clustering. Sorting on insert does the same job, because row-group statistics let the scan skip ranges that can't match. See [sort tables by common filter columns](/key-tasks/query-performance/#sort-tables-by-common-filter-columns). | | Time Travel | [Database snapshots](/concepts/snapshots/) and [UNDROP](/concepts/data-recovery/) cover point-in-time restore. Retention is set by plan rather than per query, and only the Business plan can change `snapshot_retention_days`, so check the window you land on matches what your Snowflake Time Travel settings assumed. | | Streams and Tasks | A [Flight](/concepts/flights/) on a cron schedule, or an external orchestrator for pipelines with cross-system dependencies. | | Stored procedures and Snowpark | Python in a [Flight](/key-tasks/flights/), which runs any PyPI package next to your data. | | Reader accounts | [Shares](/key-tasks/sharing-data/), which give read-only access to a database and work for recipients outside your organization. A MotherDuck share reaches MotherDuck users, though, so a recipient who needs to consume the data from Snowflake itself needs a file-based handoff instead. | | Streamlit in Snowflake | [Dives](/key-tasks/dives/), React and SQL apps that live in the workspace alongside your data. | | `GRANT SELECT ON TABLE ... TO ROLE` | Create a Share with an include pattern for the audience, then `GRANT READ ON SHARE ... TO ROLE`. See [roles and access control](/concepts/roles-and-access-control/). | | Marketplace datasets and inbound data shares | No equivalent. Anything you consume through Snowflake's sharing graph has to be re-sourced from the provider directly, or read from a small Snowflake account you keep alive for that feed. Inventory these during assessment: they're nobody's model and so they're easy to miss until something breaks. | | Warehouse sizing | Compute is per account, not per cluster. Size each service account's [Duckling](/about-motherduck/billing/duckling-sizes/) for the job it runs, and use [read scaling](/key-tasks/authenticating-and-connecting-to-motherduck/read-scaling/) for concurrent dashboard traffic. | ### Governance features without an equivalent Four Snowflake governance features have no MotherDuck counterpart: **row access policies**, **column masking policies**, **per-object and per-column grants**, and **network policies**. Access in MotherDuck is granted per Share rather than per object. [Table-level security](/key-tasks/sharing-data/table-level-security/) narrows a Share to named tables and views with an include pattern, on the Business and Enterprise plans, so the usual replacement for a row or column policy is a shape change rather than a setting: publish a Share per audience from a source that already excludes what that audience shouldn't see, or maintain a masked copy of the source database and share that. [Environment management](/key-tasks/data-warehousing/environment-management/) describes the masked-copy pattern for regulated data. Row-level security and column-level permissions on Shares are on our roadmap, so check where they've got to before you design around their absence. :::warning Surface this during assessment, not at cutover. Where masking or row access policies back a compliance commitment, the replacement is a data-modelling change with a review attached, and that's weeks of lead time rather than a configuration change on the day. ::: ## Validate against Snowflake before you cut over Run the aggregate comparison and anti-join from [Validate before you cut over](/key-tasks/data-warehousing/migrating-your-data-warehouse/#validate-before-you-cut-over). Two Snowflake-specific differences cause most mismatches: - Snowflake's `HASH()` and DuckDB's `hash()` are different functions, so hash-based checksums disagree even when the data is identical. - A `NUMBER` to `DECIMAL` mapping that changed scale rounds differently, and a `TIMESTAMP_LTZ` column that depended on the account `TIMEZONE` parameter lands on a different date near midnight. Check both before you go looking for a logic bug. ## Cut over and decommission Follow the [cutover steps](/key-tasks/data-warehousing/migrating-your-data-warehouse/#cut-over-and-decommission). On the Snowflake side: - Freeze the workload by suspending the Tasks and Snowpipes that write to it. - Suspend the warehouse rather than dropping it. Spend stops, and you can resume if something surfaces. - Drop the Snowflake objects only after the parallel window closes and a final `ACCOUNT_USAGE.ACCESS_HISTORY` read shows nothing querying them. ## Rethink what Snowflake shaped Once a workload runs on MotherDuck, some of its shape was dictated by Snowflake rather than by the problem: - PySpark jobs that existed because the transformation didn't fit in SQL often do fit, or fit in a Flight. - A dbt Cloud subscription that only runs scheduled builds can become a [scheduled Flight](/key-tasks/flights/scheduling-and-runs/). - Streamlit apps written to sit next to the data become [Dives](/key-tasks/dives/), which you generate from a prompt rather than hand-build. - Warehouse right-sizing becomes per-account Duckling sizing, so a heavy backfill no longer competes with the dashboards. Hold these until after the workload is validated. Changing the logic and the platform in the same step means a mismatch has two possible causes. ## Related - [Tuning a migrated Snowflake workload](/key-tasks/data-warehousing/migrating-your-data-warehouse/migrating-from-snowflake/tuning-a-migrated-snowflake-workload/) for what to fix once the SQL runs - [Snowflake integration](/integrations/databases/snowflake/) for the table copy mechanics - [Ingest Snowflake tables into MotherDuck from a Flight](/cookbook/flight-snowflake-ingest/) - [Migrate your data warehouse](/key-tasks/data-warehousing/migrating-your-data-warehouse/) for the source-independent process - [Environment management](/key-tasks/data-warehousing/environment-management/) --- ## 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%2Fmigrating-from-snowflake%2F&page_title=Migrate%20from%20Snowflake&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.