PDE Storing the Data Practice Test 6 — 15 Questions

Question 1

A company is migrating an on-premises PostgreSQL database to Google Cloud. The database runs complex analytical queries mixed with OLTP workloads. They need PostgreSQL compatibility and want to improve analytical query performance without changing the application. Which database should they choose?

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

Accepted Answer

AlloyDB. AlloyDB is the correct choice because it is a fully managed PostgreSQL-compatible database service specifically designed for demanding transactional and analytical workloads. It combines the PostgreSQL ecosystem with a columnar engine and adaptive caching to accelerate analytical queries by up to 100x over standard PostgreSQL, all without requiring application changes. This makes it ideal for mixed OLTP and complex analytical queries while maintaining PostgreSQL compatibility.

Answer

BigQuery

Answer

Cloud Spanner

Answer

Cloud SQL for PostgreSQL

Question 2

A financial services company uses Cloud Bigtable to store trade data. They are experiencing hot-spotting on a single node, causing high latency. The row key format is [trade_id]#[timestamp]. Which row key design change would BEST distribute writes across tablets?

Accepted Answer

Use a hashed prefix of the trade_id, e.g., [hash(trade_id)]#[trade_id]#[timestamp]. Adding a hashed prefix of the trade_id ensures that writes are evenly distributed across all Bigtable tablets. Bigtable partitions data by row key lexicographic order; without a hash, sequential trade IDs or timestamps cause all recent writes to land on a single tablet, creating a hotspot. The hash spreads the write load uniformly, regardless of the underlying key pattern.

Answer

Use a single row key of [timestamp]

Answer

Increase the number of Bigtable nodes to 20

Answer

Change row key to [timestamp]#[trade_id]

Question 3

A data engineer needs to enforce that all datasets in a project expire after 90 days to reduce storage costs. They want to automate this without manual intervention. Which approach should they use?

Accepted Answer

Set a default table expiration on each BigQuery dataset to 90 days. BigQuery datasets support a default table expiration setting that automatically deletes tables after a specified number of days. This enforces a 90-day lifecycle for all tables in the dataset without requiring manual intervention or external automation, directly addressing the cost reduction goal.

Answer

Use Cloud Storage lifecycle rules to delete tables after 90 days

Answer

Create an IAM policy that revokes access after 90 days

Answer

Use BigQuery scheduled queries to delete tables older than 90 days

Question 4

An application needs to store user profile data in a document database with flexible schema. The data is accessed frequently from a mobile app. Which Google Cloud database is BEST suited?

Accepted Answer

Cloud Firestore. Cloud Firestore is a NoSQL document database designed for mobile and web app development, offering flexible schema, real-time data synchronization, and automatic scaling. It directly supports frequent reads from mobile apps through its client SDKs and offline persistence, making it the best fit for storing user profile data with varying attributes.

Answer

Cloud Bigtable

Answer

BigQuery

Answer

Cloud Spanner

Question 5

A company wants to build a data lake on Cloud Storage for raw, curated, and processed data zones. They need to enforce data governance including column-level security and row-level filtering for BigQuery queries. Which solution should they use?

Accepted Answer

BigLake tables over Cloud Storage. BigLake tables provide a unified governance layer over Cloud Storage data, enabling fine-grained access control such as column-level security and row-level filtering directly on BigQuery queries. This is achieved by integrating BigQuery's access control policies with the external data stored in GCS, without needing to move data into BigQuery native storage. The other options either lack these granular security features or require complex workarounds.

Answer

BigQuery external tables reading from GCS

Answer

Dataproc with Spark SQL

Answer

Cloud Storage with IAM and VPC Service Controls

Question 6

A healthcare organization stores patient data in BigQuery. They need to encrypt a specific column (e.g., SSN) using a key they manage, and decrypt it only for authorized queries via a user-defined function. Which approach should they use?

Accepted Answer

Use BigQuery AEAD encryption functions with a Cloud KMS key. BigQuery AEAD encryption functions (e.g., `AEAD.ENCRYPT` and `AEAD.DECRYPT`) allow you to encrypt a specific column using a customer-managed key stored in Cloud KMS, and then decrypt it only within a user-defined function (UDF) that enforces access controls. This meets the requirement of per-column encryption with key management and authorized decryption via a UDF.

Answer

Use BigQuery column-level access controls

Answer

Use Cloud Key Management Service (Cloud KMS) with CMEK for the BigQuery dataset

Answer

Use Cloud Data Loss Prevention (DLP) to de-identify the column

Question 7

A data engineer is designing a Cloud Bigtable schema for high-volume time-series data. Which TWO practices should they follow to avoid performance issues?

Accepted Answer

Use a hashed prefix in the row key to distribute writes. Using a hashed prefix to avoid hot-spotting and grouping related columns into column families are recommended. Timestamp-first keys cause hot-spotting. Single column family for all data is inefficient. Large number of column families also adds overhead.

Answer

Place the timestamp as the first component of the row key

Answer

Create as many column families as possible

Answer

Store all columns in a single column family

Question 8

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. Cloud Spanner provides strong consistency and automatic failover across regions as core features. Strong consistency ensures that all reads return the most recent write, which is critical for globally distributed databases that require ACID transactions. Automatic failover across regions is built into Spanner's architecture using synchronous replication and Paxos-based consensus, enabling high availability without manual intervention.

Answer

Secondary indexes

Answer

Bigtable as a caching layer

Answer

Read replicas for global distribution

Question 9

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 10

Which Google Cloud service is a fully managed relational database for MySQL, PostgreSQL, and SQL Server, offering automatic replication and backups?

Accepted Answer

Cloud SQL. Cloud SQL is the correct answer because it is Google Cloud's fully managed relational database service that supports MySQL, PostgreSQL, and SQL Server. It provides automatic replication across zones and automated backups, making it the ideal choice for traditional relational database workloads without the need for manual administration.

Answer

Cloud Spanner

Answer

AlloyDB

Answer

Bigtable

Question 11

A company uses Cloud Storage as a data lake with raw, curated, and processed zones. Data in the raw zone should be automatically moved to a cheaper storage class after 30 days, and deleted after 1 year. What is the most efficient way to implement this?

Accepted Answer

Use Object Lifecycle Management with rules to transition to Coldline after 30 days and delete after 365 days.. Object Lifecycle Management in Cloud Storage allows you to set rules based on object age. You can transition objects to a lower-cost storage class (e.g., Nearline or Coldline) after 30 days and delete after 365 days.

Answer

Write a Cloud Function that runs daily, checks object ages, and moves/deletes them.

Answer

Use Cloud Scheduler to run a script that changes storage class and deletes objects.

Answer

Set a retention policy on the raw zone to prevent deletion and manually clean up.

Question 12

You are designing a row key for Cloud Bigtable to store user activity logs. Each log entry has a timestamp (millisecond precision) and a user ID. There will be millions of writes per second from many users. To avoid hotspotting, which row key design is BEST?

Accepted Answer

hash(userID)#userID#timestamp_millis. Best because it uses a hash of the user ID as the row key prefix, which distributes writes across all Bigtable nodes and avoids hotspotting. Appending the user ID and timestamp ensures uniqueness and supports efficient queries for a specific user's logs. This design prevents the sequential timestamp from creating a single hot node, which is critical for handling millions of writes per second.

Answer

timestamp_millis#hash(userID)

Answer

timestamp_millis#userID

Answer

userID#timestamp_millis

Question 13

Which Google Cloud service is a serverless, highly scalable data warehouse for analytical queries, supporting SQL and integration with BI tools?

Accepted Answer

BigQuery. BigQuery is a serverless, highly scalable data warehouse designed for analytical queries over large datasets. It supports standard SQL and integrates seamlessly with BI tools like Looker and Tableau, making it the correct choice for this use case.

Answer

Firestore

Answer

Cloud SQL

Answer

Cloud Spanner

Question 14

A company wants to run hybrid transactional and analytical workloads on a PostgreSQL-compatible database with high performance. Which service should they choose?

Accepted Answer

AlloyDB. AlloyDB is the correct choice because it is a fully managed PostgreSQL-compatible database service specifically designed for high-performance hybrid transactional and analytical workloads. It combines transactional processing with built-in columnar analytics, delivering up to 4x faster transactional performance and up to 100x faster analytical queries than standard PostgreSQL, without requiring any schema changes or ETL.

Answer

Cloud Spanner

Answer

Cloud SQL for PostgreSQL

Answer

BigQuery

Question 15

A company is using BigQuery for analytics and needs to ensure that certain columns containing PII are encrypted with a customer-managed key (CMEK). Which approach should they take?

Accepted Answer

Use BigQuery column-level encryption with AEAD functions and a Cloud KMS key.. BigQuery column-level encryption using AEAD (Authenticated Encryption with Associated Data) functions allows you to encrypt specific columns containing PII with a customer-managed key (CMEK) stored in Cloud KMS. This approach provides granular, field-level encryption that meets compliance requirements without affecting the rest of the table or dataset, and the encryption/decryption is performed transparently within BigQuery using the AEAD.DECRYPT_STRING function.

Answer

Use Cloud Data Loss Prevention (DLP) to mask the columns during query.

Answer

Apply CMEK at the dataset level; all tables inherit the encryption.

Answer

Store the data encrypted in Cloud Storage and use external tables with a CMEK.