Imagine this. You work at a major electronics store, and you want to know if it's worthwhile to market ipads to kids. So you call up head office and ask for sales data - you want to know, how many kids bought ipads at the NYC location - and how has it changed over time.

In a surprising act of trust (and gross privacy violations) head office sends you over a 1TB zip file with daily sales reports from every store since the beginning of recorded history - as CSVs. For argument's sake, it's a million files, with 10,000 rows each, a billion rows total.

How do you get the data you need?

The obvious and naive way to do it would be to open every file, read every row left to right and evaluate whether that row should be part of your data set.

Imagine how much faster this process would be if the data was sorted like this¹

sales_reports/
├── date=2026-01-15/
│   ├── location=NYC/
│   │   ├── part-00000.parquet
│   │   ├── part-00001.parquet
│   │   ├── part-00002.parquet
│   │   ├── ...
│   │   ├── part-00047.parquet
│   │   ├── part-00048.parquet
│   │   └── part-00049.parquet
│   ├── location=LAX/
│   │   ├── part-00000.parquet
│   │   ├── part-00001.parquet
│   │   ├── ...
│   │   ├── part-00022.parquet
│   │   └── part-00023.parquet
│   └── location=CHI/
│       ├── part-00000.parquet
│       ├── part-00001.parquet
│       ├── ...
│       ├── part-00014.parquet
│       └── part-00015.parquet
├── date=2026-01-16/
│   ├── location=NYC/
│   │   ├── part-00000.parquet
│   │   ├── part-00001.parquet
│   │   ├── ...
│   │   ├── part-00051.parquet
│   │   └── part-00052.parquet
│   ├── location=LAX/
│   │   ├── part-00000.parquet
│   │   ├── ...
│   │   └── part-00019.parquet
│   └── location=CHI/
│       ├── part-00000.parquet
│       ├── ...
│       └── part-00017.parquet
└── date=2026-01-17/
    ├── location=NYC/
    │   ├── part-00000.parquet
    │   ├── ...
    │   └── part-00045.parquet
    ├── location=LAX/
    │   ├── part-00000.parquet
    │   ├── ...
    │   └── part-00021.parquet
    └── location=CHI/
        ├── part-00000.parquet
        ├── ...
        └── part-00018.parquet

If you wanted to look at data for 2026-01-15 for NYC, it's as simple as traversing down two folders.

However, you still have tens to hundreds of files, each with ~10k rows and probably lots of columns. How do you quickly find the data you're looking for?

Well, imagine if you hovered over the file and it told you statistics about the data inside, for example - "in this file, 0 iPads were sold" or "in this file, all the buyers are over 18". It'd be a little tedious, but especially if you had some colleagues to help you - you each could look at one file and decide whether it was worth looking at or not.

But let’s go further. It’s normal to pack everything you know about a sale into one row (known as a wide event), and with modern day tracking, there are probably a lot of columns you don’t need like time_entered_store / time_left_store and unexpected ones like customer_household_income and customer_bmi — easily 50-100 columns per sale. The naive approach is: for each row, check if the column is the column you care about, and if it is, check if it has the value you’re looking for — if not, keep looking. If you’re lucky the columns you care about will be one of the first few columns, but often they aren’t.

But imagine if you could slice the spread sheet into multiple slices like french toast sticks.

You could hand out each slice to your coworkers and say, ok you tell me which rows have age < 18 and you tell me which rows have product SKU == "iPad". Coworker 1 says rows [1, 5, 7] coworker 2 says [5, 7, 12] you take the intersection of these sets and come up with your answer [5,7] or in this contrived example, 2 kids bought ipads in NYC on 2026-01-15.

#Explain It Like I’m a Developer

If this all sounds too good to be true, you’ll be surprised to hear it’s not. Let’s go through an example.

You get an S3 URL to a bucket with sales data stored how it is above. You have DuckDB installed and you run duckdb -c “select count(*) from 's3://big-company-sales-data/sales_reports/**/*.parquet' where age < 18 and location = 'NYC' and date = '2026-01-15'”

This creates a pushdown predicate in which DuckDB will:

• Fetch a list of top level keys in S3 looking for a key called sales_reports
• Fetch a list of keys in sales_reports looking for partition keys like date= and location=
• Finding date=2026-01-15, it prunes all other date partitions
• Within that, finding location=NYC, it prunes all other location partitions
• Now with only the relevant parquet files, it reads the footer of each file (quite probably many in parallel) to see the row statistics — metadata about which columns exist and which range of values their rows contain
• Knowing which files do and don’t have the values of interest, it uses the row statistics to compute the byte ranges of the columns of interest, i.e. bytes 0x0-0x20 have all the ages and 0x100-0x200 have all the product SKUs
• Instead of coworkers, computers have many cores, so for each file of interest it spawns a thread that fetches the byte ranges of interest for the columns of interest and returns the IDs of the rows that match
• Take the intersection of those rows and then count how many values are left

In a recent example I queried a 10gb dataset with 25 million rows in under 3 seconds with this process.