Google Cloud Professional Data Engineer Exam

What schema design should be used in BigQuery to optimize performance for hierarchical data that needs frequent querying?

1. Use nested and repeated fields.

2. Retain the data in normalized form always.

3. Copy the primary tables and use federated queries for secondary tables.

4. Copy the normalized data into partitions.

The correct answer is: Use nested and repeated fields.

Using nested and repeated fields in BigQuery is the most effective schema design for optimizing the performance of hierarchical data that requires frequent querying. BigQuery's support for nested and repeated fields allows you to represent hierarchical relationships directly within a single table, minimizing the need for expensive JOIN operations typically required in normalized tables. When you employ nested fields, data related in a hierarchy can be stored together, which reduces the amount of data that needs to be scanned during queries. This approach is particularly advantageous as it can lead to less overall data movement and more efficient processing. Repeated fields further enhance this design by allowing you to store multiple values for a single record, facilitating easier access and manipulation of data structures that naturally fit a list-like format. In contexts where hierarchical data is common, such as JSON-like structures or complex datasets that consist of parent-child relationships, this schema design not only streamlines data retrieval but also capitalizes on BigQuery's capabilities for fast processing and analysis of such data. The performance benefits realized from using nested and repeated fields often lead to lower query costs and increased execution speed, making it a suitable option for applications requiring frequent and complex queries against hierarchical datasets.