title: Event sourcing with GCP BigQuery
body: <p>We are building a system that stores purchase history of customers. Mainly 3 entities,</p>
  <ul>
  <li>Orders (mutable data as order status keep changing)</li>
  <li>Returns (again mutable as return can undergo status changes)</li>
  <li>Invoices (immutable)</li>
  </ul>
  <p>For 'Orders' and 'Returns' we are going to subscribe to pubsub events from another GCP project. Eventually, we need to have <strong>event log</strong> of all events and also the the <strong>current state</strong> of the 'orders' and 'returns'. In my view BigQuery would do a great job storing events, as it is mainly an append-only db and it can help in tracking events back in time. However, some users of BigQuery are only interested in the final state.</p>
  <pre><code>Eg:
  Events | State
  ----------------     
  Order1 | created
  Order1 | in_Progress
  Order2 | created
  Order1 | out_for_Delivery
  
  So the current state would be
  Events | State
  ----------------     
  Order1 | out_for_Delivery
  Order2 | created
  </code></pre>
  <p>I could see some options as below,</p>
  <p><strong>Option1</strong>:
  Store all events in BigQuery. And for creating the final state create <strong>BQ Views</strong> on top of the table that sort by timestamp and pick the latest version</p>
  <p><strong>Option2</strong>:
  I read about Projection queries (using CQRS) which help to build the application state at any point back in time. But I am not sure how this is practically done.</p>
  <p>Please guide me here as to which option is better and also some more details on option2 which is a standard practice for building <strong>&quot;Event sourcing + CQRS&quot;</strong> kind of systems.</p>
  <p>Regards,
  Prasad.</p>