Prepare and test your skills

Prepare and test your skills

Free sample question with answers and detailed explanation below. The Professional Cloud Developer practice bank has an additional 608 practice questions.Try 10 free realistic questions: instant score, no credit card, and no account required.
Your team is designing a Cloud Spanner schema for a globally distributed e-commerce application with high-concurrency writes. The application frequently retrieves a customer record alongside all of their orders, and runs queries that filter a single customer's orders by OrderStatus to return only OrderDate and TotalAmount.
Your schema must meet these requirements:
Orders table when querying OrderDate and TotalAmount by OrderStatus.Which schema design should you implement?
CustomerId as the primary key of Customers, define Orders with PRIMARY KEY (CustomerId, OrderId) using a UUIDv4 for OrderId and INTERLEAVE IN PARENT Customers, and create an interleaved secondary index on Orders(CustomerId, OrderStatus) STORING (OrderDate, TotalAmount) with INTERLEAVE IN Customers.This design combines non-sequential primary keys, table interleaving, and a covering interleaved secondary index to align the physical storage layout of Cloud Spanner with distributed write scalability and single-entity query locality.
CustomerId and OrderId distributes inserts uniformly across the entire key space. Spanner can divide rows across many database splits and servers, avoiding single-split bottlenecks.Orders with PRIMARY KEY (CustomerId, OrderId) and INTERLEAVE IN PARENT Customers clusters each customer's orders directly beneath their parent row in storage. Fetching a customer along with their orders reads from a single row tree and typically a single split, avoiding distributed two-phase commit overhead and cross-server network hops.Orders(CustomerId, OrderStatus) with INTERLEAVE IN Customers keeps index entries in the same row tree as the customer. Adding STORING (OrderDate, TotalAmount) turns the index into a covering index, duplicating those column values inside the index so Spanner can satisfy the query directly from the index without reading the base Orders table.STORING clause allows the query optimizer to use the index automatically for covered queries.Unlike foreign keys, sequential IDs, or non-interleaved indexes, this design solves write scaling, parent-child read locality, and index-only query execution simultaneously without sacrificing Cloud Spanner's strong consistency.
CustomerId and OrderId, link Orders to Customers using a FOREIGN KEY constraint without interleaving, and create a non-interleaved secondary index on Orders(OrderStatus).This approach applies traditional single-node relational database conventions—surrogate sequential primary keys, foreign key constraints, and standard non-interleaved secondary indexes—directly to a distributed database.
Customers and Orders insert targets the end of the key space on a single split, saturating one server.FOREIGN KEY enforces referential integrity, but it does not group child Orders rows with their parent Customers row in storage. Reading a customer and their orders requires communicating across multiple splits.OrderStatus and lacks a STORING clause, Spanner must perform a lookup back to the base Orders table to fetch OrderDate and TotalAmount. Furthermore, a non-interleaved index stores entries in separate root-level splits away from the customer's row tree.STORING, secondary indexes require extra cross-split joins to retrieve non-key columns.You were designing a schema for a traditional single-node or regional PostgreSQL database (such as AlloyDB), where SERIAL keys do not cause distributed key-range hotspots and tables are not physically interleaved.
In distributed databases like Cloud Spanner, non-sequential keys (such as UUIDv4 or bit-reversed sequences) and table interleaving replace traditional auto-incrementing IDs and unclustered foreign keys for high-throughput parent-child workloads.
CustomerId in Customers, define Orders as a separate root table with PRIMARY KEY (OrderDate, OrderId) and a FOREIGN KEY referencing Customers(CustomerId), and create a non-interleaved index on Orders(OrderStatus) STORING (OrderDate, TotalAmount).This option avoids hotspots on the Customers table by using UUIDv4 and creates a global (non-interleaved) covering index with STORING, while modeling Orders as an independent root table ordered first by OrderDate.
OrderDate—a monotonically increasing timestamp—as the first key part of Orders is a major distributed schema anti-pattern. Every new order arrives with a newer timestamp and is appended to the end of the Orders table on a single split, overwhelming one server under high write concurrency. Furthermore, a global index starting with OrderStatus can create hotspots if many concurrent orders share the same initial status (such as 'PENDING').Orders as a separate root table with a FOREIGN KEY instead of INTERLEAVE IN PARENT separates customer rows and order rows into different splits, increasing read latency and distributed transaction overhead when fetching them together.OrderStatus globally without CustomerId as the prefix scatters a single customer's orders across index splits.Orders were interleaved in Customers with CustomerId as the first key part, and the secondary index were also interleaved under Customers with (CustomerId, OrderStatus).
If you need to store timestamps in a primary key for chronological queries, place a high-cardinality non-monotonic column (such as CustomerId or a hashed ShardId) first, and consider DESC ordering on the timestamp column for recent-history reads.
CustomerId and OrderId, define Orders with PRIMARY KEY (CustomerId, OrderId) using INTERLEAVE IN PARENT Customers, and create an interleaved secondary index on Orders(CustomerId, OrderStatus) without a STORING clause.This option correctly uses table interleaving (INTERLEAVE IN PARENT Customers) and an interleaved secondary index scoped to CustomerId, but relies on UUIDv1 for primary keys and omits the STORING clause from the index.
CustomerId values to increase monotonically over time. Just like sequential integers or timestamps, this routes concurrent inserts to the same split and creates a write hotspot.STORING (OrderDate, TotalAmount), the index only stores CustomerId and OrderStatus. When a query requests OrderDate and TotalAmount, Spanner must join back to the base Orders table to retrieve those columns—and Spanner's query optimizer will not even use a secondary index automatically unless all queried columns are stored in the index or a FORCE_INDEX directive is provided.It specified UUIDv4 (which uses pseudo-random bits) instead of UUIDv1, and included STORING (OrderDate, TotalAmount) on the interleaved index.
When generating UUIDs for distributed primary keys, always verify that the generator uses random high-order bits (such as RFC-compliant UUIDv4) rather than time-ordered schemes like UUIDv1.
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