BigQuery Tips: Nested and Repeated Fields and How GOJEK Builds Data Warehouses (Cloud Next '19)

Gain expert advice on BigQuery to take advantage of data warehousing. In this session, GOJEK, a global company with multiple products and services and lots of data, will share how they have designed their data model to have BigQuery and Google Cloud Storage for their data lake and data warehouse, how they've set up their infrastructure using GCP, and how they've built internal tools as part of this process. GOJEK will also share how they govern their data warehouse to support their business, accelerate their decision making process, and gain intelligence about their products.
You will also get an in-depth walk through on how to work with semi-structured data, including how to ingest JSON array data types inside of BigQuery. But while denormalizing your schema into a single table with nested and repeated fields can yield performance improvements, the SQL syntax for working with array data can be tricky. Follow along as we demo loading, querying, troubleshooting, and unnesting various semi-structured data sets.
Build with Google Cloud → bit.ly/2TWjsxq
Watch more:
Next '19 Data Analytics Sessions here → bit.ly/Next19DataAnalytics
Next ‘19 All Sessions playlist → bit.ly/Next19AllSessions
Subscribe to the GCP Channel → bit.ly/GCloudPlatform
Speaker(s): Evan Jones, Gaurav Anand, Zaki Indra Sukma
Session ID: DA205
product: Cloud - Data Analytics - BigQuery; fullname: Evan Jones, Gaurav Anand, Zaki Indra Sukma; event: Google Cloud Next 2019;

[@Pirake123]

Run through session at the end was amazing, thanks!

[@rajkishormahanada6223]

Simple and sweet presentation. Good job 👍👍

[@pulkitdikshit6148]

how to study json funcitons to use json data with big query? #googlecloudplatform

[@puneetbhatia2326]

How do you store events on the same order that are happening at different points in time? E.g. order received, order ready for shipment , order shipped and order delivered could be days apart. Going back to update the same record and add another element to the array doesn’t seem right and more like an anti pattern

[@dokwme1211]

This should be 2 step process
You should first copy data to change log table with all states and another final table where records will be upserted

[@vsivam]

Nested record

[@tahsinature]

not bad...
good presentation...