How to Count Tokens in BigQuery SQL With AI.COUNT_TOKENS

Your AI.GENERATE query is about to run over a million rows, and everything you know about the bill is "probably fine". By the end of this guide you'll be able to count tokens in BigQuery with AI.COUNT_TOKENS, choose which model's tokenizer you're counting for, and size a whole batch from one SELECT before a single Gemini call is made.

AI.COUNT_TOKENS returns a STRUCT with an INT64 result and a JSON full_response. According to the function reference, it runs in BigQuery, doesn't incur charges in Vertex AI, and counts input tokens only.

1. Count tokens in BigQuery for one string

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

The docs put this at roughly 11 tokens. The .result matters: without it you get the whole struct back.

2. Count tokens for a whole table column

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

The .* expands the struct into two columns, result and full_response. In the docs' example, one review returns a result of 180, and its full_response shows a single TEXT modality with a tokenCount of 180. Keep full_response only if you want the modality breakdown, and use .result when you just want a number to sum.

3. Pick the endpoint whose tokenizer you'll pay for

The optional endpoint argument is a STRING literal naming the model whose tokenization rules apply. If you omit it, you get the default model used by AI.GENERATE, which the AI.GENERATE reference lists as gemini-3.5-flash-lite.

SELECT
  AI.COUNT_TOKENS(review, endpoint => 'gemini-3.5-flash-lite').result AS tokens_flash_lite,
  AI.COUNT_TOKENS(review, endpoint => 'gemini-3.5-flash').result AS tokens_flash
FROM `bigquery-public-data.imdb.reviews`
LIMIT 5;

Because the endpoint sets the tokenization rules, compare counts instead of assuming two models agree. It has to be a literal, so you can't switch it per row. The counting page doesn't list valid endpoint values, so I took these names from the AI.GENERATE supported list. Check that list before you copy them.

4. Estimate the cost of an AI.GENERATE batch before running it

Count the exact prompt you'll send, including your instruction prefix, then sum it.

DECLARE usd_per_1m_input_tokens FLOAT64 DEFAULT NULL;  # replace NULL with your model's input rate

WITH prompts AS (
  SELECT CONCAT('Summarize in one sentence: ', body) AS prompt
  FROM `bigquery-public-data.bbc_news.fulltext`
),
counted AS (
  SELECT
    prompt,
    AI.COUNT_TOKENS(prompt, endpoint => 'gemini-3.5-flash-lite').result AS tokens
  FROM prompts
)
SELECT
  COUNT(*) AS total_rows,
  COUNTIF(prompt IS NULL) AS null_prompts,
  COUNTIF(prompt IS NOT NULL AND tokens IS NULL) AS api_errors,
  SUM(tokens) AS input_tokens,
  ROUND(SUM(tokens) / 1e6 * usd_per_1m_input_tokens, 4) AS est_input_usd
FROM counted;

The rate is deliberately left as NULL, so the dollar column stays empty until you fill in the real number from the Vertex AI pricing page. Treat the result as a floor. It leaves out output tokens and thinking tokens, and the AI.GENERATE reference notes that Gemini 2.5 and later models charge for thinking.

5. Check the estimate against Job information

Run the real AI.GENERATE query on a LIMIT 100 sample, then open Job information in the Query results pane. The generative AI overview says it shows input, output, thought and cache token counts. Compare the input count with step 4 on the same rows, then scale up with what you learned. Google recommends this sample-first approach in its token quotas announcement, as covered by InfoWorld.

Gotchas

• NULL means two different things. result is NULL if the input is NULL or an API error occurs. SUM skips NULL, so failed rows quietly shrink your total. That's why step 4 counts them separately.
• Locations and permissions are undocumented here. The AI.COUNT_TOKENS page doesn't state supported locations or IAM permissions. AI.GENERATE runs in Gemini regions plus the US and EU multi-regions, but don't assume the counter matches. Test one row in your dataset's location first.
• It's a lower bound. Per InfoWorld, it also ignores instructions that AI functions add and non-text inputs like images.
• Free of Vertex AI charges isn't free of scanning. It's still a BigQuery query over your table, so sample before you point it at something huge.

One SELECT before the batch turns "probably fine" into a number you can put in a handover doc, which is what a production AI workload on BigQuery data needs.