PDE Storing the Data — Timed 20 Questions

Question 1

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?

20 minute time limit — choose an answer to begin.

Accepted Answer

Create three separate folders in a single bucket: raw, curated, processed.. The recommended data lake structure on Google Cloud is to use a single bucket with separate folders (prefixes) for raw, curated, and processed data. This aligns with the zone-based data lake architecture (raw/bronze, curated/silver, processed/gold) and simplifies management, access control, and lifecycle policies while keeping all data in one place. Cloud Storage folders are just object name prefixes, so this is a logical separation that works well with IAM and lifecycle rules.

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 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 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). Bigtable sorts row keys lexicographically and stores contiguous key ranges in tablets on specific nodes. If the row key begins with device_id, all writes for a single device land in one tablet, and if the device_id space is small or skewed, a few tablets absorb most traffic, causing hotspots. Prefixing with a hash of device_id distributes writes uniformly across the key space, while the timestamp suffix preserves the ability to scan a device's data over a time range within a single hash bucket.

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 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]. Bigtable sorts rows lexicographically by row key, and writes to adjacent keys land on the same tablet, causing hotspotting. Hashing the sensor_id and prefixing it to the timestamp spreads writes uniformly across the key space, so consecutive writes from different sensors distribute across many tablets. This is the canonical Bigtable time-series pattern recommended by Google.

Answer

[timestamp reversed]#[sensor_id]

Answer

[sensor_id]#[timestamp]

Answer

[timestamp]#[sensor_id]

Question 5

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 6

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

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. AlloyDB is also correct because it is a fully PostgreSQL-compatible database service that delivers high availability and strong consistency. It is designed to handle both transactional and analytical workloads using a combined row-store and columnar engine, enabling high-performance OLTP and real-time analytics within a single database. Both services meet the fintech company's requirements for PostgreSQL compatibility, strong consistency, and high availability.

Answer

Cloud SQL

Answer

Cloud Bigtable

Answer

BigQuery

Question 8

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 9

An IoT application writes sensor readings to Cloud Bigtable with a row key of 'deviceID#timestamp'. The team notices high write latency and hotspots on a few nodes. Which row key design change would most likely improve performance?

Accepted Answer

Add a random prefix to the row key (e.g., hash of deviceID modulo 1000). Adding a random prefix to the row key (e.g., hash of deviceID modulo 1000) distributes writes across multiple nodes, avoiding hotspots caused by sequential timestamps. Bigtable sorts data lexicographically by row key, so a key like 'deviceID#timestamp' can cause hot spotting if many devices write concurrently with similar timestamps. The random prefix ensures even distribution.

Answer

Reverse the key to 'timestamp#deviceID'

Answer

Use a single column family with many columns

Answer

Store all data in one row per device

Question 10

A company wants to use BigQuery to query data stored in Cloud Storage as Parquet files without loading the data into BigQuery storage. Which 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 is achieved by defining a table schema that references the external data source, enabling BigQuery to read the Parquet files on-the-fly using its federated query engine.

Answer

BigQuery ingestion from Cloud Storage using load jobs

Answer

Cloud Storage FUSE to mount bucket and query

Answer

BigQuery federated queries with Cloud Storage

Question 11

A healthcare company must encrypt data in BigQuery with customer-managed keys (CMEK). They want to control the key lifecycle independently. Which approach should they take?

Accepted Answer

Use Cloud KMS to create a key, then set it as the default encryption key for the BigQuery dataset. Setting a Cloud KMS key as the default encryption key on the BigQuery dataset applies CMEK to all tables created in that dataset, giving the customer full control over the key lifecycle (rotation, disable, destroy) independently of Google. This is the standard, supported approach for dataset-level CMEK in BigQuery and satisfies the requirement without custom application logic.

Answer

Use BigQuery column-level encryption (AEAD) with a key from Cloud KMS

Answer

Enable default encryption on the Cloud Storage bucket used for staging data

Answer

Encrypt the data before loading using a custom application and store the key in Secret Manager

Question 12

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.. Option D is correct because IAM conditions on Cloud Storage bucket policies can include a request attribute such as `resource.name` or, more relevantly, `request.auth.accessLevels`/network-based conditions, allowing access to be restricted to requests originating from a specified VPC network. Option E is correct because VPC Service Controls creates a service perimeter around Cloud Storage, which prevents access from outside the perimeter (including the public internet) and enforces that only authorized VPC networks/projects within the perimeter can reach the service. Option A is not correct here because Private Google Access only allows VM instances in a subnet without external IPs to reach Google APIs and services; it does not block public internet access to a Cloud Storage bucket. Option B is not correct because signed URLs grant time-limited access to anyone holding the URL, including public internet users, so they do not restrict access to a specific VPC network. Option C is not correct because Google Cloud VPCs do not have a user-managed 'default internet gateway' that can simply be turned off to block public access to a managed service like Cloud Storage.

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 13

An organization uses Cloud Storage to store backup files. They want to automatically delete files older than 90 days, and after deletion, move remaining files to Nearline storage if not accessed for 30 days. Which Cloud Storage feature should they configure?

Accepted Answer

Object Lifecycle Management. Object Lifecycle Management (D) is the correct feature because it allows you to define rules to automatically transition objects to colder storage classes (such as Nearline) after a specified period of inactivity and to delete objects after a set age. In this scenario, a lifecycle rule can be configured to delete objects older than 90 days and, for the remaining objects, move them to Nearline storage if they have not been accessed for 30 days. This fully automates the required data management without manual intervention.

Answer

Object Versioning

Answer

Retention Policies

Answer

Bucket Lock

Question 14

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

Question 15

A company needs a fully managed, PostgreSQL-compatible database that supports both transactional (OLTP) and analytical (OLAP) workloads with low latency. They want to minimize operational overhead. Which two Google Cloud services should they consider? (Choose two.)

Accepted Answer

AlloyDB with BigQuery as a federated source. Option E (AlloyDB) is correct because AlloyDB for PostgreSQL is a fully managed, PostgreSQL-compatible database engine that delivers high-performance transactional processing and includes an in-memory columnar engine that accelerates analytical queries, enabling hybrid OLTP/OLAP workloads with low latency while minimizing operational overhead. Option C (AlloyDB with BigQuery as a federated source) is correct because AlloyDB handles the low-latency transactional and analytical processing, while BigQuery federation lets AlloyDB query BigQuery data without ETL, extending analytical capability across both systems without adding operational burden. Option A (Cloud SQL for PostgreSQL) is not correct because, while it is managed and PostgreSQL-compatible, it is designed primarily for traditional OLTP workloads and lacks the integrated columnar acceleration needed for low-latency OLAP. Option B (Cloud Spanner) is not correct because it is not PostgreSQL-only in the sense required here and is optimized for globally distributed OLTP rather than hybrid OLAP acceleration. Option D (BigQuery) is not correct because it is a serverless data warehouse optimized for analytics, not a low-latency PostgreSQL-compatible transactional database.

Answer

Cloud SQL for PostgreSQL

Answer

Cloud Spanner

Answer

BigQuery

Question 16

A company stores data in a Cloud Storage bucket with versioning enabled. They want to automatically delete objects that are noncurrent (i.e., previous versions) after 30 days, and also delete the current version if it is older than 365 days. Which three Object Lifecycle Management conditions can be used together? (Choose three.)

Accepted Answer

age: 365. Option B (age: 365) is correct because the age condition measures the time since an object's current version was created, so age: 365 will delete the live (current) version once it is older than 365 days. Option C (numNewerVersions: 1) is correct because it identifies noncurrent versions by counting how many newer versions exist; with numNewerVersions: 1, any object version that has at least one newer version is treated as noncurrent and eligible for deletion. Option E (noncurrentTimeBefore: 30) is correct because noncurrentTimeBefore specifically targets the time a version became noncurrent, so a value of 30 days deletes previous versions 30 days after they were superseded. Option A (lastAccessTime: 30) is not part of the marked correct set and is not a valid Object Lifecycle Management condition for this requirement, since lastAccessTime is not a supported lifecycle condition in Cloud Storage. Option D (daysSinceCustomTime: 30) is also not in the correct set; daysSinceCustomTime applies only when an object has a custom time set and does not address noncurrent version deletion based on becoming noncurrent.

Answer

lastAccessTime: 30

Answer

daysSinceCustomTime: 30

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 storage model and automatic sharding make it ideal for time-series IoT sensor data with simple timestamp-based keys, supporting high-throughput, low-latency access without the overhead of relational features.

Answer

BigQuery

Answer

Cloud Spanner

Answer

Firestore

Question 18

A multinational corporation needs a globally distributed database that supports strong consistency, SQL queries, and automatic failover across regions. They also want to optimize join performance for parent-child relationships. Which TWO features of Cloud Spanner should they use?

Accepted Answer

Strong consistency and automatic failover across regions. Option A is correct because Cloud Spanner natively provides strong consistency (external consistency via TrueTime) and automatic failover across regions through its multi-region configurations, directly matching the requirement for a globally distributed database with strong consistency and automatic regional failover. Option E is correct because interleaved tables physically co-locate child rows with their parent rows, which optimizes join performance for parent-child relationships by reducing data shuffling and network hops. Option B is incorrect because secondary indexes improve query filtering and lookup performance, not parent-child join optimization. Option C is incorrect because Bigtable is a separate NoSQL service and cannot serve as a caching layer that provides SQL strong consistency for Spanner. Option D is incorrect because read replicas in Cloud Spanner are for scaling reads and serving data closer to users, not for providing the strong-consistency, automatic-failover multi-region capability described.

Answer

Secondary indexes

Answer

Bigtable as a caching layer

Answer

Read replicas for global distribution

Question 19

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 the correct choice because it is a fully managed, scalable NoSQL database designed for large analytical and operational workloads, offering consistent sub-10ms latency for high-throughput reads and writes. It natively supports time-series data with row key design optimized for timestamp-based queries, and can handle millions of reads per second across petabytes of data, making it ideal for IoT sensor data.

Answer

BigQuery

Answer

Firestore

Answer

Cloud Spanner

Question 20

You are designing a Cloud Storage bucket to hold sensitive financial documents that must not be deleted or overwritten for 7 years. After the retention period, the documents can be deleted automatically. Which configuration should you use?

Accepted Answer

Use Bucket Lock with a retention policy of 7 years and configure a lifecycle rule to delete objects after 7 years.. Bucket Lock (also known as Object Lock) provides a WORM (Write Once, Read Many) retention policy that prevents objects from being deleted or overwritten for a specified period. By setting a retention policy of 7 years, you enforce the required compliance hold. Then, a lifecycle rule configured to delete objects after 7 years ensures automatic removal once the retention period expires. This combination meets both the retention and automatic deletion requirements.

Answer

Set a retention policy on the bucket for 7 years and enable Object Versioning.

Answer

Use Object Lock with WORM mode and set a retention period of 7 years. After the period, objects are automatically deleted.

Answer

Set a lifecycle rule to delete objects after 7 years and enable Bucket Lock.