The DuckDB Handbook

A Comprehensive Guide to In-Memory Analytical Processing

Introduction: Why DuckDB?

1. The Analytical Data Problem
2. Enter DuckDB: A Different Kind of Database
3. When to Use (and Not Use) DuckDB
4. How This Book Is Organized

Chapter 1: Architecture and Internals

1. Vectorized Query Execution Explained
2. The Columnar Storage Engine
3. Query Optimization Pipeline
4. Memory Management Model
5. Concurrency and Threading

Chapter 2: Getting Started with DuckDB

1. Installation Across Platforms
2. The DuckDB Command-Line Interface
3. Your First Analytical Queries
4. Working with Databases and Connections
5. Basic Data Exploration Workflow

Chapter 3: Data Types, Schemas, and Tables

1. Sample Database Setup for Chapters 3 through 7
2. Primitive and Composite Data Types
3. Structs, Lists, Maps, and Unions
4. Creating and Managing Tables
5. Constraints and Integrity
6. Temporary and Transient Tables

Chapter 4: Core SQL Operations

1. SELECT Statements and Projection
2. Filtering with WHERE and Boolean Logic
3. Sorting, Limiting, and Offset
4. DISTINCT and Deduplication Patterns
5. Common Pitfalls and Best Practices

Chapter 5: Joins and Set Operations

1. Inner Joins and Outer Joins
2. Cross Joins and Semi/Anti Joins
3. Join Algorithms and When DuckDB Chooses Each
4. UNION, INTERSECT, EXCEPT
5. Large-Scale Join Strategies

Chapter 6: Aggregations and Window Functions

1. GROUP BY and Aggregate Functions
2. HAVING and Filtering Aggregates
3. Window Functions Syntax and Semantics
4. Analytical Patterns with Windows
5. Performance Considerations for Heavy Aggregation

Chapter 7: Advanced SQL Features

1. Common Table Expressions (CTEs)
2. Subqueries and Correlated Queries
3. Views and Materialized Patterns
4. Macros and Procedural Extensions
5. Transactions and Isolation Levels
6. LATERAL Joins for Row-by-Row Computation
7. PIVOT and UNPIVOT for Data Reshaping
8. QUALIFY Clause for Window Function Filtering
9. SAMPLE Clause for Efficient Data Sampling
10. POSITIONAL Joins for Row-Order Matching
11. Advanced STRUCT Operations

Chapter 8: Data Ingestion and Export

1. CSV Import and Export
2. JSON Processing
3. Parquet Integration
4. Avro, ORC, and Other Formats
5. COPY Statements and Bulk Operations

Chapter 9: The Extensions Ecosystem

1. Extension Architecture and Loading
2. Spatial and Geospatial Extensions
3. HTTP and Web Data Access
4. Apache Iceberg and Delta Lake
5. MotherDuck and Cloud Integration

Chapter 10: Programming APIs and Language Bindings

1. Python API and duckdb Package
2. R Integration via dbplyr and duckdb
3. Java and JDBC Connectivity
4. C/C++ Embedding API
5. Go, Rust, Node.js, and Other Bindings

Chapter 11: DataFrame and Ecosystem Integrations

1. Pandas Integration and Lazy Evaluation
2. Polars and Arrow Interoperability
3. Apache Arrow Flight and Memory Sharing
4. Ibis Integration
5. dbt and Data Transformation Workflows
6. Jupyter, Notebooks, and Interactive Analysis

Chapter 12: Performance Engineering

1. Understanding Vectorized Execution Performance
2. Parallel Query Execution Tuning
3. Storage Layout and Compression Strategies
4. Predicate Pushdown and Projection Pushdown
5. Late Materialization
6. Join Optimization and Hash Tables
7. Memory Limits, Caching, and Spilling
8. Reproducible Benchmark Suite

Chapter 13: Embedding DuckDB in Applications

1. The Embedded Database Paradigm
2. Building Analytics Into Your Application
3. Connection Management and Lifecycle
4. Packaging and Distribution Considerations
5. Real-World Embedding Patterns

Chapter 14: Production Deployment and Operations

1. Security Model and Access Control
2. Configuration and Pragmas Reference
3. Profiling Queries and Debugging Performance
4. Migration Strategies from SQLite and PostgreSQL
5. Enterprise Patterns and Best Practices

Conclusion: The Future of Analytical Databases

1. Where DuckDB Fits in the Modern Stack
2. Emerging Trends and Capabilities
3. Choosing Your Tool: A Decision Framework

References

Index

1. A
2. B
3. C
4. D
5. E
6. F
7. G
8. H
9. I
10. J
11. L
12. M
13. N
14. O
15. P
16. Q
17. R
18. S
19. T
20. U
21. V
22. W
23. Z