Skip to main content

Snowflake

Snowflake is a cloud data warehouse. Move data into MotherDuck with a Python job that copies tables over Arrow, by unloading Parquet to object storage, or by attaching an Iceberg catalog both engines can read.

Snowflake is a cloud data warehouse. There is no first-class DuckDB snowflake extension, so data moves between Snowflake and MotherDuck one of three ways: a Python job that pulls tables over Arrow, a Snowflake unload to object storage that MotherDuck reads back, or an Iceberg catalog that both engines can see.

Pick the route based on how often the data has to move and who owns the schedule.

Copy tables over Arrow​

For a repeatable, code-driven copy, use snowflake-connector-python to fetch a query result as an Arrow table, register it with a DuckDB connection, and write it into MotherDuck:

import duckdb
import snowflake.connector

md = duckdb.connect("md:")
sf = snowflake.connector.connect(
    account="<account_identifier>",
    user="<user>",
    password="<password>",
    warehouse="<warehouse>",
    role="<role>",
)

cursor = sf.cursor()
cursor.execute("SELECT * FROM analytics.public.orders")
orders_arrow = cursor.fetch_arrow_all()

md.register("orders_arrow", orders_arrow)
md.sql("CREATE OR REPLACE TABLE my_db.main.orders AS SELECT * FROM orders_arrow")

fetch_arrow_all() needs pyarrow installed and materializes the whole result in memory, so chunk large tables by a date or ID range, or use fetch_arrow_batches() and insert batch by batch.

To run this on a schedule without managing infrastructure, wrap it in a Flight. The Snowflake ingest Flight recipe does this in two phases: a discover phase that writes an editable inventory of source tables to a MotherDuck control table, and a move phase that copies the tables you flagged. It is built for keeping Snowflake as the source of truth while you build out MotherDuck alongside it.

Unload Parquet to object storage​

For large one-time loads and backfills, let Snowflake write the data out and have MotherDuck read the files. This keeps the transfer off your machine and lets MotherDuck's cloud compute do the reading.

In Snowflake, unload the table to your bucket as Parquet:

COPY INTO 's3://my-bucket/snowflake-unload/orders/'
FROM analytics.public.orders
STORAGE_INTEGRATION = my_s3_integration
FILE_FORMAT = (TYPE = PARQUET)
HEADER = TRUE
MAX_FILE_SIZE = 268435456;

In MotherDuck, store the bucket credentials in a secret and read the files:

CREATE SECRET my_s3_secret IN MOTHERDUCK (
    TYPE S3,
    KEY_ID '<aws_access_key_id>',
    SECRET '<aws_secret_access_key>',
    REGION '<aws_region>'
);

CREATE TABLE orders AS
SELECT * FROM read_parquet('s3://my-bucket/snowflake-unload/orders/*.parquet');

For incremental follow-up loads, unload only the new rows and append them with the watermark patterns in Data loading patterns.

Share an Iceberg catalog​

If your tables are Snowflake-managed Iceberg tables published through an Iceberg REST catalog, such as Snowflake Open Catalog, both engines can read the same tables without copying anything. Attach the catalog as a MotherDuck database:

CREATE SECRET my_catalog_secret IN MOTHERDUCK (
    TYPE ICEBERG,
    CLIENT_ID '<client_id>',
    CLIENT_SECRET '<client_secret>',
    OAUTH2_SERVER_URI '<oauth_token_endpoint>'
);

CREATE DATABASE my_lakehouse (
    TYPE ICEBERG,
    "secret" my_catalog_secret,
    endpoint '<catalog_endpoint>',
    warehouse '<warehouse_identifier>',
    default_schema '<schema_name>'
);

See Apache Iceberg for the full option list, authentication details, and write limitations.

Use an ingestion tool​

Ingestion tools that list Snowflake as a source can load into MotherDuck as a destination: dlt, Sling, Airbyte, and Fivetran. Use one of these when you already run it and want Snowflake to be one source among several.

Things to know​

  • Identifier case. Snowflake stores unquoted identifiers in uppercase, so a Snowflake ORDERS.ORDER_ID arrives as an uppercase column name. DuckDB is case-insensitive on lookup, so queries keep working, but rename columns during the load if you want lowercase names in MotherDuck.
  • Type mapping. Snowflake NUMBER(38,0) maps to a wide DECIMAL, which is slower and larger than a native integer. Cast to BIGINT or INTEGER during the load when the values fit. VARIANT, OBJECT, and ARRAY come through as JSON strings, so cast them to DuckDB JSON, STRUCT, or LIST types to query them natively.
  • Warehouse required. Any read from Snowflake, including INFORMATION_SCHEMA queries, needs an active warehouse. A missing warehouse shows up as "No active warehouse selected", not as a permissions error.
  • Cost of the read. The copy runs on Snowflake compute and shows up on your Snowflake bill. Unloading once to Parquet and re-reading the files from MotherDuck is cheaper than repeatedly querying Snowflake during development.

Related content​