Skip to main content

Migrate from Snowflake

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, 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.

Prerequisites
  • A MotherDuck organization, 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, 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?

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?

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?

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?

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 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 and group it into 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.

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:

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 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 for the workload patterns that need optimization.

Translate the Snowflake-specific pieces​

SnowflakeIn MotherDuck
Unquoted identifiers stored uppercaseDuckDB 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_TZDuckDB 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, ARRAYArrive as JSON strings. Cast to JSON, STRUCT, or LIST to query them natively.
Clustering keysDuckDB 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.
Time TravelDatabase snapshots and UNDROP 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 TasksA Flight on a cron schedule, or an external orchestrator for pipelines with cross-system dependencies.
Stored procedures and SnowparkPython in a Flight, which runs any PyPI package next to your data.
Reader accountsShares, 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 SnowflakeDives, React and SQL apps that live in the workspace alongside your data.
GRANT SELECT ON TABLE ... TO ROLECreate a Share with an include pattern for the audience, then GRANT READ ON SHARE ... TO ROLE. See roles and access control.
Marketplace datasets and inbound data sharesNo 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 sizingCompute is per account, not per cluster. Size each service account's Duckling for the job it runs, and use read scaling for concurrent dashboard traffic.

Governance features​

Access in MotherDuck is granted per Share rather than per object, and the controls on what a Share exposes belong to the Share:

  • Table-level security: an include pattern narrows a Share to named tables and views.
  • Row-level security: a secure view's WHERE clause limits the rows consumers see. This replaces Snowflake's row access policies.
  • Column-level security: a secure view that leaves a column out of its SELECT hides that column. This replaces Snowflake's per-column grants.
  • Column masking: a secure view that replaces a column with an expression, such as 'XXX-XX-' || right(ssn, 4) AS ssn, masks its values. This replaces Snowflake's column masking policies. To apply one masking rule across many tables, as a Snowflake masking policy does, define it once as a macro and call it from each secure view; see Reuse a masking rule across tables.

All four are available on the Business and Enterprise plans, including free trials.

The difference to plan for is where a policy lives. A Snowflake policy is attached to the table, so it applies to every query against it. A secure view is attached to a Share, so it applies to consumers of that Share and never to the source database's owner. Like a Snowflake policy, it's evaluated per query: a policy that branches on CURRENT_ROLE() or IS_ROLE_IN_SESSION() translates to is_role_in_session(), and one that keys on CURRENT_USER() translates to current_email() for people, or current_user for service accounts. Don't match people on current_user: MotherDuck usernames are unique only within an organization. MotherDuck has no CURRENT_ROLE() equivalent in a secure view, because a session holds a set of roles rather than one current role, so rewrite a CURRENT_ROLE() = 'X' check as is_role_in_session('X').

Network policies have no MotherDuck counterpart. Environment management describes maintaining a masked copy of the source database, which still suits data that must never leave a separate environment.

warning

Surface this during assessment, not at cutover. Where masking or row access policies back a compliance commitment, map each one to a Share and its secure views, and get that mapping reviewed before the cutover date.

Validate against Snowflake before you cut over​

Run the aggregate comparison and anti-join from 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. 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.
  • Streamlit apps written to sit next to the data become 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.