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?

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

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 allows for natural data locality and efficient queries. The composite index on (conversationId, timestamp desc) enables Firestore to quickly retrieve the most recent messages for a given conversation without scanning unrelated data, which is critical for real-time updates at scale.

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.. Storing all snapshots per customer in a nested and repeated structure (e.g., an array of structs) eliminates the need to scan multiple rows for the same customer across different snapshot dates. This reduces the table size by avoiding row duplication, and queries that filter on customer_id can leverage the nested structure to read only the relevant data, significantly cutting both query costs (less data scanned) and improving performance.

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.. Using a single schema with a tenant_id column and row-level security (RLS) in PostgreSQL allows tenant data isolation at the row level while maintaining a single database and schema. RLS policies automatically filter rows based on the current session's tenant context, ensuring performance is optimized through standard indexing on tenant_id and avoiding the overhead of multiple databases or schemas.

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 allows Cloud Spanner to efficiently perform range queries on that column without scanning the entire table. Without this index, Spanner must perform a full table scan, which is slow for large datasets. The secondary index provides a sorted structure that directly supports the range scan operation.

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 on a date column (Option C) is correct because BigQuery uses the date column to prune partitions during query execution, significantly reducing the amount of data scanned and thus lowering costs and improving performance for time-based queries. This is a core optimization for both ad-hoc analysis and scheduled dashboards that frequently filter by date ranges.

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;. In Cloud Spanner, interleaved tables store child rows physically adjacent to their parent row within the same split. Querying Orders directly on the interleaved key (CustomerId) allows Spanner to perform a local index scan within the parent row's split, avoiding a distributed cross-table join. This leverages the interleaved table's physical clustering for the most efficient retrieval.

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 policies grant permissions based on the union of all roles assigned to the user, regardless of whether they come from group membership or direct user assignment. The analyst has the `dataOwner` role directly on their user account, which includes the `bigquery.tables.create` permission required to create new tables. Group membership with a lower-privilege role (e.g., `dataViewer`) does not override or conflict with the higher-privilege role on the user account.

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 is likely a resource or memory exhaustion error caused by the complexity of the query (10 joins and multiple window functions). Simplifying the query by reducing joins or using temporary tables reduces the amount of data shuffled and processed in a single stage, directly addressing the root cause. In BigQuery, complex queries with many joins and window functions can exceed slot memory limits, and breaking them into simpler steps avoids this.

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. To efficiently retrieve the most recent 50 messages for a specific conversation, you need a composite index on (conversationId, timestamp, __name__) with descending order. This allows Firestore to perform a range query on conversationId and then order by timestamp descending, using the index to skip scanning irrelevant documents. The __name__ field is included to ensure the index is covering and to handle document name ordering ties, which is required for consistent pagination with descending order.

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. Cloud SQL for PostgreSQL does not support table inheritance, a PostgreSQL-specific feature that allows child tables to inherit columns from a parent table. Option D is correct because redesigning the schema using separate tables with joins is the standard relational approach that works across all PostgreSQL deployments, including Cloud SQL, and minimizes application changes by preserving the logical data model.

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. Partitioned tables in BigQuery allow you to divide a table into segments based on a date, timestamp, or integer column. When querying with a date range filter, BigQuery can prune partitions, scanning only the relevant data rather than the entire table. This dramatically reduces the amount of data processed, lowering query costs and improving performance, making it the most cost-effective choice for storing raw event data that is frequently queried by 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. Adding a non-nullable column with a default value in Cloud Spanner is a metadata-only operation that does not rewrite existing rows or block reads/writes. This minimizes downtime and resource usage even under thousands of concurrent writes per second, as the default value is applied logically at read time.

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. Storing sensor data in a JSON column leverages BigQuery's native support for semi-structured data (the `JSON` data type), allowing you to ingest records with varying fields without schema changes. This minimizes storage costs by avoiding the overhead of many NULL columns and eliminates the need for schema maintenance when new sensor types are added, as BigQuery can query JSON fields directly using functions like `JSON_EXTRACT` or dot notation.

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). 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 (no index) would require a full table scan, which is inefficient. Option B (separate indexes) may allow index merge but is less efficient than a single composite index. Option D puts user_id first, which is less efficient for range filtering on login_time because the index would need to scan all user_id values within the range.

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. B is correct because clustering on a high-cardinality column like product, especially as the first clustering column, allows BigQuery to prune blocks more effectively during queries that filter or group by product. Since the table is already partitioned by date, clustering on product reduces the amount of data scanned when computing total sales by product for a specific month, directly addressing the query pattern.

Answer

Use a wildcard table pattern to query across date partitions

Answer

Partition by product

Answer

Keep the current clustering on country