Google Professional Cloud Database Engineer Exam Practice Test Instant Access

Question 1

You are managing a Cloud SQL for PostgreSQL instance in Google Cloud. You have a primary instance in region 1 and a read replica in region 2. After a failure of region 1, you need to make the Cloud SQL instance available again. You want to minimize data loss and follow Google-recommended practices. What should you do?

ARestore the Cloud SQL instance from the automatic backups in region 3.

BRestore the Cloud SQL instance from the automatic backups in another zone in region 1.

CCheck 'Lag Bytes' in the monitoring dashboard for the primary instance in the read replica instance. Check the replication status using pg_catalog.pg_last_wal_receive_lsn(). Then, fail over to region 2 by promoting the read replica instance.

DCheck your instance operational log for the automatic failover status. Look for time, type, and status of the operations. If the failover operation is successful, no action is necessary. Otherwise, manually perform gcloud sql instances failover .

Answer : C

https://cloud.google.com/sql/docs/postgres/replication/cross-region-replicas#disaster_recovery

Question 2

You have a large Cloud SQL for PostgreSQL instance. The database instance is not mission-critical, and you want to minimize operational costs. What should you do to lower the cost of backups in this environment?

ASet the automated backups to occur every other day to lower the frequency of backups.

BChange the storage tier of the automated backups from solid-state drive (SSD) to hard disk drive (HDD).

CSelect a different region to store your backups.

DReduce the number of automated backups that are retained to two (2).

Answer : D

By default, for each instance, Cloud SQL retains seven automated backups, in addition to on-demand backups. You can configure how many automated backups to retain (from 1 to 365). We charge a lower rate for backup storage than for other types of instances. https://cloud.google.com/sql/docs/mysql/backup-recovery/backups

Question 3

You are building an application that allows users to customize their website and mobile experiences. The application will capture user information and preferences. User profiles have a dynamic schema, and users can add or delete information from their profile. You need to ensure that user changes automatically trigger updates to your downstream BigQuery data warehouse. What should you do?

AStore your data in Bigtable, and use the user identifier as the key. Use one column family to store user profile data, and use another column family to store user preferences.

BUse Cloud SQL, and create different tables for user profile data and user preferences from your recommendations model. Use SQL to join the user profile data and preferences

CUse Firestore in Native mode, and store user profile data as a document. Update the user profile with preferences specific to that user and use the user identifier to query.

DUse Firestore in Datastore mode, and store user profile data as a document. Update the user profile with preferences specific to that user and use the user identifier to query.

Answer : C

Use Firestore in Datastore mode for new server projects. Firestore in Datastore mode allows you to use established Datastore server architectures while removing fundamental Datastore limitations. Datastore mode can automatically scale to millions of writes per second. Use Firestore in Native mode for new mobile and web apps. Firestore offers mobile and web client libraries with real-time and offline features. Native mode can automatically scale to millions of concurrent clients.

Question 4

Your organization has an existing app that just went viral. The app uses a Cloud SQL for MySQL backend database that is experiencing slow disk performance while using hard disk drives (HDDs). You need to improve performance and reduce disk I/O wait times. What should you do?

AExport the data from the existing instance, and import the data into a new instance with solid-state drives (SSDs).

BEdit the instance to change the storage type from HDD to SSD.

CCreate a high availability (HA) failover instance with SSDs, and perform a failover to the new instance.

DCreate a read replica of the instance with SSDs, and perform a failover to the new instance

Answer : A

https://stackoverflow.com/questions/72034607/can-i-change-storage-type-from-hdd-to-ssd-on-cloud-sql-after-creating-an-instanc

Question 5

You released a popular mobile game and are using a 50 TB Cloud Spanner instance to store game data in a PITR-enabled production environment. When you analyzed the game statistics, you realized that some players are exploiting a loophole to gather more points to get on the leaderboard. Another DBA accidentally ran an emergency bugfix script that corrupted some of the data in the production environment. You need to determine the extent of the data corruption and restore the production environment. What should you do? (Choose two.)

AIf the corruption is significant, use backup and restore, and specify a recovery timestamp.

BIf the corruption is significant, perform a stale read and specify a recovery timestamp. Write the results back.

CIf the corruption is significant, use import and export.

DIf the corruption is insignificant, use backup and restore, and specify a recovery timestamp.

EIf the corruption is insignificant, perform a stale read and specify a recovery timestamp. Write the results back.

Answer : A, E

https://cloud.google.com/spanner/docs/pitr#ways-to-recover

To recover the entire database, backup or export the database specifying a timestamp in the past and then restore or import it to a new database. This is typically used to recover from data corruption issues when you have to revert the entire database to a point-in-time before the corruption occurred.

This part describes significant corruption - A

To recover a portion of the database, perform a stale read specifying a query-condition and timestamp in the past, and then write the results back into the live database. This is typically used for surgical operations on a live database. For example, if you accidentally delete a particular row or incorrectly update a subset of data, you can recover it with this method.

This describes insignificant corruption case -- E

https://cloud.google.com/spanner/docs/pitr https://cloud.google.com/spanner/docs/backup/restore-backup

Question 6

Your customer has a global chat application that uses a multi-regional Cloud Spanner instance. The application has recently experienced degraded performance after a new version of the application was launched. Your customer asked you for assistance. During initial troubleshooting, you observed high read latency. What should you do?

AUse query parameters to speed up frequently executed queries.

BChange the Cloud Spanner configuration from multi-region to single region.

CUse SQL statements to analyze SPANNER_SYS.READ_STATS* tables.

DUse SQL statements to analyze SPANNER_SYS.QUERY_STATS* tables.

Answer : C

To troubleshoot high read latency, you can use SQL statements to analyze the SPANNER_SYS.READ_STATS* tables. These tables contain statistics about read operations in Cloud Spanner, including the number of reads, read latency, and the number of read errors. By analyzing these tables, you can identify the cause of the high read latency and take appropriate action to resolve the issue. Other options, such as using query parameters to speed up frequently executed queries or changing the Cloud Spanner configuration from multi-region to single region, may not be directly related to the issue of high read latency. Similarly, analyzing the SPANNER_SYS.QUERY_STATS* tables, which contain statistics about query operations, may not be relevant to the issue of high read latency.

Question 7

Your company uses the Cloud SQL out-of-disk recommender to analyze the storage utilization trends of production databases over the last 30 days. Your database operations team uses these recommendations to proactively monitor storage utilization and implement corrective actions. You receive a recommendation that the instance is likely to run out of disk space. What should you do to address this storage alert?

ANormalize the database to the third normal form.

BCompress the data using a different compression algorithm.

CManually or automatically increase the storage capacity.

DCreate another schema to load older data.

Answer : C

https://cloud.google.com/sql/docs/mysql/instance-settings#storage-capacity-2ndgen