Offloading BigQuery Workloads to MotherDuck for Faster and Affordable Analytics

The Business Problem

BigQuery Is Powerful but Costly—Enter MotherDuck. 

Google BigQuery is great for analyzing massive datasets but isn't inexpensive, especially with large volumes of data. That's where MotherDuck, a more affordable alternative that uses the DuckDB engine, enters the picture. It's serverless, fast, and stores data cost-effectively on S3, making it ideal for teams looking to cut costs without compromising on performance. 

In this guide, you'll learn how to move data from BigQuery to MotherDuck using Estuary for real-time, cost-effective analytics. Set up a no-code CDC pipeline to reduce BigQuery costs and accelerate query performance in minutes. 

Estuary: The Perfect Connector between BigQuery and MotherDuck. 

Estuary fills the gap between MotherDuck and BigQuery, streaming data in real-time using Change Data Capture (CDC) mechanism. It only moves changes, which saves costs and speeds up the process. It is easy to implement—just enter your credentials, choose your dataset, and Estuary does the rest. It supports over 150 connectors and enables real-time ETL/ELT pipelines. 

Cost and Performance Benefits of Estuary 

Estuary is cost-effective, offering a generous free tier. It's also less expensive than alternatives like Fivetran or Airbyte due to its scalable volume-based pricing. It reduces pipeline redundancy with many-to-many dataflows and latency via real-time streaming. Its fault tolerance, combined with the ability to execute merge queries in the destination, ensures no duplicates and zero data loss.  

Why Estuary Stands Out?

Estuary fills the gap between MotherDuck and BigQuery, delivering speed, affordability, and simplicity. With Estuary’s cloud solution, it’s fast, affordable, and simple to use—up to 60% cheaper than alternative solutions. Learn more through real-world success stories. 

Want to learn more? Check out Estuary's documentation on real-time data pipelines and CDC features. 

Prerequisites

• An Estuary account: register here for free 
• A MotherDuck account 
• A BigQuery Service Account  

Step-by-Step: Connect BigQuery to Motherduck via Estuary 

First, we will connect BigQuery as our data source using one of its public datasets—specifically, the New York Taxi Trips data from 2019. Here’s how to get started: 

Step 1: Connect BigQuery as a Source 

First, set up your Google Cloud environment so Estuary can access BigQuery. You’ll create a bucket, a service account, and load sample data. 

Set Up a Google Cloud Bucket 

Head over to your account's Google Cloud Platform (GCP) console. 

To ensure seamless data transfers and avoid regional latency issues, create a new Google Cloud bucket in the same region as your BigQuery destination dataset. 

Create a Service Account for Estuary 

In GCP, create a service account specifically for Estuary. 

Assign it the following roles:  

• BigQuery Data Editor 
• BigQuery Job User 
• BigQuery Read Session User 
• BigQuery Object Admin 

Once the account is created, go to the “Keys” section, generate a new key, and download the JSON file. Keep this handy—you’ll need it later! 

Set Up Your BigQuery Dataset 

Navigate to BigQuery in the GCP console. 

Create a dataset called new_york_taxi. 

Click “+Add” and select “Public Database” to tap into BigQuery’s public datasets. 

Find the bigquery-public-data.new_york_taxi_trips.tlc_yellow_trips_2019 dataset and copy it into your own table with this query: 

plaintext
CREATE TABLE `new_york_taxi.data_taxi` AS  
SELECT * FROM `bigquery-public-data.new_york_taxi_trips.tlc_yellow_trips_2019`  

Once the table’s created, you’ve got your sample data ready to roll! 

Step 2: Set up BigQuery in Estuary 

Log in to your Estuary dashboard. 

Go to the “Sources” section, search for “BigQuery,” and click “Capture.”  

Fill in these details:  

• Name: Something unique like Estuary_Source 
• Project ID: Your BigQuery project ID (e.g., analog-bot-453520-c4) 
• Service Account JSON: Upload that JSON key file you saved earlier 
• Dataset: Enter new_york_taxi 

Estuary will automatically discover the schema. Review it under the "Collection" tab to ensure its correct, then click "Test" and proceed by selecting "Save and Publish." 

Step 3: Connecting MotherDuck as the Destination 

Next, let’s set up MotherDuck as our destination. MotherDuck is a lightweight, cloud-native database that pairs beautifully with Estuary for fast, cost-effective data processing. 

Create a MotherDuck Account 

If you don’t already have a MotherDuck account, sign up at MotherDuck. 

Head to “Settings” in the left navigation bar, look for“Access Token.” 

Generate a token for Estuary, copy it, and store it somewhere secure. 

Set Up an AWS S3 Bucket 

Log in to your AWS Console. 

Create a new S3 bucket to stage data between Estuary and MotherDuck. 

Create an IAM Role 

For security, create a dedicated IAM user in AWS with read/write access to your S3 bucket. 

Attach this policy (replace <bucket_name> with your bucket’s name)  

javascript
{ 
    "Version": "2012-10-17", 
    "Statement": [ 
        { 
            "Sid": "AllowSpecificS3Actions", 
            "Effect": "Allow", 
            "Action": [ 
                "s3:ListBucket", 
                "s3:GetObject", 
                "s3:PutObject", 
                "s3:DeleteObject" 
            ], 
            "Resource": [ 
                "arn:aws:s3:::<bucket_name>", 
                "arn:aws:s3:::<bucket_name>/*" 
            ] 
        } 
    ] 
}  

Generate an access key and secret key for this user, then save them as a .csv file. 

Configure MotherDuck as the Destination in Estuary 

Navigate to “Destinations” in the Estuary dashboard, search for “MotherDuck,” and click “Materialization.”  

Fill out these fields:  

• MotherDuck Service Token: Paste the token you generated 
• Database: Name it something like nyc_taxi_analytics 
• Database Schema: Use main (the default) 
• S3 Staging Bucket: Your S3 bucket name 
• Access Key ID: From your AWS .csv 
• Secret Access Key: From your AWS .csv 
• S3 Bucket Region: e.g., us-east-1 

In the “Source Collection” section, select “Source From Capture,” and select the collections you want to materialize into MotherDuck. Once those are added to the Materialization, Press Save and Publish to initiate the connector. 

MotherDuck is now ready to receive your data! The connector will first start with a full backfill to load all existing data into the database, then switch to an incremental mode, only loading changes. 

Step 4: Sending Data from BigQuery to MotherDuck with Estuary 

With BigQuery as the source and MotherDuck as the destination, it’s time to stream that data through Estuary Flow. 

Verify the Setup 

Estuary has already captured your BigQuery data and materialized it into MotherDuck. The schema has been automatically inferred, and collections are now organized as JSON documents in real-time data lakes. 

Stream the Data 

Once you hit “Save and Publish” in both the source and destination setups, Estuary starts streaming the New York taxi data from BigQuery to MotherDuck automatically. 

Check the Data in MotherDuck 

Open your MotherDuck dashboard and run this query to confirm the data’s there: 
 

If you see rows of taxi trip data, congrats—you’ve got a live data pipeline! 

Estuary’s Change Data Capture (CDC) keeps this process running, ensuring updates flow from source to destination. 

Why Estuary CDC is key to low-cost and faster analytics in MotherDuck.  

BigQuery's pricing model charges based on data scanned, which can get expensive, especially with large datasets. Whereas in MotherDuck, with $50, one could load a massive 100GB dataset and query it instantly and derive insights. For a much smaller dataset, MotherDuck offers a free plan, and no credit card is required. 

Estuary supports over 5500 users with less than 100 ms latency, 7+ gb/s throughput for a single dataflow, and 99.9% uptime. The platform offers three deployment options. 

• Public Deployment: Fully managed SaaS solution for small to medium businesses 
• Bring Your Own Cloud: Deploy within your existing cloud infrastructure while maintaining control 
• Private Deployment: Run the platform in your private cloud with strict security while retaining SaaS simplicity 

What’s Next? 

Sign up for Estuary Flow here and start streaming data from almost all popular sources to almost all popular destinations. 

Curious about other source systems? See how to sync Snowflake to MotherDuck in real-time.