Google Cloud Associate Data Practitioner (GCP-ADP) (GCP-ADP) — Questions 1–75

Time travel reads a consistent version of the table as of a past timestamp, so concurrent streaming inserts cannot skew results mid-query. This satisfies the stem's consistency constraint on a constantly updated table, unlike a plain query that may observe rows arriving during execution.

Dataplex enforces access through IAM policy bindings at three granularities: lake, zone and asset, letting permissions be scoped precisely to the resource hierarchy rather than the whole project. This satisfies the stem's requirement for true security statements, since inherited roles cascade downward from lake to zone to asset.

Streaming to BigQuery is a canonical Dataflow pattern: an unbounded source feeds a pipeline that writes continuously into BigQuery, typically with windowing and streaming inserts. It matches the stem's request for common Dataflow pipeline patterns.

Setting catchup=False tells the Airflow scheduler to skip all historical intervals that elapsed before the DAG was enabled, so only runs from the current time forward are created. Combined with the cron expression '0 2 * * *', the DAG fires daily at 02:00 UTC without backfilling the previous week. This directly satisfies the requirement of no historical runs.

This expression calculates the profit margin by subtracting total cost from total revenue and dividing by total revenue. Using SUM aggregates the values correctly before the division, ensuring the result is a percentage. This is the correct way to compute a ratio of aggregates in Looker Studio.

Enabling schema update options on the load job lets BigQuery extend the existing destination table schema with new fields discovered in the source data. Because the files are newline-delimited JSON and can gain top-level fields, this avoids load failures while preserving previously loaded rows. It matches the requirement to infer and evolve the schema automatically, though it only adds columns and does not remove or change existing ones.

Cron syntax in `schedule_interval` accepts five fields, letting you pin execution to a specific weekday (for example `0 9 * * 1` for Monday 09:00). This directly satisfies the stem's requirement for a task running on a chosen day of the week, since Airflow parses the day-of-week field natively.

A policy tag taxonomy is the prerequisite container that must exist before any policy tag can be created or applied. Dataplex requires tags to be organised within a taxonomy, so the column-level access control cannot be enforced until that hierarchy is defined in the same region as the BigQuery data.

Dataflow is a fully managed, autoscaling service for stream and batch processing. A single streaming pipeline can read from Pub/Sub, compute windowed aggregates into BigQuery, and simultaneously write raw events to Cloud Storage for long-term retention, all with Apache Beam code and managed runners. This satisfies near-real-time aggregation, seven-year retention, and minimal operational overhead in one pipeline.

A BigQuery subscription is a native Pub/Sub subscription type that streams messages straight into a BigQuery table without any code to write or servers to manage. It supports dead-letter topics and handles batching and retries automatically. This is the simplest supported path for getting Pub/Sub events into BigQuery with near-real-time latency.

Partitioning by transaction date lets BigQuery prune whole partitions when queries filter on the date, and clustering by store_id sorts data within each partition so filters on store_id scan fewer blocks. Together they cut bytes scanned for the exact access pattern described, with minimal ongoing effort. This is the standard, low-maintenance optimization for large time-series tables with a common secondary filter column.

Clustering on `event_type` sorts data within each partition by that column. When queries filter on `event_type` and the partition column, BigQuery can skip blocks that do not match the cluster value, reducing bytes scanned. This directly addresses the high cost while preserving the existing partitioning strategy.

BigQuery external tables can query data directly in Cloud Storage without loading it into BigQuery storage. For Parquet files laid out in a hive-partitioned folder structure, the external table definition can use the hive partitioning option so partition pruning works. This lets analysts run SQL against the raw files immediately, matching the requirement.

A consistent key=value naming convention, like dt=2024-01-01, allows BigQuery and other tools to automatically infer partitioning when you create an external table. This improves query performance by enabling partition pruning. It also makes the data layout self-describing and easier to manage across tools.

Dataflow supports side outputs, which let a DoFn emit records to a separate PCollection based on a tag. By catching parse exceptions and emitting the bad records to a tagged side output, the pipeline can continue processing valid messages while routing failures to a dead-letter destination. This directly satisfies the requirement to isolate malformed records without stopping the pipeline.

Workflows scales automatically because it is a fully managed, serverless orchestration service: Google provisions and elastically adjusts the underlying compute as execution demand rises or falls. This satisfies the stem's serverless constraint, since you never size, patch or manage instances, and pay only per step executed rather than for idle capacity.

Sensitive Data Protection, also known as Cloud DLP, provides managed discovery scans that sample and inspect data in Cloud Storage. You can configure infoType detectors such as CREDIT_CARD_NUMBER and generate findings that flag matching objects for review. This meets the requirement without custom code and is the Google Cloud service designed for automated sensitive-data classification.

Dataplex automatically captures lineage for supported services, including BigQuery and Dataflow, without manual instrumentation. This satisfies the stem's requirement to track lineage for a BigQuery table populated by a Dataflow pipeline, recording source-to-target relationships across those services.

Watermarks track event-time progress and trigger window emission, while Allowed Lateness lets the pipeline retain window state and reprocess records arriving after the watermark passes. Together they satisfy the ordering and late-arrival constraint in event-time streaming.

The Executor determines how and where task instances run, distributing queued tasks across workers. In Cloud Composer, this satisfies the requirement to scale parallel task execution: the Celery Executor dispatches work to worker pods via a queue, while KubernetesExecutor launches a pod per task, directly controlling task-to-worker allocation.

CMEK applied at Lake or Zone creation propagates that key to every asset within the zone, so encryption is enforced by the container itself rather than per-asset. This satisfies the stem's requirement that all data in the Dataplex Zone use a Customer-Managed Encryption Key.

A date range control enables users to adjust the time period for the data shown. This is a key interactive feature that allows sales team members to view performance over different periods, such as last week, last month, or a custom range. The charts automatically refresh based on the selected dates.

Fixed windows align with the 5-minute requirement. Allowed lateness of 10 minutes lets the pipeline accept events that arrive after the watermark passes, and ACCUMULATING mode re-emits the updated aggregate including late data. Together these features produce correct per-window results for late arrivals while allowing the pipeline to eventually discard state once the lateness horizon passes, controlling cost.

A Looker Explore presents a curated view of a model, letting users pivot, filter and visualise data without SQL. This satisfies the stem's requirement of exploring and visualising data, distinguishing it from dashboards, which display pre-built, fixed visualisations.

Partitioning physically divides a table by a column such as date, so queries filtering on that column scan only the relevant partitions rather than the whole table. This reduces bytes processed, directly lowering on-demand query cost and improving performance — satisfying the stem's requirement for the primary benefit.

Cloud Storage can publish object change notifications to a Pub/Sub topic, which durably buffers events even if the Composer environment is temporarily unavailable. A consumer in the environment, such as a Pub/Sub sensor or an event-driven trigger, then starts the DAG as soon as a message is received, giving near-real-time start with resilience to brief outages.

A signed URL embeds a cryptographic signature and expiry in the link itself, granting time-limited read or write access to a specific object without requiring the third party to hold IAM credentials or a Google account. This satisfies the temporary-access constraint.

Enabling 'Require partition filter' forces every query against the table to include a partition filter, rejecting unfiltered scans. This prevents full-table scans across the petabyte dataset, directly enforcing the cost-optimisation constraint by limiting bytes read.

Bucket-level IAM policies bind roles such as Storage Object Viewer to specific principals, so only granted team members can access objects. This satisfies the stem's constraint of restricting a single bucket to a named team, since IAM evaluates the binding per bucket rather than project-wide.

In Workflows, the ${} syntax substitutes the value of a variable or expression into the step definition. Since result already holds the parsed JSON payload from the Cloud Run response, referencing ${result} passes that entire object as the body argument to the Cloud Function call. This is the documented way to chain outputs between steps without manual serialization.

This approach aggregates revenue by month and category, then uses RANK() partitioned by month to rank categories within each month. Filtering for ranks 1 through 5 yields the top 5 per month. It is efficient and standard in BigQuery for top-N per group analysis, and correctly handles ties if needed.

BigQuery ML lets you create, train and evaluate machine learning models directly in BigQuery using SQL statements such as CREATE MODEL, keeping data in place. This satisfies the described purpose of running machine learning through SQL rather than external frameworks.

Fan-out occurs when a one-to-many join multiplies fact rows, because the join key lacks a properly defined primary key on the dimension side. Looker then aggregates inflated counts, producing the duplicate data observed. Defining the primary key correctly in LookML prevents this row multiplication, satisfying the stem's duplicate-data constraint.

BigQuery's CSV loader automatically handles quoted fields containing commas when the schema is provided or autodetected. By default, it respects double quotes as field enclosures, so embedded commas within quotes are parsed correctly without additional options.

A histogram bins a numeric column into ranges and plots frequency per bin, revealing the value distribution's shape, spread and skew. This directly satisfies the stem's requirement to see how values in a column are distributed.

Declaring a primary_key marks the dimension as unique, so Looker treats that view as the "one" side of a join and applies symmetric aggregates when aggregating measures across the relationship. This prevents fan-out duplication of totals, satisfying the stem's requirement to describe the primary_key's impact on join behaviour.

Airflow's execution_timeout is a task-level parameter that overrides the default timeout for that task. Setting it on the long-running task allows it to run longer without changing the environment-wide worker timeout or affecting other tasks, directly solving the problem.

INFORMATION_SCHEMA.JOBS exposes per-job metadata — bytes processed, slot milliseconds, user and statement type — for completed queries in a project or region. Querying it lets you rank jobs by cost and pinpoint expensive patterns, satisfying the requirement to monitor usage and identify costly queries.

Datastream is a serverless change data capture (CDC) and replication service that supports Oracle as a source and BigQuery as a destination. It can connect to on-premises databases securely using private connectivity such as VPN or Interconnect. It requires no code to set up and provides low-latency replication, making it ideal for this scenario.

Vertex AI Workbench provides managed JupyterLab notebook instances integrated with Google Cloud, handling provisioning, scaling and security. It satisfies the managed-environment constraint because Google operates the underlying compute rather than you maintaining your own notebook server.

Filtering directly on the partition column lets BigQuery prune partitions and read only the 30 days required, which is the single biggest lever on scan cost for partitioned tables. Selecting just the date and user identifier keeps the scanned columns narrow. Together these choices minimize bytes billed while still producing the daily active user counts the stakeholder requested.

Allowed lateness specifies how long Dataflow should wait for late data after the watermark has passed the end of a window. By setting allowed lateness to 10 minutes, you ensure that data arriving up to 10 minutes late is still included in the window's aggregation. This is the correct mechanism to handle late-arriving data in streaming pipelines.

BigQuery spills to disk when a query's intermediate data exceeds available memory per slot. Inefficient JOINs or GROUP BY operations producing large shuffles are the typical cause, forcing temporary data onto disk and slowing execution.

Granting roles/dataplex.admin provides full control over Dataplex assets, including creating, updating and deleting zones, assets and lakes. This satisfies the stem's requirement to manage assets within a Zone, since the role encompasses all Dataplex resource operations rather than only metadata viewing or data-level access.

A window function such as AVG over a 7-day range computes the rolling average inside BigQuery, and a view or scheduled query exposes that result to Looker Studio. This places the calculation in the database as requested and keeps the time series current as new events arrive, which is exactly what the analyst asked for.

Allowed lateness specifies how long after a window closes the pipeline will accept and process late data. Setting it to 30 minutes matches the maximum delay, ensuring that late events are included in the correct window. This is the standard way to handle out-of-order data in Dataflow.

External tables let BigQuery query data that lives in Cloud Storage without loading it, so the CSV files stay the system of record and no second copy is billed. This matches the requirement to avoid duplication and keeps the data in its original location while still allowing SQL access from BigQuery.

The Fine-Grained Reader role on a policy tag grants the ability to read data in columns associated with that tag. Users who lack this role on the tag cannot query the protected columns even if they have access to the table. This is the mechanism that restricts access to sensitive columns, so it is required.

Dataplex data quality tasks support CRON scheduling, letting the scan run automatically each day after the table's daily update. This satisfies the recurring, unattended execution the scenario demands, without external orchestrators such as Cloud Composer or manual triggering.

BigQuery load jobs read only from Cloud Storage or local upload, so external sources must first be staged in GCS. Uploading then loading uses the native, fastest ingestion path and satisfies the constraint that the file is not already in GCS.

Cloud Composer runs managed Apache Airflow, so existing DAGs, operators and plugins work unchanged, avoiding vendor lock-in and retraining. This directly satisfies the stem's orchestration benefit: teams port Python-defined workflows without rewriting them, and the open-source ecosystem supplies prebuilt operators for GCP services.

Dataplex is a metadata and governance layer for analytics — discovery, lineage, quality and lake zones. It does not serve high-frequency OLTP workloads, which need a transactional database with row-level locking and low-latency commits. Hence it is not a Dataplex benefit.

BigQuery Storage Write API accepts continuous record streams over gRPC, committing rows with exactly-once semantics, so it satisfies the stem's streaming ingestion requirement rather than batch loading. It supports both real-time appends and transactional writes, making it suitable for pipelines needing low-latency ingestion into BigQuery tables.

Dataprep's data profiling scans source data to compute column-level statistics such as value distributions, null counts and distinct counts, letting analysts assess quality before transformation. This satisfies the stem's requirement for a native Dataprep capability, since profiling is performed directly within the Dataprep by Trifacta interface without external tooling.

The gcloud CLI exposes Dataplex through the `gcloud dataplex` command group, letting you create, manage and query lakes, zones, assets and tasks from a terminal. This satisfies the stem's requirement for a tool that interacts with Dataplex, and it suits scripted or automated administration.

Exceeding `dagrun_timeout` causes the scheduler to mark the DAG run as failed and terminate its running tasks, satisfying the stem's requirement for default behaviour without extra configuration. This differs from task-level `execution_timeout`, which fails only the individual task rather than the whole DAG run.

Cloud Monitoring ingests Dataflow's native metrics — system lag, watermark delay and worker CPU — through the Cloud Dataflow integration, satisfying the requirement to track pipeline performance. Unlike Cloud Logging, which captures execution events, it exposes time-series dashboards and alerting policies on those streaming and batch metrics.

Complex DAGs with many tasks, dependencies, or heavy parsing increase scheduler and webserver load, causing UI latency. Simplifying DAG code reduces the parsing and scheduling overhead, directly mitigating the slow Airflow UI in Cloud Composer.

BigQuery load jobs parallelize across input files, so splitting the 4 TB file into many smaller files lets BigQuery read them concurrently, dramatically reducing load time. Specifying the pipe delimiter and skipping the header row ensures correct parsing. This is the recommended approach for large files and keeps costs low because you use the same load job capacity more efficiently. Compressed CSV is supported and splitting preserves the format.

A custom query connector executes your own SQL against the underlying database, returning the result set as a data source. This directly satisfies the stem's requirement for an arbitrary SQL query, unlike fixed-schema connectors such as BigQuery tables or Google Sheets, which expose predefined tables or ranges rather than free-form SQL.

A filter control allows viewers to select values from a dimension, and the selection applies to all charts on the page that use that data source. Using the region dimension as the control field enables multi-select and updates all charts, meeting the requirement exactly. This is the standard way to add interactive filtering.

BigQuery Data Transfer Service supports Cloud Storage as a data source and can load files from a bucket on a recurring schedule. This satisfies the minimal-operations requirement because Google manages the transfer runs, and the transfer automatically picks up new objects matching the source URI pattern on each scheduled run without any custom code.

Looker Studio is Google Cloud's native BI and dashboarding service, connecting directly to BigQuery datasets as a data source. This satisfies the stem's requirement for a valid way to visualise BigQuery data within the Google Cloud ecosystem.

Adding and removing data assets is a core Dataplex Lake administrative task, since a lake's assets define which Cloud Storage buckets and BigQuery datasets it governs. Without asset management, the lake cannot enforce metadata, discovery or security policies across its zones, directly satisfying the stem's requirement for common lake administration duties.

BI Engine is an in-memory analysis service that accelerates queries on BigQuery data. By reserving memory for the dataset, Looker Studio queries can be served from memory, drastically reducing latency for interactive charts and filters. This directly addresses slow report performance for large datasets.

Cloud Composer is Google Cloud's managed Apache Airflow service, provisioning the scheduler, web server, workers and metadata database while handling upgrades and scaling. This satisfies the stem's requirement for a managed way to run Airflow workflows, unlike self-managed Airflow on Compute Engine.

Storage Transfer Service performs server-side transfer directly between Amazon S3 and Cloud Storage, so data never traverses your local network or compute instances. This satisfies the stem's efficiency constraint by eliminating bandwidth bottlenecks and intermediate staging, while providing managed scheduling, retries and integrity verification for large-scale object migrations.

Secret Manager is the recommended service for storing sensitive data. By granting the Composer service account the Secret Accessor role, the DAG can retrieve the secret at runtime without hardcoding it. This follows security best practices and integrates natively with Cloud Composer.

Shared code belongs in plugins rather than duplicated across DAG files. Plugins are deployed to the Composer environment's plugins folder and imported by DAGs, so common operators and helpers stay versioned in one place, avoiding drift between pipelines.

Push subscriptions let Pub/Sub actively deliver each message to a registered HTTPS endpoint, rather than waiting for the subscriber to poll. This satisfies the stem's requirement for a genuine Pub/Sub feature, since push delivery is a native subscription type alongside pull, with automatic retry and configurable acknowledgement deadlines.

Centralised LookML modelling enforces governance by defining dimensions, measures and business logic once in a Git-versioned repository, so every report and dashboard inherits identical definitions. This satisfies the stem's requirement for consistent, auditable data definitions across the organisation, eliminating duplicated logic and drift between teams.

Allowed lateness specifies how long Dataflow waits for late data after the watermark passes the end of a window. Setting it to 1 hour ensures that events arriving up to 1 hour late are still processed and included in the correct window, meeting the requirement.

Cloud Audit Logs record who accessed or modified data and when, providing the immutable access trail needed to demonstrate PII handling compliance. Combined with Dataplex's governance and classification, this satisfies the stem's requirement to monitor and evidence access for compliance purposes.

Query Plan explanation exposes the execution graph, showing per-stage timing, shuffle and spill volumes, and row counts, so you can pinpoint the exact stage causing slowness. It satisfies the stem's requirement for a valid BigQuery performance analysis method, since the plan is generated from actual execution statistics rather than estimates.

A drop-down filter adds interactivity by letting viewers select dimension values that immediately constrain every chart bound to that data source. This satisfies the stem's requirement for interactive report elements, since filtering dynamically re-queries the underlying data rather than displaying a static, pre-rendered view.