DuckDB: Analytics Queries on a File, Without a Server

A small shop owner exports twelve orders to a CSV file and wants one answer: which city brought in the most money this week? The file sits on a laptop, the data fits in a spreadsheet, and yet getting the answer takes longer than it should. A spreadsheet means wrestling with filters and formulas, and the totals shift every time a row is added. A database server for a file this small is worse: installing, configuring, creating users, all for twelve rows. DuckDB sits in the middle. It is a small analytical database that runs inside your own program, reads the CSV directly, and answers with plain SQL. No server, no setup, no separate process to keep alive.

What I would check before using it

My first check is the shape of the question. DuckDB earns its place when the question is analytical: totals by group, rankings, running sums, joins across files. If the job is storing one record at a time and reading it back by key, SQLite or a JSON file is simpler, because that is not what an analytical engine is built for.

DuckDB shines on files from thousands of rows up to hundreds of millions, far beyond what a spreadsheet handles comfortably. For twelve rows it is still pleasant, because setup costs close to zero. For a dataset that no longer fits on one machine, or many writers updating the same tables at once, it is the wrong tool, and a server database or a distributed engine fits better.

DuckDB speaks SQL, so anyone comfortable with SELECT, GROUP BY, and window functions can use it on day one. A team that only knows pandas can get the same answers with a DataFrame, but SQL keeps the logic in one place when the analysis grows from one file to several.

DuckDB reads CSV, Parquet, and JSON files directly, and it can query a file without importing it first. That matters when the source file is produced by another system every day and copying it into a database would add a step that can break.

My verdict, as judgement: use DuckDB when you have files to analyse with SQL and no appetite for running a server. Skip it when you need many concurrent writers, or when the data has outgrown a single machine. The recommendation flips the moment the file becomes a shared, constantly updated system of record instead of a snapshot to analyse.

One small shop, run end to end

The example is a small online shop selling computer accessories. Its orders live in a CSV file with twelve rows: an order id, a city, an item, a quantity, and a price. The cities are Karachi, Lahore, and Islamabad, and the prices are ordinary shop prices, from a Rs 650 cable to a Rs 7,200 monitor arm. The owner wants revenue per city, ranked, and a grand total. The whole script is short, and it reads the CSV straight into DuckDB without loading it into Python first.

import duckdb csv_text = """order_id,city,item,quantity,price 1,Karachi,Wireless mouse,2,1450 2,Lahore,USB-C cable,5,650 3,Karachi,Mechanical keyboard,1,"1,250" 4,Islamabad,Laptop stand,3,2800 5,Lahore,Wireless mouse,1,1450 6,Karachi,Webcam,2,3900 7,Islamabad,USB-C cable,4,650 8,Karachi,Monitor arm,1,7200 9,Lahore,Laptop stand,2,2800 10,Islamabad,Mechanical keyboard,1,"1,250" 11,Karachi,USB-C cable,3, 12,Lahore,Webcam,1,3900 """ with open("orders.csv", "w") as f: f.write(csv_text) con = duckdb.connect() con.execute("CREATE TABLE orders AS SELECT * FROM read_csv('orders.csv')")

Two rows in that file are deliberately messy, the way real export files are. Order 3 and order 10 carry the price as "1,250", with a thousands comma, and order 11 has no price at all, because the shop had not entered it yet. DuckDB reads the file and guesses the column types from the values it sees, and the comma in "1,250" means the price column comes in as text rather than a number. The describe output from the run shows this plainly:

Columns and types DuckDB inferred: - order_id: BIGINT - city: VARCHAR - item: VARCHAR - quantity: BIGINT - price: VARCHAR

The thing that went wrong came next. The obvious query, summing the price after casting it to a number, failed on the very first comma:

Trying the obvious total, SUM(CAST(price AS DOUBLE)): Conversion Error: Conversion Error: Could not convert string '1,250' to DOUBLE when casting from source column price LINE 1: SELECT SUM(CAST(price AS DOUBLE)) FROM orders ^^^^^^^^^^^^^^^^^^^^^^

The fix is to clean the value before casting it. REPLACE strips the comma, CAST turns the result into a number, and COALESCE turns the empty price into zero so the row still counts instead of vanishing from the total. The fixed query also ranks the cities with a window function, which is one query doing the grouping and the ranking together:

rows = con.execute(""" SELECT city, COUNT(*) AS orders, SUM(quantity) AS items_sold, SUM(COALESCE(CAST(REPLACE(price, ',', '') AS DOUBLE), 0) * quantity) AS revenue, RANK() OVER (ORDER BY SUM(COALESCE(CAST(REPLACE(price, ',', '') AS DOUBLE), 0) * quantity) DESC) AS revenue_rank FROM orders GROUP BY city ORDER BY revenue DESC """).fetchall() for city, orders, items, revenue, rank in rows: print(f"- {city}: {orders} orders, {items} items, Rs {revenue:,.0f}, rank {rank}")

Here is the real output from the run:

Total revenue per city (comma stripped, empty price treated as 0): - Karachi: 5 orders, 9 items, Rs 19,150, rank 1 - Lahore: 4 orders, 9 items, Rs 14,200, rank 2 - Islamabad: 3 orders, 8 items, Rs 12,250, rank 3 Grand total revenue: Rs 45,600

The numbers check out by hand. Karachi leads because of the Rs 7,200 monitor arm and two webcams, while Islamabad trails even though it sold eight items, because four of them were low-priced cables. Order 11, the one with the missing price, contributed three cables at Rs 0, which is honest about what the data actually says, but it is also a flag: a zero in the total is a reminder to go back and fill in the real price. A cleaner long-term fix is to pass thousands = ',' to read_csv so DuckDB parses "1,250" as a number on the way in, and then the REPLACE is not needed at all.

Common mistakes and how to fix them

First, trusting the types DuckDB guesses. The symptom is a Conversion Error on a cast, or a SUM failing because the column arrived as VARCHAR. The fix is DESCRIBE after loading, and declaring the types with the columns option in read_csv when the guess is wrong.

Second, letting NULLs eat your totals. The symptom is revenue lower than expected, with no error, because a missing price makes the product NULL, and SUM skips NULLs silently. The fix is COALESCE around the value, as in the example, and count NULL prices so you know how much is guesswork.

Third, forgetting that an in-memory database disappears. The symptom is a table that exists during the run and vanishes next time, because duckdb.connect() with no file name keeps everything in memory. The fix is to pass a file name, such as duckdb.connect("shop.duckdb"), when the data should survive.

Fourth, querying the file instead of the table. The symptom is a query that re-reads the CSV on every run, so a changed file rewrites old answers. The fix is loading the file once with CREATE TABLE AS, then querying the table.

My call on DuckDB

DuckDB is at its best when the data already exists as files and the question is analytical. Twelve messy orders became a ranked revenue table with two queries and no server, and the one real problem, a comma in a price, surfaced as a clear error with a clear fix. For a shared system that many people update at once, or data that no longer fits on one machine, reach for a server database instead. If your work looks like this shop, a pile of exports and a question about totals, try it on one real file and see whether the SQL reads more clearly than the spreadsheet it replaces. If it does, keep going.