GCP-194 · Lesson 5 of 12 · 21 min

Review: Data analysis and presentation

▶ Video coming soon A video version of this lesson is on the way.

Section 2 is about 27 in 100 of the exam, roughly 16 questions on a 60-question form. It was Mateo's strongest section in the diagnostic: 9 of 11 right. Ravi was pleased but cautious. "You are strong where you have worked: SQL, views and the store dashboard," he said. "The two you missed were about LookML and about models you have not trained yourself. That is the pattern to watch." The review followed the three objectives of the section: finding trends and insights with BigQuery and notebooks, visualizing data in Looker, and defining, training, evaluating and using machine learning models.

Answering business questions with SQL

Objective 2.1 asks candidates to write SQL for reports and insights and to use data to answer business questions. On the exam this rarely means writing a query from scratch. More often a question shows a short query and asks what it returns, why it is slow or expensive, or which change would answer a slightly different question. The skills Mateo used every week in GCP-221 are the ones that matter.

SQL skills the exam expects, with the clues that point to each.
SQL skillWhat it doesTypical exam clue
GROUP BY with SUM, COUNT, AVGTotals and averages by a category"sales by store", "average basket by region"
WHERE on the partitioning columnLimits the data read, so the query is faster and cheaper"reduce the amount of data scanned"
JOIN to a reference tableAdds names, regions or targets to facts"show the store name and region with each total"
Window functions such as ROW_NUMBER and LAGRanks, running totals, change from the previous row"top three products in each store", "change from last week"
QUALIFYFilters on the result of a window function"keep only the latest record for each customer"
APPROX_COUNT_DISTINCTA fast, close estimate of a distinct count"approximate is acceptable", "very large table"
Views and materialized viewsSaved logic; a materialized view also stores precomputed results"many dashboards run the same aggregation"

Cost belongs in this objective too, because a correct query that scans far too much data is a poor answer. Under on-demand pricing, BigQuery charges by the amount of data a query reads. Google's pricing page in November 2026 listed 6.25 US dollars per tebibyte in US locations, with the first tebibyte each month free. The rules for reducing cost are the ones from GCP-221: select only the columns you need, filter on the partitioning column, cluster large tables on columns that are often filtered, and use materialized views for aggregations many people repeat.

Worked problem

The cost of a dashboard tile

In September, a marketing dashboard tile at Birchline ran a query that read 1,400 GiB each time it was viewed, and it was viewed 212 times. At 6.25 US dollars per TiB, what did one view cost, and what did the month cost? After the fix in GCP-221, each view read about 0.3 GiB. What does a view cost now?

Show solution
  1. One TiB is 1,024 GiB, so 1,400 GiB is 1,400 ÷ 1,024, or about 1.37 TiB.
  2. One view cost about 1.37 × 6.25, or 8.54 US dollars.
  3. For 212 views: 212 × 8.54 is about 1,810 US dollars, before the free tebibyte.
  4. After the fix: 0.3 ÷ 1,024 is about 0.00029 TiB, which costs about 0.0018 US dollars, a fraction of a cent.

Answer: About 8.54 US dollars a view and about 1,810 a month before the fix; well under a cent a view after it.

The example also shows the exam's view of cost. The answer to an expensive query is almost never to buy more capacity. It is to read less data. When a question describes a query or dashboard that costs too much, look first for an option that adds a partition filter, removes unused columns, or precomputes a common aggregation.

Notebooks

The guide asks candidates to use notebooks, such as Colab Enterprise, to analyze and visualize data. In BigQuery Studio, a Colab Enterprise notebook runs inside the Google Cloud project, can query BigQuery directly, and can use Python libraries to clean, chart and model data. Notebooks fit exploration: trying several ideas quickly, combining SQL with Python, and sharing a worked analysis with colleagues. They are not the right home for a report that dozens of people need every morning. That belongs in a dashboard.

Notebooks compared with other tools.
NeedBetter choice
Explore a question, try several approaches, chart the resultsA notebook
Give 40 store managers the same view every morningA dashboard in Looker or Data Studio
Share a one-time analysis with the steps visibleA notebook, shared with the team
Run the same SQL transformation every nightA scheduled query or a Dataform workflow, not a notebook

Looker, Data Studio and dashboards

Objective 2.2 asks candidates to create, modify and share Looker dashboards, to compare Looker with Looker Studio, and to modify simple LookML parameters. Lesson 1 noted the rename: the tool the guide calls Looker Studio became Data Studio in April 2026, and its paid tier became Data Studio Pro. Looker itself was not renamed. The exam may use either name for Data Studio, so learn what each product is for.

Two columns. Left, Data Studio (formerly Looker Studio): free, self-service reports; connect many sources directly; each report defines its own fields; quick to start; Pro tier adds team workspaces and admin. Right, Looker: an enterprise platform; a semantic model written in LookML defines dimensions and measures once; governed, consistent metrics; explores, dashboards and embedded analytics; version control with Git. An arrow between them labeled: choose by how much shared, governed definition the organization needs.
Data Studio and Looker compared. The deciding question is usually whether metrics must be defined once and shared across many users.
Choosing between the two.
FactorData Studio (formerly Looker Studio)Looker
Cost and start-upFree to start; Pro tier for teamsAn enterprise product with its own licensing
Where metrics are definedIn each report or data sourceOnce, in a LookML model shared by everyone
Best forQuick, self-service reports and simple sharingGoverned, consistent metrics across many teams; embedded analytics
Change controlEdited in the browserLookML in a Git repository, with development and production modes
Typical clue"quickly", "for a small team", "free""one definition of revenue for the whole company", "governed", "semantic model"

Birchline used both. The store manager dashboard was built in Data Studio Pro, because it needed to launch quickly for 40 managers and use a handful of views. Finance's monthly reporting used Looker, because revenue, refunds and gift cards had to mean the same thing in every report. A question that stresses a single trusted definition of a metric across a company is a Looker question.

Simple LookML

The guide says candidates should be able to modify simple LookML parameters. LookML is the language Looker uses to describe data: a view maps to a table and declares its dimensions and measures; a model groups views into explores, which users query. Mateo does not need to write a full model, but he should read and change a small view.

A small LookML view. Dimensions describe columns users group by; a measure describes an aggregation. Changing label renames the field users see; changing type changes the calculation.
view: sales {
  sql_table_name: curated.sales ;;
  dimension: store_id {
    type: number
    sql: ${TABLE}.store_id ;;
  }
  dimension_group: sale {
    type: time
    timeframes: [date, week, month]
    sql: ${TABLE}.sale_date ;;
  }
  measure: total_net_sales {
    type: sum
    sql: ${TABLE}.net_sales ;;
    value_format_name: usd
    label: "Net sales"
  }
}
LookML parameters to recognize.
ParameterWhat it controls
sql_table_nameThe table a view reads from
dimension, dimension_groupA field users can group or filter by; a group creates several time fields at once
measure with type: sum, count, averageAn aggregated field and how it is calculated
labelThe name users see
hidden: yesHides a field from users without deleting it
value_format_nameHow numbers are displayed
explore (in a model)Which views users can query together and how they join

Sharing is the last part of the objective. Dashboards in Looker are shared through folders and permissions, and can be scheduled to send results by email. Data Studio reports are shared like other Google files, with viewer and editor access. In both, the data a viewer can see depends on the credentials the report uses. A report that runs on the owner's credentials shows the owner's data to everyone who can open it, which is why Birchline's store dashboard used authorized views with row access policies.

Machine learning: choosing the approach

Objective 2.3 covers machine learning at a practitioner's level. The guide asks candidates to tell when to use BigQuery ML and when to use AutoML, to use pretrained Google models through BigQuery, to plan a standard project, to train and evaluate models with SQL, to run predictions, and to organize models in Model Registry.

A decision chart. Start: a machine learning task. If a pretrained model already does the job (summarize text, classify sentiment, translate, extract from documents), use a pretrained model through a BigQuery remote model. Otherwise, if the data is in BigQuery and a standard model type fits (regression, classification, clustering, forecasting, recommendations), use BigQuery ML with SQL. Otherwise, if the team wants a high-quality custom model on images, text or tabular data without writing training code, use AutoML. Otherwise, custom training.
Choosing an approach to machine learning. The simplest approach that does the job is usually the exam's answer.
Four approaches, from simplest to most effort.
ApproachFits whenExample
Pretrained model through a BigQuery remote modelA general task such as summarizing, classifying or extracting text that a Google model already does wellSummarize free-text return reasons into categories
BigQuery MLData is already in BigQuery and a standard model type fits; analysts know SQLPredict return risk with logistic regression; forecast store sales
AutoMLA custom model is needed on images, text or tables, and the team wants Google to handle model designClassify product photos by category
Custom trainingSpecialized models and an experienced ML teamBeyond the scope of this exam

BigQuery ML supports many model types. The exam expects candidates to match the business question to the type, not to tune them.

Matching questions to model types.
Business questionBigQuery ML model type
How much will this customer spend?Linear regression, or boosted trees for more accuracy
Will this order be returned?Logistic regression, or boosted trees
Which groups of similar stores or customers exist?K-means clustering
What will sales be next month?ARIMA_PLUS, or the newer forecasting functions
Which products should we recommend?Matrix factorization

The project steps are the same in every approach: define the business question and how success will be measured, prepare features and split the data, train, evaluate on data the model has not seen, run predictions, and monitor the model once it is used. In BigQuery ML each step is SQL: CREATE MODEL trains, ML.EVALUATE reports quality, and ML.PREDICT scores new rows. Trained models can be registered in Model Registry so that versions are tracked and deployed consistently.

Worked problem

Reading an evaluation

In GCP-221, Mateo evaluated a return-risk model on 2,000 test orders, of which 300 were returned. At a threshold of 0.5 it flagged 270 orders: 180 that were returned and 90 that were not. It missed 120 returns. What were its precision, recall and accuracy?

Show solution
  1. Precision is the share of flagged orders that were really returned: 180 ÷ 270, or about 0.67.
  2. Recall is the share of real returns the model caught: 180 ÷ 300, or 0.60.
  3. Correct predictions are the 180 caught returns plus the orders correctly left unflagged: 2,000 − 300 − 90 = 1,610.
  4. Accuracy is (180 + 1,610) ÷ 2,000 = 1,790 ÷ 2,000, or 0.895.

Answer: Precision about 0.67, recall 0.60, accuracy about 0.90. Accuracy looks high mainly because most orders are not returned.

That last point is a favorite of the exam. When the thing being predicted is rare, accuracy can look excellent even for a weak model, because predicting "no" for everything is usually right. A question about rare events, such as fraud or returns, usually wants precision, recall or a related measure, not accuracy.

Reading analysis questions for cost and speed

Several analysis questions turn on a cost or performance clue rather than on SQL syntax. Three patterns recur. If a dashboard runs the same heavy query many times a day over data that changes once a night, the answer is usually to precompute: a scheduled query that writes a small summary table, or a materialized view, so each view of the dashboard reads a few megabytes instead of scanning the whole fact table. If a query filters on a date, the answer is usually a table partitioned on that date, so BigQuery skips the partitions the filter excludes, with clustering on the columns most often used in filters within a partition. And if a question mentions analysts exploring data with SELECT *, the answer is to select only the columns needed, because BigQuery bills on-demand queries by the bytes of the columns it reads, not by the rows returned. A LIMIT clause does not reduce the bytes scanned. Mateo's own cost example earlier in this lesson, the store dashboard scanning about 1,400 GiB a view, is the first pattern in miniature, and his fix, a nightly summary table, is the answer the exam would expect.

Common mistakes

  • MistakeFixing an expensive dashboard by buying more capacity.

    InsteadRead less data: partition filters, fewer columns, materialized views.

  • MistakeUsing a notebook as a daily report for many people.

    InsteadUse a dashboard for repeated viewing; use notebooks for exploration.

  • MistakeChoosing Data Studio when a company needs one governed definition of revenue.

    InsteadThat is the case for Looker and its LookML model.

  • MistakeTraining a custom model for a task a pretrained model already does.

    InsteadTry a pretrained model through BigQuery first.

  • MistakeJudging a rare-event model by accuracy.

    InsteadUse precision and recall when the outcome is rare.

Summary and key terms

  • Section 2 is about 27 in 100 of the exam.
  • SQL questions test aggregation, joins, window functions, partition filters and cost; on-demand BigQuery charges by data read.
  • Notebooks suit exploration and dashboards suit repeated viewing.
  • Data Studio, formerly Looker Studio, is free and self-service; Looker gives governed, shared definitions through LookML.
  • Read simple LookML views, dimensions and measures.
  • For machine learning, try a pretrained model first, then BigQuery ML for standard model types on BigQuery data, then AutoML.
  • Train, evaluate and predict with SQL, register models in Model Registry, and judge rare-event models by precision and recall.
Partition filter
A WHERE condition on a table's partitioning column that lets BigQuery skip partitions.
Materialized view
A view whose results BigQuery precomputes and keeps up to date.
Colab Enterprise
A managed notebook environment inside a Google Cloud project.
Data Studio
Google's free self-service reporting tool, called Looker Studio until April 2026.
LookML
The language Looker uses to define views, dimensions, measures and explores.
BigQuery ML
A feature that trains and runs machine learning models with SQL in BigQuery.
Remote model
A BigQuery model that calls a pretrained or hosted model through a connection.
Recall
The share of real positive cases a model correctly identifies.

Practice problems

Work each problem on paper first, then reveal the answer to check.

  1. A query on a date-partitioned table filters on a different date column. Why might it scan the whole table?

    Show answer

    BigQuery can skip partitions only when the filter is on the partitioning column.

  2. Which LookML parameter changes the name users see for a field?

    Show answer

    label.

  3. A model predicts fraud, which occurs in 1 in 500 orders, with 99.8 in 100 accuracy. Why is that not reassuring?

    Show answer

    Predicting no fraud for every order would be just as accurate; precision and recall show whether fraud is caught.

Lesson quiz · 5 questions

Score 60% or more to complete this lesson.

Take the quiz