Google Cloud Professional Data Engineer Exam

When moving large tables from a transactional database to BigQuery, which schema design approach is recommended?

1. Retain the data on BigQuery with the same schema as the source.

2. Combine all the transactional database tables into a single table.

3. Redesign the schema to normalize the data.

4. Redesign the schema to denormalize the data.

The correct answer is: Redesign the schema to denormalize the data.

When moving large tables from a transactional database to BigQuery, redesigning the schema to denormalize the data is the recommended approach. This is due to the nature of BigQuery, which is optimized for analytical queries and often performs better with a denormalized schema. In transactional databases, normalization is typically used to reduce redundancy and improve data integrity, which is essential for transactional operations. However, this design results in multiple related tables that require expensive JOIN operations during analysis. In contrast, denormalization involves merging related data into fewer tables, or even a single table in some cases, which reduces the complexity of queries and minimizes the number of JOINs needed to retrieve the data. BigQuery is designed to handle large datasets effectively, and its architecture supports fast querying on large volumes of data. By denormalizing, the performance of analytical queries is greatly enhanced, leading to lower response times and more efficient resource utilization during data analysis. This approach aligns well with BigQuery's strengths, allowing data engineers to take advantage of its distributed architecture while simplifying data access patterns for analytics. Thus, for migrating data from a transactional database to BigQuery, redesigning the schema in a denormalized form enables more effective and efficient analytical processing.