PCDE Design and implement database schemas Practice Test 6 — 15 Questions

Question 1

A logistics company is designing a Cloud Spanner schema for a shipment tracking system. The Shipments table has a primary key (ShipmentId). They need to efficiently query shipments by their current status and also retrieve shipments for a specific customer. The queries will filter by Status and CustomerId, and sort by ShipmentId. Which schema design should you implement to optimize these queries?

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

Accepted Answer

Create a secondary index on (CustomerId, Status, ShipmentId).. A secondary index on (CustomerId, Status, ShipmentId) is optimal because it supports queries that filter by CustomerId and optionally by Status, while providing the required sort order by ShipmentId. Placing CustomerId first leverages the common filter and allows the index to be used for queries with just CustomerId. This design minimizes the number of indexes needed and balances read performance with write overhead, making it the best choice for the described access patterns.

Answer

Create two separate secondary indexes: one on (Status, ShipmentId) and one on (CustomerId, ShipmentId).

Answer

Change the primary key to (CustomerId, ShipmentId) and add an index on Status.

Answer

Create a secondary index on (Status, CustomerId, ShipmentId).

Question 2

You are designing a Cloud Spanner schema for a new inventory management system. The main table is `Products`, with a primary key `ProductId` (a random UUID string). The system will have a very high write rate, inserting thousands of new products per second. You need to avoid hotspots and maximize write throughput. Which primary key design should you choose?

Accepted Answer

Use a composite primary key with `ShardId` (a random integer between 1 and 100) as the first column and `ProductId` as the second column.. Cloud Spanner distributes data based on the first column of the primary key. A random `ShardId` as the first column ensures that inserts are spread across many splits, avoiding hotspots. The `ProductId` remains unique and can be used for direct lookups when combined with the shard. This design is a standard best practice for high-write workloads.

Answer

Use `ProductId` as the sole primary key, but hash the UUID before storing it.

Answer

Use `ProductId` as the sole primary key, but store it as a monotonically increasing integer instead of a UUID.

Answer

Use `ProductId` as the sole primary key, but reverse the string bytes.

Question 3

You are designing a schema for a new application on Cloud Firestore. The application needs to store user profiles with fields: `userId` (string), `email` (string), `age` (number), and `city` (string). You need to query users by `city` and sort by `age`. What is the most efficient schema design?

Accepted Answer

Store each user as a document in a `users` collection, with `userId` as the document ID. Create a composite index on `city` and `age`.. Firestore requires a composite index for queries that combine an equality filter on one field and an order-by on another. The correct design uses `userId` as the document ID for direct lookups and creates a composite index on `city` and `age`. This ensures efficient query execution and minimal latency.

Answer

Store each user as a subcollection under a `cities` collection, with `userId` as the document ID. Query the subcollection for each city.

Answer

Store each user as a document in a `users` collection, with an auto-generated document ID. Create a single-field index on `city` and rely on Firestore's automatic sorting by `age`.

Answer

Store each user as a document in a `users` collection, with `userId` as the document ID. Create a composite index on `age` and `city`.

Question 4

A financial analytics team uses BigQuery to store trade transactions in a partitioned table. The table is partitioned by trade_date and clustered by customer_id. A query filters on customer_id and a range of trade_date values. The query scans a large number of partitions, causing high cost. What should you do to reduce the bytes scanned while keeping the same schema?

Accepted Answer

Use a WHERE clause that filters on trade_date with a constant expression to enable partition pruning.. Partition pruning is the most effective way to reduce bytes scanned in BigQuery. When the filter on the partitioning column uses a constant expression, BigQuery eliminates irrelevant partitions. Using a non-constant expression prevents pruning, causing a full table scan across all partitions. Ensuring the filter is constant or a parameter enables pruning and lowers query cost significantly.

Answer

Create a materialized view that pre-aggregates the data by customer_id and trade_date.

Answer

Add a clustering key on trade_date in addition to customer_id.

Answer

Change the partitioning column to customer_id and cluster by trade_date.

Question 5

You are designing a schema for a Cloud BigQuery data warehouse. The main fact table `sales` contains billions of rows and is partitioned by `sale_date` (DATE) and clustered by `customer_id` (STRING). A common query pattern is to filter by `sale_date` range and then join with a dimension table `customers` on `customer_id`. You notice that the join is slow and sometimes exceeds resources. What schema change should you make to improve performance?

Accepted Answer

Ensure the `sales` table is clustered by `customer_id` and the `customers` table is also clustered by `customer_id`.. In BigQuery, clustering tables on the join key can significantly improve join performance by minimizing data movement. The fact table is already clustered by `customer_id`, so clustering the dimension table on the same key ensures that both sides of the join are sorted and co-located. This reduces shuffle and speeds up the query.

Answer

Use a materialized view that pre-joins `sales` and `customers` and is partitioned by `sale_date`.

Answer

Add a clustered column `customer_id` to the `customers` dimension table.

Answer

Change the clustering column to `sale_date` and partition by `customer_id`.

Question 6

A healthcare company is designing a Cloud Spanner schema for a patient records system. The main table 'Patients' has a primary key of PatientId (a UUID string). The table will store millions of patient records and must support high write throughput and efficient point lookups by PatientId. The application also needs to query patients by last name and date of birth. Which two design choices should you make to optimize performance and scalability? (Choose two.)

Accepted Answer

Create a secondary index on LastName and DateOfBirth to support queries.. Keeping a random UUID primary key distributes writes across Cloud Spanner splits, preventing hot spots and enabling high write throughput. A secondary index on LastName and DateOfBirth efficiently supports the required queries without affecting the primary key's write distribution. Together, these choices optimize both write scalability and query performance for the patient records system.

Answer

Use a composite primary key of LastName and PatientId to optimize queries by last name.

Answer

Use a monotonically increasing integer as the primary key instead of UUID.

Answer

Add a hash of PatientId as an additional column and use it as the primary key.

Question 7

You are designing a schema for a Cloud SQL for PostgreSQL instance that will store JSON data in a column. The application needs to query specific fields within the JSON and often filters on a nested key `metadata->>'category'`. You want to optimize query performance and ensure data integrity. What should you do?

Accepted Answer

Store the JSON as a `jsonb` data type and create a B-tree index on `(metadata->>'category')`.. Using the `jsonb` data type in PostgreSQL provides efficient storage and querying of JSON data. Creating a B-tree index on the expression `(metadata->>'category')` allows the database to quickly find rows matching a specific category value. This combination optimizes performance for the described query pattern and ensures data integrity through the binary format.

Answer

Store the JSON as a `jsonb` data type and create a GIN index on `metadata`.

Answer

Store the JSON as a `text` data type and create a full-text search index on the column.

Answer

Store the JSON as a `json` data type and create a GIN index on the entire column.

Question 8

You are designing a Cloud Spanner schema for a global ride-hailing service. The `Trips` table stores millions of rows per day and will be queried with two access patterns: (1) look up a trip by `TripId`, and (2) retrieve all trips for a given `RiderId` within a date range. You want to avoid hotspots and support both access patterns efficiently. What should you do?

Accepted Answer

Use a composite primary key of `RiderId` and `TripTimestamp` and create a secondary index on `TripId`.. A composite primary key of `RiderId` and `TripTimestamp` allows Cloud Spanner to distribute data evenly across splits while clustering trips for a rider by time, making date-range queries efficient. The secondary index on `TripId` provides a direct lookup path. This combination avoids hotspots from monotonically increasing keys and supports both required access patterns without overloading a single key range.

Answer

Use `TripId` as the primary key and create a secondary index on `RiderId`.

Answer

Use `RiderId` as the primary key and create a secondary index on `TripId`.

Answer

Use a composite primary key of `RiderId` and `TripId` and create a secondary index on `TripId`.

Question 9

You are designing a schema for a Cloud Spanner database that will support a global e-commerce application. The main table `Orders` has a primary key `OrderId` (a random UUID). The application needs to efficiently query orders by `CustomerId` and also by `OrderDate`. You want to avoid hotspots and ensure high performance for these queries. Which TWO schema changes should you make? (Choose two.)

Accepted Answer

Create a secondary index on `OrderDate` and include `CustomerId` as a stored column.. To optimize queries by `CustomerId` and by `OrderDate`, you should create secondary indexes on each of those columns and include the other column as a stored column. This makes the indexes covering for queries that filter on one column and need the other, reducing the need to fetch from the base table. The random UUID primary key already distributes writes well, so no primary key change is needed.

Answer

Create a secondary index on `CustomerId` and a separate secondary index on `OrderDate` without stored columns.

Answer

Change the primary key to a composite key of `CustomerId` and `OrderId`.

Answer

Add a `ShardId` column and make it the first part of the primary key, followed by `OrderId`.

Question 10

A logistics company uses Cloud Bigtable to store time-series data from IoT sensors. The row key is designed as sensorId#timestamp, where timestamp is in reverse order (e.g., sensorId#9999999999). The table has a single column family 'metrics' with columns for temperature and humidity. The company needs to retrieve the latest reading for a specific sensor. What is the most efficient query pattern?

Accepted Answer

Use a Scan with a row key prefix sensorId# and a limit of 1 row, relying on reverse timestamp ordering.. With a row key of sensorId#timestamp where timestamp is reversed, the latest reading has the smallest timestamp suffix and is stored first for that sensor. A Scan with a prefix filter on sensorId# and a limit of 1 row retrieves only that first row, which is the latest reading. This leverages Bigtable's lexicographic ordering and is the most efficient query pattern.

Answer

Use a Scan with a row key range from sensorId#0000000000 to sensorId#9999999999 and sort by timestamp.

Answer

Use a Get request with the exact row key sensorId#9999999999.

Answer

Use a Scan with a prefix filter on sensorId and a limit of 1 row.

Question 11

A healthcare company is designing a Cloud Firestore schema for patient records. They need to support queries that filter on `patientId`, `admissionDate`, and `department`, and also sort by `admissionDate` descending. The queries will be run frequently from a web application. They want to minimize the number of composite indexes and ensure query performance. What is the most efficient schema design?

Accepted Answer

Store each patient record as a document in a top-level collection `patients`, with fields `patientId`, `admissionDate`, `department`, and create a composite index on `patientId`, `department`, and `admissionDate` (descending).. Firestore composite indexes are defined on a set of fields and support queries that filter on a prefix of the indexed fields and sort by the remaining fields. An index on `patientId`, `department`, and `admissionDate` descending can serve queries that filter on `patientId`, `patientId` and `department`, or all three, while sorting by `admissionDate` descending. This design covers the required access patterns with one index, avoiding unnecessary index proliferation.

Answer

Use a collection group query by storing patient records as subcollections under each department document, with fields `patientId`, `admissionDate`, and create a composite index on `admissionDate` (descending) and `patientId`.

Answer

Store each patient record as a document in a top-level collection `patients`, with fields `patientId`, `admissionDate`, `department`, and create a composite index on `department`, `admissionDate` (descending), and `patientId`.

Answer

Store each patient record as a document in a top-level collection `patients`, with fields `patientId`, `admissionDate`, `department`, and create a composite index on `patientId`, `admissionDate` (descending), and `department`.

Question 12

You are designing a schema for a Cloud SQL for PostgreSQL database that will store time-series data from IoT sensors. The data volume is high, and you need to optimize for write throughput and query performance for recent data. Which two schema design choices should you make? (Choose two.)

Accepted Answer

Use a partitioned table with range partitioning on the timestamp column, creating monthly partitions.. Partitioning by range on the timestamp column enables partition pruning for time-based queries and distributes writes, improving performance and manageability. A composite index on (sensor_id, timestamp) per partition accelerates common queries filtering by sensor and time. Together, these choices optimize for both write throughput and recent-data query performance in Cloud SQL for PostgreSQL.

Answer

Use a clustered index on the timestamp column to physically order rows by time.

Answer

Use a single unpartitioned table with a BRIN index on the timestamp column.

Answer

Store each sensor's data in a separate table.

Question 13

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 14

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 15

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