Data analysis and presentation

BigQuery SQL for reports and insights

BigQuery is serverless: no servers to size, you pay for bytes scanned (on-demand) or for reserved slots (capacity pricing). Know these query building blocks.

Controlling cost and speed

• Select only the columns you need. SELECT * scans every column, because storage is columnar.
• LIMIT does not reduce bytes scanned for a normal table.
• Partition tables (by ingestion time, a date/timestamp column or an integer range) and filter on the partition column so BigQuery reads fewer partitions. Use require_partition_filter to force it.
• Cluster by up to four columns that you filter or join on often; clustering sorts data inside storage blocks so filters scan less.
• Use the query validator or a dry run to see the bytes a query will process before running.
• Results of identical queries are cached for about 24 hours, free, unless the table changed or the query uses non-deterministic functions.
• Filter early and join on selective keys; the optimizer does most of the rest.
• Prefer materialized views or summary tables for dashboards that run the same aggregation constantly.
• Set maximum bytes billed on a query to guard against surprises.

Business questions to SQL

Translate the question: “Which product category grew fastest last quarter?” means aggregate revenue by category and quarter, then compare with LAG. State the grain (one row per what?), pick the measure, add filters, group, then order. Wrong answers on the exam usually aggregate at the wrong grain or filter after a join that inflated rows (fan-out from a one-to-many join).

Jupyter notebooks and Colab Enterprise

• Colab Enterprise is a managed Jupyter-style notebook service inside Google Cloud (available in BigQuery Studio and in the Agent Platform, formerly Vertex AI). It gives you secure, collaborative notebooks with IAM, runtimes you don’t manage, and access to BigQuery without handling keys.
• Use notebooks when you need Python: pandas, plotting libraries (matplotlib, seaborn), statistics, feature engineering, or calling ML libraries. Use SQL when the job is aggregation inside the warehouse.
• In a notebook you can run SQL against BigQuery with the %%bigquery magic or the BigQuery client library and get a DataFrame back. BigQuery DataFrames (bigframes) gives a pandas-like API that runs the work inside BigQuery so large data never leaves the warehouse.
• Notebooks are stored as files in a repository (BigQuery Studio saves them in Dataform repositories) and can be scheduled.
• Typical analysis flow: load, inspect (describe, null counts), clean, explore (group and plot), draw a conclusion tied to the business question.
• A notebook is for ad-hoc and exploratory work; a dashboard is for repeated sharing.

Dashboards: Looker and Looker Studio

Decision cues: “free”, “quick”, “marketing report”, “no modelling” means Looker Studio. “single definition of a metric”, “governed”, “embedded in our app”, “version-controlled model” means Looker.

Building and sharing dashboards

• In both tools you create charts (tables, time series, bar, scatter, geo maps, scorecards), add filters and date-range controls, and use calculated fields.
• Looker Studio sharing: share by email or link with viewer or editor permission; the data source credentials decide what viewers can see (owner’s credentials vs viewer’s credentials). Reports can be scheduled by email.
• In Looker, content is organised in folders with access levels (View, Manage Access/Edit); you build Explores from a model, save them as Looks, and combine tiles in dashboards; schedule deliveries to email, Slack or storage.
• Performance tips: point dashboards at partitioned tables or materialized views, extract data in Looker Studio when the source is slow, and use BigQuery BI Engine to speed up interactive queries.
• Match the chart to the question: trend over time is a line chart, parts of a whole a stacked bar or pie (few categories), comparison across categories a bar chart, relationship between two measures a scatter plot, a single KPI a scorecard.

LookML basics

LookML is Looker’s modelling language, stored as text files in a Git-backed project.

• A view maps to a table and defines dimensions (attributes to group by) and measures (aggregations such as count, sum, average).
• A model file lists connections and explores; an explore defines the base view and its joins.
• A parameter is a field users set at query time (for example a currency choice or a metric picker); other fields read its value through Liquid templating. Typical tweak: add a parameter and a measure that switches between sum and average, or change a sql: expression or type: of a dimension.
• Changes are made in Development Mode, validated, committed and then deployed to production.
• Derived tables, access filters and persistent derived tables exist but the exam stays at “edit a simple dimension, measure or parameter”.

Machine learning in BigQuery

Choosing an approach

ML use cases to recognise

• Classification: yes/no or category (churn, fraud) via logistic regression, boosted trees, random forest.
• Regression: a number (sales, price) via linear regression or boosted trees.
• Clustering: group similar rows with no labels (k-means) such as customer segments.
• Time series forecasting: ARIMA_PLUS for demand forecasting and anomaly detection.
• Recommendation: matrix factorization.
• Other: PCA for dimension reduction; import TensorFlow or ONNX models; remote models for deep learning and LLMs.

Standard ML project flow

1. Define the business problem and a success metric.
2. Collect and prepare data: clean, handle nulls, create features, split into training, validation and test sets.
3. Train a model.
4. Evaluate on held-out data.
5. Predict (inference), then deploy and monitor, and retrain as data drifts.

BigQuery ML in SQL

CREATE OR REPLACE MODEL `proj.ds.churn_model`
OPTIONS (model_type = 'LOGISTIC_REG', input_label_cols = ['churned']) AS
SELECT * FROM `proj.ds.customers_train`;

SELECT * FROM ML.EVALUATE(MODEL `proj.ds.churn_model`, TABLE `proj.ds.customers_test`);
SELECT * FROM ML.PREDICT(MODEL `proj.ds.churn_model`, TABLE `proj.ds.new_customers`);

• CREATE MODEL trains; ML.EVALUATE returns metrics (accuracy, precision, recall, ROC AUC for classification; MAE, RMSE, R-squared for regression); ML.PREDICT runs inference; ML.FORECAST for time series; ML.GENERATE_TEXT (or the newer AI.GENERATE functions) for LLM calls; ML.GENERATE_EMBEDDING for embeddings.
• The label column is what you predict; input_label_cols names it. Other columns are features. Use TRANSFORM to bake preprocessing into the model so prediction applies it automatically.
• Hyperparameter tuning is available with options such as num_trials. Overfitting shows as strong training results and weak evaluation results; fix with more data, regularisation or simpler features.
• Training data is read from BigQuery; the model is stored as a BigQuery object in a dataset, with its own IAM.

Pretrained LLMs through a remote connection

To use Gemini or other hosted models from SQL: create a BigQuery connection (a Cloud resource connection), grant its service account the role needed to call the Agent Platform endpoint (such as Vertex AI User), then CREATE MODEL ... REMOTE WITH CONNECTION ... OPTIONS (endpoint = '...'). After that, call ML.GENERATE_TEXT on table columns, for example to summarise reviews or classify sentiment. No data is exported and no model is trained.

Model Registry

The Model Registry (in the Agent Platform, formerly Vertex AI) is a central place to organise, version and govern models. BigQuery ML models can be registered by setting the model_registry = 'VERTEX_AI' option, or registered afterwards, so teams see versions, labels and aliases and can deploy to an endpoint for online prediction. BigQuery ML models score in batch in SQL; for low-latency online prediction, deploy through the registry to an endpoint.