LAB: Partitioning Pruning Proof + Clustering Scan Win

# LAB: Partitioning Pruning Proof + Clustering Scan Win (BigQuery, 75 min) > **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 ```sql -- 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 | Criterion | Weight | Pass bar | |---|---|---| | Pruning proof with numbers | 40% | both Job-Information screenshots, ratio computed | | Clustering measurement | 30% | before/after bytes + explanation (micro-partitions) | | Correct partition/cluster key choice | 30% | justifies key by filter pattern, not by cardinality alone |