PDE Storing the Data Practice Test 2 — 15 Questions

Question 1

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 2

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 3

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 4

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 5

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

Question 6

A team wants to use Cloud Storage to build a data lake with separate zones for raw, curated, and processed data. They need to automatically move objects older than 30 days from the raw zone to a cheaper storage class. How can they achieve this?

Accepted Answer

Configure a Cloud Storage object lifecycle rule with SetStorageClass action. Option D is correct because Cloud Storage object lifecycle management rules can automatically transition objects from one storage class to a cheaper one (e.g., from Standard to Nearline or Coldline) based on age. By configuring a rule with the `SetStorageClass` action and a `Condition` of `age: 30`, objects in the raw zone bucket older than 30 days are moved to a lower-cost class without manual intervention or additional compute services.

Answer

Set a bucket retention policy that forces deletion after 30 days

Answer

Write a Cloud Function to delete objects older than 30 days

Answer

Use gsutil rsync to move objects between buckets

Question 7

A company uses BigQuery for analytics and needs to ensure that certain columns containing PII are encrypted at query time so that only authorized users can decrypt. What should they use?

Accepted Answer

BigQuery AEAD encryption functions. BigQuery AEAD encryption functions allow you to encrypt sensitive columns (e.g., PII) at query time using a user-managed key, so that only authorized users who possess the key can decrypt the data. This is the correct approach because it provides column-level, application-layer encryption that is transparent to the query engine and ensures that unauthorized users see only ciphertext.

Answer

VPC Service Controls

Answer

Customer-managed encryption keys (CMEK)

Answer

Fine-grained IAM roles

Question 8

A data engineer wants to create a BigQuery external table that queries data stored in Parquet format in Cloud Storage without loading the data into BigQuery. Which approach is correct?

Accepted Answer

Create an external table definition using a JSON schema file referencing Cloud Storage URIs. Option B is correct because creating an external table definition with a JSON schema file referencing Cloud Storage URIs allows BigQuery to query Parquet data directly from Cloud Storage without loading it. BigQuery natively supports Parquet as an external data source, and the schema can be auto-detected or explicitly defined via a JSON file. This approach avoids data ingestion costs and keeps the data in its original location.

Answer

Use Cloud SQL federated query to read Parquet from GCS

Answer

Use the bq load command with --source_format=PARQUET

Answer

Set up BigLake to create a BigQuery external table

Question 9

A mobile app needs a NoSQL database that supports offline synchronization when the device goes offline and later reconnects. Which Google Cloud database should be used?

Accepted Answer

Firestore. Firestore provides offline persistence for mobile and web apps. It caches data locally and syncs when online. Cloud SQL and Spanner are relational; Bigtable does not have offline sync.

Answer

Cloud Bigtable

Answer

Cloud Spanner

Answer

Cloud SQL

Question 10

An organization needs to restrict access to BigQuery and Cloud Storage so that data can only be accessed from within a specific VPC network and cannot be exfiltrated. Which Google Cloud feature should they use?

Accepted Answer

VPC Service Controls. VPC Service Controls (option B) is the correct choice because it creates a security perimeter around Google Cloud services like BigQuery and Cloud Storage, preventing data exfiltration even from within a VPC. It enforces context-aware access based on the VPC network, ensuring data can only be accessed from authorized VPC sources and blocking unauthorized transfers outside the perimeter.

Answer

Private Service Access

Answer

VPC firewall rules

Answer

IAM conditions

Question 11

A company uses Cloud Spanner for a global e-commerce platform. They have a table of orders and a table of order items. To optimize performance for queries that join these tables on order_id, which Spanner schema design feature should they use?

Accepted Answer

Use interleaved tables with order_items as a child table of orders. Interleaved tables store child rows physically with parent rows, reducing join latency. Secondary indexes are for filtering. Partitioned tables not in Spanner. Denormalization could help but interleaved tables are the designed approach.

Answer

Use Cloud Bigtable instead

Answer

Create a secondary index on order_id

Answer

Denormalize the order items into the orders table using repeated fields

Question 12

A team wants to store semi-structured user profile data for a web application. The data is accessed via a REST API and requires security rules to control read/write access. Which database fits best?

Accepted Answer

Firestore. Firestore is a NoSQL document database that natively stores semi-structured data (JSON-like documents) and integrates directly with Firebase Authentication and security rules to control read/write access per document or collection. Its REST API support and real-time capabilities make it ideal for web application user profiles that require flexible schemas and fine-grained access control.

Answer

BigQuery

Answer

Cloud SQL

Answer

Cloud Bigtable

Question 13

A company is designing a data lake on Cloud Storage with different zones. They need to enforce data retention so that objects in the 'raw' zone are automatically deleted after 1 year. Which TWO actions should they take? (Choose 2 correct options)

Accepted Answer

Configure a Cloud Storage object lifecycle rule with a Delete action. Option B is correct because Cloud Storage object lifecycle management allows you to set rules that automatically delete objects after a specified age. By configuring a lifecycle rule with a Delete action and setting the condition to 'Age: 365 days', objects in the 'raw' zone will be automatically removed after 1 year, meeting the retention requirement without manual intervention.

Answer

Use a bucket retention policy with a retention period of 1 year

Answer

Set an IAM policy to prevent deletion of objects

Answer

Create a Cloud Function to check object age and delete them

Question 14

A global fintech company needs a database that can serve transactional (OLTP) and analytical (OLAP) workloads with strong consistency. They require high availability and PostgreSQL compatibility. Which TWO Google Cloud databases meet these requirements? (Choose 2 correct options)

Accepted Answer

Cloud Spanner. Cloud Spanner is correct because it provides a globally distributed, strongly consistent relational database service that supports both OLTP and OLAP workloads via PostgreSQL-compatible interfaces (including the PostgreSQL dialect). It offers high availability through synchronous replication across zones and regions, and its TrueTime-based atomic clocks ensure external consistency for transactions, meeting the fintech company's requirements for strong consistency and PostgreSQL compatibility.

Answer

Cloud SQL

Answer

Cloud Bigtable

Answer

BigQuery

Question 15

A company wants to build a reporting pipeline where data is collected from IoT devices, stored raw in Cloud Storage, and then processed into BigQuery for analytics. They need to ensure data is encrypted at rest using customer-managed keys. Which THREE steps should they take? (Choose 3 correct options)

Accepted Answer

Enable CMEK on the Cloud Storage bucket by specifying the KMS key. Option B is correct because enabling CMEK on a Cloud Storage bucket by specifying a KMS key ensures that all objects stored in the bucket are encrypted at rest using a customer-managed key, which meets the requirement for customer-managed encryption. This is done by setting the bucket's default encryption to use a specific Cloud KMS key, and any object uploaded without its own encryption key will inherit this setting.

Answer

Delete the Cloud KMS key after data is loaded to BigQuery

Answer

Use Google-managed encryption keys