Prepare and test your skills
Prepare and test your skills
Defining and executing SQL queries in BigQuery (opens in a new tab) means writing GoogleSQL statements that filter, join, and aggregate datasets into structured reports and business insights. 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. A query reads rows from tables, keeps only the rows needed, combines related tables, and summarizes the result into counts, sums, and averages.
Filtering, aggregating, and joining with GoogleSQL are the three core operations that turn raw tables into accurate reports. Filtering selects only the rows needed for a report, which reduces bytes scanned and improves speed and cost on large tables. Aggregation collapses many detailed rows into totals, averages, and counts, while grouping clauses produce one summary row per category such as day, region, or customer. Joining combines rows from two or more tables on shared keys, so related facts such as customers and orders appear in one result.
BigQuery supports unenforced foreign keys in GoogleSQL that describe the intended relationship between tables for query optimization. These keys do not block mismatched values during loading and do not enforce data integrity. Reusable logic comes from user-defined functions (UDFs) written in SQL or JavaScript, which package repeated cleaning or calculation steps, and from positional parameters that let the same query run safely with different values. Together, clear filters, correct joins, and reusable expressions help queries return trusted results.
Joins can also reach data stored outside BigQuery through external tables and federated queries that read data where it lives:
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. BigQuery maintains a history of executed queries for review and troubleshooting, and the INFORMATION_SCHEMA.JOBS view retains the history of all jobs submitted in the last 6 months. A dry run can estimate bytes scanned before running, which helps control cost under on-demand pricing based on per-query bytes scanned.
Once results return, data flows from the query result to notebooks or visualization tools for exploration and sharing. Looker Studio (opens in a new tab) can be launched directly from BigQuery to chart query results, while Looker, Connected Sheets, and Tableau also connect to BigQuery datasets for dashboards and spreadsheets. The pandas-compatible BigQuery DataFrames API lets analysts continue the same analysis in Python.
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. Tables can require a partition filter so every query must include the partition column and accidental full-table scans are avoided.
Materialized views store cached persistent results to optimize 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.
| Option | What it provides | Choose when |
|---|---|---|
| Partitioned tables | Pruned storage based on time or integer ranges | Reports always filter a large table by date or integer range |
| Materialized views | Cached views with automatic query rewrite | The same aggregations or joins are queried repeatedly |
| BI Engine | Fast, in-memory analysis service | Dashboards need low-latency interactive exploration |
Exam tip: BigQuery partitions only by dates and integers; use clustering for string columns that need faster filtered retrieval.
Using Jupyter notebooks to analyze and visualize data means running SQL and Python in one interactive place where code, results, and charts stay together. 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.
A Colab Enterprise notebook must be created from BigQuery and connected to a runtime before its cells can run. Creating the notebook requires access to the project, including roles such as BigQuery User and Colab Enterprise User. A SQL cell sends a SQL statement from the notebook runtime to BigQuery, and BigQuery returns the result set to the notebook as a BigQuery DataFrame named for the SQL cell title. That DataFrame then flows into later Python cells for cleaning, calculation, and charting.
A new notebook is created and connected in this order:
A SQL cell is added through the Insert code cell options menu by selecting Add SQL cell. SQL in the cell follows BigQuery SQL syntax and can reference a Python variable by enclosing the variable name in braces, such as {my_threshold} in WHERE x > {my_threshold}. Running the cell sends the query from the notebook to BigQuery without moving the source data.
Visualizing DataFrames with Python libraries means turning rows and columns in a pandas DataFrame into charts inside the notebook. The notebook kernel runs Python code that reads data into a DataFrame, selects the columns to plot, and passes those columns to a plotting library. The library renders the figure directly below the cell, so changing the DataFrame and re-running the cell updates the visual immediately. Clear titles, axis labels, and consistent scales help the reader connect the visual back to the underlying columns.
| Library | Primary style | Choose when |
|---|---|---|
| Matplotlib | Low-level control of figures and axes | The chart needs custom layout or precise formatting |
| Seaborn | Statistical plots with clean default themes | The goal is to show distributions, correlations, or category comparisons quickly |
| Altair | Declarative, interactive charts | The audience benefits from hovering, filtering, or zooming the data |
Choosing the right chart type means matching the DataFrame data to the question being asked:
Exam tip: Enclose a Python variable name in braces to use it inside a SQL cell, such as WHERE x > {my_threshold}.
Scaling processing for notebooks means choosing a Spark backend that matches the need for control versus simplicity. Managed Service for Apache Spark offers a serverless model that provisions resources on demand for batches and interactive sessions, and a cluster-based model where the analyst controls cluster size, machine types, and configuration. Data flows from the notebook to the selected backend, which runs the Spark code and returns results to the notebook. An interactive session is a live serverless workspace for exploration from inside a Jupyter notebook, while a session template defines reusable settings including Spark properties and libraries.
A Spark Connect session links a BigQuery Studio notebook to a serverless session through the Python connector. The session is built from a session template that must specify runtime version 2.3 or later and notebook type Spark Connect. The connector needs project ID, Compute Engine region, session template name, and an optional app name. Spark Connect supports DataFrame, Functions, and Column APIs but does not support SparkContext and RDD. DIRECT writes use the BigQuery Storage Write API to write directly to BigQuery, while INDIRECT writes first write to an intermediate Cloud Storage bucket and then to BigQuery.
Notebook environments also depend on isolated libraries and cleanup to stay reliable and low-cost. A conda environment appears as a kernel in JupyterLab and isolates its own packages, so different analyses can use different versions of Python, R, TensorFlow, PyTorch, or Apache Beam. A notebook schedule (opens in a new tab) automates repeated runs of a notebook file, and its Schedule details page shows frequency and the last five executions. Deleting a schedule stops future runs but does not delete past executions, while deleting a Workbench instance or project removes the underlying resources.
Analyzing data to answer business questions means turning many raw rows into small, trusted metrics and visuals that explain performance. BigQuery (opens in a new tab) is the central service where GoogleSQL queries calculate totals, averages, and segmented comparisons. Jupyter notebooks extend the same results with Python statistics and charts, while connected analytics tools format the evidence for decision makers. The overall flow runs from BigQuery tables to grouped metrics to one-row-per-segment results, then to notebooks and dashboards.
Calculating metrics and exploring distributions with SQL means summarizing groups of rows to reveal patterns and relationships. A query reads rows from a dataset, groups them by product, store, region, or time period, and returns counts by category, averages by group, and correlations between numeric features. Segmenting splits summarized results so each segment gets its own row for comparison, which shows which category performs best or needs attention. Exploring distributions of categorical features shows which categories appear most often, while comparing numeric features shows trends over time.
BigQuery Studio is the workspace for this exploration, combining a SQL editor with code completion and byte estimates, embedded Python notebooks, a PySpark editor, asset version history, and Knowledge Catalog profiling. Queries can be sent through the console, the bq query command, the jobs.query or jobs.insert API methods, or client libraries. 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 persistent storage. The query plan view helps spot a stage that writes much more output than others and may need an earlier filter.
Connected analytics tools present the same aggregated metrics without moving data out of BigQuery:
Finding data quality issues means using SQL exploration to protect later analysis from bad conclusions. Missing values appear as empty or null entries that reduce usable rows for a metric and are found by counting nulls. Outliers appear as values far above or below the normal distribution, while anomalies appear as unexpected breaks in trends and relationships. SQL finds these issues by summarizing ranges, examining distributions of key variables, and comparing subgroups against each other. Common EDA visuals make the same issues easier to see, with histograms for one-variable distributions, box plots for spread and outliers, scatter plots for two-variable relationships, and bar charts for category comparisons.
Cleaning and wrangling prepare the data for fair comparison before deeper analysis. Cleaning removes or corrects problem points, such as removing outlier rows that distort an average. Wrangling creates analysis-ready features, such as converting categorical features to numeric form with one-hot encoding, label encoding, or BigQuery ML transformation tools. For large datasets, this work uses distributed processing with BigQuery ML, BigQuery DataFrames, or Managed Service for Apache Spark so cleaning and analysis run without moving all data to a single machine. Cleaned data then supports reliable business metrics and fair evaluation of categorical performance.
Exam tip: Use a histogram or bar chart to see the distribution of one variable, and use a scatter plot to see the relationship between two variables.
Sharing answers with notebooks means running BigQuery SQL inside a Python workflow where results flow directly into statistics and charts. A SQL cell in a Colab Enterprise notebook depends on a connected runtime and on BigQuery tables or Python variables for inputs. The output is automatically saved as a BigQuery DataFrame with the same name as the SQL cell title, so data flows from the BigQuery table to the SQL cell to the named DataFrame in the runtime. Later Python cells then explore structure, calculate correlations, compare categories, and build histograms, box plots, scatter plots, and bar charts. The pandas-gbq library and the BigQuery DataFrames API provide the access paths between BigQuery tables and notebook DataFrames.
The Data Science Agent is a Gemini-powered assistant that writes, explains, and troubleshoots analysis code directly within the notebook. The agent supports comma-separated values (CSV) files and BigQuery tables as data sources, and the code it produces only runs in the connected notebook runtime. A typical trend-finding workflow follows this order:
project_id:dataset.table, or type @ to search with the @mention function.The first run in a project can take approximately five to ten minutes during initial setup, the notebook must be in a supported region, and PySpark generation produces Managed Service for Apache Spark 4.0 code.
Continue with this study guide: Analyze data to answer business questions
INFORMATION_SCHEMA.JOBS in BigQuery retains the history of all jobs submitted in the last 6 months.{my_threshold}, and its output is saved as a BigQuery DataFrame named for the SQL cell title.Use partitioned tables in BigQuery when reports always filter a large table by date or integer range because partitioning divides the table by time or integer ranges so queries skip unneeded partitions. Use materialized views when the same aggregations or joins are queried repeatedly because they store cached persistent results with automatic query rewrite. Use BI Engine when dashboards need low-latency interactive exploration because it is the fast in-memory analysis service.
Enclose the Python variable name in braces inside the BigQuery SQL syntax, so the SQL cell sends the query with that value from the notebook runtime to BigQuery without moving the source data. The result set is returned to the notebook as a BigQuery DataFrame named for the SQL cell title, which then flows into later Python cells for cleaning, calculation, and charting.
Use Matplotlib when the chart needs custom layout or precise formatting because it provides low-level control of figures and axes. Use Seaborn when the goal is to show distributions, correlations, or category comparisons quickly because it provides statistical plots with clean default themes. Use Altair when the audience benefits from hovering, filtering, or zooming the data because it creates declarative, interactive charts.
Use GoogleSQL for new queries in BigQuery because it is the preferred ANSI-standard dialect that supports joins, aggregation and analytic functions, operators, and conditional expressions. The older legacy SQL dialect remains only for existing queries that already use it.
Last updated