How to design a historical data model in BigQuery?
Short answer
D Create a denormalized, append-only model with nested and repeated fields. Use the ingestion timestamp to track historical data.
Question
Key Takeaways
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.
Common mistake: 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.
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.
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.
Community Votes
100% of anonymous learners picked answer D. Votes are pick records left by other test-takers — they are not the verified answer.
Discussion 6 contributions
Compiled from public community threads and reader submissions, lightly edited for clarity. Handles and vote tallies are shown as recorded, and comments reflect the views of individual contributors.
From public community threads 6
-
raaad 👍 11- 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.
-
scaenruy 👍 2 Answered DD. Create a denormalized, append-only model with nested and repeated fields. Use the ingestion timestamp to track historical data.
-
GCP001 👍 1 Answered DEasy, cost effective and no cpmpexity
-
JimmyBK 👍 1 Answered DStraight forward, good for costs
-
Sofiia98 👍 1 Answered DD looks logical
Show 1 more comment
-
JyoGCP 👍 1 Answered DOption D
Reader comments & corrections
No reader corrections yet — spotted an error or have a note? Share it below.