Google Cloud Associate Data Practitioner (GCP-ADP) (GCP-ADP) — Questions 151–225

Granting the partner's service account BigQuery Data Viewer at dataset level satisfies the cross-project least-privilege constraint. IAM policies accept identities from any Google Cloud project, so no project-level or organisation-wide binding is needed. Dataset-scoped access limits exposure to that single dataset, unlike broader roles that would grant visibility across the project.

TIMESTAMP_TRUNC truncates a timestamp to the specified granularity, here HOUR, discarding finer components. It operates natively on the timestamp type without string conversion, making it the most efficient way to bucket millisecond timestamps for hourly aggregation.

A Dead Letter Queue captures messages that repeatedly fail processing, letting the pipeline route malformed records aside instead of throwing exceptions that halt the pipeline. This satisfies the requirement to handle bad messages without crashing.

External tables let BigQuery query data that stays in Cloud Storage, so the team avoids duplicating storage costs while using standard SQL. This directly matches the requirement to query without loading into BigQuery native storage. They must still consider query performance and format support, but the core capability is exactly this.

When a Workflows step makes an HTTP call, the response is stored in the step's result under the body property. To access a specific field from the JSON response, you use ${stepName.body.fieldName}. This correctly retrieves the 'result' value from the Cloud Function's JSON response.

Setting skipLeadingRows to 1 skips the header row, autodetect enables schema inference, and maxBadRecords to 0 ensures that any bad record causes the job to fail. This configuration meets all requirements: header handling, schema detection, and strict error reporting.

Filters added at the data source level are inherited by every chart that uses that data source, which guarantees the amount > 0 condition applies report-wide. This satisfies the requirement of filtering all viewers' views without altering the BigQuery table, and it avoids the maintenance burden of duplicating the same filter on each chart.

The bq load command ingests data directly from Cloud Storage into a BigQuery table without charging for a separate compute service, and it is the native, simplest path for a one-time CSV load. BigQuery charges only for storage after the load, so this meets the lowest-cost and no-transformation requirements for a 200 GB file.

A ParDo transform can access a side input containing the pipeline start time and replace null timestamps. This ensures records are not dropped and are assigned a valid timestamp. Side inputs are the correct way to provide a runtime value like pipeline start time to all elements in a PCollection.

The Cloud Console provides a graphical interface for launching a Dataflow job, letting you pick a template or specify pipeline parameters and start execution directly. This is a supported triggering method, unlike writing pipeline code or invoking the Dataflow API programmatically from a client.

Dataprep's Export feature writes transformed datasets directly to BigQuery, satisfying the requirement to persist cleaned data there. It handles schema mapping and load jobs natively, unlike running recipes that only produce results within the flow. This is the designed output path for moving prepared data into BigQuery without manual extraction.

Salting appends a random value to each key before the shuffle, spreading otherwise identical hot keys across many workers so a single worker no longer processes the bulk of records. This relieves the write bottleneck into BigQuery caused by skewed key distribution.

Airflow parses the DAG file at schedule time, so a Python loop over the bucket listing (via a hook or operator) instantiates one task per file. This is the standard dynamic task-mapping approach before Airflow's newer dynamic task mapping feature.

A scatter chart plots individual data points using one metric on the x-axis and another on the y-axis, making it ideal for visualizing the relationship and potential correlation between two numerical variables like ad spend and revenue. Each point represents a day, so patterns and outliers are easily seen.

Validation checks data quality, schema conformance and completeness before downstream processing, satisfying the pipeline's need to catch corrupt or malformed records early. In orchestration patterns, it typically runs as a discrete task between ingestion and transformation, allowing failed batches to be quarantined rather than propagated, which protects subsequent stages from propagating errors.

The Dataflow Update command replaces the job's transform graph while preserving the existing pipeline state, including buffered elements and watermarks. This satisfies the stem's no-state-loss constraint during a rolling update, unlike stop-and-restart approaches that discard in-flight data and reset streaming state.

Parquet is columnar and compressed, so BigQuery can load it much faster and at lower cost than uncompressed CSV. Splitting the data into multiple files allows BigQuery to parallelize the load across slots and workers. Since the data is already clean and needs no transformation, a direct load of Parquet is the fastest and most economical path for this 4 TB dataset.

Archive suits data accessed once yearly because its minimum storage duration is 365 days, matching that retrieval frequency without early-deletion charges. At roughly $0.0012 per GB monthly, it undercuts Coldline and Nearline, satisfying the stem's cost-effectiveness constraint while Microsoft Entra ID remains irrelevant to GCS class selection.

The --sdk_container_image pipeline option tells Dataflow which custom container image to pull for the worker environment. Specifying it satisfies the requirement to run the job with your own container rather than the default SDK image.

Column-level security with policy tags in BigQuery lets you classify columns containing identifiers and restrict access so analysts can query other columns without seeing sensitive values. This directly supports viewing aggregated results while blocking individual identifiers. It is the native BigQuery mechanism for fine-grained column access control.

A date range control lets users select a specific time period, which then filters all charts on the page that use a date dimension. This provides the required interactivity for filtering sales data by date range, allowing users to focus on specific periods and analyze trends over time.

A Single Value visualization computes one aggregate—here the average—over the dashboard's underlying query, satisfying the requirement for a single dashboard-wide metric. Unlike table or chart tiles, it renders exactly one number without dimensional breakdown, directly meeting the stem's constraint of displaying the average across the entire dashboard.

Dataplex Data Quality rules are declared in a YAML spec, where each rule targets a column and sets a dimension plus a nonNull expectation. Defining the rule in that YAML configuration file is what enforces the no-NULL constraint on user_id.

The `fields` parameter in an Explore definition explicitly whitelists which fields appear in that Explore's menu, hiding all others. If a specific set of fields is missing, this parameter is restricting visibility, satisfying the stem's constraint that only certain fields are absent rather than the whole Explore.

Configuring retries and retry_delay in default_args ensures that failed tasks are retried automatically. Adding exponential backoff with max_retry_delay increases the wait time between retries, which prevents hammering the Cloud Storage API during an outage and gives it time to recover. This approach is standard for handling transient failures in Airflow DAGs.

RANK() OVER (PARTITION BY region ORDER BY sales DESC) assigns a rank within each region independently, letting you filter to ranks 1-5 per region. The PARTITION BY region clause is the mechanism that satisfies the per-region top-5 requirement.

Partitioning the table by `order_date` makes BigQuery prune partitions that do not match the filter predicate, so a query for a specific date range reads only those partitions rather than the full 500 TB. This directly reduces bytes scanned and cost while keeping the table name and existing SQL unchanged, satisfying every stated requirement.

ML.EXPLAIN_PREDICT produces predictions together with explanations that attribute each prediction to the contributing input features, using methods such as Shapley values. This lets the team see which customer attributes most influenced churn predictions. It is designed for exactly this interpretability need and works with supported model types trained in BigQuery ML.

An ExternalTaskSensor polls the Airflow metadata database for the state of a task in another DAG, so the downstream transformation only proceeds once the partner load task has actually succeeded. This encodes the real cross-DAG dependency, works across DAGs in the same environment, and fails or retries based on the real upstream state rather than a fixed time.

This query correctly filters the last 30 days using DATE_SUB, groups by product_id, sums revenue, applies a HAVING clause to filter aggregated totals over $10,000, and orders by total revenue descending. The alias total_revenue is valid in HAVING and ORDER BY in BigQuery. It matches all requirements precisely.

Dataplex lakes accept multi-region locations such as US, satisfying the stem's requirement for a valid placement. Multi-region scopes data across the United States, unlike single-region or zonal options, and Dataplex supports this alongside regional locations. This makes US (Multi-region) a legitimate lake location.

Scheduled Reports run a Look or dashboard on a defined cadence and deliver the results by email to specified recipients, removing manual delivery. This satisfies the stem's stated benefit of automatically emailing reports to stakeholders.

Nearline satisfies the stem's requirement for a valid GCS storage class, offering low-frequency access with a 30-day minimum storage duration and retrieval costs. It sits between Standard and Coldline on the availability-versus-cost axis, making it a legitimate class alongside Standard, Coldline and Archive.

Dot notation traverses the nested RECORD structure directly, resolving the 'city' field within the 'address' record without unnesting. BigQuery supports this path syntax for accessing nested fields, satisfying the requirement to reach a field inside a record.

Dataflow pipelines can incorporate the Cloud DLP API as a transform to de-identify data in motion. You can read the CSV from Cloud Storage, apply the DLP transform to obfuscate PII, and write the cleaned data to BigQuery. This is a scalable and automated approach to prepare data with PII obfuscation.

A Looker Explore queries BigQuery directly, so the line chart always reflects the latest data without extraction or scheduling. Time-series line charts suit daily sales trends, and Looker's persistent derived tables or caching handle the six-hourly refresh cadence.

External tables in BigQuery allow you to query data directly from Cloud Storage without loading it into BigQuery. This satisfies the requirement to query without moving data. You can define the schema and then use policy tags on columns to enforce fine-grained access control, making this a necessary step for the solution.

Data Catalog surfaces technical metadata and schema for BigQuery tables registered in Dataplex, satisfying the requirement to inspect table structure. Dataplex automatically publishes entries to Data Catalog, which exposes column names, types and descriptions through search and the API, letting you view schema without querying the table directly.

A Data Zone groups assets within a lake so policies, IAM and metadata rules apply consistently across them. This satisfies the stem's governance purpose, providing the logical boundary where Dataplex enforces access and policy controls.

Rename Columns applies a transformation across all column headers at once, letting you strip special characters and enforce consistent casing globally. This satisfies the stem's requirement to standardise inconsistent header names before loading into BigQuery.

BI Engine is an in-memory analysis service that accelerates SQL queries, including those from Looker Studio. By creating a BI Engine reservation and assigning it to the project containing the dataset, queries become faster and more concurrent, reducing load times and timeouts. This directly addresses performance without altering data.

Rows written via the streaming API initially reside in a buffer before being committed to managed storage, and operations such as certain exports and some query paths may not see those rows immediately. The dashboard and the direct BigQuery report can therefore be evaluated against marginally different points in time, producing small total differences that resolve on their own once the buffer flushes.

A persistent disk attached to the Workbench instance stores data independently of the instance's running state, so notebooks survive stopping and restarting. Saving to the boot disk or instance memory would be lost, whereas the attached persistent disk satisfies the durability requirement.

This correctly configures the task to retry 3 times with a 5-minute delay. By setting downstream tasks to trigger_rule='all_done', they will execute even if the upstream task fails after retries. This allows the DAG to continue running other tasks, satisfying both requirements without altering the task's own trigger rule.

Cloud Scheduler can also use OAuth tokens for authentication. By configuring an OAuth service account with the roles/workflows.invoker role, Cloud Scheduler will generate an OAuth access token and include it in the Authorization header. This is another valid and secure method for authenticating to the Workflows API.

Table Calculations compute values in Looker's query layer using LookML functions such as offset and pivot_offset, deriving year-over-year growth from returned rows. This satisfies the stem's constraint of calculating growth without modifying the underlying SQL.

A step-level retry block accepts max_retries, an interval with optional exponential backoff, and a predicate expressed in CEL that decides which responses are retryable. Matching only the 503 status means transient cold-start failures are retried, while a 400 response does not satisfy the predicate and is raised as a step error, which is exactly the desired distinction.

Cloud Storage lifecycle management policies automatically delete objects once they reach the specified age, satisfying the 30-day regulatory retention limit without custom code or manual intervention. Rules apply at bucket level across matching objects, making this the most efficient, serverless mechanism for enforcing time-based deletion at scale.

Datastream is a serverless change data capture (CDC) service that supports MySQL as a source and can replicate changes to BigQuery in near real-time. It reads the database log (binlog) with minimal impact on the source and requires no custom code. This meets the requirements for continuous replication and low impact.

The `gcloud` CLI provides the `gcloud workflows deploy` and `gcloud workflows run` commands, giving direct command-line control over Workflow deployment and execution. This satisfies the stem's requirement for a primary deployment and management tool, as Workflows is a Google Cloud service administered through the gcloud interface rather than a separate utility.

Dataflow workers are billed per vCPU-hour, and each worker carries startup and shuffle overhead. Overprovisioning workers for a small dataset multiplies cost and coordination overhead without improving throughput, since parallelism is bounded by data size.

Cloud Scheduler is purpose-built for cron-style triggering, invoking Workflows executions on a defined timetable via HTTP targets. It satisfies the scheduling constraint directly, unlike Eventarc (event-driven) or Pub/Sub (message-based). Cloud Scheduler handles retries and time-zone-aware cron expressions, making it the designed service for time-based Workflows invocation.

Exactly-once delivery on the Pub/Sub subscription prevents redelivery of acknowledged messages, and the BigQuery Storage Write API in exactly-once mode ensures each record is written once even if the pipeline retries. Together they provide end-to-end duplicate elimination for streaming insertions, which matches the requirement to avoid duplicate rows.

Partitioning the table by a date or timestamp column allows BigQuery to prune partitions when queries filter on that column, scanning only the relevant partitions. This reduces bytes processed and improves performance for recent-data queries. It requires no change to how analysts write queries as long as they filter on the partitioning column.

A scatter plot plots one numerical variable on each axis, exposing correlation, clusters and outliers between them. This directly satisfies the stem's requirement to show the relationship between two numerical variables, unlike categorical or single-measure charts.

Data Catalog provides a searchable, organisation-wide inventory of datasets and tables, letting users discover and tag assets across all projects. It indexes metadata from BigQuery and other sources, satisfying the requirement to search the entire Google Cloud organisation.

Deleting a Dataplex lake removes only the lake resource and its metadata configuration; the underlying Cloud Storage or BigQuery assets themselves are untouched. This satisfies the stem's scenario, since the assets remain accessible directly after the lake is gone.

Setting `value_format_name` on the measure in the LookML view file applies a named format model, such as `usd`, directly to the measure's definition. This satisfies the stem's requirement to correct the display format of the total revenue measure while preserving its existing summation behaviour.

When a Looker Studio data source is defined by a custom query, the query result is cached and reused for the data source's freshness setting, commonly twelve hours by default. The dashboard therefore shows the cached snapshot rather than re-querying BigQuery each time it loads. Reducing the freshness interval or switching the source to a direct table reference restores more current data.

Clicking the refresh icon triggers a re-run of the dashboard's underlying queries against the database, fetching current data rather than serving cached results. This directly satisfies the stem's requirement to reload data within Looker, since the refresh control is the interface element that forces query re-execution and updates every tile.

Data Access audit logs on the GCS bucket record object-level operations, including reads and writes to DAG files, capturing who modified what and when. This satisfies the audit requirement without deploying additional tooling, making it the most efficient native mechanism.

Partition pruning only occurs when the query filters on the partitioning column. Without that filter, BigQuery cannot eliminate any partitions, so it reads every partition, resulting in a full table scan and higher bytes billed.

Cloud Storage stores an MD5 hash for each object, retrievable via the object's metadata, letting you compare it against the locally computed hash of the uploaded file to confirm integrity, which is the standard verification method the stem asks for.

Invoking the workflow through the REST API satisfies the HTTP POST trigger requirement, since Workflows exposes an execution endpoint that accepts POST requests carrying the workflow ID and runtime arguments. This lets external callers start executions directly over HTTP, matching the stem's constraint without needing an Eventarc or Pub/Sub intermediary.

Watermarks track event-time progress in the pipeline, defining when a window's results are considered complete. Configuring an allowed lateness window lets Dataflow retain state and process events arriving after the watermark, directly satisfying the requirement to handle out-of-order sensor data from Pub/Sub.

Discovery jobs sample data in the zone's assets and infer schema, deriving column names, data types and structure from the actual files or tables. This inferred schema populates the Dataplex metadata catalogue, enabling search and governance without manual schema definition.

Retention requirements dictate whether data must remain immutable for compliance periods, which rules out classes lacking Object Lock support or lifecycle deletion flexibility. Standard, Nearline, Coldline and Archive differ in minimum storage duration and retrieval costs, so mandated retention windows directly determine which class avoids early-deletion penalties.

Query History exposes each query's execution duration and generated SQL, letting you pinpoint whether latency stems from complex joins, missing aggregates, or full-table scans before touching dashboard configuration. This directly satisfies the stem's requirement to identify the first diagnostic step for a slow-loading Looker dashboard, since execution metrics reveal the underlying bottleneck.

Cloud Storage acts as a GCP ingestion tool because it accepts raw data files that pipelines then load into BigQuery or Dataproc, satisfying the stem's requirement for a landing zone where external sources deposit data before processing.

BigQuery's web UI provides a built-in 'Save results' menu that writes directly to a Google Sheet, avoiding exports to Cloud Storage or the API. This satisfies the requirement for the easiest method, since no external tooling or intermediate file transfer is needed.

Filtering on the partitioned column lets BigQuery prune partitions and scan only matching data, so bytes billed drop sharply from the full 500GB. This directly satisfies the stem's cost-reduction constraint, since BigQuery charges by bytes read, and partition elimination is the cheapest lever available.

The --autodetect flag tells BigQuery to infer the schema from the data, and --skip_leading_rows=1 tells it to ignore the first row, which is the header. Together, these flags allow a seamless load of CSV files with headers into a new table. This is the standard approach for ad-hoc loads when you do not want to define a schema manually.

Data quality tasks parse the YAML rule specification before execution; malformed syntax, invalid field names or wrong indentation causes the task to fail at configuration load, before any data is scanned. This directly explains the observed task failures.

Partition expiration in BigQuery automatically deletes partitions after a specified number of days. For ingestion-time partitioned tables, you can set the partition expiration at the table level, and BigQuery will remove partitions older than the specified duration. This is a native, automated way to manage storage costs and ensures partitions are completely deleted.

A scatter chart plots individual data points on two axes, making it ideal for visualizing the relationship between two metrics. By placing spend on the X-axis and revenue on the Y-axis, each campaign appears as a point. Campaigns with high spend and low revenue will appear in the lower-right area, enabling quick identification of outliers.