Google Professional Data Engineer Question 142
Select 3Google Cloud PlatformYou are designing a data model for a retail company that wants to analyze customer purchase patterns in near real-time. The data includes information about customers, products, stores, and transactions. The company plans to use Google BigQuery for the analysis. Which of the following design decisions would best optimize the data model for both performance and query efficiency in this scenario?
- A
Denormalize the data by combining customer, product, store, and transaction details into a single table.
- B
Normalize the data into separate tables for customers, products, stores, and transactions, and use JOIN queries for analysis.
- C
Use nested and repeated fields in BigQuery to group related data, such as products purchased within the same transaction.
- D
Include pre-aggregated data for commonly used metrics like total sales per store in the data model.
- E
Avoid using partitioning or clustering, as these features can add unnecessary complexity to the data model.
Show answer and explanation
Correct answers: A, C, D
Explanation
In designing data models for BigQuery, denormalization, nested/repeated fields, and pre-aggregated metrics are key strategies to optimize for performance and query efficiency. Denormalization reduces the need for expensive JOINs, while nesting and repeated fields allow for efficient storage and querying of hierarchical data. Pre-aggregated data addresses common queries efficiently. Partitioning and clustering, on the other hand, should generally be used to improve performance rather than being avoided.
- A. Correct.
Denormalizing the data into a single table can improve query performance in BigQuery, as it reduces the need for expensive JOIN operations. This is especially effective for analytical workloads.
- B. Incorrect.
While normalization is useful for reducing redundancy, it can lead to slower query performance in BigQuery due to the need for frequent JOIN operations, especially for analytical queries.
- C. Correct.
Using nested and repeated fields in BigQuery allows for efficient representation of hierarchical or related data, such as items in a transaction, reducing the need for complex joins and improving query efficiency.
- D. Correct.
Including pre-aggregated data for commonly used metrics can significantly improve query performance by reducing the need for repeated computation of these metrics.
- E. Incorrect.
Avoiding partitioning or clustering is not recommended, as these features can greatly enhance query performance by limiting the amount of data scanned.