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…

Question

Options

• ACreate a sales_transaction table that holds the sales_transaction_header information as rows and the sales_transaction_line rows as nested and repeated
• BCreate a sales_transaction table that holds the sales_transaction_header and sales_transaction_line information as rows, duplicating the
• CCreate a sales_transaction table that stores the sales_transaction_header and sales_transaction_line data as a JSON data type.
• DCreate separate sales_transaction_header and sales_transaction_line tables and, when querying, specify the sales_transaction_line first in the WHERE

• A
  81% (48)
• B
  3% (2)
• C
  10% (6)
• D
  5% (3)

Explanation

Option A leverages BigQuery's native support for nested and repeated fields (STRUCT/ARRAY types), which denormalizes the tightly coupled tables into a single physical table - eliminating costly JOIN operations entirely and aligning with BigQuery's columnar storage architecture for maximum query performance. Option B is wrong because simply duplicating header rows alongside line rows creates a flat, redundant table that wastes storage and inflates scan costs without gaining the structural benefits of nesting. Option C is wrong because storing data as JSON requires parsing at query time, which is far slower than native columnar access and defeats the purpose of using BigQuery for analytics. Option D is wrong because BigQuery's query optimizer automatically determines JOIN order; manually reordering tables in a WHERE clause has no meaningful effect on JOIN performance.

Memory tip: "Tightly coupled + immutable + frequently joined = nest it." Whenever an exam question describes a parent-child relationship that never changes and is always queried together, BigQuery's nested/repeated fields are the intended answer - one table, no JOINs, fast scans.

No community discussion yet for this question.