PDE Storing the Data — 10 Questions

Question 1

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?

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

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 2

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.. 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 3

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 4

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 5

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 6

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 7

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. 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 8

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 9

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. 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

Question 10

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 single-digit millisecond latency for high-throughput reads and writes. Its key-value model with timestamp-based versioning is ideal for time-series IoT sensor data, and it supports millions of reads per second via its HBase API and Bigtable's underlying tablet-based architecture.

Answer

Firestore

Answer

Cloud Spanner

Answer

BigQuery