title: BigQuery, using partition key and cluster key together to reduce query size - clustering not helping?
body: <p>We have a BigQuery table with fields <code>competitionId</code>, <code>conferenceId</code>, <code>name</code>, <code>age</code>, <code>desc</code>, etc and we are trying to improve the performance (reduce bytes scanned) of our queries. We are using DBT and create a partitioned &amp; clustered table as such:</p>
  <pre><code>{{ config(
      materialized = 'table',
      cluster_by = ['conferenceId'],
      partition_by = {
        &quot;field&quot;: &quot;competitionId&quot;,
        &quot;data_type&quot;: &quot;int64&quot;,
        &quot;range&quot;: {
          &quot;start&quot;: 0,
          &quot;end&quot;: 9,
          &quot;interval&quot;: 1
        }
      }
  )}}
  </code></pre>
  <p>There are 9 major competitionIds, ranging from 1-9, and we use an integer partition on the <code>competitionId</code> field. <code>conferenceId</code> is also an integer field, ranging from 0-110, and we set <code>conferenceId</code> as a cluster field. Screenshot below from BigQuery showing the table successfully created as partitioned and clustered:</p>
  <p><a href="https://i.stack.imgur.com/53S52l.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/53S52l.png" alt="enter image description here" /></a></p>
  <p>With the table created, we've got the following 3 queries:</p>
  <p><strong>Scan entire table - 373MB</strong>
  <a href="https://i.stack.imgur.com/r2nO1.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/r2nO1.png" alt="enter image description here" /></a></p>
  <p><strong>Scan with competitionId partition - 58MB (partition working!)</strong>
  <a href="https://i.stack.imgur.com/V8SD4.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/V8SD4.png" alt="enter image description here" /></a></p>
  <p><strong>Scan with both IDs - 58MB (cluster key conferenceId not helping...)</strong>
  <a href="https://i.stack.imgur.com/76AoJ.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/76AoJ.png" alt="enter image description here" /></a></p>
  <p>I had expected the query size to get much smaller when querying on both the partition key and cluster key, however adding <code>conferenceId</code> 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 <code>competitionId + conferenceId</code> for our query?</p>
  <p><strong>Edit:</strong> in this example - <a href="https://www.youtube.com/watch?v=wapi0aR4BZE" rel="nofollow noreferrer">https://www.youtube.com/watch?v=wapi0aR4BZE</a> - 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.</p>