PCDE Practice Test 26 — 15 Questions

Question 1

A company uses Cloud Spanner with a schema that includes a table 'Events' with primary key (EventId, Timestamp). They need to run range queries on Timestamp across all events. They notice slow queries. Which two actions can improve query performance? (Choose two.)

Accepted Answer

Create a secondary index on Timestamp.. Creating a secondary index on Timestamp (A) enables efficient range scans. Creating a covering index (D) that includes all queried columns avoids table lookups. Option B helps writes but not reads. Option C is about interleaving, not relevant. Option E changes the primary key, which could help but may cause hot spots; not the best immediate action.

Answer

Add a hash prefix to EventId to distribute writes.

Answer

Use interleaving with a parent table on EventId.

Answer

Change the primary key to (Timestamp, EventId).

Question 2

Your team needs to add a new non-nullable column with a default value to a large Cloud Spanner table. The table has thousands of simultaneous writes per second. Which approach minimizes downtime and resource usage?

Accepted Answer

Use ALTER TABLE ADD COLUMN with a non-null default value. Option D is correct: Cloud Spanner's ALTER TABLE ADD COLUMN with a non-null default value is an online operation that applies the default without rewriting rows, avoiding downtime and heavy resource consumption. Option A causes downtime. Option B is slow and resource-intensive. Option C requires an additional update phase, which can cause inconsistency and contention.

Answer

Use ALTER TABLE ADD COLUMN without a default value and then update rows in batches

Answer

Create a new table and use batch operations to copy data

Answer

Drop and recreate the table with the new column

Question 3

Refer to the exhibit. You receive the following query output showing bytes processed for a BigQuery query. The table is partitioned by date and clustered on country. What is the most likely reason for the high bytes processed?

Accepted Answer

The query does not filter on the clustering column, causing full scan of selected partitions. Option B is correct: the query does not filter on the clustering column (country), so BigQuery must scan all rows in the selected partitions. Clustering only reduces data scanned when there is a filter on the clustering key or when the query aggregates after filtering on it. Option A is incorrect because partitioning is working. Option C is incorrect because 31 days is a small range. Option D is incorrect because the GROUP BY does not cause full scan; the issue is lack of clustering filter.

Answer

The GROUP BY country requires sorting all rows

Answer

The table is not partitioned correctly

Answer

The date range is too wide

Question 4

A Cloud Spanner database needs to add a column 'discount' to the 'Products' table without any downtime. The table is actively used. What is the correct approach?

Accepted Answer

Execute ALTER TABLE Products ADD COLUMN discount FLOAT64. Option A is correct: Spanner supports online schema updates via ALTER TABLE ADD COLUMN, which does not block reads or writes. Option B (new table and copy) would require downtime or at least double-write logic. Option C (secondary index) is unrelated. Option D (generated column) could be used but is unnecessary.

Answer

Create a new table with the column and copy data over

Answer

Create a secondary index that includes the new column

Answer

Define a generated column based on an existing column

Question 5

A team is migrating an on-premises MySQL database to Cloud SQL. The current schema usesMyISAM tables. What is the recommended approach?

Accepted Answer

Convert MyISAM tables to InnoDB before migration.. Option B is correct because Cloud SQL supports InnoDB which is the recommended engine for transactional workloads. MyISAM is not supported on Cloud SQL. Option A is wrong because MyISAM is not supported. Option C is wrong because simply moving the dump would fail. Option D is wrong because that would lose data integrity.

Answer

Keep the schema as is; Cloud SQL supports MyISAM.

Answer

Replicate the on-premises MySQL to Cloud SQL using Database Migration Service.

Answer

Export the database using mysqldump and import directly into Cloud SQL.

Question 6

Refer to the exhibit. The team notices high write latency on the Events table. They are inserting 1,000 events per second. The EventId is generated by a sequence. What is the most likely issue?

Accepted Answer

The sequential primary key creates a hotspot on a single split.. Option B is correct because using a sequential integer as primary key causes hotspotting on the last split, as all new writes go to the same tablet. Option A is wrong because 2000 processing units (equivalent to 2 nodes) can handle 1k writes/s if distributed. Option C is wrong because BYTES(MAX) may increase size but not the primary cause of latency. Option D is wrong because commit timestamp option does not cause hotspotting.

Answer

The allow_commit_timestamp option on CreatedAt column adds overhead.

Answer

The BYTES(MAX) data type causes excessive writing.

Answer

The node count is insufficient for the write throughput.

Question 7

A global gaming company uses Cloud Spanner for player profiles and game state. The schema includes a table 'PlayerStats' with a primary key (PlayerId, GameId, Timestamp). The table stores millions of rows per player. The application frequently runs a query to fetch the most recent stats for a given player across all games, using ORDER BY Timestamp DESC LIMIT 10. This query is slow, taking several seconds. The team adds a secondary index on (PlayerId, Timestamp) but still sees high CPU usage and latency. They need to redesign the schema to optimize this query without changing the application logic significantly. What should they do?

Accepted Answer

Change the primary key to (PlayerId, Timestamp, GameId) and drop the secondary index.. Option A is correct. Reordering the primary key to (PlayerId, Timestamp, GameId) allows Spanner to efficiently perform a range scan for a given PlayerId, sorted by Timestamp, without needing a secondary index. This eliminates the need for the index and reduces CPU. Option B is not a schema change. Option C is a different database, not a schema redesign. Option D is not supported in Spanner natively.

Answer

Migrate the PlayerStats table to Cloud Bigtable for better time-series performance.

Answer

Create a stored procedure that aggregates data per player and caches results.

Answer

Add a materialized view that pre-computes the latest stats per player.

Question 8

A data engineer is designing a BigQuery schema for a dataset that will be used for both ad-hoc analysis and scheduled dashboards. They want to optimize costs and performance. Which three strategies should they consider? (Choose three.)

Accepted Answer

Use partitioning on a date column for time-based queries.. Partitioning (A) reduces scanned data by date. Clustering (B) improves filter queries. Materialized views (D) precompute aggregates. Option C (multiple tables) increases management and query complexity. Option E (wildcard tables) can be costly if not used carefully and still scans all tables.

Answer

Use wildcard tables with a suffix filter.

Answer

Store data in multiple tables per day.

Question 9

You are designing a BigQuery schema for IoT sensor data. The sensor readings have varying fields depending on the sensor type. You want to minimize storage costs and avoid schema maintenance when new sensor types are added. What is the best schema design?

Accepted Answer

Store the sensor data in a JSON column. Option A is correct: using a JSON column allows flexible schema without requiring ALTER TABLE when new fields appear; BigQuery efficiently stores JSON and can query it with standard SQL. Option B requires creating new tables for each sensor type, increasing maintenance. Option C and D are wasteful because many columns will be NULL for most rows.

Answer

Use a separate table per sensor type

Answer

Use a schema with a STRUCT containing all possible fields as optional

Answer

Use a wide table with many nullable columns

Question 10

Refer to the exhibit. You are reviewing a Firestore security rules file. What is the main security flaw in the database schema design that these rules expose?

Accepted Answer

Users can set the visibility field, allowing them to make messages public. Option B is correct: the schema includes a 'visibility' field that users can set when creating documents. Since the create rule only checks that the senderId matches the authenticated user, users can set visibility to 'public' for any message they create, potentially exposing private messages. Option A is not a security flaw. Option C is not a flaw. Option D is vague and not specifically about the schema.

Answer

The rules do not protect against brute force attacks

Answer

The senderId field is not indexed

Answer

The delete rule allows admin to delete any message

Question 11

Which TWO are best practices for designing a Cloud Spanner schema?

Accepted Answer

Use commit timestamp columns to track row versions. Option A is incorrect because monotonically increasing keys cause hotspotting. Option B is correct: interleaved tables optimize parent-child joins. Option C is incorrect: secondary indexes are often needed for non-primary key queries. Option D is correct: commit timestamp columns enable versioning without storing explicit timestamps. Option E is incorrect: storing all data in a single row leads to large rows and contention.

Answer

Avoid secondary indexes to keep writes faster

Answer

Use monotonically increasing primary keys

Answer

Store all related data in a single row to avoid joins

Question 12

A financial services company uses Cloud Spanner for transaction processing. They need to run analytical queries that scan large portions of the database without impacting OLTP performance. What schema design technique should they use?

Accepted Answer

Design the primary key so that analytical queries scan a small number of tablets by using interleaved tables.. Option D is correct because Cloud Spanner supports interleaved tables which allow efficient scans on parent rows without cross-node joins. Option A is wrong because secondary indexes may not be suitable for large scans. Option B is wrong because read replicas are not available in Spanner for analytical workloads. Option C is wrong because exporting to BigQuery adds latency and complexity.

Answer

Export data periodically to BigQuery and run queries there.

Answer

Create multiple secondary indexes on frequently scanned columns.

Answer

Use a read replica instance to offload analytical queries.

Question 13

A team executed the above DDL to create interleaved tables in Cloud Spanner. They need to query all orders for a specific customer. Which query will be most efficient?

Accepted Answer

SELECT * FROM Orders WHERE CustomerId = 1234;. Since Orders are interleaved under Customers on CustomerId, filtering by CustomerId (A) allows Spanner to directly access the colocated rows. Option B filters only by OrderId, which may require a full scan. Option C adds an extra condition but still benefits from CustomerId. Option D uses a join, which is unnecessary because interleaving already provides the relationship.

Answer

SELECT * FROM Orders WHERE CustomerId = 1234 AND OrderDate = '2023-01-01';

Answer

SELECT * FROM Customers JOIN Orders ON Customers.CustomerId = Orders.CustomerId WHERE Customers.CustomerId = 1234;

Answer

SELECT * FROM Orders WHERE OrderId = 5678;

Question 14

You have a Cloud SQL for MySQL table that stores user logins with columns: user_id, login_time, ip_address. You frequently run queries to count logins by user for a specific date range. Which index would be most efficient?

Accepted Answer

A composite index on (login_time, user_id). Option B is correct: a composite index on (login_time, user_id) because the query filters by login_time range and then groups by user_id. The index can be used for both the WHERE clause (range scan on login_time) and then user_id is available for grouping without accessing the table. Option A puts user_id first, which is less efficient for range filtering on login_time. Options C and D are not as efficient as a composite index.

Answer

No index; rely on full table scan

Answer

Separate indexes on user_id and login_time

Answer

A composite index on (user_id, login_time)

Question 15

A company is designing a schema for time-series sensor data in Cloud Spanner. They need to efficiently query the latest reading for each sensor. Which schema design is most appropriate?

Accepted Answer

Create a Sensors table and an interleaved Readings table with primary key (SensorId, Timestamp DESC). Option A is correct because interleaving the Readings table under Sensors allows efficient parent-child joins and retrieval of the latest reading per sensor using the primary key ordering. Option B (single wide table) leads to large rows and poor performance. Option C (Cloud SQL) is not optimized for time-series at scale. Option D (Bigtable) is better for time-series but the question specifies Spanner.

Answer

Use a single table with columns for each sensor and wide rows

Answer

Use Cloud SQL with a normalized schema

Answer

Use Cloud Bigtable with row keys (SensorId#Timestamp)