BQBrains | BigQuery DataFrames: pandas and scikit-learn at Warehouse Scale

Every BigQuery team eventually hits the same wall. An analyst wants to do something that is awkward in SQL — a rolling z-score, a pivot with dynamic columns, a quick scikit-learn baseline — so they run SELECT *, pull a few million rows into pandas on a laptop, and the warehouse stops being the source of truth. The notebook works until the table triples in size, then it dies on memory.

BigQuery DataFrames, the bigframes Python package, closes that gap. It gives you a pandas-shaped and scikit-learn-shaped API whose operations compile down to BigQuery SQL and execute on BigQuery slots. Nothing large moves to your machine. This tutorial covers the parts that matter in production: how laziness and caching actually behave, where the API diverges from pandas, how to push custom Python into the warehouse as a remote function, and how to keep costs from surprising you.

Install and connect

pip install --upgrade bigframes

import bigframes.pandas as bpd

bpd.options.bigquery.project = "my-analytics-project"
bpd.options.bigquery.location = "US"          # must match your datasets
bpd.options.display.progress_bar = None       # quieter in scripts

df = bpd.read_gbq("bigquery-public-data.new_york_taxi_trips.tlc_yellow_trips_2022")
print(df.shape)

df is a bigframes.pandas.DataFrame, not a pandas one. df.shape triggers a small query; the 40-million-row table itself never leaves BigQuery. In a notebook, bpd.options.display.repr_mode = "deferred" will show you the SQL and estimated bytes instead of executing a preview — a good habit while you are still shaping a pipeline.

If you work in a Colab Enterprise or BigQuery Studio notebook, the project and location are already set from the runtime and you can skip the options block.

Everything is lazy until it is not

bigframes builds an expression tree and only issues a query when it needs a result. These are cheap:

trips = df[df["trip_distance"] > 0]
trips = trips.assign(
    minutes=(trips["dropoff_datetime"] - trips["pickup_datetime"]).dt.total_seconds() / 60
)
trips = trips[trips["minutes"].between(1, 240)]

These trigger execution: .to_pandas(), .head(), .shape, len(), .describe(), .value_counts(), plotting, and printing a non-deferred repr. Each one is a billed BigQuery job.

The most common cost mistake is calling .head() after every cell out of pandas habit. Ten exploratory previews over a 40 TB table is ten full-ish scans. Two defences:

bpd.options.compute.maximum_bytes_billed = 50 * 1024**3   # hard 50 GiB ceiling
trips = trips.cache()                                      # materialise once, reuse

.cache() writes the current state to an anonymous temporary table with a 24-hour lifetime, then points subsequent operations at it. Cache immediately after your expensive filter-and-join step and before the exploratory branch of the notebook; that single line often cuts an afternoon's bill by an order of magnitude.

The aggregation you came for

by_hour = (
    trips.assign(hour=trips["pickup_datetime"].dt.hour)
         .groupby("hour")
         .agg({"minutes": "mean", "fare_amount": "median", "trip_distance": "sum"})
         .sort_index()
)

local = by_hour.to_pandas()   # 24 rows — safe to bring home
local.plot(y="minutes")

The rule of thumb: filter, join, and aggregate in bigframes; call .to_pandas() only on results small enough to fit comfortably in memory, then use real pandas, matplotlib, or seaborn on those. Anything you can express as to_pandas() on a million-plus rows is a design smell.

Want to see what was actually sent to BigQuery? by_hour.to_gbq() writes the result to a table, and bigframes.pandas.get_global_session().bqclient plus INFORMATION_SCHEMA.JOBS will show you the compiled SQL and bytes billed. During development, by_hour.sql prints the generated query directly — useful both for review and for handing an optimised version to a SQL-first colleague.

Where it is not pandas

Expect these differences, because they cause most first-week friction:

Ordering is not free. BigQuery tables are unordered sets. bigframes maintains an implicit order column so that pandas semantics like positional indexing work, and that bookkeeping costs slots. If you do not need row order, set bpd.options.bigquery.ordering_mode = "partial" for materially cheaper plans.
No true index. set_index works, but there is no B-tree behind it; joins are hash joins, not index lookups.
apply on axis=1 is not a loop. Row-wise Python only works when it is packaged as a remote function (below). Plain df.apply(f, axis=1) will raise unless f is decorated.
NULL vs NaN. BigQuery NULL maps to pandas NA semantics, not NaN; comparisons against nulls stay null rather than becoming False.
Type coverage is narrower. STRUCT and ARRAY columns are supported but with a smaller surface than SQL offers; deeply nested schemas are often easier to flatten in SQL first with a view.
Session locality. A session lives in one region. Cross-region joins fail; co-locate your datasets.

Pushing Python into the warehouse

When a transformation genuinely needs Python, bigframes will deploy it for you as a BigQuery remote function backed by Cloud Run functions:

@bpd.remote_function(
    cloud_function_service_account="bqframes-runner@my-project.iam.gserviceaccount.com",
    reuse=True,
)
def clean_zone(name: str) -> str:
    import re
    return re.sub(r"[^a-z ]", "", (name or "").lower()).strip()

trips = trips.assign(zone_clean=trips["pickup_zone"].apply(clean_zone))

Behind the scenes this creates a Cloud Run function and a BigQuery remote function that references it, so you need roles/cloudfunctions.developer, roles/run.invoker, roles/iam.serviceAccountUser, and a BigQuery connection (roles/bigquery.connectionAdmin to create one). Two operational notes: reuse=True avoids redeploying identical functions on every run, and the artifacts are not cleaned up automatically — track them, or you will find a graveyard of orphaned functions in six months. bpd.get_global_session().close() plus a scheduled audit of your connections dataset is the cheap version of governance here.

Remote functions are per-row RPCs. They are far slower than native SQL, so use them for the genuinely irreducible bits — a proprietary parser, an internal scoring library — and keep everything else in the compiled path.

ML without leaving the DataFrame

bigframes.ml mirrors scikit-learn's API and compiles to BigQuery ML:

from bigframes.ml.linear_model import LinearRegression
from bigframes.ml.model_selection import train_test_split

features = trips[["trip_distance", "passenger_count", "minutes"]]
target = trips["fare_amount"]
X_train, X_test, y_train, y_test = train_test_split(features, target, test_size=0.2)

model = LinearRegression()
model.fit(X_train, y_train)
print(model.score(X_test, y_test).to_pandas())

model.to_gbq("analytics.fare_model_v1", replace=True)

The model object lives in BigQuery, so it is queryable with ML.PREDICT from plain SQL, visible to your governance tooling, and deployable in a scheduled query — no pickle file on someone's laptop. There are also pipelines, ColumnTransformer, and wrappers over the Gemini text and embedding models (bigframes.ml.llm) if you are enriching text columns.

Fitting it into a real project

A pattern that holds up well in client work:

SQL or Dataform owns the modelled layer. Curated, tested, version-controlled tables.
bigframes owns exploration and feature engineering on top of that layer, in notebooks, with maximum_bytes_billed set and .cache() used deliberately.
Anything that graduates to production gets written back with .to_gbq() into a managed dataset, or converted to SQL via the .sql property and folded into the Dataform repo.

That last step matters. A notebook is a great place to discover a metric and a poor place to own one. Treat bigframes as the fast path from question to answer, and your orchestrated SQL as the place answers go to live.

When not to use it

If your team is entirely SQL-fluent and your transformations are set-based, bigframes adds a dependency and a compilation layer for no gain — write the SQL. If your data genuinely fits in memory, plain pandas with read_gbq is simpler and faster. bigframes earns its place at the specific intersection of Python-shaped work and data too big for one machine, which happens to be where most growing analytics teams spend their frustration.

Need help drawing that line — or untangling a notebook estate that has quietly become production? Our BigQuery data engineering and data architecture consulting teams do this work daily. Get in touch with the shape of your problem and we will tell you what we would do.