GCP Professional Data Engineer Practice Question

An ecommerce retailer streams millions of click-event messages into Cloud Pub/Sub. The data engineering team wants to land the raw events in BigQuery as fast as possible, run complex SQL-based enrichments and aggregations inside BigQuery, and then write a daily inventory-forecast table from BigQuery into an external PostgreSQL database that supplies the merchandising application. They want to minimize pipeline code and avoid duplicating heavy transformations outside BigQuery. Which data-movement pattern best satisfies all of these requirements?

Use a traditional ETL pipeline that transforms events in Dataflow before loading to BigQuery and also transforms the forecast table in Dataflow before inserting it into PostgreSQL.
Perform all transformations in Dataproc Spark jobs outside BigQuery, loading the pre-processed data into BigQuery and exporting the forecast to PostgreSQL via Cloud Storage files.
Implement a Reverse ETL pipeline for the click-event ingestion and use ELT to export the forecast table to PostgreSQL.
Apply an ELT pattern to ingest: stream raw events into BigQuery and transform them with SQL, then use a Reverse ETL pattern to write the resulting forecast table back to PostgreSQL.

Report Issue

Answer Description

Loading raw events first and transforming them inside BigQuery follows the ELT approach: Extract from Pub/Sub, Load to BigQuery, and then Transform with BigQuery SQL. Pushing the curated inventory-forecast table from the warehouse back into an operational PostgreSQL system is the classic use case for Reverse ETL. Together, ELT for ingestion and Reverse ETL for operational publishing minimize code, keep heavy processing inside BigQuery, and avoid unnecessary copies. Pure ETL would move transformations outside BigQuery, while using Reverse ETL for ingestion or relying solely on ETL would violate the stated constraints.

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.