Deciding how normalized or denormalized a schema should be

Choosing a normalization level in Google Cloud means balancing storage engine strengths: normalized (relational) schemas reduce redundancy and preserve strong consistency, while denormalized schemas trade storage/duplication for read performance and simpler queries, especially at scale. The right choice depends on the target service (Cloud SQL/Spanner vs BigQuery/Bigtable/Firestore) and the read/write pattern of the workload.

1 · Learn the must-know

• 1Cloud SQL and Cloud Spanner favor normalized, relational schemas because they enforce ACID transactions, foreign keys, and joins efficiently at OLTP scale.
• 2BigQuery generally performs better with denormalized schemas using nested and repeated fields (STRUCT/ARRAY) because it avoids expensive joins across large columnar tables and reduces query cost/latency.
• 3Bigtable requires denormalization by design since it has no joins or secondary indexes, so query patterns must be baked into the row-key and column-family structure upfront.
• 4Firestore/Datastore workloads typically denormalize by duplicating data across documents to avoid costly multi-read joins, at the expense of eventual consistency during updates to duplicated fields.
• 5Highly normalized schemas reduce storage and update anomalies but increase join complexity and can hurt performance/cost in distributed, large-scale analytical systems like BigQuery.
• 6A common exam pattern is matching workload type to schema style: OLTP/transactional and strong consistency needs favor normalization, while OLAP/analytical and high-read-throughput NoSQL needs favor denormalization.

2 · Check your understanding