Google Professional Data Engineer Question 74
Single answerGoogle Cloud PlatformYou are working on a data pipeline in Google Cloud and need to transform raw sales data stored in BigQuery. The transformation requires calculating the total revenue for each product category and ensuring that null values in the 'price' column are replaced with 0 before aggregation. Which approach should you take to define the transformation logic?
- A
Use a SQL query in BigQuery with a COALESCE function to handle null values and a GROUP BY clause to aggregate revenue by product category.
- B
Export the data to a local machine, process it using Python, and then upload the transformed data back to BigQuery.
- C
Set up a Dataflow pipeline with a ParDo transformation to handle null values and a GroupByKey transformation to aggregate revenue.
- D
Use Cloud Dataprep to visually clean the data by replacing null values and aggregating revenue.
Show answer and explanation
Correct answer: A
Explanation
BigQuery provides built-in capabilities for defining transformation logic with SQL. By using the COALESCE function to handle null values and the GROUP BY clause for aggregation, you can efficiently process the data within the same environment, avoiding unnecessary complexity and cost. Other options either involve moving data unnecessarily or using tools that are not the most efficient for this specific task.
- A. Correct.
This is the correct answer. A SQL query in BigQuery can efficiently handle null values using the COALESCE function and perform aggregation using the GROUP BY clause. This approach is cloud-native and cost-effective.
- B. Incorrect.
This is not the correct answer. Exporting and processing data locally introduces unnecessary data movement, increases complexity, and does not leverage the scalability of Google Cloud's services.
- C. Incorrect.
While Dataflow can handle the task, it is more suited for streaming or complex transformations. For a simple aggregation like this, BigQuery SQL is more appropriate and efficient.
- D. Incorrect.
Cloud Dataprep is a viable option for data cleaning but is not the best choice for aggregation tasks. It is better suited for exploratory data preparation and visual transformations.