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…

Question

Options

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

• A
  4% (1)
• B
  4% (1)
• C
  13% (3)
• D
  79% (19)

Explanation

Option D is correct because BigQuery is an OLAP system optimized for denormalized, wide tables - nested and repeated fields eliminate costly JOINs, keeping queries simple and fast. An append-only pattern means every monthly update adds new rows rather than overwriting existing ones, so the full history is preserved by design; filtering by ingestion timestamp lets you query either current or historical state without any extra infrastructure.

Option A fails on two counts: normalized schemas force expensive JOINs that BigQuery is not designed for, and "snapshots before updates" adds operational complexity (and cost) compared to simply appending rows.

Option B fails because keeping raw input files in Cloud Storage does not make historical data queryable in a visualization layer - it's an archival workaround, not a data warehouse pattern, and a normalized model is still the wrong choice for BigQuery.

Option C is almost right but falls short: updating the table in place destroys the record you're trying to preserve, and relying on snapshots for history adds complexity that the append-only approach avoids entirely.

Memory tip: Think "append, don't amend" for BigQuery history. Flat + wide (denormalized/nested) for structure, timestamp for time travel - no extra tools needed.

No community discussion yet for this question.