Most people meet DuckDB as a magic one-liner: pip install duckdb, then SQL that crunches hundreds of millions of rows inside the same Python process that would otherwise have churned through pandas loops. There is no server, no socket, no connection pool to tune. The entire analytical engine — SQL parser, planner, optimizer, execution engine, and storage manager — is linked into your process as a compact C++ library. That design choice is why DuckDB feels more like SQLite than like a warehouse, and it is also why reading its source is unusually rewarding: every stage of a query’s life is a few files away.

The repository is organized almost exactly along that life cycle. src/main owns the client-facing objects (instances, connections, result sets). src/parser turns text into a parse tree, src/planner binds names and types into a logical plan, src/optimizer rewrites that plan with roughly fifty rule passes, and src/execution lowers it to physical operators that src/parallel drives across worker threads. src/storage persists row groups into a single file with MVCC semantics. Each directory maps to a clean interface boundary, which is rare for a codebase of this age and ambition.

One structural note before we dive in: this tour anchors on the main branch as of late October 2026, and the repo layout has shifted underfoot in a way worth knowing. The classic tools/pythonpkg directory — where the duckdb.connect() Python binding lived for years — is no longer in the monorepo; the Python client now evolves in its own dedicated repository. What remains in-core is the C API surface under src/main/capi, which is precisely the layer every client binding, Python included, compiles against. So the “Python surface” in this tour is read from the bottom up: whatever your Python code calls, it lands in the same C++ entry points we will trace below.

The query life cycle across DuckDB's six subsystems: from a connection through parsing, binding, optimization, physical lowering, parallel execution, and row-group storage.

Reading the overview from left to right:

• Everything starts with an instance: DuckDB::DuckDB, L404 constructs a DatabaseInstance whose Initialize, L305 installs settings, loads extensions, and prepares the buffer manager.
• The same boot sequence calls StartScheduler, L329, spinning up the worker threads that will later execute your query work units.
• Client code talks to a connection: Connection::Query, L97 is the thin convenience wrapper every binding ultimately funnels into.
• The real work happens in ClientContext::Query, L904, which takes a lock and delegates to QueryInternal, L935 — the single choke point for statement execution.
• Text becomes a parse tree in Parser::ParseQuery, L254, which hands statements to the binder.
• Binder::Bind, L78 dispatches per statement type and produces a bound logical plan; Planner::CreatePlan, L200 owns this handoff.
• Optimization is a separate, ordered pass list driven by Optimizer::Optimize, L539 before the physical plan generator takes over.
• On the storage side, DataTable::Scan, L342 serves versioned row-group reads to the scan operators visible at the far right.

From Install to Your First Model

The promise of an in-process engine lands the moment you skip the ETL step. After pip install duckdb, you can query a CSV directly from disk, aggregate a pandas DataFrame in SQL, or hand results to scikit-learn without a single serialization boundary:

import duckdb

con = duckdb.connect()  # in-memory database, no server anywhere
con.execute("CREATE TABLE clicks AS SELECT * FROM read_csv('events/*.csv')")
top = con.sql("""
    SELECT user_id, count(*) AS n, avg(dwell_ms) AS dwell
    FROM clicks WHERE ts > now() - INTERVAL '7' DAY
    GROUP BY user_id ORDER BY n DESC LIMIT 1000
""").arrow()

That .arrow() return type is the quiet superpower for ML work: feature engineering output goes to Apache Arrow, which pandas, Polars, and most modern training stacks consume zero-copy. If your preprocessing currently lives in pandas, DuckDB can replace the hot loops while keeping the same in-memory objects — the pandas interoperability path scans NumPy-backed columns directly, and the sibling Polars engine follows the same columnar philosophy from the Rust side. For teams that build feature stages over a datasets catalog, treating SQL as the feature-definition language is often the cleanest contract between data engineering and model code.

The operational model is also SQLite-like: the database is a single file (or pure memory), connections are cheap, and PRAGMA threads=N or SET memory_limit='4GB' tune resource use per process. Nothing about the surrounding stack needs to change.

The Query Path: Bind, Optimize, Lower

The detail diagram zooms into what happens between the SQL string and the operator tree, and the code is more readable than you might expect.

Inside one query: session preparation, statement binding, the optimizer's rule passes, the physical operator tree, the event-driven scheduler, and the storage layer that feeds it.

Binding is where SQL gets real. Binder::Bind(SelectStatement &), L8 resolves each table reference into a LogicalGet, each expression into a typed tree, and each join into a logical operator with explicit column bindings. Errors you know from practice — “column X does not exist”, ambiguous references, type mismatches — are thrown here, before any optimization.

The optimizer phase is a sequence of named passes, each a small, testable rewrite. Two representative ones: FilterPushdown::Rewrite, L108 walks the tree pushing predicates toward the data sources so scans can skip row groups entirely, while StatisticsPropagator::PropagateStatistics, L66 pushes cardinality and min/max information upward so join order and aggregation strategy can be chosen with evidence rather than guesses. Because the passes run in a fixed order inside RunBuiltInOptimizers, L186, the system behaves deterministically — and EXPLAIN output maps back onto these rewrites one-to-one.

Lowering is the recursive walk driven by PhysicalPlanGenerator::CreatePlan, L75. Every logical operator gets a physical counterpart: a LogicalGet becomes a scan that emits DataChunks, a hash join becomes a build/probe pair with a shared hash table, and aggregation becomes a radix-partitioned machine that can absorb unbounded key cardinality. The generator also wires the sink/source contract that the scheduler depends on: every operator exposes either a source, a sink, or both, plus state objects created once globally and once per thread.

Prepared statements follow a shorter path on repeat runs: ClientContext::Prepare, L671 binds once and caches the plan, so the second and thousandth executions skip parsing and binding entirely. Embedded clients that want a stable ABI without C++ linkage go through the versioned C API instead — duckdb_v2_connection_create, L5 hands out a handle whose functions mirror the C++ connection almost one-to-one.

Inside the Execution Engine

DuckDB’s execution story is morsel-driven parallelism. Rather than partitioning by operator, the engine decomposes query execution into dependency-ordered work units, and worker threads cooperate on shared operator state. Executor::ScheduleEventsInternal, L83 builds that event graph: scans first, then build sides, then probes and sinks, with events rescheduling successors as they complete. Tasks are dispatched to TaskScheduler::ScheduleTask, L64, whose queues feed the worker pool started at instance boot.

Three data-structure choices make this fast. First, chunks are vectors of roughly 2048 values, so per-row virtual-call overhead is amortized: ExpressionExecutor::Execute, L86 evaluates expression trees over whole vectors, dispatching once per operation rather than once per row. Second, operator state is split into global and local halves — GetGlobalSourceState, L132 and GetLocalSourceState, L127 — so threads touch shared structures rarely and their own state constantly. Third, when work must cross threads (a hash table’s build side feeding many probes, an aggregate’s partitions being combined), it flows through a ColumnDataCollection, the chunked columnar buffer defined in src/common/types/column, which can spill to disk under memory pressure rather than fail.

The hash operators show the payoff concretely. PhysicalHashJoin::Sink, L899 has every thread append its build-side chunk in parallel before a single finalize event flips the table to probe mode, and PhysicalHashAggregate::Sink, L434 does the same for grouped aggregation with radix partitioning to bound contention. Neither operator knows how it was scheduled; the event graph decides, and the same code runs single-threaded on a laptop and wide on a 64-core machine.

Storage: Row Groups, Blocks, Checkpoints

Persistence follows the same chunk-first philosophy. Tables are sequences of row groups (large blocks of rows compressed column-wise), and DataTable::Append, L1116 adds new chunks under transactional versioning: writers allocate row versions, readers filter by snapshot, and neither blocks the other in the common case. Scans like the one feeding PhysicalTableScan::GetDataInternal, L162 iterate row groups, apply zone-map filters from metadata, and decompress only what survives.

Ingest-side table functions are the other half of the story. ReadCSVTableFunction::GetFunction, L195 registers the parallel CSV reader whose sniffer infers types, and the multi-file machinery in MultiFileReader::Create, L40 globs directories, unifies schemas, and lets one read_csv or read_parquet call range over thousands of files. Hive-style partition pruning rides on the same reader, which is why partitioned Parquet trees filter almost for free.

The on-disk format is a single file managed by the block manager: SingleFileBlockManager::WriteHeader, L1333 commits metadata updates by writing a new header with a checksummed version, so crashes leave the previous consistent state intact. Checkpointing is explicit and compaction-oriented — SingleFileCheckpointWriter::CreateCheckpoint, L235 rewrites row groups in place and clears the write-ahead log — and CHECKPOINT can be invoked manually after bulk loads to keep scans dense.

Honest Limits

The in-process design that makes DuckDB pleasant also defines its edges. One process, one writer: multiple processes cannot safely open the same database file for writes, so the “just point two services at it” deployment does not exist; if you need concurrent writers across machines, you are looking at a different class of system. RAM discipline matters too — memory_limit is a real ceiling, and while most operators spill gracefully, adversarial workloads (very high-cardinality grouped aggregates, skewed joins) can still push latency rather than throughput into the failure mode.

For the Python user there is a governance reality worth tracking: the binding lives in its own repository now, which means version skew between the core engine and the client package is possible in a way it was not when both shipped from one tree. The vectorized engine also has a learning curve at its edges — deep customization (new operator types, custom file formats) requires C++ and a rebuild, not a plugin script. And while the optimizer is excellent, it is rule-and-statistics based, not learned: pathological cardinality estimates on uncorrelated predicates remain possible, and EXPLAIN ANALYZE is still the honest arbiter.

None of these are disqualifying for the engine’s sweet spot — local analytics, feature engineering, embedded dashboards, file-format shoveling — and that sweet spot covers a surprising share of what data teams actually do. What the source tour adds is the ability to predict behavior: knowing that filters push down to row groups tells you how to order your columns; knowing that hash builds flip at a finalize event tells you why some joins stream and others materialize. The code rewards exactly this kind of reading.

Enjoyed this post? Never miss out on future posts by following us