{"id":553412,"date":"2025-02-17T12:00:19","date_gmt":"2025-02-17T11:00:19","guid":{"rendered":"https:\/\/www.devoteam.com\/expert-view\/optimise-your-data-warehouse-bigquery-nested-and-repeated-fields\/"},"modified":"2025-09-15T15:46:37","modified_gmt":"2025-09-15T13:46:37","slug":"optimise-your-data-warehouse-bigquery-nested-and-repeated-fields","status":"publish","type":"expert-view","link":"https:\/\/devoteam.info\/en-se\/expert-view\/optimise-your-data-warehouse-bigquery-nested-and-repeated-fields\/","title":{"rendered":"Optimise Your Data Warehouse: BigQuery Nested and Repeated Fields"},"content":{"rendered":"\n

Introduction<\/strong><\/h2>\n\n

In my previous blog post<\/a>, we explored the pros and cons of normalised and denormalised data structures in data warehouses. While normalised data offers advantages such as reduced redundancy and improved data integrity, denormalised structures can lead to faster and more cost-efficient query performance. This is especially true for complex queries due to the reduction in joins. Although denormalisation can increase data redundancy, the performance gains often outweigh the drawbacks, particularly when query speed is essential.<\/p>\n\n

This article explores the advantages of using nested and repeated fields in BigQuery<\/a> for storing hierarchical data. These data structures can provide significant performance improvements compared to traditional, flattened models. By leveraging nested and repeated fields, you can maintain the benefits of denormalisation while mitigating its drawbacks.<\/p>\n\n

Flattened, Nested and Repeated data in BigQuery<\/h2>\n\n

Flattened data<\/strong> refers to a default table structure where data is stored in a non-hierarchical format, with each row representing a single record and its associated attributes. This contrasts with nested and repeated fields, which are great tools for storing data in its original organisation while harnessing the benefits of denormalised data<\/a>.<\/p>\n\n

Repeated fields<\/strong> can contain multiple values for a single record. They\u2019re ideal for storing hierarchical data in a single table while minimising storage cost.<\/p>\n\n

Nested fields<\/strong> are STRUCTs (structure data type fields in SQL), which are standard SQL data types that can be seen as pre-joined tables within a table. It\u2019s possible to have a repeated STRUCT inside of a table, simulating a 1-many connection to another table but built inside the current one to avoid costly joins by utilising arrays.<\/p>\n\n

In this blog post I explore only this 1-many relationship, therefore all further mentions of BigQuery nested fields refer to repeated STRUCTs.<\/p>\n\n

Both BigQuery nested and repeated fields can be used to preserve the relational qualities of the original data and schema while enabling columnar and parallel processing of the repeated fields.<\/p>\n\n

Through these fields, one can have ONE ROW for each entity and repeated values within that ONE ROW for data at a more granular level.<\/p>\n\n

Query Performance Testing: Flattened vs. Repeated\/Nested tables<\/h2>\n\n

To get an understanding of the pros and cons of using flattened vs. repeated or nested tables, the columns\u00a0Commit, Subject and Repo_name from the public dataset table github_repos.commits in BigQuery have been stored in the respective formats.\u00a0<\/p>\n\n

The dataset focuses on the individual commits and a large number of metadata around each. This metadata includes the subject of each commit. You will also find information about the repos in which it can be found. Due to Forks or Cherry-picking of commits, they can be found in a large number of different repositories. The public dataset stores the repos a commit appears in inside an array. For this experiment, they were also stored inside a nested form (an array inside a STRUCT) and a flattened form (commit and subject fields duplicated for each repo belonging to a commit).<\/p>\n\n

Aside from their different formats, the tables also have vastly different sizes. The flattened tables have a size of 427.88 GB, while the nested and repeated tables have \u201conly\u201d a size of 112.28 GB. These demonstrated a much more efficient use of storage while storing all the same data in a single table.<\/p>\n\n

This difference in size has a large impact on storage and query cost. The storage cost for the flattened table is $7.77\/ mo, while the storage for each of the optimised tables is $1.89\/ mo in the US multi-region used for these experiments.<\/p>\n\n

The queries that were used to create the three different tables for the subsequent performance tests can be found in the appendix.<\/p>\n\n

The Tables have the following formats:<\/p>\n\n

\"Table<\/figure>\n\n
\"Table<\/figure>\n\n
\"Table<\/figure>\n\n

Test Scenarios<\/h3>\n\n

To compare the different storage formats, they were tested in three different scenarios and compared based on their query speed and query cost. The specific queries used in the analysis can be found in the appendix<\/a>.<\/p>\n\n

The three scenarios tested were:<\/p>\n\n

1st Scenario: Simple Filtering<\/h4>\n\n