How to Model Tightly Coupled BigQuery Tables for Performance?

You are creating a data model in BigQuery that will hold retail transaction data. Your two largest tables, sales_transaction_header and sales_transaction_line, have a tightly coupled immutable relationship. These tables are rarely modified after load and are frequently joined when queried. You need to model the sales_transaction_header and sales_transaction_line tables to improve the performance of data analytics queries. What should you do?

  1. Create a sales_transaction table that holds the sales_transaction_header information as rows and the sales_transaction_line rows as nested and repeated fields. Source Reference Answer
  2. Create a sales_transaction table that holds the sales_transaction_header and sales_transaction_line information as rows, duplicating the sales_transaction_header data for each line.
  3. Create a sales_transaction table that stores the sales_transaction_header and sales_transaction_line data as a JSON data type.
  4. Create separate sales_transaction_header and sales_transaction_line tables and, when querying, specify the sales_transaction_line first in the WHERE clause.

Community Votes

A
100%

100% of anonymous learners picked answer A. Votes are pick records left by other test-takers — they are not the verified answer.

Community Insight

The question tests your understanding of BigQuery's nested and repeated fields as a denormalization technique that avoids data duplication while keeping related data co-located; the trap is choosing simple denormalization (duplicating header rows) instead of nested structures.

When two immutable BigQuery tables are frequently joined, using nested and repeated fields to combine them into a single table improves query performance by avoiding expensive joins. Community consensus strongly supports this approach, as it is a key BigQuery best practice for analytics workloads.

Option B (duplicating header data for each line) is the most common wrong answer because it is a familiar denormalization pattern, but it creates massive data redundancy and storage overhead, hurting performance and scalability compared to nested fields.

Community Discussion (6 comments)

raaad 👍 6 Selected: A
  • In BigQuery, nested and repeated fields can significantly improve performance for certain types of queries, especially joins, because the data is co-located and can be read efficiently. - - This approach is often used in data warehousing scenarios where query performance is a priority, and the data relationships are immutable and rarely modified.
josech 👍 2 Selected: A
Option A https://cloud.google.com/bigquery/docs/best-practices-performance-nested
hanoverquay 👍 1 Selected: A
optional A
JyoGCP 👍 1 Selected: A
Option A
Matt_108 👍 1 Selected: A
Option A
scaenruy 👍 1 Selected: A
A. Create a sales_transaction table that holds the sales_transaction_header information as rows and the sales_transaction_line rows as nested and repeated fields.

Comments & Corrections

No comments yet — spotted an error or have a note? Share it below.

Log in to comment, report an error, or add a note about this question.

Submitted for moderation before publishing. Keep it helpful and respectful.

Expert Analysis

Why the Answer Is Correct

Option A is correct because BigQuery is optimized for nested and repeated fields, allowing you to store the sales_transaction_header as a row and the sales_transaction_line as repeated records within that row. This eliminates the need for joining two tables, as all related data is stored together and read efficiently. The immutable and rarely modified nature of these tables makes them ideal for this denormalized structure, which is a recommended best practice in BigQuery documentation.

Why the Other Options Are Wrong

Option B duplicates the header information for each line, leading to massive storage bloat and worse query performance due to scanning redundant data. Option C using a JSON data type is not optimal for analytical queries because it lacks the type safety, schema enforcement, and columnar compression benefits of native BigQuery data types, making queries slower and harder to maintain. Option D is fundamentally incorrect because the WHERE clause does not control join execution order in BigQuery and does not address the root performance issue of frequent joins; the query engine still must join the two large tables.

Community Comment Notes

Comment [1] correctly highlights that nested and repeated fields improve performance because data is co-located and joins are avoided. Comment [2] provides the official Google Cloud best practices link, reinforcing that this is the documented approach. All community comments uniformly select option A, showing strong consensus that nested/repeated fields are the right BigQuery pattern for tightly coupled immutable relationships.

Official Reference

Exam Strategy

Remember that when you see two tables with a parent-child relationship that are frequently joined and rarely updated, think of nested and repeated fields as BigQuery's native way to denormalize without duplication. Eliminate options that suggest simple row duplication, as they reintroduce the storage and performance problems denormalization is meant to solve.

Related Analysis

You need to modernize your existing on-premises data strateg

A-Grade

Your business users need a way to clean and prepare data bef

A-Grade

You are designing the architecture of your application to st

S-Grade

← Back to PDE Study Guide