VectorFin in BigQuery

Imagine we share the `transcripts` embeddings (and the raw transcript text) with you as a BigQuery Analytics Hub source or a GCS bucket. Here is how you query them with BigQuery-native vector search, with no separate vector database and no copies. The embeddings are 768-dim Google gemini-embedding-2-preview vectors, the same model you reproduce below for the query side.

Create the embedding model connection

Register a remote model over the Vertex AI Gemini embedding endpoint so BigQuery can embed your query text in-database. Use the same model and dimensionality the corpus was embedded with.

CREATE OR REPLACE MODEL `[your_project].[your_dataset].gemini2_embed`
REMOTE WITH CONNECTION DEFAULT
OPTIONS (ENDPOINT = 'gemini-embedding-2-preview');

Run vector search

Embed the question with ML.GENERATE_EMBEDDING (RETRIEVAL_QUERY, 768 dims), then VECTOR_SEARCH the shared transcript embeddings by cosine distance. Aggregating per (ticker, fiscal_period) tells you which earnings calls are most relevant and which chunks inside them matched.

SELECT
  base.ticker            AS ticker,
  base.fiscal_period     AS fiscal_period,
  MIN(distance)          AS best_chunk_distance,
  ARRAY_AGG(base.chunk_idx ORDER BY distance LIMIT 10) AS relevant_chunk_indexes,
  COUNT(*)               AS chunks_in_top_k
FROM VECTOR_SEARCH(
  TABLE `[your_project].[your_dataset].transcripts`,
  'embedding',
  (
    SELECT ml_generate_embedding_result AS embedding
    FROM ML.GENERATE_EMBEDDING(
      MODEL `[your_project].[your_dataset].gemini2_embed`,
      (SELECT 'How are operating margins trending and what is driving the change?' AS content),
      STRUCT(TRUE AS flatten_json_output,
             'RETRIEVAL_QUERY' AS task_type,
             768 AS output_dimensionality)
    )
  ),
  top_k => 100,
  distance_type => 'COSINE'
)
GROUP BY ticker, fiscal_period
ORDER BY best_chunk_distance
LIMIT 20;

Rehydrate the matched text

The search returns chunk indexes, not prose. Join the ranked indexes back to the raw transcript text (shared alongside the embeddings) to read the actual paragraphs. UNNEST the index array WITH OFFSET to keep rank order, then keep the closest few.

WITH vector_matches AS (
  SELECT
    base.ticker        AS ticker,
    base.fiscal_period AS fiscal_period,
    ARRAY_AGG(base.chunk_idx ORDER BY distance LIMIT 100) AS relevant_chunk_indexes
  FROM VECTOR_SEARCH(
    TABLE `[your_project].[your_dataset].transcripts`,
    'embedding',
    (
      SELECT ml_generate_embedding_result AS embedding
      FROM ML.GENERATE_EMBEDDING(
        MODEL `[your_project].[your_dataset].gemini2_embed`,
        (SELECT 'How are operating margins trending and what is driving the change?' AS content),
        STRUCT(TRUE AS flatten_json_output,
               'RETRIEVAL_QUERY' AS task_type,
               768 AS output_dimensionality)
      )
    ),
    top_k => 100,
    distance_type => 'COSINE'
  )
  WHERE base.ticker = 'AAPL' AND base.fiscal_period = '2026-Q2'
  GROUP BY ticker, fiscal_period
)
SELECT
  t.speaker,
  t.paragraph_number,
  t.paragraph_text
FROM vector_matches v
CROSS JOIN UNNEST(v.relevant_chunk_indexes) AS chunk_idx WITH OFFSET AS rank
JOIN `[your_project].whystock_actuals.stock_earning_call_transcripts` t
  ON  t.ticker           = v.ticker
  AND t.fiscal_period    = v.fiscal_period
  AND t.paragraph_number = chunk_idx
WHERE rank < 5
ORDER BY rank;

Why row 3 is an Operator intro: semantic similarity is not factual answering

It is completely irrelevant to a human, but to an embedding model it is a mathematically logical result. This is a classic example of the difference between semantic similarity and factual answering. Here is why our vector search pulled this specific chunk, and how to engineer your pipeline to handle it.

Why the model thinks it is relevant

The analyst vector association: gemini-embedding-2-preview was trained on mountains of financial text. It has learned that an analyst like Amit Daryanani at Evercore ISI frequently covers Apple and routinely asks highly specific questions about revenue growth and margins, so in high-dimensional vector space the concept of "Evercore analyst" sits extremely close to "financial metrics" and "margin drivers". Conversational proximity: in the structure of an earnings call the operator's introduction is the immediate precursor to the actual question, and because our embeddings are chunked strictly at the paragraph level the model scores this intro highly, its semantic neighborhood is identical to the target topic even though it lacks the factual payload. Distance context: a cosine distance of 0.29 is decent, but it is not a dead ringer (which would sit closer to 0.05 to 0.15). The model is essentially saying it is about 70% confident this chunk belongs to a conversation about revenue.

How to fix this in a trading pipeline

If you are passing raw array text directly into a quantitative trading algorithm, noise like this will break your signal. You need to layer in defensive filtering.