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.

Oltp Olap Schema Normalization Spanner Bigquery | professional-cloud-data-engineer

Oltp Olap Schema Normalization Spanner Bigquery

Worked example. The correct answer is already marked and every option is explained below, so there is nothing to select here. To answer questions yourself, start the free trial.

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.

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.

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.

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.

Professional Cloud Data Engineer

Oltp Olap Schema Normalization Spanner Bigquery

Related questions