DuckDB made local analytics feel normal

DuckDB made a particular kind of work feel less like a side quest.

I mean the work where you have a folder of CSVs, Parquet files, logs, exports, or experiment results and you need answers before it deserves a warehouse table. The old options were all slightly annoying. Load it into Postgres. Write a pandas notebook. Copy it into a spreadsheet. Ask somebody with warehouse access. Build a little script and promise yourself you will delete it later.

DuckDB sits in a useful middle place. It lets local analytical work use SQL without turning every question into infrastructure.

That is the important part to me. Local analytics should still be engineering. It should leave a query, a result file, a reproducible path, and some idea of what changed. DuckDB does not automatically give you discipline, but it makes the disciplined path short enough that I am more likely to take it.

file-shaped data is real data

A lot of useful data arrives as files. Product exports, billing reports, benchmark results, crawler output, eval runs, security findings, trace samples, one-off vendor dumps, and compressed logs all start their lives somewhere outside a database.

The bad habit is treating that stage as temporary and therefore sloppy. The file sits in Downloads. A notebook reads it by absolute path. A chart gets pasted into Slack. Two weeks later somebody asks how the number was calculated and the answer is a shrug with a timestamp.

DuckDB changes the default because it can query files directly. A Parquet file can be a table for the duration of the query:

SELECT
  model,
  count(*) AS runs,
  avg(latency_ms) AS avg_latency_ms,
  quantile_cont(latency_ms, 0.95) AS p95_latency_ms
FROM 'eval-runs/*.parquet'
GROUP BY model
ORDER BY p95_latency_ms DESC;

That is a small thing, but it matters. The analysis can start where the data already is. I do not have to create a database service before I know whether the question is worth keeping.

CSV works too, although I still prefer to convert repeated work into Parquet once the shape stabilizes. CSV is an interchange format. Parquet is where I want larger local analytical loops to settle because types, compression, columnar reads, and partitioning make the work less fragile.

sql makes the notebook less magical

I like notebooks for exploration, but notebooks can hide too much state. Cells run out of order. Variables survive longer than they should. The data frame on screen may not match the code path someone else will run later. That does not make notebooks bad. It means the notebook is often the wrong artifact to preserve as the source of truth.

SQL has a useful bluntness. The query says what it reads, what it groups, what it filters, and what it writes. You can put it in a file. You can review a diff. You can run it from the CLI or a small script. You can attach a comment explaining the weird filter that excludes a broken export from last Tuesday.

For a local analysis, that might be enough:

CREATE OR REPLACE TABLE runs AS
SELECT *
FROM read_parquet('data/evals/2024-06/*.parquet');

COPY (
  SELECT
    suite,
    model,
    count(*) AS cases,
    avg(CASE WHEN passed THEN 1 ELSE 0 END) AS pass_rate
  FROM runs
  WHERE environment = 'prod-like'
  GROUP BY suite, model
) TO 'artifacts/eval-summary.csv' (HEADER, DELIMITER ',');

The artifact is boring in the best way. A future version of me can run the query again and compare the output. A reviewer can ask why prod-like is the filter. A script can fail if the expected input folder is missing. The local analysis starts behaving like a small build step.

local does not mean casual

The word “local” can make people lower their standards. That is a mistake.

Local analytical work still needs names. It needs folders that make sense. It needs input files that are not silently edited. It needs result files that are either ignored because they are disposable or checked in because they are part of the argument. It needs enough README context that somebody else can reproduce the number without reading your mind.

I like a shape like this:

analysis/
  eval-latency/
    README.md
    queries/
      summary.sql
      slow-cases.sql
    data/
      .gitignore
    artifacts/
      summary.csv
      slow-cases.parquet

The data folder might contain large local inputs that never get committed. The queries folder is reviewable. The artifacts folder is a choice: commit small summaries when they support a post, decision, or investigation; ignore large derived files when they are easy to regenerate.

DuckDB fits that shape because the database can be ephemeral or file-backed. For many analyses, I do not need a long-lived database file at all. The query reads source files and writes a result. For repeated work, a .duckdb file can cache intermediate tables and make exploration faster. The trick is to decide which mode you are in instead of letting state accumulate accidentally.

schemas still matter

Direct file querying is convenient until a column changes under you.

CSV inference can guess wrong. Dates become strings. IDs become numbers and lose leading zeros. Empty columns look like nulls until a later file fills them. Parquet is better, but a directory of Parquet files can still drift when producers add fields, change meanings, or backfill old data with a different writer.

The local workflow needs a schema check when the result matters. That can be as simple as a query that fails loudly:

SELECT
  assert_true(count(*) = 0, 'missing model') AS model_check
FROM runs
WHERE model IS NULL;

Or it can be a small validation query that reports the shape before the real summary runs:

SELECT
  typeof(model) AS model_type,
  typeof(latency_ms) AS latency_type,
  min(created_at) AS first_run,
  max(created_at) AS last_run,
  count(*) AS rows
FROM runs;

The point is not to build a warehouse governance program around one local analysis. The point is to catch the cheap mistakes. If a chart decides whether a model route changes, I want to know whether half the rows had null labels before I trust the chart.

where duckdb fits in ai work

DuckDB is handy around AI systems because AI work produces piles of semi-structured evidence.

Eval runs create rows. Agent traces create rows. Tool calls create rows. Retrieval experiments create rows. Human review queues create rows. Prompt variants create rows. Most of that data starts life as JSONL, CSV, or Parquet before anyone knows which part will matter.

The loop I like is:

1. Capture events as append-only files.
2. Query them locally while the question is still forming.
3. Save the SQL that produced a useful answer.
4. Promote the dataset or metric only when it repeats.

That keeps early analysis close to the engineer doing the work. It also prevents the warehouse from becoming a junk drawer for every experiment.

For example, an agent eval might dump one JSON object per case:

{"case_id":"a17","model":"small-router","route":"docs","passed":true,"latency_ms":42}
{"case_id":"a18","model":"small-router","route":"code","passed":false,"latency_ms":39}

DuckDB can read that into a table, join it against case metadata, and write a failure slice without a service migration. If that slice becomes an official metric, then it deserves stronger ownership. Until then, local SQL is enough to learn.

when local stops being enough

DuckDB does not remove the need for shared infrastructure. It delays the decision until there is evidence.

Local analytics starts to break down when the dataset is too large for the machine, when multiple people need concurrent writes, when access control matters, when freshness has a service-level expectation, or when downstream systems depend on the result. At that point the work has become a data product, and it should move into a warehouse, lakehouse, or application database with the usual boring machinery around ownership and lineage.

That promotion should be a design decision, not a reflex. Plenty of analysis should die as a local folder. Some should become a scheduled job. A smaller amount should become shared data infrastructure.

DuckDB is useful because it makes the first stage respectable. You can answer a question locally without pretending the answer is production infrastructure. You can also keep enough structure that, if the question keeps coming back, the path to production is visible.

the habit i want

The habit is simple: write the query down.

If a number matters enough to mention in a meeting, a post, a PR, or a decision record, the query that produced it should probably exist somewhere. DuckDB makes that easy for file-backed analytical work. It lets the first honest version of the analysis happen before the platform conversation starts.

That is why DuckDB 1.0 felt bigger than a database release to me. It made local analytics feel normal: SQL over local files, result artifacts on disk, and fewer excuses for spreadsheet archaeology.