DuckDB Pipelines for Journalism

1. What DuckDB is

DuckDB is a free, open-source analytical database that runs insidewhatever you are already using — the command line, Python or R — with no server to install or keep alive. It stores data in columns rather than rows, which makes the counts, sums and group-bys at the heart of journalism very fast, and it speaks standard SQL. If SQLite is the pocket database for transactional work, DuckDB is its analytical cousin.

Its defining feature for reporters: it can query a CSV, Parquet or JSON file directly, without importing it first. Download the single binary from duckdb.org.

2. Query a CSV without loading it

Point a query straight at a file path. DuckDB reads it on the fly, inferring column names and types, and nothing is copied into a database first.

-- read a council spending CSV in place
SELECT service_area, sum(amount) AS total
FROM 'council-spending.csv'
GROUP BY service_area
ORDER BY total DESC;

-- or be explicit with the reader for control over options
SELECT count(*) FROM read_csv_auto('council-spending.csv');

read_csv_auto() auto-detects delimiters, headers and types; drop to read_csv() with explicit options when a messy file needs a firm hand.

3. Parquet, JSON and many files at once

Parquet is a compact columnar file format that DuckDB reads especially fast. You can also glob many files into one virtual table — useful for a folder of monthly exports.

-- a single Parquet file
SELECT * FROM 'transactions.parquet' LIMIT 20;

-- every monthly CSV in a folder, as one table
SELECT * FROM read_csv_auto('spending/2024-*.csv');

-- read line-delimited JSON
SELECT * FROM read_json_auto('records.json');

4. Reading remote files

With the httpfsextension, DuckDB can query a file over HTTPS without downloading the whole thing first — handy for a large published Parquet dataset.

INSTALL httpfs;
LOAD httpfs;

SELECT count(*)
FROM read_parquet('https://example.org/open-data/latest.parquet');

Be considerate with remote sources: filter early so you only pull the rows and columns you need, and cache a local copy if you will query it repeatedly.

5. Using the command-line tool

Launch DuckDB with no arguments for an in-memory session, or pass a filename to persist a database. Dot-commands control output and run scripts.

# start an in-memory session
duckdb

# inside the shell: pretty output, timing, and run a script
.mode box
.timer on
.read pipeline.sql

# or run one query straight from the shell and exit
duckdb -c "SELECT count(*) FROM 'council-spending.csv'"

6. From Python and R

DuckDB has first-class bindings for both languages, so you can run SQL over files and hand the result to pandas or a tibble for charting.

# Python: query a file, get a pandas DataFrame back
import duckdb
df = duckdb.sql("SELECT service_area, sum(amount) AS total "
                "FROM 'council-spending.csv' GROUP BY service_area").df()

# R: connect via DBI and query into a data frame
library(duckdb)
con <- dbConnect(duckdb())
res <- dbGetQuery(con, "SELECT * FROM 'council-spending.csv' LIMIT 100")
dbDisconnect(con, shutdown = TRUE)

7. When DuckDB beats pandas or Postgres

• 1A one-off question over a large CSV or Parquet file, where standing up a Postgres server is overkill.
• 2Data larger than your computer's memory, which DuckDB streams rather than loading whole like pandas.
• 3Aggregations -- sum, count, group-by -- over millions of rows, where columnar storage shines.
• 4Joining two files of different formats (CSV and Parquet) without importing either.
• 5A reproducible pipeline you can commit as a .sql file and re-run when the data refreshes.

8. A simple reproducible pipeline

Put the whole analysis in one pipeline.sql file: read the source in place, aggregate, and export the tidy result to a new file. Run it with duckdb -c ".read pipeline.sql" or from inside the shell.

-- pipeline.sql: source stays untouched, output is a new file
COPY (
  SELECT service_area,
         count(*)          AS payments,
         round(sum(amount)) AS total
  FROM 'council-spending.csv'
  WHERE amount > 25000
  GROUP BY service_area
  ORDER BY total DESC
) TO 'spend-by-area.csv' (HEADER, DELIMITER ',');

Swap the output to Parquet with (FORMAT parquet) if the next step is another query. Commit pipeline.sql to version control and anyone can reproduce the numbers.

9. Red flags

• ⚠Trusting auto-detected types on a messy file; check the schema and cast columns like amounts and dates explicitly.
• ⚠Hammering a remote file repeatedly instead of caching a local copy once you know you will reuse it.
• ⚠Forgetting an in-memory session vanishes on exit; pass a filename or export results if you want to keep them.
• ⚠Overwriting the source file with your output; always COPY to a new filename.
• ⚠Assuming a headline number without a sanity check; confirm count(*) against the source row count.

10. Pipeline checklist

• I have downloaded the DuckDB binary from duckdb.org.
• I have inspected the inferred schema and cast amount and date columns explicitly where needed.
• I have confirmed count(*) matches the source file's row count.
• I have written the analysis as a .sql script rather than ad-hoc console commands.
• I have exported results to a new file and left the raw download untouched.
• I have committed the .sql pipeline to version control.

Related guides

Frequently asked questions