GCP-ADP Practice Test 5 — 25 Questions

Question 1

When creating a dashboard in Looker Studio, which connector type allows you to connect to an arbitrary SQL query?

Accepted Answer

Custom query.. The Custom Query connector in Looker Studio lets you paste an arbitrary SQL statement and use its result set as a data source, which is exactly what is needed when no single table or view provides the required shape. It supports parameters and works against BigQuery, Cloud SQL, and other SQL-backed sources.

Answer

Direct table link.

Answer

LookML model connector.

Answer

Spreadsheet connector.

Question 2

You need to ingest log data into BigQuery from GCS files that arrive sporadically. Which service is best to trigger this?

Accepted Answer

Cloud Functions. Cloud Functions is an event-driven serverless compute service that can be triggered by changes in Cloud Storage, such as when a new file arrives. It is ideal for sporadic, unpredictable file arrivals because it scales automatically and you only pay for execution time. You can write a function that triggers on the object finalize event and loads the data into BigQuery.

Answer

Cloud Scheduler

Answer

BigQuery Data Transfer Service

Answer

Dataflow

Question 3

You need to perform a rolling update on a Dataflow job without losing current state. How do you do this?

Accepted Answer

Use the Dataflow Update job command. The Dataflow Update job command allows you to update a running pipeline with new code while preserving the pipeline's state, including its data and processing context. This enables rolling updates without losing state.

Answer

Create a new job and point to the same source

Answer

Stop the job and start it again

Answer

Use a pipeline template

Question 4

You are processing streaming data in Dataflow and notice 'stuck' elements causing pipeline latency. Which feature helps debug this?

Accepted Answer

Dataflow Monitoring UI. The Dataflow Monitoring UI provides detailed visibility into pipeline execution, including per-stage metrics, watermarks, and the ability to identify stuck or slow elements causing latency. It surfaces system lag, data freshness, and hot keys that indicate bottlenecks. This makes it the right tool for debugging streaming pipeline latency.

Answer

Dataprep Jobs view

Answer

Cloud Logging

Answer

Cloud Trace

Question 5

When configuring a Workflows execution, you want to ensure the workflow can be triggered by an HTTP POST request. Which feature allows this?

Accepted Answer

Executing the workflow via the REST API.. Workflows executions can be triggered by an HTTP POST request using the Workflows REST API (executions.create endpoint). This allows external systems to start a workflow by sending an authenticated POST to the Workflows API with the workflow ID and input arguments.

Answer

Configuring a Cloud Scheduler job with an HTTP target.

Answer

Enabling the 'HTTP Gateway' on the workflow itself.

Answer

Creating a Service Account with 'Public Access'.

Question 6

Your team runs a Cloud Composer 2 environment with several DAGs that process data in Cloud Storage. You need to design a DAG that reliably handles transient failures when calling the Cloud Storage API. The DAG should retry failed tasks automatically without manual intervention, but you also want to avoid overwhelming the API during an outage. Which combination of Airflow features should you implement to achieve this?

Accepted Answer

Set retries and retry_delay on the DAG's default_args, and use exponential backoff with max_retry_delay.. Setting retries and retry_delay in default_args enables automatic retries for failed tasks. Exponential backoff with max_retry_delay spaces out retries increasingly, reducing load on the Cloud Storage API during an outage. This combination is the recommended way to handle transient failures in Airflow while avoiding API overload, and it requires no custom code.

Answer

Use a single task with a long timeout and rely on the Cloud Storage client library's internal retry mechanism.

Answer

Implement a custom operator that catches exceptions and sleeps for a fixed interval before re-executing the API call.

Answer

Configure the DAG to run on a schedule with a high number of max_active_runs, and let failed tasks be skipped.

Question 7

A data engineer is building a Looker Studio report connected to a BigQuery table. The report must allow viewers to dynamically change the date range and filter data by region without editing the report. The engineer wants to add interactive controls. Which two components should be added to the report to meet these requirements? (Choose two.)

Accepted Answer

Drop-down list control. To enable viewers to change the date range and filter by region without editing the report, interactive controls are needed. A date range control provides a calendar picker for date filtering, and a drop-down list control allows selection of dimension values like region. Calculated fields, scorecards, and data blending are not interactive controls and do not fulfill the dynamic filtering requirements.

Answer

Calculated field

Answer

Scorecard with conditional formatting

Answer

Data blending

Question 8

A retail analytics team stores point-of-sale transactions in a BigQuery table named `sales.transactions` that is partitioned by a DATE column called `transaction_date`. An analyst must produce a report showing only the transactions from the previous calendar month, and the report will be refreshed on the first day of every month. Which query pattern most efficiently retrieves the required data while minimizing bytes processed?

Accepted Answer

SELECT * FROM sales.transactions WHERE transaction_date >= DATE_TRUNC(DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH), MONTH) AND transaction_date < DATE_TRUNC(CURRENT_DATE(), MONTH). Filtering a partitioned table with a range comparison on the partition column lets BigQuery scan only the partitions that satisfy the predicate. Computing the previous month's first day and the current month's first day as constant bounds produces an exact calendar-month window and enables partition pruning, keeping bytes processed low. Functions applied to the partition column or rolling date arithmetic break pruning or return the wrong period.

Answer

SELECT * FROM sales.transactions WHERE transaction_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH) AND CURRENT_DATE()

Answer

SELECT * FROM sales.transactions WHERE EXTRACT(MONTH FROM transaction_date) = EXTRACT(MONTH FROM DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH))

Answer

SELECT * FROM sales.transactions WHERE transaction_date = DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH)

Question 9

You are building a Looker Studio report connected to a BigQuery table that contains billions of rows. The report includes several charts and filters. Users report that the report is slow to load and that changing a filter takes a long time. Which TWO actions should you take to improve performance? (Choose two.)

Accepted Answer

Enable BigQuery BI Engine and reserve memory for the dataset.. For large BigQuery datasets, slow Looker Studio performance is best addressed by reducing query latency and data scanned. BigQuery BI Engine provides in-memory acceleration for interactive queries, while a materialized view pre-aggregates data to minimize processing. Both directly improve chart load and filter responsiveness. Other options either do not address query speed or are impractical for billions of rows.

Answer

Increase the data freshness setting to 24 hours to reduce the number of queries.

Answer

Extract the data into a Looker Studio extract and schedule daily refreshes.

Answer

Add more filters to the report to narrow down the data before charts load.

Question 10

A data analyst has a Looker Studio report connected to a BigQuery table containing millions of rows. The report includes a table chart that shows aggregated sales by product category. Users complain that the report takes a long time to load. Which action should the analyst take to improve performance without changing the report's layout?

Accepted Answer

Create a materialized view in BigQuery that pre-aggregates the sales data by product category and connect the report to that view.. The report is slow because the table chart queries a large BigQuery table. To improve performance without altering the layout, the analyst should reduce the amount of data processed by the query. Creating a materialized view that pre-aggregates the data by product category achieves this, as the report then queries a smaller, pre-computed dataset. This maintains the same visual output while drastically improving load times.

Answer

Enable the 'Aggregate data' option in the table chart settings.

Answer

Increase the number of rows displayed in the table chart to show more data at once.

Answer

Add a filter to limit the data to the last 30 days.

Question 11

You have a Looker Studio report that uses a BigQuery data source. You need to add a chart that shows the total number of orders per day for the last 30 days. The date field is named `order_date`. Which chart type and dimension setup should you use?

Accepted Answer

A time series chart with `order_date` as the dimension and a record count metric.. To visualize a daily trend, a time series chart is the correct choice. It uses a date dimension on the x-axis and a metric on the y-axis, automatically handling date granularity and ranges. A record count metric counts the number of orders per day. Other chart types either do not show trends or are unsuited for time-based data.

Answer

A bar chart with `order_date` as the dimension and a record count metric.

Answer

A scorecard with `order_date` as the dimension and a record count metric.

Answer

A pie chart with `order_date` as the dimension and a record count metric.

Question 12

You are the data engineer for a retail company on Google Cloud. Analysts query a BigQuery table that contains 400 TB of point-of-sale records and almost always filter by transaction_date. Query costs have grown sharply because every query scans the full table. You need the most cost-effective change that reduces bytes scanned for date-filtered queries without altering the table schema. What should you do?

Accepted Answer

Partition the table by the transaction_date column, since partition pruning skips partitions that do not match the filter predicate.. Partitioning the table on the transaction_date column is correct because BigQuery uses partition pruning to read only the partitions that satisfy the date predicate, directly lowering bytes scanned and cost. Clustering, materialized views, and BI Engine can help in narrower cases, but none changes the physical partitioning of the huge base table the way date partitioning does.

Answer

Enable the BigQuery BI Engine reservation and let the in-memory cache absorb the repeated date-range scans.

Answer

Add a clustering key on transaction_date and rely on cluster pruning to eliminate all non-matching data blocks.

Answer

Create a materialized view that pre-aggregates daily sales totals, then point all analyst queries at the view.

Question 13

A data engineer needs to load a 500 GB CSV file from a Cloud Storage bucket into a BigQuery table once per day. The CSV has a header row and uses comma delimiters. The engineer wants the simplest managed approach that requires no code and handles schema autodetection. What should the engineer use?

Accepted Answer

BigQuery Data Transfer Service with a Cloud Storage transfer configuration. BigQuery Data Transfer Service is the managed, no-code option for scheduled loads from Cloud Storage into BigQuery. It supports CSV with headers, autodetects schema, and runs on a schedule without requiring a VM, cluster, or custom pipeline code. The other options all require managing infrastructure or writing code.

Answer

Cloud Dataproc with a PySpark job that reads the CSV from Cloud Storage and writes to BigQuery

Answer

bq load with the --autodetect flag executed from a cron job on a Compute Engine VM

Answer

Cloud Dataflow with a custom Apache Beam pipeline using TextIO and BigQueryIO

Question 14

A data practitioner is building a Looker Studio report connected to a BigQuery table that contains millions of rows. The report currently returns an error indicating the query result exceeds the row limit, and stakeholders also complain that the charts take too long to render. Which two actions should the practitioner take to address these issues? (Choose two.)

Accepted Answer

Enable the 'Extract data' option on the data source so a snapshot of the data is cached and reused by the report.. Both symptoms trace back to volume: too many rows for the connector and too much work per page render. Aggregating in BigQuery before the data reaches Looker Studio reduces the row count under the connector limit and lightens each query. Caching that reduced result as an extract removes repeated warehouse round trips, so charts render from local snapshot data. Together they fix the error and the latency without changing the report's meaning.

Answer

Switch the data source to the Google Sheets connector and import the BigQuery results into a spreadsheet first.

Answer

Increase the number of rows returned by editing the BigQuery connector's maximum row setting in the data source configuration.

Answer

Add more chart types to the report page so Looker Studio distributes the query load across multiple visualizations.

Question 15

You are orchestrating a data pipeline in Cloud Composer 2. A task in your DAG downloads a large dataset from Cloud Storage and can take up to 45 minutes to complete, but it occasionally fails and needs to be retried. You want the task to automatically retry up to 3 times with a 5-minute delay between attempts. Which Airflow task parameter should you configure to achieve this?

Accepted Answer

retries=3, retry_delay=timedelta(minutes=5). To configure automatic retries with a specific delay, you must set both the retries and retry_delay parameters on the task. The retries parameter controls how many times the task will be retried after failure, and retry_delay specifies the time to wait between attempts. Using retries=3 and retry_delay=timedelta(minutes=5) directly satisfies the requirement.

Answer

sla=timedelta(minutes=5), retries=3

Answer

execution_timeout=timedelta(minutes=45), retries=3

Answer

retry_exponential_backoff=True, max_retry_delay=timedelta(minutes=5)

Question 16

You are using Workflows to orchestrate a series of steps that call external HTTP APIs. One API occasionally returns a 503 error, and you want the workflow to retry that specific step with exponential backoff, but not retry on 404 errors. How should you configure the step?

Accepted Answer

Set the step's retry predicate to check for a 503 status code and configure a retry policy with exponential backoff, and do not include 404 in the predicate.. Workflows steps support a retry policy with a predicate that can inspect the error or response. By predicating on 503 only, the step retries with exponential backoff on server errors and fails fast on 404. Manual loops or global retries do not provide the same granular control, and Cloud Scheduler is not designed for in-workflow retries.

Answer

Configure the entire workflow to retry on any error by setting a global retry policy, and rely on the API to eventually return success for 404s.

Answer

Wrap the API call in a try/except block and use a loop with a sleep timer that doubles each iteration, manually checking for 503 before retrying.

Answer

Use a Cloud Scheduler job to invoke the API step repeatedly with increasing intervals until it succeeds, then trigger the rest of the workflow.

Question 17

You are designing a Cloud Composer 2 DAG that must process files as they arrive in a Cloud Storage bucket. You want to minimize latency and avoid unnecessary worker resource consumption. Which TWO approaches should you use? (Choose two.)

Accepted Answer

Configure the bucket to send object change notifications to a Pub/Sub topic, and use a Pub/Sub sensor in the DAG to wait for messages.. Both a Pub/Sub sensor and a Cloud Function triggered by Cloud Storage events provide event-driven reactions to new files, minimizing latency and avoiding polling. The Pub/Sub sensor uses native Airflow operators and no custom code, while the Cloud Function uses the Airflow REST API to trigger the DAG. Polling sensors or frequent scheduled runs add latency and waste resources.

Answer

Schedule the DAG to run every minute with a cron expression and have it list the bucket for new files.

Answer

Use a Cloud Storage sensor that checks for new files at a fixed interval.

Answer

Increase the number of workers in the Cloud Composer environment to ensure sensors never block other tasks.

Question 18

You are using Cloud Composer to orchestrate a daily ETL pipeline. A task that normally completes in 10 minutes occasionally runs for over 2 hours due to unexpected data volume. You want to prevent this task from indefinitely blocking the workflow and ensure it fails after 30 minutes so downstream tasks can be retried or alerted. What should you configure on the task?

Accepted Answer

Set execution_timeout to 30 minutes in the task definition.. In Cloud Composer, Airflow tasks support an execution_timeout parameter that defines the maximum runtime for a single task. When the timeout is reached, the task is terminated and marked as failed, enabling downstream tasks to proceed or alerts to fire. Configuring this on the specific task ensures that only that task is affected, preserving the rest of the DAG's execution.

Answer

Set the DAG's dagrun_timeout to 30 minutes.

Answer

Set the task's retries parameter to 0.

Answer

Set the scheduler's max_threads to a higher value.

Question 19

You are designing a Looker Studio report for a sales team. The report must allow users to filter data by region and product category, and also drill down from country to city. Which two features should you implement to meet these requirements? (Choose two.)

Accepted Answer

Enable drill-down on a geo chart by adding a hierarchy of country and city dimensions, and set the chart to allow drill-down.. To filter by region and product category, drop-down controls are the standard interactive elements. To drill down from country to city, a geo chart with a drill-down hierarchy is required. These two features together provide the necessary interactivity and detail. The other options either use less appropriate mechanisms (parameters, blends) or do not address both requirements.

Answer

Use a blend to combine region and product category data, and add a filter control for the blended data source.

Answer

Add a date range control and a dimension filter for region, and set the chart to display city-level data by default.

Answer

Create a parameter for region and product category, and use it in calculated fields to filter the data.

Question 20

You are designing a data ingestion pipeline that must handle late-arriving data in a streaming source. The data arrives with timestamps that may be up to 10 minutes behind the current time. You need to ensure that all data is processed correctly and that windowed aggregations include late data. Which Dataflow feature should you use?

Accepted Answer

Allowed lateness. Allowed lateness is the Dataflow feature that specifies how long to wait for late data after the watermark passes the end of a window. Setting it to 10 minutes ensures that data arriving up to 10 minutes late is still processed and included in windowed aggregations. Other triggers and modes do not address late data.

Answer

Watermark triggers

Answer

Discarding mode

Answer

Processing time triggers