IssueTracker

Problem you have encountered:

BigQuery does not prune blocks with NULL values when a column filter is applied.

What you expected to happen:

BigQuery should skip blocks containing NULL values based on metadata, thereby avoiding scanning those blocks.

Steps to reproduce:

To replicate this issue, I created a table with a sparse column (90% NULLs, 10% non-NULL values). The table was clustered by this column to optimize for queries filtering on it. THen I queried for rows with a specific value in the clustering column (the value can be found using the data preview feature, for example).

Test table

CREATE TABLE `demo.pageviews_20240101_by_sparse_title`
CLUSTER BY sparse_title
AS
SELECT 
  datehour,
  wiki,
  IF(RAND() < 0.1, title, NULL) AS sparse_title,
  views
FROM `bigquery-public-data.wikipedia.pageviews_2024`
WHERE DATE(datehour) = DATE('2024-01-01');

The resulting table has 3.18 GB of logical bytes after materialization.

Test query

SELECT *
FROM `demo.pageviews_20240101_by_sparse_title`
WHERE sparse_title = "New_York_City_Subway_map"

Running this query results in 2.59 GB of bytes processed.

Clustering does appear to be working because parts of the table are being skipped. However, I expected approximately 90% of the table to be skipped because those blocks contain NULL values. In this case, BigQuery only skipped 18% of the table.

To compare, I created an example with a dense column.

Test table

CREATE TABLE `demo.pageviews_20240101_by_title`
CLUSTER BY sparse_title
AS
SELECT 
  datehour,
  wiki,
  title,
  views
FROM `bigquery-public-data.wikipedia.pageviews_2024`
WHERE DATE(datehour) = DATE('2024-01-01');

This table has 5.96 GB of logical bytes after materialization.

Test query

SELECT *
FROM `demo.pageviews_20240101_by_title`
WHERE title = "New_York_City_Subway_map"

Running this query results in only 14.13 MB of bytes processed.

The results are much better when the column is dense compared to when it is sparse. I would expect a similar amount of bytes processed in both cases, assuming that the blocks with NULL values are properly skipped.