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 recommends using BIT_REVERSED_POSITIVE sequences when integer primary keys are required. Bit-reversed sequences distribute writes across key ranges to avoid hotspots while preserving integer-based keys, allowing minimal application changes. Sequential integers or max(primary_key)+1 create hotspots and contention, and UUID v4 would require changing the key type and application handling.
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. Configuring automatic failover increases availability and minimizes downtime, and regular failover testing ensures the mechanism works as expected. Simply scaling vertically or increasing storage may provide temporary relief but does not address read scalability or high availability as effectively. Migrating to PostgreSQL is not an appropriate solution solely for handling peak load.
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: Semantic similarity search for freeform text is best implemented by generating embeddings for product descriptions and the query, then performing an approximate nearest neighbor (ANN) search over the embeddings. Regular expressions, LIKE, and SOUNDEX perform lexical or phonetic matching and are not suitable for finding semantically similar product descriptions.
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 is designed to scale horizontally by adding processing capacity (nodes or processing units). For predictable performance under high, bursty write workloads, you should configure autoscaling so capacity can increase to handle peak demand, with the maximum sized for the expected peak. Sharding across multiple Spanner instances is not the recommended scaling model, read-only transactions do not address write throughput, and interleaved tables optimize locality rather than 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: Google Cloud recommends Cloud Bigtable deployments for production use with multiple zones in a region for high availability, along with replication for resilience, followed by load/stress testing and ongoing performance monitoring. A single-zone cluster is not highly available, manually sharding across clusters is unnecessary and operationally complex, and simply adding nodes does not improve durability without an HA topology.
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: Automated IAM database authentication with the AlloyDB Python connector allows applications to authenticate without database usernames and passwords, satisfying the requirement. IAP is for network access rather than database authentication, SSL encryption alone does not replace authentication, and using a service account credential as a database password is not the supported IAM authentication model.
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: Automated backups provide the required daily backups. Monthly backups that must be retained until the Cloud SQL instance is deleted should be created as on-demand backups, which can have indefinite retention. To satisfy data residency requirements, store those on-demand backups in a custom backup location in the required region.
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 point-in-time recovery (PITR) requires automatic backups and binary log retention. To restore to any point within the last five days, enable automatic backups and PITR, retain backups and transaction logs for at least five days, and schedule the backup window during low-traffic periods to minimize performance impact. Hourly on-demand backups or exports do not provide true PITR and are less suitable for this requirement.
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 recommended service for storing and accessing application secrets such as database credentials. The application should retrieve the secret securely at startup (or runtime), and Secret Manager supports secret rotation schedules. Storing credentials in files, even encrypted with KMS, requires custom secret management and rotation. Cloud Storage is not appropriate for secrets. IAM database authentication is not supported for Cloud SQL for PostgreSQL in the manner described and does not satisfy rotating database passwords for application credentials.
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) conversion workspaces (formerly code conversion workspace) to automate conversion of Oracle schemas and database code objects such as procedures, functions, and triggers to PostgreSQL. After automated conversion, review, assess, and refactor any remaining incompatible objects as needed to minimize manual effort and application changes.
$19
Get all 168 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.