Answer-first summary for fast verification
Answer: Design a `sales_transaction` table with `sales_transaction_header` information as rows and `sales_transaction_line` rows as nested and repeated fields.
Option C is correct because denormalizing the data model by combining the header and line information into a single table with nested and repeated fields enhances query performance. This approach is ideal for tables with a tightly coupled relationship, reducing the need for complex joins and facilitating easier retrieval of related line data when querying the header. Options A, B, and D are incorrect due to potential inefficiencies in querying JSON data, unnecessary data redundancy, and the lack of performance improvement from separate tables with prioritized WHERE clauses, respectively.
Author: LeetQuiz Editorial Team
Ultimate access to all questions.
When designing a data model in BigQuery for retail transaction data with tables sales_transaction_header and sales_transaction_line that have a closely connected and unchangeable relationship, how can you structure these tables to optimize the efficiency of data analytics queries?
A
Store sales_transaction_header and sales_transaction_line data as a JSON data type in a single table.
B
Create separate sales_transaction_header and sales_transaction_line tables, and prioritize sales_transaction_line in the WHERE clause during queries.
C
Design a sales_transaction table with sales_transaction_header information as rows and sales_transaction_line rows as nested and repeated fields.
D
Combine sales_transaction_header and sales_transaction_line into a single table, duplicating header data for each line.
No comments yet.