Professional Cloud Database Engineer Free Practice Questions — Page 3
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.
Question 21
Your company is rapidly expanding its user base across North America, nearly doubling in the last 6 months. This expansion causes a substantial increase in query volume on your mission-critical Cloud SQL database. This has led to noticeable performance issues and slower query response times. You suspect that your Cloud SQL instance may not be able to handle the incremental load. You need to identify the root cause of this performance bottleneck and ensure that your database can scale with your growing user base. What should you do?
A. Migrate the database to Spanner.
B. Evaluate the application connection pooling configuration settings.
C. Review Cloud SQL System Insights for the instance, and analyze CPU, memory, and storage utilization metrics.
D. Create two Cloud SQL instances, and split the workload between them.
Show Answer
Correct Answer: C
Explanation: The first step is to identify the root cause of the performance bottleneck using Cloud SQL System Insights. Reviewing CPU, memory, storage, wait events, and query performance metrics helps determine whether the instance is resource-constrained, suffering from inefficient queries, or experiencing connection issues before choosing a scaling or migration strategy. Migrating to Spanner or splitting workloads are premature without diagnosis, and connection pooling is only one possible cause.
Question 22
You are the DBA working at a large bank in Europe. Your business-critical banking application runs on Oracle 12.2 in your on-premises data center. You decided to modernize your on-premises Oracle database by migrating to AlloyDB. You must meet the following requirements:
• Remain on-premises and maintain open-source portability.
• Meet regulatory requirements for data residency.
• Support both OLTP and OLAP workloads.
• Maintain high availability (HA) mode and automatic failover.
What should you do?
A. Deploy AlloyDB Omni in a standalone VM on-premises, and set up disaster recovery (DR) in AlloyDB for PostgreSQL.
B. Migrate to AlloyDB for PostgreSQL by using Database Migration Service.
C. Deploy Kubernetes-based AlloyDB Omni database cluster on-premises. Enable HA by using the AlloyDB Omni Kubernetes operator, and migrate your Oracle database to Omni.
D. Deploy AlloyDB Omni as a docker-based container in your on-premises VM, enable HA for it, and migrate your Oracle database to the Omni container.
Show Answer
Correct Answer: C
Explanation: The requirements specify staying on-premises with open-source portability, supporting both OLTP and OLAP, meeting data residency rules, and providing high availability with automatic failover. AlloyDB Omni is the on-premises offering, and its Kubernetes-based deployment with the AlloyDB Omni Kubernetes operator provides HA and automatic failover. Standalone or basic container deployments do not satisfy the HA requirement, and AlloyDB for PostgreSQL is a managed cloud service rather than an on-premises solution.
Question 23
You have an application that performs numerous reads and writes all day running in a single Bigtable cluster. A new batch analytics job needs to read the same data and you want to make sure that the new job does not affect the existing workload. What should you do?
A. Scale up the Bigtable instance by manually adding more nodes.
B. Add a new cluster to the existing instance, and enable multi-cluster routing in the app profile.
C. Add a new cluster to the existing instance, and isolate the workloads with two app profiles.
D. Scale up the Bigtable instance by enabling Autoscaling.
Show Answer
Correct Answer: C
Explanation: Adding a second cluster to the same Bigtable instance allows replication of data while providing independent compute resources. By creating separate app profiles, you can direct the batch analytics job to the new cluster and keep the production application's traffic on the original cluster, preventing resource contention. Enabling multi-cluster routing would distribute requests across clusters rather than isolating workloads, and simply adding nodes or autoscaling does not provide workload isolation.
Question 24
You are setting up a new AlloyDB instance and want users to be able to use their existing Identity and Access Management (IAM) identities to connect to AlloyDB. You have performed the following steps:
• Manually enabled IAM authentication on the AlloyDB instance
• Granted the alloydb.databaseUser and ser-viceusage.serviceUsageconsumer IAM roles to the users
• Created new AlloyDB database users based on corresponding IAM identities
Users are able to connect but are reporting that they are not able to SELECT from application tables. What should you do?
A. Grant the new database users access privileges to the appropriate tables.
B. Grant the alloydb.client IAM role to each user.
C. Grant the alloydb.viewer IAM role to each user.
D. Grant the alloydb.alloydbreplica IAM role to each user.
Show Answer
Correct Answer: A
Explanation: IAM authentication allows users to authenticate as database users, but it does not automatically grant SQL object privileges. After creating IAM-mapped database users, you must grant the appropriate database privileges (such as SELECT on the required tables or membership in roles that have those privileges). The other IAM roles relate to AlloyDB resource access in Google Cloud, not SQL permissions inside the database.
Question 25
You are using Memorystore for Redis to cache frequently accessed data and improve your application's performance. Recently, your application is experiencing sudden spikes in latency when interacting with the Memorystore for Redis instance. Upon checking the logs, you discover a high number of "evicted keys" messages. You want to reduce the occurrences of latency spikes and their impact on the application. What should you do?
A. Increase the time to live (TTL) value of all the keys within the cache.
B. Redeploy your application to the same zone as the Memorystore instance.
C. Scale the Memorystore instance to a larger memory size.
D. Enable read replicas. Deploy additional read replica instances to distribute read workloads.
Show Answer
Correct Answer: C
Explanation: A high number of evicted keys indicates the Redis instance is under memory pressure and is evicting cached data to make room for new entries. Evictions reduce cache hit rate and can cause latency spikes as the application falls back to the backing data store. Increasing the Memorystore instance's memory reduces evictions. Increasing TTL would generally worsen memory pressure, colocating the application affects network latency rather than evictions, and read replicas do not solve memory exhaustion causing key evictions.
Question 26
You are the DBA of your organization. You provided a cloned instance from the production Cloud SQL for PostgreSQL database to the developers for testing purposes. After the creation of the clone, your developers notice missing data in one of the recently altered tables. What should you do to ensure that all data is included?
A. Take a back up of the production database, and restore it to another Cloud SQL for PostgreSQL instance. Provide access to the new instance to the developers.
B. Check for missing roles and privileges in the cloned Cloud SQL instance. Grant missing privileges to the developers.
C. Clone the current production database, and restore it to an earlier point-in-time (PITR). Provide access to the cloned instance to the developers.
D. Dump the production database to a file. Modify the dumped file to ALTER TABLE to SET LOGGED on tables that were unlogged in production. Reload the data in the new Cloud SQL for PostgreSQL instance.
Show Answer
Correct Answer: D
Explanation: Cloud SQL for PostgreSQL clones do not preserve data in UNLOGGED tables because unlogged tables are not written to the write-ahead log (WAL), which cloning and recovery rely on. To ensure the data is included, convert such tables to LOGGED (via ALTER TABLE ... SET LOGGED), then recreate/reload the data into a new instance. Restoring from backup or PITR will not recover data from unlogged tables because it was never WAL-logged.
Question 27
Your company is developing a 24/7, global, real-time analytics platform that needs to store and process large amounts of versioned time-series data. You need to design a platform that is highly scalable to accommodate traffic spikes and ensure high availability for mission-critical operations. What should you do?
A. Implement a single-cluster Bigtable instance with autoscaling enabled and row key design.
B. Implement a multi-cluster Bigtable instance with autoscaling enabled and optimal schema design.
C. Implement a multi-cluster Bigtable instance across multiple regions with replication.
D. Implement AlloyDB for PostgreSQL to handle the analytical workload using read replica.
Show Answer
Correct Answer: C
Explanation: Cloud Bigtable is the appropriate service for massive, versioned time-series workloads. The requirements emphasize a global, 24/7, mission-critical platform with high availability in addition to scalability. A multi-cluster deployment across multiple regions with replication provides resilience against regional failures and supports global availability. While autoscaling is valuable, the distinguishing requirement is global high availability, making the multi-region replicated deployment the best choice.
Sources:
https://cloud.google.com/solutions/real-time-analytics-for-databases
https://cloud.google.com/blog/products/databases/plaid-puts-the-real-in-real-time-user-analytics-with-bigtable
Question 28
You are migrating an on-premises database to Spanner. There are a few tables, each with a few hundred records that do not have a primary key on the source database. You need to migrate all of the tables over to the news database while avoiding hot-spotting issues. What should you do?
A. Load the data into Spanner, and designate a primary key later based on business need.
B. Use the GENERATE_UUID() function to generate universally unique identifier (UUID) values with the STRING(36) data type when you do the migration.
C. During the migration, swap the order of keys so that the column that contains the monotonically increasing or decreasing value is the first key par.
D. Migrate the tables with no primary key to maintain consistency with source.
Show Answer
Correct Answer: B
Explanation: Spanner requires every table to have a primary key, so options A and D are invalid. To avoid hotspotting, the primary key should have good distribution rather than monotonically increasing values. Using GENERATE_UUID() creates randomly distributed STRING(36) keys that spread writes across key ranges. Option C is incorrect because placing a monotonically increasing column first in the primary key increases hotspotting risk.
$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.