How much time do you spend fighting your tools instead of solving problems? Every data scientist has been there: downsampling a dataset because it won’t fit into memory or hacking together a way to let a business user interact with a machine learning model.

The ideal environment gets out of the way so you can focus on the analysis. This article covers eight practical methods in BigQuery designed to do exactly that, from using AI-powered agents to serving ML models straight from a spreadsheet.

1. Machine Learning in your Spreadsheets

BQML training and prediction from a Google Sheet

Many data conversations start and end in a spreadsheet. They’re familiar, easy to use, and great for collaboration. But what happens when your data is too big for a spreadsheet, or when you want to run a prediction without writing a bunch of code? Connected Sheets helps by letting you analyze billions of rows of BigQuery data from the Google Sheets interface. All calculations, charts, and pivot tables are powered by BigQuery behind the scenes.

Taking it a step further, you can also access models you’ve built with BigQuery Machine Learning (BQML). Imagine you have a BQML model that predicts housing prices. With Connected Sheets, a business user could open a Sheet, enter data for a new property (square footage, number of bedrooms, location), and a formula can call a BQML model to return a price estimate. No Python or API wrangling needed - just a Sheets formula calling a model. It’s a powerful way to expose machine learning to non-technical teams.

• Get Started: Use Connected Sheets with BigQuery
• Tutorial: Create machine learning models with BQML

2. No Cost BigQuery Sandbox and Colab Notebooks

Getting started with enterprise data warehouses often involves friction, like setting up a billing account. The BigQuery Sandbox removes that barrier, letting you query up to 1 terabyte of data per month. No credit card required. It’s a great, no-cost way to start learning and experimenting with large-scale analytics.

As a data scientist, you can access your BigQuery Sandbox from a Colab notebook. With just a few lines of authentication code, you can run SQL queries right from a notebook and pull the results into a Python DataFrame for analysis. That same notebook environment can even act as an AI partner to help plan your analysis and write code.

• Get Started: BigQuery Sandbox Documentation
• Example Notebook: Use BigQuery in Colab

3. Your AI-Powered Partner in Colab Notebooks

Data Science Agent in a Colab Notebook (sequences shortened, results for illustrative purposes)

Colab notebooks are now an AI-first experience designed to speed up your workflow. You can generate code from natural language, get automatic error explanations, and chat with an assistant right alongside your code.

Colab notebooks also have a built-in Data Science Agent. Think of it as an ML expert you can collaborate with. Start with a dataset - like a local CSV or a BigQuery table - and a high level goal, like “build a model to predict customer churn”. The agent creates a plan with suggested steps (e.g. data cleaning, feature engineering, model training) and writes the code.

And you are always in control. The agent generates code directly in notebook cells, but doesn’t run anything on its own. You can review and edit each cell before deciding what to execute, or even ask the agent to rethink its approach and try different techniques.

• Get Started: Try the Data Science Agent

4. Scale your Pandas Workflows with BigQuery DataFrames

Many data scientists live in notebooks and use pandas DataFrames for data manipulation. But there’s a well-known limit: all the data you process needs to fit into your machine’s memory. MemoryError exceptions are all too common, forcing you to downsample your data early on.

This is the exact problem BigQuery DataFrames solves. It provides a Python API intentionally similar to pandas. Instead of running locally, it translates your commands into SQL and executes them on the BigQuery engine. Meaning you can work with terabyte-scale datasets from your notebook, with a familiar API, and no worries about memory constraints. The same concept applies to model training, with a scikit-learn-like API that pushes model training to BigQuery ML.

• Get Started: BigQuery DataFrames Quickstart
• Samples: Check out sample notebooks on GitHub

5. Spark ML in BigQuery Studio Notebooks

Sample Spark ML notebook in BigQuery Studio

Apache Spark is a useful tool from feature engineering to model training, but managing the infrastructure has always been a challenge. Serverless for Apache Spark lets you run Spark code, including jobs using libraries like XGBoost, PyTorch, and Transformers, without having to provision a cluster. You can develop interactively from a notebook directly within BigQuery, letting you focus on model development, while BigQuery handles the infrastructure.

You can use Serverless Spark to operate on the same data (and the same governance model) in your BigQuery warehouse.

• Get started: Run PySpark in a BigQuery Studio notebook

6. Add External Context with Public Datasets

Top 5 trending terms in the Los Angeles Area in early July 2025

Your first-party data tells you what happened, but can’t always explain why. To find that context, you can join your data with a large collection of public datasets available in BigQuery.

Imagine you’re a data scientist for a retail brand. You see a spike in sales for a raincoat in the Pacific Northwest. Was it your recent marketing campaign, or something else? By joining your sales data with the Google Trends dataset in BigQuery, you can quickly see if search queries for “waterproof jacket” also surged in the same region and period.

Or let’s say you’re planning a new store. You can use the Places Insights dataset to analyze traffic patterns and business density in potential neighborhoods, layering it on top of your customer information to choose the best location. These public datasets let you build richer models that account for real-world factors.

• Get Started: Query a Public Dataset

7. Geospatial Analytics at Scale

BigQuery Geo Viz map of a hurricane, using color to indicate radius and wind speed

Building location-aware features for a model can be complex, but BigQuery simplifies this by supporting a GEOGRAPHY data type and standard GIS functions within SQL. This lets you engineer spatial features right at the source. For example, if you are building a model to predict real estate prices, you could use a function like ST_DWithin to calculate the number of public transit stops within a one mile radius for each property. You can then use that value directly as input to your model.

You can take this further with Google Earth Engine integration, which brings petabytes of satellite imagery and environmental data into BigQuery. For that same real estate model, you could query Earth Engine’s data to add features like historical flood risk or even density of tree cover. This helps you build much richer models by augmenting your business data with planet-scale environmental information.

• Get Started: Geospatial Analytics with BigQuery
• Learn more: Earth Engine in BigQuery

8. Make Sense of Log Data

Most people think of BigQuery for analytical data, but it’s also a powerful destination for operational data. You can route all of your Cloud Logging data to BigQuery, turning unstructured text logs into queryable resources. This allows you to run SQL across logs from all your services to diagnose issues, track performance, or analyze security events.

For a data scientist, this Cloud Logging data is a rich source to build predictions from. Imagine investigating a drop in user activity. After identifying an error message in the logs, you can use BigQuery Vector Search to find semantically similar logs, even if they don’t contain the exact same text. This could help reveal related issues, like “user token invalid” and “authentication failed”, that are part of the same root cause. You could then use this labeled data to train an anomaly detection model that flags patterns proactively.

• Get Started: Analyze log data with BigQuery

Conclusion

Hopefully, these examples spark some new ideas for your next project. From scaling pandas DataFrames to feature engineering with geography data, the goal is to help you work at scale with familiar tools.

Ready to give one a shot? You can start exploring at no cost today in the BigQuery Sandbox!

Author: Jeff Nelson, Developer Relations Engineer