PCDE Design and implement database schemas — 10 Questions

Question 1

A company is using Cloud Spanner to manage financial transactions. The current schema has a single table 'Transactions' with a composite primary key (account_id, transaction_timestamp). The company frequently queries the latest transaction for each account. This query pattern is causing full table scans. Which schema design change would most improve query performance?

Choose an answer to begin — your selection is scored in the full session.

Accepted Answer

Create a separate 'LatestTransaction' table keyed by account_id, and update it whenever a new transaction occurs. It eliminates the need to scan the entire Transactions table to find the latest transaction per account. By maintaining a separate LatestTransaction table keyed by account_id, each account's latest transaction can be retrieved with a single point read. This is a classic denormalization pattern in Cloud Spanner that avoids the overhead of scanning or sorting large datasets for 'latest per group' queries.

Answer

Add a secondary index on (account_id, transaction_timestamp DESC)

Answer

Change the primary key to (transaction_timestamp, account_id) and use interleaving

Answer

Add a 'is_latest' boolean column to the Transactions table and index it

Question 2

A team is designing a relational schema for a new application on Cloud SQL. The schema includes a table 'Orders' and a table 'Customers'. Each order belongs to one customer. The team anticipates high write throughput and needs to enforce referential integrity. Which schema design is most appropriate?

Accepted Answer

Use a foreign key constraint from Orders.customer_id to Customers.customer_id. Using a foreign key constraint from Orders.customer_id to Customers.customer_id enforces referential integrity at the database level, which is essential for maintaining data consistency in a relational schema. Cloud SQL (e.g., MySQL or PostgreSQL) natively supports foreign key constraints, ensuring that every order references an existing customer without relying on application logic. This approach is efficient for high write throughput as the database handles the check atomically, avoiding race conditions.

Answer

Use Cloud Spanner interleaved tables with Orders as a child of Customers

Answer

Implement referential integrity checks in the application code and omit database constraints

Answer

Store order data as a JSON array in a column of the Customers table

Question 3

A team is designing a schema for a user activity logging system using Bigtable. Each log entry includes a user ID, activity type, timestamp, and details. The access pattern requires retrieving all activities for a specific user within a time range. Which TWO row key designs are suitable? (Choose TWO.)

Accepted Answer

user_id#activity_type#timestamp. (user_id#activity_type#timestamp) is correct because it groups all activities for a user under a single row key prefix, enabling efficient row range scans for a specific user. The activity_type suffix allows filtering by activity type if needed, while the timestamp ensures uniqueness and ordered storage. Option E (user_id#timestamp) is correct because it directly supports the access pattern of retrieving all activities for a user within a time range by scanning rows with the user_id prefix and filtering on the timestamp component.

Answer

timestamp#user_id

Answer

random_uuid

Answer

reverse_timestamp

Question 4

A company is migrating a large Oracle database to Cloud Spanner. The schema includes several tables with foreign key relationships. The team wants to minimize query latency for join queries that always involve a parent table and its children. Which THREE schema design strategies should the team consider? (Choose THREE.)

Accepted Answer

Design child table primary keys to start with the parent key (e.g., CustomerId, OrderId). Option A is correct because prefixing the child table's primary key with the parent key (e.g., CustomerId, OrderId) colocates child rows with their parent and enables efficient, locality-preserving lookups and joins on that key prefix. Option B is correct because denormalizing frequently joined lookup tables into the parent as repeated fields eliminates the join entirely, reducing latency for queries that always fetch parent and lookup data together. Option C is correct because Cloud Spanner interleaved tables physically store child rows within the parent's row range, so parent-child joins on the shared primary key prefix are executed locally without network hops. Option D is not ideal here because secondary indexes on foreign key columns add write amplification and storage cost and do not provide the same physical colocation benefit as interleaving or key prefixing. Option E is incorrect because storing foreign keys as JSON arrays in the parent breaks relational integrity, prevents efficient indexed joins, and does not leverage Spanner's interleaving or key-prefix locality.

Answer

Create secondary indexes on foreign key columns

Answer

Store foreign key relationships as JSON arrays in the parent table

Question 5

A company is migrating an on-premises PostgreSQL database to Cloud SQL for PostgreSQL. The database uses several custom PL/pgSQL functions that perform complex calculations. The migration must minimize application changes and support high availability. Which strategy should the database engineer use for the schema migration?

Accepted Answer

Use pg_dump to export the schema including functions and restore directly to Cloud SQL.. Pg_dump can export the entire PostgreSQL schema, including custom PL/pgSQL functions, in a format that Cloud SQL for PostgreSQL natively understands. Restoring directly with pg_restore or psql preserves the functions without requiring syntax conversion, minimizing application changes. Cloud SQL for PostgreSQL supports high availability through regional persistent disks and automatic failover replicas, meeting the HA requirement without altering the schema.

Answer

Convert the functions to stored procedures in Cloud Spanner and migrate data separately.

Answer

Export the functions as SQL scripts and convert them to pgSQL syntax for Cloud SQL.

Answer

Export the functions as SQL scripts and rewrite them in JavaScript using Cloud Functions.

Question 6

A financial services company uses Cloud Spanner for a global transaction processing system. They notice that certain read queries on a table with frequent writes are returning stale data even though they use strong reads. The table has a primary key of (user_id, transaction_id) and a secondary index on (timestamp). What is the most likely cause of the stale reads?

Accepted Answer

The query is using a secondary index that has not yet been updated with the latest write.. In Cloud Spanner, secondary indexes are implemented as separate tables that are updated asynchronously relative to the base table. When a strong read uses a secondary index, the read may still see a stale version of the index if the write has not yet been fully replicated to the index table. This is a known behavior: strong reads guarantee consistency only when reading from the base table using the primary key, not when using a secondary index.

Answer

The query is using a stale read timestamp.

Answer

The query is reading from a read-only replica.

Answer

Cloud Spanner is using eventual consistency for this query.

Question 7

A company is designing a Cloud Firestore schema for a social media application. Users can follow other users, and the application needs to display a feed of posts from followed users ordered by timestamp. Which schema design is most cost-effective and performant for querying the feed?

Accepted Answer

Store a feed subcollection under each user document containing references to posts from followed users.. It uses a feed subcollection under each user document to store pre-computed references to posts from followed users. This design avoids expensive collection-group queries or multiple individual queries per followed user, ensuring that fetching the feed is a single, indexed read operation ordered by timestamp, which is both cost-effective and performant at scale.

Answer

Store all posts in a top-level collection and query for posts where user ID is in the list of followed users, ordered by timestamp.

Answer

Store all user posts in an array within a single document and use array-contains queries.

Answer

Store a 'follows' collection with documents containing follower and followed user IDs; then query posts for each followed user.

Question 8

A database engineer is designing a Cloud SQL for MySQL schema for a multi-tenant SaaS application. Each tenant's data is isolated. Which TWO strategies are appropriate for tenant isolation?

Accepted Answer

Create a separate database for each tenant.. Option A is correct because creating a separate database per tenant in a single Cloud SQL for MySQL instance provides logical isolation, since MySQL databases have independent namespaces for tables and users, and access can be granted per database. Option D is correct because a separate Cloud SQL instance per tenant provides the strongest isolation boundary, separating data, connections, and resources at the instance level, which suits strict multi-tenant isolation requirements. Option B is not appropriate as the primary isolation strategy because a shared table with a tenant_id column relies entirely on application-level filtering, so a query bug or missing WHERE clause can leak cross-tenant data. Option C is incorrect because column-level security hides specific columns, not entire tenants' rows, so it does not isolate tenant data. Option E is incorrect because Cloud SQL for MySQL does not support native row-level security policies like those in PostgreSQL, so per-tenant row restriction cannot be enforced by the database itself.

Answer

Use a single table with a tenant_id column and enforce filtering in application queries.

Answer

Use column-level security to hide tenant data.

Answer

Use row-level security policies to restrict access per tenant.

Question 9

A team is migrating an on-premises PostgreSQL database to Cloud SQL. The current schema uses a composite primary key on columns (customer_id, order_date) in the orders table. The migration team wants to reduce the cost of secondary indexes. Which schema design change should they consider?

Accepted Answer

Partition the table by customer_id to reduce the number of secondary indexes needed.. Partitioning the table by customer_id can reduce the need for secondary indexes on that column. When the table is partitioned, queries that filter by customer_id can use partition pruning instead of an index scan, allowing you to drop an index on customer_id and thus reduce secondary index storage costs. Creating a secondary index on the composite key (B) would add an index and increase cost. Replacing the composite primary key with a surrogate UUID (C) would require a unique constraint index on the original columns, resulting in two indexes (the PK on UUID and the unique index on the composite columns) instead of one, which increases index costs. Using the CLUSTER command (D) physically reorders table rows but does not reduce index sizes.

Answer

Create a secondary index on the composite key to keep the same query performance.

Answer

Replace the composite primary key with a surrogate UUID primary key and add unique constraints on the original columns.

Answer

Use the CLUSTER command to physically reorder the table based on the composite key.

Question 10

A retail company uses Cloud Spanner to store product inventory data. The table structure is:

CREATE TABLE Inventory (
  ProductId INT64 NOT NULL,
  WarehouseId INT64 NOT NULL,
  StockLevel INT64 NOT NULL,
  LastUpdated TIMESTAMP NOT NULL OPTIONS (allow_commit_timestamp=true)
) PRIMARY KEY (ProductId, WarehouseId);

The application frequently runs the query: SELECT ProductId, SUM(StockLevel) AS TotalStock FROM Inventory WHERE WarehouseId = 123 GROUP BY ProductId. The query is slow and scans many rows. The index used is:

CREATE INDEX InventoryByWarehouse ON Inventory (WarehouseId);

What is the most effective schema change to improve query performance?

Accepted Answer

Modify the index to INCLUDE StockLevel: CREATE INDEX InventoryByWarehouse ON Inventory (WarehouseId) STORING (StockLevel).. The query needs to read StockLevel for every row matching WarehouseId, but the existing index only covers WarehouseId, forcing a back-join to the base table. By using STORING (StockLevel), the index becomes a covering index that includes the StockLevel column, eliminating the need for the back-join and reducing the number of rows scanned to only those matching the warehouse filter.

Answer

Change the primary key to (WarehouseId, ProductId) so rows are interleaved by warehouse.

Answer

Create a materialized view that pre-aggregates stock by warehouse.

Answer

Add a STORED GENERATED column for total stock per warehouse.