PCDE Design and implement database schemas — Timed 25 Questions

Question 1

A company is designing a database schema for a global e-commerce platform. Orders are created with high frequency, and order status updates occur frequently. The team needs to choose a primary key strategy for the orders table in Spanner. Which approach minimizes hot-spotting?

25 minute time limit — choose an answer to begin.

Accepted Answer

Use a universally unique identifier (UUID) as the primary key. In Spanner, monotonically increasing or time-ordered primary keys cause hot-spotting because all new writes are directed to the same tablet server, creating a single point of contention. UUIDs are randomly distributed, ensuring writes are spread evenly across the entire key space, which minimizes hot-spotting and maximizes write throughput.

Answer

Use a monotonically increasing integer (e.g., auto-increment)

Answer

Use a timestamp as the primary key

Answer

Use a composite key with user_id and order_date

Question 2

A team is migrating an on-premises PostgreSQL database to Cloud SQL for PostgreSQL. The existing schema uses a large number of foreign key constraints and triggers for data validation. The team wants to minimize migration effort and maintain data integrity. Which schema design approach is most appropriate for Cloud SQL?

Accepted Answer

Keep the existing foreign keys and triggers as-is in Cloud SQL for PostgreSQL. Cloud SQL for PostgreSQL is fully compatible with the PostgreSQL engine, meaning foreign key constraints and triggers operate identically to on-premises PostgreSQL. This approach minimizes migration effort by preserving the existing schema logic and maintaining referential integrity without requiring application changes or data validation rewrites.

Answer

Migrate to Cloud Spanner and use interleaved tables to simulate foreign keys

Answer

Remove all foreign keys and triggers and implement validation in the application layer

Answer

Convert the schema to use Firestore in Datastore mode with composite indexes

Question 3

A team is designing a schema for a time-series database in Bigtable to store IoT sensor readings. Each sensor sends a reading every minute. The team needs to create a row key that supports efficient queries for a specific sensor's readings over a time range. Which row key design is most appropriate?

Accepted Answer

sensor_id#reverse_timestamp. Bigtable stores rows sorted lexicographically by row key. By placing the sensor_id first, all readings for a given sensor are co-located in contiguous rows. Using reverse_timestamp (e.g., 9999-12-31 minus actual timestamp) ensures that the most recent readings appear first within that sensor's row range, which optimizes scans for the latest data and allows efficient range queries over a time window.

Answer

timestamp#sensor_id

Answer

hash(sensor_id)#timestamp

Answer

random_UUID

Question 4

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?

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 5

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 6

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 7

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). In Cloud Spanner, designing child table primary keys to start with the parent key (e.g., CustomerId, OrderId) enables efficient key-range scans and colocates related rows, reducing cross-node communication. Option B is correct because denormalizing frequently joined lookup tables into the parent table as repeated fields avoids joins entirely, further reducing latency. Option C is correct because parent-child interleaved tables physically store child rows adjacent to their parent row, minimizing splits and cross-node communication for join queries. Options D and E are incorrect: secondary indexes do not provide the same physical colocation benefits, and storing foreign keys as JSON arrays in the parent table would complicate queries and fail to leverage Spanner's distributed architecture.

Answer

Create secondary indexes on foreign key columns

Answer

Store foreign key relationships as JSON arrays in the parent table

Question 8

A retail company is designing a Cloud Spanner schema for an order management system. Orders are identified by a UUID and contain multiple line items. Each line item references a product. Which schema design best supports high read throughput for queries that retrieve all line items for a given order?

Accepted Answer

Create an Orders table and a LineItems table interleaved in Orders with ORDER_ID as the parent key.. Cloud Spanner interleaved tables store child rows (LineItems) physically adjacent to their parent row (Orders) on the same split, enabling a single key lookup to retrieve all line items for a given order without cross-table joins or distributed queries. This colocation maximizes read throughput by minimizing latency and avoiding scatter-gather operations across nodes.

Answer

Store orders and line items in a single table with repeated fields for line items.

Answer

Create separate Orders and LineItems tables with a foreign key relationship and index on ORDER_ID.

Answer

Denormalize product information into the LineItems table and store orders separately.

Question 9

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 10

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 11

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 12

A company uses Cloud Bigtable for time-series data from IoT devices. Each device sends a reading every second. The row key is device_id#timestamp (reverse timestamp). The team reports that queries for a specific device's data over the last hour are fast, but queries for all devices' data over the last minute are very slow. What is the most likely cause?

Accepted Answer

The row key design does not allow efficient scanning for all devices because device_id is the prefix.. The row key design uses device_id as the prefix, which means all data for a given device is co-located in contiguous rows, making per-device scans efficient. However, a query for all devices over the last minute requires scanning every row in the table because the timestamp suffix is reversed and not a prefix; Bigtable cannot perform a range scan across all devices for a recent time window without a full table scan, which is extremely slow.

Answer

The Bigtable cluster does not have enough nodes to handle the scan.

Answer

The query is scanning multiple column families.

Answer

The table has too many tablets, causing high overhead.

Question 13

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.. Options A and D are correct because they provide strong tenant isolation. Option A: Creating a separate database per tenant leverages MySQL's native database boundaries, preventing cross-tenant data access at the schema level. It also simplifies per-tenant backup/restore operations. Option D: Using a separate Cloud SQL instance per tenant offers physical isolation at the compute level, which is appropriate when tenants require complete resource isolation or have compliance needs. Options B, C, and E are incorrect: B relies on application filtering which can be error-prone and does not enforce isolation at the database level; C (column-level security) and E (row-level security) are not supported in MySQL and are features of other database engines like PostgreSQL or SQL Server.

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 14

A company is migrating a large Oracle Data Warehouse to BigQuery. The source schema includes many partitioned tables and materialized views. Which THREE considerations are important when designing the BigQuery schema?

Accepted Answer

Clustering can be used to improve query performance on frequently filtered columns.. Options A, B, and D are correct. Clustering (A) improves query performance on frequently filtered columns by colocating data, similar to Oracle's partitioning but without manual index management. Partitioning in BigQuery (B) supports DATE, TIMESTAMP, or INTEGER columns, which is crucial for migrating Oracle's partitioned tables. Materialized views in BigQuery (D) are automatically refreshed when base tables change, aligning with Oracle's materialized view refresh mechanisms but with less administrative overhead. Options C and E are incorrect because BigQuery does not require explicit indexes and does not enforce unique or foreign key constraints, reflecting its schema-on-read approach.

Answer

BigQuery requires explicit indexes on columns used in WHERE clauses.

Answer

BigQuery supports unique constraints and foreign keys for data integrity.

Question 15

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 16

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.

Question 17

A financial services company runs a MySQL database on Compute Engine. They want to migrate to Cloud SQL for MySQL to reduce operational overhead. The current schema includes a table 'transactions' with a composite primary key on (transaction_id, account_id) and a secondary index on account_id for account lookups. The database also uses foreign key constraints to ensure referential integrity between 'transactions' and 'accounts'. During migration testing, they observe that INSERT operations on 'transactions' are slower than expected. What schema change should they implement to improve INSERT performance in Cloud SQL?

Accepted Answer

Remove the foreign key constraints and enforce referential integrity in the application logic instead.. Foreign key constraints in MySQL (including Cloud SQL) require an internal check on every INSERT to verify that the referenced parent key exists. This adds a latency penalty proportional to the size of the parent table. Removing the constraint and moving referential integrity to the application eliminates this per-row check, directly improving INSERT throughput.

Answer

Remove the secondary index on account_id because it adds write overhead.

Answer

Change the primary key to (account_id, transaction_id) to avoid secondary index overhead.

Answer

Convert the table to a temporal table with system-versioning to avoid constraint checking.

Question 18

A company is migrating a legacy on-premises MySQL database to Cloud SQL for PostgreSQL. The database uses composite primary keys on multiple tables and heavily relies on cross-table joins with foreign keys. The team wants to minimize application code changes during migration. Which schema design strategy should the Cloud Database Engineer recommend to ensure compatibility and performance?

Accepted Answer

Use the same composite primary keys and foreign key constraints in Cloud SQL for PostgreSQL, leveraging its full support for these features.. Cloud SQL for PostgreSQL fully supports composite primary keys and foreign key constraints, which are standard SQL features. By maintaining the same schema, the team minimizes application code changes while preserving referential integrity and join performance, as PostgreSQL's query planner handles these constructs efficiently.

Answer

Maintain the same schema and rewrite joins as materialized views in PostgreSQL to optimize queries.

Answer

Migrate to Cloud Spanner instead, using interleaved tables to replace join-heavy operations.

Answer

Remove composite primary keys and replace them with surrogate keys; use look-up tables for foreign key relationships.

Question 19

A Cloud Database Engineer is designing a schema for an e-commerce application on Cloud Spanner. The application requires high read throughput for product queries by category and price range, and must support global scale with strong consistency. The team is considering primary key design and interleaved tables. Which TWO design considerations should the engineer apply? (Choose TWO.)

Accepted Answer

Use a primary key that starts with the category column to colocate product data for efficient queries by category.. Colocating product data by category in the primary key enables efficient range scans on category and price, as Cloud Spanner stores rows in sorted order by primary key. This design minimizes cross-node fan-out for queries filtering by category, directly supporting high read throughput at global scale with strong consistency.

Answer

Define secondary indexes on price and category columns to support range queries without considering the primary key design.

Answer

Use a timestamp as the first part of the primary key to enable time-based partitioning and efficient range scans.

Answer

Define interleaved tables for all related entities, even if they are not always accessed together, to reduce joins.

Question 20

Arrange the steps to perform a point-in-time recovery (PITR) for a Cloud SQL instance.

Accepted Answer

Enable automatic backups and binary logging, then create a new Cloud SQL instance from the backup at the desired point in time.. PITR requires backups and binary logs enabled; then you create a new instance from backup at the specific time.

Answer

Create a new Cloud SQL instance from the latest backup, then enable binary logging, then perform point-in-time recovery.

Answer

Perform point-in-time recovery directly on the existing Cloud SQL instance by restoring the backup.

Answer

Enable binary logging only, then create a new instance from the backup, then enable automatic backups.