Courseiva

CCNA Pde Storing Data Questions

75 of 109 questions · Page 1/2 · Pde Storing Data topic · Answers revealed

Practice these questionsExam hubAll questions
1
MCQmedium

A media company ingests thousands of small JSON files per hour into a Cloud Storage bucket and wants to analyze them with BigQuery. Analysts frequently filter by event date and by device type, and they want to minimize query cost. The team wants a managed approach that avoids writing custom transformation code. Which BigQuery feature should the engineer use?

A.Create an external table over the bucket and rely on automatic schema detection.
B.Use a BigQuery load job to create a native table partitioned by event date and clustered by device type.
C.Create a federated query that reads the bucket through the Cloud Storage connector.
D.Define a BigLake external table with a metadata cache and partition pruning enabled.
AnswerB

Loading the JSON into a native table consolidates the small files into BigQuery managed storage, and partitioning by event date lets queries prune to the relevant date range. Clustering by device type further reduces bytes scanned for filters on that column. A standard load job requires no custom transformation code, matching the managed requirement, and native storage gives the best query performance and cost profile.

Why this answer

Loading the JSON into a native, partitioned, and clustered table moves the data into BigQuery managed columnar storage, where filters on event date prune partitions and filters on device type benefit from clustering. This reduces bytes scanned and therefore cost, and it requires no custom transformation code. External and federated approaches keep querying the raw small files, which is less efficient and more expensive for repeated analysis.

Exam trap

The trap here is assuming that any external or federated access to Cloud Storage is automatically cheaper, when repeated analytical queries over raw small files usually cost more than loading into partitioned native storage.

Practice this question →
2
MCQhard

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?

A.Use BigQuery AEAD encryption functions with a Cloud KMS key
B.Use BigQuery column-level access controls
C.Use Cloud Key Management Service (Cloud KMS) with CMEK for the BigQuery dataset
D.Use Cloud Data Loss Prevention (DLP) to de-identify the column
AnswerA

BigQuery AEAD functions encrypt and decrypt individual column values using a Cloud KMS key the organisation controls, and can be invoked inside a user-defined function. This satisfies both the customer-managed key and authorised-query decryption constraints.

Why this answer

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.

Exam trap

A common mistake is to choose dataset-level encryption (CMEK) because it involves Cloud KMS, but CMEK does not allow per-column encryption or UDF-controlled decryption. The correct approach uses BigQuery AEAD encryption functions with a Cloud KMS key for column-level encryption and authorized decryption via a UDF.

How to eliminate wrong answers

Option B is wrong because BigQuery column-level access controls only restrict who can see the column, but they do not encrypt the data at rest or in transit, so the data remains in plaintext and does not satisfy the encryption requirement. Option C is wrong because Cloud KMS with CMEK encrypts the entire BigQuery dataset at the storage level, not a specific column, and decryption is automatic for authorized users, not controlled via a UDF. Option D is wrong because Cloud DLP de-identifies data (e.g., masking or tokenization) but is not designed for reversible encryption with a customer-managed key and UDF-based decryption; it is typically used for static de-identification, not dynamic per-query decryption.

Practice this question →
3
Multi-Selecthard

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

Select 3 answers
A.BigQuery
B.Dataproc
C.BigLake
D.Cloud Storage
E.Cloud SQL
AnswersA, C, D

BigQuery is used for both native tables and the unified query engine.

Why this answer

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.

Exam trap

The trap here is that candidates may confuse Dataproc (a processing engine) with a storage or query service, or think Cloud SQL can handle Parquet files, when the correct combination requires BigQuery, BigLake, and Cloud Storage to achieve unified querying and governance.

Practice this question →
4
Multi-Selecthard

A media company stores 900 TB of video master files in a Cloud Storage bucket in the US multi-region. Legal requires that the objects be retained for seven years and cannot be deleted or overwritten by any user, including project owners, during that period. The company also wants to minimize storage cost for objects that are rarely accessed after the first 90 days. The data engineer must implement a compliant configuration. (Choose two.)

Select 2 answers
A.Set the bucket's default storage class to Standard and rely on the multi-region location for durability.
B.Enable Uniform bucket-level access and grant the storage.objectViewer role to analysts.
C.Add an Object Lifecycle Management rule that transitions objects to Coldline storage 90 days after creation.
D.Apply a bucket lock on a retention policy that sets the retention period to seven years.
E.Add a lifecycle rule that deletes objects 90 days after creation to control cost.
AnswersC, D

Coldline is designed for data accessed less than once a quarter, so transitioning the rarely accessed video masters after 90 days lowers storage cost while still allowing reads. Lifecycle rules run automatically and do not conflict with the retention policy, so this satisfies the cost requirement.

Why this answer

A locked retention policy is the only Cloud Storage mechanism that prevents even project owners from deleting or overwriting objects, providing the required WORM guarantee for seven years. Pairing it with a lifecycle transition to Coldline after 90 days meets the cost objective because Coldline targets infrequently accessed data without conflicting with retention.

Exam trap

The trap here is believing that IAM roles or object holds alone satisfy a legal WORM requirement, when only a locked retention policy makes deletion impossible for everyone.

Practice this question →
5
Multi-Selecteasy

A data engineer wants to set up automatic deletion of objects from a Cloud Storage bucket after 30 days, and transition objects older than 7 days to Nearline storage. Which THREE steps should they take? (Select three.)

Select 3 answers
A.Set the bucket's default storage class to Nearline
B.Create a lifecycle rule with action Delete and condition age 30 days
C.Create a lifecycle rule with action SetStorageClass to Nearline and condition age 7 days
D.Enable Object Versioning on the bucket
E.Apply the lifecycle configuration to the bucket using gsutil lifecycle set
AnswersB, C, E

A lifecycle rule whose action is Delete with an age condition of 30 days instructs Cloud Storage to remove objects automatically once they reach that age, directly satisfying the stated requirement for deletion after 30 days without manual intervention.

Why this answer

Option B is correct because a lifecycle rule with the Delete action and an age condition of 30 days will automatically remove objects once they reach 30 days old, satisfying the deletion requirement. Option C is correct because a lifecycle rule with the SetStorageClass action targeting Nearline and an age condition of 7 days transitions objects older than 7 days to Nearline storage, exactly matching the transition requirement. Option E is correct because lifecycle rules must be applied to the bucket by submitting the JSON configuration with the gsutil lifecycle set command (or equivalent API call); creating rules without applying them has no effect.

Option A is not needed because changing the bucket's default storage class only affects newly uploaded objects and does not perform age-based transitions or deletions. Option D is not required because Object Versioning is unrelated to time-based deletion or storage-class transitions and would actually complicate deletion by retaining noncurrent versions.

Exam trap

Google Cloud Storage lifecycle rules are applied at the bucket level and can transition storage classes or delete objects based on age. A common pitfall is confusing the bucket's default storage class (which only affects new objects) with lifecycle rules (which can act on existing objects).

Practice this question →
6
MCQeasy

A mobile app needs a real-time NoSQL database that supports offline sync and automatic conflict resolution. Which Google Cloud database is best suited?

A.Cloud SQL
B.Firestore
C.Cloud Bigtable
D.Cloud Spanner
AnswerB

Firestore provides offline data persistence, automatic sync, and conflict resolution for mobile and web clients.

Why this answer

Firestore is the correct choice because it is a NoSQL, real-time database with client SDKs that provide offline data persistence and automatic conflict resolution using last-write-wins semantics. Its SDKs synchronize data in the background when connectivity is restored, making it well suited for mobile apps requiring offline-first functionality.

Exam trap

A common misconception is that any NoSQL database (like Bigtable) supports mobile offline sync, but Bigtable lacks client-side SDKs and conflict resolution mechanisms, making Firestore the best fit for real-time mobile apps with offline capabilities.

How to eliminate wrong answers

Option A (Cloud SQL) is wrong because it is a relational (SQL) database that does not natively support real-time sync or offline-first mobile clients; it requires custom backend logic for conflict resolution. Option C (Cloud Bigtable) is wrong because it is a wide-column NoSQL database designed for high-throughput analytical workloads, not for real-time mobile sync or offline support; it lacks client-side SDKs for automatic conflict resolution. Option D (Cloud Spanner) is wrong because it is a globally distributed relational database with strong consistency, but it does not provide built-in offline sync or automatic conflict resolution for mobile clients; it is optimized for OLTP workloads requiring ACID transactions across regions.

Practice this question →
7
MCQhard

A company stores JSON-formatted application logs in Cloud Storage. They need to query these logs with SQL, but they want to avoid the cost and latency of loading them into BigQuery. The logs have a consistent schema, and queries will filter on a timestamp field and a few nested fields. Which approach should they use?

A.Create an external table in BigQuery over the Cloud Storage JSON files and enable hive partitioning.
B.Create a Dataproc cluster and run Spark SQL queries against the JSON files in Cloud Storage.
C.Load the JSON files into a BigQuery native table using schema autodetect and then delete the files.
D.Use BigQuery federated queries with a Cloud SQL connection to query the JSON files.
AnswerA

BigQuery external tables can query JSON files directly in Cloud Storage without loading. Enabling hive partitioning on a timestamp-derived folder structure allows partition pruning, reducing data scanned and improving performance. This matches the need to query with SQL while avoiding load costs and leverages the consistent schema and timestamp filter.

Why this answer

BigQuery external tables allow querying JSON files in Cloud Storage without loading, and hive partitioning enables partition pruning on the timestamp field, reducing scanned data and cost. This meets the requirement to avoid load costs and latency while supporting SQL queries. Loading into a native table, federated queries, or Dataproc all introduce extra cost or complexity and do not match the serverless, direct-query need.

Exam trap

The trap here is thinking that federated queries can access Cloud Storage files, when they are actually for external databases like Cloud SQL.

Practice this question →
8
MCQhard

A healthcare company stores patient documents in a Cloud Storage bucket with a retention policy. Auditors require that once an object is written, it cannot be overwritten or deleted by any user, including project owners, for 7 years. The company also wants to minimize storage cost after the first year while preserving immutability. What should the data engineer configure?

A.Enable uniform bucket-level access and create a retention policy without locking it, then transition objects to Coldline after 365 days.
B.Apply a bucket lock on a retention policy with a 7-year retention period, then add a lifecycle rule to transition objects to Archive after 365 days.
C.Set an IAM deny policy on storage.objects.delete for all principals and use a lifecycle rule to move objects to Nearline after 365 days.
D.Enable Object Versioning and set a lifecycle rule to move objects to Coldline after 365 days.
AnswerB

A locked retention policy enforces WORM semantics: objects cannot be deleted or overwritten until the retention period expires, and even project owners cannot remove the lock. A lifecycle rule can still transition the storage class to Archive after one year, reducing cost while the retention policy continues to protect the data for the full seven years.

Why this answer

A locked retention policy is the only Cloud Storage mechanism that enforces immutability against all users, including project owners, for the specified duration. Combining it with a lifecycle rule that transitions objects to Archive after one year satisfies the retention requirement while lowering storage cost for the remaining six years.

Exam trap

The trap here is confusing permission-based deletion prevention, such as IAM deny policies, with a locked retention policy that enforces WORM even against project owners.

Practice this question →
9
MCQhard

A logistics company runs Cloud SQL for MySQL for its order management system. The database is 2 TB and growing 100 GB per month. They need to run analytical queries without impacting transactional performance, and they want to minimize operational overhead. They also require the analytics data to be no more than 15 minutes stale. Which storage approach should they use?

A.Export the Cloud SQL database to a Cloud Storage bucket nightly with mysqldump, then load the files into BigQuery each morning.
B.Enable the Cloud SQL federated query feature in BigQuery and query the Cloud SQL instance directly from BigQuery.
C.Create a read replica of the Cloud SQL instance and run analytical queries directly against the replica.
D.Configure a Datastream stream from Cloud SQL to BigQuery with a change data capture policy and query the replicated tables in BigQuery.
AnswerD

Datastream replicates changes from Cloud SQL for MySQL to BigQuery using change data capture, keeping the analytics copy fresh within minutes. BigQuery handles large analytical scans without affecting the transactional database and requires minimal operational management. This satisfies the 15-minute staleness requirement and the need to avoid impacting order management performance, while minimizing overhead compared to managing replicas.

Why this answer

Datastream provides serverless change data capture from Cloud SQL for MySQL into BigQuery, keeping the analytics copy within minutes of the source. BigQuery isolates analytical scans from the transactional instance, so order management performance is unaffected. The managed service minimizes operational overhead compared to maintaining replicas or custom export jobs, and it satisfies the 15-minute freshness requirement through continuous replication.

Exam trap

The trap here is assuming a read replica is sufficient for analytics, when heavy scans can still cause replication lag and resource contention.

Practice this question →
10
MCQhard

A financial services firm stores trade documents in a Cloud Storage bucket. Auditors require that each object be retained for exactly seven years and that no user, including project owners, be able to delete or overwrite the objects during that period. The firm also needs to prove compliance to auditors. Which combination of controls should the data engineer implement?

A.Create a bucket lock on a retention policy of seven years and grant auditors the Storage Object Viewer role.
B.Apply IAM conditions that deny storage.objects.delete unless the request comes from an auditor's service account.
C.Set an Object Lifecycle Management rule to delete objects after 2555 days and enable uniform bucket-level access.
D.Enable Cloud Storage versioning and set a lifecycle rule to keep noncurrent versions for seven years.
AnswerA

A bucket retention policy prevents deletion or replacement of objects until the retention period elapses, and locking the policy makes it permanent and irreversible even for project owners. Granting auditors Storage Object Viewer lets them inspect objects and the bucket metadata to verify the lock. This combination enforces seven-year immutability and provides the evidence auditors need.

Why this answer

A locked bucket retention policy enforces object immutability for the specified duration and cannot be removed or shortened, satisfying the requirement that even project owners cannot delete data. Because the lock is a permanent bucket property, auditors can verify it directly. Lifecycle rules, versioning, and IAM conditions are all modifiable controls and therefore cannot guarantee the required immutability.

Exam trap

The trap here is treating Cloud Storage versioning or a lifecycle rule as equivalent to WORM retention, when only a locked retention policy prevents deletion by privileged users.

Practice this question →
11
Multi-Selectmedium

A media company stores 50 TB of video metadata in Cloud Storage buckets in the Standard class. Legal requires that records for a subset of titles be retained for exactly seven years and that they cannot be altered or deleted by any user, including project owners, during that period. The company also wants to minimize storage cost for a second bucket holding derived thumbnails that are accessed only a few times per year but must be retrievable within seconds. Which two configurations should the data engineer implement? (Choose two.)

Select 2 answers
A.Enable Object Versioning on the metadata bucket to preserve prior versions of each object.
B.Set an Object Lifecycle Management rule to move the metadata objects to Archive storage after one year.
C.Create the thumbnails bucket in the Coldline storage class and enable Turbo Replication.
D.Create a bucket-level retention policy with a seven-year retention period and lock it.
E.Create the thumbnails bucket in the Nearline storage class.
AnswersD, E

A locked bucket retention policy prevents any user, including project owners, from deleting or overwriting objects until the retention period elapses, which is exactly the immutability legal requires. Locking the policy makes it permanent and irreversible, so the seven-year period cannot be shortened, satisfying the WORM-style guarantee for the retained titles while keeping objects readable.

Why this answer

A locked bucket retention policy delivers the immutability legal demands because it cannot be removed once locked and blocks deletion or overwrite by any principal for the full seven years. For the thumbnails, Nearline matches an access pattern of a few reads per year with second-level retrieval and a lower price than Standard, avoiding the longer minimum-duration and slower-access profile that Coldline or Archive would impose.

Exam trap

The trap here is confusing versioning or lifecycle tiering with true immutability, when only a locked retention policy blocks even project owners from deleting or altering objects.

Practice this question →
12
Multi-Selectmedium

A company has a data lake on Cloud Storage with raw data in the 'raw' bucket, curated data in 'curated', and processed data in 'processed'. They want to implement lifecycle management to reduce costs. Which TWO actions should they take? (Choose 2)

Select 2 answers
A.Set a lifecycle rule to change storage class from Standard to Nearline after 30 days for the 'raw' bucket.
B.Enable object versioning on all buckets to automatically delete older versions.
C.Set a partition expiration on BigQuery tables that reference data in the 'processed' bucket.
D.Set a lifecycle rule to delete objects older than 365 days in the 'curated' and 'processed' buckets.
E.Set a lifecycle rule to change storage class from Standard to Archive after 30 days for the 'raw' bucket.
AnswersA, D

Raw data is typically accessed rarely after initial ingestion, so transitioning it from Standard to Nearline at 30 days cuts storage cost while preserving availability. This satisfies the stem's cost-reduction goal for the 'raw' bucket without affecting curated or processed data.

Why this answer

Option A is correct because a lifecycle rule that transitions objects in the 'raw' bucket from Standard to Nearline after 30 days is a valid cost-reduction strategy for raw data that is accessed infrequently but may still be needed; Nearline is designed for data accessed less than once a month. Option D is correct because setting a lifecycle rule to delete objects older than 365 days in the 'curated' and 'processed' buckets removes stale data that is no longer needed, directly reducing storage costs. Option B is incorrect because enabling object versioning does not automatically delete older versions; versioning preserves them and typically increases storage costs unless combined with a separate lifecycle deletion rule.

Option C is incorrect because partition expiration applies to BigQuery table partitions, not to objects in a Cloud Storage bucket, and the scenario is about Cloud Storage lifecycle management. Option E is incorrect because transitioning raw data to Archive after only 30 days is overly aggressive and would incur early-deletion charges and retrieval costs if the data is still needed, making it a poor fit compared with Nearline.

Exam trap

PDE often tests the misconception that enabling object versioning deletes old versions automatically, and the trap of choosing Archive too early without accounting for its 365-day minimum duration and retrieval costs.

Practice this question →
13
Multi-Selectmedium

A company is designing a Cloud Bigtable row key for a time-series dataset of device readings. They want to avoid hotspotting (uneven load across tablets). Which TWO row key design patterns are effective? (Choose 2)

Select 2 answers
A.Use a monotonically increasing counter as row key
B.Use timestamp directly as the first part of the row key
C.Reverse the timestamp string
D.Prepend a hash of the device ID to the timestamp
E.Use a secondary index on the timestamp column
AnswersC, D

Reversing the timestamp string makes the least significant digits the row key prefix, spreading sequential writes across many row ranges rather than concentrating them on one tablet. This satisfies the anti-hotspotting constraint for time-series ingestion.

Why this answer

Option C is correct because reversing the timestamp string (e.g., turning a lexicographically increasing timestamp into a decreasing one) prevents new writes from always landing on the last tablet, spreading sequential writes across the keyspace. Option D is correct because prepending a hash of the device ID to the timestamp distributes writes across many distinct key prefixes, so consecutive readings from different devices do not concentrate on a single tablet. Options A and B are incorrect because a monotonically increasing counter and a timestamp as the leading key component both produce sequential, ever-increasing keys that funnel all new writes to the last tablet, causing hotspotting.

Option E is incorrect because a secondary index on the timestamp column is not a row key design pattern and Bigtable does not support secondary indexes natively; it would not address hotspotting.

Practice this question →
14
MCQmedium

A company uses Cloud Spanner and needs to store a parent-child relationship where the child table is frequently queried together with the parent. The parent has millions of rows and the child billions. Which Spanner feature optimizes performance for this pattern?

A.Partitioned tables
B.Interleaved tables
C.Secondary indexes
D.Change streams
AnswerB

Interleaved tables physically co-locate child rows with their parent row in the same storage split, so parent-child joins avoid network hops. This satisfies the pattern where the child table is queried alongside its parent, since Spanner reads both from one locality rather than performing distributed lookups.

Why this answer

Interleaved tables in Cloud Spanner physically co-locate child rows with their parent rows on the same split, so a parent-child join can be satisfied with a single local lookup rather than a distributed join across nodes. This is ideal when the child table is frequently queried together with the parent, as in this scenario with millions of parents and billions of children. The interleaving declaration (INTERLEAVE IN PARENT) makes the child's primary key prefix the parent's key, enabling efficient prefix scans and reduced network hops.

Exam trap

The trap here is confusing interleaved tables with secondary indexes or partitioned tables; candidates often pick secondary indexes thinking they optimize joins, but only interleaving provides physical co-location for parent-child access patterns.

How to eliminate wrong answers

Option A is wrong because partitioned tables (partitioned DML or table partitioning concepts) are about managing large-scale data operations and DML, not about co-locating related parent-child rows for join performance. Option C is wrong because secondary indexes improve query performance for specific access patterns but do not co-locate child rows with parents; they add storage and write overhead without solving the parent-child locality problem. Option D is wrong because change streams capture data modifications for downstream processing (e.g., analytics, event-driven apps) and have nothing to do with optimizing parent-child query performance.

Practice this question →
15
MCQmedium

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?

A.Use Cloud Storage lifecycle rules to delete tables after 90 days
B.Create an IAM policy that revokes access after 90 days
C.Use BigQuery scheduled queries to delete tables older than 90 days
D.Set a default table expiration on each BigQuery dataset to 90 days
AnswerD

A default table expiration set on each dataset automatically deletes tables 90 days after creation, requiring no manual intervention. This satisfies the stem's automation constraint while enforcing the 90-day retention across all datasets in the project.

Why this answer

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.

Exam trap

A common mistake is applying Cloud Storage lifecycle rules to BigQuery tables. BigQuery has its own table expiration settings at the dataset level, which are distinct from Cloud Storage object lifecycle management.

How to eliminate wrong answers

Option A is wrong because Cloud Storage lifecycle rules apply to objects in Cloud Storage buckets, not to BigQuery tables; BigQuery tables are stored separately and cannot be managed by Cloud Storage lifecycle policies. Option B is wrong because IAM policies control access permissions, not data lifecycle; revoking access does not delete tables or reduce storage costs. Option C is wrong because BigQuery scheduled queries can delete tables but require writing and maintaining custom SQL scripts and scheduling logic, introducing complexity and potential failure points, whereas a default table expiration is a declarative, server-managed setting that requires no ongoing maintenance.

Practice this question →
16
MCQhard

A logistics company writes shipment telemetry to a Cloud Bigtable table using a row key of shipment_id, where shipment_id values are monotonically increasing and roughly sequential over time. Write throughput has become uneven, with a small number of tablet servers overloaded while others sit idle. The engineer must improve write distribution without changing the schema of the column families. What should the engineer do?

A.Create a Cloud Bigtable replication cluster in a second zone and write half the traffic to each cluster.
B.Increase the number of column families in the table to spread the write load across more storage structures.
C.Enable automatic region rebalancing so Bigtable redistributes the hot rows across the cluster.
D.Prepend a hashed or reversed component to the row key so writes spread across the key space instead of concentrating at the high end.
AnswerD

Sequential, monotonically increasing keys concentrate writes on the tablet holding the highest key range, creating a hotspot. Prepending a hash bucket or reversing the key distributes incoming writes across many tablets, letting all tablet servers share load. This preserves the column family schema and keeps keys unique, which is the standard fix for write hotspots caused by sequential row keys.

Why this answer

Bigtable distributes rows by row key ranges, so a monotonically increasing key sends every new write to the tablet owning the highest range, overloading one node while others idle. Adding a hash bucket prefix or reversing the key scatters writes across many ranges and tablets, restoring even load without altering column families. Automatic rebalancing, extra column families, and replication clusters all leave the underlying key concentration in place, so the hotspot persists.

Exam trap

The trap here is assuming Bigtable's automatic tablet rebalancing will fix a hotspot, when rebalancing cannot compensate for a row key that always targets the same key range.

Practice this question →
17
Multi-Selectmedium

A data engineer needs to restrict access to BigQuery datasets such that only data from approved VPC networks can query them. They also need to audit data access. Which two security controls should they implement? (Choose two.)

Select 2 answers
A.Cloud Audit Logs
B.Customer-managed encryption keys (CMEK)
C.Data Loss Prevention (DLP)
D.IAM roles
E.VPC Service Controls
AnswersA, E

Cloud Audit Logs record BigQuery API calls and data access events, capturing who queried which datasets and when. This satisfies the auditing half of the requirement, providing the access visibility needed alongside network-based restrictions to demonstrate compliance and investigate suspicious queries.

Why this answer

VPC Service Controls (E) is correct because it creates a service perimeter around BigQuery that restricts access to only approved VPC networks, blocking requests from outside the perimeter and mitigating data exfiltration. Cloud Audit Logs (A) is correct because it records BigQuery data access and administrative activity (Data Access audit logs must be explicitly enabled), providing the required audit trail. CMEK (B) only controls encryption key ownership, not network-based access or auditing.

DLP (C) discovers and classifies sensitive data but does not restrict network access or provide access auditing. IAM roles (D) grant permissions to identities but cannot enforce VPC network origin restrictions on BigQuery.

Exam trap

Google Cloud often tests the distinction between identity-based controls (IAM) and network-based controls (VPC Service Controls), leading candidates to mistakenly choose IAM roles when the requirement explicitly specifies restricting access by VPC network rather than by user identity.

Practice this question →
18
MCQhard

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?

A.Use a hashed prefix of the trade_id, e.g., [hash(trade_id)]#[trade_id]#[timestamp]
B.Use a single row key of [timestamp]
C.Increase the number of Bigtable nodes to 20
D.Change row key to [timestamp]#[trade_id]
AnswerA

Hashing the trade_id prefix scatters sequential writes across the entire key space, so consecutive trades land on different tablets rather than concentrating on one node. This directly resolves the hot-spotting constraint, since Cloud Bigtable distributes rows lexicographically by row key and a uniform hash prefix balances write load.

Why this answer

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.

Exam trap

A common trap in the Google Professional Data Engineer exam is confusing the order of key components. Simply reversing the order (e.g., putting the timestamp first) is often incorrectly thought to solve hot-spotting, but any monotonically increasing value at the start of the key will still cause a hotspot because Bigtable stores rows in lexicographic order.

How to eliminate wrong answers

Option B is wrong because using a single row key of [timestamp] would cause all writes with the same timestamp to collide on one row, creating an extreme hotspot and violating Bigtable's requirement for unique, distributed row keys. Option C is wrong because increasing the number of nodes does not fix a row key design flaw; Bigtable cannot rebalance writes if the row key pattern forces all traffic to a single tablet, and adding nodes only helps if the load is already distributed. Option D is wrong because reversing the order to [timestamp]#[trade_id] still places all writes with the same timestamp adjacent in lexicographic order, so recent timestamps will still hotspot on a single tablet; it does not introduce the randomness needed for distribution.

Practice this question →
19
MCQmedium

A company has a Cloud SQL for PostgreSQL instance and wants to create a read replica to offload read traffic from the primary. They also need to ensure the replica is in a different region for disaster recovery. Which Cloud SQL feature should they use?

A.External replica
B.Automatic backups with PITR
C.Cross-region read replica
D.High availability (HA) configuration
AnswerC

A cross-region read replica is created in a different region from the primary, replicating asynchronously over Google's network. This offloads read traffic while providing regional isolation for disaster recovery, which a same-region replica or standard read replica cannot deliver.

Why this answer

Cross-region read replicas in Cloud SQL for PostgreSQL allow you to create a replica in a different region from the primary instance. This offloads read traffic from the primary while also providing disaster recovery capabilities by maintaining a standby copy in a geographically separate location. The replica uses asynchronous replication to stay up-to-date with the primary.

Exam trap

A common mistake is to confuse high availability (HA) configuration, which only protects against zone-level failures, with cross-region read replicas that provide regional disaster recovery. HA does not replicate data to another region.

How to eliminate wrong answers

Option A is wrong because an external replica refers to a replica running outside of Cloud SQL, such as on a self-managed instance or on-premises, which does not meet the requirement for a managed Cloud SQL replica in a different region. Option B is wrong because automatic backups with point-in-time recovery (PITR) are used for data recovery from failures or corruption, not for offloading read traffic or providing a separate regional replica for disaster recovery. Option D is wrong because a high availability (HA) configuration uses synchronous replication within the same region (typically across zones) to provide failover, not a cross-region replica for disaster recovery or read offloading.

Practice this question →
20
MCQeasy

A data engineering team needs to run SQL analytics on a large BigQuery dataset from a Looker Studio dashboard. The dashboard must return results quickly, and the underlying data changes only once per day through a batch load. The team wants to minimize query cost and latency for repeated dashboard queries. What should they do?

A.Enable the BigQuery BI Engine reservation and rely on it to cache all dashboard queries automatically.
B.Export the dataset to Cloud Storage as CSV and have Looker Studio query the files directly.
C.Partition the table by ingestion date and require all dashboard queries to filter on that column.
D.Create a materialized view that aggregates the dataset and configure it to refresh on a daily schedule.
AnswerD

A materialized view stores precomputed results and can be refreshed automatically or on a schedule. Because the dashboard queries repeat and the source data changes daily, the view serves cached results, reducing bytes scanned and latency while lowering cost. BigQuery can also use the materialized view for compatible queries, making it well suited to this batch-refresh analytics pattern.

Why this answer

Materialized views precompute and store query results, and with a daily refresh schedule they match the batch update cadence. Repeated dashboard queries hit the stored results, cutting bytes scanned and latency. This directly targets the cost and performance goals, unlike caching layers or partitioning that do not precompute aggregations.

Exam trap

The trap here is assuming an in-memory acceleration layer replaces precomputation, when a materialized view is what actually reduces scanned bytes for repeated aggregate queries.

Practice this question →
21
MCQmedium

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?

A.Cloud SQL with cross-region replicas
B.Firestore
C.Cloud Bigtable
D.Cloud Spanner
AnswerD

Cloud Spanner provides external consistency globally through TrueTime, and its multi-region configurations carry a 99.999% availability SLA. That combination of strong consistency, cross-continent distribution and five-nines uptime matches the OLTP high-throughput requirement, unlike eventually consistent alternatives.

Why this answer

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.

Exam trap

Candidates often confuse Cloud SQL cross-region replicas as providing strong consistency, but those replicas are eventually consistent due to asynchronous replication, and they do not meet the 99.999% SLA.

How to eliminate wrong answers

Option A is wrong because Cloud SQL with cross-region replicas uses asynchronous replication, which cannot guarantee strong consistency across regions and offers only a 99.95% SLA for regional instances, not 99.999%. Option B is wrong because Firestore is a NoSQL document database that provides strong consistency only within a single region; its multi-region mode uses eventual consistency for global reads, and it lacks the ACID transaction support needed for high-throughput OLTP across continents. Option C is wrong because Cloud Bigtable is a wide-column NoSQL database designed for analytical workloads with high throughput, but it does not support SQL queries, ACID transactions, or strong consistency across regions; it offers only single-row transactions and eventual consistency for multi-cluster replication.

Practice this question →
22
MCQmedium

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

A.Cloud Spanner
B.Cloud SQL for PostgreSQL
C.BigQuery
D.AlloyDB
AnswerD

AlloyDB is a PostgreSQL-compatible, Google-managed database engine with a columnar acceleration engine that runs hybrid transactional and analytical workloads at high performance, satisfying both the PostgreSQL compatibility and HTAP constraints. Cloud SQL and standard PostgreSQL lack this integrated analytical acceleration.

Why this answer

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.

Exam trap

Google often tests the distinction between 'PostgreSQL-compatible' and 'PostgreSQL-based' — candidates mistakenly choose Cloud SQL for PostgreSQL because it is a managed PostgreSQL service, but they overlook the specific requirement for hybrid transactional and analytical workloads, which AlloyDB uniquely addresses with its integrated columnar engine.

How to eliminate wrong answers

Option A is wrong because Cloud Spanner is a globally distributed, strongly consistent relational database that is not PostgreSQL-compatible (it uses GoogleSQL or standard SQL with Spanner-specific extensions) and is optimized for horizontal scaling across regions, not for hybrid transactional/analytical workloads with PostgreSQL compatibility. Option B is wrong because Cloud SQL for PostgreSQL is a fully managed PostgreSQL service but is designed primarily for transactional (OLTP) workloads and lacks the built-in columnar engine and analytical acceleration needed for hybrid workloads, resulting in significantly slower analytical queries. Option C is wrong because BigQuery is a serverless, highly scalable data warehouse for analytical (OLAP) workloads, not a transactional database, and it is not PostgreSQL-compatible (it uses BigQuery SQL).

Practice this question →
23
MCQeasy

A healthcare analytics team must store patient records in BigQuery and prove to auditors that no one can read or modify the data without an explicit, logged authorization. They want to enforce column-level access so that only the billing team can see insurance_id, while the research team sees only de-identified columns. Which BigQuery feature should they implement?

A.Policy tags in a data taxonomy applied to the insurance_id column, with column-level access granted to the billing team.
B.Authorized views that join the patient table with a lookup table to mask insurance_id.
C.Row-level security policies that filter rows based on the SESSION_USER() function.
D.Customer-managed encryption keys stored in Cloud KMS and rotated every 90 days.
AnswerA

Policy tags let you classify sensitive columns and grant access through Data Catalog column-level IAM. Only principals with the fine-grained reader role on the policy tag can query insurance_id, and all access is logged. This enforces column-level access at the table level, so the research team is blocked from that column even if they can query the table, meeting the audit requirement.

Why this answer

Policy tags in a data taxonomy provide column-level access control in BigQuery. Applying a policy tag to insurance_id and granting the fine-grained reader role only to the billing team ensures the research team cannot query that column even though they can access the table. BigQuery logs policy tag enforcement, supporting the auditor's requirement for explicit, logged authorization.

Other features address rows, encryption, or views but not column-level authorization.

Exam trap

The trap here is equating encryption key management with access control, when keys protect data at rest rather than restricting column reads.

Practice this question →
24
Multi-Selectmedium

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

Select 2 answers
A.BigLake requires data to be loaded into BigQuery storage.
B.BigLake tables allow querying data in Cloud Storage using BigQuery without loading into BigQuery storage.
C.BigLake only supports structured data in BigQuery storage.
D.BigLake supports row-level and column-level security.
E.BigLake automatically converts CSV files to Parquet.
AnswersB, D

BigLake's external table capability lets BigQuery query Cloud Storage data in place, so no ingestion into BigQuery-managed storage is needed. This directly satisfies the stem's data lake design, where unified governance across open formats matters more than duplicating data, and it underpins the federated query pattern the scenario requires.

Why this answer

Option B is correct because BigLake tables are external tables that let BigQuery query data residing in Cloud Storage (Parquet, ORC, Avro, JSON, CSV, Iceberg, Delta) directly, without ingesting it into BigQuery's native storage. Option D is correct because BigLake extends BigQuery's fine-grained governance to external data, supporting row-level security and column-level security (via policy tags in Data Catalog) so access controls apply uniformly across the lakehouse. Option A is wrong because BigLake's defining feature is avoiding the load into BigQuery storage; that describes native BigQuery tables.

Option C is wrong because BigLake handles structured and semi-structured formats in Cloud Storage, not only structured data in BigQuery storage. Option E is wrong because BigLake does not perform automatic format conversion of CSV to Parquet; it queries files in their existing format.

Exam trap

A common misconception is that BigLake requires data to be loaded into BigQuery storage, when in fact it is designed to query external data in Cloud Storage directly.

Practice this question →
25
MCQeasy

A startup runs an operational analytics dashboard on a Cloud SQL for MySQL instance. The dashboard issues many short read queries that are slowing down the write-heavy transactional workload on the primary instance. The team wants to offload reads without changing application write logic and without re-architecting to a different database engine. What should the data engineer configure?

A.Create one or more read replicas of the Cloud SQL instance and direct the dashboard's read queries to the replica connection.
B.Enable high availability on the primary instance so a standby instance can absorb the dashboard's read queries.
C.Migrate the dashboard to query a BigQuery federated external table that reads directly from the Cloud SQL instance.
D.Increase the number of vCPUs and memory on the primary instance to handle the combined read and write load.
AnswerA

Cloud SQL read replicas serve read-only traffic from a separate instance that receives asynchronous replication from the primary. Pointing the dashboard at the replica endpoint removes those read queries from the primary, restoring write throughput, while the application's write path continues to target the primary unchanged. This is the standard, minimal-change way to scale reads for Cloud SQL.

Why this answer

Read replicas are the intended Cloud SQL feature for scaling read traffic: the dashboard connects to a replica endpoint, the transactional application keeps writing to the primary, and no write-path code changes are required. A high-availability standby is passive and cannot serve reads, vertical scaling keeps both workloads on the same instance, and federated BigQuery queries still hit the source database. The replica approach isolates the dashboard's short read queries from the write-heavy workload.

Exam trap

The trap here is confusing a Cloud SQL high-availability standby, which only activates for failover, with a read replica, which actively serves read-only queries.

Practice this question →
26
MCQeasy

A mobile app needs an offline-first NoSQL database that syncs data across devices when connectivity is available. Which Google Cloud database meets these requirements?

A.Memorystore
B.Cloud SQL
C.Bigtable
D.Firestore
AnswerD

Firestore provides offline persistence through local caching, letting mobile apps read and write without connectivity, then synchronises changes automatically once the device reconnects. Its real-time listeners and multi-device sync satisfy the offline-first NoSQL requirement directly, unlike Cloud SQL or Bigtable, which demand continuous network access.

Why this answer

Firestore is a NoSQL, serverless, offline-first database that automatically syncs data across devices when connectivity is restored. It provides built-in offline persistence and real-time synchronization, making it ideal for mobile apps that need to work offline and sync later.

Exam trap

The trap here is that candidates may confuse Google Bigtable's NoSQL label with a mobile-friendly NoSQL database, overlooking that Bigtable is designed for high-throughput analytical workloads, not for offline-first mobile sync with real-time listeners.

How to eliminate wrong answers

Option A is wrong because Memorystore is a fully managed in-memory cache (Redis/Memcached) designed for caching and session storage, not a persistent NoSQL database with offline sync capabilities. Option B is wrong because Cloud SQL is a relational (SQL) database, not a NoSQL database, and it does not offer offline-first or cross-device sync features. Option C is wrong because Bigtable is a wide-column NoSQL database optimized for large analytical workloads, not for mobile app offline sync with real-time updates.

Practice this question →
27
MCQhard

A retail analytics team uses BigQuery to store point-of-sale transactions in a partitioned table. They need to optimize query performance for dashboards that filter on store_id and product_category, and they want to minimize the amount of data scanned. The table is partitioned by transaction_date and has a clustered column of store_id. Queries frequently filter on product_category as well. Which change should the data engineer make to improve performance?

A.Create a materialized view that pre-aggregates sales by store_id and product_category
B.Add product_category as a second clustering column after store_id
C.Change the partitioning column to product_category and keep store_id as the clustered column
D.Convert the table to use time-unit partitioning on transaction_date with a daily granularity and remove clustering
AnswerB

BigQuery supports up to four clustering columns, and the order matters: data is sorted by the first column, then the second, and so on. Adding product_category as a second clustering column allows BigQuery to prune blocks more effectively when queries filter on both store_id and product_category. This reduces the amount of data scanned and improves dashboard performance without changing the partitioning strategy, which remains optimal for date filters.

Why this answer

BigQuery clustering sorts data within partitions by the specified columns, and queries that filter on those columns can skip blocks. Adding product_category as a second clustering column after store_id lets the engine prune more effectively when both columns appear in filters. The partitioning column remains transaction_date, which is ideal for date-range dashboards.

This change reduces bytes scanned and improves latency without altering the partitioning scheme.

Exam trap

The trap here is thinking that clustering columns must be unique or that you can only cluster on one column, when BigQuery actually supports up to four clustering columns and order affects pruning efficiency.

Practice this question →
28
MCQeasy

An organization needs to store transactional data for a global e-commerce platform with strong consistency across regions and an SLA of 99.999% availability. The application requires SQL semantics with horizontal scaling. Which Google Cloud database should they choose?

A.Firestore
B.Cloud SQL
C.Cloud Spanner
D.Cloud Bigtable
AnswerC

Cloud Spanner is the only Google Cloud database delivering external strong consistency alongside horizontal scaling via TrueTime, satisfying the 99.999% SLA and SQL semantics required. Regional Cloud SQL or Firestore cannot scale horizontally while preserving global transactional consistency.

Why this answer

Cloud Spanner is the correct choice because it provides globally distributed, strongly consistent SQL semantics with horizontal scaling and a 99.999% availability SLA. It uses synchronous replication and the TrueTime API to ensure external consistency across regions, meeting the strict consistency and uptime requirements of a global e-commerce platform.

Exam trap

The trap here is that candidates often confuse Cloud Spanner with Cloud SQL, assuming any SQL database can scale horizontally, but Cloud SQL is a single-region, vertically scaled service, while Cloud Spanner is the only Google Cloud database that combines SQL, horizontal scaling, and global strong consistency with a 99.999% SLA.

How to eliminate wrong answers

Option A is wrong because Firestore is a NoSQL document database that does not support SQL semantics; it offers strong consistency only within a single region and lacks the global consistency and 99.999% SLA required. Option B is wrong because Cloud SQL is a traditional relational database that supports SQL but cannot horizontally scale across regions; it is limited to a single region and provides up to 99.95% availability, not 99.999%. Option D is wrong because Cloud Bigtable is a NoSQL wide-column database that does not support SQL semantics and provides only eventual consistency, not the strong consistency required for transactional data.

Practice this question →
29
MCQmedium

A retail analytics team stores transaction data in a BigQuery table partitioned by day on a DATE column named transaction_date. Analysts repeatedly run queries that filter on transaction_date and also on a high-cardinality STRING column named store_id. These queries scan far more data than expected, and slot usage is high. The team wants to reduce bytes processed without changing query results. What should they do?

A.Create a clustered table on store_id, so BigQuery can prune blocks within each transaction_date partition.
B.Create a materialized view that precomputes all transactions grouped by store_id and transaction_date.
C.Export the table to Cloud Storage as Avro and query it with an external table that applies store_id predicates.
D.Change the table to require a partition filter, forcing analysts to always specify transaction_date.
AnswerA

Clustering on store_id sorts data within each partition into storage blocks, and BigQuery uses the cluster column to skip blocks that cannot match the store_id filter. Because queries already filter on transaction_date, partition pruning still applies, and adding clustering further reduces scanned bytes. This matches the requirement of lowering bytes processed without changing results, and clustering can be added to an existing partitioned table.

Why this answer

Clustering a partitioned table on the frequently filtered high-cardinality column lets BigQuery prune storage blocks within each partition. Partition pruning on transaction_date still happens, and cluster pruning further limits scanned data. This directly reduces bytes processed and slot consumption for the described queries without altering results, making it the appropriate storage optimization.

Exam trap

The trap here is assuming that partitioning alone handles every filter predicate, when partitioning only prunes by the partition column and a second high-cardinality filter needs clustering.

Practice this question →
30
MCQeasy

A retail company uploads daily sales CSV files to a Cloud Storage bucket. The analytics team wants BigQuery to automatically detect the schema and make new files queryable without running load jobs. The files are added with date-based prefixes, and the team wants to minimize operational overhead. Which BigQuery feature should they use?

A.A Cloud Dataflow streaming pipeline that writes rows into BigQuery.
B.A BigQuery native table loaded with a recurring scheduled query.
C.A BigQuery table partitioned by ingestion time with a load job per file.
D.A BigQuery external table with schema autodetect over the Cloud Storage prefix.
AnswerD

BigQuery external tables can point at a Cloud Storage URI prefix, and with autodetect BigQuery infers column names and types from the files. New files matching the prefix become queryable without load jobs, which matches the low-overhead requirement and the date-prefixed upload pattern.

Why this answer

An external table over the Cloud Storage prefix with schema autodetect lets BigQuery read the CSV files directly. New files that match the prefix appear in query results without any load jobs, which precisely fits the requirement to minimize operational overhead while keeping daily files queryable.

Exam trap

The trap here is reaching for a load job or pipeline when an external table with autodetect already reads new files in place with no ingestion operations.

Practice this question →
31
MCQeasy

A marketing team needs to run ad-hoc SQL queries on terabytes of clickstream data stored in Parquet files in Cloud Storage. They want a serverless solution with no cluster management and the ability to query external data without loading. Which service should they use?

A.Cloud SQL
B.AlloyDB
C.Dataproc with Spark SQL
D.BigQuery with external tables
AnswerD

BigQuery external tables query Parquet in Cloud Storage directly, so no data loading occurs and no cluster is managed — fully serverless. This satisfies both stated constraints: ad-hoc SQL over terabytes and querying external data in place.

Why this answer

BigQuery with external tables allows querying data stored in Cloud Storage (including Parquet files) without loading it into BigQuery storage, providing a serverless, fully managed solution with no cluster management. This matches the requirement for ad-hoc SQL queries on terabytes of clickstream data in Parquet format, as BigQuery automatically scales compute and storage.

Exam trap

The trap here is that candidates may choose Dataproc with Spark SQL (Option C) because it can query Parquet files, but they overlook the 'serverless' and 'no cluster management' requirement, which BigQuery satisfies natively without any cluster provisioning.

How to eliminate wrong answers

Option A is wrong because Cloud SQL is a fully managed relational database for OLTP workloads, not designed for petabyte-scale analytical queries on external Parquet files, and requires data to be loaded into its storage. Option B is wrong because AlloyDB is a PostgreSQL-compatible database optimized for transactional and hybrid workloads, not a serverless query engine for external data in Cloud Storage, and it requires data to be imported. Option C is wrong because Dataproc with Spark SQL requires cluster management (even if ephemeral) and is not serverless; it also involves provisioning and scaling clusters, contradicting the 'no cluster management' requirement.

Practice this question →
32
MCQeasy

A media company stores final video masters in a Cloud Storage bucket. Regulatory rules require that each object be unalterable for seven years, and the company must be able to prove retention compliance to auditors. Objects are written once and never edited. The data engineer needs the strongest native Cloud Storage control that prevents deletion or overwrite for the required period. What should the engineer configure?

A.A bucket retention policy with a seven-year retention period and a locked retention policy.
B.Uniform bucket-level access with IAM roles limited to a single compliance group.
C.Object Versioning on the bucket plus a lifecycle rule to delete noncurrent versions.
D.A signed URL with a seven-year expiration that is shared only with the compliance team.
AnswerA

A bucket retention policy prevents deletion or replacement of objects until the retention period elapses, and locking the policy makes it permanent so it cannot be shortened or removed. This gives the unalterable, provable seven-year guarantee the auditors require, and it is the native Cloud Storage control designed for exactly this write-once retention need.

Why this answer

A locked bucket retention policy is the native Cloud Storage feature that enforces immutability for a fixed period and cannot be weakened once locked. It directly satisfies the write-once, provable-retention requirement, whereas versioning, signed URLs, and IAM policies only govern recovery or access. For regulatory retention of final masters, the locked retention policy is the correct control.

Exam trap

The trap here is confusing access control or versioning with true immutability, when only a locked retention policy legally prevents deletion.

Practice this question →
33
MCQhard

An organization wants to enforce that data in a Cloud Storage bucket cannot be deleted or overwritten for 7 years due to regulatory compliance. Which Cloud Storage feature should they use?

A.Retention Policy with Bucket Lock
B.IAM conditions
C.Object Lifecycle Management
D.Object holds
AnswerA

A retention policy combined with Bucket Lock prevents objects from being deleted or overwritten until the retention period expires, enforcing immutability for the full seven years. This satisfies the regulatory requirement, whereas lifecycle rules and versioning permit deletion or alteration.

Why this answer

Retention Policy with a retention period ensures objects cannot be deleted or overwritten during that period. Bucket Lock makes the policy permanent. Object holds are per-object.

Lifecycle management automates transitions/deletions, opposite of retention.

Practice this question →
34
MCQhard

A financial services company must retain trade records for seven years in Cloud Storage. Regulators require that no object can be deleted or overwritten before its retention period expires, even by project owners, and that the policy cannot be removed. The company also needs to prove compliance during audits. Which combination of controls should they implement?

A.Enable Object Versioning and apply an Organization Policy that denies storage.objects.delete for all principals.
B.Configure a lifecycle rule that transitions objects to Archive storage after 30 days and deletes them after seven years.
C.Use Customer-Managed Encryption Keys in Cloud KMS and revoke key access after each upload.
D.Set a bucket retention policy and lock it, then upload objects with a retention period covering seven years.
AnswerD

A bucket retention policy prevents deletion or replacement of objects until their retention period expires, and once the policy is locked it cannot be removed or shortened. This provides the WORM behavior regulators require and produces audit evidence. Applying it to the bucket and setting object retention for seven years ensures every trade record is protected for the mandated duration, even from project owners.

Why this answer

A locked bucket retention policy enforces WORM semantics: objects cannot be deleted or overwritten until their retention period elapses, and the lock prevents removal or reduction of the policy. Uploading records with a seven-year retention period satisfies the regulatory timeline and creates auditable proof. Other controls either can be changed, do not block deletion, or address confidentiality rather than immutability.

Exam trap

The trap here is confusing encryption key control or lifecycle rules with immutability, when only a locked retention policy prevents deletion and modification for a fixed period.

Practice this question →
35
MCQmedium

A data engineer is designing a BigQuery table for a fraud detection system. The table will store 500 TB of transaction records and will be queried constantly by filtering on a transaction timestamp column and a customer_id column. The engineer needs to minimize the amount of data scanned by these queries. What should the engineer do?

A.Create a materialized view that pre-aggregates transactions by customer_id and timestamp.
B.Use the BigQuery Storage Write API to stream data into the table with a fixed schema.
C.Set the table's expiration time to 30 days to limit the amount of historical data stored.
D.Partition the table by the transaction timestamp column and cluster by the customer_id column.
AnswerD

Partitioning by transaction timestamp restricts scans to the relevant time ranges, and clustering by customer_id further reduces the data read within each partition. This combination is the standard BigQuery optimization for high-volume tables filtered on time and a high-cardinality key, directly minimizing bytes scanned and cost.

Why this answer

Partitioning by transaction timestamp and clustering by customer_id is the recommended approach for large BigQuery tables that are frequently filtered on a time column and a high-cardinality key. Partition pruning limits scanned partitions, and clustering sorts data within partitions so that filters on customer_id read fewer blocks, lowering both cost and latency.

Exam trap

The trap here is assuming that any performance feature, such as a materialized view or the Storage Write API, will reduce bytes scanned, when only partitioning and clustering directly affect query scan cost.

Practice this question →
36
MCQhard

A data engineer is loading a 2 TB CSV dataset from Cloud Storage into a partitioned BigQuery table. The load job fails with an error indicating too many errors during parsing. The source files have inconsistent column counts and some rows contain embedded newlines. The engineer wants to load the data reliably with minimal preprocessing. What should the engineer do?

A.Load the CSV files into a single external table and query it with the ignoreUnknownValues option.
B.Set the maxBadRecords option to a high value and rerun the load job with the same CSV files.
C.Convert the CSV files to newline-delimited JSON or Parquet, then load the converted files into BigQuery.
D.Increase the load job's maximum parallelism by splitting the CSV files into smaller objects in Cloud Storage.
AnswerC

Newline-delimited JSON and Parquet handle embedded newlines and variable schemas without relying on delimiter-based parsing. Converting the source removes the ambiguity that caused the load failure, and BigQuery loads these formats natively with schema autodetection or an explicit schema, yielding reliable ingestion with minimal manual row fixing.

Why this answer

CSV parsing is delimiter-based and breaks when rows contain embedded newlines or inconsistent column counts. Converting to newline-delimited JSON or Parquet removes delimiter ambiguity and lets BigQuery load the data reliably, addressing the root cause rather than suppressing errors or increasing throughput.

Exam trap

The trap here is treating the load error as a throughput or error-tolerance problem, when the real issue is that CSV parsing cannot represent embedded newlines without careful quoting.

Practice this question →
37
MCQeasy

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?

A.Firestore
B.Cloud Bigtable
C.Cloud SQL
D.BigQuery
AnswerD

BigQuery suits this scenario because its serverless, columnar architecture executes complex analytical SQL across terabyte datasets without infrastructure provisioning, satisfying the no-management constraint. Its distributed query engine returns results in seconds to minutes, matching the stated latency tolerance, unlike transactional row-store databases or single-node engines.

Why this answer

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.

Exam trap

Google Cloud exams often test the distinction between OLTP databases (Cloud SQL, Firestore, Bigtable) and OLAP/data warehouse services (BigQuery), where candidates mistakenly choose Cloud SQL for analytical workloads due to its SQL familiarity, ignoring its scalability and performance limitations for large-scale analytics.

How to eliminate wrong answers

Option A is wrong because Firestore is a NoSQL document database optimized for real-time mobile and web app data with low-latency reads/writes, not for complex analytical queries on terabytes of data. Option B is wrong because Cloud Bigtable is a wide-column NoSQL database designed for high-throughput, low-latency operational workloads (e.g., time-series, IoT), not for complex analytical queries that require SQL-like joins and aggregations. Option C is wrong because Cloud SQL is a managed relational database for OLTP workloads with limited scalability (up to ~30 TB), and running complex analytical queries on terabytes of data would cause performance bottlenecks and require manual sharding or read replicas.

Practice this question →
38
MCQhard

A financial services company stores transaction logs in Cloud Storage. Regulatory requirements mandate that each object be retained for exactly seven years and that no user, including project owners, can delete or modify the objects during that period. The company wants to enforce this with minimal administrative overhead. What should they do?

A.Enable Uniform Bucket-Level Access and grant only the Storage Object Viewer role to all users.
B.Create a bucket with a retention policy of seven years and lock the policy.
C.Configure a Cloud Storage bucket lock using a bucket policy with a seven-year retention period.
D.Use Object Lifecycle Management to transition objects to Archive storage after 30 days and delete after seven years.
AnswerB

A locked retention policy enforces a minimum retention period during which objects cannot be deleted or overwritten, even by project owners. Locking the policy makes it permanent and irreversible, satisfying the WORM requirement. This is a native Cloud Storage feature that requires no custom code or external tooling, minimizing administrative overhead while meeting the exact seven-year retention mandate.

Why this answer

A locked retention policy on a Cloud Storage bucket enforces a minimum retention period and prevents deletion or modification of objects, even by project owners. Locking the policy makes it permanent, satisfying the seven-year WORM requirement. Lifecycle rules, IAM restrictions, or a nonexistent 'bucket policy' cannot provide the same immutable guarantee, making the locked retention policy the correct and minimal-overhead solution.

Exam trap

The trap here is confusing lifecycle management or IAM restrictions with true immutability, which only a locked retention policy provides.

Practice this question →
39
MCQmedium

A data engineer wants to create a data lake on Google Cloud for storing raw streaming data, then transform it into curated and processed zones for analytics. The data is in Avro format and will be queried by BigQuery. Which two services are MOST suitable as the primary storage and query interface?

A.Cloud Storage and BigQuery
B.Cloud Storage and Dataproc
C.Cloud Storage and Cloud SQL
D.Cloud Storage and Firestore
AnswerA

Cloud Storage provides the durable object store for raw Avro files, satisfying the data lake requirement, while BigQuery queries external data directly via its native Avro support and federated querying. This pairing separates cheap storage from serverless analytics, matching the raw-to-curated zone transformation the stem demands.

Why this answer

Cloud Storage is the most suitable primary storage for a data lake because it provides scalable, durable, and cost-effective object storage for raw Avro data. BigQuery is the ideal query interface because it can directly query Avro files stored in Cloud Storage using external tables, and it supports serverless analytics without needing to manage infrastructure.

Exam trap

Common misconception: candidates often think a processing engine like Dataproc is required to query Avro data in a data lake, but BigQuery can natively query Avro files stored in Cloud Storage without the need for intermediate processing.

How to eliminate wrong answers

Option B is wrong because Dataproc is a managed Spark/Hadoop service for batch processing, not a primary query interface for ad-hoc analytics; it would add unnecessary complexity and latency compared to BigQuery's direct Avro querying. Option C is wrong because Cloud SQL is a relational database for transactional workloads, not designed for large-scale analytics on Avro data in a data lake, and it cannot directly query Avro files. Option D is wrong because Firestore is a NoSQL document database for real-time applications, not suitable for analytical queries on large volumes of streaming data in Avro format.

Practice this question →
40
MCQhard

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?

A.Use Cloud SQL federated query to read Parquet from GCS
B.Create an external table definition using a JSON schema file referencing Cloud Storage URIs
C.Use the bq load command with --source_format=PARQUET
D.Set up BigLake to create a BigQuery external table
AnswerB, D

External table definitions point to GCS, query data in place.

Why this answer

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.

Exam trap

The Google Professional Data Engineer exam often tests the distinction between loading data (bq load) and creating external tables (bq mk --external_table_definition), where candidates mistakenly choose the load command because they think it can also create external references.

How to eliminate wrong answers

Option A is wrong because Cloud SQL federated query is designed for querying Cloud SQL databases, not for reading Parquet files from Cloud Storage; it does not support Parquet as a data source. Option C is wrong because the `bq load` command loads data into BigQuery tables, not creating external tables; it physically moves the data into BigQuery storage, which contradicts the requirement to query without loading. Option D is wrong because BigLake is a separate service for managing data lakes with fine-grained access control, but creating a BigQuery external table directly via the console or API (with a schema definition) is the standard method; BigLake is not required for this simple external table use case.

Practice this question →
41
MCQeasy

A media company stores video files in a Cloud Storage bucket and wants to serve them to users through a custom domain over HTTPS. The files are not sensitive, but the company wants to reduce egress cost and latency for users distributed globally. The company also wants to avoid managing SSL certificates manually. What should the data engineer recommend?

A.Create a Cloud CDN backend bucket with the Cloud Storage bucket as the origin and enable Google-managed SSL certificates on the load balancer.
B.Enable Object Versioning on the bucket and configure a lifecycle rule to move objects to Nearline after 30 days.
C.Set the bucket's default storage class to Standard and enable uniform bucket-level access.
D.Generate signed URLs for each video and distribute them to users through the application.
AnswerA

Cloud CDN caches content at edge locations, reducing latency and egress cost for globally distributed users. Using a backend bucket with the Cloud Storage bucket as origin, combined with a global external Application Load Balancer and Google-managed certificates, provides HTTPS on a custom domain without manual certificate management.

Why this answer

Cloud CDN with a backend bucket caches video content at Google's edge locations, lowering latency and egress for global users. Pairing it with a global external Application Load Balancer and Google-managed SSL certificates delivers HTTPS on a custom domain without the operational burden of managing certificates.

Exam trap

The trap here is focusing on storage class or access control features, which affect cost and permissions but do nothing for global delivery latency or custom domain HTTPS.

Practice this question →
42
MCQmedium

A company has a Cloud SQL for PostgreSQL instance that must be highly available across zones with automatic failover. They also need a read replica for reporting workloads. Which configuration should they use?

A.Enable high availability (regional) and create a read replica
B.Deploy Cloud SQL in multi-region mode
C.Enable automatic backups and point-in-time recovery
D.Create a cross-region replica and use it for failover
AnswerA

A regional configuration replicates the primary across zones and provides automatic failover, while a separate read replica offloads reporting queries without competing with transactional traffic. Both stated requirements — cross-zone high availability and reporting capacity — are satisfied simultaneously.

Why this answer

Cloud SQL for PostgreSQL supports regional high availability (HA) with synchronous replication across two zones, ensuring automatic failover without data loss. Additionally, you can create a read replica in a different zone or region to offload reporting workloads without impacting the primary instance's performance. This combination meets both the high availability and read replica requirements.

Exam trap

The trap here is confusing high availability (automatic zonal failover) with disaster recovery (manual cross-region promotion), and assuming that a read replica can serve as a failover target without understanding that Cloud SQL read replicas use asynchronous replication and are not suitable for automatic failover.

How to eliminate wrong answers

Option B is wrong because Cloud SQL does not support a 'multi-region mode'; it offers regional HA (zonal failover) and cross-region replicas, but not a multi-region deployment like Spanner. Option C is wrong because automatic backups and point-in-time recovery provide data protection and restore capabilities, but they do not provide automatic failover or a read replica for reporting. Option D is wrong because a cross-region replica can be promoted for failover, but it is not designed for automatic failover; promoting a cross-region replica is a manual process and does not provide the same synchronous replication and automatic failover as regional HA.

Practice this question →
43
MCQmedium

A logistics company ingests 5 TB of shipment telemetry per day into a BigQuery table named events_raw. Analysts run exploratory queries that scan the full table, which is now 900 TB, and costs are rising quickly. Most queries filter on the event_date column, which is a DATE field, and on the device_id column, but the table is not partitioned or clustered. The company wants to reduce bytes scanned without changing the query patterns or losing any historical events. Which combination of BigQuery table settings should the data engineer implement?

A.Partition events_raw by event_date and cluster by device_id.
B.Set a table expiration of 30 days on events_raw and rely on time travel for older data.
C.Cluster events_raw by event_date and device_id without partitioning.
D.Create a materialized view that aggregates events_raw by event_date and device_id.
AnswerA

Partitioning on event_date lets the query engine prune partitions when a date filter is present, while clustering on device_id sorts and colocation-stores rows within each partition so filters on device_id scan fewer blocks. Together they reduce bytes billed for the filters the analysts already use, and no rows are removed, so all historical events remain available.

Why this answer

Partitioning by a DATE column enables partition pruning whenever queries filter on that column, and clustering on a high-cardinality filter column such as device_id reduces the blocks scanned inside each partition. Because the requirement is to keep all events and not rewrite queries, this physical layout change is the right fit. Aggregation views and expiration policies do not address the full-scan cost while preserving raw history.

Exam trap

The trap here is assuming clustering alone gives the same pruning as partitioning, when only partitioning can eliminate entire date ranges from a scan.

Practice this question →
44
MCQeasy

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?

A.Cloud Bigtable
B.Cloud SQL
C.Firestore
D.Cloud Spanner
AnswerB

Cloud SQL delivers the required ACID transactions, automatic backups and point-in-time recovery, and comfortably handles workloads below 10,000 QPS. Its managed MySQL, PostgreSQL and SQL Server engines suit transactional e-commerce data, unlike analytics-oriented or NoSQL alternatives that lack full relational ACID guarantees.

Why this answer

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.

Exam trap

The trap here is that candidates often choose Cloud Spanner for any workload requiring ACID transactions and high availability, overlooking that Cloud SQL is sufficient and more cost-effective for sub-10,000 QPS workloads, and that Cloud Spanner's global distribution and strong consistency come with a significant price premium.

How to eliminate wrong answers

Option A is wrong because Cloud Bigtable is a NoSQL, wide-column database designed for high-throughput analytical workloads (millions of QPS) and does not support ACID transactions or SQL queries, making it unsuitable for transactional e-commerce data. Option C is wrong because Firestore is a NoSQL document database that, while supporting transactions, does not offer the full ACID guarantees across multiple documents in the same way as a relational database, and its automatic backup and PITR capabilities are limited compared to Cloud SQL. Option D is wrong because Cloud Spanner is a globally distributed, horizontally scalable relational database that supports ACID transactions and PITR, but it is overkill and significantly more expensive for a workload under 10,000 QPS, which can be handled more cost-effectively by Cloud SQL.

Practice this question →
45
MCQmedium

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?

A.Firestore backups
B.Cloud Storage with Object Lock
C.BigQuery table snapshots
D.Cloud Storage with retention policies
AnswerD

Retention policies are bucket-level, not per-object. Object Lock is needed for WORM.

Why this answer

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.

Exam trap

Google often tests the distinction between bucket-level retention policies and object-level retention controls. Candidates may incorrectly assume a separate 'Object Lock' feature exists, but in Google Cloud, WORM compliance is implemented using Cloud Storage retention policies (bucket-level) combined with object retention and legal holds (object-level).

How to eliminate wrong answers

Option A is wrong because Firestore backups are designed for Firestore databases and do not support WORM compliance or object-level retention policies; they are intended for point-in-time recovery of Firestore data, not for long-term archival with immutable storage. Option C is wrong because BigQuery table snapshots are used for preserving table data at a specific point in time for querying or recovery, but they do not provide WORM compliance or object-level retention policies; they are not a storage service for backup files. Option D is wrong because Cloud Storage with retention policies applies a uniform retention period to all objects in a bucket, but it does not support object-level retention policies; Object Lock is required for granular, per-object retention settings and legal holds.

Practice this question →
46
MCQhard

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?

A.Bucket Lock with a retention policy of 7 years.
B.Requester Pays bucket setting.
C.Versioning enabled with Object Hold.
D.Object lifecycle management with a delete rule after 7 years.
AnswerA

Bucket Lock applies a retention policy that prevents objects from being deleted or overwritten until the specified period elapses, directly enforcing the seven-year compliance requirement. It is the Cloud Storage mechanism designed for immutable, tamper-resistant retention.

Why this answer

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.

Exam trap

Google often tests the distinction between features that prevent deletion (Bucket Lock) versus features that only track versions or automate cleanup (Versioning, Lifecycle), leading candidates to choose Versioning or Lifecycle rules thinking they enforce immutability.

How to eliminate wrong answers

Option B is wrong because Requester Pays shifts storage costs to the requester but does not prevent deletion or overwriting of objects. Option C is wrong because Versioning enabled with Object Hold can prevent deletion of specific object versions but does not prevent overwriting of the current version, and holds are not a bucket-wide immutable policy. Option D is wrong because Object lifecycle management with a delete rule only automates deletion after a set time but does not prevent manual deletion or overwriting before that time, so it cannot enforce a non-deletion guarantee.

Practice this question →
47
MCQeasy

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

A.Cloud Spanner
B.AlloyDB
C.Bigtable
D.Cloud SQL
AnswerD

Cloud SQL is Google Cloud's fully managed relational service supporting MySQL, PostgreSQL and SQL Server, with automated replication and backups handled by the platform. This directly satisfies the stem's requirement for a managed engine covering all three database engines without manual administration.

Why this answer

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.

Exam trap

The trap here is that candidates often confuse Cloud SQL with Cloud Spanner because both are relational databases, but Cloud Spanner is designed for global scale and does not support MySQL, PostgreSQL, or SQL Server compatibility.

How to eliminate wrong answers

Option A is wrong because Cloud Spanner is a globally distributed, horizontally scalable relational database service that supports strong consistency and SQL, but it is not a fully managed service for MySQL, PostgreSQL, or SQL Server; it uses its own proprietary SQL dialect and is designed for sharded, multi-region deployments. Option B is wrong because AlloyDB is a fully managed PostgreSQL-compatible database service optimized for high performance and transactional workloads, but it does not support MySQL or SQL Server. Option C is wrong because Bigtable is a fully managed, scalable NoSQL wide-column database service, not a relational database, and it does not support MySQL, PostgreSQL, or SQL Server.

Practice this question →
48
MCQmedium

A media analytics company ingests 8 TB of new JSON event logs into BigQuery every day and keeps all history for 5 years. Analysts almost never filter on the raw event payload column and only occasionally select it, but they frequently filter on event_date and user_id. Storage cost is the top concern, and query performance on the frequently filtered columns must stay fast. What should the data engineer do?

A.Store the raw event payload in a BigQuery column of type JSON so it is parsed at query time and billed as a separate data type.
B.Partition the table by event_date and cluster it by user_id, then rely on the default columnar storage to avoid scanning the payload.
C.Enable the BigQuery long-term storage pricing tier so data older than 90 days is billed at a lower rate automatically.
D.Store the raw event payload in a Cloud Storage bucket and keep only the structured, frequently queried columns in the BigQuery table, referencing the object path.
AnswerD

Moving the rarely filtered payload out of BigQuery and into Cloud Storage Standard or Nearline removes the largest column from the columnar table, cutting both active storage cost and bytes scanned for the common queries. The structured columns remain in BigQuery for fast filtering on event_date and user_id, and the object path can be used with external tables or remote functions when the payload is genuinely needed.

Why this answer

The workload filters on structured columns but almost never touches the large payload, so the payload is dead weight in the columnar store. Keeping only the structured columns in BigQuery preserves fast partitioned and clustered filtering, while relocating the payload to Cloud Storage removes the dominant storage and scan cost. Partitioning, clustering, the JSON type, and long-term storage all leave that large column inside the table, so none of them eliminate the core expense.

Exam trap

The trap here is assuming that partitioning, clustering, or long-term storage pricing reduces the cost of a large column that queries still scan, when only removing that column from the table actually does.

Practice this question →
49
MCQmedium

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?

A.BigLake tables over Cloud Storage
B.BigQuery external tables reading from GCS
C.Dataproc with Spark SQL
D.Cloud Storage with IAM and VPC Service Controls
AnswerA

BigLake tables let BigQuery query Cloud Storage data while applying fine-grained governance: column-level security via policy tags and row-level filtering via row access policies. This satisfies the requirement to enforce governance across raw, curated, and processed zones without copying data into native BigQuery storage.

Why this answer

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.

Exam trap

Google often tests the misconception that BigQuery external tables (Option B) can support the same fine-grained security as BigLake, but they cannot because external tables lack the integrated policy engine for column and row-level controls.

How to eliminate wrong answers

Option B is wrong because BigQuery external tables reading from GCS only support table-level IAM permissions and cannot enforce column-level security or row-level filtering; they treat the external data as a flat file without fine-grained access controls. Option C is wrong because Dataproc with Spark SQL does not natively provide column-level or row-level security on Cloud Storage data; it requires manual implementation via Spark's security APIs and does not integrate with BigQuery's governance model. Option D is wrong because Cloud Storage with IAM and VPC Service Controls only provides bucket- and object-level access control and network perimeter security, but cannot enforce column-level or row-level filtering on queries executed in BigQuery.

Practice this question →
50
MCQeasy

A mobile app needs a NoSQL database that supports offline synchronization when the device goes offline and later reconnects. Which Google Cloud database should be used?

A.Firestore
B.Cloud Bigtable
C.Cloud Spanner
D.Cloud SQL
AnswerA

Firestore is a NoSQL document database with built-in offline persistence: the client SDK caches data locally and synchronises changes automatically when connectivity returns. This directly satisfies the stem's offline synchronisation constraint, unlike Cloud SQL or Bigtable, which lack native mobile offline support.

Why this answer

Firestore is a NoSQL document database that provides built-in offline synchronization for mobile and web apps. It caches data locally and automatically syncs changes when the device reconnects, making it ideal for offline-capable applications.

Exam trap

The trap is confusing Firestore with other NoSQL databases like Bigtable, or assuming that Spanner's global distribution supports offline sync, when only Firestore provides native offline synchronization for mobile apps.

How to eliminate wrong answers

Option B is wrong because Cloud Bigtable is a high-throughput, low-latency NoSQL database for analytics and time-series data, but it does not support offline synchronization for mobile apps. Option C is wrong because Cloud Spanner is a globally distributed relational database with strong consistency, not designed for offline mobile sync. Option D is wrong because Cloud SQL is a managed relational database service, which lacks native offline sync capabilities for mobile devices.

Practice this question →
51
MCQmedium

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?

A.BigQuery AEAD encryption functions
B.VPC Service Controls
C.Customer-managed encryption keys (CMEK)
D.Fine-grained IAM roles
AnswerA

BigQuery AEAD encryption functions let you encrypt specific PII columns at query time using keys managed through Cloud KMS, so only principals holding the key can decrypt. This satisfies the requirement for column-level, query-time encryption rather than storage-level or dataset-level controls.

Why this answer

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.

Exam trap

In Google Cloud exams, a common trap is to assume that Customer-managed encryption keys (CMEK) provide column-level, application-layer encryption or query-time decryption control. CMEK only protects data at rest at the storage level, not at query time. The correct approach for column-level query-time encryption is to use BigQuery AEAD encryption functions.

How to eliminate wrong answers

Option B is wrong because VPC Service Controls provide network-level security boundaries to prevent data exfiltration, not column-level encryption at query time. Option C is wrong because Customer-managed encryption keys (CMEK) encrypt data at rest (storage layer), not at query time, and do not control per-user decryption access. Option D is wrong because Fine-grained IAM roles control access to tables or rows via row-level security, but they do not encrypt the data itself; authorized users still see plaintext PII.

Practice this question →
52
MCQeasy

A startup is building a mobile app that needs to sync user data across devices in real time. They expect millions of concurrent users and need a NoSQL database with offline support and automatic multi-region replication. Which Google Cloud service meets these requirements?

A.Cloud Bigtable
B.Cloud Spanner
C.Firestore
D.Cloud SQL
AnswerC

Firestore provides a serverless NoSQL document store with built-in offline persistence via client SDKs and automatic multi-region replication, directly satisfying the real-time sync, offline support and global scale constraints. Its real-time listeners push updates across devices, meeting the millions-of-concurrent-users requirement.

Why this answer

Firestore is a NoSQL, serverless document database that provides real-time synchronization, offline support via local persistence, and automatic multi-region replication. It is designed for mobile and web apps with millions of concurrent users, making it the ideal choice for this use case.

Exam trap

The trap here is that candidates often confuse Cloud Spanner's global SQL capabilities with NoSQL requirements, or assume Cloud Bigtable's NoSQL label fits all NoSQL workloads, ignoring the specific need for real-time sync and offline support.

How to eliminate wrong answers

Option A is wrong because Cloud Bigtable is a wide-column NoSQL database optimized for high-throughput analytical workloads (e.g., time-series, IoT), not for real-time sync or offline mobile app support, and it lacks built-in multi-region replication. Option B is wrong because Cloud Spanner is a globally distributed, strongly consistent relational SQL database, not a NoSQL database, and while it supports multi-region replication, it does not provide offline support for mobile clients. Option D is wrong because Cloud SQL is a managed relational SQL database (MySQL, PostgreSQL, SQL Server) that is not NoSQL, does not support offline mobile sync, and requires manual configuration for multi-region replication.

Practice this question →
53
Multi-Selectmedium

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

Select 3 answers
A.Use Cloud SQL instead if joins are needed.
B.Design primary keys to distribute write load evenly across splits.
C.Use interleaved tables to co-locate related rows.
D.Store all data in a single table with JSON columns to avoid joins.
E.Create secondary indexes on columns used in WHERE clauses.
AnswersB, C, E

Evenly distributed primary keys prevent hotspots by spreading writes across splits, avoiding a single split becoming a bottleneck. This keeps load balanced across nodes, so frequently joined tables are not throttled by one overloaded split during concurrent inventory updates.

Why this answer

Option B is correct because Spanner scales by splitting data into ranges, and a monotonically increasing or skewed primary key concentrates writes on a single split; designing keys (for example, using hash prefixes or reversed timestamps) to distribute writes evenly avoids hotspots and keeps join-driving lookups performant. Option C is correct because interleaved tables physically co-locate child rows with their parent row in the same split, so joins between parent and child on the interleaved key prefix are executed locally without network shuffling, dramatically improving join performance. Option E is correct because secondary indexes let Spanner satisfy WHERE-clause predicates by index scan rather than full table scan, reducing the rows read before the join and thus lowering latency and cost.

Option A is not appropriate because Cloud SQL is a regional, non-horizontally-scalable relational service and does not provide Spanner's global distribution, external consistency, or interleaving; joins are fully supported in Spanner. Option D is not appropriate because collapsing everything into a single table with JSON columns discards Spanner's relational join and interleaving capabilities, prevents effective secondary indexing on nested fields, and typically worsens performance and schema maintainability.

Exam trap

Google often tests the misconception that avoiding joins entirely (Option D) is a better optimization than properly using Spanner's native features like interleaving and secondary indexes, which are designed to handle joins efficiently at scale.

Practice this question →
54
MCQmedium

A retail analytics team has a 400 TB BigQuery table partitioned by DATE on a transaction_date column. Analysts almost always filter by a single store_id and a date range, and each store has roughly 12 years of history. Queries currently scan the entire partition for the requested dates. The team wants to reduce bytes billed without changing the table name or rewriting the ingestion pipeline. What should they do?

A.Create a materialized view that pre-aggregates transactions by store_id and date, and point analysts at the view.
B.Enable partition expiration set to 30 days so older partitions are dropped automatically.
C.Add a clustering specification on store_id to the existing partitioned table.
D.Convert the table to use the US multi-region instead of a single region to increase scan parallelism.
AnswerC

BigQuery can co-locate and sort data within each date partition by the clustering columns, so a filter on store_id lets the engine read only the blocks containing that store rather than every block in the partition. This directly cuts bytes billed for the store-plus-date-range pattern and requires no change to the table name or pipeline.

Why this answer

Clustering a partitioned table on the frequently filtered column lets BigQuery prune blocks within each partition, so a query that filters on both date and store reads far less data than a full partition scan. The optimization applies transparently to the same table name, so ingestion and existing SQL keep working, and bytes billed drop in proportion to how well the clustering column discriminates rows inside each partition.

Exam trap

The trap here is assuming that partitioning alone prunes all irrelevant data, when partition pruning only eliminates whole partitions and a within-partition filter still scans the entire partition unless clustering is defined.

Practice this question →
55
Multi-Selecthard

A data engineer is designing a BigQuery table that will store billions of rows of application logs. Queries will almost always filter on a log_date column and then join on a user_id column, and the team wants to minimize both storage cost and bytes scanned. The table is append-only and grows by about 50 GB per day. Which two design choices should the engineer make? (Choose two.)

Select 2 answers
A.Cluster the table by user_id.
B.Set the table's expiration to 30 days to reduce storage cost.
C.Enable streaming inserts for the log ingestion pipeline.
D.Partition the table by log_date.
E.Create a materialized view that aggregates logs by user_id.
AnswersA, D

Clustering by user_id sorts data within each partition so that filters and joins on user_id read fewer blocks. Combined with partitioning by log_date, it addresses both the date filter and the user_id join in the stated workload. Clustering also improves the efficiency of the join by co-locating related rows within partitions.

Why this answer

Partitioning by log_date lets date-filtered queries prune partitions, and clustering by user_id reduces the blocks read for user_id filters and joins. Together they shrink bytes scanned and lower query cost for the dominant access pattern. Streaming inserts, table expiration, and materialized views do not address the general filtering and join pattern and can add cost or risk data loss.

Exam trap

The trap here is believing that clustering alone or a materialized view can substitute for partitioning when queries filter on a date column, when partition pruning is what removes whole segments of data from the scan.

Practice this question →
56
MCQhard

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?

A.Customer-managed encryption keys (CMEK) on the dataset
B.VPC Service Controls
C.AEAD encryption functions with Cloud KMS
D.BigQuery Data Catalog with policy tags
AnswerD

Policy tags classify columns and enforce access control through fine-grained permissions; they do not apply customer-managed encryption keys to individual columns. It is tempting because policy tags deliver column-level security, but that is access governance, not column-level CMEK encryption as the stem requires.

Why this answer

BigQuery supports column-level encryption with customer-managed encryption keys (CMEK) through column-level encryption using policy tags in BigQuery Data Catalog. You create a policy tag, associate a Cloud KMS key with that policy tag, and apply the policy tag to a column. BigQuery then encrypts that column with the customer-managed key.

AEAD functions with Cloud KMS provide application-layer encryption of individual values, but they are not the native BigQuery CMEK feature and should not be described as CMEK.

Exam trap

Candidates often confuse dataset-level CMEK (encrypts all data at rest in the dataset) with column-level CMEK via policy tags (encrypts specific columns with customer-managed keys), leading them to mistakenly choose dataset CMEK when the requirement is for column-level granularity.

How to eliminate wrong answers

Option A is wrong because CMEK on a dataset encrypts all data at rest in that dataset, not at the column level; it cannot selectively encrypt specific columns like credit card numbers. Option B is wrong because VPC Service Controls provide network security boundaries to prevent data exfiltration, not column-level encryption of data within BigQuery. Option D is wrong because BigQuery Data Catalog with policy tags is used for fine-grained access control and data classification (e.g., masking or row-level security), not for encrypting column data with customer-managed keys.

Practice this question →
57
Multi-Selecthard

A company uses BigQuery for analytics on petabyte-scale data. They want to improve query performance by denormalizing schemas and reducing joins. Which TWO BigQuery features should they use? (Choose 2)

Select 2 answers
A.Clustering on frequently filtered columns
B.Using subqueries instead of JOINs
C.External tables reading from Cloud Storage
D.Table partitioning by date
E.Nested and repeated fields (ARRAY<STRUCT<...>>)
AnswersA, E

Clustering physically co-locates rows sharing the clustered column values, so filters on those columns scan far fewer blocks. This directly supports denormalisation by pruning data before joins, cutting bytes read and improving performance on petabyte-scale tables.

Why this answer

Option E is correct because BigQuery's native support for nested and repeated fields (ARRAY<STRUCT<...>>) lets you store parent-child relationships inside a single table, which is the standard way to denormalize schemas and eliminate joins while preserving relational structure. Option A is correct because clustering on frequently filtered columns physically co-locates related rows within partitions, so BigQuery scans far less data and returns results faster for filtered queries on the denormalized table. Option B is not a BigQuery feature for denormalization; subqueries still execute as separate query blocks and do not remove join costs.

Option C is wrong because external tables read data from Cloud Storage and generally offer worse performance than native tables, not better denormalized performance. Option D is wrong because partitioning by date improves pruning on time filters but does not denormalize schemas or reduce joins.

Exam trap

A common mistake is to think that only clustering or partitioning can replace denormalization, but nested/repeated fields are needed for schema denormalization. Clustering is a complementary physical optimization.

Practice this question →
58
MCQmedium

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?

A.Create a single bucket with folders for each zone, and use IAM conditions to restrict access
B.Use Cloud Storage lifecycle rules to move objects between zones
C.Use a single bucket and rely on object ACLs
D.Create three separate buckets, one per zone, and assign IAM roles per bucket
AnswerD

Separate buckets per zone let IAM policies and roles be scoped at bucket level, giving clean, enforceable isolation between raw, curated and processed data. Bucket-level IAM is the granularity Cloud Storage supports, so this directly satisfies the zone-restriction governance requirement.

Why this answer

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.

Exam trap

A common misconception is that folders within a single Cloud Storage bucket can serve as effective security boundaries. However, folders are just a naming convention (prefixes) and do not provide native access control isolation without complex IAM conditions or ACLs.

How to eliminate wrong answers

Option A is wrong because IAM conditions on a single bucket with folders can restrict access based on object name prefixes, but they are complex to manage, prone to misconfiguration, and do not provide the same clear security boundary as separate buckets; also, IAM conditions are not supported for all roles and can lead to unintended access if not carefully crafted. Option B is wrong because Cloud Storage lifecycle rules are used for automating object transitions (e.g., moving to Nearline or deleting) based on age or other conditions, not for enforcing access control or governance between zones. Option C is wrong because relying on object ACLs is a legacy approach that is harder to audit and maintain at scale; ACLs provide per-object permissions but do not offer the centralized, hierarchical control of IAM roles, and they are not recommended for data lake architectures where consistent governance is required.

Practice this question →
59
MCQmedium

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?

A.Firestore
B.Cloud Spanner
C.Cloud Bigtable
D.BigQuery
AnswerC

Cloud Bigtable is a wide-column NoSQL store engineered for petabyte scale and consistent single-digit-millisecond reads at millions of operations per second on row-key lookups, matching the key-value timestamped workload. Spanner and BigQuery cannot meet that latency.

Why this answer

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.

Exam trap

A common trap in Google exams is to choose Cloud Spanner for structured data, but Spanner is optimized for strong consistency and transactional workloads, not for high-throughput time-series data at petabyte scale with millions of reads per second. BigQuery is for analytical queries, not real-time key-value access. Bigtable's key-value model and low-latency high-throughput design make it the correct choice.

How to eliminate wrong answers

Option A is wrong because Firestore is a document-oriented NoSQL database optimized for mobile and web app real-time sync, not for petabyte-scale time-series workloads with millions of reads per second; it has throughput limits (e.g., 10,000 writes/second per database) and does not natively handle high-throughput time-series data. Option B is wrong because Cloud Spanner is a globally distributed relational database with strong consistency and SQL support, but it is designed for transactional workloads (OLTP) with moderate throughput, not for petabyte-scale key-value time-series data at millions of reads per second; its latency and cost profile are not optimal for this use case. Option D is wrong because BigQuery is a serverless data warehouse for analytical SQL queries on large datasets, but it is not designed for single-digit millisecond latency at millions of reads per second; it is optimized for batch and interactive analytics, not real-time key-value lookups.

Practice this question →
60
MCQmedium

A data engineer needs to design a schema in BigQuery for a dataset that contains customer orders. Each order has a header and multiple line items. Queries frequently need to retrieve the entire order including line items. Which schema design is MOST performant and cost-effective?

A.Store all data in a flat table with repeated order info per line item
B.Use nested and repeated fields (orders table with line items as REPEATED RECORD)
C.Normalize into separate orders and line_items tables, join on order_id
D.Use a partitioned table on order date
AnswerB

Nested repeated RECORDs store line items inside the parent order row, so retrieving a full order needs one read rather than a join. This eliminates shuffle and join costs, satisfying the frequent whole-order retrieval requirement while cutting bytes scanned.

Why this answer

BigQuery is optimized for denormalized schemas using nested and repeated fields (REPEATED RECORD). Storing line items as a repeated record within the orders table avoids expensive JOIN operations, reduces data shuffling, and allows BigQuery to scan only the necessary columns, making queries that retrieve entire orders with line items both faster and more cost-effective.

Exam trap

Google often tests the misconception that normalization (Option C) is always the best practice for relational databases, but in BigQuery's distributed, columnar architecture, denormalization with nested and repeated fields is the recommended pattern for performance and cost efficiency.

How to eliminate wrong answers

Option A is wrong because storing all data in a flat table with repeated order info per line item leads to massive data duplication (each line item repeats all order header fields), increasing storage costs and query scan size without leveraging BigQuery's native nested structure. Option C is wrong because normalizing into separate orders and line_items tables and joining on order_id introduces expensive JOIN operations that require shuffling and sorting large datasets, which is inefficient in BigQuery's distributed architecture and incurs higher slot usage and cost. Option D is wrong because partitioning on order date alone does not address the structural inefficiency of storing line items separately; while partitioning can improve query performance for date-range filters, it does not eliminate the need for JOINs or duplication, and the question specifically asks about retrieving entire orders with line items.

Practice this question →
61
MCQmedium

A data engineer is designing a BigQuery table for a clickstream dataset with frequent queries aggregating over user sessions. Each user session has multiple events, and the engineer wants to avoid joins for performance. Which schema design pattern should they use?

A.Use a normalized schema with separate tables for sessions and events, then join on session ID
B.Store each event as a separate row with session key and use clustering on session ID
C.Use partitioning on event timestamp and clustering on user ID
D.Use nested and repeated fields to store events within each session row
AnswerD

Nested and repeated fields let BigQuery store each session's events inside one row, eliminating the join between sessions and events that the stem explicitly wants to avoid. Aggregations over sessions then scan a single denormalised table, and BigQuery's columnar storage reads only the referenced nested attributes.

Why this answer

BigQuery's nested and repeated fields (using STRUCT and ARRAY) allow you to store multiple events within a single session row, eliminating the need for joins when aggregating over sessions. This denormalized pattern is ideal for clickstream data and improves query performance by keeping related data together.

Exam trap

PDE often tests the trade-off between normalization and denormalization in BigQuery; candidates may default to normalized designs or clustering/partitioning without recognizing that nested repeated fields eliminate joins.

How to eliminate wrong answers

Option A is wrong because a normalized schema with separate tables requires joins, which the engineer explicitly wants to avoid for performance. Option B is wrong because storing each event as a separate row with clustering on session ID still requires aggregation across rows and does not eliminate joins if session-level attributes are needed. Option C is wrong because partitioning on event timestamp and clustering on user ID helps with filtering but does not avoid joins for session-level aggregation.

Practice this question →
62
MCQeasy

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?

A.Cloud Bigtable
B.BigQuery
C.Cloud Spanner
D.Cloud Firestore
AnswerD

Cloud Firestore is a serverless document database with flexible schema and native mobile SDKs offering real-time sync and offline support. It suits frequently accessed user profile data from mobile apps, meeting the flexible-schema and mobile-access constraints.

Why this answer

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.

Exam trap

The trap here is that candidates often confuse Cloud Firestore with Cloud Bigtable because both are NoSQL, but Bigtable lacks document flexibility, real-time sync, and mobile SDK support, which are essential for the described use case.

How to eliminate wrong answers

Option A is wrong because Cloud Bigtable is a wide-column NoSQL database optimized for high-throughput analytical and operational workloads (e.g., time-series, IoT), not for flexible document storage or mobile app real-time access. Option B is wrong because BigQuery is a serverless data warehouse for running SQL-based analytics on large datasets, not a transactional database for user profile reads/writes. Option C is wrong because Cloud Spanner is a globally distributed relational database with strong consistency and SQL support, but its rigid schema and higher latency for simple document operations make it overkill and less suitable for flexible schema mobile app data.

Practice this question →
63
MCQmedium

A mobile app uses Firestore to store user profiles. The app allows offline data creation and syncing when connectivity resumes. Which Firestore feature should the developer enable?

A.Set up a Firestore trigger to cache data in Cloud Memorystore
B.Enable offline persistence in the Firestore client SDK
C.Use Cloud Storage signed URLs for offline access
D.Enable Firestore multi-region replication
AnswerB

Enabling offline persistence in the Firestore client SDK caches data locally, so the app can create profiles without connectivity and automatically synchronise queued writes once the connection resumes, matching the offline creation and syncing scenario.

Why this answer

Firestore's offline persistence feature allows the client SDK to automatically cache data locally on the device. When the app creates or modifies data while offline, the SDK stores the changes in a local queue and syncs them with the Firestore backend once connectivity is restored. This is the correct and built-in mechanism for offline data creation and syncing.

Exam trap

Candidates often confuse Firestore's client-side offline persistence with server-side replication or caching features like multi-region replication or Cloud Memorystore, which are not designed for client-side offline data creation and syncing.

How to eliminate wrong answers

Option A is wrong because Cloud Memorystore is a managed Redis or Memcached service for caching in server-side applications, not a client-side offline cache; Firestore triggers are server-side functions that cannot cache data in Memorystore for offline client access. Option C is wrong because Cloud Storage signed URLs provide temporary, authenticated access to objects in Cloud Storage, not to Firestore documents, and they are used for online access, not offline data creation and syncing. Option D is wrong because multi-region replication improves availability and durability for Firestore databases but does not enable client-side offline caching or queuing of writes.

Practice this question →
64
MCQmedium

You need to store and query a large dataset of customer profiles. The data is semi-structured and frequently updated. The application requires offline support for mobile users. Which database is MOST appropriate?

A.Firestore
B.BigQuery
C.Cloud Bigtable
D.Cloud SQL
AnswerA

Firestore stores semi-structured documents with flexible schemas and offers real-time synchronisation plus offline persistence on mobile clients, so users can query and update profiles without connectivity. This directly satisfies the stem's offline support requirement, unlike relational databases that demand fixed schemas and constant connectivity.

Why this answer

Firestore is the most appropriate choice because it is a NoSQL document database designed for semi-structured data, real-time synchronization, and offline support. It provides built-in offline persistence for mobile clients, allowing users to read and write data even without network connectivity, and automatically syncs changes when the connection is restored. This directly meets the requirements of semi-structured data, frequent updates, and offline mobile support.

Exam trap

Google Cloud often tests the distinction between databases designed for transactional/operational workloads (like Firestore) versus analytical/warehouse databases (like BigQuery), and the trap here is assuming that any NoSQL database (like Bigtable) supports offline mobile sync, when in fact only Firestore provides native offline persistence and real-time synchronization for mobile clients.

How to eliminate wrong answers

Option B (BigQuery) is wrong because it is a serverless data warehouse optimized for analytical queries on large datasets, not for transactional or real-time updates, and it lacks native offline support for mobile applications. Option C (Cloud Bigtable) is wrong because it is a wide-column NoSQL database designed for high-throughput, low-latency workloads like time-series or IoT data, but it does not support offline mobile synchronization or semi-structured document models. Option D (Cloud SQL) is wrong because it is a relational database (MySQL, PostgreSQL, SQL Server) requiring a fixed schema, which is unsuitable for semi-structured data, and it does not provide built-in offline support for mobile clients.

Practice this question →
65
MCQeasy

A data engineer wants to store archived log files in Cloud Storage with a retention policy that prevents deletion for 5 years. Which feature should they use?

A.Object Lifecycle rule with Delete action after 5 years
B.Retention Policy on the bucket set to 5 years
C.Object Hold (temporal)
D.Versioning enabled
AnswerB

A bucket retention policy locks objects for a fixed period, preventing deletion or modification until it expires. Setting it to five years satisfies the stem's immutability requirement, unlike lifecycle rules, which delete or transition objects and therefore cannot enforce retention.

Why this answer

A retention policy on a Cloud Storage bucket enforces a minimum retention period for all objects in the bucket, preventing deletion or overwrite until the policy duration has elapsed. Setting it to 5 years ensures that archived log files cannot be deleted before that time, meeting the data engineer's requirement exactly. This is a bucket-level, immutable setting that applies to all objects, unlike object-level holds or lifecycle rules.

Exam trap

Google often tests the distinction between lifecycle rules that delete objects and retention policies that prevent deletion, so the trap here is assuming that a lifecycle rule with a Delete action can enforce a retention period, when in fact it does the opposite.

How to eliminate wrong answers

Option A is wrong because an Object Lifecycle rule with a Delete action after 5 years would automatically delete objects after 5 years, which is the opposite of preventing deletion; it does not enforce a retention period. Option C is wrong because an Object Hold (temporal) is a temporary hold placed on individual objects for a specific duration (e.g., days), not a bucket-wide policy for 5 years, and it is typically used for legal or compliance holds, not long-term retention. Option D is wrong because Versioning enabled preserves previous versions of objects but does not prevent deletion of the current version; it allows recovery after deletion but does not block deletion itself, so it does not enforce a retention policy.

Practice this question →
66
MCQmedium

A company stores sensitive data in Cloud Storage and must ensure that data is encrypted at rest with keys that they control and can rotate on demand. They also need to audit key usage and revoke access immediately if a key is compromised. Which Cloud Storage encryption option should they use?

A.Google-managed encryption keys
B.Customer-supplied encryption keys (CSEK)
C.Client-side encryption before uploading to Cloud Storage
D.Customer-managed encryption keys (CMEK) with Cloud KMS
AnswerD

CMEK with Cloud KMS lets the company create, rotate, and manage keys in Cloud KMS, and Cloud Storage uses these keys to encrypt data at rest. Cloud KMS provides audit logs for key usage and allows immediate revocation by disabling or destroying the key, meeting all requirements for control, audit, and revocation.

Why this answer

Customer-managed encryption keys (CMEK) with Cloud KMS allow the company to control and rotate keys, audit key usage through Cloud KMS audit logs, and revoke access by disabling or destroying the key. This satisfies the requirements for customer-controlled encryption, on-demand rotation, and immediate revocation for sensitive Cloud Storage data.

Exam trap

The trap here is assuming that customer-supplied encryption keys give the same integrated key management, rotation, and audit capabilities as CMEK, when CSEK keys are not stored or managed by Google Cloud.

Practice this question →
67
MCQeasy

Which Google Cloud database offers global distribution, strong consistency, and a 99.999% SLA?

A.Cloud Spanner
B.Cloud Bigtable
C.Firestore
D.Cloud SQL
AnswerA

Cloud Spanner synchronously replicates across regions using TrueTime, delivering externally consistent reads globally plus a 99.999% availability SLA. Regional Cloud SQL and Firestore cannot match that combination, and Bigtable offers neither strong consistency nor that SLA.

Why this answer

Cloud Spanner is the only Google Cloud database that provides global distribution (horizontally scaling across regions), strong consistency (external consistency with TrueTime), and a 99.999% SLA. It combines the benefits of relational database structure with non-relational horizontal scale, making it ideal for globally distributed, strongly consistent workloads.

Exam trap

Candidates may confuse Firestore's multi-region eventual consistency with the strong consistency required for the 99.999% SLA, or assume Cloud Bigtable's high throughput implies strong consistency. Spanner's external consistency with TrueTime is unique to Google Cloud.

How to eliminate wrong answers

Option B (Cloud Bigtable) is wrong because it offers only eventual consistency (not strong consistency) and a 99.99% SLA, not 99.999%. Option C (Firestore) is wrong because it provides strong consistency only within a single region; its multi-region mode uses eventual consistency, and its SLA is 99.999% only for single-region, not globally distributed strong consistency. Option D (Cloud SQL) is wrong because it is a single-region relational database with no global distribution capability and a 99.95% SLA.

Practice this question →
68
MCQmedium

A data engineer needs to store raw sensor data in Cloud Storage and automatically transition it to a lower-cost storage class after 30 days, then delete it after 365 days. What should they configure?

A.Use Cloud Pub/Sub notifications to trigger a Cloud Function that moves objects.
B.Use gsutil rewrite command in a cron job.
C.Configure a lifecycle rule with SetStorageClass to Nearline after 30 days and Delete after 365 days.
D.Set a bucket retention policy with a retention period of 365 days.
AnswerC

A single lifecycle rule with age-based conditions performs both transitions automatically: SetStorageClass to Nearline at 30 days, then Delete at 365 days. This satisfies both the cost-tiering and retention constraints without manual intervention or separate rules.

Why this answer

Cloud Storage lifecycle management rules allow you to automatically transition objects to a lower-cost storage class (such as Nearline) after a specified number of days and then delete them after another period. This is the native, serverless way to manage object lifecycle without external scripts or compute resources.

Exam trap

Google Cloud often tests the distinction between lifecycle management (which automates transitions and deletions) and retention policies (which only prevent deletion/overwrites), leading candidates to confuse the two.

How to eliminate wrong answers

Option A is wrong because Cloud Pub/Sub notifications and Cloud Functions introduce unnecessary complexity and cost; lifecycle rules handle this natively without custom code. Option B is wrong because using gsutil rewrite in a cron job is a manual, error-prone approach that does not scale and incurs additional egress/operation costs; lifecycle rules are the intended automated solution. Option D is wrong because a bucket retention policy prevents deletion before the retention period ends, but it does not automatically transition objects to a lower-cost storage class; it only enforces immutability.

Practice this question →
69
MCQhard

A healthcare company stores patient imaging metadata in Cloud Firestore in Native mode. The application must run a query that returns all documents where the status field equals 'pending' and the region field equals 'us-east', ordered by created_at descending, and it must paginate through potentially millions of matching documents. Which Firestore capability should the engineer use to satisfy this requirement efficiently?

A.A collection group query across all subcollections with an aggregation count.
B.A single-field index on created_at with offset-based pagination using the skip parameter.
C.A composite index on status, region, and created_at, combined with cursor-based pagination.
D.A distributed counter shard to track pending documents per region.
AnswerC

Firestore requires a composite index for queries combining equality filters on multiple fields with an orderBy on a different field. Cursor-based pagination using startAfter with the last document snapshot avoids the cost and inconsistency of offset-based paging, and it scales to millions of matching documents without re-reading skipped results.

Why this answer

Firestore automatically indexes single fields but requires a composite index when a query combines multiple equality filters with an orderBy on another field. Cursor-based pagination via startAfter scales to large result sets because it resumes from the last document rather than skipping offsets, making it the efficient, correct approach for this query pattern.

Exam trap

The trap here is assuming single-field indexes or offset pagination can handle a multi-filter ordered query, when Firestore requires a composite index and cursors for scale.

Practice this question →
70
MCQeasy

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

A.Firestore
B.Cloud SQL
C.Cloud Spanner
D.BigQuery
AnswerD

BigQuery is Google Cloud's fully managed, serverless analytics warehouse, separating storage from compute so it scales automatically. It natively runs ANSI SQL and connects to BI tools such as Looker and Data Studio, directly satisfying the serverless, highly scalable analytical query requirement in the stem.

Why this answer

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.

Exam trap

The trap here is that candidates may confuse Google Cloud Spanner's global scale and SQL support with data warehousing capabilities, but Spanner is optimized for transactional consistency, not analytical query performance or BI tool integration.

How to eliminate wrong answers

Option A is wrong because Firestore is a NoSQL document database for mobile and web app development, not a data warehouse for analytical SQL queries. Option B is wrong because Cloud SQL is a fully managed relational database for OLTP workloads, not a serverless data warehouse optimized for large-scale analytics. Option C is wrong because Cloud Spanner is a globally distributed, strongly consistent relational database service for transactional workloads, not a data warehouse designed for analytical queries and BI integration.

Practice this question →
71
Multi-Selecteasy

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

Select 2 answers
A.Cloud SQL
B.Cloud Spanner
C.BigQuery
D.Cloud Storage
E.Firestore
AnswersC, D

BigQuery provides the serverless analytics engine that queries the data and supports federated or external tables, satisfying the requirement to analyse both structured CSV logs and unstructured files without provisioning infrastructure. It delivers the analytics layer the scenario demands.

Why this answer

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.

Practice this question →
72
MCQeasy

A data engineer wants to automatically move objects from Standard storage class to Nearline after 30 days, and then to Archive after 365 days. Which Cloud Storage feature should they configure?

A.Object Versioning
B.Retention Policy
C.Bucket Lock
D.Object Lifecycle rule with SetStorageClass actions
AnswerD

An Object Lifecycle rule with SetStorageClass actions transitions objects automatically from Standard to Nearline at 30 days and to Archive at 365 days, satisfying both age-based tiering constraints. Lifecycle rules act on object age without manual intervention or application code.

Why this answer

Object Lifecycle rules in Google Cloud Storage allow you to automatically transition objects between storage classes (e.g., from Standard to Nearline after 30 days, then to Archive after 365 days) using the SetStorageClass action. This feature is specifically designed for automated lifecycle management, including deletion and class transitions, based on object age or other conditions.

Exam trap

Google often tests the distinction between lifecycle management (which changes storage classes) and retention/versioning features (which protect data but do not automate class transitions), leading candidates to confuse Object Versioning or Retention Policy with lifecycle rules.

How to eliminate wrong answers

Option A is wrong because Object Versioning is a feature that preserves non-current object versions to protect against accidental deletion or overwriting; it does not automate storage class transitions. Option B is wrong because Retention Policy is used to enforce a minimum retention period on objects, preventing deletion or modification, but it cannot change storage classes over time. Option C is wrong because Bucket Lock is a mechanism to permanently lock a retention policy, making it immutable; it does not provide any lifecycle-based storage class transitions.

Practice this question →
73
MCQmedium

A data engineer is designing a BigQuery table to store e-commerce order events. Each row contains an order_id (STRING, high cardinality), customer_id (STRING, high cardinality), order_timestamp (TIMESTAMP), and order_amount (NUMERIC). Queries frequently filter on order_id for lookups and also scan by order_timestamp ranges for daily reporting. The engineer wants to minimize bytes scanned by both query patterns. What should the engineer do?

A.Partition the table by order_timestamp and cluster by order_id.
B.Partition the table by order_id and cluster by order_timestamp.
C.Create a materialized view that pre-aggregates daily order totals and query the view instead.
D.Enable BigQuery BI Engine on the table and rely on in-memory caching for all queries.
AnswerA

Partitioning by order_timestamp limits daily range scans to the relevant date partitions, and clustering by order_id sorts data within each partition so point lookups on order_id read only the matching blocks. This combination addresses both access patterns without duplicating data or adding maintenance overhead.

Why this answer

Partitioning by order_timestamp restricts daily range scans to the relevant date partitions, while clustering by order_id sorts rows within each partition so that point lookups read only matching blocks. This design targets both query patterns and minimizes bytes scanned without adding a separate pipeline or storage layer.

Exam trap

The trap here is assuming clustering alone can replace partitioning for timestamp range filters, when clustering only helps when the filter columns are a prefix of the cluster keys and does not prune whole partitions.

Practice this question →
74
MCQmedium

A financial analytics team ingests trade records into BigQuery every minute. Queries filter almost exclusively on trade_date and account_id, and the table grows by roughly 400 GB per day. Analysts report that monthly reports scanning the last 30 days are slow and expensive. The team wants to reduce bytes scanned without changing the ingestion pipeline. What should they do?

A.Enable BigQuery BI Engine reservation for the reporting dataset.
B.Partition the table by trade_date and cluster it by account_id.
C.Convert the table to a BigQuery external table over Cloud Storage Parquet files.
D.Create a materialized view that pre-aggregates daily totals per account.
AnswerB

Partitioning by trade_date lets BigQuery prune all partitions outside the 30-day window, and clustering by account_id keeps account-filtered blocks co-located within each partition. Together they cut bytes scanned for both the date range and the account predicate, directly addressing the slow, expensive monthly reports without touching ingestion.

Why this answer

Partitioning by the trade_date column allows BigQuery to skip partitions outside the query's date range, and clustering by account_id further prunes blocks within each retained partition. This combination directly reduces bytes scanned for the described monthly reports, improving both latency and cost while leaving the ingestion pipeline untouched.

Exam trap

The trap here is assuming a materialized view or BI Engine is a substitute for partitioning and clustering when the real issue is scanning too much table data.

Practice this question →
75
Multi-Selecthard

A data engineer is designing a Cloud Storage layout for a data lake that will be queried by BigQuery external tables and by Dataproc jobs. The engineer wants to minimize query cost and improve scan performance across both engines. Which two practices should the engineer follow? (Choose two.)

Select 2 answers
A.Enable object versioning on the bucket to preserve historical query results.
B.Partition the data in Cloud Storage using a Hive-style key prefix such as dt=YYYY-MM-DD.
C.Store each row as a separate object to maximize parallelism during scans.
D.Store data in columnar formats such as Parquet or ORC instead of CSV.
E.Use the Standard storage class for all objects to ensure the lowest possible access latency.
AnswersB, D

Hive-style partitioning lets BigQuery external tables and Dataproc prune entire prefixes when a query filters on the partition column, so only relevant directories are listed and read. This reduces both listing overhead and bytes scanned, improving performance and lowering cost for both engines.

Why this answer

Columnar formats let both engines read only referenced columns and compress data efficiently, and Hive-style partitioning lets them prune entire prefixes when filters match the partition key. Together these reduce bytes scanned and listing overhead, which lowers query cost and improves scan performance for BigQuery external tables and Dataproc alike.

Exam trap

The trap here is focusing on storage-class or versioning settings, which affect durability and retrieval cost, instead of the data layout choices that actually reduce bytes scanned.

Practice this question →
Page 1 of 2 · 109 questions totalNext →

Ready to test yourself?

Try a timed practice session using only Pde Storing Data questions.

Start 20-question session