Week 4: DuckDB

DSAN 6000: Big Data and Cloud Computing
Fall 2026

Jeff Jacobs

jj1088@georgetown.edu

Monday, September 21, 2026

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

From Berk (2022)

DuckDB Demo

Open in Colab

References

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