PCDE Practice Exam 69 — 20 Questions

Question 1

You want to create a dashboard-as-code for Cloud Monitoring using the API. You have a JSON representation of the dashboard. Which command correctly creates the dashboard?

Accepted Answer

gcloud monitoring dashboards create --config-from-file=file.json. The gcloud monitoring dashboards create command with the --config-from-file flag allows you to create a dashboard from a JSON file.

Answer

gcloud beta monitoring dashboards create --json-file=file.json

Answer

gcloud monitoring dashboards import file.json

Answer

gcloud monitoring dashboards create --dashboard="file.json"

Question 2

Which THREE metrics from Cloud Monitoring are important for monitoring Cloud Bigtable performance?

Accepted Answer

CPU utilization. CPU utilization (option B) is a critical metric for Cloud Bigtable because it directly reflects the processing load on the cluster's nodes. High CPU utilization indicates that the cluster is approaching its throughput limits, which can lead to increased latency and throttling. Monitoring this metric helps in scaling decisions, such as adding nodes or optimizing queries, to maintain performance.

Answer

Storage utilization

Answer

Disk usage

Question 3

A company uses BigQuery for BI reporting. They have a materialized view that refreshes automatically to provide pre-aggregated sales data. Recently, the materialized view stopped reflecting new data inserted into the base table. The base table is a streaming buffer table with ingestion-time partitioning. What is the most likely reason?

Accepted Answer

The materialized view does not support streaming buffer tables.. Materialized views in BigQuery do not support base tables that use a streaming buffer, such as ingestion-time partitioned tables. The streaming buffer contains data that has not yet been committed to managed storage, and materialized views can only read from committed storage. Therefore, when new data is inserted into the streaming buffer, the materialized view cannot reflect it until the data is flushed from the buffer, which can cause the view to appear stale or stop reflecting new data entirely.

Answer

The automatic refresh interval has been exceeded due to high query load.

Answer

The materialized view has reached the maximum number of partitions allowed.

Answer

The base table's schema has changed, making the materialized view incompatible.

Question 4

A retail company uses BigQuery to analyze sales data. They need to create a weekly report showing total sales per product category for the last 4 weeks, but the query is taking too long and exceeding slot resources. The sales table has over 2 billion rows and is partitioned by date. Which design change would most improve query performance and reduce slot consumption?

Accepted Answer

Cluster the table by product_category within the existing date partitions.. Clustering the table by product_category within the existing date partitions organizes the data physically so that queries filtering or grouping by product_category can skip irrelevant blocks. This reduces the amount of data scanned and the slot consumption, directly addressing the performance issue without requiring additional resources.

Answer

Increase the number of available slots in the reservation.

Answer

Create a materialized view that pre-aggregates sales by category and date.

Answer

Partition the table by product_category instead of date.

Question 5

A site reliability team wants to reduce toil in their incident management process. They currently receive alerts via email and manually create tickets, page the on-call engineer, and update a shared spreadsheet. Which TWO Google Cloud services can help automate these tasks and reduce toil?

Accepted Answer

Cloud Monitoring. Cloud Monitoring can send alerts to notification channels that trigger Cloud Functions via webhook or Pub/Sub. Cloud Functions can then automate ticket creation and page on-call via PagerDuty API. Cloud Build is for CI/CD, not incident management. Cloud Scheduler is for cron jobs, not event-driven. Cloud Run is for stateless containers, not ideal for event-driven automation of incidents.

Answer

Cloud Build

Answer

Cloud Scheduler

Answer

Cloud Run

Question 6

A batch job on GKE needs to be resilient to node failures. The job creates several pods that run for a few minutes each. The team wants to ensure that during a voluntary node disruption (e.g., node upgrade), only a limited number of pods are affected. Which resource should they configure?

Accepted Answer

Pod Disruption Budget. PodDisruptionBudget (PDB) specifies the minimum number of pods that must remain available during voluntary disruptions, protecting the job.

Answer

Vertical Pod Autoscaler

Answer

Horizontal Pod Autoscaler

Answer

Cluster Autoscaler

Question 7

A team is migrating a self-managed PostgreSQL database to AlloyDB using DMS. They need to set up the source connection profile. The source database is in a different VPC network, and the team wants to avoid exposing it to the internet. Which connectivity option should they use?

Accepted Answer

VPC peering. VPC peering is the correct connectivity option because it allows private IP connectivity between two VPC networks without exposing the source database to the internet. DMS can use VPC peering to connect to a source PostgreSQL database in a different VPC, as long as the CIDR ranges do not overlap and the necessary firewall rules allow traffic on port 5432. This meets the requirement of avoiding internet exposure while enabling secure, low-latency replication.

Answer

Direct peering

Answer

Cloud SQL Auth Proxy

Answer

IP allowlisting for public IP

Question 8

A marketing team needs to analyze customer behavior using BigQuery. They want to create a table that stores the first and last purchase date for each customer from the `orders` table. Which SQL approach should they use?

Accepted Answer

SELECT customer_id, MIN(order_date) AS first_purchase, MAX(order_date) AS last_purchase FROM orders GROUP BY customer_id. It uses aggregate functions MIN() and MAX() with GROUP BY customer_id to directly compute the first and last purchase dates from the orders table. This is the most efficient and idiomatic SQL approach in BigQuery, leveraging the database engine's built-in aggregation to avoid self-joins or subqueries.

Answer

SELECT customer_id, (SELECT order_date FROM orders ORDER BY order_date LIMIT 1) AS first_purchase, ...

Answer

SELECT o1.customer_id, o1.order_date AS first_purchase, o2.order_date AS last_purchase FROM orders o1 JOIN orders o2 ON o1.customer_id = o2.customer_id

Answer

SELECT customer_id, order_date AS first_purchase, ... FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) AS rn) WHERE rn = 1

Question 9

An organization is planning a database migration from Oracle to PostgreSQL on Cloud SQL. They have a large number of stored procedures that use Oracle-specific PL/SQL features. Which tool should they use to automate the schema conversion, including conversion of PL/SQL to PL/pgSQL?

Accepted Answer

Ora2Pg. Ora2Pg is an open-source tool specifically designed to automate the migration of Oracle databases to PostgreSQL, including the conversion of Oracle-specific PL/SQL stored procedures into PL/pgSQL. It handles schema objects, data types, and procedural code, making it the correct choice for this scenario where PL/SQL conversion is a key requirement.

Answer

pglogical

Answer

pg_dump

Answer

Database Migration Service (DMS)

Question 10

A company uses Cloud Build to build and deploy microservices to GKE. Each microservice has environment-specific configurations (dev, staging, prod). They want to manage these configurations using Kustomize. How should they structure the pipeline?

Accepted Answer

Use a single cloudbuild.yaml with kustomize build command and pass the environment as a substitution. Kustomize allows overlays for different environments. The cloudbuild.yaml can use the kustomize builder to apply the appropriate overlay based on a substitution variable like $_ENV.

Answer

Store all configurations in separate branches

Answer

Use Helm charts with different values files

Answer

Create separate cloudbuild.yaml files for each environment

Question 11

A company is designing a star schema for a BI dashboard that tracks sales performance. The dashboard needs to aggregate sales by product, store, and date. Which schema design is most appropriate?

Accepted Answer

Create a fact table with foreign keys to dimension tables for product, store, and date. A star schema uses a central fact table with foreign keys to dimension tables, which is optimal for BI aggregation queries. Option A is wrong because a single wide table with all attributes leads to data redundancy and slower queries. Option B is wrong because a fully normalized schema (e.g., snowflake) introduces extra joins that can slow BI queries. Option D is wrong because storing data as JSON arrays in a single table is not suitable for efficient SQL aggregation.

Answer

Store all data in a single table using nested JSON arrays for product and store details

Answer

Create a single wide table with all attributes (product, store, date, sales)

Answer

Use a fully normalized snowflake schema with separate tables for each level of hierarchy

Question 12

An organization wants to centralize cost management across multiple projects. They need to analyze spending trends and set budget alerts. Which combination of services should they use?

Accepted Answer

Export billing data to BigQuery and set up budgets with alert thresholds.. For cost analysis, billing export to BigQuery provides detailed data. Budgets and alerts are set up in the Cloud Billing console and can trigger notifications via Pub/Sub or email.

Answer

Use the Cloud Billing API to retrieve cost data and store in Firestore for querying.

Answer

Export billing data to Cloud Storage and use Cloud Logging for analysis.

Answer

Enable Cloud Billing reports and configure Slack notifications via webhooks.

Question 13

A company is migrating its on-premises PostgreSQL database to Cloud SQL. The database is 2 TB and the migration must have minimal downtime. The source database supports continuous archiving. Which three steps should they take? (Choose THREE.)

Accepted Answer

Perform the cutover by promoting the Cloud SQL instance to primary.. Promoting the Cloud SQL instance to primary is the final step in a migration using continuous replication, which minimizes downtime by allowing the source database to remain operational until the cutover. This approach leverages Cloud SQL's ability to act as a replica that stays synchronized with the on-premises database via continuous archiving, ensuring data consistency with minimal interruption.

Answer

Use pg_dump to export the database and import into Cloud SQL.

Answer

Enable binary logging on the source database.

Question 14

Which Spanner feature allows you to add a new column to an existing table without blocking writes or requiring a rebuild?

Accepted Answer

Online DDL. Online DDL (Data Definition Language) in Spanner allows schema changes such as adding a new column to an existing table without blocking writes or requiring a full table rebuild. This is achieved through a non-blocking, multi-phase schema update process that applies changes in the background while the table remains fully available for reads and writes.

Answer

Optimistic locking

Answer

Interleaved tables

Answer

Schema versioning

Question 15

You are using Cloud Spanner and need to add a new column to an existing table. The table has millions of rows and must remain fully available for reads and writes during the schema change. What is the correct approach?

Accepted Answer

Use gcloud spanner databases ddl update with the ALTER TABLE statement; Spanner applies the change online.. Cloud Spanner supports non-blocking schema changes using DDL statements like ALTER TABLE ... ADD COLUMN. These operations are applied online without locking the table. The --async flag is optional for running the command asynchronously. The statement is executed via gcloud spanner databases ddl update.

Answer

Export the table using Dataflow, add the column locally, then import the data back.

Answer

Use gcloud spanner instances update to modify the table schema.

Answer

Take the instance offline, run the ALTER TABLE, then bring it back online.

Question 16

A team needs to automate the reduction of toil in their operations. Which THREE of the following are valid strategies to reduce toil according to SRE principles?

Accepted Answer

Automating repetitive manual tasks using Cloud Functions. Toil reduction involves automating repetitive manual tasks. Creating self-service tools, automating with Cloud Functions, and using Workflows for orchestration are valid strategies. Limiting toil to 50% of time is a tracking goal. Scaling team size is not a toil reduction strategy.

Answer

Scaling the operations team to handle more manual work

Answer

Setting a toil budget that limits toil to 50% of the team's time

Question 17

A company is using DMS to migrate from MySQL to Cloud SQL. During the full dump phase, the migration is taking longer than expected. Which factor most likely affects the duration of the full dump?

Accepted Answer

Size of the source database. The duration of the full dump phase primarily depends on the size of the database and the network bandwidth. The number of tables has some impact, but size is more significant. CDC lag is relevant during CDC phase, not full dump. Source engine version may affect compatibility but not duration significantly.

Answer

Source database engine version

Answer

Number of tables in the source

Answer

Replication lag during CDC

Question 18

A company needs to migrate a 5 TB Oracle database to Cloud SQL for PostgreSQL. They need to convert schemas and data, and also set up ongoing replication with minimal downtime. Which TWO services/tools should they use? (Choose TWO.)

Accepted Answer

Database Migration Service. Ora2Pg converts schemas; DMS can handle data migration with continuous replication from Oracle (using logical replication) to Cloud SQL.

Answer

Cloud SQL for MySQL

Answer

BigQuery Data Transfer Service

Answer

gcloud bigquery load

Question 19

A Database Engineer is responsible for managing a Cloud SQL for MySQL instance. The engineer needs to ensure that automated backups are retained for 14 days and that point-in-time recovery (PITR) is enabled. Which configuration should the engineer set?

Accepted Answer

Enable automated backups, set backup retention to 14 days, and enable binary logging.. Enabling automated backups with a 14-day retention ensures backup files are kept for the required duration, and enabling binary logging (which is required for PITR) allows point-in-time recovery by replaying transaction logs against a base backup. In Cloud SQL for MySQL, PITR is not automatically enabled with automated backups; binary logging must be explicitly enabled.

Answer

Create scheduled manual backups each day and retain them for 14 days; PITR is not needed because backups are daily.

Answer

Enable automated backups with a retention of 14 days; PITR is automatically enabled.

Answer

Enable automated backups with 14-day retention and set up a cross-region replica for disaster recovery.

Question 20

During a MySQL to Cloud SQL migration using DMS, the migration job fails during the full dump phase with an error indicating 'Access denied for user'. The DMS connection profile to the source was created with a user that has only SELECT privileges. What additional privilege is required for the full dump?

Accepted Answer

RELOAD privilege. DMS full dump uses mysqldump, which requires the RELOAD privilege to flush tables and the LOCK TABLES privilege for consistency. Without RELOAD, the dump cannot proceed.

Answer

INSERT privilege

Answer

SUPER privilege

Answer

CREATE privilege