BigQuery AI.COUNT_TOKENS Goes GA to Estimate Prompt Costs

Finding out what an AI function costs by reading the bill is a rite of passage. BigQuery AI.COUNT_TOKENS lets you skip it. Google's release notes list the function as generally available (GA) on October 27, 2026, so you can count input tokens in SQL before pointing AI.GENERATE or AI.IF at a few million rows.

What happened: AI.COUNT_TOKENS is generally available

The release note says the function "estimates the token count of a text string." It entered Preview on May 12, 2026. Per the reference page:

• It takes a STRING prompt and an optional endpoint, the Gemini model whose tokenization rules are used. Leave it out and you get the default model of AI.GENERATE.
• It returns a STRUCT: result (an INT64 input token count) and full_response (JSON with the modality and token count). Both are NULL if the input is NULL or an API error occurs.
• It doesn't incur Vertex AI charges. It's still a BigQuery query, so normal query compute applies.

The reference page's first example counts one sentence:

# The result is approximately 11.
SELECT
  AI.COUNT_TOKENS("Token count isn't always equal to word count.").result AS num_tokens;

Its second example expands the whole struct over a public table:

SELECT
  review,
  AI.COUNT_TOKENS(review).*
FROM
  `bigquery-public-data.imdb.reviews`
LIMIT 2;

The docs show the first two reviews at 180 and 246 tokens, each with a full_response that breaks the count down by modality (TEXT). The point: token count isn't word count, so guessing from row length won't do.

Run BigQuery AI.COUNT_TOKENS over a whole table

This is the useful move for a data team: a bigger version of the second example, built on the pattern in Google's cost-management post. Use the exact prompt template and endpoint you plan to send.

WITH counted AS (
  SELECT
    AI.COUNT_TOKENS(
      CONCAT('Classify the sentiment of this review as positive, negative, or neutral: ', review_text),
      endpoint => 'gemini-3.5-flash').result AS tokens
  FROM `<your-project>.<your-dataset>.product_reviews`
)
SELECT
  COUNT(*) AS row_count,
  SUM(tokens) AS total_input_tokens,
  AVG(tokens) AS avg_tokens_per_row,
  MAX(tokens) AS max_tokens_in_a_row,
  COUNTIF(tokens IS NULL) AS rows_without_a_count,
  SUM(tokens) / 1e6 * <input-price-per-million-usd> AS est_input_usd
FROM counted;

Replace <input-price-per-million-usd> with the input rate for your endpoint from the Gemini pricing page. Watch rows_without_a_count: a NULL means an empty input or an API error, and those rows will need a second look. max_tokens_in_a_row finds the one monster review that blows past your assumptions.

Check the estimate against a small real run

Run the real function on a slice and compare. The LIMIT here returns arbitrary rows, so pick a slice that represents your data.

SELECT
  AI.GENERATE(
    CONCAT('Classify the sentiment of this review as positive, negative, or neutral: ', review_text),
    endpoint => 'gemini-3.5-flash'
  ).result AS sentiment
FROM (
  SELECT review_text
  FROM `<your-project>.<your-dataset>.product_reviews`
  LIMIT 200
);

After it finishes, the Job information tab shows input, output, thought and cache token counts for Gemini models, per the generative AI functions overview. Your estimate should land at or just under the real input count. The output and thought numbers give you a real ratio to scale up.

What the BigQuery token count leaves out

The number is a floor, not a quote:

• Output and thinking tokens. The reference page says the result excludes both, and output tokens cost more than input.
• Instructions the AI function adds. Google's post says the count excludes them, so it's a lower bound.
• Non-text inputs. The function takes a STRING, so images and other modalities aren't covered.
• A mismatched endpoint. Tokenization follows the model you name. Count with one endpoint and call another, and the numbers drift.

Pair the count with token quotas

On October 14, 2026, Google described daily token quotas for BigQuery generative AI functions. Defaults are 200 billion input tokens per project and 150 billion per user per day, plus 20 billion and 15 billion output tokens. Output quotas include thinking tokens, and embedding functions such as AI.EMBED aren't covered. Your count and sample run tell you which limits are sane.

What to do on Monday morning

1. Pick the endpoint you will actually call and put it in endpoint => everywhere.
2. Run the table-level count with your real prompt template, and put the estimate next to the job request.
3. Run a 200-row sample and compare it with Job information to get your output-to-input ratio.
4. Set per-user quotas, and cap output with max_output_tokens where the function supports it.

A token count you can run in SQL turns an AI batch from a surprise on the invoice into a number someone approves before the job starts, and that habit is what keeps production AI pipelines funded.