PDE Practice Test 22 — 15 Questions

Question 1

You have a BigQuery table `sales` with columns `order_id`, `customer_id`, `order_date` (DATE), and `amount` (NUMERIC). You need to create a new table that contains, for each customer, the total sales amount and the date of their most recent order. The result should include `customer_id`, `total_amount`, and `last_order_date`. Which SQL query achieves this correctly?

Choose an answer to begin — your selection is scored in the full session.

Accepted Answer

SELECT customer_id, SUM(amount) AS total_amount, MAX(order_date) AS last_order_date FROM `project.dataset.sales` GROUP BY customer_id. To compute per-customer aggregates, you use GROUP BY on `customer_id` and aggregate functions SUM and MAX. The SUM function totals the amounts, and MAX retrieves the latest order date. Other approaches using window functions or incorrect grouping do not produce the required single-row-per-customer result. The correct query is straightforward and uses standard SQL aggregation.

Answer

SELECT customer_id, SUM(amount) AS total_amount, order_date AS last_order_date FROM `project.dataset.sales` GROUP BY customer_id, order_date

Answer

SELECT customer_id, SUM(amount) AS total_amount, LAST_VALUE(order_date) AS last_order_date FROM `project.dataset.sales` GROUP BY customer_id

Answer

SELECT customer_id, SUM(amount) AS total_amount, MAX(order_date) OVER (PARTITION BY customer_id) AS last_order_date FROM `project.dataset.sales`

Question 2

You are designing a BigQuery schema for a table that will store user profile data. The data includes a unique user ID, a list of email addresses (each with a type and address), and a timestamp of last update. You need to support efficient queries that retrieve all email addresses for a given user and also filter users by email type. Which schema design should you use?

Accepted Answer

Store emails as a repeated RECORD with fields 'type' and 'address' within the user table, using dot notation and UNNEST to query.. Using a repeated RECORD for emails allows BigQuery to store multiple emails per user with type and address fields. Queries can UNNEST the array to filter by email type and use dot notation to access fields. This denormalized design is efficient, avoids joins, and aligns with BigQuery's strengths for nested data.

Answer

Store emails as a single STRING column with a delimiter, and use SPLIT and REGEXP_CONTAINS to query.

Answer

Store emails as a JSON STRING column and use JSON functions to extract values.

Answer

Create a separate table for emails with a foreign key to the user table, and join when needed.

Question 3

Your organization runs a Cloud Composer (Apache Airflow) environment that executes a DAG every night to move data from Cloud Storage into BigQuery. The DAG has been succeeding for months, but last week the nightly load silently produced a BigQuery table with zero rows while the Airflow task still reported success. You need to add a safeguard that fails the DAG task whenever the loaded row count is zero before downstream tasks run. What should you do?

Accepted Answer

Add a BigQueryCheckOperator task immediately after the load task that asserts the target table row count is greater than zero.. The failure mode is a task that reports success while producing an empty result set, so the safeguard must inspect the data itself and raise a task-level failure. A BigQueryCheckOperator performs exactly that assertion and integrates with Airflow's dependency graph, which prevents downstream tasks from consuming invalid output and triggers configured alerting.

Answer

Increase the retries parameter on the load task so transient issues that produce zero rows are automatically retried.

Answer

Enable Cloud Logging export for the Composer environment and create a log-based alert that matches error text from the load operator.

Answer

Configure an Airflow SLA on the load task so that a missed deadline raises an alert about the empty table.

Question 4

A company is designing a streaming data pipeline to process real-time clickstream events. They need to aggregate events by session window with a 5-minute gap and enable exactly-once processing semantics. Which Google Cloud service should they use?

Accepted Answer

Cloud Dataflow with Apache Beam. Cloud Dataflow with Apache Beam is the correct choice because it provides native support for session windows with a 5-minute gap duration and exactly-once processing semantics via its sink and source integrations. Dataflow's Beam SDK allows you to define session windows using `Window.into(Sessions.withGapDuration(Duration.standardMinutes(5)))`, and its checkpointing and idempotent writes ensure exactly-once delivery even in failure scenarios.

Answer

Cloud Pub/Sub with Cloud Functions

Answer

Cloud Dataproc with Spark Streaming

Answer

Cloud Bigtable with Dataflow templates

Question 5

You need to schedule a recurring BigQuery query that aggregates data from a partitioned table and writes the results to a new table every day at 03:00 UTC. You want a fully managed solution with minimal operational overhead. What should you use?

Accepted Answer

BigQuery scheduled queries.. BigQuery scheduled queries are a fully managed feature that lets you schedule SQL queries to run at specified intervals. They require no additional infrastructure, support cron scheduling, and can write results to destination tables. This makes them the ideal choice for a recurring aggregation query with minimal operational overhead, unlike Cloud Functions, Composer, or VM-based cron jobs.

Answer

Cloud Scheduler with a cron job that invokes a Cloud Function to run the query.

Answer

Cloud Composer with a DAG that runs a BigQueryOperator.

Answer

A cron job on a Compute Engine instance that runs the bq query command.

Question 6

You are designing a Dataflow pipeline that reads from a Cloud Storage bucket containing thousands of small JSON files, transforms the data, and writes to BigQuery. The pipeline is slow and expensive because of the large number of small files. You want to improve throughput and reduce cost. What should you do?

Accepted Answer

Preprocess the data outside Dataflow to combine the small files into larger files (e.g., 100-500 MB each) in Cloud Storage, then run the Dataflow pipeline on the consolidated files.. The bottleneck with thousands of small files is the per-file read and metadata overhead. Combining them into larger files before the Dataflow run reduces the number of reads and improves throughput. Increasing workers or using FileIO hints does not address the root cause, and the Storage Read API is for BigQuery tables, not Cloud Storage files.

Answer

Use the FileIO transform with a match pattern and enable the `withHintMatchesManyFiles` option to optimize reading many small files.

Answer

Increase the number of Dataflow workers and set the autoscaling algorithm to THROUGHPUT_BASED to handle the small files in parallel.

Answer

Switch the pipeline to use the BigQuery Storage Read API to read the JSON files directly, bypassing Cloud Storage.

Question 7

A data engineer manages a Cloud Composer 2 environment. A DAG that downloads a large reference dataset each night occasionally exceeds the default task timeout because the source API is slow. The engineer wants the task to fail fast and be retried automatically rather than hanging for hours, and wants failed runs to be visible for alerting. Which configuration should be applied to the task?

Accepted Answer

Set execution_timeout on the task to a bounded value and set retries with a retry_delay so failed attempts are re-queued and surfaced in task state.. execution_timeout is the task-level setting that bounds wall-clock runtime and triggers a failure, while retries and retry_delay make the scheduler automatically re-queue the task. Together they produce fast failure, automatic retry, and a recorded state that alerting can observe, which is precisely what the scenario requires for a slow external API.

Answer

Configure the worker's celery_worker_autoscale setting to add more workers so the slow API call finishes sooner.

Answer

Set depends_on_past to true and add a retry_exponential_backoff so the task waits for the prior run before starting.

Answer

Increase the DAG's schedule interval and add a max_active_runs limit of one so overlapping runs cannot occur.

Question 8

A financial services company runs a Dataflow streaming pipeline that reads from Pub/Sub and writes enriched records to BigQuery. Compliance requires that the raw Pub/Sub messages be retained for seven years so that any record can be reprocessed if the enrichment logic is later found to be incorrect. The pipeline currently has no archival step. What should the data engineer do to satisfy the retention requirement with the least operational overhead?

Accepted Answer

Add a Cloud Storage sink to the Dataflow pipeline that writes each raw message to a Cloud Storage bucket with a lifecycle policy moving objects to Coldline or Archive storage after 30 days.. Retaining raw messages for years requires a durable, low-cost object store, and Cloud Storage with lifecycle-based class transitions is the standard fit on Google Cloud. By branching the existing Dataflow pipeline to write unmodified messages to a bucket, the company keeps a complete, reprocessable archive without building a separate ingestion path or managing long-lived Pub/Sub backlogs.

Answer

Configure a Pub/Sub dead-letter topic and set the maximum delivery attempts high enough that failed messages persist for the retention period.

Answer

Enable BigQuery table snapshots on the destination table and set a seven-year expiration on each snapshot.

Answer

Increase the Pub/Sub topic message retention duration to the maximum allowed and rely on the subscription backlog for seven years.

Question 9

You are using Cloud Composer to orchestrate a data pipeline that runs a Dataproc job to process data, followed by a BigQuery load. You notice that the Dataproc job sometimes takes longer than expected, causing the BigQuery load to start before the Dataproc job finishes, resulting in incomplete data. Which Airflow feature should you use to ensure the BigQuery load only runs after the Dataproc job completes successfully?

Accepted Answer

Set a dependency between the Dataproc job task and the BigQuery load task using the >> operator.. The correct way to enforce that the BigQuery load runs only after the Dataproc job completes successfully is to define a task dependency using the >> operator. This creates a directed edge in the DAG, ensuring the downstream task waits for the upstream task to succeed. Other options either do not control ordering or do not enforce success. Task dependencies are the core mechanism in Airflow for sequencing tasks in a data pipeline.

Answer

Use a TimeSensor to wait for the Dataproc job to finish.

Answer

Use the trigger_rule parameter to set the BigQuery load task to 'all_done'.

Answer

Set the Dataproc job task's retries to a high number.

Question 10

Your company uses Cloud Composer to run a daily ETL workflow. The workflow consists of several tasks that must run in a specific order. You want to receive an alert if any task fails. Which Cloud Monitoring feature should you use?

Accepted Answer

Create an alerting policy based on the Cloud Composer metric for task failures.. The recommended way to alert on task failures in Cloud Composer is to use Cloud Monitoring alerting policies based on the built-in Cloud Composer metrics for task failures. These metrics are automatically exported and can be used to trigger alerts when failures occur. This approach is native, scalable, and integrates with various notification channels. Other methods, such as log-based metrics or polling, are either redundant or less efficient.

Answer

Set up a Cloud Function that checks the Airflow UI periodically.

Answer

Configure Airflow email alerts in the DAG's default_args.

Answer

Use Cloud Logging to create a log-based metric for task failures.

Question 11

A media company runs a Cloud Composer environment whose DAGs trigger Dataflow batch jobs and BigQuery loads. The operations team reports that Composer costs are rising and that DAGs occasionally stall because workers are saturated. You review the environment and find that several tasks are long-running sensors that hold worker slots while waiting on external conditions. Which TWO changes should you make to reduce worker saturation and cost? (Choose two.)

Accepted Answer

Move the long-running Dataflow job monitoring out of the DAG by having the DAG submit the job and exit while a separate process tracks completion.. Worker saturation caused by long-blocking sensors and job-waiting tasks is best relieved by changing how those tasks consume resources rather than by adding concurrency limits or rescheduling. Deferrable sensors release slots during waits, and decoupling long Dataflow job monitoring from the worker keeps the pool available for other tasks, which lowers both stalls and cost.

Answer

Change the DAG schedule from a cron expression to a timedelta interval so runs are spaced further apart.

Answer

Set the Composer environment's worker count to the minimum and rely on autoscaling to add workers only when the queue is deep.

Answer

Increase the DAG's max_active_tasks parameter so more tasks can run in parallel on the same workers.

Question 12

A data engineer needs to run a recurring nightly extract-transform-load job that pulls data from a REST API, applies Python transformations, and writes the output to a Cloud Storage bucket. The team wants a fully managed, serverless scheduler that can retry failed runs and send notifications, and they do not want to maintain any cluster or VM. Which Google Cloud service should they use to define and run this job?

Accepted Answer

Cloud Scheduler triggering a Cloud Function that performs the API call, transformation, and Cloud Storage write.. Cloud Scheduler plus Cloud Functions delivers a fully managed, serverless combination for a recurring ETL task. Cloud Scheduler handles the cron cadence and can target a function over HTTP or Pub/Sub, while Cloud Functions executes the Python code with automatic retries, integrated logging, and the ability to publish notifications. No cluster or VM is required, satisfying the operational constraints.

Answer

A Compute Engine instance running a cron job that executes a Python script and writes to Cloud Storage.

Answer

Cloud Composer with a single-node environment and a KubernetesPodOperator that runs the Python transformation.

Answer

Dataproc Serverless with a PySpark batch that calls the REST API and writes results to Cloud Storage.

Question 13

A healthcare analytics team must store patient records in BigQuery and prove to auditors that no one can read or modify the data without an explicit, logged authorization. They want to enforce column-level access so that only the billing team can see insurance_id, while the research team sees only de-identified columns. Which BigQuery feature should they implement?

Accepted Answer

Policy tags in a data taxonomy applied to the insurance_id column, with column-level access granted to the billing team.. Policy tags in a data taxonomy provide column-level access control in BigQuery. Applying a policy tag to insurance_id and granting the fine-grained reader role only to the billing team ensures the research team cannot query that column even though they can access the table. BigQuery logs policy tag enforcement, supporting the auditor's requirement for explicit, logged authorization. Other features address rows, encryption, or views but not column-level authorization.

Answer

Authorized views that join the patient table with a lookup table to mask insurance_id.

Answer

Row-level security policies that filter rows based on the SESSION_USER() function.

Answer

Customer-managed encryption keys stored in Cloud KMS and rotated every 90 days.

Question 14

A financial services firm stores customer transaction data in BigQuery. Compliance requires that a nightly Cloud Composer DAG verify that the previous day's partition is complete before downstream reporting DAGs run, and that the reporting DAG never start if the verification fails. The engineer wants the dependency expressed inside orchestration rather than by polling from the reporting DAG. What should the engineer do?

Accepted Answer

Use a Dataset (Dataset-scheduled DAG) in Cloud Composer: have the verification DAG produce the dataset and schedule the reporting DAG to trigger on it.. Airflow datasets in Cloud Composer create a producer-consumer dependency where the verification DAG's successful completion updates a dataset and the reporting DAG is scheduled on that update. This keeps the dependency inside orchestration, avoids polling, and inherently prevents the reporting DAG from running when verification fails.

Answer

Create a Cloud Scheduler job that calls the reporting DAG's trigger endpoint only after a Cloud Function confirms verification succeeded.

Answer

Combine the verification and reporting logic into a single DAG so that task order enforces the dependency.

Answer

Have the reporting DAG poll the verification table in a loop with a sensor until a success row appears, then proceed.

Question 15

Which BigQuery SQL function returns the rank of a row within a window, with gaps in the ranking for ties?

Accepted Answer

RANK(). The RANK() function assigns a rank to each row within a window, and when there are ties, it leaves gaps in the ranking sequence. For example, if two rows tie for rank 1, the next row receives rank 3. This behavior distinguishes it from DENSE_RANK(), which does not leave gaps. RANK() is part of BigQuery's window functions and is used with an OVER clause specifying the partitioning and ordering.

Answer

NTILE()

Answer

DENSE_RANK()

Answer

ROW_NUMBER()