title: Google BigQuery Appending / Updating data
body: <p>As a background, I've been researching Google BigQuery to be used as a backend platform for a BI type tool. I'd like to point out I haven't yet used BigQuery, so my questions are around what I've seen on the docs.</p>
  
  <p>The high-level plan was to - perhaps - have Big Query as a 'live' source for a dashboard built on top of Tableau.</p>
  
  <p>Apparently the best way to load the data would be using a denormalized structure using JSON (that supports nesting)</p>
  
  <p>I see my JSON looking something like this:</p>
  
  <pre><code>{
      FirstName: 'John',
      LastName: 'Doe',
      Orders: {
          orderNo: 12345, 
          orderDate: '2013-01-01'
          orderlines: {
            lineNo: 1,
            qty: 1,
            price: 12,
            productId: 1234
            productName: 'Learning System',
           productSubsystem: 'SUB'
         }
      },
      LeadScores: {
         {
          scoreName: 'Learning Tech',
          scoreValue: 123,
          scoreDate: '2013-01-01'
         },
         {
         scoreName: 'ScoreB',
         scoreValue: 15,
         scoreDate: '2013-01-01'
         }
      },
      Activities {
       ** email opens, email clicks, page view, etc. (all here) **
        {
          activityType: 'email',
          activityAction: 'open',
          activityDescription 'message-1234'
        }
      }
  
  }
  </code></pre>
  
  <p><strong>Now my questions:</strong> </p>
  
  <p>Can I append records to "inner" collections (like I want to append more activities daily)? Or does it need to be another entity? (like the hole JSON is a single entity)</p>
  
  <p>Does this structure makes sense or would be better to have "3" or so entities (Activities, Orders, Demographics, Scores) and use JOINs? I read that BigQuery prefers not to use JOINs.</p>
  
  <p>A potential structure could be </p>
  
  <p>For Scores:</p>
  
  <pre><code> {  
     date: '2013-01-01',
     scoreName: 'Score A',
     scoreValue: '1234',
     customerId: '123456'
   }
  </code></pre>
  
  <p>For Activities:</p>
  
  <pre><code> {  
     date: '2013-01-01',
     activityType: 'email',
     activityAction: 'open',
     extra: '',
     customerId: '123456'
   }
  </code></pre>
  
  <p>For Demographic</p>
  
  <pre><code>  {
         customerId: '123456',
         firstName: 'A',
         lastName: 'B', etc..
  
    }
  </code></pre>
  
  <p><strong>Which approach makes more sense?</strong></p>
  
  <p>Thank you!</p>