Save Time and Money in BigQuery by Using Arrays and Structs

Learn to embrace and understand the nested schema.

Photo by Towfiqu barbhuiya on Unsplash

Although Arrays and Structs can add complexity to your queries, once you understand how they work you can reap the benefits of a nested data structure.

To give you an idea of how beneficial this change can be, below shows the savings I’ve gained by converting one of Google Clouds public datasets from a typical denormalised format, into a nested format.

I’ve been able to reduce the number of rows by 93.1% and the size of the table by 35.5%

Here’s what we’ll cover in this article:

• What is a STRUCT and how does it work?
• What is an ARRAY and how does it work?
• How can you use a combination of both ARRAY and STRUCT data types to reduce query and table storage costs?
• Finally, we’ll run through a few test exercises to demonstrate how to query nested data, while also gaining insights into the performance difference vs the original denormalised table