Snowflake to Motherduck: 2 Methods to Stream Your Data

Snowflake has become a dominant cloud data warehouse, offering scalability, security, and powerful analytics capabilities. However, with the rise of Motherduck, a serverless, lightweight analytics platform built on DuckDB, many users are exploring ways to stream data from Snowflake to Motherduck for real-time analytics, cost efficiency, and performance improvements.

Although Motherduck provides a SQL-friendly environment, there’s no built-in streaming connector for seamless real-time data transfer from Snowflake. Users must rely on third-party streaming tools or custom-built methods to efficiently extract, transform, and load (ETL) their data.

In this guide, we’ll explore two methods to move your data from Snowflake to Motherduck:

1. Using Estuary Flow – A real-time, fully automated streaming pipeline solution.
2. A SQL-based export & import approach – A batch-based, manual method for one-time or scheduled transfers.

Let’s dive into these methods and determine the best approach for your Snowflake to Motherduck streaming needs.

What is Snowflake?

Snowflake is a cloud-based data warehouse designed for scalability, high availability, and advanced analytics. It allows businesses to store, manage, and process large datasets efficiently using SQL-based queries.

Key Features of Snowflake:

• Cloud-native with separate compute & storage for elasticity
• Supports real-time streaming using Snowpipe for incremental data ingestion
• Pay-as-you-go pricing with dynamic scaling
• Advanced analytics & security for enterprise data processing

While Snowflake is ideal for large-scale data warehousing, its batch-oriented architecture can introduce delays in real-time analytics—or mounting costs to keep a compute warehouse active. This is where Motherduck’s streaming-friendly approach offers an alternative.

What is Motherduck?

Motherduck is a serverless, analytics-first platform built on DuckDB, designed for real-time data processing and lightweight analytics. Unlike Snowflake, which relies on batch processing, Motherduck enables interactive analytics by allowing users to stream data in near real-time.

Key Features of Motherduck:

• Optimized for real-time analytics with DuckDB’s columnar storage
• Supports streaming data for low-latency queries
• Hybrid processing – Works with both local storage and the cloud
• Minimal infrastructure – No complex ETL setup required

Since Motherduck is built for real-time data exploration, many businesses want to stream data from Snowflake to Motherduck, or migrate altogether, for faster, on-demand analytics. However, this requires an efficient streaming pipeline to ensure continuous data synchronization.

Want to Learn Easily? Watch This Video! 

Before diving into the step-by-step guide, check out this quick video to see how you can seamlessly integrate Snowflake with Motherduck using Estuary Flow.

Method 1: Using Estuary Flow to Stream Data from Snowflake to Motherduck

The fastest way to stream data from Snowflake CDC to Motherduck is by using an automated integration platform. Estuary Flow is a no-code SaaS tool that provides a pre-built Snowflake connector and Motherduck connector, handling real-time data streaming without requiring manual intervention.

This means your data pipeline can be up and running in minutes, continuously syncing Snowflake data into Motherduck without complex scripting or maintenance. Change Data Capture (CDC) ensures that new records and updates in Snowflake are automatically reflected in Motherduck in near real-time.

Prerequisites

Before setting up your Snowflake to Motherduck streaming pipeline, make sure you have:

• Estuary Flow Account – Sign up for free if you don’t have one.
• Snowflake Credentials – Account URL, login credentials, warehouse name, and database/schema details.
• AWS Credentials – An S3 bucket and an IAM user with read/write permissions. You'll need the Access Key ID & Secret Key.
• Motherduck Credentials – A Motherduck API token and AWS credentials added to Motherduck (so it can access staged data in S3).
• Target Database – The DuckDB database name in Motherduck where Snowflake data will be streamed.

Step 1: Configure Snowflake as a Source in Estuary Flow

• Log in to Estuary Flow: Go to the Estuary Flow Dashboard (dashboard.estuary.dev) and log in. If you’re new, you can quickly register for a free account—no credit card needed.
  
  
• Add a New Source: In the Estuary UI, navigate to the Sources section (left sidebar).
  
  
• Create a Snowflake Capture: Click on “+ New Capture”. This opens a list of source connectors. Search for “Snowflake” in the connector list.

• Select the Snowflake Connector: You should see the Snowflake Data Cloud connector. Click “Capture” to start configuring it.
  
  
• Enter Snowflake Connection Details: You will be prompted to fill in the required fields for Snowflake:
  
  
  • Name: A unique name for this source.
  • Account URL: Your Snowflake host URL (e.g., abc1234.us-east-2.snowflakecomputing.com).
  • User & Password: Credentials for a Snowflake user. You can see an example script setting up a user and other Snowflake resources here.
  • Warehouse: The Snowflake warehouse used for data extraction.
  • Database & Schema: The specific database and schema from which data will be streamed.
• Authorize and Connect: After filling in the details, click "Next". If successful, click “Save and Publish” to deploy the Snowflake capture. Estuary Flow will now connect to Snowflake and start streaming data into an internal staging area (called “collections”).

Behind the scenes, Estuary Flow efficiently extracts Snowflake data via CDC, manages retries, and ensures incremental data ingestion.

Step 2: Configure Motherduck as the Destination

• Add a New Destination: Once Snowflake is connected and data capture is set up, you can configure Motherduck as the target for that data. Follow the “Materialize collections” prompt after publishing the source, or manually navigate to the Destinations section and click “+ New Materialization”.
  
  
• Select the Motherduck Connector: Search the list of destination connectors for Motherduck. Click on the Motherduck connector’s “Materialization” button to configure it.

• Enter Motherduck Connection Details: You will be prompted to fill in the required fields for Motherduck:

• Name: A name for this destination.
• API Token: Your Motherduck API token (found in your Motherduck account settings).
• Target Database: The DuckDB database name where Snowflake data should be streamed.
• Configure AWS S3 Staging:
  • AWS Access Key ID & Secret Key: Provide the AWS credentials for Estuary to write data to S3.
  • S3 Bucket Name & Region: Specify the S3 bucket and region for staging the Snowflake data.
  • Optional Prefix: If you want to store staged files in a specific folder within the bucket, add a prefix.
• Set Up AWS Credentials in MotherDuck:
  • Run an SQL statement in MotherDuck to provide AWS credentials, allowing MotherDuck to read from the staged S3 bucket.

*This extra AWS step ensures that MotherDuck can access data staged in S3 before ingestion.

• Choose Soft or Hard Deletes: Decide whether you want to:
  • Use soft deletes (mark records as deleted but keep them in the table).
  • Use hard deletes (fully remove deleted records from MotherDuck).
• Authorize and Connect: After filling in the details, click "Next". If successful, click “Save and Publish”. Estuary Flow will begin streaming data from Snowflake to Motherduck in real time.

At this point, Estuary Flow is handling the pipeline: Snowflake → (S3 staging) → Motherduck. Within minutes, your Snowflake tables should appear in Motherduck, continuously updating as new data flows in.

Key Benefits of Using Estuary Flow for Snowflake to Motherduck Streaming

• Real-Time Data Sync: No batch delays—data flows continuously from Snowflake to Motherduck.
• Automated Change Data Capture (CDC): Changes in Snowflake—new records, updates, or deletes—are instantly reflected in Motherduck.
• Hands-Free Maintenance: No manual scripts or cron jobs—schema changes are auto-detected, and the pipeline adapts automatically.
• Scalable & High-Performance: Handles large datasets effortlessly, ensuring efficient real-time data ingestion.
• Secure & Reliable: End-to-end encryption with built-in API rate limit management for stability.

Ready to automate your data pipeline? Get started with Estuary Flow today and experience real-time Snowflake to Motherduck streaming!

Method 2: SQL-Based Export & Import (Manual Approach)

For those who prefer a manual method to transfer data from Snowflake to Motherduck, you can use a SQL-based export and import approach. This method involves extracting data from Snowflake, storing it in a file format, and then loading it into Motherduck. While this is a low-cost option without third-party tools, it requires manual intervention and lacks real-time data streaming.

If you need an automated, real-time, and scalable solution, Estuary Flow is the better option. But for one-time transfers or occasional batch loads, this method works.

Step 1: Export Data from Snowflake

The first step is to extract data from Snowflake and store it in a file format such as CSV or Parquet.

• Connect to Snowflake: Use a SQL client or Snowflake CLI.
• Run the following command to export data to an external stage (e.g., Amazon S3):

plaintext
COPY INTO 's3://your-bucket-name/snowflake-data/'
FROM my_snowflake_table
FILE_FORMAT = (TYPE = CSV, FIELD_OPTIONALLY_ENCLOSED_BY='"')
HEADER = TRUE;

📌 Note:

• Replace 's3://your-bucket-name/snowflake-data/' with your actual S3 bucket.
• Use Parquet instead of CSV for better performance:

plaintext
COPY INTO 's3://your-bucket-name/snowflake-data/'
FROM my_snowflake_table
FILE_FORMAT = (TYPE = PARQUET);

• If you don’t want to use AWS S3, you can export locally using:

plaintext
COPY INTO 'file://local_path/data.csv'
FROM my_snowflake_table
FILE_FORMAT = (TYPE = CSV, HEADER = TRUE);

Step 2: Load Data into Motherduck

Once the data is exported, the next step is to import it into Motherduck.

If you are using a local DuckDB instance connected to Motherduck, you will need to first install and load the httpfs extension:

plaintext
INSTALL httpfs;
LOAD httpfs;

Otherwise, if working directly from the Motherduck dashboard, Motherduck will intelligently load and install extensions for you as needed.

• If using a local file, run the following command in DuckDB:

plaintext
CREATE TABLE my_table AS
SELECT * FROM read_csv_auto('local_path/data.csv');

• If using Amazon S3, run this in Motherduck:

plaintext
CREATE TABLE my_table AS
SELECT * FROM read_parquet('s3://your-bucket-name/snowflake-data/*.parquet');

📌 Important Notes:

• Ensure Motherduck has access to your S3 bucket (similar to Estuary Flow’s method).
• Use read_parquet() instead of read_csv_auto() for better performance.

Key Limitations of SQL-Based Export & Import

• No Real-Time Sync – Requires manual reloading every time new data is available.
• High Maintenance – You’ll need to schedule and manage data exports regularly.
• Risk of Data Loss – If files are deleted or overwritten, you may lose historical data.
• Not Scalable – For frequent or large data transfers, this method becomes inefficient.

Conclusion & Next Steps

Transferring data from Snowflake to Motherduck can be done in two ways:

1. Estuary Flow (Real-Time & Automated Streaming) – The best choice for continuous, low-maintenance data sync. It uses Change Data Capture (CDC) to stream Snowflake data to Motherduck in real-time, eliminating manual work.
2. SQL-Based Export & Import – A manual, batch-based approach that works for one-time transfers but lacks real-time updates and requires ongoing maintenance.

Which Method Should You Choose?

• Use Estuary Flow if you need real-time, automated data movement and want to avoid the hassle of manual exports.
• Use SQL-based exports only if you rarely need to transfer data and are comfortable managing batch jobs manually.

Try Estuary Flow for Free!

If you want a fully automated, no-code solution that keeps your Snowflake data synced with Motherduck in real-time, then Estuary Flow is the clear winner.

📢 Start streaming Snowflake to Motherduck today! Try Estuary Flow for free and set up your pipeline in minutes. Need expert help? Contact us here. 🚀