Professional Cloud Database Engineer Free Practice Questions — Page 2
Question 11
You are migrating your on-premises PostgreSQL database to Spanner. You need to identify a solution for handling existing integer-based table primary keys. You want minimal changes to the applications while also following Google-recommended practices. What should you do?
A. Use application generated sequential integer values as primary keys.
B. Update default primary key values to max(primary_key) + 1.
C. Use a UUID function that generates UUID Version 4 values as primary keys.
D. Generate values by using BIT_REVERSED_POSITIVE sequences.
Show Answer
Correct Answer: D
Explanation: Cloud Spanner strongly discourages monotonically increasing primary keys because they cause hotspotting. Google-recommended practice for migrating existing integer-based keys with minimal application changes is to use BIT_REVERSED_POSITIVE sequences, which preserve integer semantics while distributing writes evenly across Spanner nodes. The other options either create hotspotting (A, B) or require larger application changes and different data types (C).
Question 12
Your company’s rapidly growing ecommerce application uses Cloud SQL for MySQL. Performance issues are surfacing during peak traffic periods. You need to ensure that the database can handle the load while minimizing downtime. What should you do?
A. Use read replicas for query offloading, configure automatic failover, and test failover procedures regularly.
B. Vertically scale the primary instance by significantly increasing compute resources, such as vCPUs and memory.
C. Increase storage size on the existing instance and implement client-side caching for frequently accessed data.
D. Migrate to a Cloud SQL for PostgreSQL database for better performance during high load.
Show Answer
Correct Answer: A
Explanation: Read replicas offload read traffic from the primary, improving performance during peak load, while automatic failover minimizes downtime if the primary fails. Regular failover testing ensures reliability. This approach scales read-heavy ecommerce workloads without requiring disruptive migrations or solely relying on vertical scaling.
Question 13
You have a Cloud SQL for MySQL instance with a table of product information including a column of product descriptions. Your application development team is building a customer facing chatbot and would like to find the product that most closely matches a freeform text description provided by the customer. How should you enable this functionality?
A. Create a stored procedure that uses a regular expression to match the customer’s search text to values in the product description column.
B. Create and populate a column of product description embeddings on the product table and perform an approximate nearest neighbor search using the customer’s search text.
C. Use the LIKE operator to match the customer’s search text to the product description and return similar rows.
D. Create a SQL script that compares the product description and the customer’s search text by using the SOUNDEX function.
Show Answer
Correct Answer: B
Explanation: To find the product that most closely matches a freeform text description, you need semantic similarity rather than exact or phonetic matching. Creating embeddings for product descriptions and performing an approximate nearest neighbor search allows comparison in vector space, capturing meaning and context. Regular expressions, LIKE, or SOUNDEX only support syntactic or phonetic matching and are not suitable for semantic similarity.
Question 14
You are designing a new ecommerce application on Google Cloud. You expect high and bursty write volumes during peak sales periods. You need to configure Spanner to ensure consistent database performance during this sales period. What should you do?
A. Implement sharding to distribute the write load across multiple Spanner instances.
B. Configure read-only transactions to improve read performance during peak periods.
C. Use interleaved tables to reduce the number of cross-node transactions.
D. Enable autoscaling in the Spanner instance, and set the maximum number of nodes to match the peak write volume.
Show Answer
Correct Answer: D
Explanation: Cloud Spanner performance, especially write throughput, scales with the number of nodes. For high and bursty write volumes during peak sales, enabling autoscaling and setting the maximum nodes to cover peak demand ensures consistent performance without manual intervention. The other options do not directly address write scalability: sharding is not required with Spanner, read-only transactions do not help writes, and interleaved tables mainly affect locality, not overall write capacity.
Question 15
Your organization deploys a high-volume, low-latency sensor data ingestion system. Resilience is crucial, and the workload is expected to grow significantly over time. You need to design a database tier on Google Cloud to ensure faster performance and high availability, while following Google-recommended practices. What should you do?
A. Deploy multiple Bigtable clusters, shard data manually across them, and set up the application logic for fault tolerance.
B. Deploy a single-zone Bigtable cluster and optimize the application code for maximum write throughput.
C. Deploy a single-region Bigtable instance and increase the nodes to maximize data durability.
D. Provision a multi-zone Bigtable cluster, configure replication, perform an application stress-test, and set up performance monitoring.
Show Answer
Correct Answer: D
Explanation: For a high-volume, low-latency ingestion workload on Google Cloud, Cloud Bigtable is the appropriate database. Google-recommended practice for resilience and high availability is to use a multi-zone (or multi-cluster) Bigtable configuration with replication, which protects against zonal failures and improves availability while scaling horizontally as load grows. Stress-testing validates throughput and latency at scale, and performance monitoring is essential for ongoing optimization. The other options either reduce availability (single-zone), rely on manual sharding (not recommended), or misunderstand durability and availability guarantees.
Question 16
You are developing a Python application that will connect to AlloyDB for PostgreSQL as the backend datastore. Your organization’s security requirements do not allow the use of usernames and passwords for database authentication. How should you develop your application?
A. Use any PostgreSQL-compatible Python client. Connect to the AlloyDB cluster by using Identity-Aware Proxy (IAP).
B. Use the AlloyDB Python connector. Use automated IAM authentication for connectivity.
C. Use the AlloyDB Python connector. Enforce SSL connectivity to AlloyDB by using the gcloud alloydb instance update command with the --ssl-mode=ENCRYPTED_ONLY option.
D. Use any PostgreSQL-compatible Python client. Use a service account name and credential as the username and password respectively during connection.
Show Answer
Correct Answer: B
Explanation: The requirement forbids usernames and passwords. AlloyDB supports IAM database authentication via its language connectors, including the AlloyDB Python connector, which uses automated IAM authentication instead of database credentials. Options A and D still rely on traditional PostgreSQL authentication concepts, and C only enforces SSL encryption but does not eliminate username/password authentication.
Question 17
Your organization’s databases are running in Cloud SQL. Due to data residency regulations, your organization mandates that backups are stored in a specific region. You are also required to make sure that the databases have monthly full backups, which are retained until your Cloud SQL instance is deleted, along with daily backups. What should you do?
A. Schedule automated backups for Cloud SQL with a retention period of 365 days.
B. Schedule daily and monthly on-demand backups.
C. Schedule automated backups, and create on-demand monthly backups to custom backup locations.
D. Create an Organization Policy Service resource location constraint. Schedule automated backups with the retention period of 30 days.
Show Answer
Correct Answer: C
Explanation: Cloud SQL automated backups provide daily backups but do not support monthly full backups retained indefinitely, nor do they allow full control over long-term retention. To meet the requirement for daily backups plus monthly full backups retained until instance deletion, you must use automated backups for daily protection and supplement them with on-demand monthly backups. Using custom backup locations ensures backups are stored in the required region to satisfy data residency regulations.
Question 18
You are designing the backup and recovery strategy of a Cloud SQL for MySQL database that serves a write heavy application. You need to be able to restore the database to any point in time within the past five days while minimizing the performance impact of database backups. What should you do?
A. Enable automatic backup and point in time recovery. Configure the backup window during periods of low database traffic. Set backup and transaction log retention to at least five days.
B. Schedule hourly on-demand backups and enable point in time recovery. Delete any delete backups that were taken over five days ago.
C. Schedule a database export job to run every hour from a read replica instance. Retain the export dumps for at least five days.
D. Schedule a database export job to run every hour. Use serverless export. Retain the export dumps for at least five days.
Show Answer
Correct Answer: A
Explanation: Cloud SQL for MySQL supports automatic backups combined with point-in-time recovery (PITR) using binary logs. Enabling these features allows restoration to any point within the retention window. Configuring the backup window during low-traffic periods minimizes performance impact, which is critical for a write-heavy workload. Retaining backups and transaction logs for at least five days satisfies the recovery requirement. The other options rely on exports, which do not support true PITR and impose higher operational overhead.
Question 19
Your company uses a custom application to service thousands of users. The application is running on a Compute Engine and a Cloud SQL for PostgreSQL database. The company requires database passwords to be changed every 60 days. You need to ensure that the credentials used by the web application to connect to the database are managed securely. What should you do?
A. 1. Store the credentials in an encrypted text file in the application. 2. Use Cloud Key Management Service (Cloud KMS) to store the key for decrypting the text file. 3. Modify the application to decrypt the text file and retrieve the credentials on startup. 4. Update the text file every 60 days.
B. 1. Store the credentials to the database in Secret Manager. 2. Modify the application to retrieve the credentials from Secret Manager on startup. 3. Configure the rotation interval to 60 days.
C. 1. Store the credentials in a text file in a Cloud Storage bucket. 2. Modify the application to download the text file and retrieve the credentials on startup. 3. Update the text file every 60 days.
D. 1. Configure IAM database authentication for the application to connect to the database. 2. Create an IAM user and map it to a separate database user for each application user. 3. Require users to update their passwords every 60 days.
Show Answer
Correct Answer: B
Explanation: Secret Manager is the managed service designed for securely storing and rotating application secrets. Storing the database credentials in Secret Manager, granting the application access via IAM, and configuring a 60‑day rotation meets the security and rotation requirements without embedding secrets in code or files. Other options rely on insecure storage mechanisms or add unnecessary complexity.
Question 20
Your company is migrating a legacy on-premises Oracle database to Cloud SQL for PostgreSQL. As a part of the migration, you need to convert schema and other database code objects, such as procedures, functions, and triggers, from Oracle to PostgreSQL. The database migration needs to be accomplished with minimal application code changes and manual intervention. What is the Google-recommended approach you should do?
A. Manually rewrite both the Oracle schema and queries to be compatible with PostgreSQL syntax and semantics.
B. Use a schema conversion tool to automatically convert the Oracle schema to PostgreSQL schema, then manually rewrite all queries.
C. Migrate the data directly from Oracle to PostgreSQL without any conversion. Rely on application-level compatibility layers to handle any syntax or semantic differences.
D. Use Database Migration Service code conversion workspace, then review and refactor as needed.
Show Answer
Correct Answer: D
Explanation: Google recommends using Database Migration Service (DMS) code conversion workspaces when migrating from Oracle to Cloud SQL for PostgreSQL. The conversion workspace automatically converts Oracle schemas and database objects (procedures, functions, triggers) to PostgreSQL equivalents, significantly reducing manual effort and application code changes. After automated conversion, you review and refactor only what is necessary, which aligns with the requirement for minimal manual intervention.
$19
Get all 167 questions with detailed answers and explanations
Instant download HTML + PDF delivered the moment payment clears.
Secure Stripe checkout we never see or store your card details.
7-day refund if files are defective see our refund policy.