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

Question 1

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?

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

Accepted Answer

Create an Orders table and a LineItems table interleaved in Orders with ORDER_ID as the parent key.. 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 2

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.. 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 3

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 sorts data within partitions by up to four columns, so filtering or aggregating on frequently used columns (e.g., customer_id) reduces bytes scanned and improves performance. Option B is correct because BigQuery supports partitioning by a DATE or TIMESTAMP column, by an INTEGER column (e.g., range partitioning on a numeric key), and also by ingestion time, which is essential when mapping Oracle partitioned tables. Option D is correct because BigQuery materialized views are automatically refreshed in the background when base table data changes, with refresh intervals configurable, so migrated Oracle materialized views do not need manual refresh jobs. Option C is not correct because BigQuery is a columnar, serverless engine that does not require or support user-created indexes on WHERE-clause columns; it relies on partitioning, clustering, and automatic storage optimizations instead. Option E is not correct because BigQuery only enforces NOT NULL and does not enforce unique constraints or foreign keys (they can be declared for informational purposes but are not enforced), so data integrity must be handled by the ETL process.

Answer

BigQuery requires explicit indexes on columns used in WHERE clauses.

Answer

BigQuery supports unique constraints and foreign keys for data integrity.

Question 4

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.. 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 5

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 in Cloud Spanner the primary key prefix determines data locality, so starting the key with the category column colocates products of the same category in contiguous key ranges, enabling efficient range scans for category-based product queries. Option E is correct because interleaved tables physically co-locate child rows (product variants) with their parent product row, and since variants are always accessed with the parent, this eliminates expensive joins and reduces latency for those reads. Option A is wrong because secondary indexes in Spanner are themselves keyed by the base table's primary key, so primary key design cannot be ignored; poor key design leads to hotspots even with indexes. Option B is wrong because a leading timestamp key creates monotonically increasing keys that concentrate writes on the last split, causing hotspots rather than efficient partitioning. Option C is wrong because interleaving unrelated entities that are not accessed together adds write contention and storage coupling without query benefit, and interleaving is not a substitute for joins in general.

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 6

Arrange the steps to perform a point-in-time recovery (PITR) for a Cloud SQL instance.

Accepted Answer

Enable automatic backups and binary logging, then create a new Cloud SQL instance from the backup at the desired point in time.. PITR requires backups and binary logs enabled; then you create a new instance from backup at the specific time.

Answer

Create a new Cloud SQL instance from the latest backup, then enable binary logging, then perform point-in-time recovery.

Answer

Perform point-in-time recovery directly on the existing Cloud SQL instance by restoring the backup.

Answer

Enable binary logging only, then create a new instance from the backup, then enable automatic backups.

Question 7

Arrange the steps to import data from Cloud Storage into Cloud Firestore using a managed import.

Accepted Answer

First, run the gcloud firestore import command specifying the Cloud Storage URI. Second, monitor the import operation using gcloud firestore operations describe. Third, verify the imported data in Cloud Firestore.. Import needs properly formatted files; use gcloud command, then monitor and verify.

Answer

First, monitor the import operation using gcloud firestore operations describe. Second, run the gcloud firestore import command. Third, verify the imported data in Cloud Firestore.

Answer

First, verify the imported data in Cloud Firestore. Second, run the gcloud firestore import command. Third, monitor the import operation.

Answer

First, run the gcloud firestore import command. Second, verify the imported data in Cloud Firestore. Third, monitor the import operation.

Question 8

A company uses Cloud Spanner with a schema that has a table 'Orders' with primary key (CustomerId, OrderDate, OrderId). They notice hotspots on a specific customer. Which schema change would best distribute load?

Accepted Answer

Add a hash of CustomerId as a prefix to the primary key.. Adding a hash of CustomerId as a prefix to the primary key distributes writes across multiple key ranges, preventing hotspots on a single customer. Cloud Spanner splits data by primary key ranges, so a monotonically increasing or heavily skewed key like CustomerId causes all writes for that customer to hit one split. Hashing the prefix randomizes distribution.

Answer

Use a secondary index on CustomerId.

Answer

Split the table into multiple tables per region.

Answer

Change primary key to OrderId only.

Question 9

An e-commerce platform uses Cloud SQL for PostgreSQL. They need to run complex reporting queries that join several tables. These queries are slowing down the transactional workload. What should they do?

Accepted Answer

Use read replicas to offload reporting queries.. Read replicas in Cloud SQL for PostgreSQL allow you to offload read-only queries, such as complex reporting joins, from the primary instance. This separation reduces contention for CPU, memory, and I/O resources, preserving transactional performance. Read replicas are asynchronous and can handle heavy analytical workloads without impacting the primary's write path.

Answer

Create materialized views for common reports.

Answer

Change all joins to use subqueries.

Answer

Increase the number of vCPUs on the primary instance.

Question 10

A financial services company uses Cloud Spanner with a database that has multiple tables with interleaved relationships. They need to enforce a strict consistency requirement across two related tables that are not interleaved. Which method ensures global strong consistency?

Accepted Answer

Use Spanner's built-in atomicity by executing the updates in a single read-write transaction.. Cloud Spanner provides external consistency (global strong consistency) across all tables, interleaved or not, through the use of distributed read-write transactions that leverage the TrueTime API. By executing updates to both non-interleaved tables within a single read-write transaction, Spanner ensures that all mutations are applied atomically and are visible globally at a single timestamp, meeting the strict consistency requirement.

Answer

Use Cloud Pub/Sub to eventually synchronize the tables.

Answer

Use a commit timestamp-based approach to synchronize writes.

Answer

Use a client-side distributed transaction across the two tables.

Question 11

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 12

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 13

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 14

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 15

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)