Shaping a data model for how it will actually be queried
Designing the data model for Google Cloud storage services means matching schema design and access patterns to the target system's strengths, whether that's normalized relational schemas for Cloud SQL/Spanner, denormalized wide tables for BigQuery, or key-based flat schemas for Bigtable/Firestore. On the exam, you must recognize which service fits a given access pattern, consistency need, and scale, then design the schema (columns, keys, partitioning) accordingly.
1 · Learn the must-know
- In BigQuery, denormalize and use nested/repeated (STRUCT/ARRAY) fields to avoid JOINs, and design partitioning (by date/time) and clustering (by frequently filtered/grouped columns) to reduce bytes scanned and cost.
- In Bigtable, schema design centers on a single row key that supports the required query patterns since it's the only indexed field; avoid monotonically increasing keys (e.g., timestamps) as row key prefixes to prevent hotspotting, and use field promotion/reversal or salting instead.
- In Cloud Spanner, choose primary keys carefully to avoid hotspotting on sequential IDs (use UUIDs or bit-reversed sequential IDs), and use interleaved tables to co-locate parent-child data for efficient joins and locality.
- Firestore/Datastore data modeling is document/entity-based with hierarchical collections and subcollections; design for query patterns since queries require indexes and denormalization is common to avoid multiple round trips.
- Choose Cloud SQL for normalized relational schemas needing ACID transactions and joins at moderate scale, and Spanner when you need the same relational/ACID guarantees but at global horizontal scale.
- Always let query patterns and access frequency drive the model: OLTP workloads favor normalization and low-latency point lookups, while OLAP/analytics workloads (BigQuery) favor denormalization for scan efficiency.
2 · Check your understanding
A retail company is designing a BigQuery data model to analyze customer orders. Each order can contain a variable number of line items, and analysts frequently need to query order totals alongside line-item details in a single pass. The team wants to minimize joins while keeping the schema easy to query with standard SQL. Which schema design best fits this requirement?
What you have tried across GCP PDE's objectives, not a readiness score.
Designing data processing systems~22% of the exam0 of 17 tried
Ingesting and processing the data~25% of the exam0 of 11 tried
Storing the data~20% of the exam0 of 13 tried
Preparing and using data for analysis~15% of the exam0 of 11 tried
Maintaining and automating data workloads~18% of the exam0 of 15 tried
3 · Keep going
Ready for more? Take a weighted mock or try free practice questions.