Answer-first summary for fast verification
Answer: 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.
The correct answer is A. In BigQuery, using nested and repeated fields can significantly improve the performance of queries, especially those involving joins. By nesting the sales_transaction_line rows within the sales_transaction_header rows, you maintain the tight coupling of the data within a single table structure. This approach optimizes the storage and retrieval of data and minimizes the need for complex joins, thereby enhancing query performance. This method is particularly beneficial when the relationship between the tables is immutable and rarely modified.
Author: LeetQuiz Editorial Team
Ultimate access to all questions.
You are tasked with designing a data model in Google BigQuery to manage retail transaction data effectively. Specifically, you have two crucial tables, sales_transaction_header and sales_transaction_line, which are immutably and tightly coupled. These tables are seldom modified after initial loading but are often joined together during queries. To enhance the performance of data analytics queries, you need to decide how to structure the sales_transaction_header and sales_transaction_line tables. What approach should you take?
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.
B
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.
C
Create a sales_transaction table that stores the sales_transaction_header and sales_transaction_line data as a JSON data type.
D
Create separate sales_transaction_header and sales_transaction_line tables and, when querying, specify the sales_transaction_line first in the WHERE clause.
No comments yet.