PDE Storing the Data — 25 Questions

Question 1

A company needs a fully managed, globally distributed relational database with strong consistency, external consistency, and 99.999% SLA for a financial transaction processing system. Which Google Cloud service should they use?

Accepted Answer

Cloud Spanner. Cloud Spanner is the correct choice because it is a fully managed, globally distributed relational database service that provides strong consistency, external consistency (true serializable transactions across regions), and a 99.999% SLA. These features are essential for a financial transaction processing system that requires ACID compliance and global scalability without sacrificing consistency.

Answer

Firestore

Answer

Bigtable

Answer

Cloud SQL

Question 2

A data engineer needs to store raw sensor data in Cloud Storage and automatically transition it to a lower-cost storage class after 30 days, then delete it after 365 days. What should they configure?

Accepted Answer

Configure a lifecycle rule with SetStorageClass to Nearline after 30 days and Delete after 365 days.. Option C is correct because Cloud Storage lifecycle management rules allow you to automatically transition objects to a lower-cost storage class (such as Nearline) after a specified number of days and then delete them after another period. This is the native, serverless way to manage object lifecycle without external scripts or compute resources.

Answer

Use Cloud Pub/Sub notifications to trigger a Cloud Function that moves objects.

Answer

Use gsutil rewrite command in a cron job.

Answer

Set a bucket retention policy with a retention period of 365 days.

Question 3

An e-commerce company uses Cloud Spanner for order processing. They need to query orders by customer ID and retrieve all order items. Which schema design pattern should they use for optimal performance?

Accepted Answer

Use interleaved tables where Orders is the parent and OrderItems is an interleaved child table with the same primary key prefix.. Interleaved tables in Cloud Spanner physically co-locate parent and child rows on the same split, so querying orders by customer_id and retrieving all order items becomes a single, fast key-range scan without cross-node joins. This design exploits Spanner's hierarchical storage model to minimize latency and maximize throughput for this access pattern.

Answer

Store all data in a single table with nullable columns for order item attributes.

Answer

Denormalize by storing order items as a repeated field in the orders table.

Answer

Create two separate tables with a secondary index on customer_id in the orders table and a secondary index on order_id in the order_items table.

Question 4

A data engineer is building a data lake on Google Cloud and needs to separate raw ingested data, curated/cleaned data, and processed/aggregated data. Which Cloud Storage bucket structure is recommended?

Accepted Answer

Create three separate folders in a single bucket: raw, curated, processed.. A common best practice for data lakes on GCS is to use separate buckets or folders within a bucket (e.g., raw, curated, processed) to manage different stages of data refinement and apply appropriate lifecycle policies.

Answer

Store all data in one bucket and use object labels to distinguish raw, curated, and processed.

Answer

Store raw data in a different project for security isolation.

Answer

Use different storage classes for raw, curated, and processed data within the same bucket.

Question 5

A mobile app needs an offline-first NoSQL database that syncs data across devices when connectivity is available. Which Google Cloud database meets these requirements?

Accepted Answer

Firestore. Firestore is a NoSQL, serverless, offline-first database that automatically syncs data across devices when connectivity is restored. It provides built-in offline persistence and real-time synchronization, making it ideal for mobile apps that need to work offline and sync later.

Answer

Memorystore

Answer

Cloud SQL

Answer

Bigtable

Question 6

A company stores sensitive data in BigQuery and needs to encrypt certain columns with customer-managed encryption keys (CMEK) while using BigQuery's analytics capabilities. What should they do?

Accepted Answer

Use the AEAD encryption functions in BigQuery to encrypt specific columns during query time.. Option D is correct because BigQuery's AEAD encryption functions allow you to encrypt specific columns at query time using customer-managed keys, while still leveraging BigQuery's full analytics capabilities on the unencrypted portions of the data. This approach meets the requirement of encrypting certain columns with CMEK without losing the ability to run analytical queries on the rest of the table.

Answer

Store the sensitive data in Cloud Storage with CMEK and use external tables.

Answer

Use BigQuery column-level security with data classification.

Answer

Create a BigQuery table with CMEK enabled; it will automatically encrypt all columns.

Question 7

An organization needs to prevent data exfiltration from BigQuery by ensuring all traffic to BigQuery APIs goes through VPC boundaries and is restricted to a specific service perimeter. Which Google Cloud security control should they use?

Accepted Answer

VPC Service Controls. VPC Service Controls (D) is the correct answer because it allows you to define a service perimeter around BigQuery APIs, ensuring that all traffic to BigQuery must originate from within the defined VPC boundaries. This prevents data exfiltration by blocking unauthorized access from outside the perimeter, even if valid credentials are used. It works by enforcing context-aware access policies at the Google Cloud network edge, not at the application layer.

Answer

Access Transparency

Answer

IAM conditions on BigQuery roles

Answer

Cloud Armor

Question 8

A data engineer needs to design a Bigtable row key for a time-series IoT application where each device sends data every second. The query pattern is to retrieve all data for a specific device over a time range. Which row key design minimizes hotspots?

Accepted Answer

hash(device_id)#timestamp (e.g., a3f2#2024-03-15-10:30:00). To avoid hotspots (where all writes hit a single tablet server), the row key should start with a hash of the device ID to distribute writes across the cluster, then append the timestamp for range scans.

Answer

device_id#timestamp (e.g., device123#2024-03-15-10:30:00)

Answer

timestamp#device_id (e.g., 2024-03-15-10:30:00#device123)

Answer

device_type#device_id#timestamp

Question 9

A company wants to use BigQuery to query data stored in Parquet files in Cloud Storage without loading the data into BigQuery. Which BigQuery feature should they use?

Accepted Answer

BigQuery external tables. BigQuery external tables allow querying data stored in Cloud Storage (including Parquet files) directly without loading it into BigQuery storage. This feature uses a federated query engine that reads the data on the fly, supporting formats like Parquet, Avro, ORC, CSV, and JSON. Option C is correct because it directly addresses the requirement to query Parquet files in Cloud Storage without ingestion.

Answer

BigQuery Omni

Answer

BigQuery ML

Answer

BigQuery BI Engine

Question 10

A Cloud SQL instance for PostgreSQL is experiencing heavy read traffic. The team wants to offload read queries while maintaining data consistency. Which solution meets their needs?

Accepted Answer

Create read replicas and direct read queries to them.. Read replicas in Cloud SQL for PostgreSQL allow you to offload read traffic from the primary instance by creating one or more asynchronous replicas that serve read queries. This maintains data consistency because replicas use PostgreSQL's native streaming replication, ensuring that all committed transactions on the primary are eventually reflected on the replica, providing a consistent snapshot for read operations.

Answer

Increase the tier of the Cloud SQL instance.

Answer

Create a Cloud SQL failover replica.

Answer

Use Cloud Memorystore as a cache in front of Cloud SQL.

Question 11

A company needs to store logs in Cloud Storage for compliance, with a requirement that logs cannot be deleted or overwritten for a period of 7 years. Which Cloud Storage feature should they enable?

Accepted Answer

Bucket Lock with a retention policy of 7 years.. Bucket Lock with a retention policy of 7 years is the correct feature because it enforces a WORM (Write Once, Read Many) model on the bucket. Once a retention policy is locked, objects cannot be deleted or overwritten until the retention period expires, meeting the compliance requirement for immutable log storage.

Answer

Requester Pays bucket setting.

Answer

Versioning enabled with Object Hold.

Answer

Object lifecycle management with a delete rule after 7 years.

Question 12

A data team needs to run complex analytical queries on a dataset that is frequently updated with new rows. They want to minimize query costs and avoid scanning old data that is rarely queried. Which BigQuery feature should they use?

Accepted Answer

Partitioned tables with partition expiration. Partitioned tables with partition expiration allow you to divide a table into segments based on a date/timestamp column, and automatically delete partitions that are older than a specified duration. This minimizes query costs by only scanning relevant partitions and eliminates storage costs for old, rarely queried data without manual intervention.

Answer

BigQuery materialized views

Answer

Clustered tables

Answer

BigQuery BI Engine

Question 13

A company is migrating an on-premises PostgreSQL database to Google Cloud. They need a fully managed database that is compatible with PostgreSQL and can handle both transactional and analytical workloads with high performance. Which two database services meet these requirements? (Choose TWO.)

Accepted Answer

Cloud SQL for PostgreSQL. Cloud SQL for PostgreSQL is a fully managed database service that is compatible with PostgreSQL, making it suitable for transactional workloads. AlloyDB is also a fully managed PostgreSQL-compatible database that is optimized for high performance on both transactional and analytical workloads, offering up to 100x faster query performance for analytical queries compared to standard PostgreSQL.

Answer

Cloud Spanner

Answer

BigQuery

Answer

Firestore

Question 14

A data engineer needs to create a unified table that combines data from Cloud Storage (Parquet files) and BigQuery native tables, with fine-grained access control and governance. Which three Google Cloud features should they use together? (Choose THREE.)

Accepted Answer

BigQuery. BigQuery is correct because it serves as the unified query engine that can read data from both Cloud Storage (via external tables or BigLake) and native BigQuery tables, enabling a single SQL interface for analysis. It also integrates with fine-grained access control through row-level security and column-level access policies, and supports governance via Data Catalog and VPC Service Controls.

Answer

Dataproc

Answer

Cloud SQL

Question 15

A company needs to store and analyze large amounts of unstructured data (images, videos) and structured data (CSV logs) in a cost-effective manner. The data should be accessible for analytics with BigQuery. Which two services should they use? (Choose TWO.)

Accepted Answer

BigQuery. Cloud Storage is the best option for storing unstructured and structured files cost-effectively. BigQuery can analyze this data directly via external tables or after loading, making it a powerful analytics platform.

Answer

Cloud SQL

Answer

Cloud Spanner

Answer

Firestore

Question 16

A team needs to store transactional data for an e-commerce application that requires ACID transactions, automatic backups, and point-in-time recovery. The expected workload is under 10,000 QPS. Which database should they choose?

Accepted Answer

Cloud SQL. Cloud SQL is the correct choice because it provides fully managed relational databases (MySQL, PostgreSQL, SQL Server) with built-in ACID transaction support, automated backups, and point-in-time recovery (PITR) via binary logs or write-ahead logs. The workload of under 10,000 QPS is well within Cloud SQL's performance envelope, making it a cost-effective and operationally simple solution for transactional e-commerce data.

Answer

Cloud Bigtable

Answer

Firestore

Answer

Cloud Spanner

Question 17

A company wants to store backups of on-premises databases in Google Cloud for long-term retention. They need WORM (Write Once, Read Many) compliance and object-level retention policies. What should they use?

Accepted Answer

Cloud Storage with Object Lock. Cloud Storage with Object Lock provides WORM (Write Once, Read Many) compliance by preventing objects from being deleted or overwritten for a fixed retention period. It supports both retention policies (applied at the bucket level) and legal holds (applied at the object level), meeting the requirement for object-level retention policies. This makes it the correct choice for long-term backup retention with regulatory compliance needs.

Answer

Firestore backups

Answer

BigQuery table snapshots

Answer

Cloud Storage with retention policies

Question 18

An application requires a globally distributed, strongly consistent database with 99.999% availability SLA. The workload is OLTP with high throughput across continents. Which service fits best?

Accepted Answer

Cloud Spanner. Cloud Spanner is the only service that provides globally distributed, strongly consistent (external consistency via TrueTime) OLTP with 99.999% availability SLA. It supports high-throughput ACID transactions across continents using synchronous replication and atomic clocks, meeting all stated requirements.

Answer

Cloud SQL with cross-region replicas

Answer

Firestore

Answer

Cloud Bigtable

Question 19

A data engineer is designing a Bigtable row key for a time-series application that records temperature sensor readings every second. To avoid hotspotting, they want to distribute writes across all nodes. Which row key design is best?

Accepted Answer

[hash of sensor_id]#[timestamp]. Hotspotting occurs when sequential keys hit a single tablet server. A reversed timestamp or hashed prefix distributes writes. Pre-pending a hash ensures even distribution. Timestamp alone causes hotspotting. Sensor ID + timestamp can still be sequential if sensor ID is low cardinality.

Answer

[timestamp reversed]#[sensor_id]

Answer

[sensor_id]#[timestamp]

Answer

[timestamp]#[sensor_id]

Question 20

A company wants to run complex analytical queries on structured data without managing infrastructure. The data volume is terabytes and queries can take seconds to minutes. Which service is appropriate?

Accepted Answer

BigQuery. BigQuery is correct because it is a serverless, highly scalable data warehouse designed for running complex analytical queries on terabytes of data with fast query performance (seconds to minutes) without any infrastructure management. It uses a columnar storage format and a distributed query engine to handle large-scale structured data efficiently, making it ideal for this use case.

Answer

Firestore

Answer

Cloud Bigtable

Answer

Cloud SQL