title: Propagation of BigQuery parition/cluster keys to a CTE table - Performance
body: <p>I setup a persistent table in our BigQuery database (using Looker, if that's relevant).  The table has both a <code>partition_key</code> and a few <code>cluster_keys</code>.  I partition on time, then cluster on my primary key (made with <code>GENERATE_UUID</code>), plus the two major fields that users will search on.</p>
  <p>I then have a CTE table that the rest of the queries pull data from.  This CTE selects a subset of the persistent table (the one with the partition and cluster keys), but this table is not itself persistent, so I don't think I can include partition and cluster keys in it.  It looks like this:</p>
  <pre><code>WITH my_table_pre_exclusion AS (--
          SELECT
              *
          FROM
              `server.data.prefix_my_table_persist`
          WHERE
          (
                        ( -- Some filter here
                  ) -- AND ... some filter here
          )
      )
  </code></pre>
  <p>My question is: does pulling from this table (which pre-applies a bunch of filters) hurt performance when I later do a ton of joins involving fields that ARE in the partition or cluster key fields?</p>
  <p>Would it be more performant to skip this CTE table, pull directly from the persistent table in all my downstream joins, and then re-apply the filters (which apply to everything downstream)?  It would be a lot more code bloat, but I did some benchmarking, and I <em>thinkkkk</em> it's hurting performance, but I'm not really sure.</p>
  <p>Is there a &quot;best of both worlds&quot; approach where I don't have to apply the same filters to a ton of downstream tables, but I still get optimal performance?  Maybe inner join <code>my_table_pre_exclusion</code> to all the downstream tables after-the-fact?</p>