Skip to main content

DuckDB for Local Analytics

DuckDB is an in-process analytical database. The Python package embeds the engine in your process; it does not require a separate database server. It can query files and in-memory objects with SQL, and it can persist tables in a database file when that is the right boundary.

An execution engine is not a storage format. DuckDB can scan CSV and Parquet, but those files remain CSV and Parquet. A DuckDB database file has its own storage and concurrency semantics.

Prerequisites​

Complete the first SQL batch and Schemas, Types, and Evolution. The example uses DuckDB 1.5.6 from the pinned content environment.

Learning goals​

  • Run an analytical query directly over a local file.
  • Supply a schema instead of making a production query depend on inference.
  • Explain when an in-memory connection, persistent database, or server database is appropriate.
  • Inspect a plan without treating tiny-fixture timing as evidence.

Worked example​

The columns map is the ingestion contract. SUM ignores the missing amount, while the explicit COUNT(*) keeps row count visible.

import duckdb

result = duckdb.sql("""
    SELECT customer_id,
           COUNT(*) AS order_rows,
           SUM(amount) AS known_amount
    FROM read_csv(
        'fixtures/data-engineering/files-tools/orders.csv',
        header = true,
        columns = {
            'order_id': 'BIGINT', 'ordered_at': 'DATE',
            'customer_id': 'BIGINT', 'amount': 'DECIMAL(10,2)',
            'status': 'VARCHAR'
        }
    )
    GROUP BY customer_id
    ORDER BY customer_id
""").fetchall()
print(result)

The deterministic result is [(1, 2, Decimal('100.00')), (2, 2, Decimal('25.00')), (3, 1, Decimal('80.00'))]. This example proves the query and missing-value semantics for the fixture; it says nothing about performance at production scale.

DuckDB can push filters and projections into supported scans such as Parquet. Use EXPLAIN to inspect the plan and profiling on representative workloads to observe execution. Optimizer output can change between versions, so test result invariants rather than snapshotting an entire plan drawing.

Choosing a connection boundary​

Use duckdb.connect(":memory:") for isolated ephemeral work. Use an explicit database path when local tables and metadata must persist across connections. Close connections deterministically, and coordinate writers according to DuckDB's concurrency documentation. A local embedded database is not a drop-in replacement for a multi-host transactional service with remote clients, access control, replication, and high-availability requirements.

Parameterize values rather than interpolating untrusted input into SQL. File paths and object names may require separate allow-listing because identifiers are not ordinary scalar parameters in every statement.

Limitations and common mistakes​

  • Schema inference remains inference even when invoked from SQL; supply types at stable boundaries.
  • An in-process engine shares the process's CPU, memory, file permissions, and failure domain.
  • Parallel scans and pushdown depend on the format, query, metadata, and version.
  • A persistent file needs backup, integrity, upgrade, and writer-coordination policies.
  • Do not extrapolate latency, throughput, or memory use from this five-row fixture.

Practice​

Extend the query with WHERE status = 'shipped', predict which customer groups remain, and use EXPLAIN to locate the filter. Then state what additional data and controls a meaningful performance test would require.

Next steps​

Continue with Polars Lazy DataFrames. Return to SQL Debugging and Query Plans for a grain-first debugging workflow.

Primary sources​

Checked 2026-10-15: DuckDB Python client overview, DuckDB CSV import, DuckDB Parquet scans, DuckDB concurrency, and DuckDB prepared statements.