Prepare and test your skills

Prepare and test your skills

Free sample question, answered and explained below. The Professional Data Engineer practice bank has 1,353 more like it. Try 10 free: instant score, no account.
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:
Which schema design strategy should the team implement across Google Cloud services?
This proposal attempts to define native nested and repeated column types directly inside Cloud Spanner table schemas while modeling analytical data in BigQuery using a normalized Snowflake schema with enforced relational constraints.
STRUCT objects) as physical table column definitions. While ARRAY types are supported, Spanner relies on relational normalization or table interleaving rather than document-style nested record storage.JOIN operations during analytical queries, which increases compute slot consumption.STRUCT in ad-hoc subqueries and function parameters, but not as persistent table column schemas.Cloud Spanner natively supported persistent nested record column definitions as an object-relational store, and BigQuery's optimizer required normalized Snowflake dimensions with strictly enforced foreign key checks.
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.
JOIN operations and reduces data shuffling across query execution stages.JOIN bottlenecks in BigQuery directly lowers computational complexity, slot execution time, and query costs.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.
This approach uses a flat, fully denormalized structure with duplicate attributes across rows in Cloud Spanner, while applying a normalized Third Normal Form (3NF) relational model with strict key relationships in BigQuery.
JOIN operations across massive datasets. Because storage in BigQuery is inexpensive relative to compute, optimizing for minimal storage over analytical performance wastes significant compute slots.The operational layer were an append-only time-series event store requiring no updates to past entities, and the analytical layer had strict compliance mandates requiring relational 3NF mapping with minimal storage footprint without regard to query computation costs.
This architecture keeps all operational and historical analytical data inside a single, normalized Cloud Spanner instance and executes analytical queries directly using BigQuery federated queries without duplicating or denormalizing data into native BigQuery storage.
The organization had a lightweight operational dataset where analytical queries only required real-time access to a small number of current transactional rows with zero tolerance for data transfer lag.
Answer them yourself and get your score the moment you finish. No account needed.
Full access includes
Keep the momentum going with these hand-picked practice scenarios
Last updated Validated