# DuckDB: query a client's CSV and Parquet files on your laptop

> A client has just sent you a folder of exported data, and the next meeting is an hour away. You need a database that runs inside your script, with no server to set up.

Original: https://fdetimes.net/en/tools/duckdb-query-client-csv-parquet-laptop/

A client has just sent you a folder of exported files, some CSV, some Parquet, with one question: "Is this data usable?" The best answer is a few SQL queries that finish in minutes, not a ticket requesting infrastructure.

DuckDB was built for exactly this situation. At GOTO Amsterdam 2024, a DuckDB engineer showed how the tool handles hundreds of gigabytes of data on a laptop, reading CSV and Parquet files directly.

For a Forward Deployed Engineer, that figure matters not because it is a record. It means that on day one you can run meaningful queries on the client's data, before anyone has built you a cluster.

## Why is having no server an advantage?

According to the official documentation, DuckDB does not run as a separate process but lives inside the application that calls it. The duckdb/duckdb repository on GitHub describes it as an in-process SQL database management system designed for analytical work, released under the MIT licence.

So there is no server to install on your laptop and no service to manage. If the client has a strict software approval process, put both points in your installation request: it runs embedded, and the licence is clear. The approvers will have less to review.

## First question: what is in this file?

The feature you will use most is `read_csv`. DuckDB's CSV reader detects the file's format and infers the type of each column on its own, and the documentation recommends trying this automatic approach first.

Suppose the client sends `orders.csv` and wants revenue by month. You do not need to declare a schema:

```sql
SELECT date_trunc('month', order_date) AS month,
       count(*) AS order_count,
       sum(amount) AS revenue
FROM read_csv('orders.csv')
GROUP BY month
ORDER BY month;
```

But the sniffer reads only a sequential sample of the file, 20,480 rows by default. If unusual values sit near the end of the file, it can guess a column's type wrongly. That is a risk to the correctness of your numbers, not just a matter of speed.

So before any number goes on a slide, check what type DuckDB assigned to each column. The documentation does this in two steps: load the file into a table, then run `DESCRIBE`.

```sql
CREATE TABLE orders AS SELECT * FROM read_csv('orders.csv');
DESCRIBE orders;
```

If `order_date` shows up as a string, you have three fixes. Increase `sample_size`, setting it to `-1` to read the whole file. Override the type of a single column with `types`, since the sniffer always gives priority to options the user sets. Or declare `dateformat` and `timestampformat` when DuckDB guesses the date or time format wrongly.

## Parquet: read only the columns you need

If the client already has Parquet, your job gets much easier. When querying a Parquet file, DuckDB reads only the columns the query actually needs, and filters are pushed down into the file scan.

Picture a transactions table with 200 columns, while your question uses only `customer_id`, `amount` and `created_at`. With Parquet, only those three columns are read, and the `WHERE` condition is applied as the file is read.

```sql
SELECT customer_id, sum(amount)
FROM read_parquet('transactions.parquet')
WHERE created_at >= DATE '2024-01-01'
GROUP BY customer_id;
```

When the client asks what format you want the data in, ask for Parquet. If all you have is CSV and you will query it repeatedly, convert it to Parquet once at the start with the `COPY` statement, which uses Snappy compression by default.

**Key point:** Before building a pipeline, ask the client's data a few questions in SQL on your own machine.

## What if the file is bigger than RAM?

A file larger than memory is not a reason to stop. DuckDB's performance tuning documentation says larger-than-memory workloads still run, because the system spills data to disk. It also notes that DuckDB sometimes starts too many threads, for example because of HyperThreading, which slows the machine down; in that case adjust it with `SET threads`.

Mark Needham describes having to set an explicit memory limit for DuckDB when converting CSV to Parquet in a memory-constrained container. On a laptop or a low-RAM virtual machine at a client site, do the same, and set the value below the RAM actually free, not the machine's total RAM.

One more point is easy to forget: `COPY` still reads the CSV through the same sniffer described above. If a column's type is guessed wrongly, that wrong type is baked into the Parquet file, and every later query inherits the error.

So run `DESCRIBE` and fix the types first, then carry the same options into the conversion statement. The example below assumes the machine has only about 6 GB free:

```sql
SET memory_limit = '4GB';
COPY (SELECT * FROM read_csv('events.csv', sample_size = -1))
TO 'events.parquet' (FORMAT parquet);
```

Once the conversion is done, count the rows on both sides. If `count(*)` on the CSV and on the Parquet file do not match, find out why before using the new file.

## What are the two most common mistakes?

The most dangerous mistake is trusting the types the sniffer guessed and putting a date column on a slide that is actually being read as a string. This corrupts the very numbers you present. To avoid it, run `DESCRIBE`, and raise `sample_size` or declare types yourself when a column looks odd.

The second mistake is only about speed: querying a large CSV file over and over instead of converting it to Parquet once. On low-RAM machines, add forgetting to set `memory_limit` or letting too many threads run at once. This slows you down but does not make your results wrong.

## What to learn first, and how to put it on a CV

Learn in the order you will meet the work. Start with `read_csv` and the habit of running `DESCRIBE`, along with the `sample_size`, `types` and `dateformat` options. Next comes Parquet and why it is fast, and finally `COPY`, `memory_limit` and `threads` for the days when the files are big.

The fastest way to practise is to go through the whole loop once with a public file: query the CSV, check the column types, convert to Parquet, compare row counts, then compare run times.

On your CV, do not just list "DuckDB" under skills. Describe what you achieved with it: how messy the data was, what question you answered, and how long it took to get the answer.

A line such as "converted 50 GB of CSV to Parquet on a laptop and answered the client's revenue question the same afternoon", backed by a small repository others can check, says far more than a list of tools.

Next time a client sends a folder of exports, do not rush to request infrastructure: open DuckDB, run `DESCRIBE`, and bring to the meeting a number whose data types you have checked yourself.

**Try this week:**

- Take a fairly large public CSV file, load it with read_csv, run DESCRIBE to see each column's type, then try sample_size = -1 and see whether the result changes
- Convert the same file to Parquet with COPY, run the same query on both formats and compare the timings
- Write the process up as a short section in a README or on your CV: how many GB of data, what question you asked, and how long it took to get the answer

## Sources

- [Why DuckDB](https://duckdb.org/why_duckdb)

- [GitHub - duckdb/duckdb](https://github.com/duckdb/duckdb)

- [CSV Import](https://duckdb.org/docs/current/data/csv/overview.html)

- [DuckDB's CSV Sniffer: Automatic Detection of Types and Dialects](https://duckdb.org/2023/10/27/csv-sniffer.html)

- [CSV Tips (DuckDB documentation)](https://duckdb.org/docs/current/data/csv/tips.html)

- [CSV Auto Detection](https://duckdb.org/docs/current/data/csv/auto_detection)

- [Reading and Writing Parquet Files](https://duckdb.org/docs/current/data/parquet/overview.html)

- [Tuning Workloads](https://duckdb.org/docs/current/guides/performance/how_to_tune_workloads)

- [DuckDB: Crunching Data Anywhere, From Laptops to Servers (GOTO Amsterdam 2024)](https://gotopia.tech/sessions/3114/duckdb-crunching-data-anywhere-from-laptops-to-servers)

- [Exporting CSV files to Parquet file format with Pandas, Polars, and DuckDB](https://markhneedham.com/blog/2023/01/06/export-csv-parquet-pandas-polars-duckdb)