title: Merge into a BigQuery Partitioned Table via a Join Without Scanning Entire Table
body: <p>Sample scenario..<br/>
  I have "BigTable" with millions of rows and "TinyTable" with just a few rows. I need to merge some information from TinyTable into BigTable.
  BigTable is partitioned by the column "date_time". My merge will join on date_time and ID. 
  <br/>
  I really only need the ID column to do the join, but I thought having the date_time column there as well would allow BQ to prune the partitions and look only at the dates necessary. Nope. It does a full scan on BigTable (billing me for Gigabytes of data)... even if TinyTable just has one value (i.e. from one date) in it.</p>
  
  <pre><code>BigTable
  +---------------------------+---------+-------+
  |         date_time         |      ID | value |
  +---------------------------+---------+-------+
  | '2019-03-13 00:00:00 UTC' |     100 | .2345 |
  | '2019-03-13 00:00:00 UTC' |     101 |   .65 |
  | '2019-03-14 00:00:00 UTC' |     102 |  .648 |
  |  [+50 millions rows...]   |         |       |
  +---------------------------+---------+-------+
  
  
  TinyTable
  +---------------------------+---------+-------+
  |         date_time         |      ID | value |
  +---------------------------+---------+-------+
  | '2019-03-13 00:00:00 UTC' |     100 |  .555 |
  | '2019-03-14 00:00:00 UTC' |     102 |  .666 |
  |                           |         |       |
  +---------------------------+---------+-------+
  </code></pre>
  
  <p>...</p>
  
  <p>Uses 8 GB...</p>
  
  <pre><code> MERGE BigTable
      USING TinyTable
      ON BigTable.date_time = TinyTable.date_time and BigTable.id = TinyTable.id 
      WHEN MATCHED THEN
        UPDATE SET date_time = TinyTable.date_time, value = TinyTable.value
      WHEN NOT MATCHED THEN
        INSERT  (date_time, id , value) values (date_time, id , value);
  </code></pre>
  
  <p>Uses 8 GB...</p>
  
  <pre><code>update BigTable 
  set value = TinyTable.value 
  from 
  TinyTable where 
  BigTable.date_time = TinyTable.date_time 
  and 
  BigTable.id = TinyTable.id 
  </code></pre>
  
  <p>Works as expected (only 12 MB) if I hard-code in a timestamp literal instead of using the value from the join (but not what I'm after)...</p>
  
  <pre><code>update BigTable 
  set value = TinyTable.value 
  from 
  TinyTable where 
  BigTable.date_time = '2019-03-13 00:00:00 UTC' 
  and 
  BigTable.id = TinyTable.id 
  </code></pre>
  
  <p>I need to run something like this hundreds of times per day. As-is, it's not sustainable cost-wise. What am I missing?</p>
  
  <p>Thanks!</p>