- 9 min read
BY- 9 min read
BYThe data architecture field can seem littered with products for all stages of the data lifecycle. This can make it tempting to put off implementing some of the more esoteric aspects of architecture. But one aspect you don’t want to wait on is choosing a solid analytics database.
Compared to a write-optimized transaction processing database (the kind that backs your application to keep things like online ordering quick and scalable), read-optimized analytical processing databases are designed specifically to perform intensive queries. Analytics databases can aggregate and join large tables with ease. And even if you’re not working with massive datasets (yet), it’s always good practice to separate your application database from your analytics database so queries don’t impact your production environment, especially when scaling customer-facing analytics.
One great option for an analytics database is DuckDB. Whether you’re ready to get your feet wet with an analytics database or an old salt hoping to optimize and improve your analytics experience, integrating DuckDB into your data architecture can be a simple process when using MotherDuck and Estuary.
So, let’s take a closer look at each of these components and how they all fit together.
There are lots of options if you’re searching for an analytics database, even if the space isn’t quite as cluttered as that of transaction processing databases. So, what makes DuckDB stand out? Here are some of its top features.
Open Source
The best things in life are open source. Open source projects are easily accessible, allow contributions from a diverse range of collaborators, and let experts evaluate products on best practices, like security. Remixing and expanding on a project’s freely-available underlying code can lead to industry-wide innovation, or simply let you tune a part of your architecture to your exact specifications.
Embedded Analytics
DuckDB is an embedded analytics database, so it can run within a host process, similar to SQLite. Or you can run it as a single binary. This flexibility makes it easy to implement DuckDB wherever you need it.
Fast and Efficient
It’s not a huge surprise that an analytics database like DuckDB would implement a columnar engine instead of a row-based write-optimized format. DuckDB takes this another step by supporting parallel and vectorized execution, speeding up intensive queries even further. When data is vectorized, a batch of values can be processed in one operation, reducing overhead.
Portable and Extensible
DuckDB runs on all major operating systems with drivers offered in a swath of popular programming languages. With a small, no-dependency footprint, you can deploy it directly to IoT or other resource-constrained devices. That’s not to say that DuckDB is limited; extensions provide support for additional functionality, such as file formats for geospatial data or connectivity with data sources like S3.
Once you’ve decided that DuckDB is the right analytics database for your use case, there’s still the matter of maintenance. You can, of course, deploy, scale, and upgrade your own instance of DuckDB. Or you can go with a serverless cloud offering.
MotherDuck is a cloud data warehouse that makes it easy to manage DuckDB instances in the cloud. It also provides features to collaborate with your team, securely save secrets, and intelligently query your data.
Not to mention its accessibility for various connections. Data pipeline platforms like Estuary can integrate directly with MotherDuck-hosted databases, so you can easily wire DuckDB into the rest of your data architecture.
To perform analytics, you first need to transfer your data from your source systems, whether that’s your own transaction database, external APIs, or streaming data. Instead of reinventing the wheel by writing custom integration code (and maintaining that code when source systems change), data pipeline platforms simplify the task of keeping your data connected.
Estuary can handle all kinds of integrations, whether you need real-time sub-second latency or batch analytics and reporting. Using Estuary Flow, you can transfer data from a wide selection of source systems to your MotherDuck instance, aggregating and transforming data along the way as needed. And if those source systems change, intelligent schema evolution keeps your pipeline running.
Estuary leverages CDC, or Change Data Capture, where possible to swiftly materialize reliable, accurate updates when source data changes. CDC lets you track incremental changes as they occur rather than loading data in batches, which would potentially require extra deduplication work and extraneous data transfer costs. It keeps latency low and ensures all update and delete events are preserved, since changes are read directly from the WAL (Write-Ahead Log) or other logs rather than simply capturing the current state of a database.
A simple view of the Estuary architecture is shown below:
Other reasons to love Estuary include:
Instead of just talking about it, let’s try out a demo. We’ll cover how to actually wire up your source systems and MotherDuck with Estuary.
Free plans and trials are available for all resources.
First off: Estuary and MotherDuck make sense as prerequisites if we’re wiring the two together, but why is Amazon Web Services on the list?
For Estuary’s MotherDuck connector, Estuary uses an Amazon S3 bucket to stage data loads, acting as temporary file storage. The S3 bucket will basically be an intermediary step between Estuary and MotherDuck, making use of DuckDB’s S3 extension.
To create an S3 bucket in AWS:
Both Estuary and MotherDuck will need to access this bucket. You can create an IAM user with S3 permissions and then share the user’s credentials with both systems. To do so:
In MotherDuck, we’ll set up access to the S3 bucket and then make sure that Estuary can access MotherDuck in turn.
To provide S3 credentials, you can either run a SQL query or set up access in the UI. For the SQL method, fill out the correct information in the following query and run it from your MotherDuck dashboard:
Copy code
CREATE OR REPLACE SECRET IN MOTHERDUCK (
TYPE S3,
KEY_ID '<AWS-Key-ID>',
SECRET '<AWS-Secret-Key>',
REGION '<AWS-S3-region>'
);
To create a MotherDuck access token for Estuary to use:
Choose an existing database in MotherDuck that you want to materialize into or create a new one. Note its name. We’ll then have all the information we need to wire everything up in Estuary.
Since MotherDuck is a destination connector in Estuary, you’ll first need some source data. While this guide focuses specifically on the MotherDuck connector, you can see how to set up a capture connector here.
Once you have some source data, set up the MotherDuck connector:
Estuary will start streaming your source data into your MotherDuck database.
MotherDuck makes it easy to explore your data, analyze it, and collect your queries in Notebooks. Here are some ways that DuckDB and MotherDuck make working with SQL more fun.
DuckDB offers streamlined syntax, such as FROM-first syntax when you’re selecting all columns.
If you need to read data from individual files for a one-off project where it wouldn’t make sense to set up a whole, continuous pipeline, there are multiple options to read directly from a file using functions like read_csv and read_parquet. For a hands-on guide to these file manipulation mechanics, check out our DuckDB tutorial for beginners.
And if you make a mistake, don’t sweat it. MotherDuck provides a FixIt feature that catches and suggests fixes for common SQL errors so you’re not stuck hunting for a missing comma or misspelled column name.
Explore additional options in the MotherDuck docs.
Once you have the basics down, unlock additional features by checking out Estuary’s and MotherDuck’s resources. At Estuary, discover all the data sources you can load into MotherDuck and how to perform transformations on your data between systems. At MotherDuck, learn how to build apps to visualize your data and manage organizations to collaborate with your team.
And, of course, we’d love to hear from you. Join MotherDuck and Estuary in Slack. We’re excited to hear about your data journey.
2025/02/20 - Adithya Krishnan
Learn how to build an unstructured data pipeline. This guide covers ETL, chunking, and generating vector embeddings directly in MotherDuck using Unstructured.io.
2025/02/26 - Simon Späti
Unlock the power of geospatial analysis with DuckDB Spatial and MotherDuck, making location-based data processing faster, simpler, and more accessible for data engineers.
