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

Question 1

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?

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

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 2

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 3

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 4

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 5

You are designing a schema for a Cloud SQL for PostgreSQL database that supports full-text search across millions of product descriptions. The application requires fast search results ranked by relevance. Which schema design is most appropriate?

Accepted Answer

Use a tsvector column with a GIN index on that column. PostgreSQL's tsvector data type, combined with a GIN index, is specifically designed for full-text search. It preprocesses text into lexemes, supports stemming and ranking, and the GIN index enables fast lookups for millions of rows, meeting the requirement for relevance-ranked results.

Answer

Use a separate Elasticsearch instance

Answer

Use a LIKE '%term%' query with a B-tree index

Answer

Use materialized view with trigram indexes

Question 6

Which two of the following are best practices when designing BigQuery schemas? (Choose two.)

Accepted Answer

Use denormalization to reduce the number of joins. Option B is correct because BigQuery is a columnar, distributed analytics engine where joins are expensive and require data shuffling across slots, so denormalizing (flattening) related data into wide tables minimizes joins and improves query performance. Option D is correct because using repeated fields (arrays) to embed parent-child relationships in a single table lets queries use UNNEST instead of joins, which is the recommended pattern for hierarchical data in BigQuery. Option A is not a schema design best practice but an access-control feature, option C is not inherently a best practice since RECORD should be used when nesting genuinely reduces joins or models structure, and option E is wrong because consolidating all data into one table harms performance, governance, and cost efficiency.

Answer

Use column-level security to restrict access

Answer

Use the type RECORD for structured data

Answer

Use a single table for all data to simplify queries

Question 7

Refer to the exhibit. You are reviewing the following Cloud Spanner DDL statement for a table storing customer orders. What potential performance issue will arise with this schema?

Accepted Answer

The OrderId is likely to be sequentially generated, causing write hotspots. Cloud Spanner uses a distributed architecture that splits data across splits based on the primary key range. If OrderId is sequentially generated (e.g., auto-increment), all new inserts will target the same split, creating a write hotspot that degrades throughput and latency. This is a well-known anti-pattern in Spanner; the recommended approach is to use a UUID or a monotonically increasing key with a hash prefix to distribute writes evenly.

Answer

The primary key includes two columns which reduces insert performance

Answer

The TotalAmount column should be INTEGER for performance

Answer

The table lacks a foreign key constraint

Question 8

Refer to the exhibit. You receive the following query output showing bytes processed for a BigQuery query. The table is partitioned by date and clustered on country. What is the most likely reason for the high bytes processed?

Accepted Answer

The query does not filter on the clustering column, causing full scan of selected partitions. BigQuery clustering only reduces the bytes scanned when the query filters on the clustering column (country). Without a WHERE clause on country, BigQuery must scan all rows in the selected partitions, even though partition pruning may reduce the date range. The high bytes processed indicates that clustering is not being leveraged, so the query performs a full scan of the chosen partitions.

Answer

The GROUP BY country requires sorting all rows

Answer

The table is not partitioned correctly

Answer

The date range is too wide

Question 9

A company is designing a schema for time-series sensor data in Cloud Spanner. They need to efficiently query the latest reading for each sensor. Which schema design is most appropriate?

Accepted Answer

Create a Sensors table and an interleaved Readings table with primary key (SensorId, Timestamp DESC). The most appropriate because it uses an interleaved Readings table under the Sensors table with the primary key (SensorId, Timestamp DESC). This allows efficient retrieval of the latest reading for each sensor by taking the first row per SensorId using the descending timestamp order. Interleaving ensures that rows for the same sensor are stored together, minimizing cross-node reads. Option A (single table with wide rows) leads to large rows and poor scalability. Option B (Cloud SQL) is not designed for high-throughput time-series data at scale. Option D (Bigtable) is a good choice for time-series but the question specifically requires using Cloud Spanner.

Answer

Use a single table with columns for each sensor and wide rows

Answer

Use Cloud SQL with a normalized schema

Answer

Use Cloud Bigtable with row keys (SensorId#Timestamp)

Question 10

A data warehouse in BigQuery stores event logs with nested and repeated fields (e.g., page views within a session). Which schema type is optimal for storing this data?

Accepted Answer

Use ARRAY> columns for nested repeated data. BigQuery natively supports nested and repeated data through ARRAY> columns, which preserve the hierarchical relationship (e.g., sessions containing page views) without joins and allow efficient, columnar access. Option C is optimal because it matches BigQuery's native storage model, reduces data duplication, and lets queries use UNNEST to flatten only when needed. This is the schema pattern Google recommends for event-log workloads.

Answer

Use RECORD type columns for each nested level

Answer

Normalize into separate tables and join

Answer

Store as JSON strings and parse at query time

Question 11

A Cloud Spanner application experiences high write latency on a table with a monotonically increasing primary key. Which schema change will most effectively reduce latency?

Accepted Answer

Modify the primary key to include a hash of the original key as a leading column. Cloud Spanner partitions data by primary key ranges, and a monotonically increasing key (e.g., timestamp or auto-increment) causes all writes to go to the last partition, creating a hotspot. By adding a hash of the original key as a leading column, writes are distributed across multiple partitions, significantly reducing write latency. This is a standard best practice for avoiding hotspots in Spanner.

Answer

Convert the table to an interleaved table

Answer

Add a secondary index on the existing key

Answer

Increase the number of nodes in the instance

Question 12

A BigQuery table stores daily sales data. The team commonly queries data for a specific date range. Which schema optimization will reduce query cost and improve performance?

Accepted Answer

Partition the table by date column. Partitioning the table by the date column allows BigQuery to prune entire partitions when querying a specific date range, drastically reducing the amount of data scanned. Since BigQuery charges by the bytes processed, this directly lowers query cost and improves performance by reading only the relevant partitions.

Answer

Create a view over the table

Answer

Create a materialized view with a filter on date

Answer

Cluster the table by date column

Question 13

A Cloud SQL for PostgreSQL database experiences lock contention during heavy concurrent writes on a single table. Which schema design change can most effectively reduce contention?

Accepted Answer

Partition the table by a key that spreads write load. Partitioning the table by a key that spreads write load (e.g., a hash of the user ID or timestamp) reduces lock contention because each partition is a separate physical storage unit with its own lock manager. Concurrent writes targeting different partitions can proceed in parallel without blocking each other, directly addressing the contention on a single table.

Answer

Deploy read replicas to offload reads

Answer

Use a connection pooler like PgBouncer

Answer

Create materialized views for read queries

Question 14

A Cloud Spanner database needs to add a column 'discount' to the 'Products' table without any downtime. The table is actively used. What is the correct approach?

Accepted Answer

Execute ALTER TABLE Products ADD COLUMN discount FLOAT64. Cloud Spanner supports online schema changes via ALTER TABLE without downtime. The operation is performed asynchronously in the background, allowing the table to remain fully available for reads and writes. The new column 'discount' is automatically populated with NULL for existing rows. Option A is incorrect because creating a new table and copying data introduces downtime and complexity. Option C is incorrect because a secondary index cannot add a column; it only indexes existing columns. Option D is incorrect because a generated column is derived from other columns, not used to add a new independent column.

Answer

Create a new table with the column and copy data over

Answer

Create a secondary index that includes the new column

Answer

Define a generated column based on an existing column

Question 15

Which TWO are best practices for designing a Cloud Spanner schema?

Accepted Answer

Use commit timestamp columns to track row versions. Cloud Spanner's commit timestamp columns allow you to automatically track the time of the last write to a row, which is essential for implementing optimistic concurrency control, ordering versions, and building change data capture pipelines. This feature leverages the TrueTime API to provide globally consistent timestamps, making it a best practice for versioning and auditing.

Answer

Avoid secondary indexes to keep writes faster

Answer

Use monotonically increasing primary keys

Answer

Store all related data in a single row to avoid joins