Courseiva

CCNA Pde Storing Data Questions

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

Practice these questionsExam hubAll questions
76
MCQeasy

A data engineer needs to choose a storage service for a new application that requires a schemaless document store with automatic multi-region replication, strong consistency for reads, and native mobile SDK support. The application must scale to millions of users without manual sharding. Which Google Cloud service should the engineer select?

A.Cloud Spanner
B.Cloud Bigtable
C.Cloud Firestore in Native mode
D.Cloud SQL for MySQL
AnswerC

Firestore in Native mode is a schemaless document database with automatic multi-region replication, strong consistency for document reads, and first-class mobile SDKs. It scales horizontally without manual sharding and is designed for exactly this class of mobile and web application.

Why this answer

Firestore in Native mode provides a schemaless document model, automatic multi-region replication, strong consistency for reads, and native mobile SDKs, and it scales horizontally without manual sharding. These characteristics match the application's requirements more closely than the relational or wide-column alternatives.

Exam trap

The trap here is conflating Spanner's global strong consistency with the document-store and mobile-SDK requirements, when Spanner is a relational engine with a fixed schema.

Practice this question →
77
MCQmedium

A data engineer manages a Cloud SQL for MySQL instance that stores order records. Compliance requires that the data be recoverable to any point in time within the last 30 days, and the team wants the smallest possible recovery window. They also want to avoid managing their own backup rotation scripts. Which configuration should the engineer implement?

A.Configure a read replica in a second zone and promote it if the primary fails.
B.Enable point-in-time recovery (PITR) with a transaction log retention of 30 days alongside automated backups.
C.Enable automated backups with a 30-day retention period and rely on the daily backup only.
D.Export nightly mysqldump files to a Cloud Storage bucket with a 30-day lifecycle rule.
AnswerB

Cloud SQL point-in-time recovery replays binary logs on top of the most recent automated backup, letting you restore to a specific timestamp. Setting the transaction log retention to 30 days satisfies the compliance window, and the feature is managed by the platform, so no custom rotation scripts are needed. This directly meets both the recovery granularity and the operational simplicity requirements.

Why this answer

Point-in-time recovery in Cloud SQL combines the latest automated backup with continuously archived binary logs, enabling restoration to a chosen timestamp. Extending transaction log retention to 30 days matches the compliance window while keeping the process fully managed. Daily backups alone or nightly dumps cannot achieve arbitrary-timestamp recovery, and read replicas address availability rather than historical restore.

Exam trap

The trap here is assuming that a 30-day backup retention period alone delivers 30 days of point-in-time recovery, when log retention is the separate control that governs the recovery window.

Practice this question →
78
MCQmedium

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

A.Use Object Lifecycle Management with rules to transition to Coldline after 30 days and delete after 365 days.
B.Write a Cloud Function that runs daily, checks object ages, and moves/deletes them.
C.Use Cloud Scheduler to run a script that changes storage class and deletes objects.
D.Set a retention policy on the raw zone to prevent deletion and manually clean up.
AnswerA

Object Lifecycle Management applies rule-based transitions and deletions natively at the bucket level, so no external scheduler or code is needed. Setting Age conditions of 30 days for Coldline transition and 365 days for deletion satisfies both stem constraints automatically and efficiently.

Why this answer

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

Practice this question →
79
MCQhard

A team needs to run hybrid transactional/analytical workloads on PostgreSQL-compatible data with low latency. They require high performance on both OLTP and OLAP queries, leveraging a columnar engine. Which Google Cloud service is best suited?

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

AlloyDB satisfies the hybrid transactional/analytical requirement through its columnar engine, which accelerates OLAP scans while retaining row-based OLTP performance on PostgreSQL-compatible data. This delivers the low-latency analytics alongside transactional throughput that the team needs, unlike Cloud SQL or standard PostgreSQL offerings lacking an integrated columnar accelerator.

Why this answer

AlloyDB is the correct choice because it is a fully managed PostgreSQL-compatible database service on Google Cloud that combines a columnar engine for fast analytical queries with high transactional performance. It uses a columnar query accelerator to offload analytical workloads from the transactional engine, enabling low-latency hybrid transactional/analytical processing (HTAP) without data movement.

Exam trap

The trap here is that candidates may confuse BigQuery's columnar storage with PostgreSQL compatibility, or assume Cloud SQL's PostgreSQL support is sufficient for HTAP workloads, overlooking the need for a dedicated columnar engine.

How to eliminate wrong answers

Option B (Cloud SQL for PostgreSQL) is wrong because it lacks a columnar engine and is optimized primarily for OLTP workloads, so analytical queries would suffer from high latency and poor performance. Option C (Cloud Spanner) is wrong because it is a globally distributed, strongly consistent relational database designed for horizontal scalability and high availability, not for columnar analytics or PostgreSQL compatibility. Option D (BigQuery) is wrong because it is a serverless data warehouse with a columnar storage engine but is not PostgreSQL-compatible and is designed for OLAP, not low-latency OLTP transactions.

Practice this question →
80
MCQmedium

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

A.Access Transparency
B.IAM conditions on BigQuery roles
C.Cloud Armor
D.VPC Service Controls
AnswerD

VPC Service Controls creates a service perimeter around BigQuery APIs, ensuring traffic stays within VPC boundaries and is restricted to the specified perimeter. This directly satisfies the stem's requirement to prevent exfiltration via API access controls.

Why this answer

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

Exam trap

The trap here is that candidates confuse IAM conditions (which control who can access data) with VPC Service Controls (which control where data can be accessed from), leading them to pick Option B instead of D.

How to eliminate wrong answers

Option A is wrong because Access Transparency provides logs of Google personnel access to your data, not network-level controls for data exfiltration. Option B is wrong because IAM conditions on BigQuery roles control authorization based on attributes like IP address or time, but they do not restrict traffic to VPC boundaries or create a service perimeter; they are identity-based, not network-based. Option C is wrong because Cloud Armor is a web application firewall (WAF) for HTTP(S) traffic to load balancers, not for BigQuery API traffic, and it cannot enforce VPC boundaries or service perimeters.

Practice this question →
81
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.Cloud Bigtable
B.BigQuery
C.Cloud Spanner
D.Firestore
AnswerA

Cloud Bigtable is a wide-column NoSQL store built on a sparse sorted map, delivering consistent single-digit millisecond latency at millions of reads per second. Its row-key design suits timestamped key-value IoT data at petabyte scale, matching every stated constraint.

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 sub-10ms latency at millions of reads per second. Its key-value model with timestamps directly matches the time-series IoT sensor data structure, and it supports high-throughput, low-latency access via the HBase API or Bigtable client libraries.

Exam trap

A common trap in Google Cloud exams is confusing operational databases (Bigtable) with analytical warehouses (BigQuery). The petabyte-scale data might suggest BigQuery, but the single-digit millisecond latency and high throughput key-value access pattern require Bigtable's purpose-built design.

How to eliminate wrong answers

Option B (BigQuery) is wrong because it is a serverless data warehouse optimized for analytical SQL queries on large datasets, not for single-digit millisecond point reads at millions of operations per second; it incurs higher latency (typically hundreds of milliseconds) and is not designed for real-time key-value lookups. Option C (Cloud Spanner) is wrong because it is a globally distributed relational database with strong consistency and SQL support, but its latency and throughput for simple key-value reads are higher than Bigtable, and it is overkill for time-series data that does not require relational joins or transactions. Option D (Firestore) is wrong because it is a mobile and web document database optimized for real-time updates and moderate throughput, not for petabyte-scale time-series data with millions of reads per second; it has throughput limits (e.g., 10,000 writes/second per database) and higher latency for such high-volume workloads.

Practice this question →
82
MCQmedium

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

A.Partitioned tables with partition expiration
B.BigQuery materialized views
C.Clustered tables
D.BigQuery BI Engine
AnswerA

Partitioning by date lets BigQuery prune irrelevant partitions, so queries scan only recent data, and partition expiration automatically deletes old partitions to cut storage and query costs. This suits frequently appended datasets where historical rows are rarely queried.

Why this answer

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

Exam trap

A common mistake is to choose a performance optimization feature (clustering, materialized views, BI Engine) when the question explicitly asks about minimizing costs and avoiding scanning old data. The correct focus is on data lifecycle management with partition expiration.

How to eliminate wrong answers

Option B is wrong because BigQuery materialized views precompute and cache query results for faster reads, but they do not automatically expire old data or reduce storage costs for rarely queried rows. Option C is wrong because clustered tables sort data within partitions to improve query performance and reduce bytes scanned, but they do not provide automatic deletion of old data or partition expiration. Option D is wrong because BigQuery BI Engine is an in-memory analysis service that accelerates interactive queries but does not manage data lifecycle or expiration of old rows.

Practice this question →
83
MCQmedium

A data engineer needs to store quarterly financial data that must remain immutable for 7 years to meet regulatory compliance. The data is accessed infrequently after the first year. Which Cloud Storage feature should be used to enforce immutability?

A.Object Lifecycle Rules
B.Object Lock with Retention Policy
C.Object Versioning
D.IAM Conditions
AnswerB

Object Lock with a retention policy enforces write-once-read-many (WORM) protection at the object level, preventing deletion or modification for the specified retention period. This directly satisfies the seven-year regulatory immutability requirement, while the bucket's storage class can separately be set to a colder tier for the infrequent post-first-year access.

Why this answer

Object Lock with a Retention Policy enforces immutability by preventing object deletion or modification for a specified duration. For regulatory compliance requiring 7-year immutable storage, a retention policy configured with a retention period of 7 years ensures that objects cannot be overwritten or deleted, even by the root account. This directly meets the requirement for data that must remain unchanged for the full 7-year period.

Exam trap

Candidates often confuse Object Versioning with immutability, but versioning only protects against accidental deletion by preserving old versions—it does not prevent intentional deletion or overwriting of the current version, which is required for true immutability.

How to eliminate wrong answers

Option A is wrong because Object Lifecycle Rules manage transitions and deletions based on age or other criteria, but they do not enforce immutability—objects can still be modified or deleted by users with appropriate permissions. Option C is wrong because Object Versioning preserves previous versions of objects but does not prevent deletion or overwriting of the current version; it allows recovery but does not enforce a write-once, read-many (WORM) model. Option D is wrong because IAM Conditions control access based on attributes like IP address or time, but they do not prevent deletion or modification of objects; they only restrict who can perform actions, not enforce data immutability.

Practice this question →
84
MCQmedium

An e-commerce application uses Cloud SQL (MySQL) for transaction processing. To improve read performance for reporting queries, the team wants to offload read traffic to a separate database instance that stays in sync with the primary. Which Cloud SQL feature should they use?

A.Configure a high availability (HA) replica
B.Use Cloud SQL's failover replica
C.Create a cross-region read replica
D.Enable automatic backups and point-in-time recovery
AnswerC

A cross-region read replica offloads reporting queries while asynchronously replicating from the primary, satisfying the requirement for a separate in-sync instance. However, cross-region placement adds replication lag and network cost; a same-region read replica would better suit latency-sensitive reporting, so this option is correct but not optimal.

Why this answer

Cloud SQL read replicas are designed to offload read traffic from the primary instance while staying in sync using asynchronous replication. Cross-region read replicas specifically allow you to place a replica in a different region, improving read performance for geographically distributed reporting queries without affecting the primary's transaction processing.

Exam trap

The pitfall is that candidates often assume a high availability (HA) failover replica can also serve read traffic, but in Cloud SQL, the HA standby is not directly accessible for reads; it is only used for automatic failover. Read replicas (option C) are the correct choice for offloading read queries.

How to eliminate wrong answers

Option A is wrong because a high availability (HA) replica is a synchronous standby that provides automatic failover for high availability, not a separate instance for offloading read traffic; it does not serve read queries independently. Option B is wrong because Cloud SQL does not have a separate 'failover replica' feature; failover is handled by the HA configuration, and the term is often confused with a read replica, but failover replicas are not used for read offloading. Option D is wrong because automatic backups and point-in-time recovery are disaster recovery features that protect data, not mechanisms to offload read traffic or improve query performance.

Practice this question →
85
MCQmedium

A healthcare company stores patient records in Cloud SQL for PostgreSQL and needs to run analytical queries on the same data without impacting the production database. The analytics team requires near-real-time replication and wants to use BigQuery for querying. The data must be kept in sync with minimal latency and without writing custom ETL code. Which solution should the data engineer implement?

A.Create a read replica of the Cloud SQL instance and point BigQuery federated queries at the replica
B.Use Datastream to replicate from Cloud SQL for PostgreSQL to BigQuery with a dataset-level replication
C.Use Cloud Data Fusion to build a pipeline that reads from Cloud SQL and writes to BigQuery on a schedule
D.Schedule a nightly export from Cloud SQL to Cloud Storage and load the files into BigQuery
AnswerB

Datastream is a serverless change data capture service that can replicate from Cloud SQL for PostgreSQL to BigQuery with low latency. It reads the write-ahead log and applies changes to BigQuery, keeping the data in sync without custom ETL. This meets the near-real-time and no-code requirements while offloading analytics from the production database.

Why this answer

Datastream provides serverless change data capture from Cloud SQL for PostgreSQL to BigQuery. It reads the database's write-ahead log and applies changes to BigQuery with low latency, keeping the analytical store in sync without custom code. This offloads analytics from the production database and meets the near-real-time and no-ETL requirements.

Other options either introduce batch latency, require pipeline development, or do not create a separate analytical store.

Exam trap

The trap here is assuming that BigQuery federated queries or read replicas provide a scalable analytical solution, when they actually query the transactional database and do not offload analytics.

Practice this question →
86
MCQeasy

A team wants to store semi-structured user profile data for a web application. The data is accessed via a REST API and requires security rules to control read/write access. Which database fits best?

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

Firestore stores semi-structured documents and enforces security rules directly at the database layer, satisfying the read/write access constraint without a separate authorisation tier. Its native REST API and real-time synchronisation suit web profile data, unlike relational engines requiring schema migrations or blob stores lacking granular rule enforcement.

Why this answer

Firestore is a NoSQL document database that natively stores semi-structured data (JSON-like documents) and integrates directly with Firebase Authentication and security rules to control read/write access per document or collection. Its REST API support and real-time capabilities make it ideal for web application user profiles that require flexible schemas and fine-grained access control.

Exam trap

Google often tests the distinction between NoSQL databases optimized for different workloads (document vs. wide-column vs. analytical), and the trap here is assuming any NoSQL database (like Cloud Bigtable) is suitable for semi-structured user profiles without considering the need for built-in security rules and REST API integration.

How to eliminate wrong answers

Option A is wrong because BigQuery is a serverless data warehouse designed for analytical queries on large datasets, not for transactional REST API access with per-document security rules. Option C is wrong because Cloud SQL is a relational database (MySQL, PostgreSQL, SQL Server) that requires a fixed schema and does not natively support document-level security rules or semi-structured data without additional abstraction. Option D is wrong because Cloud Bigtable is a wide-column NoSQL database optimized for high-throughput, low-latency time-series or analytical workloads, not for semi-structured user profiles with fine-grained access control via REST API.

Practice this question →
87
MCQeasy

A data engineer needs to store JSON documents in a Google Cloud database that must scale horizontally, support automatic multi-region replication, and provide strong consistency for reads. The application performs many small reads and writes keyed by a document ID, and the team wants to avoid managing servers. They also need the ability to run SQL-like queries on the documents. Which Google Cloud service should the engineer choose?

A.Cloud SQL for PostgreSQL with a read replica in each region.
B.Cloud Spanner with a multi-region configuration.
C.Bigtable with a single cluster in one region and a replication policy to a second region.
D.Firestore in Native mode with multi-region location.
AnswerD

Firestore in Native mode is a serverless, horizontally scalable document database with automatic multi-region replication and strong consistency for reads and writes. It supports document IDs as keys, handles many small reads and writes efficiently, and provides SQL-like queries through its query API. It requires no server management. This matches the requirements for scale, multi-region replication, strong consistency, and document storage. Firestore also supports real-time listeners and offline sync for mobile, but the core fit here is the document model and strong consistency.

Why this answer

Firestore in Native mode is the Google Cloud document database that provides automatic multi-region replication, strong consistency, horizontal scaling, and a SQL-like query API. It is serverless, so the team does not manage servers. The other options either lack strong consistency across regions, are relational rather than document-oriented, or are designed for different workloads.

Firestore's document ID key model and small read/write performance align with the application's access pattern.

Exam trap

The trap here is confusing Cloud Spanner's strong consistency and SQL support with document storage, when Spanner is a relational database with a fixed schema.

Practice this question →
88
MCQeasy

A company needs a fully managed, globally distributed relational database with strong consistency, external consistency, and 99.999% SLA for a financial transaction processing system. Which Google Cloud service should they use?

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

Cloud Spanner is the only Google Cloud relational database offering external consistency globally through TrueTime, satisfying the financial system's strict consistency requirement. Its multi-region configuration delivers the 99.999% SLA, and it is fully managed, unlike self-managed alternatives such as Cloud SQL or Bare Metal Solution.

Why this answer

Cloud Spanner is the correct choice because it is a fully managed, globally distributed relational database service that provides strong consistency, external consistency (true serializable transactions across regions), and a 99.999% SLA. These features are essential for a financial transaction processing system that requires ACID compliance and global scalability without sacrificing consistency.

Exam trap

The trap here is that candidates often confuse Cloud Spanner with Bigtable or Firestore because all three are globally distributed, but only Spanner offers the relational model, strong consistency, and the 99.999% SLA required for financial transactions.

How to eliminate wrong answers

Option A (Firestore) is wrong because it is a NoSQL document database that does not support relational queries or strong consistency across global distributions (it offers eventual consistency by default). Option C (Bigtable) is wrong because it is a wide-column NoSQL database designed for high-throughput analytical workloads, not relational transactions, and it does not provide SQL support or ACID transactions. Option D (Cloud SQL) is wrong because it is a regional relational database service that cannot provide global distribution or a 99.999% SLA; it supports only single-region deployments with limited failover.

Practice this question →
89
MCQhard

A data engineer is designing a Cloud Storage bucket for a machine learning training pipeline. The pipeline writes many small Parquet files (about 1 MB each) from a Dataflow job, and a downstream training job reads them sequentially. The engineer wants to minimize the number of storage operations and improve read throughput. The bucket uses Standard storage class and has no lifecycle rules. What should the engineer do?

A.Change the bucket storage class to Nearline and add a lifecycle rule to transition objects after 30 days.
B.Enable Requester Pays on the bucket so that the training job pays for egress and operations.
C.Enable Object Versioning on the bucket and set a lifecycle rule to delete noncurrent versions after 7 days.
D.Configure the Dataflow job to write larger Parquet files, for example by using a longer window or by grouping writes into 128 MB to 512 MB files.
AnswerD

Writing larger Parquet files reduces the number of objects, which lowers the number of storage operations (PUT and GET) and improves sequential read throughput because the training job opens fewer files and can read larger contiguous ranges. Parquet is columnar; larger files also improve compression and encoding efficiency. The engineer can adjust the Dataflow pipeline to batch writes, use a larger shard size, or repartition before writing. This directly addresses both the operation count and read performance.

Why this answer

The core issue is many small files, which increases the number of Cloud Storage operations and reduces read throughput because each file requires separate open, metadata, and read operations. Writing larger Parquet files, such as 128 MB to 512 MB, reduces object count and allows the training job to read contiguous data more efficiently. This is a common Dataflow and Cloud Storage optimization: adjust the pipeline to produce fewer, larger shards.

Other options address durability, cost class, or billing, not the operation-count and throughput problem.

Exam trap

The trap here is focusing on storage class or versioning as a performance fix, when the real bottleneck is the number and size of objects being read.

Practice this question →
90
MCQhard

A data engineer must store JSON documents for a product catalog in Cloud Firestore. Some documents contain nested arrays of product variants that can exceed 1 MiB when serialized. The engineer wants to keep the catalog queryable with strong consistency and low latency. What should the engineer do?

A.Move the entire catalog to Cloud Bigtable and use row keys to model variant relationships.
B.Store each variant as a separate document in a subcollection and keep only summary fields in the parent document.
C.Store the variants as a base64-encoded string field inside the parent document.
D.Increase the Firestore document size limit by enabling the Enterprise edition of Firestore.
AnswerB

Firestore enforces a 1 MiB document size limit, so moving large nested arrays into a subcollection keeps each document within the limit. Queries remain strongly consistent and low latency because subcollection documents are still part of the same Firestore database and can be queried directly.

Why this answer

Firestore imposes a hard 1 MiB limit per document, so large nested arrays must be split across documents. Moving variants into a subcollection keeps each document within the limit while preserving strong consistency and low-latency queries, since subcollection documents are first-class documents in the same database.

Exam trap

The trap here is assuming a paid or enterprise tier removes the Firestore 1 MiB document size limit, when in fact that limit is fixed across editions.

Practice this question →
91
MCQmedium

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

A.Set a bucket retention policy that forces deletion after 30 days
B.Write a Cloud Function to delete objects older than 30 days
C.Use gsutil rsync to move objects between buckets
D.Configure a Cloud Storage object lifecycle rule with SetStorageClass action
AnswerD

Object lifecycle rules evaluate objects against age conditions and apply SetStorageClass to transition them automatically, satisfying the 30-day raw-zone requirement without manual intervention. This is the native Cloud Storage mechanism for cost-tiering, unlike bucket-level retention or transfer jobs.

Why this answer

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

Exam trap

Google often tests the distinction between lifecycle rules that change storage class versus retention policies that enforce immutability, and candidates may confuse 'move to cheaper storage' with 'delete' or 'retain'.

How to eliminate wrong answers

Option A is wrong because a retention policy prevents deletion or modification of objects until the retention period expires; it does not move objects to a cheaper storage class and would lock the data, not transition it. Option B is wrong because while a Cloud Function could delete objects, the requirement is to move them to a cheaper storage class, not delete them; using a function for this is also less efficient and more complex than a native lifecycle rule. Option C is wrong because `gsutil rsync` synchronizes content between buckets but does not automatically trigger based on age; it requires manual or scheduled execution and does not natively support storage class transitions based on object age.

Practice this question →
92
MCQmedium

An organization needs to restrict access to BigQuery and Cloud Storage so that data can only be accessed from within a specific VPC network and cannot be exfiltrated. Which Google Cloud feature should they use?

A.Private Service Access
B.VPC Service Controls
C.VPC firewall rules
D.IAM conditions
AnswerB

VPC Service Controls builds a service perimeter around BigQuery and Cloud Storage, permitting access only from within the specified VPC network and blocking data exfiltration to unauthorised projects. This directly satisfies the constraint that data remain accessible solely from inside the defined network boundary.

Why this answer

VPC Service Controls (option B) is the correct choice because it creates a security perimeter around Google Cloud services like BigQuery and Cloud Storage, preventing data exfiltration even from within a VPC. It enforces context-aware access based on the VPC network, ensuring data can only be accessed from authorized VPC sources and blocking unauthorized transfers outside the perimeter.

Exam trap

The trap here is that candidates confuse VPC firewall rules (which control network traffic) with VPC Service Controls (which control data access at the API layer), leading them to choose firewall rules because they think 'restricting access to a VPC' is purely a network-level concern.

How to eliminate wrong answers

Option A is wrong because Private Service Access is used to enable private connectivity from a VPC to Google-managed services (e.g., Cloud SQL, Memorystore) via internal IPs, but it does not provide exfiltration prevention or restrict data movement across services. Option C is wrong because VPC firewall rules control network traffic at the packet level (IP addresses, ports, protocols) but cannot prevent data exfiltration via API calls or service-to-service transfers, as they operate at layers 3/4, not at the application layer. Option D is wrong because IAM conditions allow fine-grained access control based on attributes like IP address or time, but they do not create a perimeter around services; they can restrict who can call an API but cannot block data movement between services or prevent exfiltration via authorized credentials.

Practice this question →
93
MCQmedium

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

A.Create read replicas and direct read queries to them.
B.Increase the tier of the Cloud SQL instance.
C.Create a Cloud SQL failover replica.
D.Use Cloud Memorystore as a cache in front of Cloud SQL.
AnswerA

Read replicas handle read traffic, reducing load on the primary instance.

Why this answer

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

Exam trap

The Google Professional Data Engineer exam often tests the distinction between read replicas (for offloading reads) and failover replicas (for high availability), so candidates may confuse the two and incorrectly select the failover replica option.

How to eliminate wrong answers

Option B is wrong because increasing the tier of the Cloud SQL instance only scales the primary instance vertically, which does not offload read traffic — it simply gives the same instance more resources, which may not be sufficient under heavy read loads and does not separate read and write workloads. Option C is wrong because a Cloud SQL failover replica is designed for high availability and automatic failover in case of primary instance failure, not for offloading read queries; it is a synchronous standby that does not serve read traffic. Option D is wrong because Cloud Memorystore as a cache can reduce read load but does not maintain strong data consistency with Cloud SQL; cached data may become stale, and the cache is not a direct offload of read queries from the database — it introduces eventual consistency and requires application-level cache invalidation logic.

Practice this question →
94
MCQhard

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

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

AlloyDB provides full PostgreSQL wire compatibility, so the application connects unchanged, while its columnar engine accelerates analytical scans and aggregates alongside standard row-based OLTP processing. This directly satisfies the stem's requirement to improve analytical query performance on mixed workloads without application modification.

Why this answer

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

Exam trap

The trap here is that candidates often choose Cloud SQL for PostgreSQL because it is the most familiar PostgreSQL option within Google Cloud, overlooking that AlloyDB is Google's specialized service for mixed OLTP and analytical workloads, providing PostgreSQL compatibility with built-in analytical acceleration. Cloud SQL lacks these advanced analytical features.

How to eliminate wrong answers

Option A is wrong because BigQuery is a serverless data warehouse that is not PostgreSQL-compatible and requires application changes to use its SQL dialect; it is designed for large-scale analytics, not OLTP workloads. Option B is wrong because Cloud Spanner is a globally distributed, strongly consistent relational database that uses a proprietary SQL dialect, not PostgreSQL, and is optimized for horizontal scalability and high availability, not for improving analytical query performance on mixed workloads. Option C is wrong because Cloud SQL for PostgreSQL is a fully managed PostgreSQL service but lacks the built-in columnar engine and adaptive caching needed to significantly accelerate complex analytical queries; it is best suited for standard OLTP workloads, not mixed analytical and transactional demands.

Practice this question →
95
MCQmedium

A data engineer manages a BigQuery dataset holding a 40 TB partitioned table of point-of-sale transactions. Analysts frequently run queries that filter on a region column and a sale_date column together, but each query scans the full table because the region predicate is not reducing bytes billed. The engineer wants to reduce bytes scanned without changing the analytical queries or the write pipeline. What should the engineer do?

A.Define a materialized view that aggregates the table by region and sale_date and let the optimizer rewrite all analyst queries to use it.
B.Enable the require_partition_filter option on the table so that every query must include a sale_date predicate.
C.Convert the table to an external table over Cloud Storage so that only the matching Parquet row groups are read at query time.
D.Create a clustered table on the region column, because clustering co-locates rows with similar values inside each partition so block pruning can skip irrelevant data.
AnswerD

Clustering on region sorts data within each partition by that column, so BigQuery can prune storage blocks when a region predicate is present. Because the table is already partitioned on sale_date, adding clustering on region shrinks bytes scanned for queries that filter on both columns, and the write pipeline keeps inserting data unchanged.

Why this answer

Clustering is the right lever because it physically sorts data within each partition by the region column, enabling block pruning when region predicates are present. Partitioning already handles sale_date, so adding clustering on the other frequently filtered column reduces scanned bytes while leaving the queries and ingestion pipeline untouched.

Exam trap

The trap here is assuming that partitioning alone always prunes scans, when partitioning only helps for predicates on the partition column.

Practice this question →
96
MCQhard

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

A.timestamp_millis#hash(userID)
B.timestamp_millis#userID
C.userID#timestamp_millis
D.hash(userID)#userID#timestamp_millis
AnswerD

Hashing the userID spreads writes uniformly across row ranges, preventing hotspotting from sequential or timestamp-prefixed keys. Appending userID and timestamp preserves per-user chronological ordering while the hash prefix distributes millions of writes per second evenly across tablets.

Why this answer

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

Exam trap

Google PDE often tests the misconception that placing the most selective or unique field first (like timestamp) is best for queries, but in Bigtable the row key design must prioritize write distribution over read optimization to avoid hotspotting.

How to eliminate wrong answers

Option A is wrong because placing the timestamp first causes all writes for the same millisecond to hit a single tablet server, creating a hotspot. Option B is wrong because using the raw timestamp as the prefix leads to sequential writes that overload one node, negating Bigtable's horizontal scaling. Option C is wrong because while userID as prefix distributes writes, it does not guarantee uniqueness for multiple log entries from the same user at the same millisecond, and it lacks the hash to prevent skewed access patterns if user IDs are sequential or predictable.

Practice this question →
97
Multi-Selectmedium

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

Select 2 answers
A.Place the timestamp as the first component of the row key
B.Create as many column families as possible
C.Use a hashed prefix in the row key to distribute writes
D.Group related columns into column families
E.Store all columns in a single column family
AnswersC, D

Hashing the row key prefix scatters sequential writes across many tablets instead of concentrating them on one, preventing hot-spotting. This satisfies the high-volume time-series constraint where monotonically increasing keys would otherwise bottleneck a single tablet.

Why this answer

Option C is correct because Cloud Bigtable sorts rows lexicographically by row key, so monotonically increasing keys (like raw timestamps) concentrate writes on a single tablet, creating a hotspot; adding a hashed prefix distributes writes across multiple tablets and improves throughput. Option D is correct because grouping related columns into a small number of column families keeps data that is accessed together physically co-located, which improves read efficiency and avoids excessive per-row overhead. Option A is incorrect because placing the timestamp first produces sequential, monotonically increasing keys that cause write hotspots rather than distributing load.

Option B is incorrect because Bigtable recommends keeping the number of column families small (typically fewer than 10), since each family adds memory and compaction overhead. Option E is incorrect because cramming all columns into one family prevents efficient access patterns and mixes unrelated data, hurting read performance and locality.

Exam trap

The trap is assuming that timestamp-first row keys are good for time-series data, but they cause hotspots; candidates may also overuse column families.

Practice this question →
98
Multi-Selecteasy

A company wants to use BigQuery for analytics. They need to meet compliance requirements by encrypting data at rest with a key they control. Which TWO actions should they take? (Choose 2.)

Select 2 answers
A.Set the Cloud KMS key as the default encryption key for the BigQuery dataset.
B.Create a Cloud Storage bucket and load data there.
C.Use VPC Service Controls to restrict access to the dataset.
D.Create a key ring and cryptographic key in Cloud KMS.
E.Enable BigQuery column-level encryption using AEAD functions.
AnswersA, D

Setting a Cloud KMS key as the dataset default encryption key satisfies the requirement for customer-controlled keys, since BigQuery then wraps data with that CMEK rather than Google-managed encryption. This applies the key at dataset scope, so every table created within it inherits the same customer-managed protection automatically.

Why this answer

Option D is correct because customer-managed encryption keys (CMEK) in BigQuery require you to first create a Cloud KMS key ring and a cryptographic key (with appropriate rotation and IAM permissions granted to the BigQuery service account) before it can be used. Option A is correct because, once the KMS key exists, you set it as the default encryption key on the BigQuery dataset so that all tables created in that dataset are encrypted at rest with the customer-controlled key. Option B is incorrect because a Cloud Storage bucket is not required to apply CMEK to BigQuery datasets and does not by itself satisfy the BigQuery encryption requirement.

Option C is incorrect because VPC Service Controls provide network perimeter controls against data exfiltration, not encryption of data at rest. Option E is incorrect because AEAD functions provide column-level encryption of specific values within queries, not dataset-level encryption at rest with a customer-managed KMS key.

Exam trap

The trap is confusing column-level encryption (AEAD functions) with dataset-level CMEK — candidates may think encrypting columns satisfies 'encryption at rest with a key they control,' but the exam expects dataset-level CMEK configuration.

Practice this question →
99
Multi-Selectmedium

A company wants to build a reporting pipeline where data is collected from IoT devices, stored raw in Cloud Storage, and then processed into BigQuery for analytics. They need to ensure data is encrypted at rest using customer-managed keys. Which THREE steps should they take? (Choose 3 correct options)

Select 3 answers
A.Delete the Cloud KMS key after data is loaded to BigQuery
B.Enable CMEK on the Cloud Storage bucket by specifying the KMS key
C.Configure the BigQuery dataset to use a CMEK key
D.Use Google-managed encryption keys
E.Create a key ring and key in Cloud Key Management Service
AnswersB, C, E

Specifying a Cloud KMS key on the Cloud Storage bucket enforces customer-managed encryption at rest for the raw IoT data, satisfying the stem's CMEK constraint at the storage layer. Without this, Google-managed keys apply by default, so the raw landing zone would fail the customer-managed key requirement before BigQuery processing begins.

Why this answer

The scenario requires customer-managed encryption keys (CMEK) for data at rest across Cloud Storage and BigQuery, so the foundational step is option E: create a key ring and key in Cloud Key Management Service, since CMEK always begins with a KMS key ring and a key (or key version) that will be referenced by the services. Option B is correct because enabling CMEK on the Cloud Storage bucket by specifying the KMS key ensures the raw IoT data written to the bucket is encrypted at rest with that customer-managed key rather than a Google-managed key. Option C is correct because configuring the BigQuery dataset to use a CMEK key ensures the processed analytics data stored in BigQuery is also encrypted at rest with the customer-managed key, satisfying the end-to-end requirement.

Option A is wrong because deleting the Cloud KMS key after loading would make the encrypted data unrecoverable and break decryption for both Cloud Storage and BigQuery. Option D is wrong because Google-managed encryption keys are the default and do not meet the requirement for customer-managed keys.

Exam trap

A common trap is thinking that only one component needs CMEK enabled, but for this data pipeline, both Cloud Storage and BigQuery must be configured to use the same customer-managed key. Another trap is selecting 'Delete the Cloud KMS key after data is loaded' (option A), which is incorrect because it would make data unrecoverable and non-compliant.

Practice this question →
100
MCQhard

A healthcare company stores patient records in a Cloud Storage bucket that must remain in a specific region for data residency. The security team requires that all data be encrypted with keys the company controls and can rotate, and that access to the keys be auditable. The company also wants to avoid managing key material on-premises. Which approach should the data engineer choose?

A.Use Customer-Managed Encryption Keys in Cloud KMS with a key ring in the required region.
B.Use Customer-Supplied Encryption Keys and store the key material in a local secrets file.
C.Use Google-managed encryption keys and rely on Google's default encryption for the bucket.
D.Use Cloud External Key Manager to connect to a third-party external key management system.
AnswerA

CMEK in Cloud KMS lets the company own, rotate, and disable keys while Google manages the underlying infrastructure, satisfying the no-on-premises requirement. A regional key ring keeps key material aligned with the data residency constraint, and Cloud KMS logs key operations to Cloud Audit Logs for auditability. This meets all stated requirements.

Why this answer

Customer-Managed Encryption Keys in Cloud KMS give the company ownership and rotation control while Google operates the key infrastructure, and a regional key ring aligns key storage with the data residency requirement. Cloud KMS also writes key usage to audit logs. Default encryption, customer-supplied keys, and external key managers either remove control or push key management outside Google Cloud.

Exam trap

The trap here is equating default Google-managed encryption with customer-controlled keys, when only CMEK provides rotation and auditable key ownership.

Practice this question →
101
MCQhard

A retail company stores 500 TB of JSON transaction logs in Cloud Storage. Analysts need to run ad hoc SQL over the data, and the company wants to avoid managing a separate cluster while keeping query cost predictable. The logs are already partitioned into date-based prefixes. What should the data engineer do?

A.Create a Dataproc cluster with Hive and query the JSON logs through Hive external tables on Cloud Storage.
B.Load all 500 TB into a BigQuery native table partitioned by ingestion time and query the native table.
C.Create a BigQuery external table over the Cloud Storage prefixes and use a hive-partitioned layout so queries use partition pruning.
D.Use Cloud SQL for PostgreSQL with the foreign data wrapper to read the JSON files from Cloud Storage.
AnswerC

BigQuery external tables can query Cloud Storage data directly without loading it and without a cluster to manage. Defining hive partitioning on the date prefixes lets BigQuery prune partitions based on the date filter, which keeps query cost tied to the partitions scanned rather than the whole 500 TB.

Why this answer

An external table over Cloud Storage gives serverless SQL access to the JSON logs, and hive partitioning on the date prefixes enables pruning so only the relevant date partitions are read. This avoids loading 500 TB, removes cluster management, and keeps cost predictable because bytes billed track the partitions scanned.

Exam trap

The trap here is assuming external tables always scan all files, when hive partitioning lets BigQuery prune based on the partition key in the path.

Practice this question →
102
MCQeasy

Which BigQuery feature allows you to read data directly from Cloud Storage without loading it into BigQuery storage?

A.External tables
B.BI Engine
C.Federated queries
D.Authorized views
AnswerA

External tables define a schema over data residing in Cloud Storage, letting BigQuery query it directly without ingestion. This satisfies the constraint of reading Cloud Storage data without loading it into BigQuery's native storage, unlike native tables which require loading.

Why this answer

External tables in BigQuery allow you to query data directly from Cloud Storage (e.g., CSV, JSON, Avro, Parquet) without loading it into BigQuery's native storage. This is achieved by creating a table that references the external data source, enabling federated queries and on-demand access. The data remains in Cloud Storage, and BigQuery reads it at query time, which is ideal for one-time analysis or when data is frequently updated externally.

Exam trap

PDE often tests the distinction between external tables and federated queries, as both involve querying external data; candidates may confuse the two, but external tables specifically target Cloud Storage, while federated queries target other databases.

How to eliminate wrong answers

Option B is wrong because BI Engine is an in-memory analysis service that accelerates queries on BigQuery data, not a feature for reading external data. Option C is wrong because federated queries typically refer to querying data in external databases (e.g., Cloud SQL) via BigQuery, not directly from Cloud Storage; the term is broader and not specific to Cloud Storage. Option D is wrong because authorized views are used to share query results with specific users or groups, not to read data from external sources.

Practice this question →
103
Multi-Selectmedium

A company is migrating an on-premises PostgreSQL database to Google Cloud. They need a fully managed database that is compatible with PostgreSQL and can handle both transactional and analytical workloads with high performance. Which two database services meet these requirements? (Choose TWO.)

Select 2 answers
A.Cloud Spanner
B.Cloud SQL for PostgreSQL
C.BigQuery
D.AlloyDB
E.Firestore
AnswersB, D

Fully managed, PostgreSQL-compatible, supports OLTP and some analytical queries.

Why this answer

Cloud SQL for PostgreSQL (B) is correct because it is a fully managed service that runs the native PostgreSQL engine, so it is directly compatible with the on-premises PostgreSQL database and supports transactional (OLTP) workloads. AlloyDB (D) is also correct because it is a fully managed, PostgreSQL-compatible database engine that is specifically designed for high-performance transactional workloads while also accelerating analytical queries through its columnar engine, making it well suited to mixed transactional and analytical use. Cloud Spanner (A) is not the right fit here because, although it is fully managed and highly scalable, it is not PostgreSQL-compatible in the native sense and is aimed at globally distributed OLTP rather than this migration scenario.

BigQuery (C) is a fully managed analytical data warehouse optimized for OLAP, not a PostgreSQL-compatible transactional database. Firestore (E) is a fully managed NoSQL document database and is neither PostgreSQL-compatible nor designed for the transactional/analytical mix described.

Exam trap

Google often tests the distinction between general-purpose managed databases (Cloud SQL) and specialized high-performance databases (AlloyDB), and the trap here is that candidates may think BigQuery or Spanner are PostgreSQL-compatible because they support SQL, but they do not support the PostgreSQL dialect or transactional workloads natively.

Practice this question →
104
MCQmedium

A data engineer is designing a BigQuery table to store customer order records. Queries frequently filter by order_date and customer_id, and the dataset grows by about 5 TB per day. The engineer wants to minimize query cost and improve performance for these filtered queries. What should the engineer do?

A.Create a materialized view that pre-aggregates orders by customer_id and order_date.
B.Enable BigQuery BI Engine and reserve memory for the dataset.
C.Partition the table by order_date and cluster by customer_id.
D.Use a wildcard table over daily sharded tables named orders_YYYYMMDD.
AnswerC

Partitioning by order_date allows BigQuery to prune partitions when queries filter on that column, reducing bytes scanned and cost. Clustering by customer_id further sorts data within each partition, improving filter and aggregation performance for customer-specific queries. This combination directly addresses the access patterns described and is the recommended approach for large, time-series-like datasets with common filter columns.

Why this answer

Partitioning by order_date enables partition pruning, so queries with date filters scan only relevant partitions, cutting cost and improving speed. Clustering by customer_id sorts data within partitions, making customer-filtered queries more efficient. Together they match the described query patterns and scale well with daily 5 TB loads, unlike materialized views, wildcard tables, or BI Engine, which do not address base-table scan efficiency.

Exam trap

The trap here is assuming that a materialized view or BI Engine can replace partitioning and clustering for reducing scan cost on large base tables.

Practice this question →
105
Multi-Selecteasy

A company wants to implement a data lake on Google Cloud. They need to store raw, structured data in open formats and allow querying directly from BigQuery without loading. Which TWO services or features should they use? (Choose 2)

Select 2 answers
A.Cloud Storage (GCS)
B.Dataproc
C.Dataflow
D.Cloud SQL
E.BigLake
AnswersA, E

GCS is the underlying storage for the data lake, storing data in open formats like Parquet/ORC.

Why this answer

Cloud Storage (GCS) [CORRECT] is the right choice because a data lake on Google Cloud stores raw, structured data as objects in open formats (e.g., Parquet, Avro, ORC, JSON) in buckets, providing durable, scalable, and cost-effective storage. BigLake [CORRECT] is also correct because it is a storage engine that lets BigQuery query data directly in Cloud Storage (and other sources) without loading it, using external tables with fine-grained access control and metadata caching. Together, GCS holds the raw files and BigLake exposes them to BigQuery for direct querying, which matches the requirement exactly.

Dataproc (B) is a managed Spark/Hadoop service for processing, not the storage or direct-query layer, and Dataflow (C) is a managed Apache Beam pipeline service for stream/batch processing, so neither fulfills the storage-plus-query requirement. Cloud SQL (D) is a managed relational database for transactional workloads, not a data lake storage layer, and it does not provide the open-format object storage or BigQuery external querying described.

Exam trap

Google often tests the misconception that Dataproc or Dataflow are required for querying data in a data lake, when in fact BigQuery external tables and BigLake provide direct querying without loading, and the key is to recognize that storage (GCS) and the query engine (BigLake) are the correct services.

Practice this question →
106
MCQmedium

A global e-commerce platform requires a relational database that can handle millions of transactions per second across regions with strong consistency and automatic failover. The database must also support SQL joins. Which database should they choose?

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

Cloud Spanner satisfies the stem's demand for horizontal scalability with strong consistency by using TrueTime and Paxos across regions, delivering automatic failover and ANSI SQL joins. Unlike eventually consistent NoSQL stores, it preserves relational semantics at global scale, matching millions of transactions per second.

Why this answer

Cloud Spanner is the correct choice because it is a globally distributed, horizontally scalable relational database that provides strong consistency and automatic failover across regions, while fully supporting SQL joins. It combines the benefits of a traditional relational database with the horizontal scalability of NoSQL systems, making it ideal for high-throughput, globally distributed applications requiring ACID transactions.

Exam trap

A common trap in the Google Professional Data Engineer exam is to assume Cloud SQL is suitable for global-scale, high-consistency workloads because it is a relational database, but it lacks the global distribution and automatic failover capabilities required for millions of transactions per second across regions.

How to eliminate wrong answers

Option B (Cloud SQL) is wrong because it is a single-region, vertically scalable relational database that cannot handle millions of transactions per second across regions with automatic failover; it lacks global distribution and strong consistency across regions. Option C (Cloud Firestore) is wrong because it is a NoSQL document database that does not support SQL joins and is designed for mobile and web apps with eventual consistency, not for high-throughput relational workloads requiring strong consistency. Option D (Cloud Bigtable) is wrong because it is a NoSQL wide-column database that does not support SQL joins or relational queries; it is optimized for analytical and time-series workloads, not transactional applications requiring ACID properties.

Practice this question →
107
Multi-Selecthard

A financial services firm stores transaction records in a BigQuery table partitioned by transaction_date. Compliance requires that rows older than seven years be permanently and irreversibly deleted, and the team must prove deletion occurred. The table currently uses the default partition expiration of never. The engineer must implement the retention policy without dropping the whole table. (Choose two.)

Select 2 answers
A.Use Cloud Key Management Service with a customer-managed encryption key and destroy the key version after seven years.
B.Verify retention by querying INFORMATION_SCHEMA.PARTITIONS to confirm partitions beyond the retention window no longer exist.
C.Configure a Cloud Storage lifecycle rule on the table's underlying data to remove objects older than seven years.
D.Create a scheduled query that runs DELETE FROM the table WHERE transaction_date < DATE_SUB(CURRENT_DATE(), INTERVAL 7 YEAR).
E.Set a partition expiration on the table so partitions older than the required retention window are deleted automatically.
AnswersB, E

After partition expiration removes old partitions, INFORMATION_SCHEMA.PARTITIONS lists the partitions that remain, giving auditors evidence that partitions past the seven-year boundary are gone. This metadata view is the standard way to demonstrate which partitions exist and their sizes. Combined with partition expiration, it provides the proof of deletion that compliance demands.

Why this answer

Partition expiration is the native BigQuery mechanism that irreversibly removes whole partitions once they age past the retention window, and it operates without dropping the table so recent data stays available. The compliance proof comes from INFORMATION_SCHEMA.PARTITIONS, which shows exactly which partitions remain. A scheduled DELETE leaves recoverable copies through time travel and fail-safe, Cloud Storage lifecycle rules cannot see BigQuery-managed storage, and key destruction is not selective enough to age out only old rows.

Exam trap

The trap here is treating a scheduled DELETE as equivalent to partition expiration, when time travel and fail-safe retention mean deleted rows are not immediately and irreversibly gone.

Practice this question →
108
MCQeasy

A company wants to use BigQuery to query data stored in Parquet files in Cloud Storage without loading the data into BigQuery. Which BigQuery feature should they use?

A.BigQuery Omni
B.BigQuery ML
C.BigQuery external tables
D.BigQuery BI Engine
AnswerC

External tables let BigQuery query Parquet files in Cloud Storage directly, reading the data in place without ingestion. This satisfies the no-loading constraint, since no data is copied into BigQuery's native storage and queries run against the external source.

Why this answer

BigQuery external tables allow querying data stored in Cloud Storage (including Parquet files) directly without loading it into BigQuery storage. This feature uses a federated query engine that reads the data on the fly, supporting formats like Parquet, Avro, ORC, CSV, and JSON. Option C is correct because it directly addresses the requirement to query Parquet files in Cloud Storage without ingestion.

Exam trap

Google often tests the distinction between features that query external data (external tables) versus features that process data within BigQuery (like BI Engine) or across clouds (Omni), leading candidates to confuse Omni's multi-cloud capability with external data access in the same cloud.

How to eliminate wrong answers

Option A is wrong because BigQuery Omni is designed to query data across multi-cloud environments (AWS, Azure) using BigQuery's interface, not for querying Parquet files in Cloud Storage without loading. Option B is wrong because BigQuery ML is a machine learning feature that enables creating and executing models using SQL, not for querying external data files. Option D is wrong because BigQuery BI Engine is an in-memory analysis service that accelerates dashboard queries on data already stored in BigQuery, not for querying external Parquet files in Cloud Storage.

Practice this question →
109
MCQhard

An e-commerce company uses Cloud Spanner for order processing. They need to query orders by customer ID and retrieve all order items. Which schema design pattern should they use for optimal performance?

A.Use interleaved tables where Orders is the parent and OrderItems is an interleaved child table with the same primary key prefix.
B.Store all data in a single table with nullable columns for order item attributes.
C.Denormalize by storing order items as a repeated field in the orders table.
D.Create two separate tables with a secondary index on customer_id in the orders table and a secondary index on order_id in the order_items table.
AnswerC

Spanner is a relational database; repeated fields are not supported. Denormalization would break relational integrity.

Why this answer

For this access pattern, the optimal Spanner design is to denormalize order items as a repeated field in the Orders table. A repeated field stores child rows inline with the parent row, so a query by customer_id (with a secondary index on customer_id) retrieves the order and all its items in a single lookup without a join or extra network round-trip. Interleaved tables co-locate child rows with the parent, but they only help when the query uses the parent's primary key prefix; querying by customer_id would still require a secondary index and then a join-like lookup to fetch interleaved children, so option A is not the best fit here.

Exam trap

A common pitfall in Google PDE exams is assuming interleaved tables automatically optimize any parent-child query. Interleaving only provides physical co-location when the query uses the parent's primary key prefix; queries on non-key columns like customer_id still need a secondary index and can incur additional lookups. For read patterns that always fetch the full child set with the parent, a repeated field is often the better denormalization choice.

How to eliminate wrong answers

Option B is wrong because a single table with nullable columns violates normalization, wastes storage, and forces complex queries to filter order-item rows from order rows, eliminating Spanner's interleaving performance benefit. Option C is wrong because storing order items as a repeated field (e.g., ARRAY<STRUCT>) prevents independent indexing and filtering of individual items, and updating a single item requires rewriting the entire order row, causing contention and poor concurrency. Option D is wrong because two separate tables with secondary indexes on customer_id and order_id require a distributed join across splits, incurring cross-node communication and higher latency compared to the co-located access of interleaved tables.

Practice this question →
← PreviousPage 2 of 2 · 109 questions total

Ready to test yourself?

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

Start 20-question session