What is DuckDB? The Complete Introduction and Use Cases

How DuckDB Compares to Other Tools

The book makes these direct comparisons:

Key Benefits

• Compute Where Data Lives: Access data directly from S3 buckets without expensive transfers. For example, instead of paying AWS Athena to scan compressed JSON log files (charged by data volume), you can deploy DuckDB to an EC2 instance close to the data and query for a fraction of the cost.

• Rapid Experimentation: Run experiments and validate hypotheses quickly and locally using SQL—no infrastructure setup required.

• Extended SQL Dialect: Beyond ANSI SQL, DuckDB adds productivity-boosting extensions:

  • SELECT * EXCLUDE() / REPLACE() — Filter or transform columns from wide tables effortlessly
  • GROUP BY ALL / ORDER BY ALL — Automatically include all relevant columns without typing them out
  • PIVOT / UNPIVOT — Transpose rows and columns with simple syntax
  • STRUCT — Native support for complex nested data structures

Ideal Use Cases

• In-Situ Log Analysis: Analyze log files exactly where they're stored without copying them to new locations.

• Privacy-First Analytics: Process sensitive data—financial statements, medical records, personal information—locally without data ever leaving your system.

• Edge Computing: Efficiently process sensor data on edge devices, such as reporting on power generation and consumption from smart meters.

• Machine Learning Preprocessing: Rapid pre-cleaning of user-generated data for ML training pipelines.

• Data Science Acceleration: Query pandas or Polars DataFrames directly with zero-copy integration. Results can be used as DataFrames without additional memory usage or data transfer. This puts advanced analytics capabilities in the hands of data scientists without involving a data operations group.

• Rapid Prototyping: Test database schemas and complex data models locally before deploying to expensive production data warehouses.

• Transport Optimization: Analyze ride data from modern transport operations for bikes and cars.

Scale Limitations

The data volumes you can process are mostly limited by main memory. While DuckDB supports out-of-memory processing (spilling to disk), this feature targets exceptional situations where the final portion of processing won't fit into memory—not routine processing of massive datasets.

DuckDB focuses on the "long tail" of data analytics use cases. If you're in an enterprise environment with complex data sources, tools, and applications processing many terabytes, DuckDB may not be the right choice.

No Real-Time Streaming

DuckDB does not process live data streams that update continuously. Data updates should happen in bulk by loading new tables or large chunks of data at once. To process streams, you must implement your own mini-batching architecture—setting up a process to create batches from the stream and store them somewhere DuckDB can query.

Edge Computing & Privacy

When analyzing data that shouldn't leave the user's device—health, training, financial, or home automation data—an efficient local infrastructure is essential. Local analytics and preprocessing also reduce the volume of data transported from edge devices like smart meters or sensors.

Cloud Cost Reduction

DuckDB serves as a cheaper alternative to expensive cloud analytics services like BigQuery, Amazon Redshift, AWS Athena, and Snowflake, which charge by processed data volume. A common pattern: replace these services with DuckDB running inside scheduled cloud functions, processing log files directly in storage. You can chain processing functions by writing intermediate results to cloud storage for auditing, helping you reduce cloud data warehouse costs.

Data Science Empowerment

Using DuckDB's state-of-the-art query engine makes data preparation, analysis, filtering, and aggregation more efficient than using pandas—all without leaving the comfortable environment of a Python or R notebook. This puts advanced analytics capabilities in the hands of data scientists working with larger data volumes while reducing complexity and eliminating the need for dedicated data operations support.

Hybrid Cloud-Edge Analysis

A key emerging use case is distributed analysis of data between cloud storage, the edge network, and local devices. MotherDuck enables this by allowing DuckDB to run both in the cloud and locally.

Transient Transformation

Use DuckDB as a temporary processing engine to transform, filter, and pass data through to another format without persisting anything. This is ideal for format conversion (e.g., JSON to Parquet) or data cleaning.

Persistent Analysis

Create tables for your data to persist it for subsequent high-performance analysis. During ingestion, you can transform and correct column names, data types, and values. If your input consists of nested documents, you can unnest and flatten the data to make relational analysis easier and more efficient.

1. Load Data

DuckDB minimizes setup ceremony. Unlike databases like SQL Server, you don't need to specify schema details upfront. When reading data, it uses sensible defaults and inherent schema information, which you can override when needed. Because DuckDB is open source and built by practitioners, there's strong emphasis on usability—if something is too hard to use, someone in the community will propose a fix. And if built-in functionality doesn't reach far enough, there's likely an extension for it (e.g., geospatial data or full-text search).

2. Populate Tables

Determine which SQL capabilities or DuckDB features can help you perform your analysis or transformation. You can also perform exploratory data analysis (EDA) to quickly get an overview of the distribution, ranges, and relationships in your data.

3. Analyze with SQL

Build relevant SQL statements incrementally, verifying at each step that sample results match your expectations. At this stage, you might create additional tables or views before using advanced SQL features like window functions, common table expressions, and pivots.

4. Use Results

Decide how results will be consumed: turning them into files or databases again, serving them through an application or API, or visualizing them in a Jupyter notebook or dashboard.

1.7.1 Data Formats and Sources

DuckDB supports a large number of data formats and sources with little ceremony:

See the Python Quickstart guide to get started with DataFrames.

1.7.2 Data Structures

DuckDB handles a variety of tables, views, and data types. Beyond traditional types (VARCHAR, INTEGER, FLOAT, DECIMAL, DATE, TIMESTAMP, INTERVAL, BOOLEAN, BLOB), it supports complex structured data types:

• Enums — Indexed, named elements of a set that are stored and processed efficiently
• Lists (Arrays) — Hold multiple elements of the same type, with a variety of functions for list operations
• Maps — Efficient key-value pairs for keyed data points, commonly used during JSON processing
• Structs — Consistent key-value structures where the same key always has values of the same data type, enabling more efficient storage and processing

DuckDB also allows you to create custom types and database extensions that provide additional data types. You can create virtual (derived) columns generated from other data via expressions.

1.7.3 Developing the SQL

When analyzing data, start by understanding the shape of your data, then work from simple queries to increasingly complex ones:

1. Inspect Structure: Use DESCRIBE to learn about columns and data types of your data sources, tables, and views.

2. Get Statistics: Use the SUMMARIZE clause to get per-column statistics:

   • count, min, max, avg, std (deviation)
   • approx_unique (estimated count of distinct values)
   • percentiles (q25, q50, q75)
   • null_percentage

3. Build Incrementally: Start with LIMIT or a single input file to prototype. Outline the result columns you need (converting dates with strptime if necessary), then add aggregations and filters.

4. Advanced Aggregations: DuckDB provides many aggregation functions—from traditional (min, avg, sum) to advanced (histogram, bitstring_agg, list, approx_count_distinct) to statistical (percentiles, entropy, regression, skewness).

5. Window Functions: For running totals and comparisons with previous/next rows, use: aggregation OVER (PARTITION BY column ORDER BY column2 [RANGE ...])

6. Organize with CTEs: Extract repeatedly used parts of your analytics statement into named common table expressions (CTEs) or views for readability.

1.7.4 Using or Processing the Results

Once you have results from DuckDB, you have several options:

Serve via APIs

Because DuckDB is so fast, you can serve results directly from queries via an API that web, command-line, or mobile clients consume. You only need a traditional client-server database setup if your source data is too big to move and your results are comparatively small (much less than 1% of the volume). Otherwise, embed DuckDB into your application or dashboarding tool and run it on local raw data or a local DuckDB database.

---