BigQuery performs best when the data is denormalized. Rather than preserving a relational schema such as star or snowflake, tables should take advantage of nested records and nested repeated records. These records types can maintain relationships without the performance impact of querying and joining a relational (normalized) schema. Nested structures are also less consuming in terms of data scanned, meaning it is cheaper to compute. It enables running types of query, that would be slow and expensive in traditional datawarehouses, in a cheap and fast way.
Create nested columns to optimize performance in Google BigQuery. For more information, download the attached PDF.
