Connect Snowflake to VectorFin (Preview)

-- You'll need these in the Snowflake DDL below:
-- Catalog URI:  https://catalog.vectorfinancials.com/api/catalog
-- Warehouse:    vectorfin
-- Client ID:    <from dashboard>
-- Client Secret: <from dashboard — shown once>

CREATE OR REPLACE CATALOG INTEGRATION vectorfin_polaris
  CATALOG_SOURCE = POLARIS
  TABLE_FORMAT = ICEBERG
  REST_CONFIG = (
    CATALOG_URI = 'https://catalog.vectorfinancials.com/api/catalog'
    WAREHOUSE = 'vectorfin'
  )
  REST_AUTHENTICATION = (
    TYPE = OAUTH
    OAUTH_CLIENT_ID = '<your_polaris_client_id>'
    OAUTH_CLIENT_SECRET = '<your_polaris_client_secret>'
    OAUTH_ALLOWED_SCOPES = ('PRINCIPAL_ROLE:ALL')
  )
  ENABLED = TRUE;

CREATE OR REPLACE ICEBERG TABLE signals
  CATALOG = 'vectorfin_polaris'
  CATALOG_NAMESPACE = 'vectorfin'
  CATALOG_TABLE_NAME = 'signals';

CREATE OR REPLACE ICEBERG TABLE transcripts
  CATALOG = 'vectorfin_polaris'
  CATALOG_NAMESPACE = 'vectorfin.embeddings'
  CATALOG_TABLE_NAME = 'transcripts';

-- Distressed names in a bear regime, last 90 days
SELECT ticker, date, score, altman_zone, regime
FROM signals
WHERE ticker = 'AAPL'
  AND date >= DATEADD(day, -90, CURRENT_DATE())
ORDER BY date DESC;

-- Top-10 earnings-call chunks most similar to a query vector (whole corpus).
-- The literal below is the query embedding: 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.
-- CAVEAT: same model (gemini-embedding-2-preview), task_type="retrieval_query"
-- (NOT retrieval_document — docs are stored that way), 768 dims — or cosine is
-- silently wrong. No ticker filter = cross-corpus; add ticker/fiscal_period to
-- slice. The row-to-row variant below skips embedding entirely.
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 similarity
FROM transcripts
WHERE ARRAY_SIZE(embedding) = 768          -- guard ragged/NULL vectors
ORDER BY similarity DESC
LIMIT 10;

-- Row-to-row (zero-embedding): "find chunks most similar to AAPL 2024-Q4 chunk 0"
WITH q AS (
  SELECT embedding::VECTOR(FLOAT, 768) AS qv
  FROM transcripts
  WHERE ticker = 'AAPL' 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 similarity
FROM transcripts t, q
WHERE ARRAY_SIZE(t.embedding) = 768
ORDER BY similarity DESC
LIMIT 10;