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 dat a. 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?

This solution proposes a denormalized, append-only model, which is the most idiomatic and cost-effective approach for data warehousing in BigQuery. Denormalization using nested and repeated fields minimizes the need for expensive JOIN operations, simplifying queries and improving performance. An append-only strategy, where new versions of records are added instead of updating existing ones, naturally preserves a complete history. Using an ingestion timestamp allows for easy point-in-time analysis, enabling the visualization layer to query for either the most current state of the data or its state at any point in the past. This directly addresses all requirements: maintaining history, simplicity, ease of use, and cost-effectiveness.

A. A normalized model in BigQuery often leads to expensive JOINs, which contradicts the cost-effective and simple-to-use requirements. Using snapshots for historical analysis is complex for tracking an entity's changes over time.

B. A normalized model is suboptimal for BigQuery. Keeping historical data only in Cloud Storage files makes it unavailable for direct querying from the data warehouse, failing the reporting requirement.

C. While denormalization is correct, updating the table in place (in-place DML UPDATEs) overwrites and destroys historical data within the table itself. Relying on snapshots for history is inefficient for reporting.

1. Google Cloud Documentation, BigQuery Best Practices: Schema Design. In the section "Denormalization," it states, "The recommended approach to denormalizing data in BigQuery is to use nested and repeated fields. This approach maintains the relationships between entities while co-locating the data for optimal query performance." This supports the use of a denormalized model with nested and repeated fields.

2. Google Cloud Documentation, Data warehouse design for BigQuery, "Handle data changes". This section describes implementing Slowly Changing Dimensions (SCDs). It recommends an append-only approach: "To keep a history of the changes, you can add date columns to your dimension table... This approach is often called Slowly Changing Dimension Type 2." This directly supports using an append-only model with a timestamp to track history.

3. Google Cloud Documentation, Introduction to designing schemas, "Denormalization, nested, and repeated fields". This page explains that denormalization is a common strategy for increasing read performance and that using nested and repeated fields is the recommended way to do this in BigQuery, which aligns with the chosen answer's approach for simplicity and performance.