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

Question 1

A financial analytics firm is designing a BigQuery schema for time-series data that arrives continuously. They need to partition the table to optimize query performance and minimize costs. The data includes a `transaction_time` TIMESTAMP column and a `customer_id` STRING column. Queries often filter by a specific date range and sometimes by `customer_id`. The table is expected to grow by several terabytes per month, and they want to avoid scanning unnecessary data. Which partitioning strategy should they use?

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

Accepted Answer

Partition by `transaction_time` with daily granularity.. Partitioning by `transaction_time` with daily granularity is optimal because it matches the frequent date-range filters, enabling partition pruning. This reduces the amount of data scanned and lowers query costs. Daily partitions are manageable for terabytes per month, avoiding excessive metadata. Clustering on `customer_id` could be added for further optimization, but partitioning is the primary requirement.

Answer

Partition by `transaction_time` with hourly granularity.

Answer

Use a clustered table on `customer_id` without partitioning.

Answer

Partition by `customer_id` using integer range partitioning.

Question 2

A financial analytics team is designing a BigQuery schema for a fact table that stores millions of transactions per day. Queries often filter on transaction_date and customer_id, and aggregate on amount. The team wants to minimize query costs and maximize performance. What schema design should they use?

Accepted Answer

Partition the table by transaction_date and cluster by customer_id.. Partitioning by transaction_date allows BigQuery to prune partitions when queries filter on that column, reducing bytes scanned and cost. Clustering by customer_id then sorts data within each partition, making filters and aggregations on customer_id more efficient. This design directly addresses the query patterns and is optimal for large fact tables in BigQuery.

Answer

Use a partitioned table with ingestion-time partitioning and cluster by customer_id.

Answer

Partition the table by customer_id and cluster by transaction_date.

Answer

Cluster the table by transaction_date and customer_id without partitioning.

Question 3

A database engineer is designing a Cloud Spanner schema for a global inventory system. The `Inventory` table is keyed by `WarehouseId` (a string) followed by `ProductId` (a string). Most queries retrieve all products for a single warehouse, and some queries look up a product across all warehouses. The team wants to optimize read performance and avoid hotspots. Which two design choices should the engineer apply? (Choose two.)

Accepted Answer

Keep `WarehouseId` as the first key column so rows for a warehouse are stored contiguously and range scans by warehouse are efficient.. Cloud Spanner stores rows in primary key order, so leading with `WarehouseId` makes warehouse-centric range scans contiguous and fast, matching the dominant query pattern. The secondary access pattern, looking up a product across all warehouses, is served by a secondary index on `ProductId`, which provides a dedicated access path without scanning the base table. Together these choices optimize both query directions without introducing hotspots.

Answer

Interleave a `ProductDetails` child table under `Inventory` to improve join performance.

Answer

Reverse the key order to `ProductId`, `WarehouseId` to balance reads across splits.

Answer

Add a `CreatedTimestamp` column as the first key column to distribute writes uniformly.

Question 4

A financial analytics team is designing a BigQuery schema for a table that will store daily stock trades. The table will contain columns: trade_id (STRING), symbol (STRING), trade_date (DATE), price (FLOAT64), and volume (INT64). The table will be queried frequently for aggregations by symbol and trade_date, and also for full table scans to export raw data. The data volume is about 10 TB per year, and the team wants to minimize query costs. They plan to partition the table. What partitioning strategy should they use?

Accepted Answer

Partition by trade_date using daily partitions, and cluster by symbol.. Partitioning by trade_date with daily partitions enables partition pruning for date filters, significantly reducing bytes scanned. Clustering by symbol further optimizes queries that filter or aggregate by symbol within each partition. This combination is the most cost-effective for the given query patterns and data volume, as it leverages BigQuery's ability to prune both partitions and clusters.

Answer

Do not partition the table, but cluster by symbol and trade_date.

Answer

Partition by symbol using integer range partitioning, and cluster by trade_date.

Answer

Partition by trade_date using monthly partitions, and cluster by trade_id.

Question 5

A media streaming company uses Firestore for its user profiles and viewing history. The `users` collection contains documents with fields: `userId`, `email`, `subscriptionTier`, and `lastLogin`. The company wants to query users by `subscriptionTier` and `lastLogin` (e.g., to find premium users who haven't logged in for 30 days) and also by `email` for login. They want to minimize the number of indexes and avoid unnecessary composite indexes. What is the best schema design?

Accepted Answer

Create a composite index on `subscriptionTier` and `lastLogin`, and rely on the automatic single-field index for `email`.. The best design is to create a composite index on `subscriptionTier` and `lastLogin` and rely on the automatic single-field index for `email`. Firestore automatically indexes individual fields, so `email` queries are covered. The compound query on `subscriptionTier` and `lastLogin` requires a composite index, which should be added explicitly. This minimizes the number of indexes and avoids unnecessary composite indexes.

Answer

Use a collection group query on a subcollection `loginHistory` under each user, and filter by `subscriptionTier` and `lastLogin`.

Answer

Create a composite index on `email` and `subscriptionTier`, and another on `lastLogin`.

Answer

Store `subscriptionTier` and `lastLogin` as a combined field (e.g., `tier_lastLogin`) and create a single-field index on it.

Question 6

A team is designing a schema for a new Cloud SQL for PostgreSQL database that will store customer orders. They need to ensure that each order is associated with a valid customer and that deleting a customer is prevented if they have existing orders. Which schema design should they use?

Accepted Answer

Create a foreign key constraint on the orders table referencing the customers table with ON DELETE RESTRICT.. A foreign key constraint on the orders table referencing the customers table ensures referential integrity. Using ON DELETE RESTRICT prevents deletion of a customer who has orders, directly satisfying the requirement. This declarative approach is simpler and more reliable than triggers or check constraints, which cannot enforce cross-table references.

Answer

Create a trigger on the customers table that raises an exception if a delete is attempted when orders exist.

Answer

Create a foreign key constraint on the orders table referencing the customers table with ON DELETE CASCADE.

Answer

Create a check constraint on the orders table to validate that the customer_id exists in the customers table.

Question 7

A startup is building a mobile app using Firestore in Native mode. They need to store user profiles with fields such as `userId`, `displayName`, `email`, and `lastLogin`. The app must support queries that filter users by `email` and sort by `lastLogin`. What is the most appropriate way to structure the Firestore data?

Accepted Answer

Create a collection `users` where each document ID is the `userId` and fields include `email` and `lastLogin`. Add a composite index on `email` and `lastLogin`.. Firestore's data model uses collections and documents, and queries that combine an equality filter with an order by on a different field require a composite index. Storing each user as a document with a unique ID and adding a composite index on the filtered and sorted fields is the idiomatic and scalable approach. Single-document designs hit size limits, and subcollection designs hinder cross-user queries.

Answer

Create a collection `users` and store each user as a subcollection under a document keyed by `email`.

Answer

Create a single document `users` that contains an array of user profile maps, and query using array-contains.

Answer

Create a collection `users` and rely on automatic single-field indexes for `email` and `lastLogin` without any composite index.

Question 8

A healthcare company is designing a Cloud Firestore schema for a patient management system. They need to store patient records and allow efficient querying by multiple fields such as `lastName`, `dateOfBirth`, and `city`. They also need to retrieve a patient's full record by a unique `patientId`. Which TWO schema design strategies should they use to optimize query performance and cost? (Choose two.)

Accepted Answer

Create composite indexes for combinations of `lastName`, `dateOfBirth`, and `city`.. The optimal strategies are to use `patientId` as the document ID for direct lookups and to create composite indexes for multi-field queries. Document ID lookups are fast and cheap, while composite indexes enable efficient querying by combinations of fields. These align with Firestore's strengths and avoid unnecessary complexity or limitations.

Answer

Denormalize all patient data into a single document to avoid joins.

Answer

Use a separate collection for each city to partition data.

Answer

Store each patient record as a subcollection under a `patients` collection.

Question 9

A company is designing a BigQuery schema for a dataset that will store clickstream events. The events have a nested structure: each event has a user_id, timestamp, and an array of product interactions, where each interaction includes product_id, action, and duration. The team needs to write queries that analyze user behavior, such as finding all users who viewed a specific product. They also want to minimize query costs. Which TWO schema design considerations should they apply? (Choose two.)

Accepted Answer

Use a repeated RECORD field for product interactions to keep related data together and avoid joins.. Using a repeated RECORD for product interactions keeps related data together and allows efficient querying of nested fields without joins. Partitioning by timestamp reduces data scanned for time-based queries, lowering costs. Together, these design choices optimize both storage and query performance in BigQuery.

Answer

Use a STRING field to store product interactions as JSON, and parse it at query time.

Answer

Flatten the product interactions into separate rows in a single table, duplicating user_id and timestamp for each interaction.

Answer

Create a separate table for product interactions and join it with the events table on user_id and timestamp.

Question 10

A startup is designing a schema for a new Cloud SQL for PostgreSQL database. They need to store user information and ensure that each user has a unique email address. They also want to enforce that the email is always provided. Which schema constraint should they apply to the `email` column?

Accepted Answer

NOT NULL and UNIQUE. The email column should have both NOT NULL and UNIQUE constraints. NOT NULL ensures the email is always provided, and UNIQUE prevents duplicate emails. This combination is the correct way to enforce the business rules without conflicting with the primary key, which might be a surrogate key like `user_id`.

Answer

PRIMARY KEY

Answer

CHECK (email IS NOT NULL)

Answer

FOREIGN KEY

Question 11

A financial analytics team uses BigQuery to store transactional data in a table `transactions` with columns `transaction_id`, `customer_id`, `amount`, and `timestamp`. The table is partitioned by `DATE(timestamp)` and clustered by `customer_id`. Analysts frequently run queries that filter on `customer_id` and a specific date range. They report that some queries are slow and scan more data than expected. Which action should the database engineer take to improve query performance and reduce cost?

Accepted Answer

Ensure queries include a filter on the partitioning column and use the clustering column in the WHERE clause or JOIN conditions.. BigQuery's cost and performance depend on how much data a query scans. Partitioning by date allows the query engine to skip partitions that don't match the filter, and clustering by customer_id sorts data within each partition so that filters on that column can skip blocks. If analysts write queries without a date filter or with expressions that prevent pruning, the table is fully scanned. Educating them to use the partitioning column in filters and the clustering column in conditions maximizes efficiency.

Answer

Add a secondary index on `customer_id` using BigQuery's `CREATE INDEX` statement.

Answer

Convert the table to a materialized view that pre-aggregates data by `customer_id` and date.

Answer

Change the partitioning column to `customer_id` and cluster by `DATE(timestamp)`.

Question 12

A Cloud Spanner database is used for a global inventory system. The main table 'Inventory' has a primary key of (WarehouseId, ProductId). The application frequently runs a query that retrieves all products for a given WarehouseId, ordered by ProductId. The database has a large number of warehouses, and each warehouse has a moderate number of products. To optimize this query, which schema design should you use?

Accepted Answer

Keep the primary key as (WarehouseId, ProductId) and rely on the natural ordering of the primary key for the query.. Cloud Spanner sorts data by primary key. A primary key of (WarehouseId, ProductId) ensures that all products for a warehouse are stored together and sorted by ProductId. The query can use a range scan on the primary key, which is efficient and requires no extra index. Other options either disrupt the ordering or add unnecessary overhead.

Answer

Create an interleaved table 'WarehouseProducts' with a foreign key to 'Inventory' and a primary key of (WarehouseId, ProductId).

Answer

Change the primary key to (ProductId, WarehouseId) to cluster products together.

Answer

Create a secondary index on (WarehouseId, ProductId) and force the query to use it.

Question 13

A company is designing a schema for a new Cloud SQL for PostgreSQL database that will store customer orders. The `orders` table will have a foreign key to the `customers` table. The application frequently runs queries that join `orders` and `customers` on `customer_id` and filters by `order_date`. The team wants to ensure referential integrity and optimize join performance. Which two actions should they take? (Choose two.)

Accepted Answer

Create a foreign key constraint on `orders.customer_id` referencing `customers.customer_id`.. To enforce referential integrity between orders and customers, a foreign key constraint on orders.customer_id is required. To optimize join performance, an index on the foreign key column is necessary because PostgreSQL does not automatically index foreign key columns. Together, these two actions ensure data consistency and efficient lookups when joining the tables, which is critical as the orders table grows.

Answer

Create an index on `orders.order_date` to optimize filtering by date.

Answer

Partition the `orders` table by `order_date` to improve join performance.

Answer

Use a `CHECK` constraint to validate that `customer_id` exists in the `customers` table.

Question 14

A healthcare company is designing a Cloud BigQuery schema for patient records. The data includes a Patients table with a repeated field 'conditions' that stores an array of STRUCTs, each containing 'code' and 'diagnosis_date'. Analysts need to run queries that filter patients based on specific condition codes and also retrieve the diagnosis date for that condition. Which schema design approach is most efficient for these queries?

Accepted Answer

Keep the repeated field as is, and use UNNEST to flatten the array in queries.. BigQuery's nested and repeated fields are designed to efficiently store and query hierarchical data. Using UNNEST in queries allows analysts to flatten the array, filter by condition code, and retrieve the diagnosis date without joins. This leverages BigQuery's columnar storage and minimizes data scanned. Normalizing or converting to JSON would degrade performance and increase complexity. Thus, keeping the repeated field and using UNNEST is the most efficient design.

Answer

Normalize the schema by creating a separate Conditions table with a foreign key to Patients.

Answer

Convert the repeated field into a JSON string to allow flexible querying.

Answer

Create a separate column for each possible condition code to avoid arrays.

Question 15

A healthcare company is designing a Cloud Spanner schema for a patient records system. The primary table 'Patients' has a primary key 'PatientId' (STRING). A related table 'Visits' records each visit with a unique 'VisitId' (STRING) and must be queried together with patient data. The system requires strong consistency and low latency for queries that retrieve a patient and all their visits. Additionally, the schema must support efficient updates to patient demographic information. Which two design choices should you make? (Choose two.)

Accepted Answer

Define the 'Visits' table with a composite primary key of 'PatientId' and 'VisitId'.. Interleaving the 'Visits' table in 'Patients' with 'PatientId' as the prefix co-locates visits with their patient, enabling low-latency, strongly consistent retrieval of a patient and all their visits in a single operation. To interleave, the child table must have a composite primary key that includes the parent's key, so defining 'Visits' with 'PatientId' and 'VisitId' is required. This design also keeps patient demographics in a separate row, allowing efficient updates without affecting visits. Secondary indexes or denormalization would not achieve the same performance and consistency benefits.

Answer

Denormalize patient demographic information into the 'Visits' table to avoid joins.

Answer

Use a foreign key constraint from 'Visits' to 'Patients' on 'PatientId' to enforce referential integrity.

Answer

Create a secondary index on 'Visits' for 'PatientId' to speed up queries that retrieve visits by patient.