Data Wrangling: DuckDB

Florian Oswald

13 October, 2026

Where we are

S4: how an LLM actually works (tokens in, tokens out, no ground truth inside).
S5: how an agent wraps that model with tools, context, and a harness.
Today the tool is a database, and the task is data wrangling: getting from raw files to trustworthy numbers.

Today’s habit, again

The agent will happily write a query that runs without error and returns a plausible number. Running without error is not the same as being right.

Today’s plan

What DuckDB is, and why it is a good fit for this course.
One dataset, three ways to query it: SQL, dplyr, Ibis.
Let the agent draft a query. Find out it’s wrong. Fix it.
You do the same, on your own query, and write it down.

What is DuckDB?

DuckDB in one slide

An in-process analytical (OLAP) SQL database — like SQLite, but built for aggregations over large columnar data instead of many small transactions.
No server to install, no daemon to start: import duckdb (Python), library(duckdb) (R), or the duckdb CLI binary.
Reads CSV / Parquet / JSON directly, without a separate load step:

SELECT * FROM 'data/brexit.csv' LIMIT 5;

Fast, columnar, and free — a real analytical engine on your laptop.

Why DuckDB for this course

R and Python both talk SQL fluently through DuckDB — it’s a genuine common ground between the two languages this course uses.

The same question can be asked three ways:
- raw SQL — the lingua franca, and what the agent tends to reach for first.
- dplyr verbs (R) translated to SQL under the hood.
- Ibis expressions (Python) translated to SQL under the hood.

All three compile to the same DuckDB query plan. If they disagree, one of you (or the agent) made a mistake — that disagreement is itself a verification tool.

Setup

SQL / CLI

duckdb

library(duckdb)
library(dplyr)
con <- dbConnect(duckdb())
polls <- tbl(
  con,
  "read_csv_auto('data/brexit.csv')"
)

Python

import duckdb
import ibis

con = ibis.duckdb.connect()
polls = con.read_csv(
  "data/brexit.csv",
  table_name="polls"
)

All three point at the same file — no import/export, no separate database to keep in sync.

The dataset

UK EU-referendum polls (2016)

data/brexit.csv: 127 opinion polls conducted between January and June 2016, ahead of the UK’s “Brexit” referendum.
Columns: pollster, poll_type, startdate/enddate, samplesize, remain, leave, undecided, spread.
Known answer: the actual referendum result was Leave 51.89%, Remain 48.11% — a fixed, external fact we can check any query’s output against.

5 records
pollster	enddate	samplesize	remain	leave	undecided
YouGov	2016-06-23	4772	0.52	0.48	0.00
Populus	2016-06-22	4700	0.55	0.45	0.00
YouGov	2016-06-22	3766	0.51	0.49	0.00
Ipsos MORI	2016-06-22	1592	0.49	0.46	0.01
Opinium	2016-06-22	3011	0.44	0.45	0.09

Ask the agent: “what does this data predict?”

Prompt given to the agent (OpenCode):

“Write a DuckDB SQL query against data/brexit.csv that estimates the referendum outcome — the percentage voting Leave and the percentage voting Remain.”

The agent’s first draft — plausible, runs without error:

SELECT
  round(avg(leave)  * 100, 2) AS leave_pct,
  round(avg(remain) * 100, 2) AS remain_pct
FROM 'data/brexit.csv';

Running the agent’s first query

1 records
leave_pct	remain_pct
42.23	44.24

leave_pct = 42.23, remain_pct = 44.24. Looks like a plausible pair of vote shares. Do not trust it yet.

Verify before you trust it

The cheapest check costs one extra line: do the shares add up to 100%?

1 records
leave_pct	remain_pct	undecided_pct	total_pct
42.23	44.24	12.65	99.11

They do (≈100%, modulo rounding) — but the query silently dropped undecided respondents from the two numbers it reported. leave_pct + remain_pct alone is only 86.5%, not 100%. The agent answered the wrong question: “average raw share” instead of “share of those who expressed a preference.”

What actually went wrong

No re-normalization: it reported leave and remain shares out of all respondents (including undecided), not as shares of decided voters — the quantity we actually asked for.

No weighting: AVG() treats a poll of 500 people the same as a poll of 5,000 — every poll gets an equal vote, regardless of samplesize.

No date filtering: it averaged polls from January and June together, as if opinion in January (5+ months before the vote) were as informative as opinion the week before.

None of these threw an error. All three are silent, plausible-looking mistakes — exactly the kind unit tests and invariant checks (S7–S8) are built to catch.

Fixed query — SQL

1 records
leave_pct	remain_pct	n_polls
48.61	51.39	15

Re-normalized (excludes undecided from the denominator), sample-size weighted, restricted to the final week.

Same query — dplyr (R)

# Source:   SQL [?? x 3]
# Database: DuckDB 1.4.2 [floswald@Darwin 24.6.0:R 4.5.1/:memory:]
  leave_pct remain_pct n_polls
      <dbl>      <dbl>   <dbl>
1      48.6       51.4      15

tbl(con, ...) on a DuckDB connection means these dplyr verbs are translated to SQL and run inside DuckDB — dplyr::show_query(weighted_r) prints the generated SQL, useful for checking the agent’s dplyr code actually does what you think.

Same query — Ibis (Python)

   leave_pct  remain_pct  n_polls
0      48.61       51.39       15

Same numbers as the SQL and the dplyr version — three languages, one DuckDB query plan.

Check against the known answer

Fixed query, final week of polling: Leave ≈ 48.6%, Remain ≈ 51.4%.
Actual result: Leave 51.89%, Remain 48.11%.

The query is now correct. The prediction is still wrong.

Fixing the SQL bug (undecided handling, weighting, date window) got us a defensible number — but it’s still on the wrong side of the actual outcome. 2016 UK polls famously under-counted Leave support (non-response bias, “shy” Leave voters, herding among pollsters).

This is the distinction to keep: verification catches query bugs (wrong denominator, wrong weights, wrong date range — things you can check against an invariant). It does not catch measurement error in the data itself — that needs domain knowledge, not a better query. Keep this distinction for S9 (cost/quality tradeoffs) and S11 (cataloguing failure modes).

Your turn

Exercise (20 min)

Working with the same brexit.csv (or a dataset of your choice with a known, checkable answer):

Give your agent a data question and let it draft a first query — SQL, dplyr, or Ibis, your choice.
Run it. Get a number.
Before you believe it, write down at least one check: does it sum to what it should? Does it match a known external fact? Does a second language (SQL vs. dplyr vs. Ibis) give the same answer?
If the first answer fails a check, find out why, fix the query, and re-run.

Ideas to try: split by pollster or poll_type (phone vs. online — did they differ systematically?), compute a rolling weekly average, or compare spread in the data to leave - remain you compute yourself (does the pollster’s number match your recomputation?).

Deliverable: the written note

For the session outcome, write down one concrete case where the agent’s first answer was wrong, in this format:

Template

Prompt you gave the agent.
First query it produced (SQL/dplyr/Ibis).
First output — the specific wrong number(s).
How you caught it — the check that failed (invariant, known answer, cross-language comparison, or something else).
Fixed query and the corrected output.

This is not a hypothetical exercise — use whatever actually happened when you ran your own prompt. If your agent got it right first try, push it further (harder aggregation, trickier date logic, a join) until it doesn’t.

Wrap-up

DuckDB gives you one query engine reachable from SQL, R (dplyr), and Python (Ibis) — no data export/import between them.
The agent’s first query is a draft, not an answer. It ran without error and still ignored undecided respondents, sample weights, and the relevant date window.
Checking against an invariant (do shares sum to 100%?) or a known external fact (the actual referendum result) is cheap and catches real bugs.
Even a correct query only answers the question you asked — it can’t fix bias in the underlying data.
Next (S7): turning these ad-hoc checks into a proper, repeatable test suite.