title: How best cache bigquery table for fast lookup of individual row?
body: <p>I have a raw data table in bigquery that has hundreds of millions of rows. I run a scheduled query every 24 hours to produce some aggregations that results a table in the ballmark of 33 million rows (6gb) but may be expected to grow slowly to approximately double its current size.</p>
  
  <p>I need a way to get 1 row at a time quick access lookup by id to that aggregate table in a separate event driven pipeline. i.e. A process is notified that person A just took an action, what do we know about this person's history from the aggregation table?</p>
  
  <p>Clearly bigquery is the right tool to produce the aggregate table, but not the right tool for the quick lookups. So I need to offset it to a secondary datastore like firestore.  But what is the best process to do so?</p>
  
  <p>I can envision a couple strategies:</p>
  
  <p>1) Schedule a dump of agg table to GCS. Kick off a dataflow job to stream contents of gcs dump to pubsub. Create a serverless function to listen to pubsub topic and insert rows into firestore.</p>
  
  <p>2) A long running script on compute engine which just streams the table directly from BQ and runs inserts. (Seems slower than strategy 1)</p>
  
  <p>3) Schedule a dump of agg table to GCS. Format it in such a way that can be directly imported to firestore via <code>gcloud beta firestore import gs://[BUCKET_NAME]/[EXPORT_PREFIX]/</code></p>
  
  <p>4) Maybe some kind of dataflow job that performs lookups directly against the bigquery table? Not played with this approach before. No idea how costly / performant.</p>
  
  <p>5) some other option I've not considered?</p>
  
  <p>The ideal solution would allow me quick access in milliseconds to an agg row which would allow me to append data to the real time event.</p>
  
  <p>Is there a clear best winner here in the strategy I should persue?</p>