Google Cloud Associate Data Practitioner (GCP-ADP) (GCP-ADP) — Questions 76–150

Every Dataplex Lake requires a unique name as its identifying attribute within the project and region. This satisfies the mandatory configuration the stem asks for, since the name is used to reference the lake in IAM policies, assets and zones.

Error handling is essential because production pipelines must tolerate transient failures without losing data. Dataflow's retry semantics only cover certain exceptions; application-level failures, malformed records, and dead-letter routing require explicit handling. Without it, a single bad element can stall or fail the pipeline, violating the reliability requirement for production readiness.

Cloud DLP de-identifies sensitive data using techniques such as masking, tokenisation, and format-preserving encryption before data lands in BigQuery, satisfying the requirement to anonymise PII prior to loading. Its infoType detectors identify PII automatically, so redaction happens at the pipeline stage rather than after ingestion, when exposure would already have occurred.

The Airflow web UI shows each task instance's state, letting you identify failed, upstream_failed or retry states and open logs. Checking task state satisfies the stem's requirement for a valid troubleshooting method for failed tasks in Cloud Composer.

A retention policy prevents objects from being deleted or overwritten until the retention period elapses, and locking the policy makes it permanent: it cannot be removed or shortened, and the retention period cannot be reduced. This enforces immutability for seven years even against project owners, satisfying the compliance requirement. Locking is irreversible, which is exactly what makes the guarantee strong.

Parallelising across workers lets Dataflow distribute the streaming load horizontally, so throughput scales with worker count instead of bottlenecking on a single thread. This directly satisfies the high-throughput ingestion constraint by matching processing capacity to the incoming event rate.

A Dataplex Lake spanning two regions references storage buckets in each region. Access requires IAM permissions on both underlying regional buckets, so users need rights to both regional storage resources to query all lake data.

Connected Sheets links a Google Sheets tab to a BigQuery data source and supports scheduled refreshes, so the data updates each morning without manual intervention. This directly meets the requirement that non-technical staff see current figures automatically, and it retains the live connection so the numbers stay aligned with the BigQuery table.

Prefixing a shell command with an exclamation mark executes it in the notebook's underlying shell rather than as Python, so !ls lists the current working directory's files. This is the standard Jupyter-based mechanism in Vertex AI Workbench.

BigQuery dry runs parse and validate the SQL, resolve table and column references, and return an estimate of bytes processed without actually executing the query or incurring scan charges. This lets the analyst catch syntax or reference errors and judge cost before committing to a real run. It directly matches the goal of validation plus cost estimation on production tables.

Replacing the cross-join with a specific JOIN condition eliminates the Cartesian product, reducing the intermediate result from 10 billion squared rows to only matching pairs. This directly addresses the timeout constraint, since BigQuery's distributed shuffle cannot materialise such an explosive intermediate dataset within the query's execution limits.

Data Catalog taxonomies are project-scoped resources, so defining the taxonomy once in a central project and granting other projects' users or service accounts IAM access to it shares it without duplication. This satisfies the requirement to reuse one taxonomy across multiple projects.

Workflows steps reference prior outputs through variables assigned from call results, so storing the function's return value and passing it as an argument in the subsequent step satisfies the requirement to chain outputs between functions.

Datastream provides serverless change data capture, continuously replicating ongoing changes from the source database into BigQuery. This satisfies the zero-downtime constraint by keeping both systems synchronised during migration, so cutover occurs without halting writes or losing transactions.

A time series chart is designed to display data over time, with the date field as the dimension. Using `order_date` as the dimension and a record count metric (e.g., COUNT(*)) will show the number of orders per day. This directly meets the requirement of total orders per day for the last 30 days.

In Airflow, the `retries` parameter specifies the number of retry attempts, and `retry_delay` defines the wait time between retries. Setting these in the task's arguments or DAG default_args will automatically retry the task as specified, which is exactly what is needed for transient errors.

A pie chart displays data as slices of a whole, making it ideal for showing proportions or percentages of a total. With product category as the dimension and sales as the metric, each slice represents a category's share of total sales. This directly fulfills the requirement to show proportion.

Dataplex Data Steward owns metadata curation, data quality rule definition, and compliance policy enforcement across lake zones and assets. This satisfies the stem's focus on governance duties, since stewards govern business metadata and quality thresholds rather than building pipelines or administering IAM, which fall to data engineers and platform admins.

The Airflow web server exposes the DAGs page, where a run can be triggered manually via the Trigger DAG button. This satisfies the stem's requirement for a valid trigger method, since the web UI issues the same DagRun creation call the scheduler processes.

REGEXP_EXTRACT_ALL returns an array of all non-overlapping matches for a pattern, so specifying a pattern like `(fast|slow|broken)` captures each occurrence. Unnesting the array turns each match into a row, and grouping by the extracted word yields the count per word across all feedback rows. This is a standard BigQuery pattern for word-frequency analysis in a single query.

Dataplex is surfaced in the Google Cloud console under the Analytics section, alongside BigQuery and other data analytics services. Navigating to Analytics is therefore the menu path that exposes the Dataplex governance and metadata capabilities the question asks you to locate.

This is event-driven: the sensor waits for a Pub/Sub message and unblocks as soon as the event arrives, minimizing latency. It does not poll the bucket, reducing API calls and worker resource consumption. This is the recommended pattern for reacting to new files with low latency.

CMEK wraps data encryption keys with keys held in Cloud KMS under your control, so Google cannot decrypt objects without them. This satisfies the stem's constraint that keys be organisation-managed rather than Google-managed, unlike default encryption.

Looker Studio supports reusable components that can be placed on multiple pages while each instance retains page-level filter controls. This keeps a single chart definition linked to the BigQuery data source, so the revenue logic lives in one place. Each page's region filter narrows that page's instance independently, satisfying the requirement without duplicating the data source or the view.

This filter computes the first day of the previous month and the first day of the current month, giving an exact previous-calendar-month range. Both bounds are constant expressions on the partition column, so BigQuery prunes to only the relevant date partitions, minimizing bytes processed. It also correctly excludes the current month's data, which matches the monthly refresh requirement.

Filtering with a WHERE clause on the partition column enables partition pruning, so BigQuery scans only the relevant daily partitions rather than the entire table. This directly satisfies the stem's efficiency requirement for a day-partitioned dataset, cutting bytes processed and query cost.

BigQuery load jobs have a maximum file size limit (5 TB for uncompressed CSV, but practical limits and performance suggest splitting). Splitting into multiple files allows parallel loading, which is faster and avoids hitting per-job limits. This is the recommended approach for very large files.

The Storage Write API exactly-once feature deduplicates writes based on stream and offset, so Dataflow retries during restarts or bundle retries do not create duplicate rows. This directly addresses the goal of reducing duplicates at the sink while preserving streaming and low latency. It is the recommended write path for exactly-once streaming ingestion into BigQuery.

Monitoring `dag_processing_total_parse_time` directly exposes cumulative seconds Airflow spends parsing DAG files, so rising values reveal the scheduler falling behind on parsing. This satisfies the stem's constraint of identifying timely DAG parsing, unlike task-execution or queue metrics that ignore the parsing bottleneck entirely.

Cloud Composer 2 continuously synchronises the 'dags/' folder of the environment's Cloud Storage bucket to every worker's local filesystem. Storing DAG files there guarantees automatic propagation, satisfying the requirement that all workers receive the DAGs.

Scaling up the machine type allocates more vCPUs and memory to the Workbench instance, accelerating notebook build, kernel startup, and package installation. This directly reduces build time when the bottleneck is compute or memory resources.

Data Preview reads a limited number of rows from the table so analysts can inspect schema and sample values without running a query or incurring scan costs. This satisfies the stem by showing representative table data rather than full results or metadata.

HTTP is a built-in Airflow connection type, used to define host, port, schema and credentials for REST endpoints. It is one of the connection types natively supported by Cloud Composer's Airflow environment, making it valid for this selection.

Clustering physically sorts data by customer_id, so filters on that column scan only relevant blocks rather than the whole table. This directly satisfies the stem's requirement to improve performance for frequent customer_id filtering, and unlike partitioning, clustering suits high-cardinality columns where per-value partitions would be impractical.

Scaling the environment size increases the underlying Cloud SQL database tier, raising IOPS and connection limits so row-level locks clear faster under high concurrency. This directly addresses the database locking constraint, since Composer's metadata database scales with environment size rather than worker count.

Task attempt stdout/stderr is written by the Airflow worker to the environment's Cloud Storage bucket, under the logs/ prefix, in a path keyed by DAG, task and attempt. That per-attempt object satisfies the stem's requirement to inspect one specific stuck attempt, unlike Cloud Logging's aggregated streams.

In Workflows YAML, the 'result' keyword captures the return value of a call into a variable. Then, you can reference that variable in the 'args' of a subsequent call using the ${} syntax. This correctly passes the output of firstFunction to secondFunction as an argument named 'input'.

A side input join broadcasts the entire dataset to every worker's memory, so a large dataset exhausts each worker's heap and triggers OOM errors. This directly violates the stem's constraint of joining two large datasets. Instead, use a co-group or shuffle-based join, which partitions both datasets across workers rather than replicating one.

Setting a partition expiration on a partitioned table automatically deletes partitions older than the specified number of days. This reduces storage costs without affecting the table structure or recent data. It is a built-in feature of BigQuery and requires no manual intervention.

In Cloud Composer 2, tasks run on Celery workers, and the worker visibility timeout controls how long a task can run before the broker considers it lost. Raising execution_timeout on the task, dagrun_timeout on the DAG, and the worker visibility timeout ensures the task is not prematurely killed. This directly addresses the timeout that is terminating the long-running transformation.

The Dataflow Monitoring UI exposes job graphs, step-level throughput, watermarks, worker CPU and autoscaling metrics in real time. It satisfies the stem's requirement for a valid Dataflow performance monitoring method by surfacing pipeline bottlenecks directly.

Dataplex assets are bound to a single zone at creation; no move operation exists. Deleting the asset and recreating it in the target zone is the only supported procedure, satisfying the stem's cross-zone relocation requirement.

Cloud Lifecycle Management applies age-based Object Lifecycle Management rules directly on the bucket, deleting objects automatically once they exceed 90 days. This satisfies the efficiency constraint, avoiding custom scripts or scheduled jobs that list and delete objects individually.

Autoscaling dynamically adjusts the number of worker instances to match pipeline throughput, adding workers during backlog spikes and removing them when demand falls. This directly improves performance under load while cutting costs during idle periods, satisfying both optimisation goals in the stem.

Workflows YAML requires a main block, which names the subworkflow executed first when the workflow runs. Without main, the definition has no defined entry point, so execution cannot begin; other blocks are only reachable once main calls them.

Cloud Audit Logs records Admin Activity and Data Access events, capturing the identity, timestamp and resource for every GCS bucket request. Data Access logs specifically satisfy the requirement to track who accessed which bucket, since bucket reads and writes are data-plane operations that only this audit source records.

Autoscaling dynamically adds and removes worker nodes based on YARN metrics such as pending memory and available compute, which directly addresses the growing data volume without code changes. It respects the configured minimum and maximum worker bounds, so the cluster can expand during the heavy shuffle and write phases and shrink afterward, reducing both runtime and idle cost for this recurring job.

Sensitive Data Protection discovery profiles data in Cloud Storage, identifies infoTypes such as PII, and can publish findings to Data Catalog as tags. This automates classification and makes tags available for audit and IAM conditions. It is the native, low-effort solution for continuous auto-classification of data at rest in Cloud Storage.

In BigQuery, a RECORD column is the equivalent of a STRUCT, so defining `user_info` as RECORD lets the load job map the nested JSON object into a structured field. Analysts can then reference subfields directly, and BigQuery enforces the nested schema. This is the correct choice for preserving nested JSON as a queryable STRUCT in a BigQuery table.

Data Lineage records how data flows and transforms across Dataplex assets, mapping source-to-target relationships through processing steps. This satisfies the stem by showing the path data took through the system, including upstream sources and downstream consumers.

Workflows is a fully managed, serverless orchestration service that runs YAML- or JSON-defined sequences of steps, including HTTP calls, with built-in retry policies and error handling. It exposes each workflow as an execution endpoint that can be invoked via the Workflows API, gcloud, or client libraries, and it requires no cluster or VM, matching every constraint in the scenario.

Setting mode to reschedule makes the sensor task exit its worker slot between poke intervals and be rescheduled later, so a long wait does not block a worker. Combined with a GCSObjectExistenceSensor, this is the classic way to wait for a file without starving other tasks, and it is the pre-deferrable equivalent of deferring. Using mode poke instead would hold the slot continuously.

The Airflow Web Interface (the Airflow UI served by the Composer web server) is the primary interface for inspecting, triggering and managing DAGs manually. The scheduler and workers execute DAGs but offer no manual trigger surface.

Dataflow is fully managed, removing the need to provision or maintain Compute Engine instances for pipeline execution. This satisfies the stem's benefit criterion by letting teams run Apache Beam pipelines without cluster administration, as the service handles worker scaling, patching and resource lifecycle automatically.

A Cloud Storage subscription is a native Pub/Sub export sink: it writes each message to a GCS bucket automatically, satisfying the no-custom-code constraint. No subscriber client, Dataflow pipeline or Cloud Function is needed, since delivery is handled entirely by the managed subscription itself.

The impersonation_chain parameter makes the operator obtain short-lived credentials for a specified service account, so the BigQuery job runs under that identity rather than the node service account. This satisfies the stem's requirement to use a different service account.

Wrapping the HTTP call in a try block with a retry policy lets Workflows re-execute that step on transient errors, applying exponential backoff automatically. This satisfies the requirement of up to 5 retries on the specific request without affecting other steps in the orchestration.

The retries parameter specifies the number of retry attempts, and retry_delay defines the wait time between retries. Setting retries=3 and retry_delay=timedelta(minutes=5) ensures the task retries up to three times with a five-minute delay, matching the requirement exactly.

Setting a default date range of 'Last 30 days' on a date dimension makes the report automatically filter data relative to the current date each time it loads. Since the underlying table is updated daily, the report will always reflect the most recent 30 days without any manual changes. This is the standard, dynamic solution in Looker Studio.

The BigQuery Data Viewer role (roles/bigquery.dataViewer) grants read-only access to data and metadata within a dataset. It allows the analyst to query tables and view schemas but not modify data or access other datasets. This role is the least privilege needed for read-only query access.

Clustering sorts data within each partition by the clustered column, so filters on device identifier can skip blocks that do not match. Combined with timestamp partitioning, this matches the common query pattern of device plus time range and further reduces bytes scanned.

Drill-down functionality allows users to click on a data point (e.g., a country) to see more detailed data (e.g., cities within that country). By configuring a geo chart with a hierarchy of country and city, and enabling drill-down, users can navigate from a high-level view to a detailed view. This directly satisfies the drill-down requirement.

Malformed or unexpected records break parsing and schema validation inside the pipeline, causing exceptions that halt processing. Dataflow's DoFn code cannot coerce corrupt payloads, so a single bad element can fail the bundle and retry indefinitely. This directly satisfies the stem's requirement by naming a common, recurring cause of pipeline failure.

Exactly-once delivery ensures that each message is delivered and processed only once, even in the face of retries or failures. This is critical for scenarios where duplicate processing would cause incorrect results. It works in conjunction with message ordering to provide both guarantees.

A zone strategy maps raw, curated and analytics zones before migration, so assets land in the correct Dataplex zone with appropriate security and discovery settings. This satisfies the stem's requirement for a migration consideration that prevents later restructuring.

Dataplex assets support a path filter expression, such as a wildcard or prefix, that restricts discovery to matching objects under the bucket. This satisfies the requirement to include only files in a specific folder within the asset.

Dataplex metadata is a cached snapshot, not a live view. Hourly deletions change the bucket's contents without triggering discovery, so the asset's stored schema and file list drift from reality until the next rescan runs and refreshes them.

ML.EVALUATE is the correct function to assess the performance of a trained model. For linear regression, it returns metrics like R-squared, mean absolute error, and mean squared error. It compares predicted values against actual values in a dataset, providing a quantitative evaluation of model accuracy.

Cloud Monitoring ingests Cloud Storage metrics such as `storage.googleapis.com/storage/total_bytes`, letting you build an alerting policy on a threshold condition. This directly satisfies the stem's requirement to trigger when bucket size exceeds a set limit, since the metric is evaluated per bucket and fires notifications automatically.

Cloud Composer lets you declare PyPI packages as environment Python dependencies; these are installed on the scheduler, workers, and web server, and new worker pods created during autoscaling inherit the same environment, so the shared library is always available.

BI Engine provides an in-memory analysis layer that accelerates BigQuery queries used by dashboards, keeping interactive filtering responsive for executives. Combined with a Looker Studio report over BigQuery, it satisfies the performance side of the requirement while preserving the direct query path to BigQuery data.

Cloud Audit Logs records Data Access audit entries for Dataplex lake assets, capturing who read or wrote sensitive data. Enabling Data Access logs on the relevant services satisfies the audit requirement, since Admin Activity logs alone omit data reads.

The Dataflow Monitoring UI surfaces per-stage watermarks, system lag and backlog metrics, exposing exactly where elements stall within the pipeline. Unlike logs alone, it visualises streaming progress against the constraint in the stem — diagnosing stuck elements and latency — letting you pinpoint the bottleneck stage directly.

BigQuery Data Transfer Service supports Cloud Storage as a source and can load CSV files on a recurring schedule. It provides schema autodetection, header row handling, and requires no code, matching the requirement for a simple managed daily load of a 500 GB CSV file.

A drop-down list control lets viewers select one or more values from a dimension, such as region, to filter the data displayed in charts. This meets the requirement to filter by region dynamically. It is a common interactive control in Looker Studio and can be linked to multiple charts.