DuckDB & MotherDuck for Beginners: Your Ultimate Guide

Traditional Database Architecture

Databases typically fall into two categories:

• OLTP (Online Transaction Processing): Databases like PostgreSQL and MySQL, optimized for handling transactions with small datasets. Query times typically range from 1-10 milliseconds.

• OLAP (Online Analytical Processing): Databases like BigQuery and Snowflake, built for analytical queries processing large datasets. Query times can range from 100 milliseconds to several minutes.

Most traditional databases use a client-server architecture where the database runs as a separate process, and applications connect to it through SQL queries.

The In-Process Revolution

In-process databases run directly within the application process itself. While SQLite pioneered this approach for OLTP workloads, DuckDB introduces something new: an in-process OLAP database optimized for analytical workloads.

This design enables DuckDB to:

• Run on virtually any platform (laptops to cloud workflows)
• Integrate seamlessly with any programming language
• Execute in web browsers via WebAssembly
• Eliminate network overhead for local operations

Installation and Basic Usage

DuckDB can be installed through various methods:

• Direct binary download for CLI usage
• Package managers (Homebrew for macOS)
• Language-specific packages (Python, R, Java, etc.)

The CLI provides a powerful interface for data exploration:

Copy code
-- Simple query reading from S3
FROM 's3://bucket/path/to/file.parquet' LIMIT 5;

Key Features in Action

Friendly SQL Dialect: DuckDB extends standard SQL with productivity enhancements, such as the FROM-first syntax shown above.

Automatic File Format Detection: DuckDB automatically detects and handles various file formats including Parquet, CSV, JSON, Iceberg, and Delta Lake.

Extension System: DuckDB's functionality is modular through extensions. Core extensions (like HTTPFS for S3 access) are auto-loaded when needed, while community extensions can be installed manually.

Creating and Managing Databases

Copy code
-- Attach or create a database
ATTACH 'mydatabase.ddb';

-- Create a table from a query
CREATE TABLE mytable AS SELECT * FROM source_data;

The DuckDB File Format

DuckDB's native file format (.ddb or .db) is:

• Self-contained (all tables and metadata in one file)
• ACID-compliant
• Highly compressed
• Optimized for analytical workloads

Exporting Data

DuckDB supports seamless data export:

Copy code
-- Export to CSV
COPY (SELECT * FROM mytable) TO 'output.csv';

Connecting to MotherDuck

Copy code
-- Connect to MotherDuck with one command
ATTACH 'md:';

Authentication requires only a MotherDuck token, which can be set as an environment variable.

Key MotherDuck Features

Dual Execution: MotherDuck enables intelligent query execution, automatically determining whether to run computations locally or in the cloud based on data location and query requirements.

Database Sharing: Create and share cloud databases with simple commands:

Copy code
-- Create a share
CREATE SHARE myshare FROM mydatabase;

Performance Benefits: Leveraging cloud infrastructure provides:

• High-bandwidth connections to cloud storage
• Elimination of local network bottlenecks
• Seamless collaboration features

Cloud Storage Integration

MotherDuck dramatically improves performance when querying cloud storage. In benchmarks, queries that take 11 seconds locally can complete in just 2 seconds when leveraging MotherDuck's cloud infrastructure and network proximity to storage.