Skip to main content

Snowflake

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

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

Share an Iceberg catalog​

If your tables are Snowflake-managed Iceberg tables published through an Iceberg REST catalog, such as Snowflake Horizon, both engines can read and write the same tables:

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

CREATE DATABASE my_lakehouse (
TYPE ICEBERG,
"secret" my_catalog_secret,
endpoint '<catalog_endpoint>',
warehouse '<warehouse_identifier>',
default_schema '<schema_name>',
disable_multi_table_commit true,
stage_create_tables false,
skip_create_table_metadata_updates true,
remove_files_on_delete false
);

See the Snowflake tab under Catalog-specific setup in the Apache Iceberg page for more details.

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.

When you insert batch by batch, wrap the batches in one transaction. Outside a transaction, each INSERT commits on its own, so Dives and other readers can see a half-loaded table, and a failed run leaves one behind. Inside a transaction, readers keep seeing the previous table until COMMIT, and a failure rolls back to it:

cursor.execute("SELECT * FROM analytics.public.orders")

md.execute("BEGIN TRANSACTION")
try:
first = True
for batch in cursor.fetch_arrow_batches():
md.register("batch_arrow", batch)
if first:
md.execute("CREATE OR REPLACE TABLE my_db.main.orders AS SELECT * FROM batch_arrow")
first = False
else:
md.execute("INSERT INTO my_db.main.orders SELECT * FROM batch_arrow")
md.unregister("batch_arrow")
md.execute("COMMIT")
except Exception:
md.execute("ROLLBACK")
raise

See Idempotent loads with transactions for multi-step loads.

Load on a schedule with a Flight​

To run the copy above 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.

The Flight is the same code wrapped in an entrypoint, reading its credentials from the environment rather than from literals:

import os

import duckdb
import snowflake.connector


def main():
md = duckdb.connect("md:")
sf = snowflake.connector.connect(
account=os.environ["SNOWFLAKE_ACCOUNT"],
user=os.environ["SNOWFLAKE_USER"],
password=os.environ["SNOWFLAKE_PASSWORD"],
warehouse=os.environ["SNOWFLAKE_WAREHOUSE"],
)

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

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


if __name__ == "__main__":
main()

The Snowflake password is a credential, so keep it in a Flight secret rather than in the Flight's source or config. Each PARAMS key is injected into the run as an environment variable, which is what the code above reads:

CREATE SECRET snowflake_creds IN MOTHERDUCK (
TYPE FLIGHTS,
PARAMS MAP {
'SNOWFLAKE_ACCOUNT': '<account_identifier>',
'SNOWFLAKE_USER': '<user>',
'SNOWFLAKE_PASSWORD': '<password>',
'SNOWFLAKE_WAREHOUSE': '<warehouse>'
}
);

Create the Flight with MD_CREATE_FLIGHT, passing the Python above as source_code, snowflake-connector-python and pyarrow in requirements_txt, flight_secret_names := ['snowflake_creds'], and a schedule_cron for the cadence you want.

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.

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.