What should I partition a BigQuery feature table by?

Use the date column that matches the dominant access pattern, such as an event date, snapshot date, or feature generation date. Training often reads a bounded history, while scoring may read a current snapshot. The partition key should make those filters natural, so BigQuery can ignore unrelated partitions.

How is clustering different from partitioning in BigQuery?

Partitioning separates a table into coarse sections, usually by time. Clustering organises rows inside those sections by columns commonly used in filters, joins, or aggregations. For feature tables, a common design is date partitioning with clustering by entity identifier, reducing reads across both time and entity dimensions.

How do I avoid expensive BigQuery scans?

Write queries that restrict partitions explicitly, select only the columns needed, and avoid broad wildcard or full-table reads. Use dry runs and query plans to inspect expected bytes processed before running large jobs. The same habits reduce cost under on-demand pricing and improve throughput under capacity-based pricing.

Warehouses & Lakehouses

06.03 · Walkthrough

BigQuery for Large-Scale Analytics

Partition, cluster, query, and cost-estimate a large ML feature table in BigQuery.

No video curated for this lesson yet

This lesson is written, ordered and part of the path - the video slot is the only thing still open. We are working through Everything Data lesson by lesson; 55 of 85 have their video so far.

The written notes below cover this idea in full - you lose nothing by reading instead of watching.

BigQuery performance on large ML feature tables depends on aligning table layout with access patterns. Date-based partitioning limits whole-table scans, clustering narrows reads within relevant partitions, and dry runs expose cost before execution. Query shape matters as much as storage design: select required columns, filter explicitly, and avoid broad scans.

What this lesson answers

how to partition ML feature tables in BigQuery
when should I cluster a BigQuery table
how to estimate BigQuery query cost before running

Notes

BigQuery is built for scanning very large datasets with minimal operations overhead, but performance and cost depend heavily on table layout and query shape. For a large ML feature table, partitioning is usually the first design decision. Partition the table on a column that matches the most common time-based access pattern, such as event_date, snapshot_date, or feature_generation_date. This lets BigQuery skip entire partitions when queries include a filter on that column.

Common questions

What should I partition a BigQuery feature table by?: Use the date column that matches the dominant access pattern, such as an event date, snapshot date, or feature generation date. Training often reads a bounded history, while scoring may read a current snapshot. The partition key should make those filters natural, so BigQuery can ignore unrelated partitions.
How is clustering different from partitioning in BigQuery?: Partitioning separates a table into coarse sections, usually by time. Clustering organises rows inside those sections by columns commonly used in filters, joins, or aggregations. For feature tables, a common design is date partitioning with clustering by entity identifier, reducing reads across both time and entity dimensions.
How do I avoid expensive BigQuery scans?: Write queries that restrict partitions explicitly, select only the columns needed, and avoid broad wildcard or full-table reads. Use dry runs and query plans to inspect expected bytes processed before running large jobs. The same habits reduce cost under on-demand pricing and improve throughput under capacity-based pricing.

Short definition: what is BigQuery for Large-Scale Analytics?

06.02 · Warehouses & LakehousesSnowflake for ML Data 06.04 · Warehouses & LakehousesDatabricks and Unified Analytics