How to Set Up DuckDB

1. Install it

Before the explanation: every database you have met so far needed you to create a table before you could put anything in it. What would have to be true for that step to be unnecessary?

python -m pip install duckdb

That is the whole installation. No server, no service running in the background, no configuration file, and no administrator rights. Like SQLite, DuckDB is a library that runs inside your own program rather than a program you start and connect to.

Write it as python -m pip rather than bare pip, for the reason set out in installing Python for data analysis: it guarantees the package lands in the Python you are about to run.

There is also a standalone command-line version if you would rather work in a terminal than in Python, and a browser version, but the Python package is the one that fits an analyst's day and it is what this page uses.

2. Query a CSV with no import

Here is the whole point of the tool, in six lines.

import duckdb

print(duckdb.sql("""
    SELECT Country, COUNT(*) AS invoices, ROUND(SUM(Total), 2) AS revenue
    FROM 'invoices.csv'
    GROUP BY Country
    ORDER BY revenue DESC
    LIMIT 5
"""))

┌─────────┬──────────┬─────────┐
│ Country │ invoices │ revenue │
│ varchar │  int64   │ double  │
├─────────┼──────────┼─────────┤
│ USA     │       91 │  523.06 │
│ Canada  │       56 │  303.96 │
│ France  │       35 │   195.1 │
│ Brazil  │       35 │   190.1 │
│ Germany │       28 │  156.48 │
└─────────┴──────────┴─────────┘

Read the FROM line again, because it is the answer to the opening question. FROM 'invoices.csv' is a filename in quotes sitting exactly where a table name goes. There was no CREATE TABLE, no import wizard, no column list, and no database file created anywhere. DuckDB opened the file, read what it needed, and answered.

Notice the second header row in the output. Under each column name is its type: varchar, int64, double. Those were worked out from the file, and printing them by default is a small design decision that saves a great deal of confusion.

3. What it worked out about your file

You can ask directly what types it decided on, which is the first thing to do with any new file.

print(duckdb.sql("DESCRIBE SELECT * FROM 'invoices.csv'"))

┌─────────────┬─────────────┬─────────┬─────────┬─────────┬─────────┐
│ column_name │ column_type │  null   │   key   │ default │  extra  │
│   varchar   │   varchar   │ varchar │ varchar │ varchar │ varchar │
├─────────────┼─────────────┼─────────┼─────────┼─────────┼─────────┤
│ InvoiceId   │ BIGINT      │ YES     │ NULL    │ NULL    │ NULL    │
│ Country     │ VARCHAR     │ YES     │ NULL    │ NULL    │ NULL    │
│ InvoiceDate │ TIMESTAMP   │ YES     │ NULL    │ NULL    │ NULL    │
│ Total       │ DOUBLE      │ YES     │ NULL    │ NULL    │ NULL    │
└─────────────┴─────────────┴─────────┴─────────┴─────────┴─────────┘

Four columns of plain text in a file, and it correctly identified a whole number, some text, a timestamp and a decimal. The date column matters most: dates arriving as text is one of the most common frustrations in every other tool on this site, and here it was handled without being asked.

Say why you should still read this output rather than trust it, before reading on. Because inference is a guess made from the file's contents, and a column that happens to look numeric in every row you sampled can still be a code with meaning. DESCRIBE takes two seconds and tells you what the tool decided, which is the only way to notice when it decided something you did not want.

4. A whole folder as one table

This is the feature that turns DuckDB from convenient into genuinely useful for analyst work. If your data arrives as one file per month, you can query all of them at once by putting a * in the filename.

print(duckdb.sql("SELECT COUNT(*) AS rows_across_files FROM 'months/*.csv'"))

┌───────────────────┐
│ rows_across_files │
│       int64       │
├───────────────────┤
│               239 │
└───────────────────┘

Two files in that folder, 119 rows and 120 rows, and 239 came back. There was no loop, no concatenation step, and no list of filenames to maintain. Adding a new month means dropping a file into the folder, and the same query picks it up.

Think about the monthly reporting job you or a colleague runs. If the twelve files for last year sat in one folder, how much of that job is just the work of sticking them together? That part is what a single asterisk removes.

5. Into pandas, and back out

DuckDB is built to sit beside pandas rather than replace it. Add .df() to get a DataFrame.

df = duckdb.sql("""
    SELECT Country, COUNT(*) AS n
    FROM 'invoices.csv'
    GROUP BY Country ORDER BY n DESC LIMIT 3
""").df()
print(df)

Country  n
    USA 91
 Canada 56
 France 35

That needs pandas installed, which DuckDB does not require on its own. It works the other way too: a DataFrame already in memory can be queried by name, so SELECT * FROM my_dataframe works with no conversion step at all.

That two-way street is the sensible division of labour. Use SQL for filtering, joining and grouping, which is what SQL is best at, then hand the small result to pandas for reshaping and charting. It is the same dividing line described in connecting Python to a SQL database, with less ceremony in the middle.

6. DuckDB or SQLite: which, when

Both are file-based, both need no server, and they are built for different jobs. Neither is an upgrade of the other.

In practice many analysts use both without thinking about it: DuckDB to explore whatever landed today, SQLite for the tidy database a project keeps. If you are choosing your first one and preparing for interviews, start with SQLite and the fifteen-minute setup, then add DuckDB the first time a file is too big or too plural to be comfortable.

What goes wrong, and the fix

Six worth knowing.

The output prints as garbled symbols on Windows. DuckDB draws its result tables with box-drawing characters, and an older Windows console cannot encode them. Set PYTHONIOENCODING=utf-8 before running, or use .df() and print that instead. Nothing is wrong with the query.

"No files found that match the pattern". The path is relative to where you ran Python, not to where the script file sits. Print your working directory, or use a full path.

A column came out as text when it should be a number. Something in it is not numeric, often a thousands separator or an "N/A". DESCRIBE shows you which column, and CAST(col AS DOUBLE) in the query is the deliberate fix.

Files in a folder have different columns. The glob assumes one shape. Add union_by_name=true to the read function if the columns match by name but not by order.

.df() fails with a missing module. pandas is not installed in that environment. DuckDB does not install it for you.

A query on a huge file uses all your memory. Select only the columns you need rather than SELECT *. Reading fewer columns is where most of the speed comes from, and asking for all of them gives that advantage away.