PDE Storing the Data Practice Test 7 — 15 Questions

Question 1

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?

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

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 2

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 3

A company stores highly sensitive financial data in BigQuery. They need to encrypt certain columns (e.g., credit card numbers) with customer-managed encryption keys (CMEK) at the column level. Which BigQuery feature should they use?

Accepted Answer

BigQuery Data Catalog with policy tags. BigQuery supports column-level encryption with customer-managed encryption keys (CMEK) through column-level encryption using policy tags in BigQuery Data Catalog. You create a policy tag, associate a Cloud KMS key with that policy tag, and apply the policy tag to a column. BigQuery then encrypts that column with the customer-managed key. AEAD functions with Cloud KMS provide application-layer encryption of individual values, but they are not the native BigQuery CMEK feature and should not be described as CMEK.

Answer

Customer-managed encryption keys (CMEK) on the dataset

Answer

VPC Service Controls

Answer

AEAD encryption functions with Cloud KMS

Question 4

A mobile app needs a real-time NoSQL database that supports offline sync and automatic conflict resolution. Which Google Cloud database is best suited?

Accepted Answer

Firestore. Firestore is the correct choice because it is a NoSQL, real-time database with client SDKs that provide offline data persistence and automatic conflict resolution using last-write-wins semantics. Its SDKs synchronize data in the background when connectivity is restored, making it well suited for mobile apps requiring offline-first functionality.

Answer

Cloud SQL

Answer

Cloud Bigtable

Answer

Cloud Spanner

Question 5

A company has a Cloud SQL for PostgreSQL instance and wants to create a read replica to offload read traffic from the primary. They also need to ensure the replica is in a different region for disaster recovery. Which Cloud SQL feature should they use?

Accepted Answer

Cross-region read replica. Cross-region read replicas in Cloud SQL for PostgreSQL allow you to create a replica in a different region from the primary instance. This offloads read traffic from the primary while also providing disaster recovery capabilities by maintaining a standby copy in a geographically separate location. The replica uses asynchronous replication to stay up-to-date with the primary.

Answer

External replica

Answer

Automatic backups with PITR

Answer

High availability (HA) configuration

Question 6

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 you to query data directly from Cloud Storage (e.g., CSV, JSON, Avro, Parquet) without loading it into BigQuery's native storage. This is achieved by creating a table that references the external data source, enabling federated queries and on-demand access. The data remains in Cloud Storage, and BigQuery reads it at query time, which is ideal for one-time analysis or when data is frequently updated externally.

Answer

BI Engine

Answer

Federated queries

Answer

Authorized views

Question 7

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 8

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 physically co-locate child rows with their parent rows on the same split, so a parent-child join can be satisfied with a single local lookup rather than a distributed join across nodes. This is ideal when the child table is frequently queried together with the parent, as in this scenario with millions of parents and billions of children. The interleaving declaration (INTERLEAVE IN PARENT) makes the child's primary key prefix the parent's key, enabling efficient prefix scans and reduced network hops.

Answer

Partitioned tables

Answer

Secondary indexes

Answer

Change streams

Question 9

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 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. VPC Service Controls (E) is correct because it creates a service perimeter around BigQuery that restricts access to only approved VPC networks, blocking requests from outside the perimeter and mitigating data exfiltration. Cloud Audit Logs (A) is correct because it records BigQuery data access and administrative activity (Data Access audit logs must be explicitly enabled), providing the required audit trail. CMEK (B) only controls encryption key ownership, not network-based access or auditing. DLP (C) discovers and classifies sensitive data but does not restrict network access or provide access auditing. IAM roles (D) grant permissions to identities but cannot enforce VPC network origin restrictions on BigQuery.

Answer

Customer-managed encryption keys (CMEK)

Answer

Data Loss Prevention (DLP)

Answer

IAM roles

Question 11

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 12

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 13

A global e-commerce platform requires a relational database that can handle millions of transactions per second across regions with strong consistency and automatic failover. The database must also support SQL joins. Which database should they choose?

Accepted Answer

Cloud Spanner. Cloud Spanner is the correct choice because it is a globally distributed, horizontally scalable relational database that provides strong consistency and automatic failover across regions, while fully supporting SQL joins. It combines the benefits of a traditional relational database with the horizontal scalability of NoSQL systems, making it ideal for high-throughput, globally distributed applications requiring ACID transactions.

Answer

Cloud SQL

Answer

Cloud Firestore

Answer

Cloud Bigtable

Question 14

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

Question 15

A company is migrating an on-premises PostgreSQL database to Google Cloud. The database runs complex analytical queries mixed with OLTP workloads. They need PostgreSQL compatibility and want to improve analytical query performance without changing the application. Which database should they choose?

Accepted Answer

AlloyDB. AlloyDB is the correct choice because it is a fully managed PostgreSQL-compatible database service specifically designed for demanding transactional and analytical workloads. It combines the PostgreSQL ecosystem with a columnar engine and adaptive caching to accelerate analytical queries by up to 100x over standard PostgreSQL, all without requiring application changes. This makes it ideal for mixed OLTP and complex analytical queries while maintaining PostgreSQL compatibility.

Answer

BigQuery

Answer

Cloud Spanner

Answer

Cloud SQL for PostgreSQL