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

10 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