How to Use DuckDB: The Beginner's Guide (Install, First Query, CSV Files, No Server)

CodexFuse Team · 2026-10-31T14:25:38.501Z · 9 min read

Learn how to use DuckDB in this beginner guide: pip install, your first query, querying CSV files directly with no server, the in-memory vs file rule, Parquet export, and the pandas handoff.

How to Use DuckDB: The Beginner's Guide (Install, First Query, CSV Files, No Server)

Learning how to use DuckDB is the fastest way to run real SQL analytics on the files sitting on your laptop — no database server, no Docker container, no cloud account. DuckDB is an in-process, open-source analytical database: it runs inside your own Python process and queries CSV and Parquet files directly, with no loading step. This DuckDB tutorial takes you from pip install duckdb to your first query, then through the no-server rule, querying a CSV directly, the in-memory-vs-file trap every beginner hits, saving results as Parquet, and handing them back to pandas. It is the analytical sibling of SQLite — if you have read the SQLite beginner guide, this is the OLAP half of the same idea. Current version: DuckDB 2.0.0, released 2026-10-21 (verified on GitHub's releases API on 2026-10-31).

What is DuckDB?

DuckDB is an in-process, columnar, analytical (OLAP) SQL database that runs inside your application instead of on a server. There is no server process to start, no configuration file, no user accounts — you import a library, and your program itself is the database. It reads CSV, Parquet, and JSON files directly off disk (or over HTTPS and S3-style storage with an extension), stores data in a compact columnar format, and executes queries with a vectorized engine built for scanning and aggregating big tables rather than updating single rows. It is open source under the MIT license, and its creators (Hannes Muehleisen and Mark Raasveldt) first released it in 2019 as, essentially, "SQLite for analytics."

Two ideas carry the whole product. The first is in-process: the database lives inside your Python process, like SQLite, so starting a session is one line of code and zero infrastructure. The second is analytical: its storage and execution are optimized for the queries data people actually run — SELECT product, SUM(revenue) FROM huge_table GROUP BY product over millions of rows — rather than the quick single-row writes SQLite is tuned for. Everything else in this guide flows from those two facts.

How do you install DuckDB?

You install DuckDB with one command and verify it with one more — there is no server to start and nothing to configure. These duckdb install steps work on macOS, Linux, and Windows — the pip package ships prebuilt wheels for all three.

pip install duckdb
python -c "import duckdb; print(duckdb.__version__)"

If the second command prints 2.0.0 (or newer), you are ready. There is also a standalone CLI — download the binary, run it with no installation step — and a Homebrew path on macOS, but the Python package is the fastest route to your first query, and this guide uses it throughout.

How do you run your first DuckDB query?

Your duckdb first query needs exactly three lines of Python. The seven steps below build from that to a working mental model of how DuckDB sessions behave.

1. Open a Python session. A plain python3 prompt or a Jupyter notebook both work — nothing else needs to be running.
2. Import DuckDB.

   import duckdb

   One import, zero setup. The database engine is now loaded inside your process.
3. Run SQL directly.

   duckdb.sql("SELECT 'Hello, DuckDB!' AS greeting").fetchall()

   duckdb.sql() runs against a default in-memory database and returns a result — no connection object needed for quick queries.
4. Try real math.

   duckdb.sql("SELECT 42 * 2 AS answer, current_date AS today").fetchall()

   If it returns [(84, <today's date>)], your DuckDB install is fully working.
5. Open an explicit connection.

   con = duckdb.connect()

   A connection you name is where state lives — tables you create, settings you change. duckdb.connect() with no argument gives you an in-memory database.
6. Learn the in-memory rule now. In-memory data dies when the connection closes or the process exits — a scratchpad, perfect for experiments, useless for anything you want to keep. Persistence means passing a file path, which the next section covers.
7. Close cleanly.

   con.close()

   In-memory databases vanish on close; file databases flush and survive. That is the whole session model: import, connect, query, close.

How do you make a DuckDB database persist?

Pass a file path to connect() and DuckDB saves everything to that file; connect with no argument and everything lives in memory and dies on close.

con = duckdb.connect('analysis.duckdb')  # persistent — saved to disk
con_mem = duckdb.connect()                # in-memory — gone when closed

The number-one beginner trap is building a whole afternoon of work in an in-memory session, closing the notebook, and finding it all gone. The rule is simple: if the result should outlive the session, the connection must name a file. The file can use any extension, but .duckdb (or .db) is the convention, and if the file does not exist yet, DuckDB creates it. Tables created in a file session stay real tables:

con.execute("CREATE TABLE sales AS SELECT * FROM 'sales.csv'")
# later — even tomorrow, in a new process:
con2 = duckdb.connect('analysis.duckdb')
con2.execute("SELECT COUNT(*) FROM sales").fetchone()

Reopenable tomorrow, copyable to a server, queryable forever. That single file is the entire database — which is also why moving an analysis to a server is just copying one file.

How do you query a CSV file directly in DuckDB?

You point the FROM clause at the file path — no LOAD, no import, no staging table. This duckdb query csv pattern is the heart of the guide, and it is why there is no loading step at all.

import duckdb
result = duckdb.query("""
    SELECT product_name, SUM(sales_amount) AS total_sales
    FROM 'sales_data.csv'
    GROUP BY product_name
    ORDER BY total_sales DESC
    LIMIT 10
""").df()

Behind that line, DuckDB calls read_csv_auto(): it assumes the first row is a header and sniffs column types from a sample of the file. This is the direct-file-query angle that makes DuckDB feel different from every other database — the file is the table. When a file is messy — odd delimiters, no header, mixed types — call the function explicitly and give it instructions:

SELECT * FROM read_csv_auto('data_no_header.csv', header=false, delim=';', sample_size=-1)

sample_size=-1 scans the whole file for type detection instead of a sample, which fixes most "type mismatch" surprises at the cost of a slower first read. If your source data starts life in a spreadsheet, convert it first with an Excel to JSON pass — DuckDB also reads JSON directly with read_json_auto().

How do you use DuckDB with pandas?

You stop choosing between SQL and pandas — DuckDB queries your DataFrames directly, and pandas consumes the results.

import duckdb, pandas as pd

df = pd.DataFrame({'name': ['Ava', 'Ben', 'Cara'], 'score': [92, 78, 85]})

# SQL straight on the DataFrame — no loading, no registration
duckdb.sql("SELECT name FROM df WHERE score > 80").fetchall()

# and back to pandas when you need it
result = duckdb.sql("SELECT AVG(score) AS mean_score FROM df").df()

There is no copy here: DuckDB sees the DataFrame's variables as tables through what the docs call replacement scans. When you do want an explicit table, con.register('scores', df) pins it under a name for the session. This two-way handoff is the real beginner workflow — pandas for wrangling and plotting, DuckDB for the heavy analytical query in the middle.

DuckDB vs SQLite: which one should you learn?

Learn both — they are built for opposite jobs, and the choice is the workload, not the winner.

This table is original to this guide — the standard framing comes from the DuckDB and SQLite docs, summarized here in one place. If the SQLite column sounds like your problem, start with the SQLite beginner guide; if the DuckDB column does, stay here. Plenty of real pipelines use both: SQLite as the app's database, DuckDB as the analyst's query engine.

How do you save DuckDB results as Parquet?

One COPY statement converts any query result into a Parquet file — the standard compact columnar format for big data.

COPY (SELECT * FROM 'sales_data.csv') TO 'sales.parquet' (FORMAT 'PARQUET');

Reading it back is the same FROM-a-file trick, explicitly or by inference:

SELECT * FROM read_parquet('sales.parquet');
SELECT region, SUM(total_sales) FROM 'sales.parquet' GROUP BY region;

Parquet is where DuckDB shines: columnar files on disk match its columnar engine, so aggregations over large Parquet files stay fast without loading anything into memory. Globs work too — FROM 'data/*.parquet' auto-unions every matching file into one virtual table. For the data-format ecosystem around this workflow, the online XML editor is the companion tool for structured config and feed data on this site.

Can DuckDB query files on the internet?

Yes — with one extension installed, DuckDB reads over HTTPS and from S3-style storage.

INSTALL httpfs;
LOAD httpfs;

SELECT Country, Population
FROM read_csv_auto('https://example.com/population.csv');

INSTALL downloads the extension, LOAD activates it for the session. The same machinery reads from S3 buckets and globbed remote paths. Fair warning: INSTALL needs network access, and on locked-down machines extensions may be blocked — the core local-file workflow never needs it.

What mistakes do DuckDB beginners make?

Most early DuckDB frustration comes from a short list of predictable traps. Recognize them now and you will skip weeks of confusion.

What should you build after your first query?

Take a real CSV you already own — an export from a spreadsheet, a downloaded dataset — and answer one question with SQL instead of a pivot table: totals by category, the top ten rows, a monthly trend. Then save it as a persistent database, export the answer to Parquet, and you have exercised the entire beginner arc: install, first query, direct file query, persistence, the pandas handoff. If you arrived here looking for a duckdb for beginners guide that stays practical, the checklist above is your practice plan. When the analysis outgrows your laptop, the same .duckdb file copies to a server untouched — the what is VPS hosting guide covers the server side of that move.

Sources

• GitHub duckdb/duckdb releases API — v2.0.0 released 2026-10-21, fetched 2026-10-31
• Deepnote, "Ultimate Guide to DuckDB Library in Python" — in-process OLAP engine, columnar/vectorized execution, MIT license, replacement scans for DataFrames, read_csv_auto type inference, sample_size tip
• livedocs.com, "Complete DuckDB Tutorial for Beginners" — pip install arc, direct CSV query in FROM, in-memory vs persistent connection rule, .df() pandas handoff
• DZone, "Using DuckDB for Local SQL Analysis in Python" — connect()/execute()/df() pattern, read_csv_auto usage, register() for DataFrames
• MotherDuck, "DuckDB Python Quickstart (Part 2)" — con.sql() on DataFrames, .df()/.fetchdf() conversion, INSTALL/LOAD httpfs, remote CSV reads
• duckdb.org, "Python Client API" (v2.0 docs) — DuckDBPyConnection, read_csv/read_json/from_csv_auto, module-level query() and the default connection
• readmedium.com, "DuckDB Essentials" — CLI arcs (duckdb my_db.db, .open, -readonly, ATTACH DATABASE), COPY ... TO Parquet, CREATE TABLE AS from read_parquet
• CODE Magazine, "Using DuckDB for Data Analytics" — OLTP vs OLAP framing, 2019 first release by Hannes Muehleisen and Mark Raasveldt, conn.register() pattern
• CodexFuse local verification — every command in this guide was executed against a fresh DuckDB 2.0.0 install (pip, 2026-10-31): version print, in-memory query, direct CSV query, file-persistence round-trip, DataFrame SQL + .df() handoff, COPY-to-Parquet round-trip, the double-quote error message, the delim=/sample_size=/header= parameters, and the 2.0.0 relation-.fetchone() AttributeError

Frequently asked questions

Is DuckDB free?

Yes — DuckDB is open source under the MIT license. There is no paid tier for the core engine, no account, and no API key; pip install duckdb gives you everything this guide uses.

Does DuckDB need a server?

No. DuckDB runs in-process inside your application — there is no server to install, no port to open, and no credentials to manage. Importing the library is the whole "installation."

What is the difference between duckdb.connect() and duckdb.connect('file.duckdb')?

duckdb.connect() with no argument creates an in-memory database that is wiped when the connection closes. Passing a file path creates (or opens) a persistent database on disk that survives restarts.

Can DuckDB replace pandas?

Not exactly — and it is not trying to. DuckDB replaces the analytical-query middle of your workflow: use SQL for filtering, joining, and aggregating large data, then hand results back to pandas with .df() for plotting and wrangling.

DuckDB or SQLite — which should I learn first?

Learn the one that matches your job: SQLite for apps that store and update small records (OLTP), DuckDB for analyzing large tables and files (OLAP). The guides are siblings by design — the SQLite beginner guide covers the transactional half.