GCP-ADP Timed Hard Questions — 25 Difficult Questions

Question 1

You have a query that processes 500GB of data. You want to reduce the cost of this query. What is the most effective approach?

Accepted Answer

Add a WHERE clause to filter by the partitioned column.. BigQuery charges based on the amount of data processed by a query. Adding a WHERE clause that filters on a partitioned column allows BigQuery to prune partitions and scan only the relevant data, drastically reducing the bytes processed and thus the cost. This is the most effective and direct cost-reduction technique.

Answer

Use a smaller machine type for the project.

Answer

Increase the query timeout limit.

Answer

Switch to BigQuery Omni.

Question 2

A user is running a query on a BigQuery table with nested data (JSON). How do you access a field named 'city' inside a record field named 'address'?

Accepted Answer

address.city. BigQuery uses Standard SQL, which accesses nested RECORD fields using dot notation. If 'address' is a RECORD (STRUCT) column containing a 'city' field, you reference it as address.city. This is the canonical syntax for traversing STRUCT types in BigQuery.

Answer

address->city

Answer

address[city]

Answer

JSON_EXTRACT(address, '$.city')

Question 3

You are designing a data lake in Cloud Storage and need to ensure that analysts can query the data using BigQuery without moving or copying the data. You also need to enforce fine-grained access control at the column level for sensitive fields. Which two actions should you take? (Choose two.)

Accepted Answer

Create an external table in BigQuery that points to the Cloud Storage data.. To query Cloud Storage data in BigQuery without moving it, you create an external table. To enforce column-level access control, you apply policy tags to the columns of that external table. Policy tags are the native BigQuery feature for column-level security and work with external tables, meeting both requirements.

Answer

Create a BigQuery dataset and use authorized views to restrict access to specific columns.

Answer

Enable uniform bucket-level access on the Cloud Storage bucket.

Answer

Set up a Dataplex lake and zone, and register the Cloud Storage bucket as an asset.

Question 4

A team maintains a Looker dashboard fed by a BigQuery table that receives streaming inserts throughout the day. Users report that totals on the dashboard sometimes disagree with a report generated directly in BigQuery a few minutes later. What is the most likely explanation for the discrepancy?

Accepted Answer

Streaming inserts land in BigQuery's streaming buffer and are not immediately available to all query paths, so the dashboard and the direct report read slightly different snapshots.. Streaming ingestion does not write directly into fully queryable managed storage; rows first enter a buffer. Some query paths and export operations cannot see buffered rows, so a dashboard query and a report run moments apart can legitimately observe different row sets. The totals converge once the buffer commits, which is why the disagreement is small and transient rather than persistent.

Answer

BigQuery's on-demand query cache is returning an older result set to the dashboard instead of reading current data.

Answer

The dashboard's Looker model uses a different time zone setting than the BigQuery report, shifting some rows into adjacent days.

Answer

Looker is applying a symmetric aggregate that recalculates totals differently each time the dashboard is loaded.

Question 5

In Dataflow, what is the impact of using a high number of workers for a small dataset?

Accepted Answer

Increased cost and overhead. Dataflow provisions workers as a fixed pool sized by the number of workers you specify, and each worker incurs per-hour Compute Engine charges plus associated overhead (startup, shuffle, network). For a small dataset, the work is finished quickly regardless of parallelism, so extra workers add no throughput benefit while multiplying cost and adding coordination overhead. The correct answer is therefore increased cost and overhead, not faster processing.

Answer

Faster processing

Answer

Decreased latency

Answer

Data corruption

Question 6

You are creating a Looker report with a 'measure' that calculates the total revenue. You notice it is summing correctly but the format is wrong. Where do you change the format?

Accepted Answer

In the LookML view file using 'value_format_name'.. In Looker, a measure's display format is defined in the LookML view file using the `value_format_name` parameter (or `value_format` for custom formats). This parameter controls how the numeric value is rendered in the UI without altering the underlying calculation. Since the measure is already summing correctly, only the presentation layer needs adjustment, which is exactly what `value_format_name` does.

Answer

In the dashboard filter settings.

Answer

By editing the underlying SQL.

Answer

In the 'User' settings.

Question 7

Your team needs to audit all modifications made to DAG files in the GCS bucket used by Cloud Composer. What is the most efficient way to achieve this?

Accepted Answer

Enable Data Access audit logs on the GCS bucket.. Enabling Data Access audit logs on the GCS bucket is the most efficient way to audit all modifications made to DAG files because it automatically logs all object-level operations, including who modified what and when. This provides a comprehensive audit trail without requiring custom code or manual monitoring. Data Access audit logs capture both read and write operations, and can be exported to Cloud Logging for analysis and retention.

Answer

Enable 'Version History' in the Airflow UI.

Answer

Use Cloud Monitoring to alert on 'gcs_object_write' events.

Answer

Write a custom DAG to scan the bucket every minute.

Question 8

You have noticed that your data quality tasks are failing. Which THREE of the following are potential causes for failure? (Choose three)

Accepted Answer

The YAML rule configuration is malformed. Option A is correct because data quality tasks in tools like Cloud Dataplex/DQ rely on YAML rule specifications, and malformed YAML (bad indentation, invalid keys, syntax errors) will cause the task to fail during parsing before execution. Option B is correct because the service account executing the data quality job must have the required IAM permissions (e.g., BigQuery Data Viewer, Storage Object Viewer) on the underlying data; missing permissions cause authorization errors and task failure. Option D is correct because data quality tasks often run on a Dataproc cluster or serverless Dataproc environment, so if that environment is unavailable (deleted, misconfigured, or out of quota), the job cannot execute and fails. Option C is not a valid cause because a slow internet connection on the user's side does not affect server-side data quality task execution. Option E is not a valid cause because moving data files to another lake would typically result in a 'not found' or empty-scan condition rather than being a general failure cause, and it is not one of the marked correct answers.

Answer

The user has a slow internet connection

Answer

The data files were moved to another lake

Question 9

You need to ensure that PII (Personally Identifiable Information) in BigQuery is automatically detected and tagged across your organization. Which Dataplex feature should you enable on your Lake?

Accepted Answer

Automatic data discovery. Automatic data discovery in Dataplex scans data in a Lake and automatically identifies and tags sensitive information such as PII using built-in or custom data classifiers. It profiles data assets and applies metadata tags, which is exactly what's needed to detect and tag PII across an organization.

Answer

Data quality task

Answer

Lifecycle management policy

Answer

IAM policy inheritance

Question 10

You are a data engineer at a healthcare company. You need to create a Dataflow pipeline that reads from a Pub/Sub subscription and writes to BigQuery. The pipeline must handle late-arriving data and ensure exactly-once processing. You also need to minimize cost. Which of the following should you use?

Accepted Answer

Use Dataflow with Apache Beam's PubSubIO and BigQueryIO, enabling windowing and triggers for late data, and use BigQueryIO.Write with exactly-once semantics.. Dataflow with Apache Beam provides the necessary constructs for exactly-once processing and late data handling. PubSubIO and BigQueryIO support windowing and triggers, and BigQueryIO.Write can be configured for exactly-once semantics. Other options either lack these features or are not cost-effective for this use case. Therefore, using Dataflow with these configurations is the correct approach.

Answer

Use Dataflow with the Pub/Sub to BigQuery template and set the --discardUnknownFields option.

Answer

Use BigQuery's streaming ingestion with a Pub/Sub subscription directly.

Answer

Use Cloud Data Fusion with a Realtime pipeline that writes to BigQuery.

Question 11

You are configuring a Dataplex asset that points to a GCS bucket. You want to ensure that only files in a specific folder are included in the asset. How do you specify this?

Accepted Answer

Specify a path filter in the asset definition. In Dataplex, when defining a zone or asset that points to a Cloud Storage bucket, you can specify a path filter (a prefix or folder path) in the asset definition to limit discovery and inclusion to only files under that folder. This is the native mechanism for scoping a Dataplex asset to a subset of a bucket.

Answer

Define a SQL view on the folder

Answer

Use an IAM condition on the bucket

Answer

Move the files to a separate bucket

Question 12

You are configuring a Dataplex Data Quality task. You want to validate that a 'user_id' column has no NULL values. Where do you define this rule?

Accepted Answer

In a YAML configuration file. Dataplex Data Quality tasks are defined using a YAML specification that describes the rules, including non-null constraints on columns. The YAML file is uploaded to a Cloud Storage bucket and referenced when creating the Data Quality task. This approach allows for flexible, version-controlled rule definitions that are decoupled from the underlying data schema.

Answer

In the Cloud Storage metadata

Answer

In the BigQuery table schema

Answer

In the Dataplex Asset definition

Question 13

You need to run a Workflows execution based on a schedule. Which Google Cloud service is designed to manage this triggering?

Accepted Answer

Cloud Scheduler. Cloud Scheduler is Google Cloud's fully managed cron service, purpose-built to invoke HTTP endpoints, Pub/Sub topics, or App Engine routes on a defined schedule. Workflows exposes an HTTP trigger endpoint, so a Cloud Scheduler job can POST to that endpoint on a cron expression to start executions. This is the canonical, documented pattern for scheduled Workflows runs.

Answer

Eventarc

Answer

Cloud Tasks

Answer

Pub/Sub

Question 14

You are using Dataflow with autoscaling. What metric determines whether the worker pool scales up or down?

Accepted Answer

Work backlog and throughput. Dataflow autoscaling is driven by the work backlog (unprocessed elements) and throughput relative to the current worker pool. The autoscaler compares the backlog and the rate of processing to decide whether to add or remove workers, targeting a steady-state where backlog stays low without over-provisioning. This is the documented signal for Streaming Engine and batch autoscaling.

Answer

Network latency

Answer

Memory usage

Answer

User-defined triggers

Question 15

You need to load CSV files from GCS to BigQuery. The schema changes frequently. Which approach handles schema evolution automatically?

Accepted Answer

BigQuery Load job with auto-detect. BigQuery load jobs with schema auto-detect inspect the CSV header and sample rows to infer column names and types, and when combined with the load job's schema update options they can add new columns automatically as the source schema evolves. This is the only listed approach that adapts to frequent schema changes without code changes. Dataflow, Dataprep, and Cloud Functions all require the schema to be defined or mapped explicitly, so they break when columns are added or renamed.

Answer

Dataprep with rigid recipe

Answer

Cloud Functions with manual schema mapping

Answer

Dataflow with fixed schema

Question 16

Which THREE of the following are Dataflow windowing types?

Accepted Answer

Fixed. In Google Cloud Dataflow (Apache Beam), the supported windowing types are fixed windows, sliding windows, and session windows, so options A, B, and D are correct. Fixed windows (A) divide a PCollection into non-overlapping, contiguous time intervals of a set duration, such as every 60 seconds. Session windows (B) group elements that arrive close together in time, separated by a gap duration of inactivity, which is useful for user-activity streams. Sliding windows (D) are defined by a window duration and a slide period, allowing windows to overlap, such as a 60-second window that slides every 30 seconds. Binary (C) and Random (E) are not Beam/Dataflow windowing types; no such windowing functions exist in the Dataflow model.

Answer

Binary

Answer

Random

Question 17

You are writing a complex BigQuery query and need to define a temporary table to use later in the same query. What is the most efficient syntax?

Accepted Answer

WITH clause (CTE).. The WITH clause, also known as a Common Table Expression (CTE), defines a temporary named result set that can be referenced later in the same query. It is the most efficient and readable syntax for complex queries because it avoids repeating subqueries and allows modular composition without persisting data.

Answer

Nested subqueries.

Answer

Variable declaration.

Answer

CREATE TABLE AS SELECT.

Question 18

Which THREE steps are involved in an effective Dataflow pipeline development?

Accepted Answer

Deployment. The three correct steps are Deployment (A), Testing (B), and Development (D). Development (D) is where you author the pipeline code using the Apache Beam SDK, defining PTransforms, sources, and sinks before anything can be run. Testing (B) is essential to validate pipeline logic and correctness, typically via unit tests with TestPipeline and DoFnTester, and integration tests against representative data before production. Deployment (A) is the step of running the pipeline on a runner such as Dataflow, whether via the gcloud command, the Dataflow template, or a CI/CD job, to execute it at scale. Automated email alerts (C) are an operational monitoring concern, not a core pipeline development step, and manual data entry (E) is not part of building a Dataflow pipeline at all.

Answer

Automated email alerts

Answer

Manual data entry

Question 19

Which TWO of the following strategies help reduce Cloud Composer costs?

Accepted Answer

Delete unused environments. Option C (Delete unused environments) is correct because Cloud Composer charges for the underlying GKE nodes, Cloud SQL database, and web server that run continuously per environment, so removing environments that are no longer needed eliminates those recurring infrastructure costs. Option E (Enable auto-scaling) is correct because Composer's autoscaling dynamically adjusts the number of worker nodes based on the workload's queue demand, allowing the environment to scale down during idle periods and avoid paying for excess capacity. Option A (Keep all historical logs forever) is wrong because retaining logs indefinitely increases Cloud Logging storage costs rather than reducing them. Option B (Use the largest environment size) is wrong because larger environments provision more workers and thus cost more, not less. Option D (Increase scheduler frequency) is wrong because more frequent scheduler runs increase resource consumption and can raise costs instead of lowering them.

Answer

Keep all historical logs forever

Answer

Use the largest environment size

Answer

Increase scheduler frequency

Question 20

A financial analytics team needs to move several terabytes of on-premises Oracle tables into BigQuery each night. The source database cannot accept inbound connections from Google Cloud, and the team wants minimal operational overhead for the transfer. They plan to use the BigQuery Data Transfer Service. Which connector should they configure?

Accepted Answer

A BigQuery Data Transfer Service Oracle connector configured with an agent installed in the on-premises environment.. The source database cannot accept inbound connections, so the transfer must be initiated from inside the on-premises network. The BigQuery Data Transfer Service database connector uses an agent that makes outbound connections, satisfying the network constraint while providing a managed, scheduled load into BigQuery. Cloud Storage transfers do not parse Oracle data, federated queries need inbound reachability, and a Pub/Sub streaming design adds unnecessary components for a nightly batch job.

Answer

A federated query from BigQuery that uses an external connection to read the Oracle tables directly.

Answer

A Pub/Sub subscription that receives change events streamed from the Oracle database by a third-party tool.

Answer

A Cloud Storage transfer that reads database export files placed in a bucket by an on-premises script.