PCDE Design and implement database schemas — Timed 40 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?

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. Option A is correct because 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. Option C is correct because 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. Option C is correct because 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. Option D is correct because 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. Option D (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). Option A is correct because in Cloud Spanner, designing child table primary keys to start with the parent key (e.g., CustomerId, OrderId) enables efficient key-range scans and reduces the number of splits needed for join queries. This pattern leverages Spanner's distributed architecture to colocate related rows, minimizing cross-node communication and query latency.

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.. Option B is correct because 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.. Option D is correct because 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.. Option B is correct because 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.. Option B is correct because 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.. Option C is correct because 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.. Option A is correct because creating a separate database per tenant provides strong logical isolation at the schema level, preventing accidental cross-tenant data access. Cloud SQL for MySQL supports multiple databases within a single instance, and this approach leverages native MySQL database boundaries without requiring additional filtering logic. It also simplifies backup and restore operations per tenant.

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.. Option A is correct because BigQuery clustering organizes data based on the values of specified columns, which improves query performance by reducing the amount of data scanned when filtering on those columns. This is particularly useful for large data warehouses migrating from Oracle, as it mimics the performance benefits of indexes without the overhead of explicit index management.

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

Replace the composite primary key with a surrogate UUID primary key and add unique constraints on the original columns.. Option C is correct because replacing the composite primary key with a surrogate UUID primary key reduces the size of secondary indexes. In PostgreSQL (and Cloud SQL), secondary indexes include the primary key columns as row identifiers. A composite key on (customer_id, order_date) is wide, making every secondary index large and costly. A UUID surrogate key is narrower, shrinking all secondary indexes and reducing storage and I/O costs.

Answer

Partition the table by customer_id to reduce the number of secondary indexes needed.

Answer

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

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).. Option C is correct because 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.. Option B is correct because 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.. Option D is correct because 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.