Running an LLM in BigQuery without the bill scaling with users

Background

I run a social-analytics pipeline for a media group, and two parts of it use an LLM. The first is a batch classifier: every post and comment we pull from Facebook, Instagram, Threads and YouTube gets a sentiment label and a topic label, computed in dbt and queried like any other table. The second is interactive — a few “AI take” buttons on the Streamlit dashboard that summarize the day’s negative comments, or the week’s chatter, on demand.

Both call Gemini the same way: through BigQuery’s AI.GENERATE, inline in SQL. No remote model to register, no separate inference service — the model is just a function you call in a query. That convenience is also how I nearly walked into a bill that grows with my user count.

The day it cost $20

During the build-out, one day’s Vertex AI charge came in around $20 when every other day was near zero. Twenty dollars is not a crisis, but a number that jumps for no reason I can immediately name is worth chasing. It split cleanly into the two features — and the two have completely different cost shapes. One is bounded and basically free to keep running. The other grows every time another person opens the dashboard.

Why AI.GENERATE in the first place

Putting the classifier in the warehouse means the labels live next to the data. Analysts query fct_post_topics and fct_comments_sentiment directly; no Python service to deploy or babysit. The model call is just SQL:

select
post_id,
trim(AI.GENERATE(
  concat(
    '你是財經媒體的內容分類員。把以下貼文分到一個主題，只回下列其中一個中文詞：',
    '證券／金融／科技／產業／房市／國際／兩岸／政經／理財／生活／其他。貼文：',
    substr(post_text, 0, 600)
  ),
  endpoint => '{{ ai_endpoint() }}'
).result) as raw_topic
from to_classify
      dbt model: fct_post_topics (trimmed, prompt genericized)
    

Two small things in there earned their keep. The labels aren’t generic sentiment buckets — they’re the beats a financial newsroom actually runs (securities, banking, tech, property, cross-strait…). I tried a generic “finance / not-finance” cut first and ~70% of posts landed in one bucket with no signal. And the text is capped at 600 characters: sentiment and topic don’t need the whole body, and the cap keeps the token cost honest.

The gotcha that cost an afternoon

The endpoint has to be the fully-qualified locations/global path. Pass the short model name and BigQuery resolves it against the dataset’s own region — asia-east1 in my case — where that publisher model doesn’t exist, and you get a 404 with nothing useful in it. I centralized it in one macro so a model bump is a one-line change:

{% macro ai_endpoint(model='gemini-2.5-flash') %}
{{ return('https://aiplatform.googleapis.com/v1/projects/' ~ target.project
 ~ '/locations/global/publishers/google/models/' ~ model) }}
{% endmacro %}
      macros/ai_endpoint.sql
    

Cost shape #1: the batch classifier bounds itself

A post’s topic doesn’t change after it’s published, and a comment’s sentiment doesn’t either. So there’s no reason to re-classify anything I’ve already done. The models are incremental, and the only rows that reach the LLM are the ones with no label yet:

to_classify as (
select * from posts
{% if is_incremental() %}
where post_id not in (select post_id from {{ this }})
{% endif %}
)
      only classify rows that have never been classified
    

That makes the steady-state cost tiny. A daily run only pays for that day’s new posts and comments; a re-run with nothing new costs nothing, because zero rows reach AI.GENERATE. The only large bill is the first-ever build, which classifies the whole backlog once — about 66k comments came to roughly $0.30. After that, the job is boring and cheap, which is exactly what you want a daily job to be.

Cost shape #2: the dashboard button does not

The interactive buttons are the opposite. Each click is one Gemini call. So the bill scales with how many people use the dashboard times how often they click — and the whole point of building it was to get the whole group using it. Back-of-envelope, 100 people clicking 10 times a day is around $72/month and rising as more people log in. That’s the shape behind the $20 day, and left alone it only gets worse.

The fix: cache by content, not by user

Here’s the property that makes this cheap: each button’s prompt is built from that day’s data. Every user who clicks “summarize today’s negative comments” generates the exact same prompt string, so they should all get the exact same answer. There’s no reason to pay for it more than once a day.

So I hash the prompt and cache the result. First click of the day for a given prompt calls Gemini and writes the answer to a BigQuery table keyed by SHA256(prompt); everyone after that reads the cached row. Two layers, because the dashboard runs on Cloud Run and can have more than one instance:

@st.cache_data(ttl=3600)                    # L1: in-memory, per Cloud Run instance
def ai_generate(prompt: str) -> str:
  h = hashlib.sha256(prompt.encode("utf-8")).hexdigest()

  cached = ai_cache_lookup(h)             # L2: BigQuery, cross-instance, persistent
  if cached is not None:
      return cached

  result = run_gemini(prompt)             # AI.GENERATE(@p, endpoint => "…global…")
  ai_cache_insert(h, result)              # write-back for the next reader
  return result
      reporting/dashboards/ui.py — ai_generate (trimmed)
    

Now the cost is decoupled from the user count entirely. It’s bounded by the number of buttons times the date ranges they can be run over times the days in the period — on the order of $1/month, whether two people use the dashboard or two hundred. When the underlying data changes the next day, the prompt changes, the hash changes, and the cache naturally misses and regenerates. No invalidation logic to get wrong.

Three details that make it safe to leave alone

The cache can’t break the page. Both the read and the write are wrapped so any failure — table missing, permissions, a streaming hiccup — falls through to a normal generation or is simply ignored. Caching is a side path; a broken side path must never take down the main one.

The prompt is a query parameter, not string-concatenated. Only the endpoint is a literal (AI.GENERATE requires that); the prompt itself rides in as a bound @p parameter, so user-influenced text can’t rewrite the query.

Old rows get swept. A monthly scheduled query deletes cache rows older than 90 days — they’ll never be hit again (the key is a content hash), so this is purely housekeeping. Deleting only old rows also sidesteps BigQuery’s rule that you can’t DML a streaming-inserted row for ~30 minutes after it lands.

Takeaways