GCP-ADP Practice Test 18 — 15 Questions

Question 1

Which BigQuery feature allows you to monitor query usage and identify expensive queries?

Accepted Answer

INFORMATION_SCHEMA.JOBS. INFORMATION_SCHEMA.JOBS is a system view in BigQuery that provides real-time and historical metadata about all jobs (queries, loads, extracts, copies) run in a project, including details like total bytes processed, slot milliseconds, user, and query text. This makes it the ideal tool to monitor query usage and pinpoint expensive queries by analyzing metrics such as total_bytes_billed or total_slot_ms. Unlike other options, it offers granular, query-level cost and performance data directly within BigQuery.

Answer

Billing Reports.

Answer

Cloud Logging.

Answer

BigQuery Monitoring Dashboard.

Question 2

An analyst has a BigQuery table of website events partitioned by event_date. A stakeholder asks for a quick chart of daily active users for the last 30 days, and cost must stay minimal. Which query pattern best satisfies this request?

Accepted Answer

Query with a WHERE clause on event_date limiting the range to the last 30 days and select only the date and user identifier needed for the distinct count.. Partition pruning is the decisive factor for cost on a partitioned table. Restricting the WHERE clause to the event_date partition column means BigQuery reads only the 30 relevant days instead of the whole history. Selecting only the two columns required for the daily distinct count keeps the scan narrow. Together, partition pruning and column pruning produce the requested chart at the lowest achievable cost.

Answer

Create a materialized view that joins the events table to the users table and query the view for the daily counts.

Answer

Run a query that selects all columns from the events table and let the visualization tool filter the date range after retrieval.

Answer

Export the entire events table to Cloud Storage as CSV and query the exported files with an external table.

Question 3

Your team needs to ingest data from an on-premises Oracle database into BigQuery with minimal latency and without writing code. The database is behind a firewall and cannot be accessed directly from the internet. You want to use a fully managed Google Cloud service. What should you use?

Accepted Answer

Datastream with a private connectivity configuration. Datastream is a fully managed, serverless change data capture and replication service that can connect to on-premises Oracle databases using private connectivity like VPN or Interconnect. It replicates data to BigQuery with low latency and requires no coding. Other options either lack Oracle support, require code, or are not fully managed for this purpose.

Answer

Cloud SQL for Oracle with federated queries

Answer

BigQuery Data Transfer Service with a VPN connection

Answer

Cloud Data Fusion with the Oracle connector

Question 4

Which service is the primary tool for managing and running Jupyter notebooks in a managed environment within Google Cloud?

Accepted Answer

Vertex AI Workbench.. Vertex AI Workbench is Google Cloud's managed Jupyter notebook environment, offering both user-managed and fully managed notebook instances integrated with Vertex AI, BigQuery, and Dataproc. It is the designated service for running JupyterLab notebooks in a managed, scalable way on GCP.

Answer

Cloud Run.

Answer

Dataflow.

Answer

BigQuery Studio.

Question 5

You are analyzing query performance in BigQuery. You notice that your queries are spilling to disk. What is the most likely cause?

Accepted Answer

An inefficient JOIN or GROUP BY operation causing high memory pressure.. Spilling to disk in BigQuery occurs when a query's intermediate results exceed the memory available to the execution stage. This typically happens due to inefficient JOINs or GROUP BY operations that produce large intermediate datasets, forcing BigQuery to write data to disk (shuffle) and significantly slowing performance. The correct answer directly identifies this memory pressure as the root cause.

Answer

The project is using On-Demand pricing.

Answer

The dataset is stored in a multi-region location.

Answer

Using too many slots in the reservation.

Question 6

Which role is required to manage Dataplex assets within a Zone?

Accepted Answer

roles/dataplex.admin. Managing Dataplex assets within a Zone requires the roles/dataplex.admin role, which grants full control over lakes, zones, assets, and their metadata. This role includes permissions to create, update, and delete assets and to manage IAM policies on Dataplex resources.

Answer

roles/storage.admin

Answer

roles/bigquery.admin

Answer

roles/viewer

Question 7

An analyst has a BigQuery table `events.clicks` with columns `event_ts`, `country`, and `clicks`. They want a Looker Studio time series that shows a rolling 7-day average of clicks per country, updating as new data lands. The analyst wants the rolling calculation to happen in the database rather than in the visualization. What is the most appropriate approach?

Accepted Answer

Create a BigQuery view or scheduled query that computes the 7-day rolling average with a window function, then point the report at it.. Pushing the rolling average into BigQuery with a window function and exposing it through a view or scheduled query keeps the computation in the database and lets the Looker Studio time series stay current. Date comparison, visualization calculated fields, and BI Engine do not compute a database-side 7-day moving average.

Answer

Enable BI Engine on the dataset and let it derive the rolling average automatically.

Answer

Add a date range filter to the time series and rely on Looker Studio's built-in comparison to prior period.

Answer

Define a calculated field in Looker Studio using a moving average function on the clicks metric.

Question 8

You are using Dataflow to process a stream of JSON messages from Pub/Sub. The messages contain a timestamp field, but the event time is sometimes delayed by up to 30 minutes. You need to handle these late-arriving events correctly. What should you configure in your pipeline?

Accepted Answer

Enable a 30-minute allowed lateness on the window. To handle late-arriving events, you must set an allowed lateness on the window. This tells Dataflow to keep the window state open for a specified duration after the window's end, allowing late events to be processed and included in the correct aggregation. A 30-minute allowed lateness matches the maximum delay described.

Answer

Use a session window with a 30-minute gap

Answer

Set a fixed window of 1 hour

Answer

Set the pipeline's default timeout to 30 minutes

Question 9

A healthcare analytics team stores patient encounter records in Cloud Storage as CSV files and needs to run SQL over them in BigQuery without loading or duplicating the data. The files must remain the system of record, and the team wants to avoid storage charges for a second copy. Which BigQuery feature should they use?

Accepted Answer

Create an external table over the Cloud Storage bucket and query the CSV files directly.. An external table points BigQuery at the CSV objects in Cloud Storage and queries them in place, satisfying the requirement to keep the files as the system of record and avoid a duplicate billed copy. Loading duplicates data, while BigLake and non-existent dataset mounting add complexity or do not address the stated need.

Answer

Load the CSV files into a native BigQuery table with a scheduled load job every hour.

Answer

Create a BigLake table over the CSV files and query it with the BigQuery Storage API.

Answer

Mount the Cloud Storage bucket as a BigQuery dataset using a federated dataset connection.

Question 10

You are designing a data governance strategy for a BigQuery environment. You need to automatically apply policy tags to columns that contain sensitive data so that only authorized users can query those columns. Which two actions should you take? (Choose two.)

Accepted Answer

Grant the Fine-Grained Reader role on the policy tag to the group that should be able to query the sensitive columns.. Column-level security in BigQuery is implemented with policy tags from a Data Catalog taxonomy. You attach the policy tags to the sensitive columns and then grant the Fine-Grained Reader role on each tag to the groups that should see the data. Together these actions automatically restrict access at the column level. The other choices address network perimeters, data redaction, or retention rather than column-level authorization.

Answer

Configure a Cloud DLP job to redact sensitive values from the table on a nightly schedule.

Answer

Enable VPC Service Controls on the project that hosts the BigQuery dataset.

Answer

Set the default table expiration on the dataset to 30 days to limit exposure of sensitive data.

Question 11

You are planning to ingest log data into Google Cloud. Which THREE of the following services support streaming ingestion?

Accepted Answer

BigQuery Storage Write API. BigQuery Storage Write API (A) is correct because it provides a gRPC-based streaming interface that lets applications write rows directly into BigQuery tables in real time, including exactly-once semantics via the default stream. Cloud Dataflow (D) is correct because it is a fully managed Apache Beam runner that supports streaming pipelines reading from sources such as Pub/Sub and writing to sinks like BigQuery. Cloud Pub/Sub (E) is correct because it is a globally distributed, real-time messaging service designed for streaming ingestion, delivering messages to subscribers with low latency and at-least-once delivery. Dataproc (B) is not a streaming ingestion service; it is a managed Hadoop/Spark cluster platform, and while Spark Streaming can run on it, the service itself is not a streaming ingestion endpoint. Cloud Storage Transfer Service (C) is not correct because it performs batch transfers of data between object stores or from HTTP sources into Cloud Storage, not continuous streaming ingestion.

Answer

Dataproc

Answer

Cloud Storage Transfer Service

Question 12

You are loading a 4 TB compressed CSV file from Cloud Storage into BigQuery using a load job. The file has a header row and uses pipe delimiters. You need the load to complete as fast as possible and minimize cost. What should you do?

Accepted Answer

Split the file into multiple smaller files (e.g., 100–500 MB each) in Cloud Storage, then run a single load job specifying the pipe delimiter and skipping the header row.. BigQuery load jobs scale by reading multiple files in parallel. A single 4 TB file cannot be parallelized, so splitting it into many smaller files is the key to fast ingestion. Specifying the delimiter and header row ensures correct parsing. This combination minimizes load time and uses BigQuery's load capacity efficiently, keeping costs lower than query-based alternatives.

Answer

Load the file into a BigQuery external table and then run a CREATE TABLE AS SELECT statement to materialize it.

Answer

Use the bq load command with the --max_bad_records flag set to a high value to skip any malformed rows.

Answer

Use the BigQuery web UI to create a load job with autodetect schema and default settings.

Question 13

You are the data engineer for an online retailer. A Cloud Storage bucket named orders-raw contains daily CSV exports from an on-premises order management system. Each file is named with a date stamp, and new files arrive once per day. You need to load this data into BigQuery with minimal ongoing operational effort, and the load must automatically pick up each new file as it arrives. What should you do?

Accepted Answer

Create a BigQuery Data Transfer Service transfer for Cloud Storage, set the data source to the orders-raw bucket, and schedule it to run daily.. A managed BigQuery Data Transfer Service transfer for Cloud Storage is the lowest-effort way to load recurring files from a bucket into BigQuery. It supports Cloud Storage as a source, runs on a schedule you define, and automatically ingests new files that match the source path, so no custom code or cluster management is needed.

Answer

Create an external table over the orders-raw bucket and query the CSV files directly from BigQuery.

Answer

Create a Dataproc cluster with a scheduled PySpark job that reads the bucket and writes to BigQuery.

Answer

Write a Cloud Run job that reads each CSV from the bucket and calls the BigQuery load API, then trigger it from a Cloud Scheduler job every night.

Question 14

You have a Cloud Composer 2 DAG that uses a PythonOperator to run a script that connects to a Cloud SQL database. The script needs a password. You want to avoid hardcoding the password in the DAG or in environment variables. What should you do?

Accepted Answer

Store the password in Secret Manager and use the Secret Manager Airflow operator or hook to retrieve it at runtime, granting the Composer service account the Secret Accessor role.. Secret Manager is designed for storing, managing, and accessing secrets securely. By using the Secret Manager operator or hook, the DAG retrieves the password at runtime, and IAM controls access. Other options store secrets in less secure locations like Cloud Storage, the Airflow database, or Airflow Variables, which lack the necessary security controls.

Answer

Use the Cloud Composer environment's Airflow metadata database to store the password in a custom table, and query it from the PythonOperator.

Answer

Define the password as an Airflow Variable in the Cloud Composer environment and reference it in the PythonOperator.

Answer

Store the password in a Cloud Storage bucket and have the PythonOperator download it using the Cloud Storage client library.

Question 15

Which TWO of the following are best practices for managing Cloud Composer DAG dependencies?

Accepted Answer

Use plugins for shared code. Option A is correct because Cloud Composer plugins let you package and reuse shared code (operators, hooks, sensors, and libraries) in a versioned, centrally managed way, avoiding duplication and drift across many DAGs. Option E is correct because keeping DAG files small and focused reduces parse time in the Composer scheduler, limits the blast radius of changes, and makes dependencies between DAGs easier to reason about and maintain. Option B is wrong because DAGs are Python files that belong in the Composer environment's dags/ folder (or a synced GCS bucket), not in a database. Option C is wrong because hardcoding configurations is an anti-pattern; you should use Airflow Variables, Connections, environment variables, or secrets managers. Option D is wrong because cramming all tasks into a single DAG file creates a monolithic, hard-to-maintain DAG and does not reflect best practice for managing dependencies.

Answer

Store DAGs in a database

Answer

Hardcode all configurations

Answer

Run all tasks in a single DAG file