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

Question 1

A company is designing a Firestore schema for a chat application with millions of messages. They need to support real-time updates and efficient querying of recent messages per conversation. Which schema and indexing strategy is optimal?

Accepted Answer

Store messages as a subcollection under each conversation document. Create a composite index on (conversationId, timestamp desc).. Storing messages as a subcollection under each conversation document (Option A) is scalable and allows efficient queries with a composite index on (conversationId, timestamp desc). Option B (single collection) lacks natural grouping and may hit limits. Option C (subcollection without conversationId index) cannot filter by conversation efficiently. Option D (nested array) is limited to 1 MiB per document.

Answer

Store all messages in a single top-level collection. Create an index on (conversationId, timestamp desc).

Answer

Store messages in a subcollection with a single-field index on timestamp.

Answer

Use a parent document with a nested array of recent messages, and a separate collection for older messages.

Question 2

A data warehouse in BigQuery stores daily snapshots of customer data. The schema uses a single table with a snapshot_date partition column. Over time, the table has grown to 10 TB and queries often scan entire partitions. Which schema redesign would improve query performance and reduce costs significantly?

Accepted Answer

Use a nested and repeated structure to store all snapshots per customer in a single row.. Using nested and repeated fields (Option D) to store all snapshots per customer in a single row drastically reduces data scanned when querying by customer. Option A (clustering) still scans full partitions if querying across dates. Options B and C (separate tables/wildcards) still require scanning multiple partitions.

Answer

Create separate tables for each snapshot_date.

Answer

Use clustering on customer_id and snapshot_date.

Answer

Use a wildcard table with a _TABLE_SUFFIX filter.

Question 3

A team is designing a Cloud SQL for PostgreSQL schema for a multi-tenant SaaS application. They need to isolate tenant data while maintaining query performance and manageability. Which two approaches are appropriate? (Choose two.)

Accepted Answer

Use a single schema with a tenant_id column on every table and row-level security.. Separate schemas per tenant (B) provides logical isolation and easy backup/restore. Single schema with tenant_id and row-level security (C) is a standard multi-tenancy pattern. Options A and D are too costly. Option E offers no isolation.

Answer

Use separate databases per tenant.

Answer

Use a single table for all tenants with no tenant identifier.

Answer

Use a separate Cloud SQL instance per tenant.

Question 4

A company uses Cloud Spanner with a schema that includes a table 'Events' with primary key (EventId, Timestamp). They need to run range queries on Timestamp across all events. They notice slow queries. Which two actions can improve query performance? (Choose two.)

Accepted Answer

Create a secondary index on Timestamp.. Creating a secondary index on Timestamp (A) enables efficient range scans. Creating a covering index (D) that includes all queried columns avoids table lookups. Option B helps writes but not reads. Option C is about interleaving, not relevant. Option E changes the primary key, which could help but may cause hot spots; not the best immediate action.

Answer

Add a hash prefix to EventId to distribute writes.

Answer

Use interleaving with a parent table on EventId.

Answer

Change the primary key to (Timestamp, EventId).

Question 5

A data engineer is designing a BigQuery schema for a dataset that will be used for both ad-hoc analysis and scheduled dashboards. They want to optimize costs and performance. Which three strategies should they consider? (Choose three.)

Accepted Answer

Use partitioning on a date column for time-based queries.. Partitioning (A) reduces scanned data by date. Clustering (B) improves filter queries. Materialized views (D) precompute aggregates. Option C (multiple tables) increases management and query complexity. Option E (wildcard tables) can be costly if not used carefully and still scans all tables.

Answer

Use wildcard tables with a suffix filter.

Answer

Store data in multiple tables per day.

Question 6

A team executed the above DDL to create interleaved tables in Cloud Spanner. They need to query all orders for a specific customer. Which query will be most efficient?

Accepted Answer

SELECT * FROM Orders WHERE CustomerId = 1234;. Since Orders are interleaved under Customers on CustomerId, filtering by CustomerId (A) allows Spanner to directly access the colocated rows. Option B filters only by OrderId, which may require a full scan. Option C adds an extra condition but still benefits from CustomerId. Option D uses a join, which is unnecessary because interleaving already provides the relationship.

Answer

SELECT * FROM Orders WHERE CustomerId = 1234 AND OrderDate = '2023-01-01';

Answer

SELECT * FROM Customers JOIN Orders ON Customers.CustomerId = Orders.CustomerId WHERE Customers.CustomerId = 1234;

Answer

SELECT * FROM Orders WHERE OrderId = 5678;

Question 7

A company is setting up access control for a BigQuery dataset using the above IAM policy. An analyst who is a member of the group 'analysts@example.com' also has the user account 'analyst@example.com'. They need to create new tables in the dataset. What will be the outcome?

Accepted Answer

The analyst can create tables because they have dataOwner role on their user account.. IAM grants are additive. The user has the dataOwner role directly (A), which includes create table permissions. The group membership with dataViewer does not override. So the analyst can create tables. Option B is wrong because the user has explicit dataOwner. Option C (jobUser) is not needed. Option D (conflict) does not apply.

Answer

The analyst will get an error because of conflicting roles.

Answer

The analyst cannot create tables because the group only has dataViewer.

Answer

The analyst can create tables if they also have jobUser role.

Question 8

A data scientist runs a complex SQL query on a large BigQuery dataset and receives the above error. The query joins 10 tables and uses multiple window functions. Which action is most likely to resolve the issue?

Accepted Answer

Simplify the query by reducing the number of joins or using a temporary table.. The error indicates the query exceeded resource limits for tier 1, meaning it requires more intermediate resources. The best solution is to optimize the query (C) by reducing complexity, using subqueries, or breaking it into steps. Option A (increasing slots) does not affect tiers. Option B (quota increase) is for concurrency. Option D (billing tier flag) is deprecated.

Answer

Apply for a quota increase for concurrent queries.

Answer

Increase the number of slots allocated to the project.

Answer

Use the '--maximum_billing_tier' flag to increase the billing tier.

Question 9

You are designing a Firestore database for a chat application. Documents will store messages with fields: senderId, messageText, timestamp, conversationId. To efficiently retrieve the most recent 50 messages in a conversation, which index should you create?

Accepted Answer

A composite index on (conversationId, timestamp, __name__) descending. Option A creates a composite index on (conversationId, timestamp, __name__) with descending order on timestamp, which efficiently supports queries that filter by conversationId and order by timestamp descending, limiting to 50 results. Option B only indexes timestamp, not filtering by conversation. Option C indexes senderId, which is not used in the query. Option D indexes conversationId only, but without timestamp order, it would require sorting in memory.

Answer

A single-field index on timestamp

Answer

An index on conversationId only

Answer

A composite index on (senderId, timestamp)

Question 10

Your team is migrating an on-premises PostgreSQL database to Cloud SQL for PostgreSQL. The current schema uses table inheritance, which is not fully supported in Cloud SQL. What should you do to minimize application changes?

Accepted Answer

Redesign the schema using separate tables with joins. Option B is correct: redesign the schema using separate tables with joins, as this is the standard approach to replace inheritance and can be done with minimal application changes by creating views that emulate the inheritance hierarchy. Option A uses foreign data wrappers, which are not a direct replacement and add complexity. Option C uses materialized views, which don't support write operations. Option D is incorrect because Cloud SQL does not fully support table inheritance.

Answer

Continue using inheritance as Cloud SQL supports it fully

Answer

Use PostgreSQL foreign data wrappers to emulate inheritance

Answer

Use materialized views to combine data

Question 11

When designing a schema for a data warehouse in BigQuery, which table type is most cost-effective for storing raw event data that will be queried by date range filters?

Accepted Answer

A partitioned table partitioned by date column. Option C is correct: a partitioned table partitioned by date limits the data scanned to only the relevant partitions, reducing cost. Option A (regular table) scans all data. Option B (regular table clustered by timestamp) still scans all data. Option D (integer range partitioning on an ID) is not suitable for date queries and would not limit scans based on date.

Answer

A table with integer range partitioning on an ID column

Answer

A regular table with no partitioning

Answer

A regular table clustered on timestamp

Question 12

Your team needs to add a new non-nullable column with a default value to a large Cloud Spanner table. The table has thousands of simultaneous writes per second. Which approach minimizes downtime and resource usage?

Accepted Answer

Use ALTER TABLE ADD COLUMN with a non-null default value. Option D is correct: Cloud Spanner's ALTER TABLE ADD COLUMN with a non-null default value is an online operation that applies the default without rewriting rows, avoiding downtime and heavy resource consumption. Option A causes downtime. Option B is slow and resource-intensive. Option C requires an additional update phase, which can cause inconsistency and contention.

Answer

Use ALTER TABLE ADD COLUMN without a default value and then update rows in batches

Answer

Create a new table and use batch operations to copy data

Answer

Drop and recreate the table with the new column

Question 13

You are designing a BigQuery schema for IoT sensor data. The sensor readings have varying fields depending on the sensor type. You want to minimize storage costs and avoid schema maintenance when new sensor types are added. What is the best schema design?

Accepted Answer

Store the sensor data in a JSON column. Option A is correct: using a JSON column allows flexible schema without requiring ALTER TABLE when new fields appear; BigQuery efficiently stores JSON and can query it with standard SQL. Option B requires creating new tables for each sensor type, increasing maintenance. Option C and D are wasteful because many columns will be NULL for most rows.

Answer

Use a separate table per sensor type

Answer

Use a schema with a STRUCT containing all possible fields as optional

Answer

Use a wide table with many nullable columns

Question 14

You have a Cloud SQL for MySQL table that stores user logins with columns: user_id, login_time, ip_address. You frequently run queries to count logins by user for a specific date range. Which index would be most efficient?

Accepted Answer

A composite index on (login_time, user_id). Option B is correct: a composite index on (login_time, user_id) because the query filters by login_time range and then groups by user_id. The index can be used for both the WHERE clause (range scan on login_time) and then user_id is available for grouping without accessing the table. Option A puts user_id first, which is less efficient for range filtering on login_time. Options C and D are not as efficient as a composite index.

Answer

No index; rely on full table scan

Answer

Separate indexes on user_id and login_time

Answer

A composite index on (user_id, login_time)

Question 15

You have a BigQuery table with billions of rows partitioned by date and clustered on country. Users frequently query the table to compute total sales by product for a specific month. The product field has high cardinality (millions of distinct values). Which optimization would improve query performance the most?

Accepted Answer

Re-cluster the table with product as the first clustering column. Option C is correct: re-clustering the table with product as the first clustering column ensures that the aggregation benefits from clustering, as the query groups by product and filters on date. The current clustering on country is not used in the query, so it provides no benefit. Option A keeps the current clustering, which is ineffective. Option B is not possible because BigQuery only supports time-unit or integer range partitioning. Option D uses wildcard tables, which doesn't help with performance.

Answer

Use a wildcard table pattern to query across date partitions

Answer

Partition by product

Answer

Keep the current clustering on country