How to Analyze CSV Files with DuckDB

DuckDB is a free, zero-configuration SQL database that runs on your local machine and reads CSV files directly without any import step. Download the CLI binary for your operating system, launch it from your terminal, and query your file with standard SQL. No Python, no server, no schema definition required. DuckDB's sniffer detects column names and types automatically and handles files from a few rows to several gigabytes.

The main reason analysts reach for DuckDB instead of a spreadsheet tool is that it runs real SQL, including window functions, PIVOT, recursive CTEs, and JOIN operations across multiple CSV files, all without spinning up a database server or writing a single line of Python.

Install DuckDB

DuckDB ships as a single binary with no dependencies. Installation takes under a minute.

On macOS with Homebrew:

brew install duckdb

On Linux, download the precompiled CLI binary from the DuckDB releases page at duckdb.org/docs/installation. Place the binary somewhere on your PATH and make it executable:

chmod +x duckdb
mv duckdb /usr/local/bin/

On Windows, download the duckdb_cli-windows-amd64.zip from the same releases page, unzip it, and run duckdb.exe from any folder.

Verify the installation:

duckdb --version

As of April 2026, DuckDB v1.5.2 is the current stable release. Start an interactive session:

duckdb

To save work across sessions in a persistent file, pass a filename when starting:

duckdb analysis.db

All queries run in either mode, but the persistent file lets you create views and tables that survive between sessions.

Your First CSV Query

Once inside the DuckDB shell, reference any CSV file path directly in your FROM clause. No import step is needed:

SELECT * FROM 'sales.csv' LIMIT 10;

DuckDB's CSV sniffer reads the first several hundred rows to detect column names, data types, delimiters, and whether the file has a header row. This auto-detection works reliably for standard comma-separated and tab-separated files with no configuration.

To count all rows without loading the full dataset into memory:

SELECT COUNT(*) FROM 'sales.csv';

To see which columns exist and what types DuckDB inferred:

DESCRIBE FROM 'sales.csv';

If DuckDB infers a column type incorrectly, override it with the read_csv function:

SELECT * FROM read_csv('sales.csv', columns = {'order_date': 'DATE', 'revenue': 'DOUBLE'});

DuckDB also queries CSV files directly over HTTPS, which is useful for accessing public datasets without downloading them first:

SELECT * FROM 'https://example.com/data/public_dataset.csv' LIMIT 5;

Explore File Structure Automatically

Before writing any analysis query, run SUMMARIZE to get a full statistical profile of every column in one step:

SUMMARIZE FROM 'sales.csv';

This returns count, null count, minimum, maximum, mean, standard deviation, and approximate distinct counts for every column in a single output. For any unfamiliar file, this replaces the manual step of opening the file in a spreadsheet to understand its shape.

To see which values appear most often in a specific column:

SELECT category, COUNT(*) AS n
FROM 'sales.csv'
GROUP BY category
ORDER BY n DESC
LIMIT 10;

Aggregate and Summarize Data

DuckDB supports the full range of SQL aggregate functions including window functions, which most file-based tools do not support.

A typical analyst workflow:

-- Total revenue by region
SELECT region, ROUND(SUM(revenue), 2) AS total_revenue
FROM 'sales.csv'
GROUP BY region
ORDER BY total_revenue DESC;

-- Monthly revenue trend
SELECT DATE_TRUNC('month', sale_date) AS month, SUM(revenue) AS revenue
FROM 'sales.csv'
GROUP BY month
ORDER BY month;

-- Each region's share of total revenue
SELECT region,
       SUM(revenue) AS revenue,
       ROUND(100.0 * SUM(revenue) / SUM(SUM(revenue)) OVER (), 2) AS pct_of_total
FROM 'sales.csv'
GROUP BY region
ORDER BY revenue DESC;

Window functions like SUM(...) OVER () follow standard PostgreSQL syntax, so existing SQL knowledge transfers directly without learning new syntax.

Filter, Sort, and Find Outliers

Standard WHERE clauses, date filtering, and pattern matching work as expected:

-- Filter by a date range
SELECT * FROM 'sales.csv'
WHERE sale_date BETWEEN '2025-01-01' AND '2025-12-31';

-- Find rows with unusually high values (beyond two standard deviations)
SELECT * FROM 'sales.csv'
WHERE revenue > (SELECT AVG(revenue) + 2 * STDDEV(revenue) FROM 'sales.csv');

-- Find missing values across all columns
SELECT COUNT(*) - COUNT(customer_id) AS missing_customer_id,
       COUNT(*) - COUNT(revenue) AS missing_revenue
FROM 'sales.csv';

The outlier query identifies statistical anomalies in a single SQL statement, without any Python library.

Join Two CSV Files

DuckDB joins CSV files the same way it joins database tables:

SELECT o.order_id, o.revenue, c.region, c.tier
FROM 'orders.csv' o
JOIN 'customers.csv' c ON o.customer_id = c.id
WHERE c.tier = 'enterprise'
ORDER BY o.revenue DESC;

DuckDB uses hash joins with parallel execution across all available CPU cores. A join between two 1 GB CSV files completes in seconds on a standard laptop, which would take minutes in a spreadsheet tool or require a dedicated database server in most other setups.

Query Multiple CSV Files at Once

If your data is split across monthly or weekly export files, DuckDB reads all of them in a single query using a glob pattern:

SELECT * FROM '2025_*.csv' LIMIT 5;

When files have slightly different column sets across periods, use union_by_name=true to align them by column name automatically:

SELECT year, region, SUM(revenue) AS revenue
FROM read_csv('quarterly_*.csv', union_by_name=true)
GROUP BY year, region;

This replaces the common workaround of manually stacking files in a spreadsheet before analysis.

Handle Large Files Without Running Out of Memory

DuckDB uses lazy evaluation and reads only the rows and columns it needs to answer a given query. Aggregation queries on multi-gigabyte files typically do not load the entire dataset into memory.

For very large files, set an explicit memory limit and thread count:

SET memory_limit = '4GB';
SET threads = 4;

If the dataset exceeds available RAM, DuckDB spills intermediate results to disk automatically. You can specify a faster disk location for spill files:

SET temp_directory = '/path/to/fast/disk';

For repeated analysis of the same large file, convert it to Parquet once. Parquet is a columnar format that is typically 5 to 10 times smaller than the original CSV and significantly faster to scan for column-selective queries:

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

Subsequent queries against the Parquet file run noticeably faster than re-reading the CSV on each run.

Export Query Results

Export any query result to CSV or Parquet:

-- Export a summary to CSV with a header row
COPY (
  SELECT region, ROUND(SUM(revenue), 2) AS total
  FROM 'sales.csv'
  GROUP BY region
  ORDER BY total DESC
) TO 'summary.csv' (HEADER, DELIMITER ',');

-- Export a filtered subset to Parquet for downstream use
COPY (SELECT * FROM 'sales.csv' WHERE year = 2025) TO 'sales_2025.parquet' (FORMAT PARQUET);

The HEADER option includes column names in the first row of the output file. The Parquet export is useful when passing results to a BI tool or another analyst who also uses DuckDB.

DuckDB in April 2026: What Changed

DuckDB v1.5.2, released in April 2026, brings performance improvements alongside production-ready status for DuckLake, a companion lakehouse catalog format. DuckLake stores table metadata in a SQL database rather than scattered files in object storage, which delivers 8 to 258 times faster COUNT(*) queries via metadata-only execution. It also introduces sorted tables, bucket partitioning, and data inlining to eliminate the small-files problem common in streaming insert workflows. The ducklake extension is now among DuckDB's top-10 core extensions by download volume.

For teams that have outgrown individual CSV files and want a structured data lake with multiple contributors, DuckLake adds versioned, transactional storage while keeping the same DuckDB SQL interface they already use for CSV files.

If writing SQL against raw files is not your preference, VSLZ AI lets you upload the same CSV and describe what you need in plain English, returning charts and statistical summaries without any command-line setup.

Summary

DuckDB gives SQL-fluent analysts a fast, self-contained tool for querying, joining, filtering, and exporting CSV files without Python or a database server. The core workflow is: install the CLI binary, run duckdb, and write standard SQL against your file path. For large files, convert to Parquet once and query the Parquet file thereafter. For teams scaling beyond individual files, DuckLake v1.0 adds production-ready lakehouse storage with substantial performance gains over traditional catalog formats. DuckDB v1.5.2 is the current stable release as of April 2026.