Free CLOUD-DATABASE-ENGINEER Practice Test

The core issue is that the terraform destroy command, which is part of the environment teardown process, is deleting the Cloud SQL instance. The deletionprotection argument within the Terraform googlesqldatabaseinstance resource is specifically designed to prevent this. When set to true, any attempt by Terraform to destroy the instance will fail, thus protecting the persistent database layer from being lost during automated environment recreation cycles. This directly addresses the requirement to prevent the database from being dropped.

B. Rerun terraform apply: This command creates or updates resources based on the configuration. It does not prevent a resource from being deleted by a terraform destroy command.
C. Create a read replica: A read replica is for scaling read traffic or for high availability. It does not prevent the primary instance from being deleted by Terraform.
D. Use point-in-time-recovery (PITR) to recover the database: PITR is a reactive measure used to restore a database after it has been deleted or corrupted. The goal is to proactively prevent deletion. ---

1. Terraform Documentation for the Google Provider: The documentation for the googlesqldatabaseinstance resource explicitly lists the deletionprotection argument. It states, "Used to block Terraform from deleting a SQL Instance." This is the direct mechanism to solve the problem described.

Source: Terraform Registry, Google Provider, googlesqldatabaseinstance resource documentation, deletionprotection argument description.

2. Google Cloud SQL Documentation: The underlying feature in Google Cloud that Terraform manages is "Deletion protection." The official documentation explains that this setting protects an instance from accidental deletion. When enabled, you cannot delete the instance until the protection is disabled.

Source: Google Cloud Documentation, Cloud SQL > Documentation > How-to guides > Edit an instance, Section: "Enable and disable deletion protection".

3. Google Cloud SQL Documentation: The instance settings documentation further clarifies the purpose of deletion protection, confirming it's a safeguard for production instances.

Source: Google Cloud Documentation, Cloud SQL > Documentation > Concepts > Instances, Section: "Instance settings".

Cloud Spanner is the only Google Cloud database that satisfies all the stated requirements. It is designed as a global, horizontally scalable, and strongly consistent relational database. It provides full ACID compliance for transactions with external consistency, which is critical for transactional applications. Furthermore, a multi-region Cloud Spanner configuration offers a 99.999% availability Service Level Agreement (SLA), meeting the stringent uptime requirement. Its ability to handle global traffic with low latency and strong consistency makes it the most suitable choice.

A. Use Firestore. Firestore is a NoSQL document database. While it offers a 99.999% SLA in multi-region mode and supports transactions, Cloud Spanner's relational structure and global external consistency are better suited for this use case.
C. Use Cloud SQL. Cloud SQL is a regional service and its high availability configuration only provides a 99.95% SLA, which falls short of the 99.999% requirement. It is not designed for global scale.
D. Use Bigtable. Bigtable is a NoSQL wide-column store that does not support multi-row ACID transactions, a fundamental requirement for the described transactional application. ---

1. Cloud Spanner Documentation - "What is Cloud Spanner?": "Spanner is a distributed SQL database management and storage service. [...] It is a fully managed service that provides relational semantics, ACID transactions, schemas, SQL, and automatic, synchronous replication for high availability."

Source: Google Cloud Documentation, "What is Cloud Spanner?".

2. Cloud Spanner Service Level Agreement (SLA): "For a multi-region instance configuration, the Monthly Uptime Percentage is ≥99.999%."

Source: Google Cloud Legal, "Cloud Spanner Service Level Agreement (SLA)".

3. Cloud SQL Service Level Agreement (SLA): "For a High Availability configuration of a Cloud SQL instance... the Monthly Uptime Percentage is ≥99.95%."

Source: Google Cloud Legal, "Cloud SQL Service Level Agreement (SLA)".

4. Cloud Bigtable Documentation - "About Cloud Bigtable": "Bigtable is not a relational database and it does not support SQL queries or multi-row transactions."

Source: Google Cloud Documentation, "About Cloud Bigtable".

5. Google Cloud Documentation - "Choosing a storage option": This guide positions Cloud Spanner for "Global-scale, strongly consistent, relational database service" and highlights its use for "high availability with strong consistency."

Source: Google Cloud Documentation, "Choosing a storage option".

Cloud Spanner is the ideal choice for applications requiring high availability, strong consistency, and seamless scalability from regional to global. Starting with a regional configuration is a cost-effective approach for the initial pilot phase. This configuration provides a 99.99% availability SLA and supports zero-downtime maintenance and schema updates, fulfilling the 24/7 operational requirement. As the application expands globally, the Spanner instance can be seamlessly moved or reconfigured to a multi-region setup without requiring a complex migration, thus providing a clear and efficient growth path.

B. A multi-region configuration is premature and not cost-effective for the initial pilot, which is described as small and in one country.
C. Cloud SQL requires downtime for certain maintenance operations, such as major version upgrades, violating the minimal downtime requirement. It also doesn't scale writes horizontally for a global user base.
D. Highly available Cloud SQL is a regional solution. It does not address the future requirement to expand to a global audience and has the same maintenance downtime limitations as other Cloud SQL configurations.

1. Cloud Spanner Documentation - When to use Cloud Spanner: "Spanner is a good choice for... applications that have a global user base or have high availability requirements... Spanner offers up to 99.999% availability for multi-region instances, with no maintenance windows." This supports the choice of Spanner for high availability and global scale. The strategy starts regional and can grow to multi-region.

Source: Google Cloud Documentation, "When to use Cloud Spanner".

2. Cloud Spanner Documentation - Instances: "A regional configuration stores a full copy of your data in two zones within a single region... This configuration offers an availability of 99.99%." This validates that a regional start meets high availability needs for the pilot.

Source: Google Cloud Documentation, "Instances and configurations".

3. Cloud Spanner Documentation - Schema updates: "You can add or drop a column, add or drop an index, and add, drop, or alter a table without taking the database offline... Spanner schema updates do not require any downtime." This directly addresses the "minimal downtime for maintenance" requirement.

Source: Google Cloud Documentation, "Schema updates".

4. Cloud SQL Documentation - High Availability: "During a maintenance window, Cloud SQL HA instances are also subject to downtime." This confirms that Cloud SQL does not meet the minimal downtime requirement, making options C and D incorrect.

Source: Google Cloud Documentation, "High availability configuration".

The scenario presents two distinct problems: slow report performance on a single read replica and high replication lag. The chosen solutions must address both issues.
B. Create additional read replicas, and partition your analytics users to use different read replicas: This approach directly addresses the slow report performance. The slowness is likely due to resource contention caused by multiple users running analytics workloads on a single replica. By adding more replicas and distributing the load, each replica has more resources (CPU, I/O) available for individual queries, improving performance. This is a standard horizontal scaling strategy for read-intensive workloads.
C. Disable replication on the read replica, and set the flag for parallel replication on the read replica. Re-enable replication...: This approach directly addresses the replication lag. By default, MySQL replication is single-threaded. If the primary instance handles many concurrent writes, the replica can fall behind. Enabling parallel replication (using the slaveparallelworkers or replicaparallelworkers flag) allows the replica to apply transactions in parallel, significantly increasing throughput and reducing lag.

A. Create secondary indexes on the replica. In Cloud SQL, a read replica's schema is an exact copy of the primary's. You cannot add indexes directly to the replica; they must be added to the primary instance first.
D. Disable replication on the primary instance, and set the flag for parallel replication on the primary instance... This is technically incorrect. Parallel replication is a feature configured on the replica to speed up the application of logs, not on the primary instance where the logs are generated.
E. Move your analytics workloads to BigQuery, and set up a streaming pipeline to move data and update BigQuery. This would require rewriting the reports to query BigQuery instead of Cloud SQL, which violates the explicit constraint "without making changes to the current reports."

1. Google Cloud Documentation - Tips for working with read replicas: Under the section "Improve replication performance," the documentation explicitly states, "To improve replication performance, you can enable parallel replication on replicas before you create them." This directly supports option C as a method to reduce lag.

2. Google Cloud Documentation - About read replicas: The documentation states, "You can use read replicas to scale reads for read-heavy workloads." This supports the principle behind option B, which is to scale out the read capacity by adding more replicas to handle the analytics load.

3. Google Cloud Documentation - Managing read replicas: This page clarifies a key limitation: "You cannot make any changes to a read replica's schema. Any schema changes you want to make must be made to the primary instance." This directly refutes the feasibility of option A.

4. Google Cloud Documentation - Supported flags for Cloud SQL for MySQL: This document lists slaveparallelworkers as a supported flag that can be configured for a Cloud SQL instance, confirming the technical mechanism mentioned in option C is available.

Database Migration Service (DMS) for PostgreSQL relies on logical replication to perform continuous, minimal-downtime migrations. For this mechanism to function correctly with an on-premises source, two key prerequisites must be met. First, the pglogical extension must be installed on the source database. DMS uses this extension to capture and stream data changes to the Cloud SQL destination. Second, every table intended for migration must have a primary key. Logical replication requires a primary key to uniquely identify rows for replicating UPDATE and DELETE operations accurately. Tables lacking a primary key will not be migrated by DMS.

A. Drop or disable all users except database administration users. This is not a DMS prerequisite. DMS only requires a specific user with sufficient replication privileges; other application users can remain active.
B. Disable all foreign key constraints on the source PostgreSQL database. This is incorrect and would compromise data integrity on the source. DMS is designed to handle data with existing constraints.
D. Shut down the database before the Data Migration Service task is started. This is incorrect. DMS is designed for minimal-downtime migrations and requires the source database to be online to capture ongoing changes (CDC).

1. Google Cloud Documentation: Prepare a source PostgreSQL database.

Under the "Prerequisites" section, it explicitly states: "Install the pglogical extension on your source database... Database Migration Service uses the pglogical extension to connect to the source database and stream changes to the Cloud SQL destination database." This supports option E.

The same section also states: "All tables in the database that you want to migrate must have a primary key." This supports option C.

2. Google Cloud Documentation: Known limitations | Database Migration Service.

Under the "General limitations" section, it lists: "Tables without a primary key aren't migrated." This further validates option C as a critical prerequisite.

The most effective way to perform Cloud SQL maintenance without impacting users is to schedule it during a time of guaranteed inactivity. The scenario specifies that the application is used from 6 AM to 10 PM, leaving a clear eight-hour window (10 PM to 6 AM) for maintenance. Cloud SQL's maintenance window feature allows you to specify a preferred day and hour for maintenance activities. By configuring the window during this off-peak period, any brief downtime will not affect users. Additionally, controlling the update order by updating non-production instances first is a best practice for validating updates before they are applied to the production environment.

B. Read replicas are used for scaling read-heavy workloads and do not provide high availability or reduce maintenance downtime for the primary instance's write operations.
C. While notifications and rescheduling are useful features, they are reactive. A pre-configured maintenance window is a proactive and more reliable strategy to consistently avoid user impact.
D. High availability (HA) significantly minimizes downtime to a brief failover period (typically under 60 seconds), but it does not eliminate it. Scheduling maintenance when no users are active completely avoids any user-facing impact.

1. Google Cloud Documentation - About maintenance on Cloud SQL instances: This document details the maintenance window feature. It states, "By configuring a maintenance window, you can control when downtime for maintenance occurs... We recommend that you set maintenance windows for times when your instance has the lowest traffic." It also describes the "Maintenance order" setting, which supports the second part of the correct answer.

Source: https://cloud.google.com/sql/docs/mysql/maintenance, Sections: "Set a preferred maintenance window" and "Set maintenance order".

2. Google Cloud Documentation - High availability for Cloud SQL: This source explains how HA works during maintenance. It clarifies that HA minimizes downtime, not eliminates it: "During a maintenance window, Cloud SQL first applies the maintenance update to the standby instance. If the update is successful, it initiates a failover... This process minimizes the downtime for your application." This confirms that a brief interruption still occurs.

Source: https://cloud.google.com/sql/docs/mysql/high-availability, Section: "Maintenance".

3. Google Cloud Documentation - Replication in Cloud SQL: This document explains the purpose of read replicas, confirming they are for offloading read workloads and do not serve as a failover target for the primary instance during maintenance.

Source: https://cloud.google.com/sql/docs/mysql/replication/overview, Section: "Read replica use cases".

This solution is the most cost-effective and operationally efficient. Cloud Scheduler is a fully managed cron service designed for triggering jobs on a schedule, directly replacing the on-premises cron functionality. Cloud Functions provides a serverless compute environment, meaning you only pay for the brief execution time of the export script. This serverless combination eliminates the need to manage and pay for a constantly running virtual machine, minimizing both cost and operational overhead, which directly aligns with the requirements.

B. A streaming Dataflow job is designed for continuous, real-time data processing, not for a scheduled daily batch export. This is the wrong tool and would be unnecessarily complex and costly.
C. Cloud Composer is a powerful workflow orchestration tool for complex, multi-step data pipelines (DAGs). Using it for a single, simple daily task is excessive and violates the principle of minimizing cost and overhead.
D. Using a Compute Engine instance replicates the on-premises model, requiring a virtual machine to run 24/7. This incurs unnecessary costs and operational overhead for patching, security, and management.

1. Cloud Scheduler and Cloud Functions Pattern: The official Google Cloud documentation provides a tutorial for this exact use case. It states, "This page shows how to use Cloud Scheduler to trigger a Cloud Function on a schedule." This pattern is recommended for running background jobs reliably.

Source: Google Cloud Documentation, "Schedule a Cloud Function to run with Cloud Scheduler".

2. Cloud Functions for Cost-Effectiveness: The Cloud Functions documentation highlights its serverless nature. "With Cloud Functions, you write simple, single-purpose functions... and you're only billed for the time your code runs, metered to the nearest 100 milliseconds." This directly supports the "minimize cost" requirement.

Source: Google Cloud Documentation, "What is Cloud Functions?".

3. Cloud SQL Export API: The Cloud Function would call the Cloud SQL Admin API to perform the export. This is a standard, supported method for programmatically managing database operations.

Source: Google Cloud Documentation, "Cloud SQL Admin API v1beta4, instances: export".

4. Cloud Composer Use Case: The Cloud Composer documentation clarifies its purpose: "Cloud Composer is a fully managed workflow orchestration service... for creating, scheduling, monitoring, and managing workflows that span across clouds and on-premises data centers." This indicates it is for more complex scenarios than a single export task.

Source: Google Cloud Documentation, "Cloud Composer overview".

Creating an external replica is the Google-recommended practice for migrating a MySQL database to Cloud SQL with minimal downtime. This method involves configuring the on-premises database as the source and the new Cloud SQL instance as the replica. Data is continuously synchronized while the source application remains online. Once the replica is caught up, a brief cutover window is used to promote the Cloud SQL instance to a primary and redirect application traffic. This process significantly reduces the downtime compared to offline methods like dump and import, and it moves the database to a fully managed service, which addresses the customer's concerns about backup and maintenance overhead.

A. Creating a GKE cluster to run MySQL is a self-managed solution that increases operational complexity and does not reduce the maintenance overhead as effectively as a managed service.
B. Using the mysqldump utility constitutes an offline migration. This requires taking the application offline for the duration of the data dump and import, which does not meet the minimal downtime requirement.
C. Installing MySQL on a Compute Engine VM is another self-managed approach. It shifts the maintenance burden to the cloud but does not eliminate it, contrary to the goal of reducing overhead. ---

1. Google Cloud Documentation, "Migrating data from an external server to Cloud SQL": This document explicitly outlines the process for minimal downtime migration. It states, "To migrate your data from an external MySQL server to Cloud SQL with minimal downtime, you can set up your external server as the source for replication to a Cloud SQL replica." This directly supports the chosen answer (D).

2. Google Cloud Documentation, "Migration strategies for MySQL": This page compares different migration strategies. It categorizes the "External replica" method under "Continuous replication for minimal downtime migrations," while categorizing "Export and import files" (using mysqldump) as a method with higher downtime. This confirms that option D is superior to B for the stated requirement.

3. Google Cloud Documentation, "About importing and exporting data": This page describes the dump file method. It notes, "Importing data into Cloud SQL overwrites all existing data in the target database." This implies the database is unavailable for writes during the import, confirming that this method incurs significant downtime, making option B incorrect.

4. Google Cloud Documentation, "Cloud SQL for MySQL features": This page details the benefits of Cloud SQL as a fully managed service, including "Automated backups" and "Automated maintenance." This supports the reasoning that moving to Cloud SQL (as in option D) reduces maintenance overhead, while self-managing on GKE or Compute Engine (options A and C) does not.

The scenario describes a classic Internet of Things (IoT) time-series use case with extremely high write throughput (100,000 sensors 10 readings/sec = 1,000,000 writes/sec). Bigtable is a fully managed, scalable NoSQL wide-column database designed for large analytical and operational workloads with very low latency. It excels at ingesting massive volumes of time-series data and serving it for real-time monitoring dashboards that require single-digit millisecond read latency. Its flexible schema is also ideal for the semi-structured nature of sensor data. This makes Bigtable the most appropriate choice to meet all the requirements of high-throughput ingestion, real-time access, and support for semi-structured data.

A. Deploy the database using Cloud SQL. Cloud SQL is a relational database service (OLTP) not designed for the massive write throughput (1M ops/sec) and semi-structured data model required by this IoT use case.
B. Use BigQuery, and load data in batches. Loading data in batches is inherently not real-time and introduces significant latency, which contradicts the "real-time monitoring" requirement. BigQuery is an analytical data warehouse, not an operational database for low-latency point reads.
D. Deploy the database using Cloud Spanner. Cloud Spanner is a globally distributed, strongly consistent relational database. It is not the optimal or most cost-effective solution for this specific high-volume, time-series ingestion workload, which typically does not require Spanner's strong transactional consistency guarantees. ---

1. Google Cloud Documentation - Bigtable Use Cases: "Bigtable is ideal for applications that need to handle large amounts of data with very low latency... Common use cases include... Time series data, such as CPU and memory usage over time for multiple servers... Internet of Things (IoT) data, such as energy consumption reports from smart meters and home appliances." This directly aligns with the question's scenario.

Source: Google Cloud Documentation, "About Bigtable," Use cases section.

2. Google Cloud Documentation - Schema Design for Time Series Data: "Bigtable is a good choice for storing and tracking time-series data... A time-series schema in Bigtable is a design that optimizes for time-series data." This document provides specific guidance on using Bigtable for IoT sensor data, confirming it as the purpose-built solution.

Source: Google Cloud Documentation, "Bigtable Schema Design for Time Series Data."

3. Google Cloud Solutions - Architecture for IoT on Google Cloud: Reference architectures for IoT frequently depict a pattern where streaming data is ingested into Bigtable for real-time operational dashboards and simultaneously into BigQuery for long-term analytics. This highlights Bigtable's primary role in the real-time monitoring component.

Source: Google Cloud Solutions, "Connected factory on Google Cloud," Architecture diagram and component descriptions.

4. Google Cloud Documentation - BigQuery Data Loading: "Batch loading involves loading source data into a BigQuery table in a single batch operation... For time-sensitive data, you can stream data into BigQuery one record at a time." The option explicitly states "load data in batches," which is not a real-time method.

Source: Google Cloud Documentation, "Introduction to loading data from files."

This solution uses two fully-managed Google Cloud services to create a robust, near-real time replication pipeline. Datastream is a serverless Change Data Capture (CDC) service that can connect to the private IP Cloud SQL for MySQL instance and stream data changes to Cloud Storage. A pre-built Dataflow template can then read this change stream from Cloud Storage and efficiently write the updates to the specified BigQuery tables. This architecture is designed for low-latency, reliable data replication and is the recommended Google-managed pattern for this specific use case.

A. Developing a custom service contradicts the requirement to use Google-managed services and introduces unnecessary operational overhead and complexity.
B. Cloud SQL federated queries allow BigQuery to query data directly in Cloud SQL but do not replicate or move the data into BigQuery, which is a key requirement for analytics and ML performance.
C. Database Migration Service (DMS) is designed for migrating and replicating databases to other managed relational databases like Cloud SQL, AlloyDB, or Cloud Spanner, not to a data warehouse like BigQuery.

1. Datastream and Dataflow for BigQuery Replication: Google Cloud Documentation, "Replicate data to BigQuery". This page explicitly describes the recommended pattern: "Datastream uses the BigQuery Change Data Capture (CDC) functionality and Dataflow to replicate data and schema updates from a source database directly into BigQuery."

2. Datastream Overview: Google Cloud Documentation, "Datastream overview". This document identifies Datastream as Google's "serverless change data capture (CDC) and replication service" and lists BigQuery as a primary destination, reinforcing its suitability for this task.

3. Cloud SQL Federated Queries: Google Cloud Documentation, "Query Cloud SQL data". The documentation states, "BigQuery Cloud SQL federation enables BigQuery to query data residing in Cloud SQL in real time, without copying or moving data." This confirms it is for querying, not replication.

4. Database Migration Service Overview: Google Cloud Documentation, "Database Migration Service overview". The "Supported source and destination databases" section lists the valid destinations for DMS, which include Cloud SQL, AlloyDB, and Cloud Spanner, but not BigQuery.