PDE Storing the Data Practice Test 5 — 15 Questions

Question 1

An organization needs to prevent data exfiltration from BigQuery by ensuring all traffic to BigQuery APIs goes through VPC boundaries and is restricted to a specific service perimeter. Which Google Cloud security control should they use?

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

Accepted Answer

VPC Service Controls. VPC Service Controls (D) is the correct answer because it allows you to define a service perimeter around BigQuery APIs, ensuring that all traffic to BigQuery must originate from within the defined VPC boundaries. This prevents data exfiltration by blocking unauthorized access from outside the perimeter, even if valid credentials are used. It works by enforcing context-aware access policies at the Google Cloud network edge, not at the application layer.

Answer

Access Transparency

Answer

IAM conditions on BigQuery roles

Answer

Cloud Armor

Question 2

A company wants to use BigQuery to query data stored in Parquet files in Cloud Storage without loading the data into BigQuery. Which BigQuery 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 feature uses a federated query engine that reads the data on the fly, supporting formats like Parquet, Avro, ORC, CSV, and JSON. Option C is correct because it directly addresses the requirement to query Parquet files in Cloud Storage without ingestion.

Answer

BigQuery Omni

Answer

BigQuery ML

Answer

BigQuery BI Engine

Question 3

A Cloud SQL instance for PostgreSQL is experiencing heavy read traffic. The team wants to offload read queries while maintaining data consistency. Which solution meets their needs?

Accepted Answer

Create read replicas and direct read queries to them.. Read replicas in Cloud SQL for PostgreSQL allow you to offload read traffic from the primary instance by creating one or more asynchronous replicas that serve read queries. This maintains data consistency because replicas use PostgreSQL's native streaming replication, ensuring that all committed transactions on the primary are eventually reflected on the replica, providing a consistent snapshot for read operations.

Answer

Increase the tier of the Cloud SQL instance.

Answer

Create a Cloud SQL failover replica.

Answer

Use Cloud Memorystore as a cache in front of Cloud SQL.

Question 4

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 5

A data team needs to run complex analytical queries on a dataset that is frequently updated with new rows. They want to minimize query costs and avoid scanning old data that is rarely queried. Which BigQuery feature should they use?

Accepted Answer

Partitioned tables with partition expiration. Partitioned tables with partition expiration allow you to divide a table into segments based on a date/timestamp column, and automatically delete partitions that are older than a specified duration. This minimizes query costs by only scanning relevant partitions and eliminates storage costs for old, rarely queried data without manual intervention.

Answer

BigQuery materialized views

Answer

Clustered tables

Answer

BigQuery BI Engine

Question 6

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 (B) is correct because it is a fully managed service that runs the native PostgreSQL engine, so it is directly compatible with the on-premises PostgreSQL database and supports transactional (OLTP) workloads. AlloyDB (D) is also correct because it is a fully managed, PostgreSQL-compatible database engine that is specifically designed for high-performance transactional workloads while also accelerating analytical queries through its columnar engine, making it well suited to mixed transactional and analytical use. Cloud Spanner (A) is not the right fit here because, although it is fully managed and highly scalable, it is not PostgreSQL-compatible in the native sense and is aimed at globally distributed OLTP rather than this migration scenario. BigQuery (C) is a fully managed analytical data warehouse optimized for OLAP, not a PostgreSQL-compatible transactional database. Firestore (E) is a fully managed NoSQL document database and is neither PostgreSQL-compatible nor designed for the transactional/analytical mix described.

Answer

Cloud Spanner

Answer

BigQuery

Answer

Firestore

Question 7

A data engineer needs to create a unified table that combines data from Cloud Storage (Parquet files) and BigQuery native tables, with fine-grained access control and governance. Which three Google Cloud features should they use together? (Choose THREE.)

Accepted Answer

BigQuery. BigQuery is correct because it serves as the unified query engine that can read data from both Cloud Storage (via external tables or BigLake) and native BigQuery tables, enabling a single SQL interface for analysis. It also integrates with fine-grained access control through row-level security and column-level access policies, and supports governance via Data Catalog and VPC Service Controls.

Answer

Dataproc

Answer

Cloud SQL

Question 8

A company needs to store and analyze large amounts of unstructured data (images, videos) and structured data (CSV logs) in a cost-effective manner. The data should be accessible for analytics with BigQuery. Which two services should they use? (Choose TWO.)

Accepted Answer

BigQuery. Cloud Storage is the best option for storing unstructured and structured files cost-effectively. BigQuery can analyze this data directly via external tables or after loading, making it a powerful analytics platform.

Answer

Cloud SQL

Answer

Cloud Spanner

Answer

Firestore

Question 9

A team needs to store transactional data for an e-commerce application that requires ACID transactions, automatic backups, and point-in-time recovery. The expected workload is under 10,000 QPS. Which database should they choose?

Accepted Answer

Cloud SQL. Cloud SQL is the correct choice because it provides fully managed relational databases (MySQL, PostgreSQL, SQL Server) with built-in ACID transaction support, automated backups, and point-in-time recovery (PITR) via binary logs or write-ahead logs. The workload of under 10,000 QPS is well within Cloud SQL's performance envelope, making it a cost-effective and operationally simple solution for transactional e-commerce data.

Answer

Cloud Bigtable

Answer

Firestore

Answer

Cloud Spanner

Question 10

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 retention policies. Cloud Storage supports WORM (Write Once, Read Many) compliance through bucket retention policies and object retention (object-level retention lock), along with legal holds. A bucket retention policy sets a minimum retention period for all objects in the bucket, while object retention allows you to set or extend retention on individual objects, and legal holds prevent deletion or modification until removed. Together these provide the object-level retention controls required for long-term backup retention and regulatory compliance, making Cloud Storage with retention policies the correct choice.

Answer

Firestore backups

Answer

Cloud Storage with Object Lock

Answer

BigQuery table snapshots

Question 11

An application requires a globally distributed, strongly consistent database with 99.999% availability SLA. The workload is OLTP with high throughput across continents. Which service fits best?

Accepted Answer

Cloud Spanner. Cloud Spanner is the only service that provides globally distributed, strongly consistent (external consistency via TrueTime) OLTP with 99.999% availability SLA. It supports high-throughput ACID transactions across continents using synchronous replication and atomic clocks, meeting all stated requirements.

Answer

Cloud SQL with cross-region replicas

Answer

Firestore

Answer

Cloud Bigtable

Question 12

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 13

A team wants to use Cloud Storage to build a data lake with separate zones for raw, curated, and processed data. They need to automatically move objects older than 30 days from the raw zone to a cheaper storage class. How can they achieve this?

Accepted Answer

Configure a Cloud Storage object lifecycle rule with SetStorageClass action. Cloud Storage object lifecycle management rules can automatically transition objects from one storage class to a cheaper one (e.g., from Standard to Nearline or Coldline) based on age. By configuring a rule with the `SetStorageClass` action and a `Condition` of `age: 30`, objects in the raw zone bucket older than 30 days are moved to a lower-cost class without manual intervention or additional compute services.

Answer

Set a bucket retention policy that forces deletion after 30 days

Answer

Write a Cloud Function to delete objects older than 30 days

Answer

Use gsutil rsync to move objects between buckets

Question 14

A company uses BigQuery for analytics and needs to ensure that certain columns containing PII are encrypted at query time so that only authorized users can decrypt. What should they use?

Accepted Answer

BigQuery AEAD encryption functions. BigQuery AEAD encryption functions allow you to encrypt sensitive columns (e.g., PII) at query time using a user-managed key, so that only authorized users who possess the key can decrypt the data. This is the correct approach because it provides column-level, application-layer encryption that is transparent to the query engine and ensures that unauthorized users see only ciphertext.

Answer

VPC Service Controls

Answer

Customer-managed encryption keys (CMEK)

Answer

Fine-grained IAM roles

Question 15

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 is a valid direct way to query Parquet data without loading. BigLake (option D) is also a valid approach: BigLake tables are external tables over Cloud Storage that BigQuery can query without loading, with additional governance features. Therefore both B and D are correct. Option A is Cloud SQL, not BigQuery; option C loads data into BigQuery.

Answer

Use Cloud SQL federated query to read Parquet from GCS

Answer

Use the bq load command with --source_format=PARQUET