Google Cloud Professional Data Engineer — Question 181

You are designing a data warehouse in BigQuery to analyze sales data for a telecommunication service provider. You need to create a data model for customers, products, and subscriptions. All customers, products, and subscriptions can be updated monthly, but you must maintain a historical record of all data. You plan to use the visualization layer for current and historical reporting. You need to ensure that the data model is simple, easy-to-use, and cost-effective. What should you do?

Answer options

• A. Create a normalized model with tables for each entity. Use snapshots before updates to track historical data.
• B. Create a normalized model with tables for each entity. Keep all input files in a Cloud Storage bucket to track historical data.
• C. Create a denormalized model with nested and repeated fields. Update the table and use snapshots to track historical data.
• D. Create a denormalized, append-only model with nested and repeated fields. Use the ingestion timestamp to track historical data.

Correct answer: D

Explanation

The correct answer, D, is appropriate because an append-only model allows for easy tracking of historical changes without overwriting previous data, while nested and repeated fields simplify the data structure. Options A and B focus on normalized models, which can complicate historical tracking and may increase costs, while option C does not emphasize the append-only design which is crucial for maintaining a clear historical record.