Why Does It Read Every File? in Python

The interviewer asks: "The orders table is partitioned by day, yet a query for one day still reads every file. Why, and how do you fix it?" A partition is a folder that holds the rows of one value of a column. The starting code writes the 78 orders into stg_orders/ with PARTITION_BY (day): one folder per day, named like day=2026-05-04, where day is the day the order was placed. This column=value naming is called hive partitioning, and read_parquet('stg_orders/*/*.parquet', hive_partitioning = true) turns each folder name back into a day column.

Partition pruning means skipping folders: when the WHERE filters on the partition column itself, DuckDB picks the folders by their names and never opens the others. A filter on a column stored inside the files can't do that, because DuckDB has to open a file to see its values. An example on another table, events, partitioned by country:

events/country=DE/   events/country=FR/   events/country=RO/
WHERE country = 'FR'   →  opens 1 folder: country is in the folder names
WHERE city = 'Lyon'    →  opens all 3: city is inside the files

The trap: most people say "it's partitioned by day, so a date filter is cheap". But slow_query filters on CAST(ordered_at AS DATE), and ordered_at sits inside the files, so it reads all 7 files for one day's answer. At work, a table holds years of days: a query that opens every file is slow, and on a cloud warehouse that bills by the data scanned, it is expensive too.

To count files, files_read(query) runs the query under EXPLAIN ANALYZE, which runs it and reports what each step of the plan really did, and returns the plan's Total Files Read. A file count stays the same on every run; a time doesn't. Your code and the check print:

slow_query files: 7
query files: 1
Same answer as slow_query: True
status | orders
cancelled | 2
paid | 7
(2 rows)

Your task: print how many files slow_query reads, then how many query reads, and give query a WHERE that keeps 2026-05-04 by filtering on the partition column day.

Assignment
Follow the Coding Tutorial to measure the files both queries read and to make query read one folder instead of seven.

Hint
A filter can skip a folder only if it uses the column the folders are named by: day, not ordered_at. A date goes in quotes, like '2026-05-04'.