Storage engineering

Partitioning writes each group of rows to its own file (or folder of files) keyed by a column. A query that filters on that column reads only the matching partitions - it prunes the rest. Pick the column queries filter on most, and pick a granularity that keeps partitions a sensible size.

• One big file is bad: every query, however selective, must scan the whole thing. No pruning is possible - the engine cannot skip what is not separated.
• A million tiny files is worse: over-partition (say, by the second) and you drown in file overhead - each file has a cost to open and read metadata for. The engine spends more time managing files than reading data.
• The sweet spot: partitions large enough to be worth opening, small enough that a typical query skips most of them. Date partitions (day or month) hit this for most time-series data.

Beyond which column, file layout matters. Columnar formats like Parquet (b4) store row groups you can skip within a file; partitioning skips whole files between them. The two compound. The failure modes are symmetric: too few files (one giant blob) means no skipping at all; too many files (the "small files problem") means the engine pays per-file overhead that swamps the read. Healthy targets are partitions in the tens-to-hundreds-of-megabytes range - big enough to amortize opening, small enough to prune usefully. Compaction jobs exist precisely to merge small files back up when a streaming or frequent-append pipeline has fragmented a table.

Partitioning means splitting a table into separate files by a column, so a query that filters on that column reads only the files it needs. The simplest form is one file per partition: write each month to its own Parquet file, then a month-query reads just that one file. Press ▶ Run - note the row count is one month's slice, not all 300k.

-- hand-partition: write January's slice to its own file
COPY (
  SELECT * FROM big_orders
  WHERE strftime(order_date, '%Y-%m') = '2026-01'
) TO 'orders_2026_01.parquet' (FORMAT PARQUET);

-- read ONE partition file - only January's rows are scanned
SELECT count(*)                           AS rows_scanned,
       ROUND(avg(quantity * unit_price),2) AS avg_line_value
FROM 'orders_2026_01.parquet';

Not all data deserves the same storage. Tiering trades latency for cost:

• Hot: queried constantly, must be instant. Kept on the fastest, most expensive storage (SSD-backed, standard object storage). Daybreak's last-90-days orders.
• Warm: queried occasionally. Cheaper storage, slightly slower retrieval. Last year's orders you still report on monthly.
• Cold: rarely touched, kept for compliance or the odd deep dive. Cheapest storage (S3 Glacier and the like), retrieval measured in minutes to hours. Daybreak's five-year-old records.

The base of all three is object storage (Amazon S3, GCS, Azure Blob) - cheap, effectively infinite, and where lifecycle rules automatically move data hot → warm → cold as it ages. You engineer the tiering; the cloud enforces it.

Behind an S3 bucket is a distributed file system: your "file" is split into pieces, replicated across many machines for durability, and addressed as one logical object. You never see the machines - you just read a path. That abstraction is what lets a single SELECT ... FROM 's3://daybreak/orders/*.parquet' transparently read data spread across a fleet. The read below is exactly what your playground does locally; only the path changes when it points at the cloud:

No SQL - reason about the failure mode. Partitioning helps by letting queries skip files, so it is tempting to partition on everything: month and channel and customer and product. Do that and you shatter 300k rows into thousands of tiny files, each with a handful of rows. Now every query, even a pruned one, must open, read metadata for, and stitch together a huge number of files - and the per-file overhead swamps any saving from skipping. This is the small files problem, and at real scale it is a classic performance killer: a table "optimized" into millions of kilobyte files runs slower than the single blob it started as. The rule: partition on the one or two columns queries actually filter on, at a granularity that keeps each partition a healthy size (tens to hundreds of MB). More partitions is not more speed - past a point it is the opposite. When a streaming or frequent-append job has already fragmented a table, a compaction step merges the shards back up.