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

Question 1

You are identifying trends in a large dataset using BigQuery. Which SQL function is best suited to identify the top 5 products sold per region?

Accepted Answer

RANK() OVER (PARTITION BY region ORDER BY sales DESC).. RANK() OVER (PARTITION BY region ORDER BY sales DESC) assigns a rank to each product within each region based on sales, so filtering for rank <= 5 yields the top 5 products per region. This is the standard SQL window-function pattern for per-group top-N queries.

Answer

GROUP BY region, product.

Answer

SELECT TOP 5 * FROM sales.

Answer

SUM(sales) OVER (ORDER BY region).

Question 2

A marketing manager needs to see daily sales trends in Looker. The underlying data is in BigQuery and is updated every 6 hours. What is the best way to visualize this?

Accepted Answer

Create a Looker Explore and build a time-series line chart.. Creating a Looker Explore and building a time-series line chart is the best approach because Looker connects directly to BigQuery, supports scheduled/real-time data refresh, and provides interactive visualization tailored to trend analysis. A time-series line chart is the appropriate visualization for daily sales trends over time, and Looker's Explore lets the marketing manager self-serve without manual data exports.

Answer

Create a static PDF report sent via email.

Answer

Export BigQuery data to a CSV file.

Answer

Build a dashboard in Cloud Monitoring.

Question 3

You are using Vertex AI Workbench and want to ensure your notebook environment persists after the instance is stopped. Where should you save your work?

Accepted Answer

In a persistent disk attached to the Workbench instance.. Vertex AI Workbench instances use a persistent disk as the boot disk and for user data, so files saved outside the container image persist across stop/start cycles. Saving work in the persistent disk ensures notebooks, datasets, and environment customizations survive instance shutdown. The root directory of the container image and /tmp are ephemeral and are lost when the instance stops.

Answer

In the root directory of the container image.

Answer

In the local /tmp folder.

Answer

In the system memory.

Question 4

You need to analyze a petabyte-scale dataset in BigQuery. To optimize cost, you must ensure that users only query relevant partitions. How should you enforce this?

Accepted Answer

Enable the 'Require partition filter' option in the table settings.. BigQuery's 'Require partition filter' table option forces every query against the table to include a WHERE clause that filters on the partitioning column, preventing full-table scans and the associated on-demand costs. This is the native, enforced mechanism for ensuring users only query relevant partitions. It applies to partitioned tables and rejects queries that lack a partition predicate.

Answer

Use a materialized view for all queries.

Answer

Apply a data masking policy to the partitioning column.

Answer

Set a project-level query quota.

Question 5

A user needs to see the distribution of values in a column in BigQuery. Which visualization approach is most effective for this?

Accepted Answer

Histogram.. A histogram is the most effective visualization for showing the distribution of values in a column because it groups continuous data into bins and displays frequency counts, revealing the shape, central tendency, and spread of the data. Line charts are for trends over time, pie charts for parts of a whole, and scatter plots for relationships between two variables. Thus, histogram is the correct choice for distribution analysis.

Answer

Line chart.

Answer

Pie chart.

Answer

Scatter plot.

Question 6

You are developing a dashboard in Looker. You notice that the 'Explore' menu is missing a specific set of fields. What should you check in the LookML model?

Accepted Answer

Verify the 'fields' parameter in the Explore definition.. If the Explore menu is missing a specific set of fields in Looker, you should verify the 'fields' parameter in the Explore definition within the LookML model. This parameter explicitly lists which fields are included in the Explore, and if a field is omitted, it will not appear. Other options like BigQuery location, table indexing, or browser cache are unrelated to LookML field visibility.

Answer

Check the project's 'BigQuery' location.

Answer

Check if the table is indexed.

Answer

Clear the browser cache.

Question 7

In BigQuery, you want to improve query performance for a table that is frequently filtered by a `customer_id` column. What is the recommended strategy?

Accepted Answer

Cluster the table by 'customer_id'.. Clustering a BigQuery table by customer_id physically sorts the data by that column, so queries with filters on customer_id can prune blocks and scan far less data. This is the recommended strategy for improving filter performance on a frequently queried column. Clustering is especially effective for high-cardinality columns and works well with partitioning.

Answer

Add a primary key constraint.

Answer

Convert the table to a View.

Answer

Increase the number of slots in the reservation.

Question 8

When creating a Looker visualization, which chart type is best for showing the relationship between two numerical variables?

Accepted Answer

Scatter plot.. A scatter plot is specifically designed to display the relationship between two numerical variables by plotting each data point as a dot on a two-dimensional Cartesian plane, with one variable on the x-axis and the other on the y-axis. This allows you to visually assess correlation, clustering, outliers, and patterns such as linear or non-linear relationships. In Looker, the scatter plot chart type automatically maps two measures to the axes, making it the ideal choice for bivariate numerical analysis.

Answer

Donut chart.

Answer

Table chart.

Answer

Area chart.

Question 9

You have a dashboard in Looker Studio and you want to allow users to dynamically change the date range without editing the report. What should you add?

Accepted Answer

Date range control.. A Date range control in Looker Studio is a native chart component that renders an interactive date picker on the report canvas, letting viewers change the time window of all charts bound to that date dimension without editing the report. It is the standard, purpose-built UI element for dynamic date filtering.

Answer

Parameter.

Answer

Calculated field.

Answer

Chart interaction filter.

Question 10

A user is running a query on a BigQuery table with nested data (JSON). How do you access a field named 'city' inside a record field named 'address'?

Accepted Answer

address.city. BigQuery uses Standard SQL, which accesses nested RECORD fields using dot notation. If 'address' is a RECORD (STRUCT) column containing a 'city' field, you reference it as address.city. This is the canonical syntax for traversing STRUCT types in BigQuery.

Answer

address->city

Answer

address[city]

Answer

JSON_EXTRACT(address, '$.city')

Question 11

You are working in a Vertex AI Workbench notebook and want to list the files in your current working directory. What command should you run?

Accepted Answer

!ls. In a Vertex AI Workbench notebook (Jupyter-based), shell commands are executed by prefixing them with an exclamation mark (!). Therefore, !ls runs the Linux ls command in the notebook's underlying VM and lists the files in the current working directory. This is the standard Jupyter/IPython syntax for invoking shell commands from a code cell.

Answer

find .

Answer

dir

Answer

list_files()

Question 12

A BigQuery table has 10 billion rows. You notice a query performing a cross-join is timing out. What should you do?

Accepted Answer

Replace the cross-join with a specific JOIN condition.. A cross-join produces the Cartesian product of two tables — with 10 billion rows, even a modest second table explodes the result set and causes the query to time out. The correct fix is to replace the cross-join with a proper JOIN that includes a specific join condition (ON clause), which restricts the output to matching rows and dramatically reduces the data processed.

Answer

Use a wildcard table.

Answer

Cache the results in BigQuery.

Answer

Increase the number of slots.

Question 13

When designing a Looker dashboard, you need to display a value that is the average of a specific metric across the entire dashboard. What is the best method?

Accepted Answer

Single Value visualization.. A Single Value visualization in Looker is designed to display one aggregated number prominently, such as an average, sum, or count, and it can compute that value across the entire dashboard's underlying query. It is the standard tile for KPI-style metrics like an overall average.

Answer

Treemap.

Answer

Data table.

Answer

Waterfall chart.

Question 14

You have a Looker dashboard that loads slowly. What is the first thing you should check in the 'Looker System Activity' or 'Query History'?

Accepted Answer

Check the Query History to see execution time and SQL complexity.. When a Looker dashboard loads slowly, the first diagnostic step is to inspect Query History (or System Activity) to see which queries are slow, how long they executed, and how complex the generated SQL is. This reveals whether the bottleneck is an expensive query, a missing index, a large data scan, or a caching issue before you change any configuration.

Answer

Restart the Looker instance.

Answer

Delete all existing users.

Answer

Change the Looker instance color theme.

Question 15

You need to perform a time-series analysis in BigQuery. Your data contains timestamps at a millisecond level, but you need to aggregate by hour. What is the most performant way to truncate the timestamp?

Accepted Answer

TIMESTAMP_TRUNC(timestamp_column, HOUR).. TIMESTAMP_TRUNC(timestamp_column, HOUR) is the purpose-built BigQuery function for truncating a TIMESTAMP to a specified date part (HOUR, DAY, MONTH, etc.), returning a TIMESTAMP value that can be grouped or aggregated directly. It operates natively on the TIMESTAMP type without type conversion, so it is the most performant and semantically correct option for hourly time-series aggregation.

Answer

EXTRACT(HOUR FROM timestamp_column).

Answer

CAST(timestamp_column AS STRING).

Answer

Using a CASE statement to manually subtract milliseconds.