GCP-ADP Data Analysis And Presentation Practice Test 2 — 15 Questions

Question 1

A data analyst needs to present a summary of last quarter's sales performance to executives. They have a BigQuery table with sales data and want to create a simple, interactive chart that updates automatically. Which Google Cloud tool should they use?

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

Accepted Answer

Looker Studio. Looker Studio provides a user-friendly interface to build interactive reports and dashboards. It connects natively to BigQuery, supports automatic data refresh, and allows sharing with stakeholders. For executive summaries, it offers various chart types and filters, making it the appropriate choice.

Answer

BigQuery BI Engine

Answer

Data Studio (now Looker Studio)

Answer

Google Sheets

Question 2

Your team maintains a Looker Studio report connected to a BigQuery dataset. A stakeholder wants to see month-over-month revenue growth as a percentage, but the raw data only contains daily revenue values. You need to add a calculated field to the report. Which approach should you use?

Accepted Answer

Blend the BigQuery data source with itself, joining on a date offset of one month, then calculate the percentage change in a calculated field.. Looker Studio does not have built-in functions for period-over-period comparisons like some BI tools. The reliable approach is to blend the data source with itself, shifting the date by one month, so that each row contains both current and previous period values. Then a calculated field can compute the percentage change. This method works with any data source and avoids unsupported syntax.

Answer

Create a calculated field using the formula (SUM(Revenue) - SUM(Revenue)) / SUM(Revenue) with a date range dimension.

Answer

Create a calculated field using the formula (SUM(Revenue) - SUM(Revenue FOR PREVIOUS MONTH)) / SUM(Revenue FOR PREVIOUS MONTH).

Answer

Use a date range dimension and the PARALLEL_PERIOD function to compare current revenue to the previous month's revenue.

Question 3

A data analyst has a Looker Studio report connected to a BigQuery table that contains millions of rows. The report has a chart that shows total sales by country. The analyst notices that the chart takes a long time to load. They want to improve performance. What should they do first?

Accepted Answer

Create a BigQuery view that pre-aggregates the data by country and use that view as the data source.. The most effective first step is to reduce the data volume processed by Looker Studio. Pre-aggregating in BigQuery via a view or materialized table minimizes rows returned, which speeds up chart rendering. Increasing rows, restricting dates (which alters the report), or changing chart type do not solve the root cause of large data transfer.

Answer

Add a filter to the chart to limit the date range to the last 7 days.

Answer

Increase the number of rows displayed in the chart settings.

Answer

Switch the chart type from a bar chart to a pie chart.

Question 4

An analyst is building a Looker Studio report connected to a BigQuery table. The report contains a scorecard showing total revenue and a time series chart showing daily revenue. A stakeholder reports that the scorecard total does not match the sum of the daily values in the time series. Both charts use the same data source and no filters are applied. What is the most likely cause?

Accepted Answer

The scorecard uses a different aggregation than SUM, such as AVG, on the revenue field.. When two charts share a data source and filters, a discrepancy between a single total and the sum of a time series usually comes from how the metric is aggregated. A scorecard configured with an average or count rather than sum produces a number that will not equal the summed daily values.

Answer

The time series chart is limited to a default date range that excludes some days.

Answer

BigQuery applies eventual consistency to table reads, so totals lag.

Answer

Looker Studio samples the data for time series charts, dropping rows.

Question 5

A data analyst is creating a Looker Studio report that connects to a BigQuery dataset. They want to ensure the report performs well and displays data efficiently. Which two actions should they take to optimize the report's performance? (Choose two.)

Accepted Answer

Limit the number of dimensions and measures in charts to only those needed.. Enabling BI Engine accelerates queries by caching data in memory, significantly improving performance for Looker Studio reports connected to BigQuery. Limiting the number of dimensions and measures reduces the data processed per query, lowering latency and cost. Together, these actions optimize report performance without compromising data freshness.

Answer

Schedule the report to refresh every hour to distribute the query load.

Answer

Set the data freshness to 1 minute to ensure real-time data.

Answer

Use extracted data sources instead of live connections.

Question 6

You are analyzing daily sales data stored in BigQuery. Your Looker Studio report currently shows total revenue by product category. The product manager now wants to see the percentage contribution of each category to total revenue, displayed as a pie chart. What should you do?

Accepted Answer

Add a calculated field that divides each category's revenue by the total revenue using a fixed total, then use that field as the metric in a pie chart.. To show percentage contribution in Looker Studio, you need a calculated field that divides each category's revenue by the overall total revenue. This ensures the denominator is consistent across categories. Using a calculated field keeps the calculation within Looker Studio, enabling dynamic updates. The other options either rely on non-existent features, manual work, or unnecessary database objects.

Answer

Change the chart type to a pie chart and set the metric to 'Revenue' with the aggregation method 'Percent of total'.

Answer

Create a new report with a table showing revenue by category, then manually calculate the percentages in a spreadsheet and import them back as a data source.

Answer

Use a BigQuery SQL query with a window function to calculate the percentages, then create a new view and connect Looker Studio to that view.

Question 7

You are building a Looker Studio report that pulls data from BigQuery. The report will be used by executives who need to see key metrics updated near real-time. You want to optimize query performance and reduce cost. Which two actions should you take? (Choose two.)

Accepted Answer

Enable BigQuery BI Engine and reserve capacity for the dataset used by the report.. To optimize performance and cost for a near real-time executive dashboard, use BigQuery BI Engine to accelerate queries in memory, and create a materialized view with a frequent refresh schedule to pre-aggregate data. These reduce data scanned and latency. Live Connection increases cost, Storage API is not applicable, and long caching conflicts with freshness requirements.

Answer

Use the BigQuery Storage API to read data directly into Looker Studio.

Answer

Increase the Looker Studio cache duration to 12 hours to reduce the number of queries.

Answer

Set the Looker Studio data source to use the 'Live Connection' mode instead of 'Extract' mode.

Question 8

A data analyst needs to create a report in Looker Studio that combines data from BigQuery and Google Sheets. The BigQuery table contains sales transactions, and the Google Sheets file contains a mapping of product IDs to product names. What is the recommended way to join these two data sources in Looker Studio?

Accepted Answer

Use the 'Blend data' feature to join the BigQuery and Google Sheets data sources on the product ID field.. The recommended way to join BigQuery and Google Sheets data in Looker Studio is to use the 'Blend data' feature. This allows you to combine multiple data sources by defining a join key, such as product ID. Blending is native to Looker Studio, supports various join types, and does not require data movement or external scripts. The other options involve unnecessary data duplication or unsupported functions.

Answer

Use Google Apps Script to periodically copy the Google Sheets data into a BigQuery table, then join the tables in BigQuery and connect Looker Studio to the result.

Answer

Export the Google Sheets data to BigQuery, then create a new BigQuery table that joins the two tables, and connect Looker Studio to that table.

Answer

Create a calculated field in Looker Studio that uses a VLOOKUP function to match product IDs between the two sources.

Question 9

A data analyst runs a query in BigQuery that joins a large fact table with a small dimension table. The query is slow and scans a large amount of data. The dimension table is only a few megabytes. What is the most efficient way to improve the query performance?

Accepted Answer

Use an INNER JOIN with the dimension table as the right table and enable the 'broadcast join' optimization by ensuring the dimension table is small enough.. BigQuery's query optimizer can broadcast a small table to all nodes, avoiding a shuffle of the large fact table. This is automatic when one side of the join is small enough. Ensuring the dimension table is small and using an INNER JOIN allows the optimizer to choose a broadcast join, which is the most efficient for this scenario. Cross joins, extra filters, or clustering alone are less effective.

Answer

Add a filter on the dimension table to reduce its size before the join.

Answer

Create a clustered table on the join key of the fact table.

Answer

Use a CROSS JOIN instead of an INNER JOIN.

Question 10

You have a Looker Studio report that uses a BigQuery data source. The report contains a time series chart showing daily active users. You notice that the chart is missing data for the last two days, even though the BigQuery table is updated in near real-time. What is the most likely cause?

Accepted Answer

The Looker Studio data source is connected to a BigQuery view that filters out the most recent two days of data to avoid incomplete data.. The most likely cause is that the BigQuery view underlying the data source filters out the most recent two days. This is a deliberate design to exclude potentially incomplete data. The other options either affect all data (caching), are not specific to a two-day gap (partitioning), or are less likely given real-time updates (created_date). The view filter is a precise match for the symptom.

Answer

The time series chart is configured to use a date dimension that is based on a field that is not updated for the last two days, such as a 'created_date' field that is only populated upon record creation.

Answer

The BigQuery table uses a date partition, and the query does not include a filter for the latest partitions, so Looker Studio is not querying the newest data.

Answer

The Looker Studio data source has a caching setting that delays data freshness by up to 12 hours.

Question 11

You are building a Looker Studio report that includes a scorecard showing total revenue. The data source is a BigQuery table with millions of rows. You want the scorecard to display the total revenue for the current month, and you want it to update automatically as new data arrives. What should you do?

Accepted Answer

Create a calculated field that sums revenue where the date is in the current month, and use that field as the metric in the scorecard.. A calculated field that sums revenue for the current month ensures the scorecard always shows the current month's total without manual intervention. It dynamically evaluates the date condition each time the report loads. The other options either require manual updates, add unnecessary complexity, or do not guarantee automatic updates.

Answer

Use a BigQuery scheduled query to calculate the current month's revenue and write it to a separate table, then connect the scorecard to that table.

Answer

Apply a date range filter to the scorecard set to 'Current month' and use the revenue field as the metric.

Answer

Create a parameter that allows the user to select the month, and set the default to the current month. Use the parameter in a calculated field to filter revenue.

Question 12

You need to create a Looker Studio report that shows a time series of daily active users (DAU) from a BigQuery table. The table contains one row per user session with a timestamp. You want the report to be interactive and allow users to filter by country. Which combination of Looker Studio features should you use?

Accepted Answer

Use a time series chart with the timestamp as the dimension, a calculated field for DAU using COUNT(DISTINCT user_id), and a drop-down list control for country.. A time series chart with timestamp as the dimension displays daily trends. A calculated field using COUNT(DISTINCT user_id) correctly computes DAU. A drop-down list control for country provides interactive filtering. Other options either use the wrong chart type (scorecard, table) or an incorrect aggregation (SUM instead of COUNT DISTINCT).

Answer

Use a table chart with timestamp as a dimension, a calculated field for DAU, and a country filter.

Answer

Use a time series chart with a date range dimension, a calculated field for DAU using SUM(user_id), and a country filter.

Answer

Use a scorecard with a calculated field for DAU, and a date range control for filtering by date, plus a country filter.

Question 13

A financial analyst uses Looker Studio to monitor daily transaction volumes from a BigQuery table. The report contains a time series chart showing total transactions per day for the last 90 days. The analyst notices that the chart is slow to load and sometimes shows stale data because the underlying BigQuery table is updated multiple times per day. The analyst wants to improve performance and ensure the chart reflects the latest data. What should they do?

Accepted Answer

Create a materialized view in BigQuery that aggregates transactions per day, and use it as the data source in Looker Studio.. A materialized view in BigQuery pre-aggregates the daily transaction counts and is automatically refreshed as the base table changes. This reduces the amount of data scanned by Looker Studio, improving load time, and ensures the chart shows current data without manual intervention. It is the most efficient and reliable solution for both performance and freshness.

Answer

Enable BigQuery BI Engine and set up a reservation with sufficient memory for the dataset.

Answer

Change the Looker Studio data source to use a custom query that selects only the last 90 days of data, and set the data freshness to 15 minutes.

Answer

Schedule a daily BigQuery query to export the aggregated data to a Google Sheet, and connect Looker Studio to that sheet.

Question 14

You are building a Looker dashboard and need to calculate the year-over-year growth rate. Which feature should you use to perform this without modifying the underlying SQL?

Accepted Answer

Looker Table Calculations.. Looker Table Calculations allow you to create calculations (such as year-over-year growth) directly in the Explore or dashboard using Looker's expression language, without modifying the underlying SQL or LookML. They operate on the query results already returned, so you can compute percent change, offsets, and running totals on the fly. This makes them ideal for ad-hoc metrics like YoY growth.

Answer

BigQuery query parameters.

Answer

LookML derived tables with persistent caching.

Answer

Database-level User Defined Functions (UDFs).

Question 15

You are analyzing query performance in BigQuery. You notice that your queries are spilling to disk. What is the most likely cause?

Accepted Answer

An inefficient JOIN or GROUP BY operation causing high memory pressure.. Spilling to disk in BigQuery occurs when a query's intermediate results exceed the memory available to the execution stage. This typically happens due to inefficient JOINs or GROUP BY operations that produce large intermediate datasets, forcing BigQuery to write data to disk (shuffle) and significantly slowing performance. The correct answer directly identifies this memory pressure as the root cause.

Answer

The project is using On-Demand pricing.

Answer

The dataset is stored in a multi-region location.

Answer

Using too many slots in the reservation.