Prepare and test your skills

Prepare and test your skills

Free sample question with answers and detailed explanation below. The Professional Cloud Database Engineer practice bank has an additional 769 practice questions.Try 10 free realistic questions: instant score, no credit card, and no account required.
Your team manages a write-heavy Spanner database with a Documents table (DocumentId primary key, Status, Priority, a small Metadata string, and a 500 KB Content JSON column). The table has separate secondary indexes on (Status), (Priority), and an unused (CreatedDate) column.
A frequent query runs:
SELECT DocumentId, Metadata FROM Documents WHERE Status = 'PENDING' AND Priority = 'HIGH'
Execution plans show an Index scan on Status followed by a high-CPU Table scan back-join to the base table, while load tests indicate index maintenance is degrading write throughput.
Which indexing strategy should you implement?
Column projection specifies which columns a query returns in its SELECT list rather than using SELECT *. While restricting projected columns reduces network serialization and client memory usage, it does not change how a row-oriented storage engine reads data blocks from disk.
Content: In Spanner's row-oriented storage format, all columns of a row are stored together on disk. When a Table scan back-join fetches a row to evaluate Priority and extract Metadata, the engine still reads the full row bytes, including the 500 KB Content column.DocumentId and Metadata, yet execution plans still show a high-CPU Table scan back-join because neither Priority nor Metadata exists in the (Status) index.(CreatedDate) index and redundant single-column indexes untouched fails to address the write throughput bottleneck identified in load testing.STORING) or column separation (such as a non-default locality group) prevents reading large unqueried columns.Column projection alone would significantly reduce disk I/O if the workload ran on a columnar storage engine (such as BigQuery or Spanner's columnar engine for analytical scans), where unprojected columns are skipped during storage reads.
A common misconception is assuming SELECT col1, col2 reduces storage I/O identically across OLTP row stores and OLAP column stores. In OLTP row stores, you must use covering indexes or separate large columns into another table or locality group to isolate scan I/O.
The INDEX_STRATEGY=FORCE_INDEX_UNION table hint instructs Spanner's query optimizer to scan two separate secondary indexes independently and merge their results using Union all and Hash aggregate operators. This strategy is designed specifically to optimize disjunctions (OR conditions) across predicates covered by different indexes.
AND predicates: The target query filters with Status = 'PENDING' AND Priority = 'HIGH' (a conjunction), not an OR condition. FORCE_INDEX_UNION targets index unions for OR logic rather than index intersection for AND logic.Metadata: Even if two separate indexes were intersected using INTERSECT DISTINCT, neither index contains Metadata, forcing Spanner to join the matching primary keys back to the Documents base table and read the 500 KB Content rows.(Status), (Priority), and unused (CreatedDate) indexes preserves the exact over-indexing issue that is degrading write throughput.OR queries: Index union prevents full table scans when a query checks colA = X OR colB = Y across two separate indexes.STORING, Spanner must still fetch missing columns from the base table.Using INDEX_STRATEGY=FORCE_INDEX_UNION would be appropriate if the query used an OR filter (WHERE Status = 'PENDING' OR Priority = 'HIGH'), the optimizer failed to choose an index union automatically, and write amplification from multiple indexes was not a constraint.
The STORING clause in Spanner copies non-key columns from the base table into a secondary index so queries can retrieve them without accessing the base table. Retaining all existing indexes keeps every historical index structure active and updated on every write operation.
Content JSON column into the secondary index causes the index rows to become just as large as the base table rows, defeating the purpose of scanning a compact index.Documents would now have to write the 500 KB Content payload twice (once to the base table and once to the index) while continuing to maintain the unused (CreatedDate) index and separate (Priority) index.(Status) as the index key still forces the Index scan to read all 'PENDING' entries before filtering on Priority.STORING multiplies network, CPU, and storage costs on mutations.Content is not selected.(CreatedDate) consumes storage and degrades DML throughput without delivering query benefits.This approach would only make sense if the Content column were tiny (a few bytes) and frequently required by the query, and if every existing index were actively serving critical, high-priority read workloads.
A covering composite secondary index in Spanner combines multiple filter columns in the index key while using the STORING clause to duplicate small, non-key projected columns directly inside the index structure. Because Spanner stores indexes as separate sorted tables, including Metadata via STORING (Metadata) allows the query engine to satisfy the entire query from the index alone without performing a base-table back-join.
Table scan back-join: Indexing (Status, Priority) lets Spanner seek directly to rows matching both AND predicates (Status = 'PENDING' and Priority = 'HIGH') instead of scanning all 'PENDING' entries and joining back to Documents.Content column: Because DocumentId (the primary key, automatically included in secondary indexes) and Metadata are both present in the index, Spanner never reads the large Documents base rows.(Status) index rather than keeping redundant indexes, removing the unused (CreatedDate) index, and omitting the large Content column from STORING minimizes write amplification during inserts and updates.Index scan to only matching records.Metadata column keeps the index footprint lightweight compared to duplicating the base table.Unlike maintaining multiple single-column indexes or relying on query hints, restructuring the index schema into a single covering composite index directly resolves both the read-side I/O bottleneck and the write-side maintenance overhead. If (Priority) is still needed by other standalone queries, it can remain; otherwise, evaluating and removing redundant indexes ensures optimal write scalability.
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