Google Professional Data Engineer Question 144
Single answerGoogle Cloud PlatformYou are designing the schema for a transactional data processing system using Google Cloud BigQuery. The system requires frequent analytical queries that involve joins across multiple tables. However, the data volume is large, and the performance of these queries is critical. How should you decide the degree of data normalization in this scenario?
- A
Fully normalize the data to reduce redundancy and minimize storage costs.
- B
Partially normalize the data and use nested and repeated fields to optimize for query performance.
- C
Denormalize the data completely to eliminate the need for joins and improve query performance.
- D
Normalize the data and use partitioned tables to enhance query performance.
Show answer and explanation
Correct answer: B
Explanation
In BigQuery, the degree of normalization should be decided based on the nature of the workload. Since analytical queries involving joins are frequent and performance is critical in this scenario, partial normalization with nested and repeated fields helps maintain a balance between storage efficiency and performance. This approach leverages BigQuery’s strengths in handling semi-structured data while optimizing query execution.
- A. Incorrect.
Fully normalizing the data reduces redundancy and storage costs, but it increases the complexity and cost of analytical queries that involve multiple joins. This is not optimal for query performance in BigQuery.
- B. Correct.
Partially normalizing the data and utilizing BigQuery's support for nested and repeated fields strikes a balance between reducing redundancy and improving query performance. This approach is well-suited for analytical workloads.
- C. Incorrect.
Completely denormalizing the data eliminates joins but can lead to significant data duplication and increased storage costs. While it may improve performance for some queries, it is not always the most efficient choice for large-scale systems.
- D. Incorrect.
Normalizing the data and using partitioned tables improves query performance in scenarios involving date- or range-based partitions but does not address the performance issues caused by excessive joins in normalized datasets.