Exam Professional Data Engineer topic 1 question 197 discussion

We exclude [C[ as non ACID and [D] for being invalid (location is configured on Dataset level, not Table). Then, let's focus on "minimal human intervention in case of a failure" requirement in order to eliminate one answer among [A] and [B]. Basically, we have to compare point-in-time recovery with high availability. It doesn't matter whether it's about MySQL or PostgreSQL since both databases support those features. - Point-in-time recovery logs are created automatically, but restoring an instance in case of failure requires manual steps (described here: https://cloud.google.com/sql/docs/mysql/backup-recovery/pitr#perform-pitr) - High availability, in case of failure requires no human intervention: "If an HA-configured instance becomes unresponsive, Cloud SQL automatically switches to serving data from the standby instance." (from https://cloud.google.com/sql/docs/postgres/high-availability#failover-overview) So answer [B] wins.

Its B because of HA cant be A because point in time recovery still requires human intervention

The best option to meet the ACID compliance and minimal human intervention requirements is to configure a Cloud SQL for PostgreSQL instance with high availability enabled. Key reasons: Cloud SQL for PostgreSQL provides full ACID compliance, unlike Bigtable which provides only atomicity and consistency guarantees. Enabling high availability removes the need for manual failover as Cloud SQL will automatically failover to a standby replica if the leader instance goes down. Point-in-time recovery in MySQL requires manual intervention to restore data if needed. BigQuery does not provide transactional guarantees required for an ACID database. Therefore, a Cloud SQL for PostgreSQL instance with high availability meets the ACID and minimal intervention requirements best. The automatic failover will ensure availability and uptime without administrative effort.

I vote for D - BigQuery with multi region configuration. According to https://cloud.google.com/bigquery/docs/introduction , BigQuery support ACID and automatically replicated for high availability. """BigQuery stores data using a columnar storage format that is optimized for analytical queries. BigQuery presents data in tables, rows, and columns and provides full support for database transaction semantics (ACID). BigQuery storage is automatically replicated across multiple locations to provide high availability."""

Option B

Answer B, ACID -compliant database are Spanner and CloudSQL Option A could be the answer if they setup a secondary or failure replicas and auto maintenance window that could trigger in non business hours. Option B, does not explain about extra replica but in postgresql Highavailablity option means the same extra replicas instances are available for emergency.

B. Configure a Cloud SQL for PostgreSQL instance with high availability enabled.

B is the answer. https://cloud.google.com/sql/docs/postgres/high-availability#HA-configuration The purpose of an HA configuration is to reduce downtime when a zone or instance becomes unavailable. This might happen during a zonal outage, or when an instance runs out of memory. With HA, your data continues to be available to client applications.

I voted for B

B it is exact anwer.

Ans B Postgres is highly ACID compliant as compared to Mysql

cloud sql with high availability enabled is enough

B. Configure a Cloud SQL for PostgreSQL instance with high availability enabled.

I voted for B