Skip to content
Search

DuckDB

lastmod 2026-02-19created 2020-12-0717 edits

DuckDB is an embeddable SQL OLAP database management system.

Features

  • SQLite for Analytics
  • Embedded RDBMS optimized for complex analytical queries
  • Dependency-free single binary written in C++11, runs inside the host process
  • Provides Python/R/Java/C/C++/Node.js APIs
  • Stores the DB in a single file, like SQLite
  • Suitable use cases
    • Processing and storing tabular data such as CSV/Parquet
    • Interactive data analysis that joins & aggregates several large tables
    • Making large changes to several large tables at once (adding many rows, or adding/deleting/changing columns, etc.)

One of 7 databases for 2025

  • See Database#7 databases for 2025 (GeekNews)
  • DuckDB is an embedded database specialized for OLAP
    • Runs alongside the application like SQLite, but focuses on OLAP workloads instead of OLTP
    • A system designed around data analysis and queries
  • DuckDB's "Query-Anything" nature
    • Can query various data sources directly with SQL:
      • Common file formats such as CSV, TSV, JSON
      • Supports advanced file formats such as Parquet
    • This provides flexibility, e.g. analyzing Bluesky's data stream
  • Extensibility and ecosystem
    • DuckDB also has extensions, but not as many as Postgres (a relatively young project)
    • There are many community-contributed extensions; gsheets (Google Sheets integration) is notable
  • Recommended to learn by using DuckDB
    • Experiment with data analysis and processing through Python notebooks or Evidence
    • Combine with SQLite: delegate analytical queries on an SQLite database to DuckDB for better performance

Data science with DuckDB: taming complex file environments

In Alex Monahan's talk video, he explains how DuckDB, an open-source analytical database library, can help data scientists manage and analyze diverse, messy data files.

  • The file zoo problem: Data scientists deal with countless files in various formats such as CSV, Parquet, and spreadsheets, which may be scattered across multiple locations and cloud platforms. These files are large, numerous, messy, or prone to corruption.
  • DuckDB overview: DuckDB is introduced as "SQLite for analytics." It is open source (MIT license) and an in-process (embedded) database designed for analytical workloads. It has no dependencies, installs easily with pip install duckdb, and can be used directly within Python scripts or notebooks.
  • Reading various files: DuckDB can read a wide range of file formats, including files in cloud storage such as S3. Its especially powerful CSV reader excels at handling real-world messy CSV files, outperforming many other tools in its ability to successfully parse problematic files.
  • Friendly SQL and relational API: DuckDB provides a user-friendly SQL interface (e.g. SELECT * is optional) and a Python-style relational API. You can query files directly as if they were tables, and it works lazily, reading data only when needed.
  • Interoperability: It integrates smoothly with popular data science libraries such as Pandas and Polars, and because it runs in the same process, zero-copy data exchange is possible.
  • DuckDB file format: The native DuckDB file format is a single columnar compressed file that can store multiple tables, views, SQL functions, and relationships. The format is editable, supports ACID properties, and is designed for speed and efficiency.
  • ACID properties: DuckDB brings database robustness to data science workflows through atomicity (all-or-nothing changes), consistency (using keys to prevent data quality problems), isolation (concurrent queries do not interfere with each other), and durability (committed data is safe from corruption).
  • Use cases: Useful for taming the "file zoo" by consolidating data into a single, efficient, queryable format. It also helps prevent new file zoos from being created during iterative data science work.
  • Community and extensibility: DuckDB is extensible, and community contributions are adding support for formats such as statistical package files and Google Sheets.

Alex Monahan, who works at DuckDB Labs and MotherDuck (a cloud data warehouse based on DuckDB), emphasized that DuckDB aims to simplify data processing for data scientists by combining the power of a database with the flexibility data science needs.

See also

  • SQLite
  • DuckDB
  • Realm
  • big data
  • OLAP
  • Lance - Modern columnar data format optimized for ML workflows
  • Substrait - Cross-platform interchange standard for data processing operations
  • Shaper - DuckDB-based alternative to Metabase that lets you visualize and share data with SQL

Favorite site

Articles

  • DuckDB Local UI released | GeekNews
  • "Instant SQL" released for DuckDB, showing results instantly as you type SQL | GeekNews
    • [Original] Instant SQL is here: Speedrun ad-hoc queries as you type - MotherDuck Blog
    • Instant SQL is a tool that previews results in real time as you write SQL queries, speeding up query writing and debugging
    • Lets you easily break down and analyze complex CTEs or column expressions, and instantly preview AI-based edit suggestions, making query writing and editing more efficient
    • Available in MotherDuck and DuckDB Local UI, and can explore various external data sources in real time on top of DuckDB
    • Made possible by a combination of advanced techniques such as DuckDB's local-first design and performance optimizations, caching strategies, and AST-based cursor mapping
    • MotherDuck is a managed DuckDB cloud service that provides various tools for data analysis
  • DuckDB has emerged as the most important geospatial software of the past decade | GeekNews
    • DuckDB's geospatial extension dramatically lowers the barrier to accessing geospatial data with a simple SQL-based interface
    • As a result, even ordinary data analysts can do geospatial analysis with two lines of code
    • Search interest in 'geospatial' has surged since late 2023, coinciding with the release of DuckDB's extension
    • The spread of modern geospatial projects such as Overture Maps is also seen as reflecting DuckDB's influence
    • The DuckDB team resolved complex dependencies and GIS tool integration internally, simplifying the user experience
  • Why DuckDB is my first choice for data processing | GeekNews
    • [Original] Why DuckDB is my first choice for data processing
    • DuckDB is an open-source SQL engine that can process large tabular data quickly and simply on a single machine, and is now widely used in data engineering
    • Simple to install, dependency-free, and runs immediately in a Python environment, making it well suited to CI and test automation
    • Analytical query optimizations make it up to 1,000x faster than SQLite or Postgres, and it can query various file formats (csv, parquet, json) directly
    • Friendly SQL syntax (EXCLUDE, COLUMNS, QUALIFY, function chaining, etc.) and a Python API make it possible to develop complex pipelines efficiently
    • With ACID compliance, high-performance UDFs, and a PostgreSQL integration extension, it is emerging as a lakehouse alternative for medium-sized data
author Zer0 sitemap