Free Google Cloud Professional Data Engineer practice test

Option C is correct. Partitioning by result_date aligns with the most common filter pattern (queries filtering by date range, e.g., "results from the last 30 days"). Clustering by test_name and patient_id optimizes the secondary filter and join patterns. Partitioning reduces the data scanned for date-range queries (mandatory pruning), while clustering provides optional pruning for test_name. BigQuery charges by data scanned, so this strategy minimizes cost. Option D (partition by patient_id) is inefficient—patient_id has high cardinality (~millions of unique values), creating too many partitions and fragmenting data, which hurts query performance and increases metadata overhead. Option A (partition by test_name) creates too few partitions; test_name has low cardinality and doesn't reflect access patterns. Option B (no partitioning) scans the entire table for every query, increasing cost and latency—partitioning is essential for billion-row tables.

Option 2 is correct: This is the most cost-effective approach for predictable, scheduled batch jobs:

• Cloud Scheduler triggers the job at a fixed time (e.g., 5 AM), allowing 4 hours for processing to complete by 9 AM.
• Spot VMs cost 60-80% less than on-demand; since Dataproc jobs are fault-tolerant and re-runnable, spot VM preemption is acceptable (reschedule via Cloud Scheduler logic or Dataproc's built-in auto-recovery).
• Tearing down the cluster post-job saves costs during the 20-hour idle window (no cluster running overnight).
• This is ideal for predictable daily jobs.

Option 0 is incorrect: CUDs require minimum 1-year commitment and upfront payment; they're best for always-on infrastructure. This job runs 4 hours/day, leaving 20 hours of unused capacity. CUDs would be wasteful. Spot VMs (option 2) are cheaper for variable workloads.

Option 1 is incorrect: Dataflow is better for streaming or complex ETL workflows, not simple batch transformations. BigQuery Flex Slots are useful for variable query loads but don't replace Dataproc for Spark jobs. This would likely be more expensive and introduces unnecessary complexity.

Option 3 is incorrect: While optimizing Spark partitioning is good practice, it doesn't change the fundamental cost structure. A persistent on-demand cluster incurs charges 24/7 ($X/hour × 24 hours), even when idle. Spot VMs with cluster teardown (option 2) is more cost-effective than persistent clusters.

Options B and F are correct. Option F (Dataplex) provides the governance fabric for a data mesh—it manages distributed data assets, enforces policies, and provides lineage without centralizing data ownership. CMEK ensures encryption meets compliance needs. Option B (Organization Policies + Dataplex) combines infrastructure-level enforcement (org policies prevent non-compliant configs) with data-layer governance (Dataplex for discovery). Together, they enforce compliance across autonomous teams. Option A fails the data mesh principle by centralizing data into a single warehouse, removing domain autonomy. Option D focuses only on classification via DLP but doesn't address enforcement; DLP is a tool for detecting sensitive data, not a governance platform. Option C is noncompliant and risky. Option E is incorrect and contradicts the data mesh architecture.

Option 2 is correct. BigQuery's federated queries directly on Cloud Storage (especially with efficient columnar formats like Parquet/ORC) provide fast query latency (seconds) with minimal ETL overhead and no storage duplication. Option 1 adds unnecessary latency (Dataflow processing time) and storage cost (BigQuery table storage). Option 3 is inappropriate; Bigtable is for time-series or key-value data, not unstructured log files, and requires pre-processing to extract structured fields. Option 4 (Pandas UDF) is not a GCP-native solution for this scale and would require pulling 100 GB daily into client memory, which is inefficient.

Dataflow's horizontal autoscaling continuously monitors pipeline throughput and the unprocessed backlog (e.g., the number of unacknowledged Pub/Sub messages) and automatically scales the number of worker VMs up or down to meet demand, directly addressing sudden traffic spikes without manual intervention. Option D is incorrect because the Shuffle Service (now part of the Streaming Engine for streaming jobs) offloads state and shuffle data but does not by itself scale worker count in response to volume spikes. Option B is incorrect because FlexRS is a batch-only feature that schedules jobs on spare capacity using preemptible VMs to reduce cost; it is not available for streaming jobs and does not address real-time spike handling. Option C is incorrect because Dataflow templates allow parameterized job launches but do not provide automatic scaling—they still require a human or external automation to detect the spike and trigger a restart.

The correct answer is option 1. Loading the 100 GB dimension table into BigQuery (which is fast and inexpensive) and performing a native BigQuery JOIN is the most cost-effective approach because: (1) BigQuery's JOIN is optimized for large datasets, (2) you avoid spinning up a large Spark cluster in Cloud Data Fusion, and (3) Cloud Data Fusion can efficiently pull the joined result. Option 2 is incorrect because it requires a full Cloud Data Fusion cluster to perform the join, which is expensive for a relatively small dimension table (100 GB). Option 3 is incorrect because exporting 5 TB from BigQuery to Cloud Storage is costly and slow, defeating the purpose of using BigQuery. Option 4 is incorrect because caching 100 GB in memory on a 2-node cluster is not feasible (each node wouldn't have enough memory), and it doesn't address the fact that the 5 TB fact table still needs to be processed.

Option 0 is correct. Datastream instance size directly affects the parallelism and throughput of snapshot operations. Increasing CPU and memory allows more parallel workers to read from the source database, significantly accelerating the initial sync. Option 1 is incorrect—adding indexes helps with query performance at the source but doesn't directly improve Datastream's snapshot throughput. Option 2 is operationally complex and may not address the root cause; Datastream is designed to handle large tables. Option 3 is incorrect—streaming inserts are for ongoing CDC replication, not the initial snapshot, and don't solve the 8-hour bottleneck for large table synchronization.

The correct answers are options 0 and 5. Both correctly address the requirement: fixed windows are appropriate for per-user revenue aggregation, afterWatermark trigger fires when the watermark passes the window's end time, and allowedLateness of 2 hours ensures late events are included in results. Option 5 adds afterCount(1) for additional pane emissions but is still valid. Option 1 is incomplete—it lacks the allowedLateness configuration. Option 2 explicitly disables late data processing, contradicting the requirement. Option 3 uses discardingFiredPanes, which discards panes instead of accumulating them, losing late data contributions. Option 4 uses session windows and event-count triggers, which are not ideal for revenue aggregation requiring consistent time-based windows.

Option 1 is correct. Pub/Sub's ordering guarantee (enabled per-topic with an ordering key) ensures that messages with the same key arrive in order to all subscriptions. Dataflow automatically processes messages with the same key on the same worker/thread, enforcing sequential processing per key. No additional configuration is needed beyond setting the ordering key to customer_id. Option 0 is wrong because single-threaded workers are inefficient and Dataflow doesn't require this when ordering is enabled. Option 2 is impractical and doesn't scale. Option 3 is incorrect—Dataflow's stateful processing does not enforce ordering by itself; it requires ordered input from Pub/Sub.

A and C are correct. A is true—slots provide a fixed commitment cost, making budget predictable. C is true—slots offer better ROI for steady-state workloads with consistent query volume. B is misleading in framing ('twice as much' phrasing is accurate but incomplete); on-demand is simpler but not directly compared to slots this way. D overgeneralizes; on-demand can be competitive for true bursty loads, but slots offer better unit economics for many sustained patterns. E is false—idle slots still incur their commitment cost; there's no automatic pausing. F is false—slots and on-demand are billing models, not requiring schema changes.

Since files are read multiple times in the first week, Standard storage is appropriate for the active period (no retrieval fees, no minimum storage duration). After 30 days the files are never accessed, so deleting them via a lifecycle rule eliminates all ongoing storage cost — the most economical outcome. Option D uses Nearline as the default (30-day minimum duration, retrieval fees), which is suboptimal for files accessed multiple times per week; transitioning to Archive after 30 days still incurs Archive storage cost when deletion is cheaper. Option A transitions to Coldline at 7 days — files are still being accessed during days 7–30, so Coldline retrieval fees would apply, increasing cost. Option C puts all new files in Coldline immediately; retrieval fees for multiple reads per week on Coldline storage would far exceed Standard storage savings.

Options 1 and 3 are correct. Option 1 uses authorized views (a BigQuery security feature) to expose department-specific columns and tables; this is the standard pattern for fine-grained access control without modifying the underlying dataset. Option 3 combines BigQuery RLS policies (to filter rows by department) with column masking policies (to hide sensitive columns like salary), providing both row and column-level access control in a single dataset. Option 2 is unnecessarily complex; cross-dataset views add operational overhead. Option 4 (roles/bigquery.dataEditor) grants broad write permissions, inappropriate for analysts who should have read-only access. Option 5 (100 datasets per analyst) is unmaintainable.

Analytics Hub is the purpose-built Google Cloud service for sharing live BigQuery datasets across organizational boundaries. Publishers create a listing; subscribers create a linked dataset in their own project that queries the original data without copying it. Revoking the listing or subscriber's access immediately removes query access. Option B (authorized views + IAM) works within a single organization but managing 50 external IAM bindings is operationally complex and does not provide the governed marketplace model Analytics Hub offers. Option A (signed URLs for exports) copies data, is not live, and managing 50 time-bounded URLs is cumbersome. Option C (Data Transfer Service copies) physically replicates data to each partner project, breaking the 'no copy' requirement and creating data governance challenges.

The correct answers are A and C. Answer A (separate authorized views with static WHERE clauses) is a valid approach: you create two views—one for finance (no WHERE clause) and one for regional managers (WHERE region = 'X')—then grant each role access to the appropriate view. Answer C (RLS with session variables) is also correct and more scalable: you define RLS rules that evaluate SESSION_USER() or custom session variables at query time, applying region filtering dynamically. Answer D is partially correct but misleading—while SESSION_USER() in a single view could work, granting different IAM roles different permissions on a view is complex and not the intended design pattern. Answer B (policy tags) is for column-level masking, not row filtering, so it doesn't address the per-region filtering requirement. Answer E (Looker Studio filters) provides no real security; filters are applied client-side and can be bypassed if users query BigQuery directly.

The correct answers are A (NULL values) and D (masked/hashed values depending on the masking rule). Policy tags enforce column-level access control: by default, unauthorized users see NULL in policy-tagged columns (Answer A), but administrators can configure custom masking rules (like hashing or redaction) so unauthorized users see masked values instead (Answer D). The actual behavior depends on the policy tag's masking rule configuration. Answer B is false—authorized views do NOT bypass policy tag enforcement; policy tags operate at the column level regardless of how the data is accessed. Answer C is incorrect; users aren't denied access to the entire table, only to specific columns.

JavaScript UDFs in BigQuery can be marked with OPTIONS(deterministic=true) to enable result caching when the same inputs are provided, which optimizes performance. Option A uses imprecise language ('safely cached' without the explicit DETERMINISTIC option). Option B is incorrect; both JavaScript and SQL UDFs support the DETERMINISTIC option. Option C is incorrect; without the DETERMINISTIC flag, the UDF is not automatically re-evaluated for identical inputs—BigQuery's optimizer may still cache, but the explicit option ensures it. The correct approach is to explicitly declare DETERMINISTIC=true.

The correct answers are B and D. Option B directly addresses quota limits by guaranteeing write capacity through slot reservations, while exponential backoff handles transient failures. Option D (windowing/triggers + monitoring) controls write concurrency and provides visibility into usage, preventing quota saturation. Together, they provide both capacity and intelligent throttling. Option E is reactive, not scalable. Option C adds unnecessary complexity with Pub/Sub. Option A (Streaming Inserts) doesn't avoid quota limits—they have different quotas and can still be exceeded, and they're not appropriate for all use cases.

BigQuery automatically applies long-term storage pricing (approximately 50% discount) to any table or partition that has not been modified in 90 consecutive days. The key action is to avoid unnecessary writes or updates to old partitions (e.g., not running UPDATE/MERGE on them), which would reset the 90-day clock. No configuration change is needed — simply letting unmodified partitions age unlocks the discount while keeping full query access. Option A (90-day partition expiration) would DELETE the data, not retain it, which violates the requirement of maintaining query access. Option B moves data to Cloud Storage, which reduces BigQuery storage costs further but adds complexity, ETL overhead, and external table query performance drawbacks — not 'most cost-effective' given long-term pricing already achieves a 50% reduction with no operational change. Option C destroys historical data by replacing the table every 90 days.

The correct answer is C. Since CPU and memory utilization are low, the bottleneck is not resource scarcity but likely a slow operation (e.g., BigQuery writes, external API calls, or an inefficient transform). Reviewing pipeline metrics and step-level latencies in the Dataflow UI will identify the bottleneck. Option A is speculative; BigQuery quotas typically aren't the first culprit with low CPU/memory. Option D is incorrect; low utilization doesn't indicate parallelism issues—it suggests idle time waiting for something else. Option B is misleading; Pub/Sub automatically rebalances across Dataflow workers, so more workers alone won't help if the bottleneck is elsewhere.

The correct answer is C. The symptom is doubled bytes_processed, indicating the query is scanning twice as much data. This is most consistent with a query logic change—such as a removed WHERE clause—that now processes more rows before filtering. You should audit recent changes to the query. Option A (partitioning) would help but is less likely the cause of a sudden doubling if partitioning wasn't recently removed. Option B is incorrect; query caching affects execution time, not bytes_processed. Option D is false; compression is transparent to bytes_processed metrics.