# How BigQuery Partitioning Improves Query Performance: Partition Pruning Explained

> Learn how BigQuery partitioning improves query performance through partition pruning. Scan less data, reduce costs, and speed up queries by understanding this essential optimization technique.

- Repository: [DataTalksClub/data-engineering-zoomcamp](https://github.com/DataTalksClub/data-engineering-zoomcamp)
- Tags: deep-dive
- Published: 2026-05-31

---

**BigQuery partitioning improves query performance by enabling partition pruning, which allows the query engine to scan only relevant physical storage slices when filters are applied to the partitioning column, typically reducing bytes processed from gigabytes to megabytes.**

BigQuery partitioning is a critical optimization technique for data warehouses handling time-series data at scale. According to the DataTalksClub/data-engineering-zoomcamp course materials, properly partitioned tables can reduce query scan sizes by over 90% compared to non-partitioned alternatives when queries filter on the partitioning key. This article explains how partitioning works under the hood using actual SQL examples and performance metrics from the course repository.

## How Partition Pruning Works

BigQuery stores tables in **columnar storage** that is automatically divided into discrete **storage partitions**. When you define a table with a `PARTITION BY` clause—typically on a `DATE` or `TIMESTAMP` column—each distinct partition value receives its own physical slice of storage.

During query execution, BigQuery applies **partition pruning** to eliminate irrelevant slices from the scan. If your `WHERE` clause filters on the partitioning column, the query planner identifies exactly which physical partitions contain matching data and ignores the rest. This architectural optimization is implemented in [`03-data-warehouse/big_query.sql`](https://github.com/DataTalksClub/data-engineering-zoomcamp/blob/main/03-data-warehouse/big_query.sql), where a `DISTINCT(VendorID)` query demonstrates the dramatic reduction in data scanned.

## Performance Impact and Cost Reduction

The performance gains from partitioning are directly measurable through the **bytes processed** metric visible in the BigQuery UI.

In the DataTalksClub examples, identical queries show radically different scan sizes:

*   **Non-partitioned table:** Approximately **1.6 GB** scanned
*   **Partitioned table:** Approximately **106 MB** scanned

This represents a **93% reduction** in bytes processed for queries filtering on date ranges.

The cost implications are significant because BigQuery billing is based on bytes scanned. The course homework in [`cohorts/2024/03-data-warehouse/homework.md`](https://github.com/DataTalksClub/data-engineering-zoomcamp/blob/main/cohorts/2024/03-data-warehouse/homework.md) (lines 55-60) illustrates how selective filtering on partitioned columns avoids expensive full-table scans that would otherwise process hundreds of megabytes or gigabytes of unnecessary data.

## Creating Partitioned Tables in BigQuery

Partitioning is declared at table creation using the `PARTITION BY` clause. The DataTalksClub repository demonstrates this with NYC taxi data partitioned by pickup date:

```sql
CREATE OR REPLACE TABLE `taxi-rides-ny.nytaxi.yellow_tripdata_partitioned`
PARTITION BY DATE(tpep_pickup_datetime) AS
SELECT *
FROM `taxi-rides-ny.nytaxi.external_yellow_tripdata`;

```

This SQL creates daily partitions based on the `tpep_pickup_datetime` column, separating data into discrete physical units that can be independently pruned.

### Query Optimization with Filter Predicates

To benefit from partition pruning, queries must include predicates on the partitioning column. The repository example shows optimal filtering:

```sql
SELECT DISTINCT VendorID
FROM `taxi-rides-ny.nytaxi.yellow_tripdata_partitioned`
WHERE DATE(tpep_pickup_datetime) BETWEEN '2019-06-01' AND '2019-06-30';

```

As shown in [`03-data-warehouse/big_query.sql`](https://github.com/DataTalksClub/data-engineering-zoomcamp/blob/main/03-data-warehouse/big_query.sql) (lines 34-42), this query scans only the June 2019 partitions (≈106 MB) rather than the full table (≈1.6 GB).

## Combining Partitioning with Clustering

For additional optimization, BigQuery supports **clustering** within partitions using the `CLUSTER BY` clause. While partitioning divides data into coarse physical slices, clustering sorts rows within each partition by high-cardinality columns.

The course demonstrates this in [`03-data-warehouse/big_query.sql`](https://github.com/DataTalksClub/data-engineering-zoomcamp/blob/main/03-data-warehouse/big_query.sql) (lines 54-71):

```sql
CREATE OR REPLACE TABLE `taxi-rides-ny.nytaxi.yellow_tripdata_partitioned_clustered`
PARTITION BY DATE(tpep_pickup_datetime)
CLUSTER BY VendorID AS
SELECT *
FROM `taxi-rides-ny.nytaxi.external_yellow_tripdata`;

```

Clustering improves performance for queries filtering on non-partition columns. The example shows that adding clustering reduces scanned data from **1.1 GB** to **864.5 MB** for queries filtering on `VendorID` within specific date ranges.

```sql
SELECT COUNT(*) AS trips
FROM `taxi-rides-ny.nytaxi.yellow_tripdata_partitioned_clustered`
WHERE DATE(tpep_pickup_datetime) BETWEEN '2019-06-01' AND '2020-12-31'
  AND VendorID = 1;

```

## Monitoring Partition Metadata

You can inspect partition statistics using `INFORMATION_SCHEMA.PARTITIONS` to verify how data is distributed across slices:

```sql
SELECT
    table_name,
    partition_id,
    total_rows
FROM `nytaxi.INFORMATION_SCHEMA.PARTITIONS`
WHERE table_name = 'yellow_tripdata_partitioned'
ORDER BY total_rows DESC;

```

This query, referenced in [`03-data-warehouse/big_query.sql`](https://github.com/DataTalksClub/data-engineering-zoomcamp/blob/main/03-data-warehouse/big_query.sql) (lines 45-52), reveals the row count per daily partition, helping you identify data skew or verify that partition pruning is physically eliminating expected slices.

## Summary

*   **Partition pruning** restricts scans to relevant physical storage slices when queries filter on the partitioning column
*   **Cost reduction** is proportional to bytes saved—with examples showing drops from 1.6 GB to 106 MB
*   **Clustering** provides secondary optimization within partitions for high-cardinality filter columns
*   **Time-series data** benefits most from daily or hourly partitioning on timestamp columns
*   **Metadata inspection** via `INFORMATION_SCHEMA` confirms physical partition boundaries

## Frequently Asked Questions

### What column types work best for BigQuery partitioning?

**Date and timestamp columns are optimal for partitioning**, especially for time-series data like logs, events, or transactions. BigQuery supports partitioning by `DATE`, `TIMESTAMP`, or `DATETIME` columns, as well as integer-range partitioning. The DataTalksClub examples use `DATE(tpep_pickup_datetime)` to create daily partitions that align with common query patterns filtering on specific months or date ranges.

### How much can BigQuery partitioning reduce query costs?

**Partitioning can reduce scanned bytes by over 90%** when queries include selective filters on the partitioning column. In the course repository examples, partitioning reduced scans from approximately 1.6 GB to 106 MB for identical business logic. Since BigQuery charges based on bytes processed, this translates directly to proportionally lower costs per query.

### What is the difference between partitioning and clustering in BigQuery?

**Partitioning divides data into separate physical storage units** based on a column value (typically time), while **clustering sorts data within each partition** by specified columns to optimize block skipping. Partitioning eliminates entire slices from consideration, whereas clustering helps BigQuery skip irrelevant blocks within the partitions that must be read. The DataTalksClub materials demonstrate using both together: partitioning by date for coarse-grained pruning, then clustering by `VendorID` for fine-grained filtering within those date ranges.

### How can I verify that partition pruning is working for my queries?

**Check the BigQuery execution details or query `INFORMATION_SCHEMA.PARTITIONS`**. The bytes processed metric in the query results should be significantly smaller than the total table size. Additionally, the `INFORMATION_SCHEMA.PARTITIONS` view shows the specific partition IDs and row counts, allowing you to confirm that query plans are accessing only the partitions matching your `WHERE` clause predicates.