Partitioning and Parquet: cut query costs cheaply

Rohit Athaide · 2026年11月3日 公開

If your queries are billed by the data they read, the layout of your files is a cost control. Two changes do most of the work: store data in a columnar format, and split it into partitions you filter on. Both are free, and neither needs a new service.

Columnar formats

A row-based format such as CSV or JSON stores each record together. To read one column, the engine reads every row in full.

A columnar format stores each column together. Parquet and ORC are the common ones. A query that needs three columns of fifty reads roughly those three. Columnar files also compress well, because values in one column resemble each other, and they carry statistics that let the engine skip blocks that cannot match a filter.

Conversion is usually a one-time job: read the raw files and write Parquet. Keep the raw files in cheap storage as the source of truth, and query the Parquet copy.

Partitions

A partition is a folder in the file path named for a column value, for example year=2026/month=11/day=03/. When a query filters on those columns, the engine reads only the matching folders and skips the rest, without opening them.

Choose the partition column by how you filter. For event data that is almost always the date. For multi-tenant data it might be tenant and date.

Mistakes to avoid:

• Too many partitions. Partitioning by a high-cardinality column, such as user id, makes millions of tiny folders, which slows listing and can raise cost.
• Partitions too fine. Hourly partitions of a small dataset create many small files.
• Partitioning on a column nobody filters on.

File size

Engines work best on files of reasonable size, commonly tens to a few hundred megabytes. Thousands of tiny files slow queries and, on some services, add per-request charges. A compaction job that merges small files into larger ones, run daily, keeps the lake healthy. This matters for streaming data, which tends to write many small files.

Other habits

• Select only the columns you need.
• Filter on partition columns in every query, and test that the filter really prunes.
• Compress with a widely supported codec.
• Sort or cluster within files by a column you filter on, where the engine supports it, so block statistics skip more.
• Keep the schema stable, so that old and new files stay compatible.

A conversion checklist

1. Pick the three queries that cost the most. Check what they scan today.
2. Decide the partition column from their filters.
3. Write a job that converts one month to partitioned Parquet.
4. Run the same queries against it and compare bytes scanned.
5. If the drop is large, convert the rest and move the dashboards over.
6. Add a compaction job and a lifecycle rule for the raw files. See object storage classes and lifecycle.

Sensor data

Time-stamped machine data suits date partitions well. The guide on object storage for sensor archives covers how long to keep it hot. To see how scan size feeds the monthly estimate, open the data platform estimator and change the amount each query scans. How per-scan billing works is in serverless SQL billed per scan.