Google Cloud Professional Data Engineer Exam

To compute values across a group of rows and return a unique result for each row in BigQuery, which method should be used?

1. Use an aggregate function.

2. Use a UDF (user-defined function).

3. Use BigQuery ML.

4. Use a window function with an OVER clause.

The correct answer is: Use a window function with an OVER clause.

Using a window function with an OVER clause is the appropriate method to compute values across a group of rows while returning unique results for each row in BigQuery. Window functions allow you to perform calculations across a defined range of rows, known as the "window", without collapsing the result set into a single row for each group. Each row retains its identity in the output, which is particularly beneficial when you need calculations like running totals, moving averages, or rankings where each row's context within its defined partition needs to be kept intact. For example, if you want to calculate a running total of sales for each product category without grouping all the rows into a single summary row, you can do this effectively with a window function. The OVER clause specifies the partition of rows to which the function will be applied, enabling row-wise calculations while keeping each individual record in the result set. Aggregate functions, while useful for summarizing data into a single result per group, do not return unique results for each row and would group multiple rows together, which is not desired in this scenario. User-defined functions (UDFs) can perform similar calculations but may not be as efficient or straightforward as built-in window functions for standard aggregations across rows. BigQuery ML is focused on