GCP-ADP Timed Practice Test 3 — 10 Questions

Question 1

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 2

A product team uses Looker Studio to visualize metrics from a BigQuery table that is updated by an hourly ETL job. Users report that the dashboard shows data that is several hours stale even though the ETL completes successfully. The data source was configured with a custom query rather than a direct table reference. What is the most likely cause of the staleness?

Accepted Answer

The BigQuery data source uses a custom query, and Looker Studio caches the query results for the configured freshness interval.. Looker Studio caches results from a custom query data source and refreshes them according to the data source's freshness setting, which defaults to twelve hours. Because the ETL updates BigQuery but the dashboard serves a cached snapshot, users see data that lags by several hours. Lowering the freshness interval, or using a direct table reference where freshness behaves differently, resolves the mismatch.

Answer

The hourly ETL job writes to a staging table and the dashboard reads from the wrong table.

Answer

The dashboard's date range control is set to a fixed historical period and is not updating automatically.

Answer

BigQuery streaming inserts are not immediately visible to queries, so the dashboard cannot see the newest rows.

Question 3

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 4

You are querying a dataset in BigQuery that uses partitioning. Your query doesn't use the partitioning column. What is the consequence?

Accepted Answer

The query will perform a full table scan.. When a query filters or joins on a non-partitioning column, BigQuery cannot prune partitions and must scan every partition in the table, resulting in a full table scan. This increases bytes processed, cost, and query latency, but the query still returns correct results.

Answer

BigQuery will automatically optimize the scan.

Answer

The query will fail immediately.

Answer

The query results will be inaccurate.

Question 5

Your team uses Dataprep by Trifacta to clean data before loading it into BigQuery. You notice that the column header names contain inconsistent casing and special characters. Which Dataprep transformation should you use to standardize these headers globally?

Accepted Answer

Rename Columns. The Rename Columns transformation in Dataprep by Trifacta lets you standardize column headers globally — changing case, removing special characters, and applying consistent naming conventions across the dataset. It is the direct tool for header normalization.

Answer

Nest Columns

Answer

Aggregate

Answer

Merge Columns

Question 6

You are using BigQuery and need to load data from an external file that is not in GCS. Which method is recommended?

Accepted Answer

Upload to GCS then BigQuery. The recommended method to load external data into BigQuery is to first upload the file to Google Cloud Storage, then use BigQuery's native load job to ingest it. This approach is reliable, scalable, and leverages BigQuery's optimized loading pipeline. It also allows for better error handling and supports various file formats.

Answer

Use Cloud Functions

Answer

Stream directly from the local machine

Answer

Use an external API

Question 7

You are ingesting CSV files into BigQuery using the bq load command. The CSV files have a header row, and you want BigQuery to automatically detect the schema and skip the header. Which flags should you include?

Accepted Answer

--autodetect --skip_leading_rows=1. To load CSV files with a header row and let BigQuery infer the schema, use bq load with --autodetect and --skip_leading_rows=1. The --autodetect flag enables schema inference, and --skip_leading_rows=1 skips the header. The other flag combinations either use invalid flags or require manual schema definition, which is not what the scenario asks for.

Answer

--autodetect --header=true

Answer

--infer_schema --skip_rows=1

Answer

--schema --header

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 have a BigQuery table that is partitioned by ingestion time. You need to reduce storage costs by automatically deleting data older than 90 days. You want to ensure that the deletion happens without manual intervention and that the partitions are completely removed. What should you do?

Accepted Answer

Set a partition expiration on the table for 90 days.. Partition expiration is a table setting that automatically deletes partitions after a specified number of days. For ingestion-time partitioned tables, this is the simplest and most efficient way to remove old data and reduce storage costs. It requires no manual intervention and ensures partitions are dropped entirely.

Answer

Configure a Cloud Scheduler job that calls the BigQuery API to drop partitions older than 90 days.

Answer

Create a scheduled query that runs daily to delete rows older than 90 days using a DELETE statement.

Answer

Use a lifecycle rule on the BigQuery dataset to delete tables older than 90 days.

Question 10

A retail analytics team runs a Looker Explore against a BigQuery table containing order line items. Analysts need a single visual that shows the average order value per country, but the underlying table stores one row per line item, so multiple rows share the same order_id. Which approach produces a correct average order value per country?

Accepted Answer

Create a measure that divides total revenue by the count of distinct order_id values, then group by the country dimension in the Explore.. The table's grain is one row per line item, so any row-level average reports line-item value rather than order value. The reliable pattern is a ratio measure: summed revenue divided by the distinct count of order identifiers. Grouping that measure by the country dimension gives a defensible average order value per country, and using a distinct count in the denominator neutralizes the fan-out that multi-item orders introduce.

Answer

Apply a SUM aggregation to the order_total dimension and rely on Looker's default symmetric aggregates to return the per-country average.

Answer

Enable the Symmetric Aggregates setting on the country dimension and select Average as the measure type for order_total.

Answer

Add the order_total column as a dimension, use the Average aggregation type, and group by country to obtain the mean order value.