Courseiva

CCNA Design innovative, scalable, and highly available cloud database solutions Questions

75 of 141 questions · Page 1/2 · Design innovative, scalable, and highly available cloud database solutions · Answers revealed

Practice these questionsDomain overviewAll questions
1
MCQhard

A company is using Cloud Spanner and needs to add a new column to an existing table that has billions of rows. The column must have a default value of 0. The team is concerned about downtime. Which approach should they take to add the column with zero downtime?

A.Take the application offline, run ALTER TABLE to add the column, then bring the application back online
B.Create a new table with the column, copy data using Dataflow, then rename the tables
C.Add the column without a default, then run a batch update to set the default value
D.Use ALTER TABLE ADD COLUMN with DEFAULT 0; the operation is online and non-blocking
AnswerD

Spanner schema changes are non-blocking; adding a column with a default is immediate and does not cause downtime.

Why this answer

Cloud Spanner supports online schema changes that are non-blocking. Adding a column with a DEFAULT value is an online operation that does not block reads or writes, and does not require copying data. The new column is added with the default value for existing rows at read time, not by backfilling.

Practice this question →
2
MCQmedium

An organization needs to run both transactional (OLTP) and real-time analytical (OLAP) queries on the same dataset without data duplication. The dataset is moderately large (a few terabytes). Which Google Cloud database service is MOST appropriate for this HTAP workload?

A.Bigtable
B.BigQuery
C.AlloyDB
D.Cloud SQL for PostgreSQL
AnswerC

AlloyDB is purpose-built for HTAP with its columnar engine for fast analytics on transactional data.

Why this answer

AlloyDB is a fully managed PostgreSQL-compatible database designed for hybrid transactional and analytical processing (HTAP). It includes a built-in columnar engine that accelerates analytical queries on transactional data without separate ETL or duplication.

Practice this question →
3
MCQmedium

A data engineer is designing a schema for Cloud Spanner to store a hierarchy of customers and their orders. Customers have many orders, and queries often retrieve orders for a specific customer. To optimize performance and reduce cross-node reads, which schema design pattern should the engineer use?

A.Denormalize by embedding order details as a repeated field within the customer row.
B.Use a parent-child interleaved table structure where Orders are interleaved in Customers.
C.Normalize customers and orders into separate tables without any relationship.
D.Create a secondary index with STORING on the order ID.
AnswerB

Interleaving co-locates data, improving performance for hierarchical queries.

Why this answer

Spanner's interleaved tables allow storing child rows (orders) physically co-located with their parent row (customer) using the same primary key prefix. This enables efficient joins and reduces cross-node reads. A secondary index with STORING is useful for other access patterns but not for hierarchical queries.

Denormalization is not recommended in Spanner.

Practice this question →
4
Multi-Selectmedium

A retail company uses Cloud Bigtable for real-time inventory. They want to improve read performance for queries that filter by product category and last_updated timestamp. Which THREE row key design strategies should they adopt? (Choose 3)

Select 3 answers
A.Use a reverse timestamp for time-based queries
B.Promote product_category to the first part of the row key
C.Use a monotonically increasing timestamp as the first component
D.Store all data in a single column family
E.Add a hash prefix (salting) to distribute writes
AnswersA, B, E

Reverse timestamp enables efficient queries for recent data.

Why this answer

Field promotion places frequently filtered attributes first. Salting with hash prefix distributes writes. Reverse timestamp ensures recent data is efficiently queried.

A single monotonically increasing timestamp causes hotspots. Using a single column family doesn't affect row key.

Practice this question →
5
Multi-Selecthard

An online payment processing system uses Cloud SQL for MySQL with a 1 TB database. The system experiences high write throughput (~5000 writes/sec) and needs sub-10ms latency. The current instance has 8 vCPUs and 32 GB RAM. Which two metrics would indicate that the instance needs a larger tier? (Choose TWO.)

Select 2 answers
A.Memory usage is 50% of 32 GB.
B.CPU utilization consistently above 80%.
C.Disk IOPS is consistently reaching the instance's I/O limit.
D.Average query latency of 5 ms.
E.Network traffic is 100 Mbps.
AnswersB, C

High CPU indicates need for more vCPUs.

Why this answer

Sustained CPU utilization above 80% indicates the instance is compute-bound, which can lead to queuing and increased latency for write operations. In Cloud SQL for MySQL, high CPU usage often means the instance lacks sufficient vCPUs to handle the write throughput, necessitating a larger tier with more CPU cores.

Exam trap

Google Cloud often tests the misconception that high memory usage or low latency alone indicates a need for a larger tier, but the key metrics are CPU saturation and I/O limit exhaustion, which directly impact write throughput and latency.

Practice this question →
6
Multi-Selectmedium

A company is designing a Cloud Bigtable schema for time-series data. The data is written by millions of devices every second. The query patterns are: (1) retrieve the most recent reading for a specific device, (2) retrieve all readings for a device in a time range. Which TWO row key design techniques should the team use to optimize for these patterns? (Choose two.)

Select 2 answers
A.Use salting (hash prefix) to distribute writes
B.Use field promotion: put device_id as the first component of the row key
C.Use a reverse timestamp (e.g., MAX_TIMESTAMP - timestamp) as part of the row key
D.Use a monotonically increasing timestamp as the first part of the row key
E.Store the entire row as a single column family to reduce overhead
AnswersB, C

Field promotion ensures that queries for all readings of a device can use a prefix scan.

Why this answer

Using a reverse timestamp allows the most recent data to be at the beginning of the row key, making scans for the latest reading efficient. Field promotion ensures that device_id comes first in the row key, enabling efficient prefix scans for all readings of a device. Salting is not needed because device_id already distributes writes if there are many devices.

Practice this question →
7
MCQmedium

A company is migrating an on-premises OLTP application to Google Cloud. The application requires high concurrency (up to 5,000 simultaneous connections) and uses a relational schema with strong transactional integrity. Which database service is the MOST suitable?

A.Firestore
B.Cloud SQL
C.BigQuery
D.Cloud Spanner
AnswerB

Cloud SQL provides full SQL compatibility, high connection limits, and strong consistency for OLTP workloads.

Why this answer

Cloud SQL supports up to 4,096 connections by default (configurable) and provides full transactional integrity. For 5,000 connections, Cloud SQL can be configured with appropriate instance sizing. Spanner also supports transactions but is overkill for a single-region OLTP migration.

BigQuery is for analytics, Firestore is NoSQL.

Practice this question →
8
MCQmedium

A company uses Cloud Spanner for its global inventory system. The current schema has a table 'Orders' with a primary key of OrderID (UUID). The team wants to add a secondary index to support queries filtering by 'status' and 'order_date'. Which type of index should they create and how should they define it to ensure the index covers the query without needing to read the base table?

A.Create a secondary index on (status, order_date)
B.Create a secondary index on (status) STORING (order_date)
C.Create a global secondary index on status, and let the query join with the base table for order_date
D.Create a local secondary index on status with order_date stored in the index
AnswerB

This creates a covering index: the index includes the status column for filtering and stores order_date, so queries on status and order_date can be satisfied by the index alone.

Why this answer

A secondary index with a STORING clause includes additional columns (like order_date) in the index, allowing the index to cover queries that reference only the indexed and stored columns, avoiding a back-join to the base table.

Practice this question →
9
MCQeasy

A data engineer needs to run complex analytical queries on terabytes of data with sub-second query latency. The data is stored in Google Cloud Storage and updated daily. Which database service should they use?

A.Cloud Bigtable
B.BigQuery
C.Cloud SQL
D.Cloud Spanner
AnswerB

BigQuery is the correct choice for petabyte-scale analytics with fast SQL queries.

Why this answer

BigQuery is a serverless data warehouse designed for complex SQL queries on large datasets with fast query execution. It can query data directly from Cloud Storage using external tables. Cloud SQL and Spanner are for OLTP, Bigtable for real-time low-latency lookups.

Practice this question →
10
MCQmedium

A company is migrating from a relational database to Cloud Bigtable. They have a table with a 'user_id' and 'login_timestamp'. Queries often filter by user_id and time range. What should be the row key?

A.timestamp
B.hash(user_id) + timestamp
C.user_id + timestamp
D.user_id
AnswerB

Hash distributes, timestamp enables range queries.

Why this answer

hashing the user_id prevents hotspots, and appending timestamp enables range scans. 'user_id' alone may cause hotspots if sequential.

Practice this question →
11
MCQhard

A team is migrating a MySQL OLTP database to Cloud Spanner. The existing schema uses auto-increment primary keys. They plan to convert them to STRING columns with UUIDs. However, the application also relies on ORDER BY on the original integer key. How should they preserve ordering while avoiding hotspots in Spanner?

A.Keep the auto-increment key but use bit-reversal
B.Use a composite primary key with a hash prefix followed by the UUID
C.Use the UUID as the primary key and create a secondary index on the original integer
D.Use a monotonically increasing custom ID and rely on Spanner's split management
AnswerC

Correct. UUID primary key avoids hotspots, and a secondary index on the original integer provides the needed ordering.

Why this answer

Using UUID as the primary key avoids hotspotting because UUIDs are random and distribute writes evenly across splits. Creating a secondary index on the original integer key allows efficient ORDER BY queries on that column. While the secondary index may experience some hotspotting due to monotonically increasing values, Spanner's automatic split management can mitigate this, and the approach meets both requirements: hotspot avoidance on the primary key and ordering capability on the original integer.

Exam trap

Candidates often think a composite key with a hash prefix and a sortable UUID is the best solution, but this does not preserve ordering by the original integer unless the UUID is specifically generated to reflect that order. The secondary index approach directly supports ordering on the original key.

Practice this question →
12
MCQmedium

A financial services firm needs a database for real-time fraud detection that requires single-digit millisecond latency for lookups on a precomputed feature set. The dataset is 5 TB and grows by 1 TB per month. Which database service BEST meets these requirements?

A.BigQuery
B.Cloud Bigtable
C.Cloud Spanner
D.Cloud SQL (MySQL)
AnswerB

Bigtable provides consistent single-digit ms latency and scales horizontally.

Why this answer

Bigtable is designed for high-throughput, low-latency access to large datasets. It can handle 5 TB (or more) with SSD nodes. Cloud SQL and Spanner have lower throughput limits.

BigQuery is not real-time.

Practice this question →
13
Multi-Selecthard

A company is migrating a monolithic application to a microservices architecture and plans to use multiple Google Cloud databases. The application has the following workloads: (1) user profiles with high read/write concurrency, (2) product catalog with complex queries, (3) session data that requires low-latency access. Which three Google Cloud databases should be used? (Choose three.)

Select 3 answers
A.Firestore
B.Cloud SQL
C.Cloud Bigtable
D.Memorystore (Redis)
E.Cloud Spanner
AnswersA, B, D

Firestore handles high concurrency for user profiles.

Why this answer

Cloud SQL is correct for the product catalog with complex queries because it is a fully managed relational database that supports standard SQL, enabling complex joins and aggregations. Firestore is correct for user profiles with high read/write concurrency because it is a NoSQL document database that scales horizontally and provides strong consistency. Memorystore (Redis) is correct for session data requiring low-latency access because it is an in-memory cache delivering sub-millisecond response times.

Cloud Bigtable is designed for high-throughput time-series or key-value data, not complex queries, and Cloud Spanner is more suited for globally distributed transactional workloads.

Exam trap

Google often tests the misconception that Cloud Bigtable can handle both high concurrency and complex queries, but it is optimized for wide-column storage and simple key-based lookups, not relational queries or low-latency session caching.

Practice this question →
14
MCQmedium

You need to design a Cloud Bigtable row key for a time-series application that records user activity. The most common queries filter by user_id and then by timestamp (most recent first). Which row key design is MOST appropriate?

A.user_id#timestamp
B.user_id#reverse_timestamp
C.reverse_timestamp#user_id
D.timestamp#user_id
AnswerB

This ensures data for a user is together and the most recent entry comes first.

Why this answer

For time-series with frequent queries filtering by user_id and recent data, the recommended design is to put user_id first and then use a reverse timestamp. This ensures data for a user is co-located and the latest data appears first when scanning.

Practice this question →
15
MCQeasy

An engineer is designing a global inventory system that requires strong consistency across continents, with the ability to handle write conflicts and ensure ACID transactions. The system expects millions of reads and writes per second. Which Google Cloud database service meets these requirements?

A.Cloud Bigtable
B.Cloud SQL
C.Firestore
D.Cloud Spanner
AnswerD

Spanner offers global strong consistency, ACID transactions, and horizontal scaling.

Why this answer

Cloud Spanner is the correct choice because it provides ACID transactions and strong consistency across globally distributed regions, using TrueTime and synchronous replication to handle write conflicts at scale. It is designed for millions of reads and writes per second while maintaining external consistency, making it ideal for a global inventory system.

Exam trap

The trap here is that candidates often confuse Firestore's strong consistency within a single region with global consistency, overlooking the need for ACID transactions and conflict resolution at planetary scale, which only Cloud Spanner provides.

How to eliminate wrong answers

Option A is wrong because Cloud Bigtable is a NoSQL wide-column database that offers only eventual consistency and does not support ACID transactions or strong consistency across regions. Option B is wrong because Cloud SQL is a relational database limited to a single region, cannot handle millions of writes per second globally, and lacks built-in conflict resolution for multi-region deployments. Option C is wrong because Firestore provides strong consistency only within a single region and uses optimistic locking that can lead to write conflicts under high concurrency, not designed for ACID transactions at global scale.

Practice this question →
16
MCQmedium

A team is designing a Spanner schema for an online gaming leaderboard. The leaderboard stores player scores and requires high write throughput. Which primary key design is BEST to avoid write hotspots?

A.Primary key: (HashOfPlayerId, Timestamp)
B.Primary key: (PlayerId, Timestamp)
C.Primary key: (Score, PlayerId)
D.Primary key: (Timestamp, PlayerId)
AnswerA

Hash prefix distributes writes evenly. Timestamp as second part allows ordering.

Why this answer

Monotonically increasing keys (like score or timestamp) cause hotspotting. Using a random prefix (e.g., hash of player ID) ensures writes are distributed across splits. Player ID alone might also cause hotspotting if many players write simultaneously.

Practice this question →
17
MCQeasy

A data analyst needs to run a one-time SQL query on a dataset stored in CSV files in Cloud Storage. The query will scan the entire dataset, and the analyst wants to minimize cost. Which Google Cloud service should they use?

A.Cloud SQL
B.BigQuery
C.Dataproc
D.Cloud Spanner
AnswerB

Serverless and pay-per-query; ideal for ad-hoc queries on data in Cloud Storage.

Why this answer

BigQuery can query external data directly from Cloud Storage; using query-on-demand billing, they only pay for the data scanned. It is serverless and requires no infrastructure.

Practice this question →
18
Multi-Selecthard

An organization uses Cloud SQL for PostgreSQL with read replicas to offload reporting queries. During peak, the primary instance's CPU spikes to 90%. The team suspects the read replica is falling behind. Which two settings should they check to diagnose replication lag? (Choose TWO.)

Select 2 answers
A.pg_stat_replication view on the primary.
B.pg_replication_slots view on the replica.
C.Replica lag metric in Cloud Monitoring.
D.max_wal_size configuration parameter.
E.SHOW REPLICATION STATUS command.
AnswersA, C

Shows replication lag and status.

Why this answer

The `pg_stat_replication` view on the primary instance shows the WAL sender process state, including the `write_lag`, `flush_lag`, and `replay_lag` columns that directly measure replication lag in PostgreSQL. This view provides real-time data on how far behind each standby (including read replicas) is in receiving, flushing, and applying WAL data, making it the primary diagnostic tool for replication lag.

Exam trap

Google Cloud exams often test the distinction between PostgreSQL-specific commands and MySQL commands, so the trap here is that candidates familiar with MySQL might choose `SHOW REPLICATION STATUS` (which is `SHOW REPLICA STATUS` in MySQL 8.0.23+) instead of the correct PostgreSQL diagnostic tools.

Practice this question →
19
MCQmedium

A company needs to store petabytes of time-series IoT sensor data and query it with single-digit millisecond latency at millions of reads per second. The data has a simple key-value structure with timestamps. Which Google Cloud database is MOST appropriate?

A.Firestore
B.Cloud Spanner
C.Cloud Bigtable
D.BigQuery
AnswerC

Bigtable is the correct choice: wide-column NoSQL, designed for time-series and IoT workloads, single-digit ms latency, and scales to millions of QPS with additional nodes.

Why this answer

Cloud Bigtable is designed for exactly this use case — petabyte-scale, low-latency (single-digit ms), high-throughput NoSQL storage for time-series, IoT, and financial data. It scales horizontally by adding nodes. BigQuery is optimised for analytics (seconds-to-minutes latency), Cloud SQL is for OLTP (limited to tens of thousands of QPS), and Firestore is for document data with hierarchical structure.

Practice this question →
20
Multi-Selecthard

You are migrating a 5 TB MySQL database to Cloud SQL. The migration must have minimal downtime. Which THREE steps should you include in your migration plan?

Select 3 answers
A.Configure continuous replication between on-premises and Cloud SQL.
B.Set up Database Migration Service from the on-premises database to Cloud SQL.
C.Take a full backup and restore to Cloud SQL.
D.Perform a manual failover by stopping the application and promoting the replica.
E.Use mysqldump to export the database.
AnswersA, B, D

DMS uses CDC for continuous replication.

Why this answer

Configuring continuous replication (e.g., using Database Migration Service with CDC) ensures that changes made on the on-premises MySQL database are continuously applied to Cloud SQL, keeping them in sync with minimal lag. This is essential for achieving minimal downtime, as it allows the final cutover to be nearly instantaneous.

Exam trap

Google often tests the misconception that a full backup and restore (Option C) or a simple export/import (Option E) can be performed with minimal downtime, but these methods inherently require the source database to be offline or heavily locked for the duration of the operation.

Practice this question →
21
MCQmedium

A team is designing a Cloud Spanner schema for an e-commerce platform. They have 'Customer' and 'Order' tables and want to ensure that queries for all orders of a specific customer are efficient. Which schema design approach should they use?

A.Interleave the 'Order' table under the 'Customer' table with CustomerID as the first part of Orders' primary key
B.Use a single table with denormalized customer and order data
C.Use a secondary index on CustomerID in the Order table
D.Create a separate 'Order' table with a foreign key to Customer
AnswerA

Interleaving colocates orders with their customer, making queries for a customer's orders very efficient.

Why this answer

Spanner supports interleaving tables, where child rows are stored physically near the parent row. Interleaving Orders under Customers using the CustomerID as the first part of the primary key in Orders makes queries for all orders of a customer efficient by colocating related data.

Practice this question →
22
MCQmedium

A company uses Cloud Bigtable to store time-series data from IoT devices. Each device sends a reading every minute. The row key currently is: device_id + timestamp (e.g., 'device123#2024-01-01T00:00:00Z'). Write throughput is lower than expected. Which row key modification would MOST improve write distribution?

A.Add a hash prefix of the device_id (e.g., hash(device_id) + device_id + timestamp)
B.Reverse the timestamp: timestamp + device_id
C.Use only device_id as the row key
D.Remove the device_id and use only timestamp
AnswerA

Salting with a hash prefix spreads row keys across tablets, improving write distribution.

Why this answer

Adding a hash prefix (salting) to the row key distributes writes across multiple tablet servers. The current row key starts with device_id, which may cause hotspots if many writes target the same device. A hash prefix ensures uniform distribution.

Practice this question →
23
MCQeasy

Which database service should you use if your workload requires complex JOINs, ACID transactions, and you want to avoid operational overhead of managing patching and backups?

A.Cloud SQL
B.Cloud Bigtable
C.Firestore
D.Cloud Storage
AnswerA

Cloud SQL provides fully managed relational databases with ACID transactions and JOIN support.

Why this answer

Cloud SQL is a fully managed relational database service for MySQL, PostgreSQL, and SQL Server that handles patching, backups, and replication. AlloyDB also is managed, but Cloud SQL is more common for standard OLTP. Firestore and Bigtable are NoSQL.

Practice this question →
24
MCQmedium

A company runs a MySQL database on-premises and wants to migrate to Cloud SQL with minimal downtime. They have a multi-terabyte database. Which migration method is most appropriate?

A.Use Cloud SQL's import feature from a CSV file.
B.Take a physical backup and restore to Cloud SQL.
C.Use Database Migration Service with continuous replication.
D.Use mysqldump to export and then import into Cloud SQL.
AnswerC

DMS provides minimal downtime via CDC.

Why this answer

Database Migration Service (DMS) supports continuous replication and minimal downtime for large databases. It handles initial snapshot and ongoing CDC.

Practice this question →
25
MCQeasy

You are designing a schema for Cloud Spanner and need to avoid write hotspots. Which primary key design strategy is recommended?

A.Use the most frequently queried column as the primary key
B.Use a UUID or hash prefix as the first part of the primary key
C.Use a composite key with the leading column being a timestamp
D.Use a monotonically increasing timestamp as the primary key
AnswerB

UUIDs or hash prefixes distribute writes across tablets, avoiding hotspots.

Why this answer

Using a UUID or a hash prefix as the first part of the primary key ensures that writes are distributed across nodes, avoiding hotspots. Monotonically increasing keys like timestamps cause all writes to hit the same tablet, creating a hotspot.

Practice this question →
26
Multi-Selectmedium

A company is designing a schema for Cloud Spanner for an order management system. They need to ensure efficient joins between Order and OrderItems tables. Which TWO design practices should they adopt? (Choose 2)

Select 2 answers
A.Use parent-child interleaving for Order and OrderItems
B.Use a monotonically increasing primary key for Order
C.Store OrderItems in a separate database
D.Use UUID as primary key for Order
E.Denormalize OrderItems into an array column in Order
AnswersA, D

Interleaving optimizes joins between parent and child tables.

Why this answer

Parent-child interleaving places child rows physically near parent rows, enabling efficient joins. Using the OrderId as the primary key of Order and also as the first part of the primary key of OrderItems (with a line item identifier) allows for strongly consistent interleaved queries.

Practice this question →
27
Multi-Selecthard

You are migrating a relational database to Cloud Bigtable. The source schema has a Users table and an Orders table with a one-to-many relationship. The application frequently queries all orders for a user. Which three Bigtable schema design practices should you apply?

Select 3 answers
A.Denormalize by storing orders as multiple columns or a serialized column in the Users row
B.Maintain 3NF to avoid data duplication
C.Use a row key that includes a hash prefix to distribute writes
D.Keep the foreign key relationship by storing UserId in the Orders row key
E.Create a separate column family for user attributes and orders
AnswersA, C, E

Denormalization avoids joins; storing orders within the user row allows single-row reads for all orders.

Why this answer

Cloud Bigtable is a wide-column NoSQL database that does not support joins. To efficiently query all orders for a user, you must denormalize the one-to-many relationship by storing orders as multiple columns (e.g., order_1, order_2) or as a serialized JSON/protobuf column within the user's row. This avoids the need for a separate lookup or join, enabling single-row reads for the user's orders.

Exam trap

Google often tests the misconception that relational normalization principles apply to NoSQL databases, but in Bigtable, denormalization and row-key design are critical for performance, and maintaining foreign keys or 3NF leads to inefficient scans and poor latency.

Practice this question →
28
Multi-Selecthard

A gaming company is designing a leaderboard using Cloud Spanner. The leaderboard updates scores in real time and supports queries for top players. Which THREE strategies should they implement to achieve high performance and avoid hotspots? (Choose 3)

Select 3 answers
A.Use global secondary indexes with STORING clause to avoid back-to-base lookups
B.Use a monotonically increasing primary key for scores
C.Use a single table with all data and rely on Spanner auto-scaling
D.Maintain a separate table for top scores and update it asynchronously
E.Add a hash prefix to the primary key to distribute writes
AnswersA, D, E

Improves read performance for leaderboard queries.

Why this answer

Salting distributes writes, storing top scores in a separate table reduces contention, and using a STORING clause in indexes prevents data lookup. Global secondary indexes without STORING cause extra round trips.

Practice this question →
29
MCQmedium

A gaming company uses Cloud Bigtable to store player session data. The row key is player_id (UUID) and they have a column family 'sessions' with multiple columns per session. They want to query all sessions for a specific player in a given time range efficiently. Which row key design improvement should they consider?

A.Add a hash prefix to player_id
B.Append a reverse timestamp to the row key
C.Create a secondary index on timestamp
D.Use a separate table for time-range queries
AnswerB

This allows efficient scanning of recent sessions by scanning rows with player_id prefix and then reading sequentially.

Why this answer

Including a timestamp in the row key after the player_id allows scanning rows with a prefix of player_id and then filtering or scanning within a time range. Using a reverse timestamp helps get recent data first.

Practice this question →
30
MCQmedium

A company is planning to use Cloud Spanner for a new global application. They estimate a peak write throughput of 10,000 mutations per second. What is the minimum number of processing units (PUs) required, given that each PU supports up to 2000 mutations/second?

A.1 processing unit
B.5 processing units
C.100 processing units
D.10 processing units
AnswerB

5 PUs provide 10,000 mutations/second.

Why this answer

Each PU supports 2000 mutations/second. To get 10,000 mutations/second, you need 10,000 / 2000 = 5 PUs. However, note that Spanner also requires at least 1 node (1000 PUs) for production.

But the question asks for PUs based on write throughput alone.

Practice this question →
31
Multi-Selecteasy

A startup is building a mobile app with a relational database backend. They expect moderate traffic and need strong consistency, automatic backups, and point-in-time recovery. Which two Google Cloud database services meet these requirements? (Choose TWO.)

Select 2 answers
A.Firestore
B.Cloud Spanner
C.Cloud SQL
D.Cloud Bigtable
E.BigQuery
AnswersB, C

Relational, strongly consistent, with backups and PITR.

Why this answer

Cloud Spanner is correct because it provides strong consistency across globally distributed data using TrueTime and synchronous replication, along with automatic backups and point-in-time recovery. Cloud SQL is correct because it offers fully managed relational databases (MySQL, PostgreSQL, SQL Server) with automated backups and point-in-time recovery, meeting the need for strong consistency and moderate traffic.

Exam trap

Google Cloud certifications often test the distinction between relational and NoSQL databases, trapping candidates who assume Firestore or Bigtable can serve as relational backends with strong consistency, when in fact they sacrifice consistency for scalability or are designed for different use cases.

Practice this question →
32
MCQmedium

A company uses Cloud SQL for PostgreSQL and needs to add a full-text search capability to a table with product descriptions. Which index type should be used?

A.B-tree index
B.GiST index
C.Hash index
D.GIN index
AnswerD

GIN indexes are designed for full-text search and composite types.

Why this answer

PostgreSQL supports full-text search using indexes created with the 'gin' index type on tsvector columns. The question asks for index type. In Cloud SQL for PostgreSQL, you can use the built-in full-text search with GIN indexes.

Practice this question →
33
MCQhard

An engineer is configuring a Cloud SQL for PostgreSQL instance for an OLTP workload. The instance has 30 GB of RAM and expects up to 2000 concurrent connections. The default max_connections is 100. The engineer needs to set max_connections appropriately based on Google's recommendation. What should the max_connections be set to?

A.1500
B.1920
C.3072
D.2000
AnswerB

1920 = 30720 MB / 16, following Google's recommendation.

Why this answer

Google Cloud SQL recommends setting max_connections = RAM_MB / 16. With 30 GB RAM = 30720 MB, that gives 1920 connections. This balances connection overhead and available memory.

Practice this question →
34
Multi-Selectmedium

A team is designing a Bigtable schema for a real-time fraud detection system. The row key includes device ID and timestamp. They need to avoid hotspotting during high write periods. Which two row key design patterns help achieve this? (Choose TWO.)

Select 2 answers
A.Add a hash prefix or salt to the beginning of the row key.
B.Put the most frequently filtered fields first in the row key.
C.Use reverse timestamp to keep recent data first.
D.Use device ID as the sole row key prefix to group data by device.
E.Use a hash of the device ID to randomize the row key.
AnswersA, E

Salting distributes writes across tablets.

Why this answer

Adding a hash prefix or salt to the beginning of the row key distributes writes across multiple tablet servers, preventing hotspotting on a single node. In Bigtable, row keys are sorted lexicographically, so sequential device IDs or timestamps would cause all writes to hit the same tablet. A hash prefix ensures even distribution of write load.

Exam trap

Google Cloud often tests the distinction between patterns that optimize reads (like putting filtered fields first) versus patterns that prevent write hotspotting (like salting or hashing), and candidates mistakenly choose read-optimization patterns for a write-heavy scenario.

Practice this question →
35
MCQeasy

A financial services company is migrating a legacy on-premises OLTP application to Google Cloud. The application requires high transaction rates (thousands per second), strict ACID compliance, and the ability to scale horizontally across multiple regions with strong consistency. Which Google Cloud database service should the company choose?

A.Bigtable
B.Cloud SQL for PostgreSQL
C.Firestore
D.Cloud Spanner
AnswerD

Spanner provides global distribution, strong consistency, ACID transactions, and horizontal scaling, meeting all OLTP requirements.

Why this answer

Cloud Spanner is a globally distributed, horizontally scalable relational database service that provides ACID transactions and strong consistency across regions. It is ideal for OLTP workloads that require high throughput, consistency, and global scalability.

Practice this question →
36
MCQmedium

A company is migrating a large on-premise Hadoop workload to Google Cloud. The data is stored in HBase and consists of time-series logs with 10 PB of data. They need a fully managed NoSQL solution with high throughput and low latency. Which migration path should they choose?

A.Migrate HBase tables to Cloud Spanner using Dataflow
B.Export data to BigQuery using Storage Transfer Service
C.Re-architect as Firestore collections
D.Use Cloud Bigtable and the HBase client
AnswerD

Bigtable supports HBase client via the Bigtable HBase client, allowing a smooth migration.

Why this answer

Cloud Bigtable is the fully managed, scalable NoSQL database that is compatible with HBase API, making migration straightforward. Cloud Spanner is relational, not the best for time-series logs. BigQuery is for analytics, not real-time.

Firestore is document-based and not suited for petabyte-scale logs.

Practice this question →
37
Multi-Selectmedium

A Cloud Spanner instance has a single node and is experiencing high write contention. The workload is 3000 writes per second with 2 KB mutations. Which two changes would improve write throughput? (Choose TWO.)

Select 2 answers
A.Redesign the primary key to distribute writes evenly across splits.
B.Increase the mutation size to 10 KB to reduce number of writes.
C.Scale the instance to 2 nodes.
D.Use a monotonically increasing primary key to improve index performance.
E.Split the table into multiple smaller tables.
AnswersA, C

Even distribution reduces contention.

Why this answer

High write contention in Cloud Spanner often stems from hotspotting, where all writes target the same split. By redesigning the primary key to distribute writes evenly across splits, you reduce contention and improve throughput. This is a fundamental design pattern for Spanner's distributed architecture.

Exam trap

Commonly, candidates think scaling nodes alone solves write contention, but the trap is that while adding nodes increases throughput capacity, it does not fix hotspotting from a poorly designed primary key—so both changes are needed for optimal write throughput.

Practice this question →
38
MCQeasy

You are designing a Cloud Bigtable schema for a time-series application that stores temperature readings from sensors. Each reading has a sensor ID (string), a timestamp (microseconds), and a temperature value. Queries always filter by sensor ID and a time range. Which row key design is optimal?

A.[sensor_id]#[timestamp]
B.[salted_hash]#[sensor_id]#[reversed_timestamp]
C.[timestamp]#[sensor_id]
D.[sensor_id]#[reversed_timestamp]
AnswerB

Salting distributes writes, sensor ID groups data, reversed timestamp enables efficient time-range queries.

Why this answer

Optimal because it uses a salted hash to distribute writes across Bigtable tablets, avoiding hot-spotting on a single node for high-write sensors. The sensor_id ensures all data for a sensor is co-located for efficient range scans, and the reversed timestamp allows queries for the most recent data to be served from the start of the row range, leveraging Bigtable's lexicographic ordering.

Exam trap

A common misconception is that a simple sensor_id prefix is sufficient for time-series data, ignoring the need for write distribution via salting to avoid hot-spotting in high-throughput scenarios in Cloud Bigtable.

How to eliminate wrong answers

Option A is wrong because using [sensor_id]#[timestamp] without salting causes all writes for a given sensor to hit a single tablet, creating a hot spot and degrading write throughput. Option C is wrong because [timestamp]#[sensor_id] scatters data for the same sensor across many tablets, making range scans by sensor_id and time range extremely inefficient as they require multiple tablet lookups. Option D is wrong because [sensor_id]#[reversed_timestamp] lacks a salt, so it still suffers from write hot-spotting on the sensor_id prefix, and while it improves recent-data scans, it does not solve the fundamental write distribution problem.

Practice this question →
39
MCQeasy

A company needs a fully managed, relational database with strong consistency and global distribution for a travel booking application that supports high concurrency and ACID transactions. Which Google Cloud database should they choose?

A.Cloud SQL
B.Cloud Spanner
C.Cloud Bigtable
D.Firestore
AnswerB

Spanner provides global strong consistency and ACID transactions.

Why this answer

Cloud Spanner is the correct choice because it is a fully managed, globally distributed relational database that provides strong consistency and ACID transactions across regions, making it ideal for high-concurrency travel booking applications that require global distribution and transactional integrity.

Exam trap

Candidates often mistakenly think that Cloud SQL can be globally distributed by using read replicas, but read replicas do not provide strong consistency or ACID transactions across regions, which is a critical requirement for this scenario.

How to eliminate wrong answers

Option A is wrong because Cloud SQL is a regional relational database that does not support global distribution or strong consistency across multiple regions; it is designed for single-region deployments. Option C is wrong because Cloud Bigtable is a NoSQL wide-column database that does not support relational queries or ACID transactions, and it offers only eventual consistency. Option D is wrong because Firestore is a NoSQL document database that provides strong consistency but is not relational and does not support ACID transactions across globally distributed data; it is optimized for mobile and web apps, not for complex relational workloads.

Practice this question →
40
MCQeasy

An application uses Cloud SQL (MySQL) and experiences an increasing number of 'too many connections' errors. The current instance has 4 vCPUs and 15GB of RAM. The application's connection pool is configured for 500 connections. What should the engineer do to resolve this error?

A.Configure a Cloud SQL connection pool with a maximum of 100 connections
B.Create a read replica to distribute read connections
C.Upgrade the instance to have 8 vCPUs to increase connection capacity
D.Increase the max_connections flag in the Cloud SQL database flags settings
AnswerD

Raising the max_connections flag allows more concurrent connections, resolving the error.

Why this answer

The max_connections in MySQL is typically set based on memory: max_connections = RAM_MB/16. With 15GB RAM = 15360 MB, max_connections = 15360/16 = 960. The application pool of 500 should be fine, but if the error occurs, it might be due to other factors.

However, the most common fix is to increase the max_connections parameter via a flag. The correct step is to increase the 'max_connections' flag in Cloud SQL. Reducing connections or using a connection pool won't help if the limit is reached.

Increasing vCPUs does not directly change max_connections.

Practice this question →
41
Multi-Selecthard

A company is designing a Cloud Spanner database for a global supply chain application. The schema includes a table 'Shipments' with columns: shipment_id (INT64), created_at (TIMESTAMP), origin (STRING), destination (STRING), status (STRING). The application frequently queries for shipments by origin and status. Which three design choices optimize query performance? (Choose THREE.)

Select 3 answers
A.Partition the table by region to limit scans.
B.Use the STORING clause in the index to include frequently accessed columns.
C.Interleave an 'OrderItems' table under 'Shipments' for join performance.
D.Create a secondary index on (origin, status) for efficient filtering.
E.Use a monotonically increasing integer primary key for high write throughput.
AnswersB, C, D

Reduces additional reads.

Why this answer

The STORING clause in a Cloud Spanner secondary index allows you to include additional columns (such as 'destination' or 'status') in the index storage, enabling index-only scans that avoid fetching rows from the base table. This reduces read latency and resource consumption for queries that frequently access those columns along with the indexed keys. Option C is correct because interleaving an 'OrderItems' table under 'Shipments' places child rows physically close to parent rows, improving join performance by reducing round trips and allowing efficient row scans.

Option D is correct because a secondary index on (origin, status) directly supports the frequent queries filtering on those columns, enabling fast lookups without scanning the entire table. Options A and E are incorrect: Cloud Spanner does not support traditional partitioning by region, and monotonically increasing primary keys cause hotspotting at the leader, reducing write throughput.

Exam trap

Google Cloud often tests the misconception that Cloud Spanner supports traditional partitioning or that monotonically increasing keys are safe for distributed databases, leading candidates to select options that would actually degrade performance.

Practice this question →
42
MCQeasy

A startup is building a mobile app backend with high-concurrency user authentication and profile updates. They need strong consistency and ACID transactions. Which database service should they choose?

A.Firestore
B.BigQuery
C.Cloud Bigtable
D.Cloud SQL
AnswerD

Cloud SQL (MySQL/PostgreSQL) supports ACID, strong consistency, and high concurrency for OLTP.

Why this answer

Cloud SQL provides fully managed relational databases with ACID compliance, suitable for high-concurrency OLTP workloads like user authentication and profile updates.

Practice this question →
43
Multi-Selectmedium

You are designing a Spanner schema for a global inventory system. The table Products has primary key (ProductId STRING). The table Inventory has primary key (ProductId STRING, WarehouseId INT64) and is interleaved in Products. You expect high write throughput on Inventory. Which TWO design choices will help avoid hotspots?

Select 2 answers
A.Use UUID for ProductId
B.Use a hash prefix of ProductId as the first key part in Inventory
C.Reverse the timestamp in the row key
D.Use a monotonically increasing sequence for ProductId
E.Create a secondary index on WarehouseId with STORING
AnswersA, B

UUIDs are random and help distribute writes.

Why this answer

To avoid hotspots in Spanner, avoid monotonically increasing keys. ProductId should be a UUID or hash. Also, using a hash prefix on ProductId as the first part of the key can help distribute writes.

Interleaving is fine. Secondary indexes with STORING are for reads. Reverse timestamps are for Bigtable.

The correct choices are using UUID and adding a hash prefix.

Practice this question →
44
MCQmedium

A company is migrating a relational database to Cloud Bigtable. The source schema has a customers table and an orders table with a foreign key. Which data model approach is recommended for Bigtable?

A.Create a view to join the tables
B.Maintain two separate tables with foreign keys
C.Use Cloud Spanner to maintain relational structure
D.Denormalize and store orders as a column family within the customer row
AnswerD

Denormalization is typical in Bigtable to read all related data in one row.

Why this answer

Bigtable is a wide-column NoSQL database, so denormalization is recommended to avoid joins. Embedding order data within customer rows (or using a single table with composite keys) is common. Referential integrity is not enforced.

Practice this question →
45
Multi-Selectmedium

A manufacturing company uses Cloud SQL for PostgreSQL for its inventory system. The database has grown and now experiences high read latency. The team wants to improve read performance without changing application code. Which THREE actions should they consider? (Choose 3)

Select 3 answers
A.Add read replicas
B.Create appropriate indexes based on query patterns
C.Migrate to Cloud Spanner
D.Implement database sharding
E.Increase the instance's memory allocation
AnswersA, B, E

Offloads read traffic from the primary instance.

Why this answer

Adding read replicas spreads read load, appropriate indexing speeds up queries, and increasing instance memory improves cache hit ratio. Sharding would require application changes.

Practice this question →
46
Multi-Selecthard

A company runs a financial analytics platform on BigQuery. They need to reduce query costs for frequent, predictable queries. Which three strategies can help? (Choose THREE.)

Select 3 answers
A.Use BI Engine to cache results of frequent queries.
B.Create materialized views for common aggregations.
C.Partition tables by ingestion time.
D.Cluster tables on frequently filtered columns.
E.Use DML statements to pre-aggregate data.
AnswersA, B, D

Correct: BI Engine caches results of frequent, predictable queries in memory, reducing slot consumption and storage scans.

Why this answer

BigQuery BI Engine provides an in-memory analysis service that caches results of frequent and predictable queries, reducing the need to scan data in BigQuery storage and thereby lowering query costs. By serving cached results directly from memory, BI Engine avoids repeated data processing and slot consumption for recurring queries.

Option B is correct because materialized views allow you to pre-compute and store the results of common aggregations. When you query a materialized view, BigQuery uses the pre-computed results instead of scanning the base tables, which reduces the amount of data processed and thus lowers query costs, especially for frequent, predictable aggregations.

Option D is correct because clustering tables on frequently filtered columns can significantly reduce the amount of data scanned by queries that filter on those columns. By organizing data based on the clustering columns, BigQuery can efficiently prune partitions and only scan relevant blocks. This reduces the bytes billed for each query, leading to cost savings for frequent, predictable queries with filtering predicates.

Option C is incorrect because partitioning by ingestion time is primarily used for managing data lifecycle and improving query performance on time-based ranges, but it does not directly address cost reduction for frequent, predictable queries. While it can reduce scanned data for time-range queries, it is not as targeted as the other strategies for predictable, repeated access patterns.

Option E is incorrect because using DML statements to pre-aggregate data would require additional processing and storage costs for the aggregated tables, and the queries against those tables would still incur costs. This approach does not inherently reduce query costs compared to using materialized views or BI Engine, and it adds complexity and maintenance overhead.

Exam trap

Candidates often confuse cost-reduction techniques that directly cache or precompute results (BI Engine, materialized views) with performance optimizations that also reduce scanned data (clustering). Here, clustering is correct because it reduces bytes billed for frequent filters, but partitioning by ingestion time is less effective for predictable queries unless they always filter on that timestamp.

Practice this question →
47
MCQeasy

A company is building a real-time leaderboard for a mobile game using Google Cloud. The data includes player scores that update frequently (thousands of writes per second) and queries for top 100 players. Which database is most suitable?

A.BigQuery
B.Cloud Bigtable
C.Cloud Firestore
D.Cloud SQL (PostgreSQL)
AnswerB

Bigtable can handle high write volumes and read the top 100 rows efficiently via a prefix scan.

Why this answer

Cloud Bigtable is a good fit for high write throughput and simple range scans (e.g., scanning top scores). Firestore has limited write capacity. Cloud SQL cannot handle thousands of writes per second.

Spanner can but is overkill and more expensive.

Practice this question →
48
MCQeasy

You are designing a Spanner schema for a global social media application that stores user posts. Each user can have millions of posts. The most common query is 'get the most recent 10 posts for a user'. Which table interleaving design minimizes latency?

A.Interleave the Posts table under the Users table, with user_id as parent key and post_timestamp as the child ordering key.
B.Use a secondary index on user_id in the Posts table.
C.Create a single table with user_id and post_timestamp as a composite primary key.
D.Store posts in a separate Cloud Bigtable table and use the user_id as part of the row key.
AnswerA

Interleaving ensures all posts of a user are stored together, enabling fast retrieval.

Why this answer

Interleaving the Posts table under the Users table in Spanner ensures that all posts for a given user are stored in the same split, co-located on the same tablet server. This allows the query for the most recent 10 posts to be served with a single, local range scan on the interleaved child table, using post_timestamp as the descending ordering key, minimizing cross-node communication and latency.

Exam trap

Google Cloud often tests the misconception that a secondary index or composite primary key alone provides the same performance as interleaving, but they fail to guarantee physical co-location, which is critical for minimizing latency in globally distributed databases.

How to eliminate wrong answers

Option B is wrong because a secondary index on user_id would require an index lookup followed by a back-join to the base table, adding an extra round-trip and potentially scattering the posts across splits, increasing latency. Option C is wrong because a single table with a composite primary key (user_id, post_timestamp) does not guarantee co-location of a user's posts; without interleaving, Spanner may distribute rows across splits, causing cross-split reads. Option D is wrong because Cloud Bigtable is a different database service with a different consistency model and API; using it would require cross-service calls and introduce additional latency, and it does not benefit from Spanner's interleaving optimization.

Practice this question →
49
MCQmedium

An e-commerce platform runs on Cloud SQL and expects heavy write traffic during a flash sale. The database instance currently has 8 vCPUs and 32 GB RAM. Based on the max_connections formula (max_connections = RAM_MB/16), what is the current maximum number of connections, and what change would increase it?

A.2048 connections; increase vCPU to 16
B.4096 connections; increase vCPU to 16
C.2048 connections; increase RAM to 64 GB
D.4096 connections; increase RAM to 64 GB
AnswerC

64 GB = 65536 MB, max_connections = 4096.

Why this answer

With 32 GB = 32768 MB, max_connections = 32768/16 = 2048. To increase connections, you need to increase RAM (e.g., 64 GB gives 4096). vCPU does not directly affect max_connections per the formula.

Practice this question →
50
MCQmedium

A company needs to migrate its on-premises Oracle database to a fully managed relational database on Google Cloud. The application uses stored procedures and requires high availability across multiple zones. Which migration path is MOST appropriate?

A.Use Database Migration Service to migrate to Cloud SQL for SQL Server
B.Use Bare Metal Solution to run Oracle Database on dedicated hardware
C.Migrate to AlloyDB for PostgreSQL with cross-region replication
D.Migrate to Cloud SQL for PostgreSQL and configure cross-zone high availability
AnswerD

Cloud SQL supports PostgreSQL, cross-zone HA, and fully managed. Stored procedures can be migrated with adaptation.

Why this answer

Cloud SQL for PostgreSQL supports high availability across zones and can use the PostgreSQL dialect with some migration effort. Bare Metal Solution is for lifting and shifting Oracle workloads but is not fully managed. AlloyDB is PostgreSQL-compatible and provides high availability.

The best option is Cloud SQL for PostgreSQL due to its managed nature and cross-zone HA.

Practice this question →
51
MCQmedium

A company is using Cloud SQL for MySQL for its OLTP workload. They want to run complex analytical queries on the same data without impacting transactional performance. The analytical queries involve large scans and joins. What is the recommended approach?

A.Export the data to BigQuery periodically and run analytical queries there
B.Enable MySQL Query Cache to speed up analytical queries on the primary instance
C.Use Cloud Spanner to handle both OLTP and analytics with interleaved tables
D.Create a Cloud SQL read replica and route analytical queries to it
AnswerA

BigQuery is designed for complex analytics; periodic exports ensure no impact on the OLTP instance.

Why this answer

BigQuery is a serverless, highly scalable data warehouse designed for analytical queries on large datasets. By exporting data from Cloud SQL (OLTP) to BigQuery, you isolate the analytical workload from the transactional database, preventing resource contention and performance degradation on the primary instance. This separation of concerns is the recommended pattern for running complex scans and joins without impacting OLTP performance.

Exam trap

Google often tests the misconception that a read replica can handle analytical workloads without impact, but the trap here is that read replicas still run the same MySQL engine and share storage I/O, making them unsuitable for large scans and joins that would degrade performance for all queries on that replica.

How to eliminate wrong answers

Option B is wrong because MySQL Query Cache is designed to cache the result set of SELECT statements for repeated identical queries, not to accelerate large scans or complex joins; it was deprecated in MySQL 8.0 and offers no benefit for analytical workloads that involve full table scans. Option C is wrong because Cloud Spanner is a globally distributed, strongly consistent database for OLTP workloads, not optimized for complex analytical queries; interleaved tables improve join performance for hierarchical data but do not isolate analytical queries from transactional impact. Option D is wrong because a Cloud SQL read replica shares the same underlying storage and compute resources as the primary instance; while it can offload read traffic, it still runs the same MySQL engine and will experience performance degradation under heavy analytical queries, and it cannot handle large scans and joins without affecting the replica's ability to serve other reads.

Practice this question →
52
MCQmedium

An engineer is designing a Cloud SQL database for an e-commerce platform. They need to store product inventory and order history. Which schema normalization level is recommended for OLTP to avoid data anomalies?

A.Second Normal Form (2NF)
B.First Normal Form (1NF)
C.Third Normal Form (3NF)
D.Denormalized schema
AnswerC

3NF removes transitive dependencies, reducing anomalies for OLTP.

Why this answer

Third Normal Form (3NF) eliminates transitive dependencies, reducing update anomalies in OLTP systems.

Practice this question →
53
MCQhard

You need to design a Spanner schema for a social media application that stores user posts. Each post has a unique ID, author ID, timestamp, and content. The primary access pattern is querying all posts for a given author in reverse chronological order. Which schema design minimizes the risk of hotspotting?

A.Primary key: (AuthorId, PostId) where PostId is generated using a UUID
B.Primary key: (AuthorId, HashOfPostId)
C.Primary key: (AuthorId, Timestamp) with Timestamp stored in descending order
D.Primary key: (PostId) with a secondary index on AuthorId
AnswerA

Correct. The UUID as the second part of the key ensures writes are distributed randomly across splits, avoiding hotspotting, while the AuthorId prefix supports efficient reverse-chronological queries (by ordering on PostId, which is random, not chronological; but the access pattern is querying all posts for an author, which can be done by scanning the AuthorId prefix; reverse chronological order can be achieved by storing timestamps in a separate column and ordering by that during query, or by using a descending index on timestamp). The primary purpose here is to minimize hotspotting.

Why this answer

Using a composite primary key with AuthorId as the first part distributes writes across splits. However, adding Timestamp as the second key causes monotonically increasing writes for a single author, leading to hotspotting on the last split. Instead, a random component like a UUID (as in option A) distributes writes evenly across splits, minimizing hotspot risk.

Option A's UUID provides sufficient randomness to avoid sequential writes while still enabling efficient queries per author via the prefix.

Practice this question →
54
MCQhard

A company is migrating a large on-premises MySQL database to Cloud SQL. The source database supports heavy reporting queries that scan millions of rows and join multiple tables. The team wants to minimize downtime and avoid performance degradation during migration. Which migration approach should they use?

A.Use gcloud sql import command to import a CSV export while the source database remains in use
B.Create a BigQuery transfer service to move data from MySQL to Cloud SQL
C.Set up Database Migration Service (DMS) with continuous replication from the source to Cloud SQL, then cut over when replication lag is minimal
D.Use mysqldump to export the data and import it into Cloud SQL during a maintenance window
AnswerC

DMS with continuous replication minimizes downtime by synchronizing data in real-time until cutover.

Why this answer

Database Migration Service (DMS) with continuous replication allows near-zero downtime migration by replicating changes from the source to Cloud SQL while the source remains operational. Export/import methods typically require downtime. Replicating to BigQuery is not relevant for Cloud SQL migration.

Just changing instance size does not handle data migration.

Practice this question →
55
Multi-Selecteasy

A company wants to migrate from a self-managed MySQL database to a fully managed GCP service. They need high availability with automatic failover, automated backups, and read replicas for scaling read traffic. Which two Google Cloud services meet these requirements? (Choose TWO.)

Select 2 answers
A.Firestore
B.Cloud Spanner
C.Cloud Bigtable
D.Cloud SQL for MySQL
E.BigQuery
AnswersB, D

Fully managed, globally distributed, HA, and can handle read scaling.

Why this answer

Cloud SQL for MySQL is correct because it is a fully managed MySQL-compatible service that offers high availability with automatic failover, automated backups, and support for read replicas to scale read traffic. Cloud Spanner is also correct because it provides a fully managed, horizontally scalable relational database with automatic synchronous replication for high availability, automatic failover, automated backups, and read replica support, making it suitable for migrating from MySQL. Both services meet the requirements for a fully managed, highly available database with read scaling.

Exam trap

The trap here is that candidates may incorrectly choose Firestore or Cloud Bigtable because they see 'fully managed' and 'high availability' without recognizing that the requirement for MySQL compatibility and read replicas eliminates NoSQL options, or they may overlook Cloud SQL for MySQL as a valid choice because they assume only Cloud Spanner can provide high availability with read replicas in this Google Cloud context.

Practice this question →
56
MCQmedium

A company wants to store and analyze time-series metrics from thousands of servers. The data is write-heavy with occasional reads of recent data. They need low-latency writes and the ability to scan large ranges later. Which Google Cloud database is MOST appropriate?

A.Firestore
B.Cloud Spanner
C.Cloud Bigtable
D.Cloud SQL
AnswerC

Bigtable is ideal for time-series workloads with high write throughput and range scan queries.

Why this answer

Bigtable is designed for time-series data, high write throughput, and efficient range scans. It can handle large volumes of time-series data with low latency.

Practice this question →
57
MCQhard

An e-commerce platform uses Cloud Spanner as its database. The orders table has a monotonically increasing order_id as the primary key, and the team observes high write latency during peak hours. Which design change would BEST distribute write load across nodes?

A.Add a secondary index on order_id
B.Increase the number of nodes
C.Use interleaving with a parent table
D.Change the primary key to a UUID string
AnswerD

UUIDs are random, distributing writes evenly across nodes and avoiding hotspots.

Why this answer

Using a UUID (or a hash prefix) as the primary key or as a leading part of a composite key prevents hotspotting on a single tablet server by distributing writes across the cluster.

Practice this question →
58
MCQhard

You are designing a Bigtable schema for a time-series application that records sensor readings every second. Queries always filter by device ID and time range. To avoid hotspotting and ensure recent data is retrieved quickly, which row key design is MOST effective?

A.Row key: deviceID#timestamp
B.Row key: (reverseTimestamp#deviceID)
C.Row key: (hash(deviceID)#deviceID#reverseTimestamp)
D.Row key: timestamp#deviceID
AnswerC

Salting distributes writes, deviceID enables filtering, reverseTimestamp orders recent first.

Why this answer

Salting (hash prefix) distributes writes across nodes, avoiding hotspots from sequential timestamps. Field promotion (device ID first) allows efficient prefix scans. Reverse timestamp ensures most recent data appears first when scanning.

Practice this question →
59
MCQmedium

A team is designing a schema for Cloud Spanner to store user profiles. The primary access pattern is to read a user's profile by their unique user ID. To avoid write hotspots, which primary key design strategy should the team use?

A.Use the user's email address as the primary key
B.Use a monotonically increasing integer as the primary key
C.Use a UUID (universally unique identifier) as the primary key
D.Use a timestamp as the primary key
AnswerC

UUIDs are randomly distributed, spreading writes across tablets and avoiding hotspots.

Why this answer

Monotonically increasing keys (e.g., sequential integers or timestamps) cause all writes to hit the same tablet, creating hotspots. Using a random UUID distributes writes evenly across the cluster, avoiding hotspots.

Practice this question →
60
MCQhard

A Cloud Bigtable instance stores high-volume time-series data. Write throughput is at node capacity, but read latency spikes occasionally. The row key pattern is 'timestamp#device_id'. Which optimization should be applied first?

A.Add more nodes to the cluster
B.Enable SSD storage
C.Reverse the row key to device_id#timestamp
D.Use a single column family
AnswerC

Device ID first distributes writes across nodes, reducing hotspots.

Why this answer

With timestamp first, writes are concentrated on the same tablet, causing hotspots and read latency spikes. Reversing the order or salting distributes writes.

Practice this question →
61
MCQeasy

A company is running analytical queries on large datasets (terabytes) that involve aggregations, joins, and window functions. The data is updated daily via batch loads. The queries must complete in seconds to minutes. Which Google Cloud database service is BEST suited for this workload?

A.Cloud SQL for MySQL
B.Cloud Spanner
C.Bigtable
D.BigQuery
AnswerD

BigQuery is purpose-built for analytics, with fast SQL on large datasets.

Why this answer

BigQuery is a serverless data warehouse designed for large-scale analytics with fast SQL queries. It handles terabytes to petabytes, charges by query usage, and is ideal for OLAP workloads.

Practice this question →
62
MCQmedium

A team is migrating a MongoDB application to Firestore. The data model includes embedded documents and references between collections. Which approach should they follow to maintain similar query performance?

A.Flatten all data into a single document per entity
B.Replicate data across multiple collections to avoid reads
C.Use subcollections for embedded data and references for relationships
D.Use a single collection for all documents and rely on client-side joins
AnswerC

Subcollections preserve the embedded document structure; references handle relationships.

Why this answer

Firestore supports subcollections (similar to embedded documents) and references. Denormalization can reduce the need for joins. Avoiding transactions for every write is not ideal for consistency.

The recommended pattern is to use subcollections for embedded data and references for related entities.

Practice this question →
63
MCQmedium

A startup is building a real-time analytics dashboard that ingests 500,000 events per second and needs to query the last hour of data with sub-second latency. The data has a high write volume and the query pattern is time-range scans. Which Google Cloud database is most appropriate?

A.Cloud Spanner
B.Cloud SQL (PostgreSQL)
C.BigQuery
D.Cloud Bigtable
AnswerD

Bigtable supports millions of writes per second and sub-second latency for time-range scans, perfect for this workload.

Why this answer

Cloud Bigtable is the correct choice because it is a fully managed, scalable NoSQL database designed for high-throughput writes and low-latency time-series data access. It supports sub-second latency on time-range scans by storing data in sorted order by row key, making it ideal for ingesting 500,000 events per second and querying the last hour of data.

Exam trap

The trap here is that candidates often choose BigQuery for analytics workloads, but BigQuery is not designed for sub-second real-time queries on streaming data, whereas Cloud Bigtable is purpose-built for high-throughput, low-latency time-series access.

How to eliminate wrong answers

Option A is wrong because Cloud Spanner is a globally distributed relational database optimized for strong consistency and complex transactions, not for high-volume time-series scans with sub-second latency. Option B is wrong because Cloud SQL (PostgreSQL) is a traditional relational database that cannot handle 500,000 writes per second without significant scaling issues and lacks the columnar or sorted storage needed for fast time-range scans. Option C is wrong because BigQuery is a data warehouse designed for analytical queries on large datasets, not for real-time sub-second queries on streaming data; its latency is typically seconds to minutes for interactive queries.

Practice this question →
64
Multi-Selectmedium

You are designing a Spanner schema for a financial application that stores transactions for user accounts. To avoid hotspots and optimize performance, which TWO practices should you follow?

Select 2 answers
A.Store all data in a single table without interleaving.
B.Use the STORING clause in secondary indexes to include frequently accessed columns.
C.Use a UUID as the primary key.
D.Use a monotonically increasing integer as the primary key.
E.Create secondary indexes without the STORING clause.
AnswersB, C

Optimizes read performance.

Why this answer

The STORING clause in a Spanner secondary index allows you to include non-key columns directly in the index, enabling index-only scans that avoid a back-join to the base table. This reduces read latency and resource consumption, which is critical for high-throughput financial transaction queries.

Exam trap

A common misconception in Spanner is that monotonically increasing keys are safe, but they create hotspots. Candidates must remember that UUIDs or other high-cardinality, non-sequential keys are required for write distribution.

Practice this question →
65
Multi-Selectmedium

A company is evaluating Google Cloud databases for a new application that requires: (1) strong global consistency across multiple regions, (2) the ability to run complex analytical queries on the same data as the transactional workload, and (3) high write throughput. Which TWO databases should they consider?

Select 1 answer
A.Bigtable
B.AlloyDB
C.BigQuery
D.Firestore
E.Cloud Spanner
AnswersE

Cloud Spanner meets all three: strong global consistency via synchronous multi-region replication, high write throughput (scalable), and support for transactional workloads. It does not natively run complex analytical queries, but that can be addressed with external analytics tools.

Why this answer

Cloud Spanner provides strong global consistency across multiple regions via multi-region configurations using synchronous replication, high write throughput (up to 20,000 writes per second per node, scalable), and support for transactional workloads. However, it does not natively handle complex analytical queries on the same data; for HTAP, a separate analytics engine like BigQuery or a dedicated HTAP database such as AlloyDB would be needed. AlloyDB offers HTAP and high write throughput, but its cross-region replication is eventually consistent, failing the strong global consistency requirement.

BigQuery lacks transactional strong consistency and high write throughput. Bigtable provides eventual consistency and no SQL analytical queries. Firestore offers strong consistency only within a single region and no complex analytical queries.

Thus, only Cloud Spanner fully meets the three requirements.

Exam trap

Candidates often assume AlloyDB provides strong global consistency because of its marketing around 'global scale' and 'cross-region replication', but in reality its cross-region replication is eventually consistent. Cloud Spanner is the only Google Cloud database offering strong global consistency across multiple regions.

Practice this question →
66
Multi-Selectmedium

A company is designing a Cloud Spanner database for a global inventory system. The application runs OLTP transactions on inventory levels and also needs to generate daily reports that scan the entire inventory table. Which two approaches will reduce the impact of analytical queries on transactional performance?

Select 2 answers
A.Export data to BigQuery daily for reporting
B.Use strong reads for all queries to ensure consistency
C.Use read-only replicas in separate regions for analytical queries
D.Increase the number of processing units to handle both workloads
E.Use interleaved tables for inventory items
AnswersA, C

Offloads analytical queries entirely from Spanner, preventing any impact on OLTP.

Why this answer

Exporting data to BigQuery offloads analytical workloads from Cloud Spanner entirely, preventing large scans from competing for Spanner's CPU and memory resources. BigQuery is purpose-built for analytical queries on large datasets, so daily exports ensure transactional performance remains unaffected by reporting queries.

Exam trap

A common mistake is to think that simply scaling up resources (Option D) or using strong consistency (Option B) can solve workload isolation problems, when in reality architectural separation via read-only replicas or data export is required to prevent analytical queries from starving transactional operations.

Practice this question →
67
MCQmedium

A team is migrating a relational database to Cloud Bigtable. They need to design a row key that distributes write traffic evenly across nodes. The original table had a composite primary key of customer_id and order_date. Which row key design is BEST for high write throughput?

A.customer_id # order_date
B.hash(customer_id) # customer_id # order_date
C.order_date # customer_id
D.customer_id only
AnswerB

The hash prefix distributes writes evenly while preserving query ability on customer_id.

Why this answer

Adding a hash prefix (salting) to the row key distributes writes across multiple tablet servers, avoiding hotspots. The customer_id alone may cause hotspots if some customers are more active.

Practice this question →
68
MCQhard

An e-commerce platform uses Cloud SQL for PostgreSQL with max_connections set to 500. They plan to increase the number of application instances requiring connections. The instance has 8 vCPUs and 32 GB RAM. What is the maximum number of connections Cloud SQL can support based on the default formula?

A.2048
B.4096
C.1024
D.500
AnswerA

The default formula for Cloud SQL for PostgreSQL is max_connections = min(4 * vCPUs + 100, 2048). With 8 vCPUs, the raw value is 132, but the cap of 2048 sets the maximum possible connections. Thus, the answer is 2048.

Why this answer

Cloud SQL for PostgreSQL uses a default formula to calculate max_connections: max_connections = min(4 * vCPUs + 100, 2048) for PostgreSQL instances. With 8 vCPUs, the raw calculation gives 132, but the cap of 2048 governs the maximum allowed. Since the question asks for the maximum based on the default formula, the correct answer is the cap value of 2048.

Candidates often mistakenly use the raw formula output (132) or confuse with MySQL's cap (4096).

Exam trap

Google Cloud often tests the distinction between Cloud SQL for PostgreSQL and MySQL caps; the trap here is that candidates assume the formula output is the final answer, or they confuse the PostgreSQL cap (2048) with MySQL's cap (4096).

How to eliminate wrong answers

Option B (4096) is wrong because Cloud SQL for PostgreSQL caps max_connections at 2048, not 4096; 4096 is the default limit for Cloud SQL for MySQL, not PostgreSQL. Option C (1024) is wrong because it is not the cap for PostgreSQL; it might be confused with the default formula result for a different vCPU count (e.g., 4 * 231 + 100 = 1024, but 231 vCPUs is unrealistic). Option D (500) is wrong because it simply repeats the current setting from the question, ignoring that Cloud SQL's default formula and cap override the user-configured value when determining the maximum supported.

Practice this question →
69
MCQeasy

Which Google Cloud database service is designed for hybrid transactional and analytical processing (HTAP) with a built-in columnar engine?

A.BigQuery
B.Cloud SQL
C.Cloud Spanner
D.AlloyDB for PostgreSQL
AnswerD

AlloyDB includes a columnar engine for fast analytical queries on transactional data.

Why this answer

AlloyDB for PostgreSQL is a fully managed database with a columnar engine that accelerates analytical queries on transactional data. Cloud Spanner offers an analytics interface, but AlloyDB explicitly mentions a columnar engine for HTAP.

Practice this question →
70
MCQmedium

A company is running a financial application on Cloud Spanner and needs to ensure strong transactional consistency across regions. The application requires both high write throughput (2000 mutations/second) and read throughput (2000 reads/second). According to Spanner capacity planning, how many processing units (PUs) are needed for the combined workload?

A.2 processing units (PUs)
B.4 processing units (PUs)
C.0.5 processing units (PUs)
D.1 processing unit (PU)
AnswerD

One PU provides 2000 writes/s and 2000 reads/s, exactly matching the requirement.

Why this answer

Spanner capacity: write throughput 2000 mutations/second per PU, read throughput 2000 reads/second per PU. For 2000 writes/s and 2000 reads/s, you need 1 PU for writes and 1 PU for reads, but the reads and writes share the same PU. Actually, the formula is: PUs needed = max(write_throughput/2000, read_throughput/2000).

For 2000 each, that's max(1,1)=1 PU. However, the question states 'high write throughput (2000 mutations/second) and read throughput (2000 reads/second)'. So 1 PU is sufficient.

But note: in practice, you might need more for other factors. The question tests the calculation. The answer is 1 PU.

Practice this question →
71
MCQmedium

An engineer is designing a Cloud Spanner schema for a chat application where users send messages. Messages are ordered by timestamp per conversation. The primary key chosen is (ConversationId, MessageId) where MessageId is a monotonically increasing integer. What potential issue might arise with this key design?

A.Queries by conversation will be slow.
B.Writes will be concentrated on a single split causing hotspots.
C.The table cannot be interleaved with another table.
D.The table will not support secondary indexes.
AnswerB

Monotonically increasing keys lead to hotspots in Spanner.

Why this answer

Using a monotonically increasing integer as the second part of the primary key (MessageId) in Cloud Spanner causes all new writes to be concentrated on a single split (tablet) that handles the highest key range. This creates a hotspot, degrading write throughput and latency, as Cloud Spanner splits data by key range and sequential inserts target the same node.

Exam trap

In Google Cloud Spanner, monotonically increasing keys cause write hotspots because Spanner splits data by key range and sequential inserts target the same tablet, unlike traditional single-node databases where such keys are safe.

How to eliminate wrong answers

Option A is wrong because queries by conversation (using ConversationId as the leading key) are efficient; Cloud Spanner can use the primary key prefix to locate data quickly via a split scan. Option C is wrong because interleaving tables in Cloud Spanner requires a parent-child relationship based on the primary key prefix, which this design supports (ConversationId as the parent key). Option D is wrong because Cloud Spanner supports secondary indexes regardless of the primary key design; the issue is write performance, not index capability.

Practice this question →
72
MCQeasy

A startup needs a database for its new web application that will serve a global user base. The application requires low-latency reads and writes (single-digit milliseconds), strong consistency, and the ability to handle high concurrency (thousands of transactions per second). Which Google Cloud database service should the startup choose?

A.Cloud SQL for MySQL
B.Cloud Spanner
C.Bigtable
D.Firestore
AnswerB

Spanner provides global scalability, strong consistency, and ACID transactions with low latency.

Why this answer

Cloud Spanner offers global distribution, strong consistency, and low-latency transactions at high concurrency. It is designed for globally scaled OLTP workloads.

Practice this question →
73
Multi-Selecteasy

An engineer is designing a Bigtable schema for a weather data application. The data is written by thousands of sensors, each generating a reading every minute. Queries typically retrieve all readings for a sensor in a time range. The row key should be designed to avoid hotspots and support these queries. Which two row key components are recommended? (Choose two.)

Select 2 answers
A.Sensor ID (raw) as the only key
B.Sensor location as a column
C.Reversed timestamp
D.Timestamp in natural order
E.Hash of sensor ID as prefix
AnswersC, E

Reversed timestamp spreads writes and allows recent-first queries.

Why this answer

Using a reversed timestamp (e.g., Long.MAX_VALUE - timestamp) as part of the row key spreads writes across Bigtable tablets, avoiding hotspots that occur when sensors write sequentially in natural time order. This design also supports efficient range scans for a sensor's data over a time range when combined with a sensor ID prefix.

Exam trap

Google often tests the misconception that natural-order timestamps are optimal for time-range queries, but the trap here is that they cause write hotspots in Bigtable, so a reversed timestamp is required to distribute writes while still supporting range scans.

Practice this question →
74
MCQmedium

A global e-commerce platform expects 50,000 concurrent users during flash sales, each performing short transactions like adding to cart and checking out. The database must provide strong transactional consistency across regions. Which Google Cloud database is most appropriate?

A.Cloud Bigtable
B.Cloud Spanner
C.Firestore
D.Cloud SQL (MySQL)
AnswerB

Spanner provides global distribution, strong consistency, and ACID transactions, making it ideal for high-concurrency OLTP across regions.

Why this answer

Cloud Spanner is a globally distributed, strongly consistent relational database that can handle high-concurrency OLTP workloads with ACID transactions across regions. Cloud SQL is limited to single region and cannot meet global consistency requirements. Bigtable and Firestore are NoSQL and do not support strong ACID transactions natively.

Practice this question →
75
MCQhard

A company is migrating an on-premise PostgreSQL database to Cloud SQL. The current database has a connection pool with 200 connections and uses 64 GB RAM. According to Cloud SQL best practices, what is the maximum recommended max_connections setting?

A.200
B.64
C.4096
D.1024
AnswerC

64 GB RAM = 65536 MB, divided by 16 gives 4096 connections.

Why this answer

The formula for max_connections on Cloud SQL is RAM_MB / 16. 64 GB = 65536 MB. 65536 / 16 = 4096. However, the question asks for the maximum recommended setting based on RAM. Note: This formula is for Cloud SQL; the actual max_connections default may vary, but the formula is a best practice guide.

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

Ready to test yourself?

Try a timed practice session using only Design innovative, scalable, and highly available cloud database solutions questions.

Start 20-question session