DuckDB in WSL: Embedded Analytics for Local Files and Tests

DuckDB is an embedded analytical database that fits naturally into a WSL development workflow: a command-line process or application can query local files and produce deterministic test outputs without provisioning a separate database server. It is a useful tool for exploring Parquet, CSV, and application-generated datasets. It is not interchangeable with a multi-user warehouse service, and a successful local query does not prove that a production connector, object store, or distributed query engine will behave the same way.

The WSL boundary matters because an embedded database reads and writes files through the Linux process that owns it. Keep the database file, source fixtures, Python environment, and output under the distribution’s Linux filesystem, such as $HOME/project or a dedicated directory under /var/lib for a service. Microsoft recommends the Linux filesystem for Linux command-line workloads because Windows-mounted paths can have different performance and filesystem semantics. Use /mnt/c only when testing cross-OS access deliberately.

Choose between in-memory and persistent files

DuckDB’s Python API can run in memory or connect to a named database file. In-memory sessions are useful for unit tests and short exploration, but the data disappears when the process exits. A file-backed database is appropriate for repeatable local analysis when the application should persist state. Do not create a database file inside a build output directory that is routinely cleaned, or inside a shared synced folder that may observe partially written state.

Start with a project-specific virtual environment and record the DuckDB client version:

mkdir -p "$HOME/duckdb-lab/data" "$HOME/duckdb-lab/output"
cd "$HOME/duckdb-lab"
python -m venv .venv
source .venv/bin/activate
python -m pip install duckdb
python -c 'import duckdb; print(duckdb.__version__)'
df -T "$HOME/duckdb-lab/data"

Use the Python interpreter that belongs to this environment when installing and running the client. A Windows Python package and a WSL Python package are separate installations, even if a terminal makes them appear close together. Check sys.executable and the resolved data path before investigating a query error.

For a repeatable script, explicitly connect to one file and close the connection at the end. Use a stable path derived from the project root rather than an absolute developer-specific location. Keep the database file out of Git unless the project intentionally versions a small fixture. Treat real datasets as input artifacts with their own provenance and retention rules.

Query file formats without hiding the input boundary

DuckDB’s Python client can query data directly through SQL and convenience APIs. The official Python documentation demonstrates reading Parquet and CSV paths. For example, a query over a Parquet fixture can be expressed as:

import duckdb

con = duckdb.connect("data/lab.duckdb")
result = con.execute(
    "SELECT region, count(*) AS rows "
    "FROM read_parquet('data/events.parquet') "
    "GROUP BY region ORDER BY region"
).fetchall()
print(result)
con.close()

The relative paths resolve from the process working directory, so record or set that directory deliberately. To avoid an accidental path mismatch, print Path.cwd() and resolve the fixture before opening it. A notebook launched from a different directory can otherwise query a second empty database file with the same name, producing plausible but misleading results.

For exports, use an explicit destination and format, then read the result back and compare schema, row count, and representative values. Do not overwrite input data in place. In an automated test, write to a unique temporary output directory and remove only that directory after the connection has closed. This is safer than deleting every .duckdb file found below a project tree.

When querying CSV, define expected types and null behavior in a reproducible way rather than relying on inference for every production-shaped dataset. Type inference is convenient for exploration but can vary with input composition. Validate timestamp zones, decimal precision, quoted delimiters, malformed rows, and empty fields using fixtures that represent the actual data contract. For Parquet, inspect the schema and logical types before assuming a timestamp or decimal maps exactly to the application’s expected representation.

Treat file placement and concurrent access deliberately

An embedded database runs within the application’s process and uses local filesystem paths. That simplicity is useful but places responsibility on the application to coordinate access, process lifetime, and backups. Before multiple processes access the same file, review DuckDB’s current concurrency documentation for the exact deployment model and supported read/write coordination. Do not infer that because one Python process and one notebook can each open a database, arbitrary concurrent writers are safe.

Keep active database files on the Linux filesystem. A live database on /mnt/c adds a Windows filesystem boundary and may behave differently under locking, metadata updates, and I/O pressure. If the purpose is a cross-boundary compatibility test, use a disposable database, one process, and explicit checks; do not benchmark it as a native Linux baseline.

Backups should be made using a documented approach that matches the DuckDB version and database usage. Do not copy a database file while a process is writing and claim the copy is consistent. For a disposable analytics lab, the most robust recovery path may be to preserve source fixtures and deterministic SQL so the file can be recreated. For valuable data, test restoration into a separate path before relying on any backup procedure.

Use local analytics as a test stage, not an environment clone

DuckDB can help validate transformations on representative small files before they are submitted to Trino, Spark, or a remote warehouse. Keep those comparisons honest. Differences in SQL dialect, type systems, transaction semantics, file access, extensions, and parallel execution can change results. Run a shared set of assertions on both the local and target engine for critical models rather than assuming local success transfers automatically.

Design tests around stable outcomes: input row count, accepted schema, duplicate-key behavior, null constraints, partition boundaries, and output checksums for immutable fixtures. Record whether a test is in-memory or file-backed, which extensions were loaded, and where input/output paths resolve. For performance work, distinguish startup overhead, cold filesystem cache, warm cache, query planning, and actual scan work.

For workloads where several scripts share one database file, define process ownership in the test plan: identify the initializer, writer, and readers, and sequence them explicitly. A file path is not a network endpoint with service-managed user isolation. If the application needs coordinated concurrent writers or remote clients, compare the embedded design with a server database or analytical service rather than adding ad hoc locks around shared state.

Troubleshoot the right layer

If a query cannot find a file, inspect the process working directory, path case, Linux permissions, and whether the path is inside ext4 or on /mnt/c. If the Python import fails, check that the active interpreter matches the environment where the package was installed. If an extension is missing, use the selected release’s extension documentation and do not download unverified binaries. If the output is unexpectedly empty, check that the query opened the intended persistent file and did not create a fresh database at another working directory.

If memory grows, compare the input size, process RSS, WSL VM limit, and query plan. Do not raise WSL’s global memory cap and DuckDB’s per-query resource settings simultaneously; isolate one change and measure. If the VHDX expands, identify database, source, output, and package-cache usage separately before deleting anything.

Acceptance criteria

Accept the lab when DuckDB and Python versions are recorded, the database and data paths resolve within the intended Linux filesystem, a deterministic query returns the expected rows and types, and an exported result can be read back and validated. Confirm that the process closes cleanly and that any retained file has an explicit backup or rebuild policy.

DuckDB in WSL is an efficient local analytical workbench and test dependency. It is not a substitute for validating production warehouse behavior, distributed execution, multi-user access controls, or independent storage durability.

Related:

Sources: