An enterprise e-commerce platform processes high-frequency transactional updates for customer orders and inventory line items while concurrently running complex business intelligence queries across multi-year purchasing histories. The data engineering team must design a storage and schema architecture that satisfies the following requirements: Maintain strict transactional write integrity, external consistency, and eliminate update anomalies for operational workloads. Minimize compute slot consumption and eliminate expensive join operations when running large-scale analytical aggregations over hierarchical order data. Which schema design strategy should the team implement across Google Cloud services?

Enforce a normalized relational schema in Cloud Spanner for transactional workloads, and denormalize data into BigQuery using nested and repeated fields (STRUCT and ARRAY) for analytical workloads.: What the Solution Is This architecture pairs Cloud Spanner for Online Transactional Processing (OLTP) with BigQuery for Online Analytical Processing (OLAP). It applies strict relational normalization to the operational database and leverages native denormalization—using nested (STRUCT) and repeated (ARRAY) columns—in the data warehouse. How It Meets the Requirements Write Integrity & Consistency: Enforcing relational normalization (such as Third Normal Form) in Cloud Spanner guarantees data integrity, prevents duplicate updates across rows, and utilizes Spanner's serializable isolation and distributed transaction capabilities for mission-critical writes. Minimizing Analytical Compute & Joins: Migrating and transforming data into BigQuery using nested and repeated fields preserves parent-child entity relationships (such as orders and order line items) within a single record. This eliminates the need for expensive multi-table JOIN operations and reduces data shuffling across query execution stages. Key Advantages and Capabilities Optimal Storage Architecture Alignment: Aligns with BigQuery's underlying Capacitor columnar storage and Dremel execution engine, allowing parallel scans of nested data structures. Elimination of Update Anomalies: Normalization in Spanner ensures operational state changes (e.g., product pricing or customer address updates) occur in a single place without multi-record write contention. Reduced Analytical Slot Usage: Eliminating JOIN bottlenecks in BigQuery directly lowers computational complexity, slot execution time, and query costs. Separation of Workloads: Decouples read-intensive analytical queries from production transactional systems, protecting operational throughput and latency SLAs. Why This Is Optimal Transactional databases like Cloud Spanner are optimized for row-level writes and point lookups requiring relational integrity, whereas BigQuery's columnar, massively parallel processing architecture benefits significantly from denormalized hierarchical schemas where storage redundancy is traded for fast scan and aggregation performance.

StudyCerts

Professional Cloud Data Engineer

professional-cloud-data-engineer

Advanced

Prepare and test your skills

Track Readiness

Set Exam Goal

Advanced

Prepare and test your skills

Track Readiness

Set Exam Goal

Oltp Olap Schema Normalization Spanner Bigquery

Section: Deciding the degree of data normalization

An enterprise e-commerce platform processes high-frequency transactional updates for customer orders and inventory line items while concurrently running complex business intelligence queries across multi-year purchasing histories.

The data engineering team must design a storage and schema architecture that satisfies the following requirements:

Maintain strict transactional write integrity, external consistency, and eliminate update anomalies for operational workloads.
Minimize compute slot consumption and eliminate expensive join operations when running large-scale analytical aggregations over hierarchical order data.

Which schema design strategy should the team implement across Google Cloud services?

Implement a fully denormalized flat schema in Cloud Spanner to maximize write throughput, and enforce a 3NF normalized schema with primary and foreign keys in BigQuery to minimize data warehouse storage costs.

Enforce a normalized relational schema in Cloud Spanner for transactional workloads, and denormalize data into BigQuery using nested and repeated fields (STRUCT and ARRAY) for analytical workloads.

Maintain a single normalized schema in Cloud Spanner for both OLTP and OLAP, and execute federated BigQuery queries directly against Spanner to avoid denormalization pipelines.

Define nested and repeated column types in Cloud Spanner table definitions for transactional storage, while maintaining a normalized multi-table Snowflake schema in BigQuery using enforced foreign keys.

Professional Cloud Data Engineer

professional-cloud-data-engineer

Advanced

Prepare and test your skills

Track Readiness

Set Exam Goal

Advanced

Prepare and test your skills

Track Readiness

Set Exam Goal

Oltp Olap Schema Normalization Spanner Bigquery

Section: Deciding the degree of data normalization

The data engineering team must design a storage and schema architecture that satisfies the following requirements:

Maintain strict transactional write integrity, external consistency, and eliminate update anomalies for operational workloads.
Minimize compute slot consumption and eliminate expensive join operations when running large-scale analytical aggregations over hierarchical order data.

Which schema design strategy should the team implement across Google Cloud services?

Enforce a normalized relational schema in Cloud Spanner for transactional workloads, and denormalize data into BigQuery using nested and repeated fields (STRUCT and ARRAY) for analytical workloads.

Maintain a single normalized schema in Cloud Spanner for both OLTP and OLAP, and execute federated BigQuery queries directly against Spanner to avoid denormalization pipelines.

Explanations

What the Solution Is

This architecture pairs Cloud Spanner for Online Transactional Processing (OLTP) with BigQuery for Online Analytical Processing (OLAP). It applies strict relational normalization to the operational database and leverages native denormalization—using nested (STRUCT) and repeated (ARRAY) columns—in the data warehouse.

How It Meets the Requirements

Write Integrity & Consistency: Enforcing relational normalization (such as Third Normal Form) in Cloud Spanner guarantees data integrity, prevents duplicate updates across rows, and utilizes Spanner's serializable isolation and distributed transaction capabilities for mission-critical writes.
Minimizing Analytical Compute & Joins: Migrating and transforming data into BigQuery using nested and repeated fields preserves parent-child entity relationships (such as orders and order line items) within a single record. This eliminates the need for expensive multi-table JOIN operations and reduces data shuffling across query execution stages.

Key Advantages and Capabilities

Optimal Storage Architecture Alignment: Aligns with BigQuery's underlying Capacitor columnar storage and Dremel execution engine, allowing parallel scans of nested data structures.
Elimination of Update Anomalies: Normalization in Spanner ensures operational state changes (e.g., product pricing or customer address updates) occur in a single place without multi-record write contention.
Reduced Analytical Slot Usage: Eliminating JOIN bottlenecks in BigQuery directly lowers computational complexity, slot execution time, and query costs.
Separation of Workloads: Decouples read-intensive analytical queries from production transactional systems, protecting operational throughput and latency SLAs.

Why This Is Optimal

Transactional databases like Cloud Spanner are optimized for row-level writes and point lookups requiring relational integrity, whereas BigQuery's columnar, massively parallel processing architecture benefits significantly from denormalized hierarchical schemas where storage redundancy is traded for fast scan and aggregation performance.

View source documentation

Professional Cloud Data Engineer

Oltp Olap Schema Normalization Spanner Bigquery

Professional Cloud Data Engineer

Oltp Olap Schema Normalization Spanner Bigquery

What the Solution Is

How It Meets the Requirements

Key Advantages and Capabilities

Why This Is Optimal

Related questions