Google Cloud Associate Data Practitioner (GCP-ADP) (GCP-ADP) — Questions 226–272

Because the table is at line-item grain, counting rows would inflate the denominator whenever an order contains multiple items. Dividing summed revenue by COUNT(DISTINCT order_id) yields the true average per order, and grouping by country produces one value per country. This is the standard way to express a ratio measure in Looker when fan-out would otherwise distort the calculation.

Google Cloud Storage automatically encrypts every object at rest using AES-256 before writing to disk, with no configuration required. This default server-side encryption satisfies the stem's requirement to protect sensitive data at rest, applying uniformly across all storage classes and regions without additional keys or setup from you.

Automatic data discovery profiles and scans data in the lake, using its built-in classifiers to detect sensitive information such as PII and attach corresponding tags automatically. This satisfies the stem's requirement for organisation-wide automatic detection and tagging without manual intervention.

Partitioning the table on transaction_date lets BigQuery prune partitions when a query filters on that column, so only the relevant date ranges are read instead of the full 400 TB. It requires no schema change to existing columns and is the standard low-cost lever for reducing bytes scanned on time-filtered analytical workloads.

Partitioning the table by the timestamp column (e.g., daily partitions) allows BigQuery to scan only the partitions that satisfy the time filter. This dramatically reduces the amount of data processed, lowering cost and improving performance. For future queries with similar time filters, the partition pruning applies automatically. This is the recommended approach for large time-series datasets in BigQuery.

Dataflow provides a distributed Apache Beam runner that scales horizontally across many workers, satisfying the large-dataset scalability constraint. Unlike single-node sampling engines, it parallelises cleansing transforms across the dataset, so processing time does not degrade as volume grows. Dataprep uses Dataflow as its default execution engine for exactly this reason.

Apache Beam's PubSubIO and BigQueryIO support windowing and triggers to handle late-arriving data. BigQueryIO.Write can be configured with exactly-once semantics using the withMethod and withWriteDisposition settings. This approach ensures data is processed correctly even if it arrives late, and exactly-once processing is achieved. It is the standard way to build such pipelines in Dataflow.

An external table points at the CSV object in Cloud Storage, so each query reads the current file contents. Joining that external table with a native campaign table is fully supported in BigQuery SQL, and it avoids any weekly load job, which matches the requirement to always reflect the latest partner data.

Cloud Scheduler can invoke Workflows via an HTTP POST request. The Workflows API expects runtime arguments in the request body as JSON. By setting the 'Body' field to {"projectId": "my-project"}, the argument is passed correctly. The Workflows execution will receive it as part of the execution context, making it available to the workflow definition.

IAM roles grant identities granular permissions on buckets and objects, letting you restrict who may read, write or administer GCS data. This satisfies the question's requirement for an access-control method, operating at the identity and resource-policy layer rather than encrypting the stored bytes.

Vertex AI Workbench instances run as a service account; client libraries and the BigQuery API use its credentials automatically, so no manual key or user login is needed. This satisfies the stem's private-dataset access requirement through IAM roles on that account.

Table calculations operate on the results already returned by the query, so offset() can reference the previous row's revenue value within the month-ordered result set. Dividing the difference by the prior month's revenue yields a growth percentage, and because the calculation lives in the Looker tile it recalculates automatically whenever the dashboard runs with fresh data, requiring no manual monthly edit.

OIDC tokens carry a signed identity assertion that Workflows validates against the caller's service account, satisfying the stem's requirement for authenticated HTTP invocation. Cloud Scheduler attaches this token in the Authorization header, and granting that service account the Workflows Invoker role scopes permission precisely, avoiding broader OAuth scopes.

Prefixing a command with ! in a Jupyter notebook cell executes it as a shell command on the instance, so pip installs seaborn into the notebook's Python environment. This satisfies the stem's requirement for installing the library in the Vertex AI Workbench instance.

Pub/Sub is a fully managed, globally scalable messaging service that decouples mobile producers from downstream consumers, absorbing unpredictable device traffic bursts. It satisfies the streaming ingestion constraint by providing durable, at-least-once delivery with automatic scaling, unlike batch-oriented alternatives such as Cloud Storage or Dataflow itself.

`count_distinct` aggregates by counting unique values in a dimension, directly satisfying the requirement for distinct customer counts. Unlike `count`, which tallies every row including duplicates, it deduplicates at query time. This makes it the precise measure type for counting distinct customers in a Looker model.

execution_timeout is an Airflow task parameter that sets the maximum time a task is allowed to run. If the task exceeds this duration, it is terminated and marked as failed. Setting it to 30 minutes ensures that long-running tasks are killed, allowing downstream tasks to handle the failure or alerts to be triggered. This directly addresses the requirement to avoid indefinite blocking.

Datastream is a serverless change data capture and replication service that reads the PostgreSQL write-ahead log, captures inserts, updates, and deletes with minimal latency, and writes them to BigQuery without custom extraction code. It handles the primary key updates and schema changes as part of replication, matching the continuous, low-latency requirement.

Dataplex Discovery automatically scans registered data lakes and zones, generating metadata and profiling statistics for each dataset. This satisfies the stem's requirement for the primary function: it catalogues assets into the Knowledge Catalog and profiles their structure and content, enabling search, governance and understanding without manual metadata entry.

A date range control binds a filter directly to the report canvas, letting viewers adjust the start and end dates at view time without opening the report editor. This satisfies the stem's requirement for dynamic, user-driven date selection, since the control writes the chosen range into the chart queries rather than hard-coding it.

Dataplex lakes span projects: a single lake acts as the governance boundary, and you attach assets that reference BigQuery datasets or Cloud Storage buckets residing in other projects. This gives one unified view without duplicating or moving the underlying data.

Object Versioning retains noncurrent object generations after deletion or overwrite, letting you restore the prior generation for as long as you keep it. Combined with a lifecycle rule that deletes noncurrent versions after 30 days, it satisfies the stem's 30-day restorability constraint for deleted files in Cloud Storage.

Deleting unused environments removes the underlying GKE cluster, Cloud SQL instance, and storage buckets that Composer provisions per environment, eliminating their hourly compute and database charges. This directly satisfies the stem's cost-reduction goal, since idle environments still bill continuously even when no DAGs run.

Partitioning the table on event_time allows BigQuery to scan only the partitions that contain the relevant data. When you filter on a specific day, BigQuery prunes all other partitions, significantly reducing the bytes processed and thus the query cost. This is the most effective way to minimize costs for queries that filter on a time column. It requires recreating or altering the table to be partitioned.

Selecting a higher memory machine type gives each worker more RAM, preventing the out-of-memory crashes observed. This directly addresses the excessive memory consumption constraint by increasing available memory per worker rather than altering pipeline logic.

Dataplex defines its own IAM roles, such as Dataplex Viewer and Dataplex Editor, that grant access to lake, zone and asset metadata independently of the underlying storage. Assigning these roles to users satisfies the requirement to control access within the lake itself.

A materialized view pre-computes and stores the aggregated results, so the report queries a much smaller dataset. This significantly reduces query time and cost. Since the view is refreshed automatically, the report remains current. The layout of the report remains the same; only the underlying data source changes to a more efficient one.

Cloud Functions provides event-driven, serverless execution triggered by GCS object finalisation events, so sporadic file arrivals invoke ingestion only when data lands. Dataproc and Dataflow need persistent clusters or streaming jobs, which waste resources during idle gaps between arrivals.

A WITH clause defines a named common table expression that BigQuery evaluates once and references throughout the query, avoiding repeated subquery scans. It is the most efficient syntax for a temporary table used later in the same query.

Increasing the number of workers allows Dataflow to parallelize the processing across more machines, which can significantly speed up a batch pipeline. Using a larger machine type provides more CPU and memory per worker, which helps if the transformations are resource-intensive. This is a common and effective way to optimize Dataflow performance when the pipeline is slow due to insufficient resources.

Session windows group elements separated by gaps of inactivity, merging windows when events arrive within the configured gap duration. This directly satisfies the stem's session-based activity requirement, where bursts of user actions must be captured as discrete sessions rather than fixed time intervals.

Adding a BigQuery dataset as a lake asset requires the user to hold BigQuery permissions on that dataset, such as bigquery.datasets.get. This satisfies the stem's prerequisite, since Dataplex must be able to read the dataset's metadata before registering it.

Cloud Data Fusion database source plugins support an incremental mode where you specify a column such as a last-modified timestamp or an incrementing ID. The plugin stores the last processed value and uses it to filter subsequent runs, so only new or changed rows are ingested. This directly satisfies the daily incremental load requirement without custom code.

Dataflow autoscaling evaluates the ratio of work backlog to throughput, scaling workers when backlog per worker exceeds the target. This directly satisfies the stem's constraint: autoscaling responds to outstanding work versus processing rate, not CPU or memory utilisation, which Dataflow does not use for horizontal worker scaling decisions.

Dataplex zones are logical partitions of a lake, and IAM policies can be attached at zone level, so each zone grants distinct access. This satisfies the stem's requirement for a valid way zones improve data organisation.

Pub/Sub decouples ingestion from processing by durably buffering messages across distributed partitions, absorbing millions of messages per second without backpressure. This satisfies the stem's buffering constraint: publishers write independently of BigQuery load, letting subscribers drain at their own pace and preventing data loss during traffic spikes.

Workflows allows you to define a retry predicate that examines the error or response and a retry policy with backoff parameters. By predicating only on 503, the step retries on server errors but fails immediately on 404. This matches the requirement precisely and uses native Workflows retry capabilities.

Allowed lateness extends how long a window's state is retained after the watermark passes the window end. Setting it lets late events still be assigned to their correct window and emitted as late panes, so the mobile events arriving hours behind are counted. This is the intended Dataflow mechanism for bounded late data, and the team must also decide how to handle late panes downstream.

`skipLeadingRows` skips the header row, and `maxBadRecords` lets the load succeed as long as the number of bad rows does not exceed the threshold. BigQuery records the count of skipped records, which you can inspect. A row with extra columns counts as a bad record, so this configuration tolerates malformed rows instead of failing the entire job.

Project-level Viewer grants broad read access but does not include Dataplex-specific permissions. The roles/dataplex.viewer role supplies dataplex.lakes.get and related metadata reads required to render the Lake in the console, so it must be granted separately.

Extracts store a materialized copy of the query results inside Looker Studio rather than querying BigQuery on every page load. This removes repeated round trips to the warehouse, cutting render latency, and the extract itself is subject to the connector's row limit, so pairing it with aggregation keeps the snapshot within bounds. It is a standard remedy for slow dashboards that do not need second-by-second freshness.

Fixed windows partition unbounded data into equal, non-overlapping intervals based on event time, satisfying the stem's requirement for a genuine Dataflow windowing type. Apache Beam, which underpins Dataflow, defines fixed, sliding, and session windows as its core built-in types, so Fixed qualifies directly.

A Dataplex asset is created within a zone, and its location is not set independently; it is derived from the parent zone's location. This inheritance satisfies the constraint that assets cannot span regions outside their zone.

Deployment is a required pipeline development step because a Dataflow pipeline must be built, tested and then released to production. Packaging the Apache Beam code and submitting it to the Dataflow service, whether via template or direct runner, satisfies the stem's demand for an end-to-end development lifecycle rather than coding alone.

Cloud Functions suits this because it executes event-driven, serverless code on a schedule via Cloud Scheduler, letting you fetch a fresh OAuth token and call the external API each run. It satisfies the periodic execution constraint without managing servers, unlike batch-oriented Dataflow or orchestration-heavy Composer.

A Flow encapsulates the entire recipe, including transformations and inputs, so sharing it gives teammates the complete reproducible pipeline rather than just output data. This directly satisfies the requirement to share the preparation work itself.

A BigQuery external table lets you query data that lives in Cloud Storage directly with SQL, so no ingestion into BigQuery native storage is needed. Enabling automatic metadata refresh makes BigQuery detect new files and update the table's partition and file metadata on its own, which is exactly the behavior requested. The external table definition stays decoupled from the physical data, and the refresh interval is governed by the max_staleness setting.