**Are you taking advantage of partition pruning?**

We reviewed how Google Partitioning and Clustering works in a previous post. Have a look if you need a refresher

Now, what is Partition Pruning?

• By using qualifying filters on the value of the partitioning column, you can reduce the scanned data to that of the matched partitions.
• This translates in huge cost savings, especially if the table is big.

The code is something as simple as:

SELECT customer, order
FROM my_partitioned_table
WHERE DATE(partition_timestamp) > ‘2025-10-10’

Taking this approach into a Data Warehouse model, you might have the following:

• SRC layer where mostly renaming happens. These models are usually materialised as views.
• DWH layer to pull from SRC models. This is where transformations are performed.

In order to reduce costs, dbt provides mechanisms to use an incremental strategy. That is, only the new data is scanned and for that it will make use of your partitions.

Note Google is smart enough to push down from the DWH table to the SRC view your partitions filter and apply that to the underlying data, providing only the partitions that match your filter.

When does it break?

There is however something to consider. Sometimes we want to dedup the data at source, ensuring that any data that gets to our DWH layer is at least safe from duplicates.

In that case if your SRC view does something more than just renaming, like deduping or any other logic that involves complex window functions, then you might be breaking your partition pruning and forcing BigQuery to scan the full table.

See an example below:

-- This is my DWH model (dwh_table)
SELECT 
   retailer, 
   customer, 
   order
FROM src_view
WHERE DATE(partition_timestamp) > ‘2025-10-10’

-- This is my SRC model (src_view)
WITH
source_data AS (
   SELECT
      ret,
      cus,
      ord,
      partition_timestamp
   FROM my_partitioned_table
),

renaming AS (
   SELECT
      ret AS retailer,
      cus AS customer,
      ord AS order,
      partition_timestamp
   FROM source_data
   QUALIFY ROW_NUMBER() OVER(
      PARTITION BY retailer, customer, order
      ORDER BY partition_timestamp DESC
   ) = 1
)
SELECT * FROM renaming

By deduping in the SRC model we are

• Placing a ROW_NUMBER() clause inside a view.
• In general: any GROUP BY, QUALIFY or other complex window functions will prevents partition pruning, acting as an optimization barrier
• BigQuery is forced to scan the full table to execute the logic before the where clause can filter the partitions

Solution

By moving the deduplication to the STG or DWH model

• The order of the logic is changed
• The filter logic acts first by pruning the partitions
• Then the deduplication is done

-- This is my DWH model (dwh_table)
SELECT 
   retailer, 
   customer, 
   order,
   partition_timestamp
FROM src_view
WHERE DATE(partition_timestamp) > ‘2025-10-10’
QUALIFY ROW_NUMBER() OVER(
   PARTITION BY retailer, customer, order
   ORDER BY partition_timestamp DESC
) = 1

-- This is my SRC model (src_view)
WITH
source_data AS (
   SELECT 
      ret, 
      cus,
      ord,
      partition_timestamp
   FROM my_partitioned_table
),

renaming AS (
   SELECT
      ret AS retailer,
      cus AS customer,
      ord AS order,
      partition_timestamp
   FROM source_data
)
SELECT * FROM renaming