LAB: Partitioning Pruning Proof + Clustering Scan Win

Outcome: Partition a table and prove pruning; cluster and measure scan reduction. Covers plan 100 wh-04 (partitioning) + wh-05 (clustering).

1. Brief

On BigQuery sandbox: (a) create an unpartitioned copy and a date-partitioned copy of the orders table; (b) run an identical month-filtered aggregate on both and capture bytes-billed (pruning proof); (c) cluster by a low-cardinality column and measure the scan reduction on a filtered query.

2. Required deliverables

1. partition_lab.sql — DDL for both copies + the two probe queries.
2. evidence.md — bytes-billed for unpartitioned vs partitioned (LAB BQ-2 pruning proof) + clustered vs unclustered bytes (measured writeup).

3. Starter template

-- CHECK: partitioned-ddl (PARTITION BY order_date)
CREATE OR REPLACE TABLE lab.orders_part PARTITION BY order_date AS SELECT * FROM lab.orders;
-- CHECK: probe-query (identical filter on both tables)
SELECT date_trunc(order_date, MONTH) AS m, SUM(line_total) FROM lab.orders WHERE order_date BETWEEN '2023-01-01' AND '2023-01-31' GROUP BY 1;

4. Deterministic structural checks

• partitioned-ddl: PARTITION BY present on the copy.
• pruning-proof: partitioned bytes-billed < 50% of unpartitioned.
• clustering-win: clustered query bills fewer bytes than unclustered.

5. AI-review rubric