GCP-ADP Data Analysis And Presentation — 40 Questions

Question 1

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.. Granting 'Storage Object Viewer' on the Cloud Storage bucket that holds the .ipynb file follows the principle of least privilege — the team member gets read-only access to exactly the notebook object and nothing else in the project. Vertex AI Workbench stores notebooks as objects in a GCS bucket, so object-level IAM is the correct, minimal scope. This avoids exposing the entire project or the instance itself.

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 2

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'.. Configuring the Looker Studio data source to use 'Direct Query' means every dashboard view queries BigQuery live, so the dashboard always reflects the latest data without manual refreshes or scheduled extracts. This is the most efficient way to ensure freshness when near-real-time data is required. It avoids the latency and storage overhead of extracts.

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 3

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 correct parameter is sql_always_where. This LookML parameter injects a WHERE clause into every SQL query generated for the Explore, which can be used to limit the data returned and prevent expensive full-table scans. It is applied at the model or Explore level and cannot be overridden by users.

Answer

query_limit

Answer

access_filter

Answer

always_filter

Question 4

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 is the official, first-party Google Cloud client library for BigQuery and provides the BigQuery client that can run SQL queries and return results directly as a pandas DataFrame via the to_dataframe() method (or QueryJob.to_dataframe()). It is the recommended library for programmatic BigQuery access from Vertex AI Workbench because it handles authentication, job management, and result pagination natively.

Answer

requests

Answer

pandas-gbq

Answer

sqlalchemy

Question 5

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.. To show the top 10 products plus an 'Other' bucket, you need a dimension that classifies products into 'Top 10' and 'Other' based on a ranking or threshold. A CASE statement in a Looker dimension can implement this logic, often combined with a table calculation or a derived table to compute ranks. This groups the long tail into a single category for visualization.

Answer

Change the sort order.

Answer

Use a table calculation.

Answer

Filter the report by 'Top 10'.

Question 6

You want to automate the export of a BigQuery table to Cloud Storage on a daily basis. What should you use?

Accepted Answer

BigQuery Data Transfer Service.. BigQuery Data Transfer Service is the managed GCP service for scheduled, recurring data movement — including exporting BigQuery tables to Cloud Storage on a schedule. You configure a transfer with a destination URI, schedule, and format, and the service handles the recurring export without custom code. This is the correct managed solution for daily automated exports.

Answer

Cloud Monitoring alerts.

Answer

Looker studio refresh.

Answer

A manual SQL query.

Question 7

Which THREE factors should you consider when choosing a partitioning strategy for a BigQuery table?

Accepted Answer

The query frequency for specific date ranges. Option C is correct because partitioning is most valuable when queries repeatedly target specific date ranges, since date/timestamp partitioning lets BigQuery prune partitions and scan only the relevant time slices, reducing bytes billed and improving performance. Option D is correct because the partition column should match the columns commonly used in WHERE filters (for example, a DATE or TIMESTAMP column), so that partition pruning can actually be applied to typical query patterns. Option E is correct because partitioning interacts directly with retention: partition expiration can drop old partitions automatically, so data expiration and retention requirements should drive whether and how you partition. Option A is not relevant because the database name has no bearing on BigQuery partitioning strategy, and Option B is not relevant because the table's color in the console is purely a UI cosmetic setting with no effect on partitioning.

Answer

The database name

Answer

The table color in the console

Question 8

You need to change the color palette of your Looker dashboard charts. Where should you make this change?

Accepted Answer

Dashboard settings.. In Looker, dashboard-level visual settings such as chart color palettes are configured directly within the dashboard's settings. This allows each dashboard to have a distinct look and feel without affecting other dashboards or the underlying data model. The dashboard settings panel provides options to customize the color palette for all charts on that dashboard, ensuring consistency and branding. This is the correct location because it scopes the change to the dashboard itself.

Answer

BigQuery project settings.

Answer

The user profile.

Answer

LookML model file.

Question 9

Which TWO types of data sources can be used to create a dashboard in Looker Studio?

Accepted Answer

BigQuery. BigQuery (D) is correct because Looker Studio provides a native BigQuery connector that lets you query tables or custom SQL directly from a Google Cloud project to build reports. Google Sheets (E) is correct because Looker Studio has a built-in Google Sheets connector that reads spreadsheet data as a live data source for dashboards. Google Cloud Functions (A) is a serverless compute service, not a data source connector in Looker Studio. Google Cloud IAM (C) is an access-management service for identities and permissions, not reportable data. Google Cloud Billing console (B) is a management UI for costs and invoices, not a Looker Studio data source type.

Answer

Google Cloud Functions

Answer

Google Cloud Billing console

Answer

Google Cloud IAM

Question 10

In LookML, which parameter is used to define how a table should join to another?

Accepted Answer

relationship. In LookML, the `relationship` parameter is used within a join definition to specify the cardinality between the current view and the joined view. It tells Looker how many rows from the base view match rows in the joined view, which is critical for generating correct SQL and avoiding fanouts. Valid values are `one_to_one`, `many_to_one`, `one_to_many`, and `many_to_many`. This parameter directly influences the join logic and aggregation behavior.

Answer

join_type

Answer

dimension_group

Answer

foreign_key

Question 11

Which TWO of the following SQL techniques in BigQuery are best for optimizing performance on large tables?

Accepted Answer

Filtering with WHERE clauses on indexed columns. Option C is correct because applying WHERE clauses lets BigQuery prune partitions and blocks before scanning, drastically reducing the bytes read on large tables; filtering on clustered or partitioned columns (BigQuery's equivalent of indexed columns) is especially effective. Option D is correct because BigQuery is a columnar store, so selecting only the needed columns avoids reading unnecessary column data, lowering I/O and cost. Option A is wrong because omitting a LIMIT does not optimize performance and can force scanning of the entire table. Option B is wrong because Cartesian products generate massive intermediate result sets and are almost always a performance anti-pattern. Option E is wrong because SELECT * reads every column, which is the opposite of the column-pruning optimization in D.

Answer

Running queries without a limit

Answer

Using Cartesian products for all joins

Answer

Using SELECT *

Question 12

A colleague has shared a Vertex AI Workbench notebook with you. How can you run it?

Accepted Answer

Open the notebook in a managed instance and click 'Run'.. Vertex AI Workbench notebooks are managed JupyterLab environments hosted on Google Cloud; when a colleague shares one with you, you access it through the Workbench console or the provided URL and run cells directly in the managed instance. The notebook executes against the instance's runtime, which has the necessary libraries and IAM permissions. This is the standard workflow for collaborative notebook development in Vertex AI.

Answer

Copy the code into a BigQuery query window.

Answer

Paste it into the Cloud Shell terminal.

Answer

Deploy it to App Engine.

Question 13

Your team maintains a Looker Studio report connected to a BigQuery table that stores web session events. Analysts frequently filter the report by 'session_date' and 'country', but the report takes over 30 seconds to render. The underlying table is 5 TB and is partitioned by 'event_timestamp' and clustered by 'country'. You want to reduce the report's query time without changing the report's filters. What should you do?

Accepted Answer

Change the table partitioning to 'session_date' and keep clustering by 'country'.. Partitioning by 'session_date' and clustering by 'country' matches the report's filter patterns, allowing BigQuery to prune partitions and blocks. This reduces the amount of data scanned, which is the primary driver of query latency and cost. The other options either do not address the root cause or require changes to the report's data source.

Answer

Increase the Looker Studio data freshness to 1 hour.

Answer

Add a materialized view that pre-aggregates sessions by 'session_date' and 'country'.

Answer

Enable BI Engine reservation and assign it to the project.

Question 14

A financial analyst needs to create a chart in Looker Studio that shows the cumulative revenue over time, where each point includes all previous revenue. Which chart type and configuration should they use?

Accepted Answer

Time series chart with the 'Cumulative' option enabled on the revenue metric.. Cumulative revenue over time requires a running total that adds each period's revenue to all previous periods. In Looker Studio, the time series chart includes a built-in 'Cumulative' option on metrics, which automatically computes a running sum. Other chart types either lack this feature or are not designed for time-based trends, so the time series chart with cumulative enabled is the correct choice.

Answer

Scorecard with a delta indicator showing the change from the previous period.

Answer

Bar chart with the revenue metric set to 'Running total' in the metric properties.

Answer

Pie chart with revenue broken down by month and a 'Cumulative' toggle.

Question 15

You need to create a Looker Studio report that shows a time series of daily active users. The data source is a BigQuery table that is updated with new rows throughout the day, and you want the report to always show the most recent data without manual refresh. You also want to minimize query costs. Which combination of settings should you use?

Accepted Answer

Set the data freshness to 1 hour and enable caching.. Looker Studio's data freshness setting controls how often the report queries the underlying data source, and caching stores results to avoid repeated queries. For a table updated throughout the day, a 1-hour freshness with caching enabled provides a good balance: data is at most one hour old, and repeated views within that hour do not incur additional BigQuery costs. This meets both the recency and cost-minimization goals.

Answer

Set the data freshness to 4 hours and disable caching.

Answer

Set the data freshness to 12 hours and enable caching.

Answer

Set the data freshness to 15 minutes and enable caching.

Question 16

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?

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 17

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 18

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 19

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 20

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.