Design a table for its queries

How a table is stored decides how many bytes a query can skip, and partitioning can only be chosen when the table is created, so it should come from the queries the table will answer. This lesson builds tables from learn.shop.orders, the 200 orders of the online shop, which has order_id, customer_id, product_id, order_date, quantity and status.

How it works

A partitioned table is stored in pieces, one for each value of its partition column. A filter on that column picks pieces before the query runs, so the estimate drops. A clustered table sorts the rows inside each piece by up to four columns. A filter on those columns skips blocks of rows while the query runs, so the bill can drop and the estimate cannot promise it. You cannot add partitioning to a table that exists, so you build a new table from a query with CREATE TABLE ... AS SELECT. Clustering can be added to an existing table later, but only the rows written afterward are sorted.

Common mistake

A layout helps only the queries that filter on its columns. A table partitioned by order_date does nothing for a query that filters only on customer_id: that query reads every piece, and its estimate equals the plain table. Clustering by a column no query filters on does nothing either. List the filters of the queries first, then choose the columns.

Worked example

DROP TABLE IF EXISTS learn.shop.orders_by_month;
CREATE TABLE learn.shop.orders_by_month
PARTITION BY DATE_TRUNC(order_date, MONTH)
CLUSTER BY product_id
AS SELECT order_id, product_id, order_date, quantity
FROM learn.shop.orders;

SELECT partition_id, total_rows
FROM `learn.shop`.INFORMATION_SCHEMA.PARTITIONS
WHERE table_name = 'orders_by_month'
ORDER BY partition_id
LIMIT 3;

The first statement removes an earlier copy, so the script runs again. The second builds a table of four columns of orders, with one piece for each month and the rows of each piece sorted by product_id.

The last statement lists the first three pieces. The partition_id is the month as YYYYMM, and total_rows counts the orders in it: 7 in January 2024, 11 in February and 9 in March.

All BigQuery lessons