We have a BigQuery table with fields competitionId, conferenceId, name, age, desc, etc and we are trying to improve the performance (reduce bytes scanned) of our queries. We are using DBT and create a partitioned & clustered table as such:
{{ config(
materialized = 'table',
cluster_by = ['conferenceId'],
partition_by = {
"field": "competitionId",
"data_type": "int64",
"range": {
"start": 0,
"end": 9,
"interval": 1
}
}
)}}
There are 9 major competitionIds, ranging from 1-9, and we use an integer partition on the competitionId field. conferenceId is also an integer field, ranging from 0-110, and we set conferenceId as a cluster field. Screenshot below from BigQuery showing the table successfully created as partitioned and clustered:
With the table created, we've got the following 3 queries:
Scan with competitionId partition - 58MB (partition working!)

Scan with both IDs - 58MB (cluster key conferenceId not helping...)

I had expected the query size to get much smaller when querying on both the partition key and cluster key, however adding conferenceId to the query did not reduce the bytes scanned at all... How can we update this such that our queries only scan over the specific competitionId + conferenceId for our query?
Edit: in this example - https://www.youtube.com/watch?v=wapi0aR4BZE - they are able to combine the partition key and cluster key to dramatically reduce the size of the query vs. using the partition key alone.

