The Bank of New York Mellon Corporation Earnings Embeddings in Snowflake

-- PREVIEW / WORK IN PROGRESS: the VectorFin Snowflake path is not yet GA.
-- Mount VectorFin Iceberg catalog in Snowflake.
-- No EXTERNAL_VOLUME: the Polaris catalog vends short-lived, prefix-scoped
-- GCS credentials per read, so Snowflake needs no GCS grant of its own.
CREATE OR REPLACE ICEBERG TABLE BK_embeddings
  CATALOG = 'vectorfin_polaris'
  CATALOG_NAMESPACE = 'vectorfin.embeddings'
  CATALOG_TABLE_NAME = 'transcripts';

-- Vector search over BK earnings embeddings.
-- The Iceberg list<float> 'embedding' column surfaces as a Snowflake ARRAY;
-- cast it to VECTOR(FLOAT, 768) at query time (no Cortex license needed —
-- external embeddings are supported as-is).
--
-- Supply the query vector as a bracket-array literal: embed your query text
-- out-of-band with gemini-embedding-2-preview, task_type="retrieval_query",
-- output_dimensionality=768, then paste its 768 floats. Snowflake's AI_EMBED
-- can't produce Gemini vectors and VECTOR bind variables aren't supported, so
-- the literal is the only path.
-- CAVEAT: the query vector MUST use the same model (gemini-embedding-2-preview),
-- task_type="retrieval_query" (NOT retrieval_document — docs are stored that
-- way), and 768 dims, or cosine similarity is silently wrong.
SELECT ticker, fiscal_period, chunk_idx,
       VECTOR_COSINE_SIMILARITY(
         embedding::VECTOR(FLOAT, 768),
         [0.0123, -0.0456, 0.0789 /* ...768 floats from retrieval_query... */]::VECTOR(FLOAT, 768)
       ) AS score
FROM   BK_embeddings
WHERE  ARRAY_SIZE(embedding) = 768   -- guard against ragged/NULL vectors
  AND  ticker = 'BK'          -- per-ticker slice to bound the scan; drop
  AND  fiscal_period = '2024-Q4'     -- ticker/period filters to search wider
ORDER  BY score DESC
LIMIT  10;

-- No query model handy? Rank by similarity to a chunk you already store —
-- this skips the query-embedding step (the stored vector is the query).
WITH q AS (
  SELECT embedding::VECTOR(FLOAT, 768) AS qv
  FROM   BK_embeddings
  WHERE  ticker = 'BK' AND fiscal_period = '2024-Q4' AND chunk_idx = 0
)
SELECT t.ticker, t.fiscal_period, t.chunk_idx,
       VECTOR_COSINE_SIMILARITY(t.embedding::VECTOR(FLOAT, 768), q.qv) AS score
FROM   BK_embeddings t, q
WHERE  ARRAY_SIZE(t.embedding) = 768
ORDER  BY score DESC
LIMIT  10;