How to design a historical data model in BigQuery?
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?
Community Votes
100% of anonymous learners picked answer D. Votes are pick records left by other test-takers — they are not the verified answer.
Community Insight
This question tests the ability to design a cost-effective and simple historical data model, where the common trap is choosing a normalized model or a snapshot-based approach which increases complexity and cost.
For a historical record in BigQuery, the community consensus is to use a denormalized, append-only model with nested and repeated fields, utilizing an ingestion timestamp to track data changes over time.
The most common mistake is choosing Option A, a normalized model with snapshots, because it seems intuitive for tracking history but leads to higher query complexity and storage costs compared to an append-only model.
Community Discussion (6 comments)
- A denormalized, append-only model simplifies query complexity by eliminating the need for joins. - Adding data with an ingestion timestamp allows for easy retrieval of both current and historical states. - Instead of updating records, new records are appended, which maintains historical information without the need to create separate snapshots.
Comments & Corrections
No comments yet — spotted an error or have a note? Share it below.
Expert Analysis
Why the Answer Is Correct
Option D is the best choice because a denormalized, append-only model with nested and repeated fields is optimized for BigQuery's columnar storage. By appending new records with an ingestion timestamp instead of updating existing ones, you inherently maintain a full history. This approach is simple, as it eliminates the need for complex joins, and is cost-effective because it avoids the storage overhead of creating full table snapshots for every update.Why the Other Options Are Wrong
Option A is incorrect because a normalized model requires joins, which increase query complexity and cost. Furthermore, using snapshots for history creates redundant data, significantly increasing storage costs. Option B is incorrect because relying on raw input files in Cloud Storage for historical reporting is not a viable data modeling strategy; it makes querying slow and complex. Option C is incorrect because while it uses a denormalized model, the use of snapshots to track history is inefficient and more expensive than the append-only method described in Option D.Community Comment Notes
The community strongly supports Option D, noting that an append-only model simplifies query complexity by eliminating joins. Comments highlight that appending data with an ingestion timestamp allows for easy retrieval of both current and historical states without the overhead of managing separate snapshots. This approach is consistently described as straightforward, good for costs, and free from unnecessary complexity.Official Reference
Exam Strategy
When faced with data modeling questions in BigQuery involving history or updates, prioritize denormalized, append-only schemas with timestamps over normalized models or snapshots to ensure simplicity and cost-efficiency.