DuckDB: What It Is, When To Use It, and How To Try It

What is DuckDB?

DuckDB is a free, open-source analytical (OLAP) SQL database that runs in-process — there is no server to install or connect to. Think of it as "SQLite for analytics": a single dependency you embed in your program (or run from a CLI, or even in the browser) that is built to scan, filter, join and aggregate large tables quickly.

You can try it right now, with nothing to install, in the DuckDB Playground — it runs the official DuckDB WebAssembly build entirely in your browser.

DuckDB vs SQLite: analytical vs transactional

DuckDB and SQLite are both small, embedded, serverless SQL engines, but they are optimized for opposite workloads:

• SQLite is row-oriented and tuned for transactional (OLTP) work — many small reads and writes, like an app's local database.
• DuckDB is column-oriented and tuned for analytical (OLAP) work — scanning and aggregating millions of rows across a few columns.

They are complements, not competitors. If you already know SQLite, see the SQLite guide and SQLite Playground for the transactional side.

The real differentiator: querying files directly

DuckDB's standout feature is that it can query CSV, JSON, Parquet, Arrow and even remote files directly — no import step, no loading into tables first:

-- Query a Parquet file as if it were a table
SELECT * FROM 'events.parquet' LIMIT 10;

-- Read a CSV with automatic type/schema detection
SELECT country, count(*) FROM read_csv('users.csv') GROUP BY country;

-- Scan many files at once with a glob
SELECT * FROM 'logs/2024-*.parquet';

-- Convert CSV to Parquet in one statement
COPY (SELECT * FROM 'users.csv') TO 'users.parquet' (FORMAT PARQUET);

Because Parquet is columnar, DuckDB only reads the columns and row groups a query needs (projection and predicate pushdown), which is what makes this fast. See the Parquet file format guide to understand why.

A first query in Python

DuckDB has a first-class Python API and can query Pandas/Arrow dataframes in place:

import duckdb

# Query a Parquet file directly — no server, no import
duckdb.sql("SELECT count(*) FROM 'events.parquet'").show()

# Query a Pandas DataFrame in place
import pandas as pd
df = pd.read_csv("users.csv")
duckdb.sql("SELECT country, count(*) FROM df GROUP BY country").show()

Try it and go deeper

1. DuckDB Playground — run DuckDB SQL in your browser with sample data and presets.
2. Parquet Viewer — open a .parquet file to preview rows, inspect its schema, query it and export.
3. Parquet file format explained — what columnar storage is and when to use it.
4. SQLite guide — the transactional counterpart.