GCP-ADP Data Analysis And Presentation — 30 Questions

Question 1

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.. Table calculations allow for creating new measures based on existing data in the Explore interface without affecting the underlying database model.

Answer

BigQuery query parameters.

Answer

LookML derived tables with persistent caching.

Answer

Database-level User Defined Functions (UDFs).

Question 2

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.. Operations like ORDER BY, JOIN, or GROUP BY on large datasets without efficient distribution or filtering often cause spills to disk when memory limits are exceeded.

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.

Question 3

A data scientist is using Vertex AI Workbench and needs to share a notebook with a team member who only needs read access. What is the most secure way to do this?

Accepted Answer

Grant the user 'Storage Object Viewer' role on the Cloud Storage bucket containing the .ipynb file.. Storing the notebook in Cloud Storage and using IAM permissions is the standard way to share notebook artifacts.

Answer

Add the user as a 'Vertex AI User' on your project.

Answer

Copy the notebook code into an email.

Answer

Change the instance permissions to 'Public'.

Question 4

Your team wants to visualize BigQuery data in Looker Studio. What is the most efficient way to ensure the dashboard reflects fresh data without manual refreshes?

Accepted Answer

Configure the data source to use 'Direct Query'.. Looker Studio supports query caching settings, but for live data, using the 'Direct Query' connection method is best.

Answer

Export the data to Google Sheets every hour.

Answer

Enable 'Automatic Refresh' in the Looker Studio browser extension.

Answer

Use a scheduled extract refresh.

Question 5

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).. Window functions, specifically RANK() or DENSE_RANK() combined with PARTITION BY, are designed for this exact use case.

Answer

GROUP BY region, product.

Answer

SELECT TOP 5 * FROM sales.

Answer

SUM(sales) OVER (ORDER BY region).

Question 6

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 enables the manager to create visualizations dynamically while ensuring they always see the latest data from BigQuery.

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 7

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.. Notebook instances are compute resources; the persistent disk attached to the instance or an external Cloud Storage bucket is required to keep data.

Answer

In the root directory of the container image.

Answer

In the local /tmp folder.

Answer

In the system memory.

Question 8

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.. Requiring partition filters forces users to include a WHERE clause on the partitioning column, preventing full table scans.

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 9

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 standard statistical visualization for showing the distribution of numerical data.

Answer

Line chart.

Answer

Pie chart.

Answer

Scatter plot.

Question 10

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 fields are defined but not included in the Explore via the 'fields' parameter or 'join' syntax, they will not be visible.

Answer

Check the project's 'BigQuery' location.

Answer

Check if the table is indexed.

Answer

Clear the browser cache.

Question 11

When optimizing a Looker model, you want to prevent users from accidentally running very expensive queries. Which parameter should you use in LookML?

Accepted Answer

sql_always_where. The 'sql_always_where' parameter forces a filter on every query, which is an effective guardrail for cost and performance.

Answer

query_limit

Answer

access_filter

Answer

always_filter

Question 12

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 by `customer_id` organizes the data physically to optimize queries that filter by that specific column.

Answer

Add a primary key constraint.

Answer

Convert the table to a View.

Answer

Increase the number of slots in the reservation.

Question 13

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 the standard chart for observing the correlation or relationship between two numeric axes.

Answer

Donut chart.

Answer

Table chart.

Answer

Area chart.

Question 14

You are writing a Python script in Vertex AI Workbench. You want to query BigQuery and load the result into a pandas DataFrame. Which library should you use?

Accepted Answer

google-cloud-bigquery. The `google-cloud-bigquery` library contains the `to_dataframe()` method which is specifically designed for this.

Answer

requests

Answer

pandas-gbq

Answer

sqlalchemy

Question 15

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' allows end users to filter the entire report by a specific timeframe.

Answer

Parameter.

Answer

Calculated field.

Answer

Chart interaction filter.

Question 16

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. Standard SQL uses the dot notation (dot-walk) to access fields within a STRUCT.

Answer

address->city

Answer

address[city]

Answer

JSON_EXTRACT(address, '$.city')

Question 17

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 Jupyter-based environment, the `ls` command (prefix with % or ! if in a cell) lists files in the current folder.

Answer

find .

Answer

dir

Answer

list_files()

Question 18

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.. Cross-joins on massive datasets are the leading cause of query failure; they should be avoided in favor of JOIN ON or other set-based operations.

Answer

Use a wildcard table.

Answer

Cache the results in BigQuery.

Answer

Increase the number of slots.

Question 19

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.. Single Value visualizations in Looker allow for displaying an aggregated metric clearly.

Answer

Treemap.

Answer

Data table.

Answer

Waterfall chart.

Question 20

A user wants to see the top 10 products sold, but also an 'Other' category for the rest. How can you achieve this in a Looker Explore?

Accepted Answer

Create a dimension using a 'CASE' statement to group items.. A 'Dimension Fill' or using a 'Case' statement in a dimension allows grouping everything else into an 'Other' bucket.

Answer

Change the sort order.

Answer

Use a table calculation.

Answer

Filter the report by 'Top 10'.