Week 4: DuckDB

DSAN 6000: Big Data and Cloud Computing
Fall 2026

Class Sessions

Author

Affiliation

Jeff Jacobs

jj1088@georgetown.edu

Published

Monday, September 21, 2026

Open slides in new tab →

Computer Architecture in One Diagram

*Possibly the last non-AI diagram Jeff will make*

Storage ↑ higher on pyramid is faster but more expensive
Registers, Caches built into processor
Everything from RAM upwards uses electricity ⚡️, dies when unplugged
Solid-State Drives (SSDs) have no moving parts (“flash memory”)
Hard-Drive Disks (HDDs) have moving parts: “heads” which scan over spinning magnetic disk then read data sequentially

The Problem With CSVs (for Data Engineers)

Inherent ambiguities…	No built-in way to communicate schema info!
Delimiter: Comma? Tab? Semicolon?	Schema information: Sent or looked up separately (codebooks)
Quote characters: Single `'` or double `"` quote? ASCII `""` or UTF-8 `“”`?	Nested structures: …Improvised and included in schema
Escaping special characters: `\n` = new line, or part of string val?

\(\leadsto\) Data engineers working with CSV data have to build robust error detection systems to ensure data quality (Pydantic! Pandera!)

The Problem With CSVs (for Data Scientists)

Brainstorming exercise: What kinds of operations do you think of when you think of “doing” data science?
For me: mean(), std(), max(), min(), etc…
Computing SSRs: (df['y'] - df['y_pred']) ** 2
These are columnar operations, yet .csv files are read row-by-row

How CSVs Are Actually Stored on Disk

dsan6000_staff.csv

staff_id,last,first,points\n1,jacobs,jeff,300\n2,vakkalanka,samyu,400\n3,wang,fangzhou,500\n4,wu,siru,600

…Even if we only want to compute average of last column, computer has to scan over all data in row first (remember: sequential memory reads)

…But Imagine A World Where… (John Lemon, from Beetles)

dsan6000_staff.csv

points,300,400,500,600\nstaff_id,1,2,3,4\nlast,jacobs,vakkalanka,wang,wu\nfirst,jeff,samyu,fangzhou,siru

…But I completely cheated here! I moved the last column to be the first column. How could we achieve this in practice?

The Skip List Approach

(Note: If you took DSAN 5000, you saw the skip list data structure… here we’re generalizing this to DataFrames!)

Now imagine a fancier header, with an index telling the reader the exact byte where each column starts:

column name	column start
`staff_id`	0
`last`	18
`first`	50
`points`	82

dsan6000_staff.csv

staff_id,1,2,3,4\nlast,jacobs,vakkalanka,wang,wu\nfirst,jeff,samyu,fangzhou,siru\npoints,300,400,500,600

\(\leadsto\) Now the reader can just skip over bytes 0-81!

Even more efficient if we implement index as Binary Search Tree

Sidenote Now, Important Later

If we did have a setup where we wanted to operate over rows, this optimized-index approach would work just as well!

Sorted Data:

If sorted by staff_id: 1000 employees total, employee 500 starts at byte 820
\(\leadsto\) (Higher ids guaranteed to be in later bytes)

Time Series Data:

2020 data starts at byte 0
2021 data starts at byte 458
2022 data starts at byte 1810
2023 data starts at …

Binned Data: Last names N-Z start at byte 1130

(Faster insertions; Can’t make guarantees besides bin start/end)

The Secret Columnar Sauce: RLE Compression

With row-based storage, we “hop” from one data type to another with each subsequent read
- 1 → "jeff" → 300 → date(2023)
With columnar storage, sequential blocks have same data type! \(\leadsto\) Run-Length Encoding (RLE)
2 million consecutive records with date(2023) becomes (date(2023), 2000000) 🤯
10 years of records: 20 million bytes → 20 bytes

Dream No More… Parquet Files Have Arrived

Hybrid Columnar/Row-Based Storage

DuckDB Demo

Open in Colab

References

Berk, Michael. 2022. “Demystifying the Parquet File Format.” Towards Data Science. August 16, 2022.