Courseiva

CCNA Design and implement database schemas Questions

75 of 100 questions · Page 1/2 · Design and implement database schemas · Answers revealed

Practice these questionsDomain overviewAll questions
1
MCQhard

A multinational corporation uses Cloud Spanner with a multi-region configuration. The schema includes a table that is updated frequently by users in two distant regions. They are experiencing high commit latencies due to distributed transactions. Which schema change would most reduce latency?

A.Reduce the number of replicas in the Spanner configuration.
B.Use a table-level leader placement configuration to keep the table's splits in a single region.
C.Convert the table into an interleaved child of a parent table.
D.Increase the number of splits by using a more granular primary key.
AnswerB

Leader placement allows directing all writes for a table to the nearest region, reducing distributed transaction overhead.

Why this answer

Table-level leader placement ensures that all writes for the table are processed in a single region, minimizing cross-region coordination and reducing commit latency. Option A is wrong: reducing replicas can hurt availability but doesn't address transaction distribution. Option C is wrong: interleaving tables does not affect region placement.

Option D is wrong: more granular primary keys increase splits, which can exacerbate distributed transactions and latency.

Practice this question →
2
Multi-Selecteasy

A startup is using Firestore in Native mode for a real-time chat application. They want to design the schema for chat rooms and messages. Which TWO design patterns are recommended? (Choose two.)

Select 2 answers
A.Use arrays in the chat room document to store message IDs.
B.Use a composite index on chat room ID and timestamp.
C.Store all messages in a single top-level collection with a field for chat room ID.
D.Use a separate top-level collection for each chat room.
E.Store messages as documents in a subcollection under each chat room document.
AnswersB, E

A composite index is required for querying messages efficiently.

Why this answer

A composite index on chat room ID and timestamp is essential for efficiently querying messages in order within a specific chat room. Firestore requires composite indexes for queries that combine equality filters on one field (chat room ID) with an order on another (timestamp). Option E is correct because storing messages as documents in a subcollection under each chat room document is the recommended pattern for Firestore, as it allows scalable, independent message collections per room without hitting the 1 MiB document size limit.

Exam trap

Firestore exams often test the misconception that arrays are suitable for storing related data, but Firestore arrays lack the indexing and scalability needed for relational-like references, leading candidates to incorrectly choose Option A.

Practice this question →
3
MCQhard

A team is migrating an on-premises PostgreSQL database to Cloud SQL for PostgreSQL. The existing schema uses a large number of foreign key constraints and triggers for data validation. The team wants to minimize migration effort and maintain data integrity. Which schema design approach is most appropriate for Cloud SQL?

A.Keep the existing foreign keys and triggers as-is in Cloud SQL for PostgreSQL
B.Migrate to Cloud Spanner and use interleaved tables to simulate foreign keys
C.Remove all foreign keys and triggers and implement validation in the application layer
D.Convert the schema to use Firestore in Datastore mode with composite indexes
AnswerA

Cloud SQL supports these features, minimizing migration effort.

Why this answer

Cloud SQL for PostgreSQL is fully compatible with the PostgreSQL engine, meaning foreign key constraints and triggers operate identically to on-premises PostgreSQL. This approach minimizes migration effort by preserving the existing schema logic and maintaining referential integrity without requiring application changes or data validation rewrites.

Exam trap

The trap here is that candidates assume managed cloud databases require schema simplification or NoSQL conversion, but Cloud SQL for PostgreSQL is a direct lift-and-shift target that preserves all relational features like foreign keys and triggers.

How to eliminate wrong answers

Option B is wrong because Cloud Spanner uses interleaved tables for hierarchical data relationships, not as a direct replacement for foreign keys; it does not support PostgreSQL triggers or the same constraint enforcement, requiring significant schema redesign and application logic changes. Option C is wrong because removing foreign keys and triggers shifts data integrity to the application layer, which increases complexity, risk of data corruption, and violates the goal of minimizing migration effort while maintaining integrity. Option D is wrong because Firestore in Datastore mode is a NoSQL document database that does not support SQL foreign keys, triggers, or relational integrity constraints, requiring a complete schema transformation and loss of existing PostgreSQL functionality.

Practice this question →
4
Multi-Selecteasy

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

Select 2 answers
A.Avoid secondary indexes to keep writes faster
B.Use monotonically increasing primary keys
C.Use commit timestamp columns to track row versions
D.Use interleaved tables for parent-child relationships
E.Store all related data in a single row to avoid joins
AnswersC, D

Commit timestamps provide automatic versioning.

Why this answer

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.

Exam trap

Google often tests the misconception that avoiding secondary indexes universally improves write performance, but in Cloud Spanner, secondary indexes are strongly consistent and designed to handle high write throughput without significant overhead, so candidates incorrectly select Option A as a best practice.

Practice this question →
5
Multi-Selectmedium

Which THREE are considerations when designing a schema for Cloud Firestore?

Select 3 answers
A.Use subcollections to organize related data
B.Avoid large arrays to prevent document size limits
C.Denormalize data to reduce the need for joins
D.Use nested maps for deeply structured data
E.Always use transactional writes to ensure consistency
AnswersA, B, C

Subcollections enable scalable data modeling.

Why this answer

Subcollections in Cloud Firestore allow you to organize related data hierarchically within a document, enabling efficient queries and scalability without hitting the 1 MiB document size limit. This structure is ideal for data like user posts or product reviews, where each subcollection can grow independently.

Exam trap

A common trap in Google exams is the misconception that nested maps are a good alternative to subcollections for deeply structured data, but they ignore Firestore's indexing and size limitations that make subcollections the correct choice for scalability.

Practice this question →
6
Matchingmedium

Match each Cloud Spanner concept to its definition.

Drag a concept onto its matching description — or click a concept then click the description.

Concepts
Matches

Automatic data distribution across nodes

Global clock service for external consistency

Parent-child table with co-located rows

Read with guaranteed latest data

Read with bounded staleness for lower latency

Why these pairings

These are key concepts for understanding Spanner's architecture and consistency.

Practice this question →
7
Multi-Selecthard

A financial services company is designing a Cloud Spanner schema for a trading system. They have two main entities: 'accounts' and 'transactions'. Each account has many transactions, and queries almost always retrieve transactions for a specific account. Which TWO schema design strategies should they employ?

Select 2 answers
A.Use a secondary index on transactions.account_id.
B.Ensure the primary key of transactions includes the account_id as the first part.
C.Define a foreign key constraint from transactions to accounts.
D.Store transactions as a JSON array of repeating fields within the account record.
E.Use an interleaved table hierarchy with accounts as parent and transactions as child.
AnswersB, E

This is required for interleaved tables: the child's primary key must start with the parent's primary key.

Why this answer

Cloud Spanner distributes rows across splits based on the primary key prefix. By making `account_id` the first part of the transactions table primary key, all transactions for a given account are co-located, enabling efficient range scans and point lookups without cross-node shuffling.

Exam trap

Google Cloud often tests the misconception that secondary indexes are the default solution for filtering, when in Cloud Spanner the primary key design and interleaving are the preferred strategies for performance and cost efficiency.

Practice this question →
8
MCQeasy

A startup is migrating from MongoDB to Firestore in Datastore mode. Their existing documents contain nested arrays of sub-objects (e.g., tags, comments). They want to design a schema that scales well and supports efficient queries. What is the recommended approach for handling these nested arrays in Firestore?

A.Use maps instead of arrays to store the data.
B.Store the arrays as stringified JSON in a single field.
C.Flatten the arrays into subcollections under each document.
D.Keep the nested arrays as they are; Firestore supports arrays.
AnswerC

Subcollections scale independently and allow efficient queries.

Why this answer

Firestore in Datastore mode does not support querying or indexing nested array elements efficiently, which can lead to performance issues as the dataset grows. Flattening nested arrays into subcollections allows each sub-object to be a separate document, enabling scalable queries and proper indexing without the limitations of array-based storage.

Exam trap

Candidates might assume that Firestore in Datastore mode supports querying nested arrays like MongoDB does, but Google Cloud Firestore does not index nested array elements. Flattening into subcollections is necessary for scalability.

How to eliminate wrong answers

Option A is wrong because using maps instead of arrays still stores nested data within the document, which does not resolve the indexing and query limitations for nested objects in Firestore. Option B is wrong because storing arrays as stringified JSON in a single field prevents any server-side querying or filtering on individual elements, forcing application-side parsing and defeating the purpose of a managed database. Option D is wrong because while Firestore supports arrays, it does not support indexing individual elements within nested arrays, making queries on those elements inefficient or impossible at scale.

Practice this question →
9
MCQmedium

A team is designing a BigQuery schema for time-series analytics on IoT sensor data. They expect high write throughput and queries that aggregate data by hour. Which partitioning and clustering strategy is most cost-effective?

A.Partition by ingestion_time and cluster by sensor_id.
B.Use integer range partitioning on sensor_id.
C.Partition by date and cluster by sensor_id with a timestamp column.
D.Partition by sensor_id and cluster by timestamp.
AnswerC

Date-based partitioning efficiently prunes scans; clustering by sensor_id further reduces data read.

Why this answer

Partitioning by date (e.g., _PARTITIONDATE) allows BigQuery to prune partitions when querying hourly aggregates, drastically reducing scanned bytes. Clustering by sensor_id with a timestamp column further organizes data within each partition, enabling efficient filtering and sorting for sensor-specific queries. This combination optimizes both write throughput (partitioning avoids small, frequent partitions) and query performance (clustering reduces scan on sensor_id filters).

Exam trap

The trap here is that candidates confuse the roles of partitioning and clustering. A common mistake is to partition on a high-cardinality column like sensor_id, which BigQuery limits to 4,000 partitions, causing partition explosion and degraded performance. Understanding this BigQuery constraint is key to choosing the right strategy.

How to eliminate wrong answers

Option A is wrong because partitioning by ingestion_time (using _PARTITIONTIME) creates a new partition for each ingestion batch, which can lead to excessive partitions under high write throughput, increasing metadata overhead and query latency. Option B is wrong because integer range partitioning on sensor_id is not suitable for time-series analytics; it does not enable time-based partition pruning for hourly aggregation queries, forcing full-table scans. Option D is wrong because partitioning by sensor_id would create a separate partition per sensor, which is impractical for high-cardinality sensor data (thousands of sensors) and violates BigQuery's limit of 4,000 partitions per table, while clustering by timestamp alone does not optimize the hourly aggregation pattern.

Practice this question →
10
MCQhard

A Cloud Spanner database has a parent table 'Customers' and a child table 'Orders' interleaved on CustomerId. The most common query retrieves the last 10 orders for a given customer. How should the primary key of Orders be defined for optimal performance?

A.(CustomerId, OrderId)
B.Add a commit timestamp column as part of the primary key
C.No change; use a secondary index on OrderDate
D.(CustomerId, OrderDate DESC)
AnswerD

Descending order stores newest first, enabling efficient limit queries.

Why this answer

Defining the primary key as (CustomerId, OrderDate DESC) leverages Cloud Spanner's interleaved table structure to physically co-locate rows for the same customer, and the descending order on OrderDate allows the most common query (retrieving the last 10 orders) to be served as a sequential scan of the most recent rows without sorting or a full table scan.

Exam trap

A common trap in Google Cloud exams is assuming that a secondary index on OrderDate is sufficient for ordered retrieval. However, without using the primary key ordering in an interleaved table, the query cannot exploit physical co-location and will require an extra index lookup and sort, which is suboptimal.

How to eliminate wrong answers

Option A is wrong because (CustomerId, OrderId) does not provide ordering by date, so retrieving the last 10 orders would require scanning all orders for that customer and sorting, which is inefficient. Option B is wrong because adding a commit timestamp column as part of the primary key does not guarantee order by order date, and commit timestamps are not user-defined; they are set by Spanner and cannot be used for application-level ordering without additional logic. Option C is wrong because using a secondary index on OrderDate would require an extra index lookup and does not benefit from the interleaved table's physical locality, leading to higher latency and cost compared to a primary key that directly supports the query.

Practice this question →
11
Multi-Selecthard

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?

Select 3 answers
A.Clustering can be used to improve query performance on frequently filtered columns.
B.Partitioning in BigQuery can be based on a DATE, TIMESTAMP, or INTEGER column.
C.BigQuery requires explicit indexes on columns used in WHERE clauses.
D.Materialized views in BigQuery are automatically refreshed based on base table changes.
E.BigQuery supports unique constraints and foreign keys for data integrity.
AnswersA, B, D

Clustering sorts data within partitions for better filter performance.

Why this answer

Options A, B, and D are correct. Clustering (A) improves query performance on frequently filtered columns by colocating data, similar to Oracle's partitioning but without manual index management. Partitioning in BigQuery (B) supports DATE, TIMESTAMP, or INTEGER columns, which is crucial for migrating Oracle's partitioned tables.

Materialized views in BigQuery (D) are automatically refreshed when base tables change, aligning with Oracle's materialized view refresh mechanisms but with less administrative overhead. Options C and E are incorrect because BigQuery does not require explicit indexes and does not enforce unique or foreign key constraints, reflecting its schema-on-read approach.

Exam trap

Candidates often assume BigQuery requires traditional database features like indexes (C) or constraint enforcement (E), but BigQuery relies on partitioning, clustering, and automatic materialized view refresh for performance and manageability.

Practice this question →
12
MCQmedium

A company is designing a Cloud Firestore schema for a social media application. Users can follow other users, and the application needs to display a feed of posts from followed users ordered by timestamp. Which schema design is most cost-effective and performant for querying the feed?

A.Store all posts in a top-level collection and query for posts where user ID is in the list of followed users, ordered by timestamp.
B.Store a feed subcollection under each user document containing references to posts from followed users.
C.Store all user posts in an array within a single document and use array-contains queries.
D.Store a 'follows' collection with documents containing follower and followed user IDs; then query posts for each followed user.
AnswerB

This allows direct query on the feed subcollection ordered by timestamp.

Why this answer

It uses a feed subcollection under each user document to store pre-computed references to posts from followed users. This design avoids expensive collection-group queries or multiple individual queries per followed user, ensuring that fetching the feed is a single, indexed read operation ordered by timestamp, which is both cost-effective and performant at scale.

Exam trap

The trap here is that candidates often choose Option A, thinking a single top-level query with an 'in' filter is simpler, but they overlook Firestore's 10-value limit on 'in' queries and the resulting need for multiple queries, which destroys both performance and cost predictability at scale.

How to eliminate wrong answers

Option A is wrong because querying a top-level posts collection with a list of followed user IDs requires an 'in' query, which is limited to 10 values per query and does not scale to hundreds or thousands of followed users, leading to multiple queries and high read costs. Option C is wrong because storing all user posts in an array within a single document violates the 1 MiB document size limit and cannot support ordered queries or pagination, making it impractical for any real-world social media feed. Option D is wrong because querying posts for each followed user individually results in N+1 read operations per feed request, causing high latency and cost proportional to the number of followed users, with no built-in ordering across results.

Practice this question →
13
MCQhard

A financial services company uses Cloud Spanner for a ledger application. The ledger table has a primary key of 'transaction_id' which is a monotonically increasing integer. During peak hours, they observe high write latencies due to hot spots on the last tablet. They need to redesign the schema to distribute writes evenly while still allowing efficient point lookups by transaction ID. What is the best approach?

A.Reverse the timestamp and use it as the primary key.
B.Use a UUID as the primary key to ensure randomness.
C.Use a composite primary key with a timestamp and a random number.
D.Use a composite primary key with a hash prefix of the transaction ID as the first component, followed by the transaction ID.
AnswerD

The hash prefix evenly distributes writes, and the transaction ID allows efficient point lookups.

Why this answer

Using a hash prefix of the transaction ID as the first component of a composite primary key distributes writes across multiple tablets in Cloud Spanner, avoiding hot spots on a single tablet. The transaction ID as the second component still enables efficient point lookups by transaction ID, as Spanner can use the hash prefix to locate the tablet and then scan within it for the exact row.

Exam trap

A common mistake in Google Cloud Spanner design is thinking that simply adding randomness (like a UUID or random number) solves hot spots, but this fails to consider that the primary key must still support efficient point lookups by the original identifier, which a composite key with a hash prefix achieves.

How to eliminate wrong answers

Option A is wrong because reversing a timestamp does not guarantee even distribution; timestamps often have low cardinality in the leading digits, leading to similar reversed values and continued hot spots. Option B is wrong because a UUID as the primary key, while random, is not a composite key and would require a full table scan for point lookups by transaction ID, as the UUID replaces the transaction ID. Option C is wrong because a composite primary key with a timestamp and a random number does not directly support efficient point lookups by transaction ID; the transaction ID is not part of the key, so lookups would require scanning or a secondary index.

Practice this question →
14
MCQhard

A game company uses Cloud Bigtable to store player session data. Access patterns include looking up a player's most recent sessions and scanning sessions by time range. Which row key design is most appropriate?

A.Use only player ID as row key with column qualifiers for timestamps.
B.Use a row key of player ID followed by reversed timestamp.
C.Prefix with timestamp and append player ID.
D.Use a hash of player ID as row key and store timestamps in cell versions.
AnswerB

Player ID distributes writes across tablets; reversed timestamp makes recent data appear at the start of the range for efficient scans.

Why this answer

It leverages Cloud Bigtable's sorted row key structure to group all sessions for a given player ID together, while the reversed timestamp ensures that the most recent session appears first within that group. This design enables efficient point lookups for a player's latest sessions and supports range scans over time ranges by using the timestamp portion of the key.

Exam trap

The trap here is that candidates often think hashing or using timestamps as a prefix will distribute load evenly, but they overlook that the primary access pattern requires grouping by player ID and ordering by time, which the reversed timestamp suffix elegantly satisfies.

How to eliminate wrong answers

Option A is wrong because using only player ID as the row key with column qualifiers for timestamps would create a single row per player that grows unboundedly, leading to hotspotting and poor performance as the number of sessions increases. Option C is wrong because prefixing with timestamp would scatter a single player's sessions across many tablets, making it impossible to efficiently retrieve all sessions for a player without a full table scan. Option D is wrong because hashing the player ID destroys the natural ordering of sessions, preventing efficient time-range scans, and relying on cell versions for timestamps is not designed for querying by time range and can lead to data loss due to garbage collection.

Practice this question →
15
MCQeasy

A financial services company runs a MySQL database on Compute Engine. They want to migrate to Cloud SQL for MySQL to reduce operational overhead. The current schema includes a table 'transactions' with a composite primary key on (transaction_id, account_id) and a secondary index on account_id for account lookups. The database also uses foreign key constraints to ensure referential integrity between 'transactions' and 'accounts'. During migration testing, they observe that INSERT operations on 'transactions' are slower than expected. What schema change should they implement to improve INSERT performance in Cloud SQL?

A.Remove the foreign key constraints and enforce referential integrity in the application logic instead.
B.Remove the secondary index on account_id because it adds write overhead.
C.Change the primary key to (account_id, transaction_id) to avoid secondary index overhead.
D.Convert the table to a temporal table with system-versioning to avoid constraint checking.
AnswerA

Foreign key constraints require a lookup on the parent table for every INSERT, causing latency. Removing them reduces write overhead, though integrity must be ensured by the application.

Why this answer

Foreign key constraints in MySQL (including Cloud SQL) require an internal check on every INSERT to verify that the referenced parent key exists. This adds a latency penalty proportional to the size of the parent table. Removing the constraint and moving referential integrity to the application eliminates this per-row check, directly improving INSERT throughput.

Exam trap

Google Cloud often tests the misconception that secondary indexes are the primary cause of write slowdowns, when in reality foreign key constraint checks are far more expensive per row than index maintenance.

How to eliminate wrong answers

Option B is wrong because removing the secondary index on account_id would degrade SELECT performance for account lookups, and the index's write overhead is negligible compared to the cost of foreign key checks. Option C is wrong because changing the primary key order does not eliminate foreign key validation overhead; it only affects index clustering and does not address the root cause of slow INSERTs. Option D is wrong because temporal tables with system-versioning add additional metadata and version-row writes on every INSERT, which would further degrade performance, not improve it.

Practice this question →
16
MCQhard

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?

A.Create a new table with the column and copy data over
B.Execute ALTER TABLE Products ADD COLUMN discount FLOAT64
C.Create a secondary index that includes the new column
D.Define a generated column based on an existing column
AnswerB

Spanner allows DDL changes while the table remains fully available.

Why this answer

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.

Exam trap

The trap here is that candidates may assume schema changes require downtime or data migration in a distributed database, but Cloud Spanner's online schema change capability allows ALTER TABLE to be executed without blocking reads or writes.

How to eliminate wrong answers

Option A is wrong because creating a new table and copying data over introduces significant downtime and complexity, and is unnecessary since Cloud Spanner handles schema changes online. Option C is wrong because a secondary index does not add a column to the table; it only creates an index on existing columns, which does not meet the requirement of adding a new column. Option D is wrong because a generated column derives its value from other columns and cannot be used to introduce a new independent column like 'discount'.

Practice this question →
17
MCQhard

Your company runs an e-commerce platform on Google Cloud. The platform uses Cloud SQL for MySQL to store product inventory. The inventory table has the following schema: CREATE TABLE inventory (product_id INT PRIMARY KEY, quantity INT, last_updated TIMESTAMP) ENGINE=InnoDB. The application performs frequent updates on quantity for a subset of popular products. Recently, you have noticed increased deadlock errors during peak hours. The application uses REPEATABLE READ isolation level. You suspect that the schema design is contributing to locking contention. After analyzing the workload, you find that the updates often involve incrementing or decrementing quantity by small amounts and are mostly on the same set of popular products. What would be the best course of action to reduce deadlocks without compromising data integrity?

A.Rewrite the update query to use atomic operations (e.g., UPDATE inventory SET quantity = quantity - ? WHERE product_id = ?) without pre-fetching the current value.
B.Change the engine to MyISAM to avoid row-level locking.
C.Partition the inventory table by product_id range to spread the load.
D.Reduce the isolation level to READ COMMITTED to reduce locking.
AnswerA

Atomic updates avoid the need for SELECT ... FOR UPDATE and significantly reduce locking and deadlock chances.

Why this answer

The application's pattern of reading the current quantity before updating (e.g., SELECT quantity FROM inventory WHERE product_id = ?, then UPDATE inventory SET quantity = ? WHERE product_id = ?) causes gap locks and deadlocks under REPEATABLE READ. By using an atomic UPDATE (UPDATE inventory SET quantity = quantity - ? WHERE product_id = ?), the database performs the update without a prior read, reducing lock contention. This maintains data integrity because the subtraction is atomic and accurate.

Option B is wrong because MyISAM does not support transactions or row-level locking, which compromises data integrity. Option C is wrong because partitioning does not reduce locking contention on the same rows; it only improves data management. Option D is wrong because reducing isolation to READ COMMITTED may reduce some locking but introduces non-repeatable reads and does not address the fundamental read-before-write pattern that causes deadlocks.

Practice this question →
18
MCQhard

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?

A.The Bigtable cluster does not have enough nodes to handle the scan.
B.The query is scanning multiple column families.
C.The row key design does not allow efficient scanning for all devices because device_id is the prefix.
D.The table has too many tablets, causing high overhead.
AnswerC

Prefix scans on device_id are efficient per device, but scanning all devices requires a full table scan.

Why this answer

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.

Exam trap

Google Cloud often tests the misconception that adding more nodes or tablets fixes scan performance, but the real issue is row key design that prevents Bigtable from using its sorted storage to limit the scan range.

How to eliminate wrong answers

Option A is wrong because insufficient nodes would cause general performance degradation across all queries, not specifically slow down the all-devices query while keeping the per-device query fast. Option B is wrong because scanning multiple column families adds overhead only if the query retrieves data from many families, but the problem statement does not mention column families, and the slowness is tied to the row key design, not column family access. Option D is wrong because too many tablets can cause high overhead for any scan, but the per-device query would also be affected; the asymmetry between fast per-device and slow all-devices queries points directly to row key ordering, not tablet count.

Practice this question →
19
MCQmedium

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?

A.Create materialized views for common reports.
B.Change all joins to use subqueries.
C.Increase the number of vCPUs on the primary instance.
D.Use read replicas to offload reporting queries.
AnswerD

Read replicas serve read-only traffic without impacting the primary.

Why this answer

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.

Exam trap

The trap here is that candidates confuse materialized views (which still run on the primary) with read replicas (which offload the query execution entirely), leading them to choose A instead of D.

How to eliminate wrong answers

Option A is wrong because materialized views are stored on the same primary instance and do not offload query processing; they still consume the same CPU and I/O resources, and refreshing them can add further load. Option B is wrong because changing joins to subqueries does not reduce resource consumption—subqueries often perform worse than joins in PostgreSQL due to lack of optimization for correlated subqueries. Option C is wrong because increasing vCPUs on the primary instance only scales vertically, which may help but does not isolate reporting workloads; the transactional queries still compete for the same resources, and scaling is limited by instance tier constraints.

Practice this question →
20
Multi-Selecthard

Which THREE considerations are important when designing a schema for Cloud Firestore to ensure scalability?

Select 3 answers
A.Design collections to avoid high read/write rates on a single document.
B.Create composite indexes tailored to the application's query patterns.
C.Nest subcollections up to 10 levels deep to model complex hierarchies.
D.Use collection group indexes for all queries to avoid manual index creation.
E.Limit document size to avoid exceeding the 1 MiB limit.
AnswersA, B, E

Hot documents cause contention; distribute writes across documents.

Why this answer

Cloud Firestore scales by distributing data across multiple documents. A single document with high read/write rates creates a hotspot, leading to contention and degraded performance. Designing collections to spread load evenly avoids this bottleneck and ensures linear scalability.

Exam trap

A common misconception is that deeper nesting (up to 10 levels) or automatic indexes simplify schema design, but Cloud Firestore's scalability relies on shallow, flat structures and explicit composite indexes tailored to query patterns.

Practice this question →
21
MCQhard

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?

A.Apply for a quota increase for concurrent queries.
B.Increase the number of slots allocated to the project.
C.Use the '--maximum_billing_tier' flag to increase the billing tier.
D.Simplify the query by reducing the number of joins or using a temporary table.
AnswerD

Reducing query complexity lowers resource demands and can stay within tier limits.

Why this answer

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.

Exam trap

A common misconception in BigQuery is that increasing slot allocation or concurrency limits will resolve resource exhaustion errors caused by complex queries. In reality, the root cause is often query complexity, and simplifying the query (e.g., by reducing joins or using temporary tables) directly addresses memory and slot limits.

How to eliminate wrong answers

Option A is wrong because applying for a quota increase for concurrent queries addresses the number of queries running simultaneously, not the resource consumption of a single complex query. Option B is wrong because increasing the number of slots allocated to the project provides more parallel processing capacity but does not fix the underlying issue of a query that exceeds per-stage memory or shuffle limits; it may only delay the failure. Option C is wrong because the '--maximum_billing_tier' flag is a legacy BigQuery feature that caps query cost, not a way to increase resources; it cannot resolve memory or complexity errors.

Practice this question →
22
MCQeasy

A team is designing a schema for a time-series database in Bigtable to store IoT sensor readings. Each sensor sends a reading every minute. The team needs to create a row key that supports efficient queries for a specific sensor's readings over a time range. Which row key design is most appropriate?

A.timestamp#sensor_id
B.hash(sensor_id)#timestamp
C.sensor_id#reverse_timestamp
D.random_UUID
AnswerC

Groups all readings for a sensor together in reverse chronological order.

Why this answer

Bigtable stores rows sorted lexicographically by row key. By placing the sensor_id first, all readings for a given sensor are co-located in contiguous rows. Using reverse_timestamp (e.g., 9999-12-31 minus actual timestamp) ensures that the most recent readings appear first within that sensor's row range, which optimizes scans for the latest data and allows efficient range queries over a time window.

Exam trap

Google Cloud often tests the misconception that putting the timestamp first is always best for time-range queries, but in Bigtable, the row key's prefix determines data locality, so the sensor_id must come first to avoid scattering reads across the entire table.

How to eliminate wrong answers

Option A is wrong because timestamp first scatters readings for the same sensor across the entire table, making queries for a specific sensor's time range require a full table scan or multiple lookups. Option B is wrong because hashing the sensor_id destroys the natural sort order, so even though the sensor_id is first, the hash distributes rows randomly, preventing efficient range scans over time. Option D is wrong because a random UUID provides no ordering or grouping, forcing full table scans for any sensor-specific time-range query.

Practice this question →
23
MCQeasy

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?

A.A composite index on (conversationId, timestamp, __name__) descending
B.A single-field index on timestamp
C.An index on conversationId only
D.A composite index on (senderId, timestamp)
AnswerA

This index covers the query with filtering and ordering, enabling efficient retrieval.

Why this answer

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.

Exam trap

A common misconception is that a single-field index on the ordering field (timestamp) is sufficient for filtered and ordered queries, but Firestore requires a composite index that includes all equality filter fields before the order field to avoid a full collection scan. Additionally, including __name__ in the index ensures proper handling of document name sorting for consistent pagination with descending order.

How to eliminate wrong answers

Option B is wrong because a single-field index on timestamp alone cannot filter by conversationId, so Firestore would have to scan all messages across all conversations to find the most recent 50 for a specific conversation, which is inefficient and expensive. Option C is wrong because an index on conversationId only allows filtering by conversation but does not support ordering by timestamp, so Firestore would need to sort the results in memory, which fails for large datasets and cannot guarantee efficient retrieval of the most recent 50. Option D is wrong because a composite index on (senderId, timestamp) filters by sender, not by conversation, so it cannot efficiently retrieve messages for a given conversation; it would require a full collection scan or an additional filter step.

Practice this question →
24
MCQeasy

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?

A.Store orders and line items in a single table with repeated fields for line items.
B.Create an Orders table and a LineItems table interleaved in Orders with ORDER_ID as the parent key.
C.Create separate Orders and LineItems tables with a foreign key relationship and index on ORDER_ID.
D.Denormalize product information into the LineItems table and store orders separately.
AnswerB

Interleaving colocates line items with their order for fast retrieval.

Why this answer

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.

Exam trap

Google Cloud often tests the misconception that a foreign key with an index is equivalent to interleaving for performance, but in Cloud Spanner, only interleaved tables guarantee physical colocation and single-split access for parent-child queries, whereas indexed foreign keys still require distributed lookups.

How to eliminate wrong answers

Option A is wrong because storing repeated fields (e.g., ARRAY<STRUCT>) for line items within a single row violates Cloud Spanner's 10 MB row size limit and prevents efficient indexing or atomic updates of individual line items, degrading throughput for large orders. Option C is wrong because separate tables with a foreign key and index on ORDER_ID require a two-step lookup (index scan then table access) and may involve distributed reads if the index and data are on different splits, increasing latency compared to interleaving. Option D is wrong because denormalizing product information into LineItems does not address the core read pattern (retrieving all line items for an order) and introduces data redundancy and update anomalies without improving colocation; it still requires a separate table or repeated fields, neither of which matches the interleaved design's performance benefit.

Practice this question →
25
MCQmedium

A retail company uses Cloud Spanner to store product inventory data. The table structure is: CREATE TABLE Inventory ( ProductId INT64 NOT NULL, WarehouseId INT64 NOT NULL, StockLevel INT64 NOT NULL, LastUpdated TIMESTAMP NOT NULL OPTIONS (allow_commit_timestamp=true) ) PRIMARY KEY (ProductId, WarehouseId); The application frequently runs the query: SELECT ProductId, SUM(StockLevel) AS TotalStock FROM Inventory WHERE WarehouseId = 123 GROUP BY ProductId. The query is slow and scans many rows. The index used is: CREATE INDEX InventoryByWarehouse ON Inventory (WarehouseId); What is the most effective schema change to improve query performance?

A.Change the primary key to (WarehouseId, ProductId) so rows are interleaved by warehouse.
B.Create a materialized view that pre-aggregates stock by warehouse.
C.Modify the index to INCLUDE StockLevel: CREATE INDEX InventoryByWarehouse ON Inventory (WarehouseId) STORING (StockLevel).
D.Add a STORED GENERATED column for total stock per warehouse.
AnswerC

The STORING clause adds StockLevel to the index, making it a covering index for the query, so Cloud Spanner can return results from the index alone without scanning the base table.

Why this answer

The query needs to read StockLevel for every row matching WarehouseId, but the existing index only covers WarehouseId, forcing a back-join to the base table. By using STORING (StockLevel), the index becomes a covering index that includes the StockLevel column, eliminating the need for the back-join and reducing the number of rows scanned to only those matching the warehouse filter.

Exam trap

The trap here is that candidates often think changing the primary key order (Option A) will physically colocate data and speed up the query, but in Cloud Spanner, primary key order does not eliminate the need to scan all rows for a given WarehouseId, and the query still requires aggregation across ProductId groups, so a covering index is the correct optimization.

How to eliminate wrong answers

Option A is wrong because changing the primary key to (WarehouseId, ProductId) would reorder the table's physical storage, but Cloud Spanner does not support interleaving in the same way as Cloud SQL; more importantly, the query still needs to aggregate StockLevel across all rows for each ProductId, and a primary key change does not avoid scanning all rows for the given WarehouseId. Option B is wrong because creating a materialized view that pre-aggregates stock by warehouse would not help this query, which groups by ProductId, not by warehouse; the materialized view would need to be grouped by (WarehouseId, ProductId) to be useful, and even then, maintaining a materialized view adds write overhead and complexity. Option D is wrong because a STORED GENERATED column for total stock per warehouse is not possible in Cloud Spanner—generated columns cannot reference rows from other rows or perform aggregation, and they are computed per row, not across rows.

Practice this question →
26
MCQmedium

A healthcare analytics company uses Cloud Bigtable to store time-series data from medical devices. The table has a row key of 'device_id#timestamp' where timestamp is stored in reverse order (max - timestamp) so that recent data is at the top. Queries that fetch data for a specific device over a date range are very fast. However, analysts also need to run queries that aggregate data across all devices for a specific hour (e.g., count of readings between 2023-01-01 10:00 and 11:00). These queries are extremely slow because they require scanning all rows. The team must redesign the schema to support both access patterns without duplicating data unnecessarily. What is the best approach?

A.Use BigQuery to query Bigtable via an external table and run the aggregation there.
B.Increase the number of Bigtable nodes to improve scan throughput.
C.Add a secondary index on the timestamp column.
D.Create a second table with row key 'timestamp#device_id' (with timestamp in natural order) to support time-range queries.
AnswerD

This provides efficient access for the aggregation query by allowing a range scan over the timestamp.

Why this answer

Creating a second table with row key 'timestamp#device_id' (with timestamp in natural order) allows efficient range scans for a given time period across all devices, because Bigtable rows are sorted lexicographically by row key. This enables fast aggregation queries without scanning all rows. Option A (using BigQuery external table) would still require scanning the entire Bigtable for each query, and adds latency.

Option B (increasing nodes) improves throughput but does not change the need to scan all rows, so it does not address the root cause. Option C (secondary index) is not supported in Bigtable.

Practice this question →
27
MCQhard

You have a Cloud Spanner table 'Orders' with columns: OrderId, CustomerId, OrderDate, Status. You need to support a query that finds all orders for a customer in the last 30 days, sorted by OrderDate descending, with strong consistency. Using only indexes, what is the best approach?

A.Create a secondary index on (OrderDate) only
B.Create a secondary index on (CustomerId, OrderDate)
C.Use a manual table scan with filter
D.Create a secondary index on (CustomerId, OrderDate DESC) with INCLUDE (OrderId, Status)
AnswerD

Index covers the query completely, providing efficient ordered retrieval.

Why this answer

It creates a covering index with the exact sort order needed (DESC on OrderDate) and includes all required columns (OrderId, Status) to avoid back-to-table lookups. This ensures strong consistency in Cloud Spanner by using a single index scan without needing to read the base table, while the composite key on (CustomerId, OrderDate) efficiently filters by customer and date range.

Exam trap

Google Cloud Spanner requires a composite index with the correct sort order (DESC) and covering columns for optimal query performance. A simple index on OrderDate alone would not efficiently filter by CustomerId, and a table scan is not recommended when an index can satisfy the query.

How to eliminate wrong answers

Option A is wrong because an index on (OrderDate) only cannot efficiently filter by CustomerId, requiring a full scan of the index or table to find orders for a specific customer, which violates the query requirement. Option B is wrong because while (CustomerId, OrderDate) allows filtering by customer and date, it defaults to ascending order on OrderDate, so the query would need an extra sort step or a reverse scan, which is less efficient than a pre-sorted descending index. Option C is wrong because a manual table scan with filter would scan the entire table, incurring high latency and cost, and does not leverage indexes, which contradicts the 'using only indexes' constraint in the question.

Practice this question →
28
MCQmedium

Refer to the exhibit. A developer creates these tables and notices that queries joining Users and Orders on UserId are slow. What is the most likely cause?

A.The primary key of Orders should include UserId as a prefix for co-location.
B.The foreign key constraint is missing, causing full table scans.
C.Tables are not interleaved, so parent and child rows may be in different splits.
D.The foreign key reference should be on the parent table.
AnswerC

Interleaving is required to guarantee co-location. Without it, joins may be distributed.

Why this answer

In Google Cloud Spanner, interleaving tables physically co-locates parent and child rows in the same tablet split, reducing cross-node lookups. Without interleaving, rows from Users and Orders may reside on different servers, causing distributed queries that are slower due to network round trips. Option C correctly identifies this as the most likely cause of slow joins.

Exam trap

Google often tests the misconception that foreign keys or primary key ordering alone solve performance issues, when in Cloud Spanner the key optimization is interleaving for co-location.

How to eliminate wrong answers

Option A is wrong because the primary key of Orders should not include UserId as a prefix for co-location; CockroachDB uses interleaving, not composite primary key ordering, to achieve co-location. Option B is wrong because foreign key constraints do not prevent full table scans; they enforce referential integrity but do not affect query performance or index usage. Option D is wrong because foreign key references are correctly placed on the child table (Orders) to reference the parent (Users); placing them on the parent would be syntactically incorrect and meaningless.

Practice this question →
29
Multi-Selecteasy

Which TWO data types are supported in Cloud Spanner schemas?

Select 2 answers
A.ARRAY
B.GEOMETRY
C.TIMESTAMP
D.TEXT
E.TINYINT
AnswersA, C

ARRAY is supported for storing repeated values of a specific type.

Why this answer

ARRAY is a supported data type in Cloud Spanner, allowing you to store ordered lists of elements of the same primitive type (e.g., ARRAY<STRING>, ARRAY<INT64>). This is essential for modeling one-to-many relationships without requiring separate tables, and it integrates seamlessly with Cloud Spanner's query engine for array operations.

Exam trap

Candidates often mistakenly choose GEOMETRY or TEXT because they are common in other databases, but Cloud Spanner uses STRING for text and does not support spatial types. Also, TINYINT is not a Cloud Spanner type; use INT64 instead.

Practice this question →
30
MCQhard

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?

A.The GROUP BY country requires sorting all rows
B.The table is not partitioned correctly
C.The date range is too wide
D.The query does not filter on the clustering column, causing full scan of selected partitions
AnswerD

Clustering on country helps only if the WHERE clause filters on country; otherwise, all rows in partitions are scanned.

Why this answer

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.

Exam trap

Google Cloud often tests the misconception that clustering alone reduces bytes scanned, but the trap is that clustering only helps when the query includes a filter on the clustering column; otherwise, it provides no scanning benefit.

How to eliminate wrong answers

Option A is wrong because GROUP BY country does not inherently require sorting all rows; BigQuery can use hash aggregation and clustering metadata to avoid a full sort, and the high bytes processed is due to scanning data, not sorting. Option B is wrong because the table is partitioned correctly by date, as shown by the partition pruning in the query output; incorrect partitioning would cause a different symptom, such as scanning all partitions. Option C is wrong because the date range being too wide would increase bytes processed, but the question states the table is partitioned by date and the query likely filters on date; the primary issue is the lack of a filter on the clustering column, not the date range width.

Practice this question →
31
MCQhard

A team is migrating an on-premises PostgreSQL database to Cloud SQL. The current schema uses a composite primary key on columns (customer_id, order_date) in the orders table. The migration team wants to reduce the cost of secondary indexes. Which schema design change should they consider?

A.Partition the table by customer_id to reduce the number of secondary indexes needed.
B.Create a secondary index on the composite key to keep the same query performance.
C.Replace the composite primary key with a surrogate UUID primary key and add unique constraints on the original columns.
D.Use the CLUSTER command to physically reorder the table based on the composite key.
AnswerA

Partitioning by customer_id can eliminate the need for a secondary index on that column by enabling partition pruning, thereby reducing storage costs.

Why this answer

Partitioning the table by customer_id can reduce the need for secondary indexes on that column. When the table is partitioned, queries that filter by customer_id can use partition pruning instead of an index scan, allowing you to drop an index on customer_id and thus reduce secondary index storage costs. Creating a secondary index on the composite key (B) would add an index and increase cost.

Replacing the composite primary key with a surrogate UUID (C) would require a unique constraint index on the original columns, resulting in two indexes (the PK on UUID and the unique index on the composite columns) instead of one, which increases index costs. Using the CLUSTER command (D) physically reorders table rows but does not reduce index sizes.

Exam trap

It is a common misconception that only narrowing the primary key can reduce secondary index costs in PostgreSQL. In PostgreSQL, secondary indexes do not include primary key columns, so partitioning is a valid method to reduce the need for certain indexes by enabling partition pruning.

How to eliminate wrong answers

Option A is wrong because partitioning by customer_id does not reduce the number or size of secondary indexes; it only splits the table into smaller physical segments, and each partition still needs its own indexes. Option B is wrong because creating a secondary index on the composite key duplicates the primary key index, increasing storage and write overhead without reducing cost. Option D is wrong because the CLUSTER command physically reorders rows based on an index, which can improve locality but does not reduce secondary index size or cost; it is a one-time maintenance operation, not a schema design change.

Practice this question →
32
Multi-Selecthard

A company uses Firestore to power a live sports score app. Scores are updated frequently, and many clients listen to real-time updates on specific games. Which two design decisions will minimize the number of reads and reduce costs? (Choose two.)

Select 2 answers
A.Use a collection group query to listen to all games at once
B.Store an aggregate score summary document per game and listen to it
C.Use a separate document per game and listeners filter by game ID
D.Use a single document for all games with nested fields
E.Use a subcollection of periods (quarters) to spread writes
AnswersB, C

Reduces write operations and read frequency; clients get updates from a single summary document.

Why this answer

Storing an aggregate score summary document per game and listening to it reduces reads by consolidating frequently updated fields into a single document, minimizing the number of document reads per update. Option C is correct because using a separate document per game with listeners filtered by game ID ensures each client only listens to the specific game they care about, avoiding unnecessary reads from irrelevant documents.

Exam trap

Avoid the misconception that spreading writes across many documents (e.g., subcollections) reduces costs. In Firestore, reads are charged per document read. Consolidating frequently updated scores into a single summary document per game and having clients listen only to specific game IDs minimizes document reads and reduces costs.

Practice this question →
33
MCQmedium

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?

A.Convert the table to an interleaved table
B.Add a secondary index on the existing key
C.Modify the primary key to include a hash of the original key as a leading column
D.Increase the number of nodes in the instance
AnswerC

Hash prefix distributes writes uniformly across splits.

Why this answer

Monotonically increasing primary keys in Cloud Spanner cause writes to be concentrated on a single split (hotspotting), leading to high write latency. By modifying the primary key to include a hash of the original key as a leading column, writes are distributed uniformly across all nodes, eliminating the hotspot. Option A (interleaved tables) is a table organization pattern that does not address hotspotting.

Option B (secondary index on existing key) does not change the write pattern; writes still go to the same primary key range. Option D (increasing nodes) can improve overall throughput but does not fix the hotspotting; writes remain concentrated on one split, so latency may not improve significantly.

Practice this question →
34
MCQhard

A global gaming company uses Cloud Spanner for player profiles and game state. The schema includes a table 'PlayerStats' with a primary key (PlayerId, GameId, Timestamp). The table stores millions of rows per player. The application frequently runs a query to fetch the most recent stats for a given player across all games, using ORDER BY Timestamp DESC LIMIT 10. This query is slow, taking several seconds. The team adds a secondary index on (PlayerId, Timestamp) but still sees high CPU usage and latency. They need to redesign the schema to optimize this query without changing the application logic significantly. What should they do?

A.Migrate the PlayerStats table to Cloud Bigtable for better time-series performance.
B.Change the primary key to (PlayerId, Timestamp, GameId) and drop the secondary index.
C.Create a stored procedure that aggregates data per player and caches results.
D.Add a materialized view that pre-computes the latest stats per player.
AnswerB

This allows efficient range scans for a player’s stats ordered by time.

Why this answer

The correct answer. By reordering the primary key to (PlayerId, Timestamp, GameId), Spanner can efficiently perform a range scan for a given PlayerId with results sorted by Timestamp because the primary key order determines the storage order. This eliminates the need for a secondary index and reduces CPU usage and latency.

Option A is incorrect because migrating to Bigtable is a different database technology and not a schema redesign. Option C is incorrect because stored procedures are not a schema change and may not integrate well with the existing application logic. Option D is incorrect because Spanner does not natively support materialized views.

Practice this question →
35
MCQeasy

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?

A.Use RECORD type columns for each nested level
B.Normalize into separate tables and join
C.Use ARRAY<STRUCT<...>> columns for nested repeated data
D.Store as JSON strings and parse at query time
AnswerC

Arrays of structs are the native way to represent nested repeated data in BigQuery.

Why this answer

Using ARRAY<STRUCT<...>> columns allows storing nested repeated data natively in BigQuery, enabling efficient querying without joins. Option A (RECORD type columns) is a legacy approach not optimized for repeated data; arrays of structs are preferred. Option B (normalizing into separate tables) would require costly joins.

Option D (storing as JSON strings) loses schema enforcement and query performance.

Practice this question →
36
Drag & Dropmedium

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

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

Import needs properly formatted files; use gcloud command, then monitor and verify.

Practice this question →
37
MCQeasy

Your company runs a global e-commerce platform on Google Cloud Spanner. The database schema includes an 'Orders' table with primary key (OrderId, CustomerId) and an 'OrderItems' table with primary key (OrderId, CustomerId, ItemId), interleaved in parent Orders on delete cascade. During peak shopping hours, you notice that queries retrieving all items for a specific order are performing full table scans on the OrderItems table, leading to increased latency and higher CPU utilization. The queries use the OrderId as the filter condition. The database administrators have already checked that the query plans show table scans instead of using the interleaved index. You are tasked with resolving this performance issue. Which of the following actions should you take?

A.Remove CustomerId from the Orders primary key (making it just OrderId) and update OrderItems to have primary key (OrderId, ItemId), maintaining interleaving.
B.Change the primary key of Orders to (OrderId, CustomerId) and update OrderItems accordingly.
C.Create a secondary index on OrderItems(OrderId).
D.Increase the number of Spanner nodes to improve throughput.
AnswerA

This allows efficient lookup using only OrderId and leverages interleaving.

Why this answer

The interleaved index in Cloud Spanner requires that the parent table's primary key columns be a prefix of the child table's primary key. With the original schema, queries filtering only on OrderId cannot use the interleaved index because CustomerId is missing from the filter, forcing a full table scan. By removing CustomerId from the primary key of Orders and OrderItems, OrderId becomes the leading column, allowing the interleaved index to be used for efficient point lookups.

Exam trap

Google Cloud often tests the misconception that secondary indexes are the default fix for query performance issues, when in fact the schema design—specifically the primary key structure for interleaved tables—is the root cause and must be corrected first.

How to eliminate wrong answers

Option B is wrong because it keeps CustomerId in the primary key, which does not fix the issue—queries filtering only on OrderId still cannot use the interleaved index. Option C is wrong because creating a secondary index on OrderItems(OrderId) would add storage and write overhead, and while it could help, it is not the optimal solution; the correct fix is to adjust the primary key to leverage the interleaved index directly. Option D is wrong because increasing Spanner nodes improves throughput but does not address the root cause of full table scans caused by an inefficient schema design.

Practice this question →
38
Multi-Selecthard

A company is migrating a large Oracle database to Cloud Spanner. They need to define the schema for relational tables with foreign keys. Which THREE considerations are important when designing the Spanner schema? (Choose three.)

Select 3 answers
A.Use NULL values in primary key columns to allow optional fields.
B.Use INTERLEAVE tables to model parent-child relationships.
C.Avoid using composite primary keys; use single-column keys instead.
D.Define secondary indexes for querying on non-key columns.
E.Foreign keys are automatically enforced in Cloud Spanner.
AnswersB, D, E

Interleaving allows co-locating parent and child rows, reducing read latency.

Why this answer

Options B, D, and E are correct. Interleaved tables (B) model parent-child relationships and optimize joins and data locality. Secondary indexes (D) are essential for efficient queries on non-key columns.

Cloud Spanner enforces foreign key constraints (E) to maintain referential integrity. Option A is incorrect because primary key columns cannot be NULL. Option C is incorrect because composite primary keys are commonly used in Spanner.

Practice this question →
39
MCQmedium

A team is designing a relational schema for a new application on Cloud SQL. The schema includes a table 'Orders' and a table 'Customers'. Each order belongs to one customer. The team anticipates high write throughput and needs to enforce referential integrity. Which schema design is most appropriate?

A.Use Cloud Spanner interleaved tables with Orders as a child of Customers
B.Implement referential integrity checks in the application code and omit database constraints
C.Store order data as a JSON array in a column of the Customers table
D.Use a foreign key constraint from Orders.customer_id to Customers.customer_id
AnswerD

Enforces integrity efficiently within the database.

Why this answer

Using a foreign key constraint from Orders.customer_id to Customers.customer_id enforces referential integrity at the database level, which is essential for maintaining data consistency in a relational schema. Cloud SQL (e.g., MySQL or PostgreSQL) natively supports foreign key constraints, ensuring that every order references an existing customer without relying on application logic. This approach is efficient for high write throughput as the database handles the check atomically, avoiding race conditions.

Exam trap

Google Cloud often tests the misconception that application-level checks are sufficient for high-throughput systems, but the trap here is that database-level foreign keys are the only way to guarantee referential integrity under concurrent writes, as application code cannot prevent race conditions or orphaned records.

How to eliminate wrong answers

Option A is wrong because Cloud Spanner interleaved tables are designed for hierarchical data and strong consistency in a globally distributed environment, not for standard relational schemas on Cloud SQL; they also introduce complexity and cost that are unnecessary for a simple parent-child relationship. Option B is wrong because implementing referential integrity checks in application code is error-prone and cannot guarantee consistency under high write throughput, as concurrent writes can bypass application logic, leading to orphaned records. Option C is wrong because storing order data as a JSON array in a column of the Customers table violates normalization principles, making it difficult to query individual orders, enforce constraints, and scale write throughput efficiently.

Practice this question →
40
MCQmedium

A Cloud Firestore database stores documents for a mobile app. The app frequently queries for documents where a specific Boolean field is true. The field is not part of the collection group index. What should the developer do to improve query performance?

A.Add a synthetic field that combines the Boolean with a timestamp for range queries.
B.Create a composite index that includes the Boolean field and the query ordering field.
C.Denormalize the Boolean field into separate subcollections.
D.Rely on the automatic single-field index already created.
AnswerB

A composite index tailored to the query pattern improves performance and avoids full collection scans.

Why this answer

Cloud Firestore requires a composite index to efficiently query on a Boolean field combined with an ordering field. Without this index, the query would perform a full collection scan, leading to poor performance. Creating a composite index that includes the Boolean field and the query ordering field allows Firestore to use the index to directly locate matching documents, avoiding expensive sequential scans.

Exam trap

Candidates often assume that automatic single-field indexes are sufficient for all queries, but in Firestore, queries with both a filter and an order-by clause require a composite index, even if the filter is on a simple Boolean field.

How to eliminate wrong answers

Option A is wrong because adding a synthetic field that combines the Boolean with a timestamp is unnecessary and does not address the missing composite index; it would only help if the query involved range filtering on the timestamp, which is not stated. Option C is wrong because denormalizing the Boolean field into separate subcollections would increase complexity and data duplication without improving query performance, as Firestore still needs to query across subcollections unless collection group indexes are used. Option D is wrong because automatic single-field indexes are created by default for each field, but they do not support queries that filter on one field and order by another; a composite index is required for such queries.

Practice this question →
41
Multi-Selecteasy

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

Select 3 answers
A.Use wildcard tables with a suffix filter.
B.Store data in multiple tables per day.
C.Use partitioning on a date column for time-based queries.
D.Use materialized views for pre-aggregated results.
E.Cluster on columns frequently used in filters.
AnswersC, D, E

Partitioning prunes partitions not needed by the query, reducing cost.

Why this answer

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.

Exam trap

The Google Cloud exam often tests the misconception that wildcard tables or multiple tables per day are efficient for time-series data, but the correct approach is to use a single partitioned table to leverage BigQuery's native partition pruning and reduce management overhead.

Practice this question →
42
MCQmedium

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?

A.Deploy read replicas to offload reads
B.Use a connection pooler like PgBouncer
C.Create materialized views for read queries
D.Partition the table by a key that spreads write load
AnswerD

Partitioning reduces lock contention by distributing writes.

Why this answer

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.

Exam trap

The trap here is that candidates confuse read scaling solutions (replicas, materialized views) or connection management with write concurrency fixes, failing to recognize that only partitioning or sharding directly reduces lock contention on a heavily written table.

How to eliminate wrong answers

Option A is wrong because read replicas offload SELECT queries but do not reduce write lock contention on the primary table; writes still serialize on the source. Option B is wrong because a connection pooler like PgBouncer manages client connections to reduce overhead but does not change the locking behavior of concurrent DML statements on the same table. Option C is wrong because materialized views are read-only snapshots that do not affect write locking; they only improve read performance for complex aggregations.

Practice this question →
43
MCQhard

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?

A.Use a wildcard table pattern to query across date partitions
B.Re-cluster the table with product as the first clustering column
C.Partition by product
D.Keep the current clustering on country
AnswerB

Clustering on product improves aggregation performance by grouping data physically.

Why this answer

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.

Exam trap

Google often tests the distinction between partitioning and clustering, and the trap here is that candidates mistakenly choose partitioning by product (Option C) without realizing BigQuery's partition limit and the unsuitability of high-cardinality columns for partitioning.

How to eliminate wrong answers

Option A is wrong because using a wildcard table pattern does not improve query performance; it is a method for querying multiple tables, not an optimization for pruning within a single partitioned table. Option C is wrong because partitioning by product is impractical for high-cardinality columns (millions of distinct values) — BigQuery limits partitions to 10,000 per table, and excessive partitions degrade performance and increase metadata overhead. Option D is wrong because keeping the current clustering on country does not optimize queries that filter or group by product; clustering on a column not used in the query provides no pruning benefit.

Practice this question →
44
Multi-Selecthard

Which TWO techniques can help avoid hot spotting in a Cloud Spanner table?

Select 2 answers
A.Add a hash of the primary key as the first part of the key
B.Use a monotonically increasing integer as the key
C.Use interleaved tables to distribute writes
D.Create a secondary index on a high-cardinality column
E.Use a random prefix or UUID as the first key column
AnswersA, E

Hash prefix evenly distributes writes.

Why this answer

Adding a hash of the primary key as the first part of the key distributes writes evenly across Cloud Spanner's split boundaries. This prevents hot spotting, which occurs when monotonically increasing keys cause all new writes to land on the same tablet server. By hashing the key, writes are spread across multiple nodes, avoiding contention.

Exam trap

A common mistake is believing that secondary indexes or interleaved tables can prevent write hot spotting in Spanner. In reality, only key design techniques like hashing or using UUIDs as the first key column distribute writes across splits and avoid hot spots.

Practice this question →
45
MCQhard

A company is using Cloud Spanner to manage financial transactions. The current schema has a single table 'Transactions' with a composite primary key (account_id, transaction_timestamp). The company frequently queries the latest transaction for each account. This query pattern is causing full table scans. Which schema design change would most improve query performance?

A.Add a secondary index on (account_id, transaction_timestamp DESC)
B.Change the primary key to (transaction_timestamp, account_id) and use interleaving
C.Create a separate 'LatestTransaction' table keyed by account_id, and update it whenever a new transaction occurs
D.Add a 'is_latest' boolean column to the Transactions table and index it
AnswerC

Enables direct point reads for the latest transaction.

Why this answer

It eliminates the need to scan the entire Transactions table to find the latest transaction per account. By maintaining a separate LatestTransaction table keyed by account_id, each account's latest transaction can be retrieved with a single point read. This is a classic denormalization pattern in Cloud Spanner that avoids the overhead of scanning or sorting large datasets for 'latest per group' queries.

Exam trap

Google Cloud often tests the misconception that a secondary index with DESC ordering can efficiently retrieve the latest row per group, but in Cloud Spanner, secondary indexes do not support 'top-N per group' without scanning all index entries for each group.

How to eliminate wrong answers

Option A is wrong because a secondary index on (account_id, transaction_timestamp DESC) would still require a full index scan to find the latest transaction per account, as Cloud Spanner secondary indexes do not support 'latest per group' without scanning all rows for each account. Option B is wrong because changing the primary key to (transaction_timestamp, account_id) would scatter rows for the same account across splits, making per-account queries inefficient and requiring a full table scan to gather all rows for a single account. Option D is wrong because adding an 'is_latest' boolean column and indexing it would require updating all previous rows for an account on every insert to set is_latest=false, which is both expensive and prone to race conditions in a distributed database like Cloud Spanner.

Practice this question →
46
MCQmedium

A company is designing a Cloud Spanner database for a global user base. They need to support strong consistency and low-latency reads across multiple regions. Which schema design practice is most important?

A.Denormalize data into wide tables to reduce the number of joins.
B.Use interleaved tables to co-locate related rows that are queried together.
C.Use a single table with composite primary key to avoid joins.
D.Create secondary indexes on every column to optimize read queries.
AnswerB

Interleaving ensures parent and child rows are stored on the same split, reducing latency for joins.

Why this answer

Interleaved tables in Cloud Spanner physically co-locate parent and child rows on the same split, enabling local joins with strong consistency and low latency across regions. This design minimizes cross-node communication, which is critical for global workloads that require both strong consistency and fast reads.

Exam trap

A common mistake is thinking that denormalization or secondary indexes are the best way to optimize reads in Cloud Spanner, but the key to low-latency global reads is physical data locality via interleaved tables, not schema flattening or excessive indexing.

How to eliminate wrong answers

Option A is wrong because denormalizing into wide tables increases storage costs and write overhead, and does not guarantee low-latency reads across regions since wide rows can still be split across nodes. Option C is wrong because a single table with a composite primary key does not avoid joins when querying related data; it forces all data into one table, leading to redundancy and potential hotspots. Option D is wrong because creating secondary indexes on every column increases write latency and storage costs, and secondary indexes in Spanner are not co-located with the base table, so reads may require cross-node lookups.

Practice this question →
47
MCQmedium

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?

A.The analyst will get an error because of conflicting roles.
B.The analyst cannot create tables because the group only has dataViewer.
C.The analyst can create tables because they have dataOwner role on their user account.
D.The analyst can create tables if they also have jobUser role.
AnswerC

The dataOwner role includes all dataset permissions, including table creation.

Why this answer

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.

Exam trap

Google Cloud IAM often tests the misconception that group membership overrides direct user roles or that conflicting roles cause errors, when in reality IAM permissions are additive and the highest privilege always applies.

How to eliminate wrong answers

Option A is wrong because IAM permissions are additive, not conflicting; having multiple roles does not cause errors—the effective permissions are the union of all granted roles. Option B is wrong because the analyst's direct `dataOwner` role on their user account supersedes the group's `dataViewer` role, allowing table creation. Option D is wrong because the `jobUser` role is not required for table creation; the `dataOwner` role already includes the necessary `bigquery.tables.create` permission, and `jobUser` only allows running query jobs, not creating tables.

Practice this question →
48
MCQeasy

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?

A.Continue using inheritance as Cloud SQL supports it fully
B.Use PostgreSQL foreign data wrappers to emulate inheritance
C.Use materialized views to combine data
D.Redesign the schema using separate tables with joins
AnswerD

Standard approach; can use views to simulate inheritance for read operations.

Why this answer

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.

Exam trap

Google often tests the misconception that Cloud SQL for PostgreSQL is a fully compatible drop-in replacement for on-premises PostgreSQL, but table inheritance is a notable exception that requires schema redesign.

How to eliminate wrong answers

Option A is wrong because Cloud SQL for PostgreSQL does not fully support table inheritance; it is a known limitation documented by Google Cloud. Option B is wrong because foreign data wrappers (FDW) are used to access remote tables, not to emulate inheritance; they introduce network latency and complexity without solving the schema design issue. Option C is wrong because materialized views are read-only snapshots that do not support DML operations (INSERT/UPDATE/DELETE) on the underlying data, making them unsuitable for transactional workloads.

Practice this question →
49
MCQmedium

A company is migrating an on-premises PostgreSQL database to Cloud SQL for PostgreSQL. The database uses several custom PL/pgSQL functions that perform complex calculations. The migration must minimize application changes and support high availability. Which strategy should the database engineer use for the schema migration?

A.Convert the functions to stored procedures in Cloud Spanner and migrate data separately.
B.Export the functions as SQL scripts and convert them to pgSQL syntax for Cloud SQL.
C.Export the functions as SQL scripts and rewrite them in JavaScript using Cloud Functions.
D.Use pg_dump to export the schema including functions and restore directly to Cloud SQL.
AnswerD

pg_dump preserves PL/pgSQL functions; restore works in Cloud SQL.

Why this answer

Pg_dump can export the entire PostgreSQL schema, including custom PL/pgSQL functions, in a format that Cloud SQL for PostgreSQL natively understands. Restoring directly with pg_restore or psql preserves the functions without requiring syntax conversion, minimizing application changes. Cloud SQL for PostgreSQL supports high availability through regional persistent disks and automatic failover replicas, meeting the HA requirement without altering the schema.

Exam trap

Google Cloud often tests the misconception that PL/pgSQL functions need to be converted or rewritten for Cloud SQL, when in fact Cloud SQL for PostgreSQL is a fully managed PostgreSQL service that supports the same procedural language natively.

How to eliminate wrong answers

Option A is wrong because Cloud Spanner does not support PL/pgSQL functions or stored procedures with the same syntax; migrating to Spanner would require rewriting all functions and changing application queries, violating the 'minimize application changes' requirement. Option B is wrong because PL/pgSQL is already the native procedural language for PostgreSQL; exporting as SQL scripts and 'converting to pgSQL syntax' is unnecessary and implies a false need for syntax conversion, as Cloud SQL for PostgreSQL uses the same PostgreSQL engine. Option C is wrong because rewriting PL/pgSQL functions in JavaScript using Cloud Functions would require significant application refactoring to call external HTTP-triggered functions instead of inline database functions, breaking the 'minimize application changes' constraint.

Practice this question →
50
MCQeasy

A mobile app backend uses Firestore for user profiles. The schema has a single collection 'users' where each document contains: user_id (used as document ID), name, email, and friends (an array of user IDs). The friends array can grow large (thousands of IDs). When a user adds a friend, the application updates the array, causing the document to grow and leading to write contention and size limit warnings. The team needs to redesign the schema to scale better. What is the best approach?

A.Move the friends list to a subcollection under each user document.
B.Migrate user profiles and friendships to Cloud SQL for relational capabilities.
C.Limit the maximum size of the friends array to 1000 at the application level.
D.Create a new 'friendships' collection with documents containing user_id_1 and user_id_2 fields.
AnswerD

A separate collection for relationships scales well and avoids large documents.

Why this answer

It normalizes the friendship relationship into a separate 'friendships' collection, where each document represents a single bidirectional link between two users. This avoids unbounded document growth and write contention on user documents, as adding a friend only requires a small write to a new friendship document rather than updating a potentially large array. Firestore's 1 MiB document size limit and 1 write per second per document limit are no longer risk factors.

Exam trap

The trap here is that candidates often assume subcollections (Option A) are the universal solution for nested data growth, but they fail to recognize that subcollections still tie writes to a parent document's write limit and do not solve the array-size problem; the correct approach is to normalize the relationship into a separate top-level collection.

How to eliminate wrong answers

Option A is wrong because moving the friends list to a subcollection still requires updating a parent document (or a subcollection document that can grow) and does not eliminate the fundamental issue of array growth and write contention; subcollections are not inherently better for large arrays and still suffer from the same per-document write limits. Option B is wrong because migrating to Cloud SQL is an overengineered solution that introduces relational complexity and operational overhead, while Firestore is fully capable of handling this relationship with a normalized collection design; the question asks for a schema redesign within Firestore, not a database migration. Option C is wrong because arbitrarily limiting the array size to 1000 at the application level is a brittle workaround that does not solve the underlying scalability problem and may break user functionality; it also fails to address write contention on the document.

Practice this question →
51
MCQhard

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?

A.Maintain the same schema and rewrite joins as materialized views in PostgreSQL to optimize queries.
B.Use the same composite primary keys and foreign key constraints in Cloud SQL for PostgreSQL, leveraging its full support for these features.
C.Migrate to Cloud Spanner instead, using interleaved tables to replace join-heavy operations.
D.Remove composite primary keys and replace them with surrogate keys; use look-up tables for foreign key relationships.
AnswerB

Cloud SQL for PostgreSQL fully supports composite primary keys and foreign keys, minimizing application changes.

Why this answer

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.

Exam trap

For the Google Cloud Professional Cloud Database Engineer exam, candidates may assume that migrating to Cloud SQL for PostgreSQL requires schema redesign, but PostgreSQL’s full SQL compliance allows direct lift-and-shift of composite keys and foreign keys, minimizing application code changes.

How to eliminate wrong answers

Option A is wrong because materialized views are not a direct replacement for joins; they store precomputed results and require manual refresh, which adds complexity and does not eliminate the need for application code changes to query the views instead of the original tables. Option C is wrong because migrating to Cloud Spanner would require significant schema redesign (e.g., denormalization into interleaved tables) and application code changes, contradicting the goal of minimizing changes. Option D is wrong because removing composite primary keys and replacing them with surrogate keys would break existing application logic that relies on composite keys for joins and lookups, requiring extensive code modifications.

Practice this question →
52
Multi-Selecteasy

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

Select 2 answers
A.Use column-level security to restrict access
B.Use denormalization to reduce the number of joins
C.Use the type RECORD for structured data
D.Use repeated fields to avoid joins when querying parent-child data
E.Use a single table for all data to simplify queries
AnswersB, D

Denormalization improves query performance by reducing joins.

Why this answer

Best practices in BigQuery schema design include denormalization (option B) to reduce joins and improve query performance, and using repeated fields (option D) to model parent-child relationships without expensive JOIN operations. Options A and C are incorrect: column-level security (option A) is a data governance feature, not a schema design best practice; using RECORD type (option C) is a way to model nested data but is not a standalone best practice—repeated fields are more appropriate for avoiding joins. Option E is incorrect because using a single table for all data leads to poor performance and maintenance issues; BigQuery supports multiple tables and logical data models.

Practice this question →
53
MCQmedium

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?

A.Use a separate table per sensor type
B.Store the sensor data in a JSON column
C.Use a schema with a STRUCT containing all possible fields as optional
D.Use a wide table with many nullable columns
AnswerB

JSON provides schema flexibility and cost-effective storage for varying fields.

Why this answer

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.

Exam trap

Google Cloud often tests the misconception that a STRUCT with optional fields is equivalent to a JSON column, but the trap is that a STRUCT still requires a fixed schema definition, whereas JSON allows fully dynamic fields without schema changes.

How to eliminate wrong answers

Option A is wrong because using a separate table per sensor type increases storage costs (due to table metadata overhead) and requires schema maintenance (creating new tables for each new sensor type), which contradicts the goal of minimizing maintenance. Option C is wrong because a STRUCT with all possible fields as optional still requires you to know and define every potential field in advance, leading to schema maintenance when new sensor types introduce new fields; it also incurs storage cost for NULL values in unused fields. Option D is wrong because a wide table with many nullable columns wastes storage on NULL values (BigQuery charges for NULL storage in fixed-length types) and requires schema updates to add columns for new sensor types, failing the 'avoid schema maintenance' requirement.

Practice this question →
54
Multi-Selectmedium

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

Select 2 answers
A.Define secondary indexes on price and category columns to support range queries without considering the primary key design.
B.Use a timestamp as the first part of the primary key to enable time-based partitioning and efficient range scans.
C.Define interleaved tables for all related entities, even if they are not always accessed together, to reduce joins.
D.Use a primary key that starts with the category column to colocate product data for efficient queries by category.
E.Create an interleaved table for product variants under the product table, since variants are always queried with the parent product.
AnswersD, E

Leading with category allows Spanner to distribute rows by category, improving locality for queries filtering by category.

Why this answer

Colocating product data by category in the primary key enables efficient range scans on category and price, as Cloud Spanner stores rows in sorted order by primary key. This design minimizes cross-node fan-out for queries filtering by category, directly supporting high read throughput at global scale with strong consistency.

Exam trap

Google Cloud often tests the misconception that secondary indexes are a universal solution for query performance, ignoring that primary key design and interleaved tables are critical for colocation and avoiding cross-node fan-out in globally distributed databases like Cloud Spanner.

Practice this question →
55
MCQeasy

A developer is designing a schema for Firestore to store user profiles. Each user has a unique ID and multiple addresses. Which data modeling approach is recommended for Firestore?

A.Store addresses as a string array in the user document.
B.Use a relational join between users and addresses collection.
C.Create a separate collection for addresses with a reference to user ID.
D.Store addresses as a nested map within the user document.
AnswerD

Nested maps are ideal for one-to-few relationships and minimize reads.

Why this answer

Firestore recommends denormalizing one-to-few relationships. Storing addresses as a nested map within the user document (Option D) preserves structure and is efficient for small, fixed sets. Option A (string array) loses key-value structure.

Option B is not possible because Firestore does not support relational joins. Option C (separate collection) is more appropriate for large or frequently changing lists, not for a few addresses per user.

Practice this question →
56
MCQeasy

A team is migrating an on-premises MySQL database to Cloud SQL. The current schema usesMyISAM tables. What is the recommended approach?

A.Keep the schema as is; Cloud SQL supports MyISAM.
B.Convert MyISAM tables to InnoDB before migration.
C.Replicate the on-premises MySQL to Cloud SQL using Database Migration Service.
D.Export the database using mysqldump and import directly into Cloud SQL.
AnswerB

InnoDB is the default and recommended engine; conversion ensures compatibility and transactional support.

Why this answer

Cloud SQL for MySQL does not support MyISAM tables because MyISAM lacks transaction support, row-level locking, and crash recovery, which are essential for a managed database service. Converting MyISAM tables to InnoDB before migration ensures compatibility, data integrity, and performance. The recommended approach is to alter the table engine to InnoDB prior to export or use a migration tool that handles the conversion.

Exam trap

The trap here is that candidates assume Cloud SQL supports all MySQL storage engines, but it explicitly restricts to InnoDB and NDB, making MyISAM incompatible without prior conversion.

How to eliminate wrong answers

Option A is wrong because Cloud SQL does not support MyISAM; it only supports InnoDB and NDB Cluster (for high availability). Option C is wrong because Database Migration Service (DMS) can replicate data but does not automatically convert MyISAM tables to InnoDB; the schema must be compatible beforehand. Option D is wrong because a direct mysqldump import of MyISAM tables into Cloud SQL will fail or produce errors since Cloud SQL rejects unsupported storage engines.

Practice this question →
57
MCQeasy

A Cloud SQL for PostgreSQL instance is used for an OLTP application. The database schema has many foreign key constraints. Which action improves write performance?

A.Create indexes on foreign key columns.
B.Drop all foreign key constraints.
C.Add more triggers to enforce integrity.
D.Increase the instance storage size.
AnswerA

Indexes on foreign key columns speed up lookups during INSERT/UPDATE/DELETE operations.

Why this answer

Creating indexes on foreign key columns prevents full table scans when checking referential integrity, reducing lock contention and improving write performance. Option B is wrong because dropping foreign key constraints sacrifices data integrity, but it could actually improve write performance; however, this is not a best practice and violates data consistency. Option C is wrong because adding triggers adds overhead to write operations, slowing performance.

Option D is wrong because increasing storage size does not address the performance bottleneck caused by missing indexes on foreign key columns.

Practice this question →
58
MCQhard

Refer to the exhibit. What is the most likely performance issue with this schema?

A.No performance issue; the schema is optimal
B.Hotspotting on UserId due to frequent queries
C.Hotspotting on TransactionId due to monotonically increasing values
D.Too many secondary indexes causing write amplification
AnswerC

Monotonically increasing keys cause all writes to target a single split.

Why this answer

The schema uses TransactionId as the partition key with monotonically increasing values (e.g., timestamps or auto-incrementing integers). In a distributed database like Cloud Spanner or Bigtable, this causes all writes to land on a single partition, creating a hotspot that throttles throughput and increases latency. The correct answer is C because this hotspotting is the most likely performance issue.

Exam trap

The trap is the assumption that any unique identifier works as a partition key. Google Cloud exams test that monotonically increasing values (e.g., timestamps) as partition keys create hotspotting in distributed databases like Spanner or Bigtable, limiting write scalability.

How to eliminate wrong answers

Option A is wrong because the schema has a clear hotspotting problem, so it is not optimal. Option B is wrong because UserId is not the partition key; even if queried frequently, hotspotting on UserId would require it to be the partition key with skewed access patterns, which is not indicated. Option D is wrong because the exhibit does not show multiple secondary indexes; write amplification from secondary indexes is a concern only when many indexes exist, and the primary issue here is partition-level hotspotting from the monotonically increasing partition key.

Practice this question →
59
MCQeasy

A company is migrating an on-premises MySQL database to Cloud SQL for MySQL. The current schema uses InnoDB with foreign keys. What is a key consideration for maintaining referential integrity in Cloud SQL?

A.Enable the foreign_key_checks flag during migration.
B.Convert foreign keys to application-level checks.
C.Use Cloud SQL's built-in foreign key enforcement which is identical to on-premises.
D.Foreign keys are not supported in Cloud SQL MySQL.
AnswerC

Cloud SQL for MySQL behaves exactly like standard MySQL for foreign keys.

Why this answer

Cloud SQL for MySQL uses the same MySQL database engine as on-premises, including full support for InnoDB foreign key constraints. When you migrate the schema, Cloud SQL enforces referential integrity identically to a self-managed MySQL instance, so no changes to foreign key definitions are required.

Exam trap

The trap here is that candidates assume managed cloud databases have limited SQL features, leading them to incorrectly choose Option D, when in fact Cloud SQL for MySQL provides identical foreign key support to on-premises MySQL.

How to eliminate wrong answers

Option A is wrong because enabling the foreign_key_checks flag during migration would actually disable foreign key enforcement, risking data integrity violations; the flag should be enabled after migration to ensure referential integrity. Option B is wrong because converting foreign keys to application-level checks is unnecessary and introduces complexity and potential inconsistency, as Cloud SQL fully supports native foreign key enforcement. Option D is wrong because Cloud SQL for MySQL does support foreign keys; this is a common misconception that stems from confusion with other managed database services like Cloud SQL for PostgreSQL or Spanner.

Practice this question →
60
MCQmedium

A Cloud Bigtable instance stores time-series data with a row key format: [metric_id]#[timestamp]. The team notices read throughput is low when scanning a metric over a time range. What is the likely cause?

A.All rows for a given metric are stored in a single tablet causing a hotspot.
B.Too many column families in the schema.
C.The number of nodes is insufficient.
D.Replication factor is set too low.
AnswerA

With metric_id prefix, all rows for that metric are on one tablet, limiting read throughput.

Why this answer

The row key format [metric_id]#[timestamp] causes all rows for the same metric_id to share the same lexicographic prefix. Cloud Bigtable stores rows in sorted order by row key, so all rows for a given metric are co-located in a single tablet. When scanning a time range for that metric, all read requests hit the same tablet, creating a hotspot that limits throughput to the capacity of a single node.

Exam trap

Google often tests the misconception that adding more nodes or increasing replication will solve a hotspot issue, but the root cause is a poorly designed row key that prevents even data distribution across tablets.

How to eliminate wrong answers

Option B is wrong because column families do not affect read throughput for range scans; they affect storage and write performance, and Cloud Bigtable supports up to a few hundred column families without performance degradation. Option C is wrong because insufficient nodes would cause overall throughput issues across all operations, not specifically low read throughput for a single metric's time-range scan; the hotspot is a data distribution problem, not a capacity problem. Option D is wrong because replication factor is not a configurable parameter in Cloud Bigtable; it uses a single cluster with automatic replication within the cluster, and replication does not affect read throughput for range scans.

Practice this question →
61
MCQhard

A financial services company uses Cloud Spanner for transaction processing. They need to run analytical queries that scan large portions of the database without impacting OLTP performance. What schema design technique should they use?

A.Export data periodically to BigQuery and run queries there.
B.Create multiple secondary indexes on frequently scanned columns.
C.Design the primary key so that analytical queries scan a small number of tablets by using interleaved tables.
D.Use a read replica instance to offload analytical queries.
AnswerC

Interleaving related rows keeps them co-located, allowing efficient scans on parent-child relationships without distributed reads.

Why this answer

Interleaved tables in Cloud Spanner physically co-locate parent and child rows on the same tablet (split). This ensures that analytical queries scanning a large portion of the database can be served by a small number of tablets, minimizing cross-tablet reads and reducing contention with OLTP traffic. By designing the primary key to leverage interleaving, you keep analytical scans localized and avoid the performance penalty of scattering reads across many tablets.

Exam trap

A common misconception is that read replicas or secondary indexes are the primary way to isolate analytical workloads, but in Cloud Spanner the correct schema-level isolation technique is interleaved tables to minimize tablet scans and avoid cross-split contention.

How to eliminate wrong answers

Option A is wrong because exporting data to BigQuery is an operational workaround, not a schema design technique; it introduces latency, data staleness, and additional ETL overhead, whereas the question asks for a schema design technique. Option B is wrong because creating multiple secondary indexes on frequently scanned columns does not reduce the number of tablets scanned; secondary indexes are stored separately and can actually increase write amplification and contention during OLTP writes. Option D is wrong because Cloud Spanner does not support read replica instances in the traditional sense; Spanner uses a single global instance with automatic replication, and offloading queries to a read replica is not a schema design technique and would not prevent impact on OLTP performance due to shared underlying storage.

Practice this question →
62
MCQeasy

A startup uses Cloud SQL (MySQL) for a blogging platform. The schema has a table 'posts' with columns: post_id (auto-increment PK), title, content, author_id, created_at. The application frequently runs a query to display the latest 10 posts from a specific author: SELECT * FROM posts WHERE author_id = ? ORDER BY created_at DESC LIMIT 10. This query is slow when an author has thousands of posts. The team wants to optimize this query without changing the application code. What schema change will be most effective?

A.Add a composite index on (author_id, created_at DESC).
B.Partition the table by author_id using range partitioning.
C.Increase the query cache size in Cloud SQL.
D.Migrate the posts table to Cloud Spanner and use interleaved indexes.
AnswerA

This index directly supports the query, allowing an index range scan and limit.

Why this answer

A composite index on (author_id, created_at DESC) allows the database to efficiently locate posts for a given author sorted by creation date without scanning all rows. Option B (partitioning by author_id) does not directly help because ordering across partitions would still require sorting or scanning all partitions. Option C (increasing query cache) is not a schema change and may not help if the query is not cached or the data changes frequently.

Option D (migrating to Spanner) is a drastic change and not necessary; a well-designed index in Cloud SQL can solve the issue.

Exam trap

Watch out for the option letter mix-up. Partitioning might seem useful but does not optimally support ORDER BY and LIMIT across partitions; a composite index is more effective.

Practice this question →
63
MCQhard

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?

A.Use Spanner's built-in atomicity by executing the updates in a single read-write transaction.
B.Use Cloud Pub/Sub to eventually synchronize the tables.
C.Use a commit timestamp-based approach to synchronize writes.
D.Use a client-side distributed transaction across the two tables.
AnswerA

Spanner supports multi-table transactions with global strong consistency.

Why this answer

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.

Exam trap

Google Cloud often tests the misconception that interleaved tables are required for strong consistency in Spanner, but the trap here is that Spanner's distributed transaction support works across any tables, interleaved or not, as long as they are within the same database.

How to eliminate wrong answers

Option B is wrong because Cloud Pub/Sub is an asynchronous messaging service that provides at-least-once delivery and eventual consistency, not strong consistency; it cannot guarantee that both tables are updated atomically. Option C is wrong because a commit timestamp-based approach, while useful for ordering, does not by itself provide atomicity across multiple tables; without a transaction, writes to separate tables can be interleaved or partially applied. Option D is wrong because client-side distributed transactions are not supported by Cloud Spanner; Spanner manages all transaction coordination internally using TrueTime and Paxos, and attempting to implement distributed transactions at the client level would violate Spanner's consistency guarantees and could lead to anomalies.

Practice this question →
64
MCQeasy

A startup is using Cloud Spanner for a global user base. They need to design a schema that minimizes interleaved table joins for common access patterns. Which schema design principle should they prioritize?

A.Normalize all tables to reduce data redundancy.
B.Store data in separate databases per region.
C.Use secondary indexes on all foreign key columns.
D.Use composite primary keys to colocate related data.
AnswerD

Correct. Composite primary keys enable interleaving, colocating rows and minimizing joins.

Why this answer

Cloud Spanner uses interleaved tables to colocate parent and child rows physically on the same split, based on a shared prefix of the primary key. By designing composite primary keys that include the parent key as the leading column, related data is stored together, eliminating the need for distributed joins across nodes. This minimizes latency for common access patterns in a globally distributed database.

Exam trap

Candidates often assume that normalization or secondary indexes are always optimal for performance, but in Cloud Spanner's distributed architecture, physical colocation via interleaved composite keys is the critical design principle to avoid expensive cross-node joins.

How to eliminate wrong answers

Option A is wrong because normalizing tables increases the number of joins, which in Spanner can require cross-node communication and degrade performance; Spanner is optimized for denormalized, interleaved schemas. Option B is wrong because storing data in separate databases per region defeats Spanner's purpose of providing a single, globally consistent database with automatic replication and strong consistency. Option C is wrong because secondary indexes on foreign keys do not colocate data; they only speed up lookups but still require separate index scans and potential cross-split reads, whereas interleaving physically co-locates rows.

Practice this question →
65
Multi-Selectmedium

Which TWO schema design practices help reduce write contention in Cloud Spanner?

Select 2 answers
A.Use a hash prefix in the primary key to distribute writes across splits.
B.Use a timestamp prefix in the primary key to sort by time.
C.Use interleaved tables to keep related rows together.
D.Design the schema so that hot rows are split into multiple rows with different keys.
E.Decrease the number of splits by using a less granular primary key.
AnswersA, D

Hashing prevents sequential writes from hitting the same split.

Why this answer

Using a hash prefix in the primary key distributes write operations uniformly across multiple splits (tablets). Cloud Spanner splits data based on key ranges; without a hash prefix, sequential writes (e.g., monotonically increasing keys) concentrate on a single split, causing hot spots and write contention. A hash prefix ensures that each new row lands on a different split, balancing the write load.

Exam trap

Candidates often assume that using a timestamp prefix (Option B) is beneficial for time-series queries, but in Cloud Spanner, monotonically increasing keys cause all writes to hit a single split, creating a hot spot. Instead, hash prefixes (Option A) distribute writes evenly. Another misconception is that interleaved tables (Option C) reduce write contention; they actually improve read performance but do not address write hot spots.

Also, decreasing splits (Option E) reduces parallelism, worsening contention.

Practice this question →
66
MCQeasy

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?

A.SELECT * FROM Orders WHERE CustomerId = 1234 AND OrderDate = '2023-01-01';
B.SELECT * FROM Customers JOIN Orders ON Customers.CustomerId = Orders.CustomerId WHERE Customers.CustomerId = 1234;
C.SELECT * FROM Orders WHERE CustomerId = 1234;
D.SELECT * FROM Orders WHERE OrderId = 5678;
AnswerC

Interleaving colocates all orders for a customer, making this query very efficient.

Why this answer

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.

Exam trap

Google Cloud often tests the misconception that an explicit JOIN is required for interleaved tables, but the correct approach is to query the child table directly using the parent key, as the interleaved structure already enforces the relationship without a join.

How to eliminate wrong answers

Option A is wrong because adding an extra filter on OrderDate does not improve efficiency; it may force a full scan of the Orders table if no secondary index exists on (CustomerId, OrderDate), and the query still benefits from the interleaved structure but the additional predicate is unnecessary and could mislead the optimizer. Option B is wrong because it performs an explicit JOIN between Customers and Orders, which in Spanner requires a distributed cross-table lookup even though the tables are interleaved; the join is redundant since the interleaved key already provides the parent-child relationship, and it adds network overhead. Option D is wrong because filtering by OrderId alone does not use the interleaved key (CustomerId), so Spanner must scan the entire Orders table or rely on a secondary index, which is less efficient than a direct interleaved key lookup.

Practice this question →
67
MCQeasy

Refer to the exhibit. You are reviewing a Firestore security rules file. What is the main security flaw in the database schema design that these rules expose?

A.The rules do not protect against brute force attacks
B.The senderId field is not indexed
C.The delete rule allows admin to delete any message
D.Users can set the visibility field, allowing them to make messages public
AnswerD

The create rule does not restrict the visibility value, so users can bypass intended privacy.

Why this answer

The Firestore security rules allow any authenticated user to set the `visibility` field on a message document. This means a user could change the visibility to 'public', making private messages accessible to all users regardless of the intended audience. The rules do not validate that the user setting the visibility is the sender or an admin, exposing a data access control flaw.

Exam trap

The Google Cloud Professional Data Engineer exam often tests the misconception that indexing or brute force protection are security concerns in Firestore, when the real flaw is unvalidated field writes that bypass intended access control.

How to eliminate wrong answers

Option A is wrong because brute force attacks are mitigated by Firebase Authentication's built-in rate limiting and account locking, not by Firestore security rules; the rules shown do not expose any vulnerability to brute force. Option B is wrong because indexing is a performance optimization for queries, not a security mechanism; the absence of an index does not create a security flaw in the schema design. Option C is wrong because the delete rule shown allows only the sender or an admin to delete a message, which is a legitimate access control pattern; the flaw is not that admins can delete messages, but that users can arbitrarily set visibility.

Practice this question →
68
Multi-Selectmedium

A team is designing a schema for a user activity logging system using Bigtable. Each log entry includes a user ID, activity type, timestamp, and details. The access pattern requires retrieving all activities for a specific user within a time range. Which TWO row key designs are suitable? (Choose TWO.)

Select 2 answers
A.timestamp#user_id
B.random_uuid
C.reverse_timestamp
D.user_id#activity_type#timestamp
E.user_id#timestamp
AnswersD, E

Allows filtering by activity type within a user.

Why this answer

(user_id#activity_type#timestamp) is correct because it groups all activities for a user under a single row key prefix, enabling efficient row range scans for a specific user. The activity_type suffix allows filtering by activity type if needed, while the timestamp ensures uniqueness and ordered storage. Option E (user_id#timestamp) is correct because it directly supports the access pattern of retrieving all activities for a user within a time range by scanning rows with the user_id prefix and filtering on the timestamp component.

Exam trap

Google Cloud often tests the misconception that a timestamp-first key is optimal for time-range queries, but the actual requirement is user-specific retrieval, which demands a user-first key design to avoid full-table scans.

Practice this question →
69
MCQmedium

An e-commerce platform uses Cloud Bigtable for real-time analytics on customer behavior. The table uses a row key of 'customer_id#timestamp' (customer ID followed by reverse timestamp). Queries for a specific customer's recent events are fast, but queries that filter by event type (e.g., 'purchase') across many customers are slow. What schema change can improve query performance for event-type filtering?

A.Create a separate column family for each event type.
B.Add a secondary index on the event_type column.
C.Use a separate Bigtable instance for each event type.
D.Change the row key to 'event_type#customer_id#timestamp'.
AnswerD

This allows efficient range scans for a specific event type across all customers.

Why this answer

Cloud Bigtable's performance depends heavily on row key design for efficient scans. By changing the row key to 'event_type#customer_id#timestamp', queries filtering by event type can use a single row key prefix scan, which is fast and avoids full table scans. This leverages Bigtable's lexicographic ordering to group all events of the same type together, making event-type filtering a range scan rather than a filter across unrelated rows.

Exam trap

Many candidates mistakenly think Bigtable supports secondary indexes like a relational database, leading them to choose Option B. However, Bigtable's architecture requires all access patterns to be designed into the row key for optimal performance.

How to eliminate wrong answers

Option A is wrong because column families in Bigtable are used for grouping related columns and access control, not for indexing or partitioning data by value; they do not improve query performance for filtering on a column value like event type. Option B is wrong because Bigtable does not support secondary indexes; it relies solely on the row key for data access, and adding a secondary index is not a feature of Bigtable. Option C is wrong because using a separate Bigtable instance for each event type would introduce significant operational overhead, data duplication, and cross-instance query complexity without solving the fundamental row key design issue.

Practice this question →
70
Matchingmedium

Match each Google Cloud tool to its purpose in database management.

Drag a concept onto its matching description — or click a concept then click the description.

Concepts
Matches

Web-based UI for managing resources

Command-line tool for managing Google Cloud services

Browser-based terminal with pre-installed tools

Infrastructure as code for provisioning databases

Observability and alerting for database performance

Why these pairings

Cloud SQL, Cloud Spanner, and BigQuery are core Google Cloud database tools. Cloud SQL is for managed relational databases, Cloud Spanner for globally distributed relational databases, and BigQuery for analytics. Distractors confuse Cloud SQL with NoSQL services and Cloud Spanner with caching services.

Practice this question →
71
MCQmedium

Refer to the exhibit. Which of the following statements is true regarding this schema design?

A.Deleting a Singer row will automatically delete all associated Album rows.
B.The Albums table cannot have any secondary indexes because of the INTERLEAVE clause.
C.The Albums table's rows are physically stored independent of the Singer table.
D.The Albums table's primary key must include the SingerId column only.
E.The ON DELETE CASCADE clause ensures that deleting an Album row will delete the corresponding Singer row.
AnswerA

The ON DELETE CASCADE clause enforces this behavior.

Why this answer

The `ON DELETE CASCADE` clause on the foreign key from `Albums` to `Singer` ensures that when a row in the `Singer` table is deleted, all rows in the `Albums` table that reference that singer are automatically deleted. This is a standard referential integrity behavior in relational databases, and in Cloud Spanner (the technology context for PCDE), it is enforced at the database level to maintain consistency.

Exam trap

Google Cloud often tests the direction of `ON DELETE CASCADE` — candidates mistakenly think it deletes the parent when a child is deleted, but it only propagates from parent to child.

How to eliminate wrong answers

Option B is wrong because the `INTERLEAVE` clause does not prevent secondary indexes on the `Albums` table; Cloud Spanner allows secondary indexes on interleaved tables, though they must be created with the `INTERLEAVE IN` option to maintain locality. Option C is wrong because the `INTERLEAVE` clause physically stores child rows (Albums) adjacent to their parent row (Singer) in the same split, not independently. Option D is wrong because the `Albums` table's primary key must include `SingerId` as the first column (due to interleaving), but it can and typically does include additional columns (e.g., `AlbumId`) to uniquely identify rows.

Option E is wrong because `ON DELETE CASCADE` propagates deletion from the parent (Singer) to the child (Albums), not the reverse; deleting an `Album` row does not delete the corresponding `Singer` row.

Practice this question →
72
MCQmedium

Refer to the exhibit. Which BigQuery SQL query correctly flattens the items into rows?

A.SELECT * FROM orders WHERE items IS NOT NULL
B.SELECT * FROM orders, UNNEST(items) AS items
C.SELECT * FROM orders INNER JOIN items ON true
D.SELECT * FROM orders CROSS JOIN UNNEST(items) AS items
AnswerD

This is correct because CROSS JOIN UNNEST expands the items array into separate rows, preserving other order columns.

Why this answer

`CROSS JOIN UNNEST(items)` is the standard BigQuery syntax to flatten a repeated (array) column into individual rows. The `UNNEST` operator expands each array element into a separate row, and `CROSS JOIN` ensures that all non-array columns from the `orders` table are preserved alongside each element. This is the only option that correctly transforms the nested `items` array into a normalized row-per-item structure.

Exam trap

The Google Cloud Professional Data Engineer exam often tests the requirement that `UNNEST` must be paired with a join (like `CROSS JOIN` or `LEFT JOIN`) and that using `UNNEST` alone or with a `WHERE` clause is syntactically invalid in BigQuery, leading candidates to mistakenly choose Option B.

How to eliminate wrong answers

Option A is wrong because `WHERE items IS NOT NULL` only filters out rows where the entire `items` array is NULL, but does not flatten the array into individual rows; the result still contains arrays. Option B is wrong because `UNNEST(items) AS items` without a `CROSS JOIN` or `LEFT JOIN` is syntactically invalid in BigQuery; `UNNEST` must be used with a join operator (typically `CROSS JOIN` or `LEFT JOIN`). Option C is wrong because `INNER JOIN items ON true` assumes `items` is a separate table, but in this context `items` is a nested array column within the `orders` table, not a standalone table; this would cause a table-not-found error.

Practice this question →
73
MCQeasy

A company uses Cloud SQL for SQL Server. They want to store JSON data in a column and query it efficiently. What should they do?

A.Store each JSON field as a separate column.
B.Store JSON in an nvarchar(max) column and use JSON_VALUE in queries.
C.Use a TEXT column with no indexing.
D.Store JSON as a binary column and parse in application.
AnswerB

SQL Server's JSON support allows querying inside nvarchar(max) columns.

Why this answer

Cloud SQL for SQL Server supports JSON functions like JSON_VALUE to extract and query data from JSON stored in nvarchar(max) columns. This allows efficient querying without schema changes, leveraging SQL Server's built-in JSON support. Option B is correct because it uses the recommended data type and function for JSON storage and querying in SQL Server.

Exam trap

Candidates may mistakenly think Cloud SQL for SQL Server requires a separate JSON column type (like MySQL's JSON), but SQL Server stores JSON in nvarchar(max) and uses JSON_VALUE for querying. TEXT columns lack JSON function support.

How to eliminate wrong answers

Option A is wrong because storing each JSON field as a separate column defeats the purpose of JSON's flexible schema and increases schema complexity, making it harder to handle dynamic or nested data. Option C is wrong because TEXT columns are deprecated in SQL Server and do not support JSON functions like JSON_VALUE, nor can they be indexed efficiently for JSON queries. Option D is wrong because storing JSON as binary requires application-level parsing, losing the ability to use server-side JSON functions and indexes, which degrades query performance and adds complexity.

Practice this question →
74
Multi-Selecthard

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

Select 2 answers
A.Create a secondary index on Timestamp.
B.Create a covering index that includes all queried columns.
C.Add a hash prefix to EventId to distribute writes.
D.Use interleaving with a parent table on EventId.
E.Change the primary key to (Timestamp, EventId).
AnswersA, B

A secondary index on Timestamp allows efficient range scans.

Why this answer

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.

Exam trap

Google often tests the distinction between indexing strategies that improve write distribution (like hash prefixes) versus those that improve read performance for range scans, and candidates mistakenly choose hash prefixes for range queries.

Practice this question →
75
MCQhard

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?

A.Store all messages in a single top-level collection. Create an index on (conversationId, timestamp desc).
B.Store messages in a subcollection with a single-field index on timestamp.
C.Store messages as a subcollection under each conversation document. Create a composite index on (conversationId, timestamp desc).
D.Use a parent document with a nested array of recent messages, and a separate collection for older messages.
AnswerC

Subcollections scale well and composite index enables efficient per-conversation queries.

Why this answer

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.

Exam trap

The trap here is that candidates often choose Option A because they think a single collection with a composite index is simpler, but they overlook Firestore's index scaling limits and the performance hit from querying across all conversations in a high-volume chat app.

How to eliminate wrong answers

Option A is wrong because a single top-level collection with millions of messages creates a massive index that degrades query performance and increases latency for real-time updates, as Firestore must scan across all conversations. Option B is wrong because a single-field index on timestamp alone cannot efficiently filter by conversationId, leading to full collection scans or requiring client-side filtering, which breaks real-time requirements. Option D is wrong because storing recent messages in a nested array within a parent document violates Firestore's 1 MiB document size limit and does not support scalable querying for millions of messages, while the separate collection for older messages introduces complexity without indexing benefits.

Practice this question →
Page 1 of 2 · 100 questions totalNext →

Ready to test yourself?

Try a timed practice session using only Design and implement database schemas questions.

Start 20-question session