Professional Cloud Database Engineer Free Practice Questions — Page 3
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.
Question 29
You are migrating your critical production database from Amazon RDS for MySQL to Cloud SQL for MySQL by using Google Cloud's Database Migration Service. You want to keep disruption to your production database to a minimum and, at the same time, optimize migration performance. What should you do?
A. Create and start multiple Database Migration Service jobs to migrate your database to the target Cloud SQL for MySQL instance.
B. Upgrade the Amazon RDS for MySQL primary instance to an instance with more vCPUs and memory, and then run Google Cloud's Database Migration Service.
C. Create a single Database Migration Service migration job with initial load parallelism configured to maximum on the source Amazon RDS for MySQL read replica.
D. Create a single Database Migration Service migration job with initial Load Parallelism configured to Maximum on the Amazon RDS for MySQL primary instance.
Show Answer
Correct Answer: C
Explanation: Using a single Database Migration Service job with maximum initial load parallelism improves migration throughput. Running the initial load against an Amazon RDS read replica minimizes impact on the production primary while ongoing replication keeps the target synchronized until cutover. Multiple migration jobs are not the intended approach, and running the intensive initial load against the primary increases production impact.
Question 30
You are running a Cloud SQL for PostgreSQL 13 Enterprise Edition instance. During an audit, you discovered that the write-ahead logs used for point-in-time recovery (PITR) are stored on disk. You need to store PITR logs in a Cloud Storage bucket going forward. How should you do this without compromising recoverability or losing the current PITR logs?
A. Clone the instance. Create a new instance with PITR retention set to 30 days.
B. Change the transaction logs (WAL) retention period.
C. Upgrade to Enterprise Plus Edition.
D. Disable PITR, then enable PITR.
Show Answer
Correct Answer: C
Explanation: In Cloud SQL for PostgreSQL, Enterprise Edition stores PITR write-ahead logs on disk, while Enterprise Plus stores PITR logs in Cloud Storage. Upgrading to Enterprise Plus changes the storage location for future PITR logs without requiring PITR to be disabled, preserving recoverability and existing PITR history. Disabling and re-enabling PITR would reset the recovery window and lose existing PITR logs.
$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.