PCDBE practice questions 41–50

1. Question 41 · 170 in bank

   You are managing multiple applications connecting to a database on Cloud SQL for PostgreSQL. You need to be able to monitor database performance to easily identify applications with long-running and resource-intensive queries. What should you do?

   • AUse log messages produced by Cloud SQL.
   • BUse Query Insights for Cloud SQL.
   • CUse the Cloud Monitoring dashboard with available metrics from Cloud SQL.
   • DUse Cloud SQL instance monitoring in the Google Cloud Console.

   Show answer

   Answer: B

   Explanation

   B. Query Insights helps identify performance and load issues at the database application layer. None of the other options do that. So the answer is B.

   Source: Adapted from community discussion — dynamic_dba

   References

   • Google Cloud documentation
   • Google Cloud documentation
   • Google Cloud documentation
   • Google Cloud documentation

   Next question

2. Question 42 · 170 in bank

   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.

   Show answer

   Answer: C

   Explanation

   Dynamic schema indicates this is a NoSQL solution (ruling out Cloud SQL) and the application use case specifically suits Firestore (the question even refers to storing data in documents) as opposed to BigTable. Firestore in Native supports realtime client updates, which is needed for the analytics requirement: https://cloud.google.com/firestore/docs/firestore-or-datastore#feature_comparison

   Source: Adapted from community discussion — BenMS

   References

   • Google Cloud documentation
   • Google Cloud documentation

   Next question

3. Question 43 · 170 in bank

   Select 2

   Your application uses Cloud SQL for MySQL. Your users run reports on data that relies on near-real time; however, the additional analytics caused excessive load on the primary database. You created a read replica for the analytics workloads, but now your users are complaining about the lag in data changes and that their reports are still slow. You need to improve the report performance and shorten the lag in data replication without making changes to the current reports. Which two approaches should you implement? (Choose two.)

   • ACreate secondary indexes on the replica.
   • BCreate additional read replicas, and partition your analytics users to use different read replicas.
   • CDisable replication on the read replica, and set the flag for parallel replication on the read replica. Re-enable replication and optimize performance by setting flags on the primary instance.
   • DDisable replication on the primary instance, and set the flag for parallel replication on the primary instance. Re-enable replication and optimize performance by setting flags on the read replica.
   • EMove your analytics workloads to BigQuery, and set up a streaming pipeline to move data and update BigQuery.

   Show answer

   Answer: BC

   Explanation

   B, C. You have 2 problems. Replication lag and slow report performance. E is eliminated because using BigQuery would mean changes to the current reports. Report slowness could be the result of poor indexing or just too much read load (or both!). Since excessive load is mentioned in the question, creating additional read replicas and spreading the analytics workload around makes B correct and eliminates A as a way to speed up reporting. That leaves the replication problem. Cloud SQL enables single threaded replication by default, so it stands to reason enabling parallel replication would help the lag. To do that you disable replication on the replica (not the primary), set flags on the replica and optionally set flags on the primary instance to optimize performance for parallel replication. That makes C correct and D incorrect. https://cloud.google.com/sql/docs/mysql/replication/manage-replicas#configuring-parallel-replication

   Source: Adapted from community discussion — dynamic_dba

   References

   • Google Cloud documentation
   • Google Cloud documentation
   • Google Cloud documentation

   Next question

4. Question 44 · 170 in bank

   You are evaluating Cloud SQL for PostgreSQL as a possible destination for your on-premises PostgreSQL instances. Geography is becoming increasingly relevant to customer privacy worldwide. Your solution must support data residency requirements and include a strategy to: configure where data is stored control where the encryption keys are stored govern the access to data What should you do?

   • AReplicate Cloud SQL databases across different zones.
   • BCreate a Cloud SQL for PostgreSQL instance on Google Cloud for the data that does not need to adhere to data residency requirements. Keep the data that must adhere to data residency requirements on-premises. Make application changes to support both databases.
   • CAllow application access to data only if the users are in the same region as the Google Cloud region for the Cloud SQL for PostgreSQL database.
   • DUse features like customer-managed encryption keys (CMEK), VPC Service Controls, and Identity and Access Management (IAM) policies.

   Show answer

   Answer: D

   Explanation

   D because CMEK where the encryption keys are stored IAM govern the access to data VPC Service Controls configure where data is stored control

   Source: Adapted from community discussion — juliorevk

   References

   • Google Cloud documentation

   Next question

5. Question 45 · 170 in bank

   Select 2

   Your digital-native business runs its database workloads on Cloud SQL. Your website must be globally accessible 24/7. You need to prepare your Cloud SQL instance for high availability (HA). You want to follow Google-recommended practices. What should you do? (Choose two.)

   • ASet up manual backups.
   • BCreate a PostgreSQL database on-premises as the HA option.
   • CConfigure single zone availability for automated backups.
   • DEnable point-in-time recovery.
   • ESchedule automated backups.

   Show answer

   Answer: DE

   Explanation

   D,E. A is wrong because why bother configuring manual backups when Cloud SQL will automate that for you. B seems attractive, but why bother replicating back to on-prem when you can configure a Cloud SQL for HA. C is wrong because a single zone failure would not give you HA. That leaves D & E.

   Source: Adapted from community discussion — dynamic_dba

   References

   • Google Cloud documentation

   Next question

6. Question 46 · 170 in bank

   Your customer is running a MySQL database on-premises with read replicas. The nightly incremental backups are expensive and add maintenance overhead. You want to follow Google-recommended practices to migrate the database to Google Cloud, and you need to ensure minimal downtime. What should you do?

   • ACreate a Google Kubernetes Engine (GKE) cluster, install MySQL on the cluster, and then import the dump file.
   • BUse the mysqldump utility to take a backup of the existing on-premises database, and then import it into Cloud SQL.
   • CCreate a Compute Engine VM, install MySQL on the VM, and then import the dump file.
   • DCreate an external replica, and use Cloud SQL to synchronize the data to the replica.

   Show answer

   Answer: D

   Explanation

   D. The question says backups and maintenance are an issue, so moving to a managed service (Cloud SQL) would be the right thing to do. That eliminates C and A. Option B could (depending upon the DB size) require a lot of downtime to export, copy the dump file to Cloud Storage, then import into Cloud SQL. Therefore, the least amount of downtime would be D. https://cloud.google.com/sql/docs/mysql/replication/configure-replication-from-external

   Source: Adapted from community discussion — dynamic_dba

   References

   • Google Cloud documentation

   Next question

7. Question 47 · 170 in bank

   Your team uses thousands of connected IoT devices to collect device maintenance data for your oil and gas customers in real time. You want to design inspection routines, device repair, and replacement schedules based on insights gathered from the data produced by these devices. You need a managed solution that is highly scalable, supports a multi-cloud strategy, and offers low latency for these IoT devices. What should you do?

   • AUse Firestore with Looker.
   • BUse Cloud Spanner with Data Studio.
   • CUse MongoD8 Atlas with Charts.
   • DUse Bigtable with Looker.

   Show answer

   Answer: D

   Explanation

   By combining Google Cloud IoT Core for device management and data ingestion, Google Cloud Bigtable for storing and processing IoT data with low latency, and Looker for advanced analytics and visualization, you can build a highly scalable, multi-cloud compatible, and low-latency solution to address your IoT device maintenance requirements effectively.

   Source: Adapted from community discussion — KennyHuang

   References

   • Google Cloud documentation

   Next question

8. Question 48 · 170 in bank

   Your application follows a microservices architecture and uses a single large Cloud SQL instance, which is starting to have performance issues as your application grows. in the Cloud Monitoring dashboard, the CPU utilization looks normal You want to follow Google-recommended practices to resolve and prevent these performance issues while avoiding any major refactoring. What should you do?

   • AUse Cloud Spanner instead of Cloud SQL.
   • BIncrease the number of CPUs for your instance.
   • CIncrease the storage size for the instance.
   • DUse many smaller Cloud SQL instances.

   Show answer

   Answer: C

   Explanation

   The solution D is better if we can execute a massive refactor. So, the best solution is C because if CPU is normal, the issue is the capacity of the I/O. For increase it, it's enough increase the disk storage. Source: https://cloud.google.com/sql/docs/sqlserver/best-practices

   Source: Adapted from community discussion — fraloca

   References

   • Google Cloud documentation
   • Google Cloud documentation

   Next question

9. Question 49 · 170 in bank

   You need to perform a one-time migration of data from a running Cloud SQL for MySQL instance in the us-central1 region to a new Cloud SQL for MySQL instance in the us-east1 region. You want to follow Google-recommended practices to minimize performance impact on the currently running instance. What should you do?

   • ACreate and run a Dataflow job that uses JdbcIO to copy data from one Cloud SQL instance to another.
   • BCreate two Datastream connection profiles, and use them to create a stream from one Cloud SQL instance to another.
   • CCreate a SQL dump file in Cloud Storage using a temporary instance, and then use that file to import into a new instance.
   • DCreate a CSV file by running the SQL statement SELECT...INTO OUTFILE, copy the file to a Cloud Storage bucket, and import it into a new instance.

   Show answer

   Answer: C

   Explanation

   C. The only way to minimize performance impact of running an export on a Cloud SQL instance is to use a serverless export. The fact that no data synchronization is needed since it’s a one off eliminates every option apart from C.

   Source: Adapted from community discussion — dynamic_dba

   References

   • Google Cloud documentation

   Next question

10. Question 50 · 170 in bank

    You are running a mission-critical application on a Cloud SQL for PostgreSQL database with a multi-zonal setup. The primary and read replica instances are in the same region but in different zones. You need to ensure that you split the application load between both instances. What should you do?

    • AUse Cloud Load Balancing for load balancing between the Cloud SQL primary and read replica instances.
    • BUse PgBouncer to set up database connection pooling between the Cloud SQL primary and read replica instances.
    • CUse HTTP(S) Load Balancing for database connection pooling between the Cloud SQL primary and read replica instances.
    • DUse the Cloud SQL Auth proxy for database connection pooling between the Cloud SQL primary and read replica instances.

    Show answer

    Answer: B

    Explanation

    A. Eliminate D because Cloud SQL Auth Proxy by itself does not provide connection pooling. There’s nothing in the question about needing to load balance HTTP traffic specifically, so ignore C. B is eliminated on the basis Pgbouncer does not have multi-host configuration, failover, or detection and the question specifically says “mission critical”. That leaves A which makes sense since Google Cloud Load Balancer is a regional service and the question specifically mentions a single region.

    Source: Adapted from community discussion — dynamic_dba

    References

    • Google Cloud documentation
    • Google Cloud documentation

    Next question

Keyboard:J Kmove between questionsA–Dselect an answerRshow or hide the answer← →change page

PCDBE practice, question 41. 170 questions available.