GCP Professional Data Engineer Practice Question

An e-commerce company uses BigQuery Data Transfer Service to land daily snapshots of its OLTP tables into raw staging datasets in BigQuery. Analysts must generate a curated star-schema each night that (1) standardizes all monetary columns to a common currency, (2) joins fact rows with multiple dimension tables, and (3) calculates daily revenue aggregates so that reports are ready by 06:00 each morning. The analysts want to maintain the transformation logic themselves using only SQL, and the data engineering team wants to minimize operational overhead. Which approach best satisfies these requirements?

Develop an Apache Beam batch pipeline on Dataflow, triggered daily by Cloud Scheduler, to read the raw BigQuery tables, perform the joins and aggregations in Java, and write the results back.
Implement an ELT workflow by writing BigQuery SQL that converts currency values, joins staging tables into star-schema tables, and computes daily aggregates; use BigQuery Scheduled Queries to run the statements before 06:00.
Nightly export the raw snapshots to Cloud Storage, use Cloud Dataprep visual pipelines to cleanse and join the data, and load the transformed files back into BigQuery.
Create a Cloud Composer DAG that launches a transient Dataproc cluster nightly to execute Spark SQL jobs transforming the raw BigQuery tables into the required star-schema.

Report Issue

Answer Description

Using BigQuery's native ELT pattern keeps all data inside the warehouse and lets analysts express cleansing, joins, and aggregations in familiar SQL. BigQuery is fully managed and serverless, so there is no infrastructure to provision or maintain, satisfying the low-operations goal. Scheduled Queries can invoke the SQL statements on a daily timetable to meet the 06:00 deadline and populate partitioned fact and dimension tables. The other options add unnecessary complexity: spinning up Dataproc clusters or managing Dataflow code increases operational burden, and exporting to Cloud Storage for Dataprep introduces extra data movement and may limit complex multi-table joins.

Ask Bash

Bash is our AI bot, trained to help you pass your exam. AI Generated Content may display inaccurate information, always double-check anything important.