PDE Storing the Data Practice Test 5 — 15 Questions

Question 1

An e-commerce application uses Cloud SQL (MySQL) for transaction processing. To improve read performance for reporting queries, the team wants to offload read traffic to a separate database instance that stays in sync with the primary. Which Cloud SQL feature should they use?

Choose an answer to begin — your selection is scored in the full session.

Accepted Answer

Create a cross-region read replica. Cloud SQL read replicas are designed to offload read traffic from the primary instance while staying in sync using asynchronous replication. Cross-region read replicas specifically allow you to place a replica in a different region, improving read performance for geographically distributed reporting queries without affecting the primary's transaction processing.

Answer

Configure a high availability (HA) replica

Answer

Use Cloud SQL's failover replica

Answer

Enable automatic backups and point-in-time recovery

Question 2

An organization needs to store transactional data for a global e-commerce platform with strong consistency across regions and an SLA of 99.999% availability. The application requires SQL semantics with horizontal scaling. Which Google Cloud database should they choose?

Accepted Answer

Cloud Spanner. Cloud Spanner is the correct choice because it provides globally distributed, strongly consistent SQL semantics with horizontal scaling and a 99.999% availability SLA. It uses synchronous replication and the TrueTime API to ensure external consistency across regions, meeting the strict consistency and uptime requirements of a global e-commerce platform.

Answer

Firestore

Answer

Cloud SQL

Answer

Cloud Bigtable

Question 3

A data engineer wants to create a data lake on Google Cloud for storing raw streaming data, then transform it into curated and processed zones for analytics. The data is in Avro format and will be queried by BigQuery. Which two services are MOST suitable as the primary storage and query interface?

Accepted Answer

Cloud Storage and BigQuery. Cloud Storage is the most suitable primary storage for a data lake because it provides scalable, durable, and cost-effective object storage for raw Avro data. BigQuery is the ideal query interface because it can directly query Avro files stored in Cloud Storage using external tables, and it supports serverless analytics without needing to manage infrastructure.

Answer

Cloud Storage and Dataproc

Answer

Cloud Storage and Cloud SQL

Answer

Cloud Storage and Firestore

Question 4

An organization uses Cloud Storage to store backup files. They want to automatically delete files older than 90 days, and after deletion, move remaining files to Nearline storage if not accessed for 30 days. Which Cloud Storage feature should they configure?

Accepted Answer

Object Lifecycle Management. Object Lifecycle Management (D) is the correct feature because it allows you to define rules to automatically transition objects to colder storage classes (such as Nearline) after a specified period of inactivity and to delete objects after a set age. In this scenario, a lifecycle rule can be configured to delete objects older than 90 days and, for the remaining objects, move them to Nearline storage if they have not been accessed for 30 days. This fully automates the required data management without manual intervention.

Answer

Object Versioning

Answer

Retention Policies

Answer

Bucket Lock

Question 5

Which BigQuery feature allows you to read data directly from Cloud Storage without loading it into BigQuery storage?

Accepted Answer

External tables. External tables in BigQuery allow querying data stored in Cloud Storage (e.g., CSV, Parquet, ORC) without loading. Authorized views restrict access, federated queries allow querying other databases, and BI Engine is for acceleration.

Answer

BI Engine

Answer

Federated queries

Answer

Authorized views

Question 6

A data engineer needs to design a schema in BigQuery for a dataset that contains customer orders. Each order has a header and multiple line items. Queries frequently need to retrieve the entire order including line items. Which schema design is MOST performant and cost-effective?

Accepted Answer

Use nested and repeated fields (orders table with line items as REPEATED RECORD). BigQuery is optimized for denormalized schemas using nested and repeated fields (REPEATED RECORD). Storing line items as a repeated record within the orders table avoids expensive JOIN operations, reduces data shuffling, and allows BigQuery to scan only the necessary columns, making queries that retrieve entire orders with line items both faster and more cost-effective.

Answer

Store all data in a flat table with repeated order info per line item

Answer

Normalize into separate orders and line_items tables, join on order_id

Answer

Use a partitioned table on order date

Question 7

A company uses Cloud Spanner and needs to store a parent-child relationship where the child table is frequently queried together with the parent. The parent has millions of rows and the child billions. Which Spanner feature optimizes performance for this pattern?

Accepted Answer

Interleaved tables. Interleaved tables in Cloud Spanner co-locate parent and child rows on the same split, reducing cross-node communication when joining. Secondary indexes are for lookups, not co-location. Partitioning is not a Spanner concept.

Answer

Partitioned tables

Answer

Secondary indexes

Answer

Change streams

Question 8

An organization wants to enforce that data in a Cloud Storage bucket cannot be deleted or overwritten for 7 years due to regulatory compliance. Which Cloud Storage feature should they use?

Accepted Answer

Retention Policy with Bucket Lock. Retention Policy with a retention period ensures objects cannot be deleted or overwritten during that period. Bucket Lock makes the policy permanent. Object holds are per-object. Lifecycle management automates transitions/deletions, opposite of retention.

Answer

IAM conditions

Answer

Object Lifecycle Management

Answer

Object holds

Question 9

A company needs a fully managed, PostgreSQL-compatible database that supports both transactional (OLTP) and analytical (OLAP) workloads with low latency. They want to minimize operational overhead. Which two Google Cloud services should they consider? (Choose two.)

Accepted Answer

AlloyDB with BigQuery as a federated source. AlloyDB is a fully managed PostgreSQL-compatible database service designed for both transactional (OLTP) and analytical (OLAP) workloads with low latency. By using BigQuery as a federated source, you can run analytical queries directly against AlloyDB data without moving it, combining operational and analytical capabilities while minimizing operational overhead.

Answer

Cloud SQL for PostgreSQL

Answer

Cloud Spanner

Answer

BigQuery

Question 10

A data engineer needs to restrict access to BigQuery datasets such that only data from approved VPC networks can query them. They also need to audit data access. Which two security controls should they implement? (Choose two.)

Accepted Answer

Cloud Audit Logs. Cloud Audit Logs (option A) is correct because it provides a record of all administrative and data-access operations on BigQuery datasets, enabling the data engineer to audit who accessed what data and from which network. This satisfies the requirement to audit data access by capturing detailed logs of API calls, including the identity of the caller and the source IP address.

Answer

Customer-managed encryption keys (CMEK)

Answer

Data Loss Prevention (DLP)

Answer

IAM roles

Question 11

A company stores data in a Cloud Storage bucket with versioning enabled. They want to automatically delete objects that are noncurrent (i.e., previous versions) after 30 days, and also delete the current version if it is older than 365 days. Which three Object Lifecycle Management conditions can be used together? (Choose three.)

Accepted Answer

age: 365. The `age` condition in Object Lifecycle Management specifies the number of days since object creation, and setting it to 365 will delete the current version when it is older than 365 days. This directly meets the requirement to delete current versions older than a year.

Answer

lastAccessTime: 30

Answer

daysSinceCustomTime: 30

Question 12

A company needs to store petabytes of time-series IoT sensor data and query it with single-digit millisecond latency at millions of reads per second. The data has a simple key-value structure with timestamps. Which Google Cloud database is MOST appropriate?

Accepted Answer

Cloud Bigtable. Cloud Bigtable is a fully managed, scalable NoSQL database designed for large analytical and operational workloads, handling petabytes of data with consistent sub-10ms latency at millions of reads per second. Its key-value storage model and automatic sharding make it ideal for time-series IoT sensor data with simple timestamp-based keys, supporting high-throughput, low-latency access without the overhead of relational features.

Answer

BigQuery

Answer

Cloud Spanner

Answer

Firestore

Question 13

A startup is building a mobile app that needs to sync user data across devices in real time. They expect millions of concurrent users and need a NoSQL database with offline support and automatic multi-region replication. Which Google Cloud service meets these requirements?

Accepted Answer

Firestore. Firestore is a NoSQL, serverless document database that provides real-time synchronization, offline support via local persistence, and automatic multi-region replication. It is designed for mobile and web apps with millions of concurrent users, making it the ideal choice for this use case.

Answer

Cloud Bigtable

Answer

Cloud Spanner

Answer

Cloud SQL

Question 14

A data engineer needs to store quarterly financial data that must remain immutable for 7 years to meet regulatory compliance. The data is accessed infrequently after the first year. Which Cloud Storage feature should be used to enforce immutability?

Accepted Answer

Object Lock with Retention Policy. Object Lock with a Retention Policy enforces immutability by preventing object deletion or modification for a specified duration. For regulatory compliance requiring 7-year immutable storage, a retention policy configured with a retention period of 7 years ensures that objects cannot be overwritten or deleted, even by the root account. This directly meets the requirement for data that must remain unchanged for the full 7-year period.

Answer

Object Lifecycle Rules

Answer

Object Versioning

Answer

IAM Conditions

Question 15

A marketing team needs to run ad-hoc SQL queries on terabytes of clickstream data stored in Parquet files in Cloud Storage. They want a serverless solution with no cluster management and the ability to query external data without loading. Which service should they use?

Accepted Answer

BigQuery with external tables. BigQuery with external tables allows querying data stored in Cloud Storage (including Parquet files) without loading it into BigQuery storage, providing a serverless, fully managed solution with no cluster management. This matches the requirement for ad-hoc SQL queries on terabytes of clickstream data in Parquet format, as BigQuery automatically scales compute and storage.

Answer

Cloud SQL

Answer

AlloyDB

Answer

Dataproc with Spark SQL