Courseiva

CCNA Db Schema Design Questions

25 of 100 questions · Page 2/2 · Db Schema Design topic · Answers revealed

Practice these questionsExam hubAll questions
76
MCQmedium

A company is designing a database schema for a global e-commerce platform. Orders are created with high frequency, and order status updates occur frequently. The team needs to choose a primary key strategy for the orders table in Spanner. Which approach minimizes hot-spotting?

A.Use a monotonically increasing integer (e.g., auto-increment)
B.Use a timestamp as the primary key
C.Use a composite key with user_id and order_date
D.Use a universally unique identifier (UUID) as the primary key
AnswerD

Distributes writes uniformly across splits.

Why this answer

In Spanner, monotonically increasing or time-ordered primary keys cause hot-spotting because all new writes are directed to the same tablet server, creating a single point of contention. UUIDs are randomly distributed, ensuring writes are spread evenly across the entire key space, which minimizes hot-spotting and maximizes write throughput.

Exam trap

Google Cloud often tests the misconception that composite keys with a user_id prefix are sufficient to avoid hot-spotting, but the trap is that any time-ordered component (like order_date) in the key still causes sequential writes to target the same tablet, negating the distribution benefit.

How to eliminate wrong answers

Option A is wrong because monotonically increasing integers concentrate writes on the last tablet, causing severe hot-spotting. Option B is wrong because timestamps are inherently monotonically increasing, leading to the same hot-spotting issue as auto-increment keys. Option C is wrong because a composite key with user_id and order_date still has a time-ordered component (order_date) that causes sequential writes to cluster on the same tablet, especially for users placing orders in quick succession.

Practice this question →
77
MCQhard

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?

A.Use a tsvector column with a GIN index on that column
B.Use a separate Elasticsearch instance
C.Use a LIKE '%term%' query with a B-tree index
D.Use materialized view with trigram indexes
AnswerA

PostgreSQL full-text search with tsvector/GIN is purpose-built for fast ranked search.

Why this answer

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.

Exam trap

Google often tests the misconception that LIKE queries with B-tree indexes are sufficient for full-text search, but the trap here is that LIKE '%term%' cannot leverage a B-tree index and forces a sequential scan, while tsvector with GIN is purpose-built for this workload.

How to eliminate wrong answers

Option B is wrong because it introduces an external Elasticsearch instance, which violates the schema design constraint for Cloud SQL for PostgreSQL and adds operational complexity; the question asks for a schema design within PostgreSQL. Option C is wrong because a LIKE '%term%' query cannot use a B-tree index efficiently—it requires a full table scan, which is impractical for millions of product descriptions. Option D is wrong because materialized views with trigram indexes (pg_trgm) support fuzzy matching but are not optimized for full-text search ranking and relevance scoring; they are better suited for pattern matching, not linguistic search.

Practice this question →
78
Multi-Selectmedium

Your team is designing a schema for Cloud SQL (MySQL) for a content management system. You need to implement full-text search on article content. Which TWO schema design choices are appropriate? (Choose two.)

Select 2 answers
A.Use the LIKE operator with wildcards for pattern matching.
B.Store article content in a Cloud Storage bucket and query metadata.
C.Normalize content into a separate table and use joins.
D.Use Cloud SQL's built-in full-text search feature.
E.Add a FULLTEXT index on the content column.
AnswersD, E

Cloud SQL for MySQL supports full-text search via FULLTEXT indexes and MATCH AGAINST queries.

Why this answer

Cloud SQL for MySQL provides built-in full-text search capabilities that allow efficient searching of text data. Adding a FULLTEXT index on the content column enables the use of MATCH...AGAINST queries, which are optimized for natural language search and are far more performant than LIKE operations. This is the correct approach because it leverages the database engine's native indexing and search algorithms.

Exam trap

A common pitfall in Cloud SQL for MySQL is relying on LIKE with wildcards for text search, which cannot use FULLTEXT indexes and leads to full table scans. Cloud SQL supports FULLTEXT indexes and MATCH...AGAINST queries for efficient full-text search.

Practice this question →
79
MCQeasy

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?

A.A partitioned table partitioned by date column
B.A table with integer range partitioning on an ID column
C.A regular table with no partitioning
D.A regular table clustered on timestamp
AnswerA

Only scans partitions matching the date range, minimizing cost.

Why this answer

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.

Exam trap

A common misconception is that clustering alone is sufficient for cost savings on date-range queries in BigQuery. However, without partitioning, BigQuery cannot skip entire storage blocks, so clustering only provides minor sorting benefits and does not reduce the amount of data scanned. Partitioning by date is essential for pruning and cost-effectiveness.

How to eliminate wrong answers

Option B is wrong because integer range partitioning on an ID column does not align with date-based queries; it would require scanning all partitions or using inefficient filters, and it does not leverage BigQuery's optimized date pruning. Option C is wrong because a regular table with no partitioning forces a full table scan on every query, even when filtering by date, leading to maximum cost and slower performance. Option D is wrong because clustering on timestamp alone, without partitioning, still requires scanning the entire table; clustering only sorts data within a table, but BigQuery cannot skip entire storage blocks without partitioning, so cost savings are minimal compared to partitioning.

Practice this question →
80
Multi-Selecthard

A company is migrating a large Oracle database to Cloud Spanner. The schema includes several tables with foreign key relationships. The team wants to minimize query latency for join queries that always involve a parent table and its children. Which THREE schema design strategies should the team consider? (Choose THREE.)

Select 3 answers
A.Design child table primary keys to start with the parent key (e.g., CustomerId, OrderId)
B.Denormalize frequently joined lookup tables into the parent table as repeated fields
C.Use parent-child interleaved tables where the child table's primary key includes the parent's primary key
D.Create secondary indexes on foreign key columns
E.Store foreign key relationships as JSON arrays in the parent table
AnswersA, B, C

Enables interleaving and efficient queries.

Why this answer

In Cloud Spanner, designing child table primary keys to start with the parent key (e.g., CustomerId, OrderId) enables efficient key-range scans and colocates related rows, reducing cross-node communication. Option B is correct because denormalizing frequently joined lookup tables into the parent table as repeated fields avoids joins entirely, further reducing latency. Option C is correct because parent-child interleaved tables physically store child rows adjacent to their parent row, minimizing splits and cross-node communication for join queries.

Options D and E are incorrect: secondary indexes do not provide the same physical colocation benefits, and storing foreign keys as JSON arrays in the parent table would complicate queries and fail to leverage Spanner's distributed architecture.

Exam trap

Google Cloud often tests the misconception that secondary indexes alone can optimize join performance in distributed databases, but in Spanner, physical colocation via interleaved tables is the key to minimizing query latency for parent-child joins.

Practice this question →
81
Multi-Selectmedium

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

Select 2 answers
A.Use separate databases per tenant.
B.Use a single schema with a tenant_id column on every table and row-level security.
C.Use a single table for all tenants with no tenant identifier.
D.Use a separate Cloud SQL instance per tenant.
E.Use separate schemas per tenant.
AnswersB, E

Row-level security enforces tenant isolation while keeping a single schema.

Why this answer

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.

Exam trap

Google often tests the misconception that separate databases or instances are required for data isolation, but the trap here is that PostgreSQL's row-level security and schema-based isolation (Option E) are both valid and more manageable at scale than physical separation.

Practice this question →
82
MCQmedium

A retail company uses Cloud SQL for PostgreSQL for inventory management. The schema has a table 'inventory' with columns: product_id, warehouse_id, quantity, last_updated. The table contains over 100 million rows. The application frequently runs aggregate queries to compute total quantity of a product across all warehouses (e.g., SELECT SUM(quantity) FROM inventory WHERE product_id = ?). These queries are slow, taking tens of seconds. The team tries a covering index on (product_id, quantity) but sees little improvement because they still need to scan many rows. They need to redesign the schema to improve aggregation performance. What is the best approach?

A.Add a covering index on (product_id, quantity).
B.Migrate the inventory table to Cloud Spanner and use interleaved indexes.
C.Use BigQuery as a read replica and query there.
D.Create a summary table 'product_totals' with columns product_id and total_quantity, and use triggers to keep it updated on INSERT/UPDATE/DELETE in inventory.
AnswerD

Pre-aggregation reduces the amount of work needed at query time.

Why this answer

Creating a summary table 'product_totals' that pre-aggregates total quantity per product, updated via triggers on INSERT, UPDATE, DELETE in the inventory table, dramatically speeds up aggregate queries by avoiding full scans of the large table. Option A (covering index on product_id, quantity) was tried and still requires scanning many rows to sum quantities, so it does not solve the performance issue. Option B (migrating to Cloud Spanner) is an unnecessary and costly migration.

Option C (using BigQuery as a read replica) adds latency and complexity without being a schema redesign. Thus, D is the best approach.

Exam trap

Candidates might assume that a covering index or a materialized view would be sufficient. However, Cloud SQL for PostgreSQL does not support materialized views with automatic refresh for this pattern, and a covering index still requires scanning all rows per product. The correct schema redesign is a summary table with triggers.

Practice this question →
83
MCQhard

Refer to the exhibit. The team notices high write latency on the Events table. They are inserting 1,000 events per second. The EventId is generated by a sequence. What is the most likely issue?

A.The sequential primary key creates a hotspot on a single split.
B.The allow_commit_timestamp option on CreatedAt column adds overhead.
C.The BYTES(MAX) data type causes excessive writing.
D.The node count is insufficient for the write throughput.
AnswerA

Sequential keys cause all writes to hit the same split, leading to contention and latency.

Why this answer

The sequential primary key (EventId generated by a sequence) causes all new writes to be directed to the last tablet or split in the table, creating a hotspot. In Cloud Spanner, this leads to contention on a single split, increasing write latency despite adequate overall throughput capacity.

Exam trap

In the Google Professional Cloud Developer Engineer exam, a common pitfall is confusing high write latency with insufficient node count or data type choices, when the real issue is often key design causing a hotspot on a single split in Cloud Spanner.

How to eliminate wrong answers

Option B is wrong because allow_commit_timestamp on the CreatedAt column does not add significant overhead; it simply enables commit timestamp-based reads and does not affect write latency. Option C is wrong because BYTES(MAX) data type does not inherently cause excessive writing; the issue is write distribution, not column size. Option D is wrong because the node count may be sufficient for the write throughput; the problem is that writes are not distributed across nodes due to the sequential key, not that there are too few nodes.

Practice this question →
84
MCQhard

A financial services company uses Cloud Spanner for a global transaction processing system. They notice that certain read queries on a table with frequent writes are returning stale data even though they use strong reads. The table has a primary key of (user_id, transaction_id) and a secondary index on (timestamp). What is the most likely cause of the stale reads?

A.The query is using a stale read timestamp.
B.The query is using a secondary index that has not yet been updated with the latest write.
C.The query is reading from a read-only replica.
D.Cloud Spanner is using eventual consistency for this query.
AnswerB

Secondary indexes can lag behind the base table; a strong read on the index may return stale data if the write committed after the index was last updated.

Why this answer

In Cloud Spanner, secondary indexes are implemented as separate tables that are updated asynchronously relative to the base table. When a strong read uses a secondary index, the read may still see a stale version of the index if the write has not yet been fully replicated to the index table. This is a known behavior: strong reads guarantee consistency only when reading from the base table using the primary key, not when using a secondary index.

Exam trap

The trap here is that candidates assume 'strong reads' guarantee consistency for all queries, but Cloud Spanner's strong consistency guarantee applies only to reads that use the primary key; secondary index reads may return stale data because the index is updated asynchronously.

How to eliminate wrong answers

Option A is wrong because the question explicitly states that strong reads are used, which means the read timestamp is automatically set to the current timestamp, not a stale one. Option C is wrong because Cloud Spanner does not have read-only replicas; all replicas can serve reads, but strong reads are always served from the leader replica, so reading from a non-leader replica would not occur with strong reads. Option D is wrong because Cloud Spanner provides strong consistency for all reads by default; eventual consistency is not a mode that can be selected, and the issue is specific to secondary index staleness, not a general consistency model.

Practice this question →
85
MCQhard

A social media platform uses Cloud SQL for PostgreSQL for its user and post data. The schema has a normalized design with separate 'users' and 'posts' tables. Queries that fetch a user's timeline (joining users and posts) are slow due to heavy read volume. The team wants to optimize the schema for this read-heavy workload without changing the application logic significantly. What schema design change is most appropriate?

A.Migrate to a NoSQL database like Firestore for better read performance.
B.Create a materialized view that joins users and posts, refreshed periodically.
C.Add GIN indexes on the posts table for faster full-text search.
D.Denormalize by embedding commonly accessed user fields (e.g., username, avatar URL) into the posts table.
AnswerD

Denormalizing by embedding commonly accessed user fields (e.g., username, avatar URL) into the posts table reduces the need for JOINs, significantly improving read performance.

Why this answer

Denormalizing by storing relevant user data (e.g., username, avatar) directly in the posts table reduces the need for JOINs, significantly improving read performance. Option A (Migrate to NoSQL like Firestore) is a major architectural change that may not be worth the effort. Option B (Create a materialized view) could help but may introduce staleness and overhead.

Option C (GIN indexes) are for full-text search, not join performance.

Practice this question →
86
MCQmedium

A Firestore application stores user profiles that must be queried by any of multiple attributes (age, city, last_login). What is the best schema design to support these queries efficiently?

A.Store attributes in an array field and query with array-contains
B.Create a composite index on the attributes in a single collection
C.Use subcollections per attribute value
D.Create separate documents for each attribute value
AnswerB

Composite indexes enable efficient multi-attribute queries in Firestore.

Why this answer

Firestore requires composite indexes to efficiently query documents across multiple fields (age, city, last_login) in a single collection. Without a composite index, Firestore would need to perform a full collection scan or merge results from separate index scans, which is inefficient and can lead to high latency or query failures. Creating a composite index on the three attributes allows Firestore to use a single index scan to satisfy queries filtering on any combination of these fields.

Exam trap

A common misconception is that array-contains or subcollections can replace composite indexes for multi-attribute filtering, but Firestore's query engine requires explicit composite indexes for any query that combines fields with inequality or equality filters.

How to eliminate wrong answers

Option A is wrong because array-contains queries only check for the presence of a single value in an array field, not for equality or range comparisons on multiple distinct attributes; it cannot support queries like 'age > 30 AND city == 'NYC''. Option C is wrong because using subcollections per attribute value would require multiple queries and client-side merging to filter on multiple attributes, leading to poor performance and complexity; Firestore subcollections are designed for hierarchical data, not multi-attribute filtering. Option D is wrong because creating separate documents for each attribute value would require multiple reads and client-side joins to reconstruct a user profile, violating Firestore's document-oriented model and causing excessive read costs and latency.

Practice this question →
87
MCQhard

An online advertising platform uses Cloud Spanner for ad impression tracking. The table 'ad_impressions' has a primary key (ad_id, timestamp). The table receives millions of writes per minute. A secondary index on (campaign_id, timestamp) was created to support queries that sum impressions per campaign. During high traffic, the team notices increased write latency and hotspotting on the index (the campaign_id has low cardinality, causing all writes to a campaign to hit the same index split). They need to redesign the schema to avoid hotspotting on the index while still supporting the campaign aggregation queries. What is the best solution?

A.Modify the secondary index to include a hash prefix (e.g., use 'hash(campaign_id)' as the first column of the index).
B.Migrate the ad_impressions table to Cloud Bigtable with row key 'campaign_id#timestamp'.
C.Change the primary key of the base table to include campaign_id as the first column.
D.Create a separate table that stores per-campaign aggregations, updated in real time.
AnswerA

A hash prefix distributes index writes evenly across splits, preventing hotspotting.

Why this answer

Adding a hash prefix to the index key (e.g., using a hash of campaign_id as the leading column) distributes index writes across multiple splits, eliminating the hotspotting on the secondary index. Option B (migrating to Bigtable) introduces operational complexity and does not leverage Spanner's existing capabilities. Option C (changing the base table's primary key to start with campaign_id) would not fix the index hotspot because the secondary index on (campaign_id, timestamp) would still concentrate writes on a single split due to low cardinality of campaign_id.

Option D (creating a separate aggregation table) adds complexity and would require additional mechanisms to keep it updated, which can be error-prone and may not solve the underlying index hotspot.

Practice this question →
88
MCQmedium

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?

A.The primary key includes two columns which reduces insert performance
B.The TotalAmount column should be INTEGER for performance
C.The table lacks a foreign key constraint
D.The OrderId is likely to be sequentially generated, causing write hotspots
AnswerD

Sequential keys lead to hotspotting; consider using a hash prefix or UUID.

Why this answer

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.

Exam trap

A common misconception tested in this exam is that composite primary keys or missing foreign keys are the main performance culprits, when in fact the critical issue is write hotspotting caused by monotonically increasing primary keys in a distributed database like Cloud Spanner.

How to eliminate wrong answers

Option A is wrong because having two columns in the primary key does not inherently reduce insert performance; Spanner can efficiently handle composite primary keys as long as they are not monotonically increasing. Option B is wrong because using INTEGER vs FLOAT64 for TotalAmount is a data type choice, not a performance issue; Spanner handles both efficiently, and the real concern is write distribution, not column type. Option C is wrong because foreign key constraints are optional in Spanner and their absence does not cause performance issues; they are used for data integrity, not write throughput.

Practice this question →
89
MCQmedium

A global e-commerce company is designing a Cloud Spanner schema for order processing. They need strong consistency across regions and high write throughput. Orders are identified by a globally unique order ID (UUID). Currently, they use the UUID as the primary key, but they observe write hotspots during peak hours. What primary key design change should they make to distribute writes more evenly?

A.Use the timestamp of order creation as the primary key.
B.Use a sequential integer primary key with auto-increment.
C.Use a composite primary key starting with a hash of the order ID, followed by the order ID.
D.Keep UUID as primary key but add a secondary index on a hash of the UUID.
AnswerC

A hash prefix ensures writes are distributed across all splits, avoiding hotspots.

Why this answer

Using a composite primary key starting with a hash of the order ID distributes writes evenly across all Cloud Spanner nodes, preventing hotspots. Cloud Spanner uses the first column of the primary key to determine row distribution; a monotonically increasing UUID as the first column causes all new writes to land on the same tablet, creating a hotspot. By hashing the UUID first, writes are spread uniformly across the key space, while the order ID ensures uniqueness.

Exam trap

The trap here is that candidates often think secondary indexes or adding a hash anywhere in the schema will fix distribution, but Cloud Spanner only distributes rows based on the first column of the primary key, so the hash must be the leading column.

How to eliminate wrong answers

Option A is wrong because using a timestamp as the primary key is monotonically increasing, which causes all new writes to be directed to the same tablet, creating severe write hotspots and defeating the purpose of distribution. Option B is wrong because a sequential integer primary key with auto-increment is also monotonically increasing, leading to the same hotspot problem as timestamps, and it introduces contention on the auto-increment mechanism. Option D is wrong because keeping the UUID as the primary key does not solve the hotspot issue; adding a secondary index on a hash of the UUID does not change the physical distribution of rows, and Cloud Spanner distributes data based on the primary key, not secondary indexes.

Practice this question →
90
Drag & Dropmedium

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

Drag steps to the numbered slots on the right, or tap a step then tap a slot.

Steps
Order
1Step 1
2Step 2
3Step 3
4Step 4

Why this order

PITR requires backups and binary logs enabled; then you create a new instance from backup at the specific time.

Practice this question →
91
MCQeasy

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?

A.Use a single table with columns for each sensor and wide rows
B.Use Cloud SQL with a normalized schema
C.Create a Sensors table and an interleaved Readings table with primary key (SensorId, Timestamp DESC)
D.Use Cloud Bigtable with row keys (SensorId#Timestamp)
AnswerC

Correct: Interleaved hierarchy with descending timestamp allows efficient latest row retrieval per sensor.

Why this answer

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.

Practice this question →
92
MCQeasy

Based on the exhibit, what is the primary key of the Readings table?

A.(SensorId, Timestamp)
B.(SensorId, SensorType)
C.(SensorId)
D.(ReadingsId)
AnswerA

The DDL explicitly defines this as the primary key.

Why this answer

The Readings table captures sensor measurements over time, so the natural primary key is the combination of SensorId and Timestamp, which uniquely identifies each reading. This is a classic example of a composite primary key in relational database design, ensuring that no two readings from the same sensor at the same time can exist.

Exam trap

The Google Professional Data Engineer exam often tests the misconception that a single column like SensorId can serve as a primary key, when in fact the combination of SensorId and Timestamp is required to guarantee uniqueness in a time-series table.

How to eliminate wrong answers

Option B is wrong because (SensorId, SensorType) is not unique — a sensor has a fixed SensorType, so multiple readings for that sensor would have the same pair, violating primary key uniqueness. Option C is wrong because (SensorId) alone cannot be a primary key — a single sensor produces many readings over time, so SensorId is not unique across rows. Option D is wrong because ReadingsId is not mentioned in the exhibit; the table likely does not include a surrogate key, and the question asks for the primary key based on the given schema, not an artificial one.

Practice this question →
93
MCQmedium

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?

A.Use a secondary index on CustomerId.
B.Split the table into multiple tables per region.
C.Add a hash of CustomerId as a prefix to the primary key.
D.Change primary key to OrderId only.
AnswerC

Hash prefix distributes writes evenly across nodes, reducing hotspots.

Why this answer

Hotspots occur due to concentrated traffic on a single key range, such as a specific customer's orders. Adding a hash of CustomerId as a prefix to the primary key (Option C) distributes writes across multiple splits, alleviating the hotspot. Option A (secondary index) improves read performance but does not affect write distribution.

Option B (splitting by region) adds complexity without directly addressing the key-ordering issue. Option D (OrderId only) loses the natural ordering and may still cause hotspots on the most recent OrderId.

Practice this question →
94
MCQmedium

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?

A.Use ALTER TABLE ADD COLUMN without a default value and then update rows in batches
B.Use ALTER TABLE ADD COLUMN with a non-null default value
C.Create a new table and use batch operations to copy data
D.Drop and recreate the table with the new column
AnswerB

Cloud Spanner applies the default immediately without scanning or rewriting rows.

Why this answer

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.

Exam trap

The trap here is that candidates assume adding a column with a default value requires a full table scan or row updates, similar to traditional databases, but Cloud Spanner handles this as a schema-only change without data movement.

How to eliminate wrong answers

Option A is wrong because adding a nullable column without a default value requires a subsequent batch update to populate the column, which would cause massive write contention and long-running transactions on a large table with high write throughput. Option C is wrong because creating a new table and copying data via batch operations involves significant resource overhead, double storage costs, and potential downtime during the switchover, making it far less efficient than a metadata-only schema change. Option D is wrong because dropping and recreating the table results in complete data loss and extended downtime, which is unacceptable for a production system with continuous writes.

Practice this question →
95
MCQhard

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?

A.Create separate tables for each snapshot_date.
B.Use clustering on customer_id and snapshot_date.
C.Use a nested and repeated structure to store all snapshots per customer in a single row.
D.Use a wildcard table with a _TABLE_SUFFIX filter.
AnswerC

Nested fields allow storing an array of snapshots per customer, reducing data scanned per query significantly.

Why this answer

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.

Exam trap

A common mistake in Google Professional Data Engineer exams is to assume that partitioning or clustering alone solves all performance issues, but for snapshot data with repeated customer records, a nested schema is the most efficient way to reduce data scanned and costs, especially when queries often scan entire partitions.

How to eliminate wrong answers

Option A is wrong because creating separate tables for each snapshot_date would require managing hundreds or thousands of tables, complicating maintenance and querying; BigQuery does not benefit from this approach as it still scans entire tables unless wildcard unions are used, which can increase costs. Option B is wrong because clustering on customer_id and snapshot_date improves performance only within a partition, but since the table is already partitioned by snapshot_date, queries scanning entire partitions would still read all rows in those partitions, and clustering does not reduce the amount of data scanned for full-partition scans. Option D is wrong because using a wildcard table with _TABLE_SUFFIX filter is essentially the same as partitioning by date (if tables are named by date) and does not reduce the data scanned when queries target entire partitions; it also adds management overhead of multiple tables.

Practice this question →
96
MCQhard

In Cloud Spanner, a table 'Orders' has a primary key (OrderId INT64) and is frequently updated. The application often queries for orders placed in the last hour. To reduce read latency, you decide to add a column to store the commit timestamp. Which approach should you use?

A.Define the column with the `allow_commit_timestamp` option and set it to 'true'
B.Create an interleaved table with the timestamp
C.Use a generated column with expression to get current_timestamp
D.Add a secondary index on a user-managed timestamp column
AnswerA

Spanner automatically assigns the commit timestamp to such columns, enabling efficient time-based queries.

Why this answer

Cloud Spanner's `allow_commit_timestamp` option, when set to 'true' on a column of type `TIMESTAMP`, automatically populates that column with the exact commit timestamp of the transaction. This enables efficient time-based queries (e.g., orders placed in the last hour) without requiring application-managed timestamps or additional writes, reducing read latency by leveraging Spanner's built-in commit-time visibility.

Exam trap

This question tests the misconception that generated columns or secondary indexes can substitute for Spanner's native commit timestamp feature, but only `allow_commit_timestamp` guarantees the exact commit time without application overhead.

How to eliminate wrong answers

Option B is wrong because creating an interleaved table does not automatically capture commit timestamps; interleaving is a schema design pattern for hierarchical relationships and locality, not for timestamp management. Option C is wrong because Cloud Spanner does not support generated columns with expressions like `CURRENT_TIMESTAMP`; generated columns are limited to deterministic expressions based on other columns, not volatile functions. Option D is wrong because a secondary index on a user-managed timestamp column would require the application to explicitly set and maintain the timestamp, introducing complexity and potential inconsistency, and does not leverage Spanner's native commit timestamp feature.

Practice this question →
97
Multi-Selectmedium

A database engineer is designing a Cloud SQL for MySQL schema for a multi-tenant SaaS application. Each tenant's data is isolated. Which TWO strategies are appropriate for tenant isolation?

Select 2 answers
A.Create a separate database for each tenant.
B.Use a single table with a tenant_id column and enforce filtering in application queries.
C.Use column-level security to hide tenant data.
D.Use a separate Cloud SQL instance per tenant.
E.Use row-level security policies to restrict access per tenant.
AnswersA, D

Separate databases provide strong isolation and are easy to manage.

Why this answer

Options A and D are correct because they provide strong tenant isolation. Option A: Creating a separate database per tenant leverages MySQL's native database boundaries, preventing cross-tenant data access at the schema level. It also simplifies per-tenant backup/restore operations.

Option D: Using a separate Cloud SQL instance per tenant offers physical isolation at the compute level, which is appropriate when tenants require complete resource isolation or have compliance needs. Options B, C, and E are incorrect: B relies on application filtering which can be error-prone and does not enforce isolation at the database level; C (column-level security) and E (row-level security) are not supported in MySQL and are features of other database engines like PostgreSQL or SQL Server.

Exam trap

Google Cloud often tests the misconception that MySQL supports advanced security features like row-level or column-level security, which are actually available in other database engines like PostgreSQL or SQL Server, leading candidates to incorrectly select options C or E.

Practice this question →
98
MCQeasy

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?

A.Create a view over the table
B.Create a materialized view with a filter on date
C.Cluster the table by date column
D.Partition the table by date column
AnswerD

Partition pruning reduces data scanned.

Why this answer

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.

Exam trap

In Google Cloud BigQuery, partitioning by date enables partition pruning that reduces data scanned, directly lowering cost. Candidates often confuse this with clustering, which only reorders data within a partition and does not independently reduce scanned bytes.

How to eliminate wrong answers

Option A is wrong because a view is just a saved SQL query; it does not reduce the amount of data scanned or improve performance, as BigQuery still processes the underlying table fully. Option B is wrong because a materialized view with a date filter pre-computes results but still requires scanning the base table for incremental refreshes, and it does not optimize the base table's storage or query pruning for ad-hoc date range queries. Option C is wrong because clustering only sorts data within a table or partition, reducing the data scanned for filter predicates but not eliminating entire storage blocks; without partitioning, BigQuery still must scan all blocks that might contain matching dates, whereas partitioning physically separates data by date.

Practice this question →
99
MCQmedium

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?

A.No index; rely on full table scan
B.Separate indexes on user_id and login_time
C.A composite index on (login_time, user_id)
D.A composite index on (user_id, login_time)
AnswerC

Allows efficient range scan on login_time and provides user_id for grouping.

Why this answer

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.

Practice this question →
100
Multi-Selectmedium

Which three of the following are valid considerations when designing secondary indexes in Cloud Spanner? (Choose three.)

Select 3 answers
A.Secondary indexes maintain strong consistency with the base table
B.Secondary indexes are automatically used for queries that filter on primary key columns
C.Secondary indexes require a unique constraint
D.Secondary indexes can be created on child tables without including the parent key
E.Secondary indexes can be created with a STORING clause to include non-key columns
AnswersA, D, E

All indexes in Spanner are strongly consistent.

Why this answer

Cloud Spanner secondary indexes are fully synchronous with the base table, meaning they are updated atomically in the same transaction as the table write. This ensures that reads using the secondary index always return strongly consistent data, without any eventual consistency window.

Exam trap

A common misconception is that secondary indexes in Cloud Spanner are automatically used for any query filter. In reality, the optimizer only uses a secondary index when the query filter columns match the index key. Filters on primary key columns will not automatically leverage secondary indexes; they use the primary index instead.

Practice this question →
← PreviousPage 2 of 2 · 100 questions total

Ready to test yourself?

Try a timed practice session using only Db Schema Design questions.

Start 20-question session