Querying CSV Files With DuckDB Instead of Loading Them
DuckDB runs SQL on a CSV file with no import, no schema and no server. See what it guesses, what that costs, where it fails, and how to make it strict.
The usual route from a CSV file to an answer goes through a database: create a table, write the types, load, check, query. For a file you want to ask three questions about, that is more ceremony than the question deserves. DuckDB skips it. You write FROM 'orders.csv' in a query and it reads the file, guesses the format and runs. This guide covers what it guesses and how to see the guess, how fast it is without a load and when a load still pays, the places where the guess fails, and how to turn a one-off query into something you can run again and trust. Everything below was run on DuckDB 2.0.0 against the 130 MB, 2,000,000 row orders file used in this section.
The first query
Four customers, one with a comma in the name, one with an empty email and balance. This is the file and a query that filters it:
id,name,email,signup_date,balance
1,Ana Silva,[email protected],2025-01-15,120.50
2,"Smith, Jane",[email protected],2025-02-03,0
3,Bo Chen,,2025-02-20,
4,Cy Dubois,[email protected],2025-03-01,15.75duckdb -box -c "SELECT name, balance FROM 'customers.csv' WHERE balance > 10 ORDER BY balance DESC"┌───────────┬─────────┐
│ name │ balance │
├───────────┼─────────┤
│ Ana Silva │ 120.5 │
│ Cy Dubois │ 15.75 │
└───────────┴─────────┘There is no CREATE TABLE, no COPY and no schema. A file name in quotes where a table would go is a shortcut for read_csv('customers.csv'), and the result is a table like any other, which JOIN, GROUP BY and window functions all accept. The article on running SQL directly on a CSV goes through the command line forms: FROM-first queries, DESCRIBE, SUMMARIZE, reading from a pipe and writing CSV out of a query.
On the big file, the same shape of query:
cd /batches/csv-0001-0200/work && time duckdb -box -c "SELECT country, count(*) AS orders, round(sum(amount), 2) AS revenue FROM 'orders-2m.csv' GROUP BY country ORDER BY revenue DESC LIMIT 3" 2>&1┌─────────┬────────┬─────────────┐
│ country │ orders │ revenue │
├─────────┼────────┼─────────────┤
│ FR │ 286777 │ 22969870.61 │
│ IN │ 286243 │ 22934328.0 │
│ BR │ 285957 │ 22933345.89 │
└─────────┴────────┴─────────────┘
real 0m0.290s
user 0m0.364s
sys 0m0.261sCountry, order count and revenue for the 2,000,000 rows, grouped and sorted, in 0.37 s of wall time, including starting the process. That is not a trick of caching: the file is read and parsed on every run. DuckDB's CSV reader splits the file into chunks and parses them on all cores, and it only parses the columns the query uses.
What it guessed
When you give read_csv no options it sniffs. It samples the start of the file, tries combinations of delimiter, quote and escape, scores each by how consistently it parses, then infers the header and the types of every column. The result is inspectable. sniff_csv returns the decision, and its Prompt column is the exact read_csv call that reproduces it:
duckdb -line -c "SELECT Delimiter, Quote, Escape, SkipRows, HasHeader, DateFormat, TimestampFormat FROM sniff_csv('customers.csv')" Delimiter = ,
Quote = "
Escape = (empty)
SkipRows = 0
HasHeader = true
DateFormat = %Y-%m-%d
TimestampFormat = NULLduckdb -list -noheader -c "SELECT Prompt FROM sniff_csv('customers.csv')"FROM read_csv('customers.csv', auto_detect=false, delim=',', quote='"', escape='', new_line='\n', skip=0, comment='', header=true, columns={'id': 'BIGINT', 'name': 'VARCHAR', 'email': 'VARCHAR', 'signup_date': 'DATE', 'balance': 'DOUBLE'}, dateformat='%Y-%m-%d');The sniffer chose a comma, a double quote, a header, the date format %Y-%m-%d, and column types BIGINT, VARCHAR, VARCHAR, DATE and DOUBLE. Copying that prompt into your script, with auto_detect=false, freezes the guess so it cannot change when next month's file looks slightly different. Letting DuckDB sniff the delimiter and types shows more of what it looks at, and the auto detection page in the manual describes the algorithm.
It is also better than most at the awkward parts. On a file whose first two lines are a title and a comment, the sniffer decided to skip two rows and found the header in the third, without being told:
duckdb -box -c "SELECT * FROM read_csv('report.csv')" 2>&1 | head -12┌────────┬──────────┬──────────┬──────┐
│ region │ q1 │ q2 │ note │
├────────┼──────────┼──────────┼──────┤
│ EMEA │ 1.234,50 │ 2.000,00 │ n/a │
│ APAC │ 900,00 │ NULL │ - │
│ AMER │ 1.500,25 │ 1.750,00 │ ok │
└────────┴──────────┴──────────┴──────┘The values 1.234,50 stayed text, because they use a decimal comma, and that is the other half of the story: it finds structure well, and it does not know your locale.
Types, and the ZIP code test
The type guesses that matter most are the ones that destroy information. I made a file with identifiers that look like numbers, a Y and N column, true and false flags, percentages and a 20 digit number:
duckdb -box -c "DESCRIBE SELECT * FROM 'types-guess.csv'"┌─────────────┬─────────────┬──────┬──────┬─────────┬───────┐
│ column_name │ column_type │ null │ key │ default │ extra │
├─────────────┼─────────────┼──────┼──────┼─────────┼───────┤
│ id │ VARCHAR │ YES │ NULL │ NULL │ NULL │
│ zip │ VARCHAR │ YES │ NULL │ NULL │ NULL │
│ flag │ BOOLEAN │ YES │ NULL │ NULL │ NULL │
│ answer │ VARCHAR │ YES │ NULL │ NULL │ NULL │
│ seen │ DATE │ YES │ NULL │ NULL │ NULL │
│ ratio │ VARCHAR │ YES │ NULL │ NULL │ NULL │
│ big │ HUGEINT │ YES │ NULL │ NULL │ NULL │
│ when_ts │ TIMESTAMP │ YES │ NULL │ NULL │ NULL │
└─────────────┴─────────────┴──────┴──────┴─────────┴───────┘id and zip stayed VARCHAR because their values have leading zeros, which is the check the sniffer applies to protect them. flag, with T and F, became BOOLEAN and answer, with Y and N, did not. seen became DATE and when_ts TIMESTAMP. The 20 digit big became HUGEINT, since it exceeds 64 bits. The percentage column stayed text. Each is defensible, and the one I would not accept without looking is the boolean: a column of T and F might be a flag or might be a grade, and it is your call. Fixing type detection that guessed wrong has the overrides.
On the big file, the same protection kept the ZIP codes intact. The Parquet file written from the default read has 190,451 zip values starting with zero, the same count as in the CSV, and pandas without hints lost every one of them, as the types article measured.
What it costs, and when loading pays
Querying in place re-parses the text each time. I asked three questions of the 2,000,000 row file in each of three forms, five runs each, taking the median:
python bench_duckdb.pyone-time: CREATE TABLE from CSV 1.32 s (51 MB file), COPY to Parquet 0.72 s (34 MB file), CSV is 130 MB
query (median of 5) CSV in place loaded table Parquet
count(*) 0.24s 0.04s 0.04s
sum where status = 'paid' 0.23s 0.05s 0.05s
group by country, status 0.24s 0.05s 0.05sFrom the CSV, each query took about 0.24 s, and that includes starting the duckdb process. From a loaded table in a DuckDB database file, 0.04 to 0.05 s, and from Parquet the same. The one time costs: loading the CSV into a table took 1.32 s and produced a 51 MB database file, and writing Parquet with zstd took 0.72 s and produced 34 MB, against the CSV's 130 MB.
The difference per query is about 0.19 s, so the load pays for itself after about seven queries, and the Parquet conversion after about four. Those are the numbers for this file on this machine, and the shape generalizes: the first few questions are cheapest straight from the CSV, a dozen questions justify a conversion, and anything that will be queried for weeks should live in Parquet or a table. Converting a CSV to Parquet in one line has the command and the compression comparison.
Memory is not a constraint. The grouped query peaked at about 215 MB of resident memory on a 130 MB file. With a deliberately small memory_limit of 200 MB, a sort over all 2,000,000 rows still returned its three largest amounts correctly, because DuckDB spills to disk when operators need more than the limit. A file larger than RAM is fine for scans and aggregates, and slower for sorts and joins.
Profiling a file in SQL
Because the file is a table, the questions you would ask before loading it are queries. SUMMARIZE returns one row per column with the type, the range, an approximate count of distinct values and the share of NULLs, and it answers most of "what is in this file" in one statement:
duckdb -box -c "SELECT column_name, column_type, min, max, approx_unique, null_percentage FROM (SUMMARIZE SELECT * FROM 'orders-2m.csv')"┌─────────────┬─────────────┬─────────────────────┬─────────────────────┬───────────────┬─────────────────┐
│ column_name │ column_type │ min │ max │ approx_unique │ null_percentage │
├─────────────┼─────────────┼─────────────────────┼─────────────────────┼───────────────┼─────────────────┤
│ order_id │ BIGINT │ 1 │ 2000000 │ 1903536 │ 0.00 │
│ customer_id │ BIGINT │ 1 │ 249999 │ 236513 │ 0.00 │
│ zip │ VARCHAR │ 00500 │ 99998 │ 104607 │ 0.00 │
│ amount │ DOUBLE │ 0.04 │ 654.24 │ 36532 │ 0.00 │
│ created_at │ TIMESTAMP │ 2025-01-01 00:00:04 │ 2025-12-31 23:59:38 │ 1855178 │ 0.00 │
│ status │ VARCHAR │ failed │ shipped │ 5 │ 0.00 │
│ country │ VARCHAR │ BR │ US │ 7 │ 0.00 │
│ note │ VARCHAR │ call first │ rush order │ 4 │ 20.03 │
└─────────────┴─────────────┴─────────────────────┴─────────────────────┴───────────────┴─────────────────┘Read it the way you would read the profile of a table you were about to load. order_id runs from 1 to 2,000,000, which matches the row count. zip is VARCHAR with a minimum of 00500, so the leading zeros are real. status has 5 distinct values and country 7, which makes them candidates for categories or enums. note is 20.03% null, which agrees with the 400,697 empty notes I counted elsewhere in this section. The approx_unique figure is an estimate; for an exact count use count(DISTINCT ...).
The same engine runs your own checks. These are the ones I put in front of any file that is going to be loaded or reported on, written as one pass:
duckdb -box -c "SELECT count(*) AS rows, count(DISTINCT order_id) AS distinct_ids, count(*) FILTER (WHERE note = '' OR note IS NULL) AS empty_notes, count(*) FILTER (WHERE length(zip) <> 5) AS bad_zip_length, count(*) FILTER (WHERE amount <= 0) AS non_positive_amounts, count(*) FILTER (WHERE status NOT IN ('paid','refunded','pending','failed','shipped')) AS unknown_status FROM 'orders-2m.csv'"┌─────────┬──────────────┬─────────────┬────────────────┬──────────────────────┬────────────────┐
│ rows │ distinct_ids │ empty_notes │ bad_zip_length │ non_positive_amounts │ unknown_status │
├─────────┼──────────────┼─────────────┼────────────────┼──────────────────────┼────────────────┤
│ 2000000 │ 2000000 │ 400697 │ 0 │ 0 │ 0 │
└─────────┴──────────────┴─────────────┴────────────────┴──────────────────────┴────────────────┘2,000,000 rows and 2,000,000 distinct ids, so the key is unique. 400,697 empty notes, no ZIP codes of the wrong length, no zero or negative amounts and no status outside the five expected values. Each check is a FILTER clause on one scan, so adding more costs little. When one of them returns a nonzero count, the same predicate in a WHERE clause lists the offending rows, which is the part spreadsheets and loaders make hard.
Compressed input, and the speed of each
read_csv detects compression from the extension and decompresses as it reads, so there is nothing to unpack. I compressed the 130 MB file with gzip and with zstd and ran the same count and sum on all three:
for f in orders-2m.csv orders-2m.csv.gz orders-2m.csv.zst; do
printf '%-20s' $f; /usr/bin/time -f '%e s' duckdb -list -noheader -c "SELECT count(*), round(sum(amount), 2) FROM read_csv('$f')" 2>&1 | paste -sd' '
doneorders-2m.csv 2000000|160174646.36 0.28 s
orders-2m.csv.gz 2000000|160174646.36 0.73 s
orders-2m.csv.zst 2000000|160174646.36 0.49 sThe plain file took 0.23 s, the zstd file 0.47 s and the gzip file 0.71 s, and all three returned 2,000,000 rows and the same total. Decompression is serial per file, so it is the part that does not use all the cores, and gzip is the slowest to inflate. If you must store a CSV compressed and query it often, zstd costs half as much as gzip on this evidence. If you query it often, the better answer is to convert it once, as the Parquet article shows.
Threads
The parallel reader is why the numbers are small. I ran one grouped query with the thread count set from 1 to 10, five runs each:
python duckdb_threads.py threads median s
1 0.51
2 0.31
4 0.24
10 0.20One thread took 0.58 s, two took 0.34 s, four took 0.25 s and ten took 0.21 s. The speed-up is 2.8 times, not 10, because process start, file reading and the final merge are not parallel, and four threads already capture most of the gain on this file. By default DuckDB uses all the cores it sees, which is what you want on a workstation and a mistake on a shared server, where SET threads = 4 keeps one query from taking the machine.
From Python
The same engine is a library. In Python, duckdb.sql takes a query that names the file, and .df() returns a pandas DataFrame, so the heavy work happens in DuckDB and pandas receives a small result. I compared that with reading the whole file into pandas and grouping there:
python duckdb_python.pyDuckDB on the CSV, result as a DataFrame: 0.31 s
country orders revenue
FR 286777 22969870.61
IN 286243 22934328.00
BR 285957 22933345.89
pandas read_csv then groupby: 0.45 s
DuckDB querying the pandas DataFrame: 0.01 s -> ('pending', 400329)DuckDB answered in 0.31 s including the import and the conversion to a DataFrame, and the pandas read and group took 0.45 s, a difference that grows with the file because pandas holds the whole table in memory. The last line shows DuckDB querying a pandas DataFrame by variable name, in 0.01 s: a DataFrame in scope is a table to DuckDB. (My first attempt named the variable full, which is a reserved word in SQL, and failed with a syntax error that points at the wrong place; avoid names like full, order and group.) The Python CSV guide covers the standard library route.
A CSV at a URL
read_csv also takes an http or https address, and the HTTP support loads itself the first time it is needed. I served the 130 MB file from a small local web server and queried it by URL:
time duckdb -box -c "SELECT count(*) AS rows, round(sum(amount), 2) AS revenue FROM read_csv('http://127.0.0.1:8099/ok.csv')" 2>&1 | tail -8WARNING:
Falling back to full file download for file 'http://127.0.0.1:8099/ok.csv': the server does not support HTTP range requests. Performance and memory usage are potentially degraded.
┌─────────┬──────────────┐
│ rows │ revenue │
├─────────┼──────────────┤
│ 2000000 │ 160174646.36 │
└─────────┴──────────────┘
real 0m1.422s
user 0m0.739s
sys 0m0.369sThe result is the same count and revenue, 2,000,000 and 160,174,646.36, in 1.4 s. The warning at the top is worth reading: my test server does not support HTTP range requests, so DuckDB fell back to downloading the whole file, and the warning says performance and memory may suffer. A server that supports ranges lets DuckDB fetch only the parts of the file it needs, which matters far more for Parquet, where a query may read a few columns of a large file, than for a CSV that has to be scanned end to end.
The failure behavior is the part I care about. My server can send a response that stops halfway, and one that is a 404. Both are errors in DuckDB, not silent short results:
duckdb -c "SELECT count(*) FROM read_csv('http://127.0.0.1:8099/cut.csv')" 2>&1 | head -4IO Error: Transferred a partial file error for HTTP GET to 'http://127.0.0.1:8099/cut.csv'
LINE 1: SELECT count(*) FROM read_csv('http://127.0.0.1:8099/cut.csv')
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^HTTP Error: HTTP GET error on 'http://127.0.0.1:8099/missing.csv' (HTTP 404 Not Found)
LINE 1: SELECT count(*) FROM read_csv('http://127.0.0.1:8099/missing.csv')Transferred a partial file error for HTTP GET and HTTP GET error ... (HTTP 404 Not Found), each with a nonzero exit. That is what the shell pipe from curl to a database does not give you without pipefail, as the PostgreSQL URL article measured: a 404 through a plain pipe loaded zero rows and exited 0. Querying the URL directly makes the reader responsible for the transfer, and it checks it.
Two cautions apply. Authentication and signed URLs need a secret configured, which belongs in an environment variable or a DuckDB secret, not in a query that ends up in shell history. And a remote file is read again on every query, so the 0.19 s per query that a loaded table saves becomes a network round trip per query. For anything repeated, download once or convert to Parquet.
The questions people actually ask
With the file addressable as a table, ordinary SQL does the analysis, and the friendly extensions in DuckDB shorten the awkward parts. Monthly orders, revenue and the refund rate, from one scan:
duckdb -box -c "SELECT date_trunc('month', created_at)::DATE AS month, count(*) AS orders, round(sum(amount), 2) AS revenue, round(100.0 * count(*) FILTER (WHERE status = 'refunded') / count(*), 1) AS refund_pct FROM 'orders-2m.csv' GROUP BY 1 ORDER BY 1 LIMIT 4"┌────────────┬────────┬─────────────┬────────────┐
│ month │ orders │ revenue │ refund_pct │
├────────────┼────────┼─────────────┼────────────┤
│ 2025-01-01 │ 170226 │ 13610353.85 │ 20.0 │
│ 2025-02-01 │ 154241 │ 12356780.42 │ 20.3 │
│ 2025-03-01 │ 169678 │ 13608069.16 │ 19.9 │
│ 2025-04-01 │ 164296 │ 13136101.82 │ 20.0 │
└────────────┴────────┴─────────────┴────────────┘January had 170,226 orders and 13,610,353.85 in revenue and a refund rate of 20.0%. The refund rate hardly moves from month to month because the test data is generated; real data would show the movement you are looking for, and the point is that the query is five lines and runs in a fraction of a second.
The largest order in each country uses QUALIFY, DuckDB's filter on window function results, which saves the subquery that most databases need:
duckdb -box -c "SELECT country, order_id, amount FROM 'orders-2m.csv' QUALIFY row_number() OVER (PARTITION BY country ORDER BY amount DESC, order_id) = 1 ORDER BY country"┌─────────┬──────────┬────────┐
│ country │ order_id │ amount │
├─────────┼──────────┼────────┤
│ BR │ 1079902 │ 614.39 │
│ DE │ 1108619 │ 630.31 │
│ FR │ 862676 │ 592.08 │
│ GB │ 1012726 │ 654.24 │
│ IN │ 336317 │ 599.8 │
│ JP │ 158192 │ 651.04 │
│ US │ 290812 │ 552.91 │
└─────────┴──────────┴────────┘And distribution questions use the quantile functions. The median, the 95th percentile and the largest amount by status:
duckdb -box -c "SELECT status, round(quantile_cont(amount, 0.5), 2) AS median, round(quantile_cont(amount, 0.95), 2) AS p95, round(max(amount), 2) AS largest FROM 'orders-2m.csv' GROUP BY status ORDER BY status"┌──────────┬────────┬────────┬─────────┐
│ status │ median │ p95 │ largest │
├──────────┼────────┼────────┼─────────┤
│ failed │ 67.36 │ 189.93 │ 614.39 │
│ paid │ 67.11 │ 189.86 │ 654.24 │
│ pending │ 67.25 │ 189.91 │ 651.04 │
│ refunded │ 67.32 │ 189.92 │ 649.03 │
│ shipped │ 67.19 │ 189.77 │ 621.19 │
└──────────┴────────┴────────┴─────────┘Every status has a median near 67 and a 95th percentile near 190, which is what you would expect if the status does not depend on the amount, and which would be a finding in real data. The data analysis articles take these further, with window functions, cohorts and time series on CSV exports.
Changes in DuckDB 2.0 that show up in CSV work
Two behaviors changed in 2.0 and appear in older tutorials. Writing a file name without quotes as a table function argument, such as read_csv(customers.csv), used to be accepted as a string, and now prints a deprecation warning that says to use a string literal; the old behavior can be re-enabled with SET table_function_identifier_conversion='ENABLE_IMPLICIT_STRING', and I would not rely on it. And the single arrow in a lambda, x -> x * 2, is deprecated. It fails with a binder error that names the new syntax, and lambda x: x * 2 works:
duckdb -c "SELECT list_transform([1, 2, 3], x -> x * 2)" 2>&1 | head -3
duckdb -list -noheader -c "SELECT list_transform([1, 2, 3], lambda x: x * 2)"Binder Error: Deprecated lambda arrow (->) detected. Please transition to the new lambda syntax, i.e.., lambda x, i: x + i, before DuckDB's next release.
Use SET lambda_syntax='ENABLE_SINGLE_ARROW' to revert to the deprecated behavior.
For more information, see https://duckdb.org/docs/current/sql/functions/lambda.html.
[2, 4, 6]The command line client also prints a hint: line under errors suggesting its -agent mode, which renders results compactly for programs that read the output. It is harmless, and it appears at the bottom of every error shown in this section.
Where the guess fails
Four failures cover most of what goes wrong, and each is one option away from fixed.
The first is a type that changes late. The sniffer reads a sample, 20,480 rows by default, and a value outside that sample can break the type. I made a 40,000 row file with a number in qty on every row except row 30,000, which holds n/a:
duckdb -c "SELECT count(*), sum(qty) FROM read_csv('late-text.csv')" 2>&1 | head -8Conversion Error: CSV Error on Line: 30001
Original Line:
30000,n/a,item 30000
Error when converting column "qty". Could not convert string "n/a" to 'BIGINT'
Column qty is being converted as type BIGINT
This type was auto-detected from the CSV file.
Possible solutions:The sample saw only integers, picked BIGINT, and the read failed at line 30,001 with Could not convert string "n/a" to 'BIGINT'. The message names the line, the column and four ways out. Raising the sample size changes what the sniffer sees: sample_size=1000 and 20480 both gave BIGINT, and -1, which means the whole file, gave VARCHAR. Naming the null marker is usually the better fix, since n/a is a missing value, not a string:
duckdb -box -c "SELECT count(*) AS rows, sum(qty) AS total, count(*) FILTER (WHERE qty IS NULL) AS nulls FROM read_csv('late-text.csv', nullstr='n/a', types={'qty': 'INTEGER'})"┌───────┬────────┬───────┐
│ rows │ total │ nulls │
├───────┼────────┼───────┤
│ 40000 │ 980000 │ 1 │
└───────┴────────┴───────┘With nullstr='n/a' and qty declared INTEGER, all 40,000 rows loaded, the sum was 980,000 and exactly one value was NULL.
The second is a file that is not rectangular. A file where some rows have too few fields confused the sniffer so much that the default read returned a single column holding the whole line, with the commas inside it:
printf 'a,b,c\n1,2,3\n4,5\n6\n' > short.csv
duckdb -c "SELECT * FROM read_csv('short.csv')" 2>&1 | head -6
duckdb -box -c "SELECT * FROM read_csv('short.csv', null_padding=true)"┌─────────┐
│ a,b,c │
│ varchar │
├─────────┤
│ 1,2,3 │
│ 4,5 │
┌───┬──────┬──────┐
│ a │ b │ c │
├───┼──────┼──────┤
│ 1 │ 2 │ 3 │
│ 4 │ 5 │ NULL │
│ 6 │ NULL │ NULL │
└───┴──────┴──────┘null_padding=true told it to pad short rows with NULL and the three columns came back. The related options for damaged rows are in skipping bad rows while reading.
The third is dates, and the fourth is multiple files with different layouts. The date case, where 03/04/2025 is read day first by default and March 14, 2025 is not a date at all, is covered in reading mixed date formats. Files are covered next.
Many files at once
A glob in the path reads every match as one table, and a filename column says which file each row came from. I put five monthly files in a folder, two of them with a different layout: April has an extra column, May has the columns in another order.
duckdb -box -c "SELECT file FROM glob('monthly/*.csv') ORDER BY file"┌───────────────────────────┐
│ file │
├───────────────────────────┤
│ monthly/sales-2025-01.csv │
│ monthly/sales-2025-02.csv │
│ monthly/sales-2025-03.csv │
│ monthly/sales-2025-04.csv │
│ monthly/sales-2025-05.csv │
└───────────────────────────┘DuckDB 2.0 aligned the reordered file by name and read May correctly, and it dropped April's extra column silently when April came after a file without it. When the extra column came first, the other files could not supply it and the read failed with a schema mismatch and a suggestion to use union_by_name. Querying a folder of CSV files with a glob has all three outputs and the option.
Joining, and writing the result
Two CSV files are two tables. I joined the 2,000,000 row orders file to a 250,000 row customers file and summed revenue by customer segment in 0.29 s of wall time, with no import of either:
cd /batches/csv-0001-0200/work && time duckdb -box -c "SELECT c.segment, count(*) AS orders, round(sum(o.amount), 2) AS revenue FROM 'orders-2m.csv' o JOIN 'customers-250k.csv' c USING (customer_id) GROUP BY c.segment ORDER BY revenue DESC" 2>&1┌───────────┬────────┬─────────────┐
│ segment │ orders │ revenue │
├───────────┼────────┼─────────────┤
│ online │ 503514 │ 40309459.75 │
│ wholesale │ 500307 │ 40108102.92 │
│ partner │ 498710 │ 39931531.58 │
│ retail │ 497469 │ 39825552.11 │
└───────────┴────────┴─────────────┘
real 0m0.369s
user 0m0.551s
sys 0m0.191sThe keys are the hazard. Customer ids that differ by case or a stray space joined to nothing, and a join between a text key and an integer key failed with a conversion error. Joining two CSV files with SQL shows both and the normalizing fix. And the results leave the same way they came in: COPY (SELECT ...) TO 'out.csv' (HEADER) writes a CSV with the quoting rules from the CSV guide, as writing query results back to CSV shows.
View, table or Parquet
Three ways to keep a CSV queryable, and the choice follows from how the file changes. A view keeps no data: it stores the read_csv call, so it always reflects the file as it is now, costs nothing to create and pays the parse on every query. Use it for files that are replaced by a new export, where freshness matters more than speed. A table copies the data into DuckDB's own storage, which took 1.32 s and 51 MB for this file and made queries about five times faster. Use it when the file is a fixed snapshot, such as a month closed and signed off. Parquet is a table stored as a file you can hand to anyone: 34 MB, the types stored in the file, readable by DuckDB, pandas, Spark and most warehouses. Use it when the data leaves your machine or when several tools read it.
The deciding question is who else touches the data. If only you query it for an afternoon, leave it as a CSV. If a team queries the same monthly export, keep a view with a declared schema, so everyone gets the same types. If the file is the input to a pipeline, convert to Parquet at the first step and keep the CSV only as the archive of what arrived.
A view you can trust
For a file you will query more than once, write the schema down. This is the view I would keep in a file next to the data. It declares every column and type, turns on strict parsing, and ends with a check that fails the script when the row count is not the one a strict reader reported.
-- A reusable, defensive view over the orders export: declared columns, strict parsing, and a row count that must match.
CREATE OR REPLACE VIEW orders AS
SELECT * FROM read_csv('orders-2m.csv',
header = true, delim = ',', quote = '"', escape = '"', strict_mode = true, null_padding = false,
columns = {'order_id': 'BIGINT', 'customer_id': 'BIGINT', 'zip': 'VARCHAR', 'amount': 'DECIMAL(10,2)',
'created_at': 'TIMESTAMP', 'status': 'VARCHAR', 'country': 'VARCHAR', 'note': 'VARCHAR'});
-- Fail loudly when the file is not the size we expect (the expected count comes from a strict reader outside DuckDB).
SELECT CASE WHEN count(*) = 2000000 THEN 'row count ok: ' || count(*)
ELSE error('expected 2000000 rows, found ' || count(*)) END AS check_rows
FROM orders;
SELECT status, count(*) AS orders, round(sum(amount), 2) AS revenue FROM orders GROUP BY status ORDER BY revenue DESC;Run in the CLI, it prints the check and the report:
duckdb -box -c ".read orders_checked.sql"┌───────────────────────┐
│ check_rows │
├───────────────────────┤
│ row count ok: 2000000 │
└───────────────────────┘
┌──────────┬────────┬─────────────┐
│ status │ orders │ revenue │
├──────────┼────────┼─────────────┤
│ pending │ 400329 │ 32077864.50 │
│ refunded │ 400135 │ 32069815.84 │
│ shipped │ 399890 │ 32018038.17 │
│ failed │ 399533 │ 32017945.09 │
│ paid │ 400113 │ 31990982.76 │
└──────────┴────────┴─────────────┘The count of 2,000,000 came from qsv count, a strict CSV reader outside DuckDB. If the file had lost or gained rows, the error() call would stop the script. To show that, I changed the expected number and ran it again:
sed 's/2000000/2000001/g' orders_checked.sql > orders_wrong.sql
duckdb -c ".read orders_wrong.sql" 2>&1 | head -4; echo "exit status ${PIPESTATUS[0]}"Invalid Input Error: expected 2000001 rows, found 2000000
hint: -agent renders errors as JSON and results compactly for AI coding agents (duckdb -help lists all options)
exit status 1expected 2000001 rows, found 2000000 and exit status 1, so a cron job or a CI step fails instead of reporting numbers from a wrong file. The view itself can live in a DuckDB database file, which stores the definition and reads the CSV when you query it, so a team can share one .duckdb file of views over the exports and everyone sees the same schema.
Where each article fits
For the command line itself, see running SQL on a CSV and the read_csv options. For the guess, see sniffing and fixing wrong types. For awkward values, see mixed dates and skipping bad rows. For many files and for joins, see globbing a folder and joining two CSV files. For leaving CSV, see converting to Parquet and writing results back out. The loading routes for databases that hold the data themselves are in the PostgreSQL, MySQL and SQLite guides.
When a CSV is the wrong thing to query in place
Four cases. If the file changes while you read it, a growing log, for instance, a query sees whatever has been written so far, with a possibly truncated last line, and that is a case for a snapshot copy first. If many people query the same file all day, parse it once into Parquet or a table; the 0.19 s saved per query is small, and the consistency of one parsed copy is the larger benefit. If you need updates, constraints, transactions or concurrent writers, DuckDB reading a CSV is not a database for that, and the guides for PostgreSQL, MySQL and SQLite above are. And if the file is on a slow network disk, the parse speed is irrelevant and the transfer is the cost, so copy it locally or convert it once.
The last caution is the one this section keeps returning to. A fast, friendly reader makes it easy to skip the checks that a loader forces on you. The type guess, the sample, the silently dropped column and the join key that did not match are all invisible unless you look, and the habit that has served me is the one in the last section: freeze the schema, count the rows, and read a few of them.
