PDE Storing the Data — Timed 40 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?

40 minute time limit — choose an answer to begin.

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

Question 11

A company runs a global financial application requiring strong consistency across continents with 99.999% availability. They need to store transaction data with ACID properties and sub-10ms write latency from any region. Which storage service meets all requirements?

Accepted Answer

Cloud Spanner. Cloud Spanner is the only service that provides ACID transactions, strong consistency across continents, and 99.999% availability with sub-10ms write latency. It uses synchronous replication with Paxos-based consensus to ensure consistent writes globally, meeting the strict requirements of a global financial application.

Answer

Cloud SQL (MySQL) with cross-region read replicas

Answer

BigQuery multi-region

Answer

Cloud Bigtable multi-cluster routing

Question 12

A company is designing a data lake on Cloud Storage with three zones: raw, curated, and processed. They need to enforce data governance by restricting access to each zone using IAM. Which approach should they take?

Accepted Answer

Create three separate buckets, one per zone, and assign IAM roles per bucket. Cloud Storage buckets are the fundamental access boundary for IAM policies. By creating three separate buckets (raw, curated, processed), you can assign distinct IAM roles (e.g., roles/storage.objectViewer, roles/storage.objectAdmin) per bucket, ensuring that users or service accounts only have access to the specific zone they are authorized for. This approach aligns with the principle of least privilege and avoids the complexity and limitations of IAM conditions or object-level ACLs.

Answer

Create a single bucket with folders for each zone, and use IAM conditions to restrict access

Answer

Use Cloud Storage lifecycle rules to move objects between zones

Answer

Use a single bucket and rely on object ACLs

Question 13

A team needs to run hybrid transactional/analytical workloads on PostgreSQL-compatible data with low latency. They require high performance on both OLTP and OLAP queries, leveraging a columnar engine. Which Google Cloud service is best suited?

Accepted Answer

AlloyDB. AlloyDB is the correct choice because it is a fully managed PostgreSQL-compatible database service on Google Cloud that combines a columnar engine for fast analytical queries with high transactional performance. It uses a columnar query accelerator to offload analytical workloads from the transactional engine, enabling low-latency hybrid transactional/analytical processing (HTAP) without data movement.

Answer

Cloud SQL for PostgreSQL

Answer

Cloud Spanner

Answer

BigQuery

Question 14

A company is designing a data lake on Cloud Storage with BigLake tables for unified governance. Which TWO statements about BigLake are correct? (Choose 2.)

Accepted Answer

BigLake tables allow querying data in Cloud Storage using BigQuery without loading into BigQuery storage.. BigLake tables allow you to query data directly in Cloud Storage using BigQuery without the need to first load the data into BigQuery storage. This enables a data lake architecture where the data remains in an open format in Cloud Storage, while BigLake provides unified governance and fine-grained access control.

Answer

BigLake requires data to be loaded into BigQuery storage.

Answer

BigLake only supports structured data in BigQuery storage.

Answer

BigLake automatically converts CSV files to Parquet.

Question 15

A company uses Cloud Storage to store sensitive customer data. They need to restrict access to the data so that only requests from within a specific VPC network are allowed, and block all access from the public internet. Which TWO configurations should they implement? (Choose 2.)

Accepted Answer

Use IAM conditions to restrict access based on VPC network.. IAM conditions can be used to restrict access to Cloud Storage based on the requester's VPC network, ensuring only requests originating from the specified VPC are allowed. Option E is correct because VPC Service Controls create a service perimeter that prevents data exfiltration and blocks access from outside the perimeter, effectively denying public internet requests.

Answer

Enable Private Google Access on the VPC subnet.

Answer

Use Cloud Storage signed URLs for all access.

Answer

Disable public internet access by turning off the default internet gateway.

Question 16

A team is designing a Spanner database for a global inventory system. They need to optimize query performance for frequently joined tables. Which THREE design decisions help achieve this? (Choose 3.)

Accepted Answer

Design primary keys to distribute write load evenly across splits.. Spanner uses distributed splits for scalability, and designing primary keys to avoid hotspots (e.g., using hash prefixes or monotonically increasing keys) ensures write load is evenly distributed across splits, preventing performance bottlenecks.

Answer

Use Cloud SQL instead if joins are needed.

Answer

Store all data in a single table with JSON columns to avoid joins.

Question 17

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 model with timestamps directly matches the time-series IoT sensor data structure, and it supports high-throughput, low-latency access via the HBase API or Bigtable client libraries.

Answer

BigQuery

Answer

Cloud Spanner

Answer

Firestore

Question 18

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

AEAD encryption functions with Cloud KMS. BigQuery's AEAD (Authenticated Encryption with Associated Data) encryption functions, when used with Cloud KMS, allow you to encrypt individual columns (e.g., credit card numbers) using customer-managed encryption keys (CMEK) at the column level. This provides granular, application-layer encryption where the key is managed by the customer, not Google, and decryption is performed within SQL queries using the AEAD.DECRYPT_STRING function. Dataset-level CMEK (Option A) encrypts all data at rest in the dataset but cannot target specific columns.

Answer

Customer-managed encryption keys (CMEK) on the dataset

Answer

VPC Service Controls

Answer

BigQuery Data Catalog with policy tags

Question 19

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 that provides offline data persistence and automatic conflict resolution via multi-version concurrency control (MVCC) and last-write-wins (LWW) semantics. Its client SDKs synchronize data in the background when connectivity is restored, making it ideal for mobile apps requiring offline-first functionality.

Answer

Cloud SQL

Answer

Cloud Bigtable

Answer

Cloud Spanner

Question 20

A data engineer is designing a Bigtable row key for a time-series dataset where each row represents a sensor reading. The team expects high write throughput and wants to avoid hotspots. Which row key design is BEST?

Accepted Answer

Sensor ID followed by reversed timestamp. Prepending the sensor ID distributes writes across multiple tablet servers, while reversing the timestamp ensures that recent data (which is written most frequently) is spread across different rows rather than creating a hotspot at the end of the table. This design leverages Bigtable's lexicographic ordering to balance write load.

Answer

Only sensor ID

Answer

Timestamp followed by sensor ID

Answer

Random salt appended to timestamp