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

Question 1

You are creating a Looker Studio report that must allow users to interactively explore sales data. The report should include controls that let users filter data by product category and date range. Which two controls should you add? (Choose two.)

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

Accepted Answer

A date range control.. A filter control on the product category dimension and a date range control are the standard Looker Studio controls for interactive filtering by category and time. They apply to all charts on the page, enabling users to dynamically explore sales data. The other options either do not provide filtering or are not interactive.

Answer

A parameter control to adjust the date granularity.

Answer

A chart-level filter on each chart for product category.

Answer

A data control to switch between different data sources.

Question 2

A data analyst has a BigQuery table named `sales` with columns `order_date` (DATE), `product_id` (STRING), and `revenue` (NUMERIC). They need to create a report that shows total revenue per product for the last 30 days, but only for products that generated more than $10,000 in that period. They want the result sorted by revenue descending. Which SQL query should they use?

Accepted Answer

SELECT product_id, SUM(revenue) AS total_revenue FROM sales WHERE order_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY) GROUP BY product_id HAVING total_revenue > 10000 ORDER BY total_revenue DESC. The correct query filters the last 30 days, groups by product, sums revenue, and then applies a HAVING clause to keep only products with total revenue above $10,000, finally sorting by that total descending. Using WHERE to filter individual revenues would incorrectly exclude products with many smaller sales, and placing HAVING after ORDER BY or ordering by a non-aggregated column would cause errors.

Answer

SELECT product_id, SUM(revenue) AS total_revenue FROM sales WHERE order_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY) AND revenue > 10000 GROUP BY product_id ORDER BY total_revenue DESC

Answer

SELECT product_id, SUM(revenue) AS total_revenue FROM sales WHERE order_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY) GROUP BY product_id ORDER BY total_revenue DESC HAVING total_revenue > 10000

Answer

SELECT product_id, SUM(revenue) AS total_revenue FROM sales WHERE order_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY) GROUP BY product_id HAVING SUM(revenue) > 10000 ORDER BY revenue DESC

Question 3

A data analyst is building a Looker Studio report connected to a BigQuery table. The report needs to display data for the last 30 days, but the underlying table is updated daily at midnight. Which approach ensures the report always shows the most recent 30 days without manual intervention?

Accepted Answer

Create a date range dimension in the report and set a default date range of 'Last 30 days'.. The requirement is for a report that dynamically shows the last 30 days without manual intervention. Looker Studio's date range dimension feature allows setting a default range like 'Last 30 days', which recalculates based on the current date each time the report is viewed. This leverages built-in functionality and ensures the report stays current as the BigQuery table updates daily.

Answer

Add a filter to the report that explicitly selects the start and end dates for the current month.

Answer

Use a BigQuery scheduled query to materialize the last 30 days into a new table and connect the report to that table.

Answer

Create a calculated field that subtracts 30 days from the current date and use it as a filter.

Question 4

A data engineer is building a Looker Studio report connected to a BigQuery table. The report must allow viewers to dynamically change the date range and filter data by region without editing the report. The engineer wants to add interactive controls. Which two components should be added to the report to meet these requirements? (Choose two.)

Accepted Answer

Drop-down list control. To enable viewers to change the date range and filter by region without editing the report, interactive controls are needed. A date range control provides a calendar picker for date filtering, and a drop-down list control allows selection of dimension values like region. Calculated fields, scorecards, and data blending are not interactive controls and do not fulfill the dynamic filtering requirements.

Answer

Calculated field

Answer

Scorecard with conditional formatting

Answer

Data blending

Question 5

A data engineer needs to create a visualization in Looker Studio that shows the relationship between advertising spend and revenue for each marketing campaign. They want to quickly identify which campaigns have high spend but low revenue. Which chart type is most appropriate?

Accepted Answer

Scatter chart with advertising spend on the X-axis and revenue on the Y-axis, with each point representing a campaign.. To identify campaigns with high spend but low revenue, the analyst needs to see the correlation between the two metrics across campaigns. A scatter chart is designed for this purpose, plotting each campaign as a point based on its spend and revenue values. This visual format makes it easy to spot outliers that fall outside the expected pattern, such as those with high spend and low revenue.

Answer

Time series chart with both advertising spend and revenue as metrics over time.

Answer

Geo chart with campaign locations and revenue as the metric.

Answer

Bar chart with campaigns on the X-axis and revenue on the Y-axis, sorted by revenue descending.

Question 6

A data analyst is working in BigQuery and needs to create a visualization that shows the relationship between advertising spend and revenue for each day over the past month. The data is in a table with columns `date`, `ad_spend`, and `revenue`. They want to quickly identify if there is a correlation. Which type of chart should they use in Looker Studio?

Accepted Answer

Scatter chart. A scatter chart is designed to display the relationship between two numerical variables by plotting each data point according to its values on both axes. In this scenario, plotting ad spend on one axis and revenue on the other allows the analyst to visually assess correlation and outliers. Time series, bar, and pie charts do not serve this purpose.

Answer

Time series chart

Answer

Pie chart

Answer

Bar chart

Question 7

A retail analytics team stores transaction data in a BigQuery table named `sales.transactions` with columns `order_id`, `customer_id`, `order_ts`, and `amount`. A Looker Studio report uses this table as its data source. The team wants every report viewer to see only rows where `amount` is greater than 0, without modifying the underlying BigQuery table. Where should they enforce this filter so it applies to the entire report regardless of which chart a viewer interacts with?

Accepted Answer

Apply the filter at the data source level in Looker Studio.. Enforcing the condition at the data source means every chart built on that source inherits the filter, so all viewers see only rows with amount greater than 0 without touching the BigQuery table. Chart filters are scoped too narrowly, and viewer-editable controls can be changed, so neither enforces the rule consistently.

Answer

Add a report-level filter that viewers can edit from the toolbar.

Answer

Add a filter to each individual chart on the report page.

Answer

Create a BigQuery authorized view that filters the rows and repoint the report.

Question 8

You are analyzing a BigQuery table of e-commerce orders and want to identify the top 5 product categories by total revenue for each month in 2024. You need to produce a result set with columns `order_month`, `category`, and `total_revenue`. Which SQL technique should you use?

Accepted Answer

Use a GROUP BY on order_month and category, then a window function RANK() OVER (PARTITION BY order_month ORDER BY SUM(revenue) DESC) to filter top 5.. To get top N per group, you need to aggregate first, then rank within each group. A GROUP BY on month and category computes total revenue, and a window function like RANK() partitioned by month orders categories by revenue within each month. Filtering on the rank gives the top 5 per month. This is the correct and efficient pattern in BigQuery.

Answer

Use a CROSS JOIN with a calendar table and then filter with a WHERE clause on revenue.

Answer

Use a GROUP BY on order_month and category, then a LIMIT 5 clause at the end of the query.

Answer

Use the APPROX_TOP_COUNT function on category partitioned by month.

Question 9

A data analyst is using Looker Studio to create a dashboard for a sales team. They want to enable users to interact with the data and explore different dimensions without leaving the dashboard. Which two features should they add to the report? (Choose two.)

Accepted Answer

A date range control that lets users choose a custom time period.. To allow users to interact with the dashboard and explore data, the analyst should add filter controls and date range controls. These are standard Looker Studio components that let viewers adjust the data displayed without editing the report. Filter controls for dimensions like region and date range controls for time periods directly empower users to customize their view, making the dashboard more interactive and useful for the sales team.

Answer

A calculated field that computes profit margin for each sale.

Answer

A scheduled email delivery of the report to the sales team.

Answer

A data source with a parameter that allows switching between different tables.

Question 10

A retail analytics team stores point-of-sale transactions in a BigQuery table named `sales.transactions` that is partitioned by a DATE column called `transaction_date`. An analyst must produce a report showing only the transactions from the previous calendar month, and the report will be refreshed on the first day of every month. Which query pattern most efficiently retrieves the required data while minimizing bytes processed?

Accepted Answer

SELECT * FROM sales.transactions WHERE transaction_date >= DATE_TRUNC(DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH), MONTH) AND transaction_date < DATE_TRUNC(CURRENT_DATE(), MONTH). Filtering a partitioned table with a range comparison on the partition column lets BigQuery scan only the partitions that satisfy the predicate. Computing the previous month's first day and the current month's first day as constant bounds produces an exact calendar-month window and enables partition pruning, keeping bytes processed low. Functions applied to the partition column or rolling date arithmetic break pruning or return the wrong period.

Answer

SELECT * FROM sales.transactions WHERE transaction_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH) AND CURRENT_DATE()

Answer

SELECT * FROM sales.transactions WHERE EXTRACT(MONTH FROM transaction_date) = EXTRACT(MONTH FROM DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH))

Answer

SELECT * FROM sales.transactions WHERE transaction_date = DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH)

Question 11

You are building a Looker Studio report connected to a BigQuery table that contains billions of rows. The report includes several charts and filters. Users report that the report is slow to load and that changing a filter takes a long time. Which TWO actions should you take to improve performance? (Choose two.)

Accepted Answer

Enable BigQuery BI Engine and reserve memory for the dataset.. For large BigQuery datasets, slow Looker Studio performance is best addressed by reducing query latency and data scanned. BigQuery BI Engine provides in-memory acceleration for interactive queries, while a materialized view pre-aggregates data to minimize processing. Both directly improve chart load and filter responsiveness. Other options either do not address query speed or are impractical for billions of rows.

Answer

Increase the data freshness setting to 24 hours to reduce the number of queries.

Answer

Extract the data into a Looker Studio extract and schedule daily refreshes.

Answer

Add more filters to the report to narrow down the data before charts load.

Question 12

A data analyst has a Looker Studio report connected to a BigQuery table containing millions of rows. The report includes a table chart that shows aggregated sales by product category. Users complain that the report takes a long time to load. Which action should the analyst take to improve performance without changing the report's layout?

Accepted Answer

Create a materialized view in BigQuery that pre-aggregates the sales data by product category and connect the report to that view.. The report is slow because the table chart queries a large BigQuery table. To improve performance without altering the layout, the analyst should reduce the amount of data processed by the query. Creating a materialized view that pre-aggregates the data by product category achieves this, as the report then queries a smaller, pre-computed dataset. This maintains the same visual output while drastically improving load times.

Answer

Enable the 'Aggregate data' option in the table chart settings.

Answer

Increase the number of rows displayed in the table chart to show more data at once.

Answer

Add a filter to limit the data to the last 30 days.

Question 13

A financial analyst needs a Looker Studio report that displays the same revenue chart but filtered by a different region on each of four pages, without duplicating the underlying BigQuery data source. The report currently uses one chart that reads from a BigQuery view. What should the analyst do to reuse the chart across pages with different region filters?

Accepted Answer

Create a reusable chart as a component in the report's component library, then place it on each page and bind a page-specific region filter.. Reusable components let a single chart definition be placed on several pages while each placement keeps its own page-level filter controls. Because the component stays bound to the same BigQuery data source, revenue logic is maintained once and each page can target a different region. Duplicating views or relying on report-level filters either multiplies maintenance or forces one filter across all pages.

Answer

Create four separate BigQuery views, one per region, and add a chart for each view on its own page.

Answer

Add a report-level filter for region and let each page inherit it, then rely on page-level filter overrides.

Answer

Use a blend between the BigQuery view and a manually entered region parameter table, then add a parameter control per page.

Question 14

You have a Looker Studio report that uses a BigQuery data source. You need to add a chart that shows the total number of orders per day for the last 30 days. The date field is named `order_date`. Which chart type and dimension setup should you use?

Accepted Answer

A time series chart with `order_date` as the dimension and a record count metric.. To visualize a daily trend, a time series chart is the correct choice. It uses a date dimension on the x-axis and a metric on the y-axis, automatically handling date granularity and ranges. A record count metric counts the number of orders per day. Other chart types either do not show trends or are unsuited for time-based data.

Answer

A bar chart with `order_date` as the dimension and a record count metric.

Answer

A scorecard with `order_date` as the dimension and a record count metric.

Answer

A pie chart with `order_date` as the dimension and a record count metric.

Question 15

A junior analyst has written a BigQuery SQL statement that joins an orders table to a customers table and then aggregates total spend per customer. Before running it against production data, the analyst wants to confirm the query is syntactically valid and to see estimated bytes processed without incurring charges for scanning the full tables. What should the analyst do?

Accepted Answer

Run the query with the BigQuery dry run option enabled to validate syntax and view the estimated bytes processed.. A dry run in BigQuery performs full SQL validation, including table and column resolution, and returns the estimated bytes processed without executing the statement or billing for data scanned. That makes it the appropriate pre-flight check for syntax and cost. Sampling data, inspecting job history, or building a materialized view all require execution or answer a different question.

Answer

Query the INFORMATION_SCHEMA.JOBS view to inspect prior executions of similar queries and their bytes billed.

Answer

Create a materialized view over the join and inspect its refresh statistics before querying it.

Answer

Run the query against a small sample table created with SELECT * LIMIT 100 to approximate the result.