GCP-ADP Practice Test 11 — 15 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)