GCP-ADP Full-Length Exam Simulation

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

Which THREE of the following are valid GCS storage classes?

Accepted Answer

Nearline. Option B (Nearline) is a valid GCS storage class designed for data accessed less than once per month, offering lower storage cost with a 30-day minimum storage duration. Option D (Coldline) is also valid, intended for data accessed less than once per quarter, with a 90-day minimum storage duration and retrieval costs. Option E (Standard) is the default GCS storage class for frequently accessed ("hot") data with no minimum storage duration and the highest availability. Options A (Warmline) and C (Hotline) are not real GCS storage classes; they are fabricated names that do not correspond to any Google Cloud Storage offering.

Answer

Warmline

Answer

Hotline

Question 3

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 4

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 5

Which service should you use to ingest streaming data from mobile devices?

Accepted Answer

Pub/Sub. Pub/Sub is designed for real-time messaging and ingestion of streaming data from various sources, including mobile devices. It decouples producers from consumers, allowing mobile apps to publish events to topics that downstream services can subscribe to. This makes it ideal for high-throughput, low-latency streaming ingestion. Other options are storage or analytics services, not ingestion services.

Answer

Cloud Storage

Answer

BigQuery

Answer

Cloud SQL

Question 6

Which service allows you to view the schema and metadata of a BigQuery table that has been registered in Dataplex?

Accepted Answer

Data Catalog. Data Catalog is the metadata management service in Google Cloud that indexes and exposes schema, column names, descriptions, tags, and other metadata for BigQuery tables (and other assets) registered in Dataplex. Once a BigQuery table is registered as a Dataplex asset, its technical metadata is surfaced through Data Catalog, which is the intended UI/API for schema and metadata discovery.

Answer

Cloud Storage Console

Answer

Cloud Build

Answer

Cloud IAM console

Question 7

You need to analyze a petabyte-scale dataset in BigQuery. To optimize cost, you must ensure that users only query relevant partitions. How should you enforce this?

Accepted Answer

Enable the 'Require partition filter' option in the table settings.. BigQuery's 'Require partition filter' table option forces every query against the table to include a WHERE clause that filters on the partitioning column, preventing full-table scans and the associated on-demand costs. This is the native, enforced mechanism for ensuring users only query relevant partitions. It applies to partitioned tables and rejects queries that lack a partition predicate.

Answer

Use a materialized view for all queries.

Answer

Apply a data masking policy to the partitioning column.

Answer

Set a project-level query quota.

Question 8

How do you restrict access to a specific GCS bucket to only members of a specific team?

Accepted Answer

Apply an IAM policy to the bucket. Bucket-level IAM policies are the native GCS mechanism for granting access to specific principals. By binding roles such as roles/storage.objectViewer to a Google Group representing the team, only members of that group inherit access to the bucket. This enforces least privilege at the resource boundary without affecting other buckets in the project.

Answer

Use file-level permissions

Answer

Use project-level roles only

Answer

Use public access

Question 9

You manage a BigQuery dataset that contains a table with millions of rows. Analysts frequently query only the most recent 30 days of data, but full-table scans are expensive. You need to reduce query costs and improve performance for these time-based queries with minimal changes to the table structure. What should you do?

Accepted Answer

Partition the table by a DATE or TIMESTAMP column using daily partitioning.. Partitioning a BigQuery table by a date or timestamp column enables partition pruning, so queries that filter on that column scan only the relevant partitions. This directly reduces the bytes processed and improves performance for time-based queries. Other options either do not reduce scanned data for arbitrary filters or add unnecessary complexity without addressing the root cause.

Answer

Create a separate table for each month and use a UNION ALL view to combine them.

Answer

Create a materialized view that selects the last 30 days of data and instruct analysts to query the view.

Answer

Enable the BigQuery BI Engine reservation on the dataset.

Question 10

You have a Looker Studio report connected to a BigQuery table that contains millions of rows. The report takes a long time to load and often times out. You need to improve performance without changing the underlying data. What should you do?

Accepted Answer

Enable BigQuery BI Engine and set up a reservation for the dataset used by the report.. BI Engine provides an in-memory cache that accelerates queries from Looker Studio, reducing latency and preventing timeouts. It integrates seamlessly with BigQuery and requires no data duplication. The other options either do not improve performance or introduce impractical workarounds that fail with large datasets.

Answer

Set the Looker Studio report to use 'Extract data' with a refresh schedule of every 15 minutes.

Answer

Increase the number of Looker Studio data sources by adding duplicate connections to the same table.

Answer

Schedule a daily extract of the BigQuery table into a Google Sheet and connect Looker Studio to the Sheet.

Question 11

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 12

Your team uses Cloud Composer (Airflow) to run a daily ETL DAG. A task occasionally fails due to transient network errors when calling an external API. You want the task to retry automatically up to 3 times with a 5-minute delay between attempts, and you want the DAG to continue running other tasks even if this task ultimately fails. Which combination of Airflow task parameters should you configure?

Accepted Answer

Set retries=3, retry_delay=timedelta(minutes=5), and set the task's downstream tasks to use trigger_rule='all_done'.. To handle transient failures, configure retries and retry_delay on the task. To allow the DAG to continue despite a task failure, set the downstream tasks' trigger_rule to 'all_done', which triggers them regardless of upstream success or failure. This combination ensures retries occur and subsequent tasks are not blocked, meeting both requirements.

Answer

Set retries=3, retry_delay=timedelta(minutes=5), and set the task's trigger_rule='all_failed'.

Answer

Set retries=3, retry_delay=timedelta(minutes=5), and set the task's trigger_rule='all_success'.

Answer

Set retries=3, retry_delay=timedelta(minutes=5), and trigger_rule='all_done' on the task.

Question 13

You are using Cloud Scheduler to trigger a Cloud Workflows execution every hour. You need to ensure that the scheduler authenticates to the Workflows API securely. Which two of the following are valid methods for authenticating Cloud Scheduler to Workflows? (Choose two.)

Accepted Answer

Use an OAuth token generated for a service account that has the Workflows Invoker role.. Cloud Scheduler can authenticate to Workflows using either OIDC or OAuth tokens generated for a service account. The service account must have the Workflows Invoker role. These methods leverage Google Cloud IAM and avoid managing static secrets, making them secure and recommended. API keys and basic auth are not supported for the Workflows API.

Answer

Use an API key associated with the Workflows API and include it in the request header.

Answer

Use an OAuth 2.0 client ID and secret stored in Cloud Scheduler's configuration.

Answer

Use a static username and password stored in Secret Manager and referenced by Cloud Scheduler.

Question 14

Which THREE of the following are valid Airflow connection types in Cloud Composer?

Accepted Answer

HTTP. Option A (HTTP) is correct because Cloud Composer's Airflow connection types include 'HTTP', which stores host, schema, login, password, and port for calling REST endpoints via the HttpHook. Option D (Google Cloud) is correct because 'Google Cloud' is a built-in connection type used to store a GCP service account key or scopes, enabling operators like BigQueryInsertJobOperator and GCS hooks to authenticate. Option E (Postgres) is correct because 'Postgres' is a standard Airflow connection type that stores host, schema, login, password, and port for the PostgresHook and PostgresOperator. Option B (Kubernetes Pod) is not a connection type; KubernetesPodOperator uses a 'Kubernetes' connection (or in-cluster config), not 'Kubernetes Pod'. Option C (Python) is not a connection type; Python callables are executed via the PythonOperator, and no 'Python' connection exists in Airflow's connection type list.

Answer

Kubernetes Pod

Answer

Python

Question 15

You have sensitive data in Cloud Storage that needs to be deleted after 30 days to comply with regulatory requirements. What is the most efficient way to manage this?

Accepted Answer

Use Cloud Storage lifecycle management policies. Cloud Storage Object Lifecycle Management lets you define rules directly on a bucket (e.g., Delete objects 30 days after creation) that GCS enforces automatically at no additional cost. This is the native, server-side, most efficient mechanism for time-based deletion and requires no code, scheduler, or external service.

Answer

Run a manual BigQuery script

Answer

Write a Cloud Function to check dates and delete files

Answer

Create a Dataplex lifecycle task

Question 16

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 17

Your team is ingesting streaming clickstream events from Pub/Sub into BigQuery. You need to ensure that the pipeline can handle duplicate message deliveries without creating duplicate rows in the destination table. What should you do?

Accepted Answer

Enable exactly-once delivery on the Pub/Sub subscription and use a Dataflow pipeline with the BigQuery Storage Write API configured for exactly-once semantics.. End-to-end duplicate elimination for Pub/Sub to BigQuery requires exactly-once delivery on the subscription and a sink that supports exactly-once writes. The BigQuery Storage Write API with exactly-once semantics, combined with Pub/Sub exactly-once delivery, ensures each message is processed and written once. Other options either provide best-effort deduplication or do not prevent duplicates.

Answer

Use the BigQuery streaming insert API with insertId populated from a unique event identifier, and rely on BigQuery to deduplicate rows.

Answer

Use Cloud Data Fusion to read from Pub/Sub and write to BigQuery with the deduplicate plugin enabled.

Answer

Configure the Pub/Sub subscription to use a dead-letter topic and set the maximum delivery attempts to 1.

Question 18

When creating a Looker visualization, which chart type is best for showing the relationship between two numerical variables?

Accepted Answer

Scatter plot.. A scatter plot is specifically designed to display the relationship between two numerical variables by plotting each data point as a dot on a two-dimensional Cartesian plane, with one variable on the x-axis and the other on the y-axis. This allows you to visually assess correlation, clustering, outliers, and patterns such as linear or non-linear relationships. In Looker, the scatter plot chart type automatically maps two measures to the axes, making it the ideal choice for bivariate numerical analysis.

Answer

Donut chart.

Answer

Table chart.

Answer

Area chart.

Question 19

Which service should you use to search for and discover datasets across your entire Google Cloud organization?

Accepted Answer

Data Catalog. Data Catalog is Google Cloud's fully managed, scalable metadata management service that enables search and discovery of datasets across the organization. It provides a unified inventory of data assets with tagging and search capabilities, making it the correct choice for discovering datasets organization-wide.

Answer

BigQuery Explorer

Answer

Cloud Storage Browser

Answer

Cloud Logging

Question 20

A data engineer needs to create a visualization in Looker Studio that shows the relationship between advertising spend and revenue for each marketing campaign. They want to quickly identify which campaigns have high spend but low revenue. Which chart type is most appropriate?

Accepted Answer

Scatter chart with advertising spend on the X-axis and revenue on the Y-axis, with each point representing a campaign.. To identify campaigns with high spend but low revenue, the analyst needs to see the correlation between the two metrics across campaigns. A scatter chart is designed for this purpose, plotting each campaign as a point based on its spend and revenue values. This visual format makes it easy to spot outliers that fall outside the expected pattern, such as those with high spend and low revenue.

Answer

Time series chart with both advertising spend and revenue as metrics over time.

Answer

Geo chart with campaign locations and revenue as the metric.

Answer

Bar chart with campaigns on the X-axis and revenue on the Y-axis, sorted by revenue descending.