Analyzing and Presenting Data with BigQuery and Looker

Identifying trends with BigQuery (opens in a new tab) and Jupyter notebooks means using SQL to summarize large tables and Python to explore and chart the results. BigQuery stores and processes the table data, while a notebook provides the interactive place where code, tables, and charts stay together. Data flows from BigQuery tables to query results to notebook DataFrames and then to charts and dashboards.

1.1Writing GoogleSQL Queries for Reports

Writing GoogleSQL queries for reports means filtering, joining, and aggregating rows into counts, sums, and averages. BigQuery uses GoogleSQL as its preferred ANSI-standard dialect, which supports joins, aggregation and analytic functions, operators, and conditional expressions. The older legacy SQL dialect remains only for existing queries that already use it. Filtering keeps only needed rows, which reduces bytes scanned and improves speed and cost, aggregation collapses detail rows into totals by day, region, or customer, and joining combines related tables such as customers and orders on shared keys. BigQuery supports unenforced foreign keys that describe intended relationships for optimization but do not block mismatched values or enforce integrity. Reusable logic comes from user-defined functions (UDFs) written in SQL or JavaScript and from positional parameters that run the same query with different values. Queries can also reach outside data through external tables and federated queries:

• Cloud Storage holds files queried as external tables without loading them.
• Bigtable, Spanner, and Cloud SQL provide operational data for federated queries.
• Google Sheets, AWS S3, and Azure Blob Storage extend joins to spreadsheets and other clouds.

1.2Running Queries and Moving Results to Visuals

Running queries with BigQuery interfaces means sending the same SQL text through different tools that create query jobs on managed storage and compute. Queries can run as interactive queries for fast results or as batch queries for larger jobs that can wait. A dry run estimates bytes scanned before running, which helps control cost under on-demand pricing based on bytes scanned. BigQuery keeps a history of executed queries, and the INFORMATION_SCHEMA.JOBS view retains all jobs submitted in the last 6 months.

• BigQuery Studio provides a web editor for writing, running, and exploring queries.
• bq command-line tool runs queries and manages jobs from a terminal.
• APIs let applications and notebooks submit queries programmatically.
• ODBC and JDBC drivers connect existing tools with standard connections.
• Python notebooks with pandas-gbq run queries from Jupyter and load results into DataFrames. Once results return, data flows from the query result to notebooks or visualization tools. Looker Studio can launch directly from BigQuery to chart results, while Looker, Connected Sheets, and Tableau also connect to BigQuery datasets. The pandas-compatible BigQuery DataFrames API lets analysts continue the same analysis in Python.

1.3Speeding Up Repeated Reports

Speeding up reports in BigQuery means using storage and caching designs so repeated queries scan less data and return faster. Partitioned tables divide a large table by time or integer ranges so queries that filter on the partition column skip unneeded partitions. Clustering stores data ordered on disk so queries that filter on clustered columns retrieve matching rows faster, including string columns. Tables can require a partition filter so every query must include the partition column and avoid accidental full-table scans. Materialized views store cached persistent results for queries that run often. BigQuery provides Smart Tuning materialized views that always return current data and support automatic query rewrite even when the query refers to the base table. BI Engine is the fast, in-memory analysis service for low-latency dashboards and interactive exploration. Search Indexes and Vector Indexes, which can be automatic or managed, speed specific lookup and search workloads.

1.4Colab Enterprise Notebooks with SQL and Python

A Colab Enterprise notebook is a BigQuery Studio code asset powered by Dataform that supports SQL, Python, and common data packages. The notebook provides the frontend where code is written, while an attached runtime provides the compute that executes cells. BigQuery stores and processes the table data, so the notebook depends on both the runtime and BigQuery to work. Creating the notebook requires project access, including roles such as BigQuery User and Colab Enterprise User. A new notebook is created and connected in this order:

1. Open BigQuery: Go to the BigQuery page in the Google Cloud console.
2. Create notebook: Expand the project, click Notebooks, then click New notebook > Empty notebook.
3. Save and name: Save and rename the notebook so it can be found later.
4. Select region: Open the notebook from the region that contains it.
5. Connect: Click Connect on the toolbar to attach the default runtime. A SQL cell sends SQL from the runtime to BigQuery, and BigQuery returns the result as a BigQuery DataFrame named for the SQL cell title. That DataFrame then flows into later Python cells for cleaning and charting. SQL follows BigQuery syntax and can reference a Python variable in braces, such as {my_threshold} in WHERE x > {my_threshold}.

1.5Visualizing DataFrames and Scaling Notebook Work

Visualizing DataFrames means turning rows in a pandas DataFrame into charts inside the notebook. The kernel runs Python that selects columns and passes them to a plotting library, which renders the figure below the cell. Clear titles, axis labels, and consistent scales help connect the visual to the underlying columns. Common choices are line charts for change over time, bar charts for category comparison, histograms for distribution, box plots for spread and outliers, scatter plots for two-variable relationships, and heatmaps for table patterns.

1.6Calculating Metrics and Cleaning Data Quality Issues

Calculating metrics with SQL means grouping rows by product, store, region, or time to reveal patterns. A query reads rows, groups them, and returns counts by category, averages by group, and correlations between numeric features. Segmenting gives each segment its own row for comparison, while exploring distributions shows which categories appear most often. BigQuery Studio supports this work with a SQL editor, embedded Python notebooks, PySpark editor, asset version history, and Knowledge Catalog profiling. When a query runs, BigQuery builds an execution tree that breaks work into stages, moves intermediate data through a fast distributed shuffle, and writes final results to storage. Finding data quality issues means protecting analysis from bad conclusions. Missing values appear as nulls and are found by counting nulls, outliers appear far above or below normal, and anomalies appear as unexpected breaks in trends. Cleaning removes or corrects problem points, and wrangling creates analysis-ready features such as one-hot or label encoding. For large data, this work uses distributed processing with BigQuery ML, BigQuery DataFrames, or Managed Service for Apache Spark. The Data Science Agent, a Gemini-powered assistant in the notebook, can then generate Python or SQL from prompts using CSV files or BigQuery tables selected with the table selector or @mention.

Visualizing data in Looker (opens in a new tab) means turning saved Explore queries into interactive dashboards that answer business questions. Data flows from an Explore query into a saved Look or tile, and from one or more tiles into a dashboard where viewers filter and explore. Dashboards combine charts, text, filters, and layout so one page can show the same question from different angles.

2.1Building Dashboards from Looks and Explores

A Look is a saved Explore query plus its chart that can be reused later or added to dashboards. The flow moves from an Explore query to a saved Look, and then from the Look to one or more dashboard tiles. Saving a Look preserves both the query and the visualization, so teams reuse the same definition without rebuilding it. A user-defined dashboard is created and edited in the Looker interface and stored in a folder, while a LookML dashboard is defined in code in a .dashboard.lookml file and kept in version control. To view either type, a person needs View access for its folder and access to the models behind its tiles. A blank user-defined dashboard is created from a folder where you have Manage Access, Edit access:

1. Navigate to folder: Open the folder where the dashboard should live.
2. Start creation: Click New, then click Dashboard.
3. Name dashboard: Enter a name in the Name field.
4. Create page: Click Create Dashboard to open a blank dashboard.
5. Enter edit mode: Click Edit dashboard to add tiles, text, filters, and tabs. A dashboard can also be created directly from a Look or Explore by opening it, clicking Save, and choosing As a new dashboard. In that flow you enter a title for the dashboard and its first tile, select a folder, and choose whether to use the Look or Explore filters as dashboard filters.

2.2Dashboard Tile Types and Layout

Dashboard tiles are the building blocks that display queries, Looks, text, or custom apps. The first tile takes the full page width, and later tiles are sized to one-third width and placed in rows underneath. Tiles can be moved by dragging, resized, renamed, or edited to change the visualization or query.

2.3Filters, Scheduling, Alerts, and Access

Dashboard filters and cross-filters let one dashboard serve many questions. A creator adds a dashboard filter in edit mode, chooses which field it controls, and uses Tiles to Update to choose which tiles it applies to. A viewer selection then flows from the filter control to each connected tile, such as narrowing by date, region, or product. Cross-filtering is ad hoc filtering where a viewer clicks a data point in one tile and all other tiles filter to that value. Cross-filters cannot be saved, but they can be shared by sharing the URL. Tiles also support drilling to see granular detail, open Explore rows, follow links, or run data actions. The Looker Scheduler sends instant or recurring deliveries from dashboards, Looks, and Explores to destinations inside and outside Looker. A schedule can run after a datagroup triggers and rebuilds relevant persistent derived tables (PDTs), so recipients get fresh data. Delivery depends on data access, because a user can only schedule models and data they can see. Built-in destinations are Email, Webhook, Amazon S3 bucket, and SFTP server. Dashboards deliver as PDF, visualization (PNG), or CSV ZIP, while Looks and Explores deliver as CSV, XLSX, JSON variants, Text, or HTML. Deliveries without schedule_without_limit permission are limited to 5,000 rows. Data-driven alerts watch a tile for change, require create_alerts permission plus tile access, and folder access is separate: View opens content while Manage Access, Edit changes content and sharing.

2.4Choosing Between Looker and Looker Studio

Choosing between Looker and Looker Studio means matching governance, speed, and sharing needs. Looker is a governed enterprise business intelligence platform that defines metrics once in a central model. Looker Studio is a no-cost, self-service reporting tool that builds fast, flexible reports directly from data sources. In every Looker deployment, data remains in-database and is not copied to the Looker instance. Looker is the right choice when the work needs governance for key metrics, robust permissions, sharing with scheduling and alerting, operational analytics, embedding and API integration, or custom applications. Looker can be customer-managed in Google Cloud, hosted in another cloud, or hosted on customer servers. Looker (Google Cloud core) offers the same features with deeper Google Cloud integration, including provisioning from the Google Cloud console. Looker Studio is the right choice for self-service visuals, flexible ad hoc reporting, access to more than 1,000 data sources, web embedding, one-off reports, or single-source datasets. A Looker Studio report is a multi-page asset with charts, controls, text, shapes, and images, using embedded sources editable by report editors or reusable sources shared across reports. Looker connects as a governed modeling layer that queries live data without storing a copy. Data flows from the database to Looker when a user runs an Explore, and Looker generates SQL against the source. Direct connections include BigQuery, Spanner, Cloud SQL for MySQL, and Cloud SQL for PostgreSQL. Looker Studio connects as a lightweight layer that attaches directly through named connectors, so data flows from the source directly to the report. Using a governed Looker model inside Looker Studio lets one Looker Explore become a Studio data source, inheriting definitions and permissions. That path requires explore permission to connect and access_data plus clear_cache_refresh for viewers, with 12-hour default freshness, 5-minute query timeout, 5,000-row chart limit, 75,000-row export limit, and job label requestor:looker_studio on BigQuery queries.

2.5LookML Dimensions, Measures, Explores, and Formatting

Manipulating simple LookML parameters means editing settings that control what users see and how Looker queries the database. Every change alters the SQL that Looker generates, changing both the field picker and the rows in reports. A dimension is a groupable row-level attribute such as product name or created date, declared with dimension: field_name plus type and sql, often sql: ${TABLE}.column_name ;;. A measure aggregates many rows with functions such as COUNT, SUM, AVG, MIN, MAX, count_distinct, or percent_of_previous, often reusing a dimension like sql: ${amount} ;; with type: sum. A dimension group creates multiple time dimensions such as date, week, month, quarter, and year from one column.

Defining and using machine learning models on Google Cloud means matching the data shape and team skills to the right training path. BigQuery ML (opens in a new tab) trains and predicts with SQL where large structured tables already live, while AutoML trains on labeled images, text, and other unstructured data without custom model code. Data flows from BigQuery tables directly to a BigQuery ML model and back to BigQuery tables, while data flows from labeled examples to an AutoML model that learns only from those examples.

3.1BigQuery ML and AutoML Use Cases

BigQuery ML fits SQL workflows because training, evaluation, and prediction all run inside BigQuery with GoogleSQL. An analyst who can write SELECT can train without notebooks or deployment servers, and BigQuery handles scaling where data already lives. Choose BigQuery ML when labeled history exists in the warehouse and results need to flow back to tables for dashboards.

3.2Calling Pretrained LLMs from BigQuery

Using pretrained large language models from BigQuery means letting SQL call models hosted outside BigQuery. A Cloud resource connection authorizes BigQuery to send requests to Vertex AI, and a remote model resource points to a foundation model such as Gemini. Data flows from BigQuery tables to the remote endpoint for inference in Vertex AI, and results flow back into query results or tables. The connection definition is encrypted and stored in the BigQuery connection service, and BigQuery holds the trust boundary between the user and the external system. Connection type controls the destination that traffic flows from BigQuery toward, including Amazon Simple Storage Service and Azure Blob Storage through BigQuery Omni, Apache Spark for Python procedures, Spanner and Cloud SQL and AlloyDB for federated queries, and Google Cloud resources for Vertex AI remote models and BigLake. No connection is required for Bigtable and Google Drive. Authentication for a Cloud resource connection uses a system service account that BigQuery creates automatically. The administrator copies the service account ID and grants it Vertex AI permission, and grants users permission to use the connection. Creation follows select the Cloud resource type, create the connection in a location, copy the service account ID, and reference the connection in CREATE MODEL. The connection and dataset should be in the same location, such as multi-region US. A remote model registers a Vertex AI model as a BigQuery model without training data in BigQuery. The basic form is CREATE OR REPLACE MODEL PROJECT_ID.DATASET_ID.MODEL_NAME REMOTE WITH CONNECTION CONNECTION_PATH OPTIONS (ENDPOINT = 'ENDPOINT'). Use DEFAULT when a default connection exists, or the full path PROJECT_ID.REGION.CONNECTION_ID. The dataset location must match the connection location, and the Vertex AI model must be available in that location. Gemini, embedding, Claude, and Mistral models use a pretrained name such as gemini-2.0-flash, Llama models use an OpenAI-style path, and custom endpoints use a full URL plus INPUT and OUTPUT clauses. Generative AI functions then work with text in queries for generation, summarization, extraction, classification, and embeddings, while task-specific functions use Cloud AI APIs for translation, document processing, transcription, and vision.

3.3Planning an ML Project from Collection to Prediction

A standard ML project moves in order from data collection to training to evaluation to prediction. Raw data flows into storage, clean tables flow into training, a trained model flows into evaluation, and only acceptable quality flows into prediction. BigQuery ML supports this lifecycle with SQL so training data does not move elsewhere. Cloud Storage archives original raw files as the durable record, BigQuery stores structured training-ready tables, and Dataflow reads raw records, applies cleansing, and writes clean rows to BigQuery. Keeping the raw copy separate from the prepared copy protects the project because tables can be rebuilt from the archive.

3.4Creating, Training, and Evaluating with BigQuery ML SQL

Executing SQL to create and evaluate models means using CREATE MODEL and evaluation functions instead of Python. Training data stays in BigQuery tables or views, and the trained model is stored in a dataset like a table or view. The workflow moves from training query to stored model to predictions and explanations:

1. Create the model: Name the model, set OPTIONS such as MODEL_TYPE, and supply rows with AS SELECT.
2. Preprocess features: BigQuery ML applies automatic preprocessing, plus manual TRANSFORM logic.
3. Refine the model: Tune hyperparameters such as NUM_TRIALS.
4. Evaluate the model: Check performance outside training and compare models.
5. Perform inference: Score new data with functions such as ML.PREDICT.
6. Explain and inspect: Review feature influence and weights such as coefficients or cluster centers. A statement names the destination as project.dataset.model, sets MODEL_TYPE, and ends with the training query. Classification and regression use INPUT_LABEL_COLS, matrix factorization uses USER_COL, ITEM_COL, and RATING_COL, and time-series models use TIME_SERIES_TIMESTAMP_COL and TIME_SERIES_DATA_COL. Split options such as DATA_SPLIT_METHOD control rows reserved for evaluation. Internally trained models train entirely inside BigQuery and support dry runs, including linear and logistic regression, k-means, matrix factorization, principal component analysis (PCA), contribution analysis, and ARIMA_PLUS. Externally trained models train in Agent Platform with TensorFlow, XGBoost, or AutoML, remote models reference a deployed HTTPS endpoint, and imported models load Open Neural Network Exchange (ONNX) or TensorFlow files from Cloud Storage. Linear regression, logistic regression, and k-means differ by question and label need. Linear regression predicts a numeric value such as house price and requires a real-valued numeric label that is not infinity or NaN. Logistic regression predicts a category such as spam or low, medium, or high value and requires a categorical label with up to 50 unique values. K-means clustering assigns groups such as customer segments, needs no label, and uses all input data. ML.EVALUATE takes a stored model and evaluation data and returns summary statistics, where accuracy, precision, and recall are higher-is-better and mean squared error is lower-is-better. ML.METRICS differs because it scores actual and predicted columns directly without a stored model.

3.5Batch Inference, Forecasting, and Anomaly Detection

Inference with BigQuery ML models means applying a trained model to new data to generate predictions, forecasts, or anomaly flags. Batch inference scores many rows where data already lives, so data flows from BigQuery tables into the model and back to BigQuery tables. Teams can work through the console, bq tool, REST API, or Colab Enterprise notebooks. The batch job and tables must be in the same region. A typical batch workflow preserves order from preparation to delivery:

1. Prepare inputs: Keep inputs in a BigQuery table and carry extra columns to output.
2. Submit the batch job: Point the job to the model and input table.
3. Monitor the job: Track status and resume incomplete jobs when needed.
4. Retrieve output: Query the predictions table and separate error table. Time-series forecasting predicts future values from ordered history using TimesFM, ARIMA_PLUS, and ARIMA_PLUS_XREG. Queries set horizon for future points and confidence level for intervals. Choose ARIMA_PLUS_XREG when external regressors matter, and TimesFM or ARIMA_PLUS when only history matters. Anomaly detection flags breaks from baseline: supervised detection with ML.PREDICT applies when labeled training examples exist, while unsupervised detection applies with no labels. TimesFM uses AI.DETECT_ANOMALIES for time series, while ARIMA_PLUS and ARIMA_PLUS_XREG use ML.DETECT_ANOMALIES. For independent rows, ML.DETECT_ANOMALIES uses k-means distance, autoencoder loss, or PCA loss. BigQuery sources must be no larger than 100 GB, and CSV sources need a header row, quoted strings, and limits of 10 GB per object and 100 GB total.

3.6Organizing Models in Model Registry

Organization in Model Registry means keeping models, versions, metadata, and deployment history in one central place. A single registered model can hold multiple versions, each with its own version ID, files, and evaluation results. The registry connects stored metadata to organizing, evaluating, deploying to an endpoint for online inference, creating batch inferences, and viewing version details. Registration copies a BigQuery ML model's reference and metadata into the registry for versioning and deployment. The ordered flow is to create the model in BigQuery ML with SQL, choose which models to register, and then manage and deploy the registered model.

Key Facts

• BigQuery uses GoogleSQL as its preferred dialect, retains job history in INFORMATION_SCHEMA.JOBS for 6 months, and speeds repeated queries with time or integer partitioning, clustering, Smart Tuning materialized views, and BI Engine in-memory analysis.
• A Colab Enterprise notebook depends on an attached runtime and BigQuery tables, returns SQL cell output as a DataFrame named for the cell title, and can reference Python variables in braces such as {my_threshold}.
• A Look is a saved Explore query and chart, where query tiles belong to the dashboard and survive deletion of the source Look while Look-linked tiles show an error if that Look is deleted.
• Looker defines metrics centrally in LookML and queries live without copying data, while Looker Studio builds multi-page reports directly from connectors, with a Looker Explore source limited to 12-hour freshness, 5-minute timeout, 5,000-row charts, and 75,000-row exports.
• BigQuery ML follows train with CREATE MODEL, evaluate with ML.EVALUATE, and predict with ML.PREDICT, where linear regression needs a numeric label, logistic regression needs a categorical label up to 50 values, and k-means needs no label.