# Lorelai (Snowflake Cortex Analyst) - Integration Assessment for ows-coda > **Status:** Draft | **Owner:** Ray | **Last Updated:** 2026-03-24 --- ## 1. Context The Lorelai team has built a tool on Snowflake Cortex Analyst with a Streamlit UI ([Snowflake AI Chat](https://ai.snowflake.com/sme/orchard/#/ai/chat/)). This document evaluates whether and how Lorelai / Cortex Analyst could integrate with ows-coda, and whether it would add value given Coda's existing Snowflake capabilities. --- ## 2. What is Cortex Analyst? Cortex Analyst is Snowflake's built-in natural-language-to-SQL feature. It has three components: | Component | Description | | ----------------------------- | ----------------------------------------------------------------------------------------------------------------------------- | | **LLM (hosted in Snowflake)** | Generates SQL from natural language questions. Runs inside Snowflake's infrastructure so data never leaves the platform. | | **Semantic Model** | A YAML file that defines tables, columns, metrics, joins, and business terms. Gives the LLM context to generate accurate SQL. | | **Chat UI (Streamlit)** | The interactive frontend - the page linked above. Not an API. | Cortex Analyst exposes a REST API: ``` POST /api/v2/cortex/analyst/message ``` You send a natural language question + a pointer to a semantic model (a staged YAML file), and it returns generated SQL + query results. --- ## 3. How Coda Already Solves This Coda has a mature Snowflake integration that covers the same problem space: ``` User question | v Claude (Bedrock) <-- generates SQL directly | v snowflake_query tool <-- executes SQL via SecureConnectionPool | v Snowflake <-- agent-consumable views, RBAC, session-var filtering | v Claude (Bedrock) <-- reasons over results, combines with other data sources | v User response <-- formatted answer with sources, files, deep links ``` ### Coda's Snowflake capabilities | Capability | How Coda does it | | -------------------------- | -------------------------------------------------------------------------------------------------------------------------------- | | **Natural language → SQL** | Claude generates SQL directly via `snowflake_query` tool | | **Schema awareness** | Schema index built from `AGENT_CONSUMABLE`-tagged views, refreshed via polling | | **Business logic** | Encoded in curated views (pre-computed metrics) and the system prompt (field definitions, rules) | | **Access control** | Key-pair auth, per-request session variables, source-team row access policies | | **Multi-source reasoning** | Claude combines Snowflake results with data from 6 downstream services (accounts, contracts, revenue, products, ledger, GraphQL) | | **Output formats** | Text, tables, Excel files, PDF reports, source links, deep links | --- ## 4. Integration Options ### Option A: Add a Cortex Analyst tool to Coda Add a new tool (e.g. `cortex_analyst_query`) that calls the Cortex Analyst REST API. ``` User question | v Claude (Bedrock) <-- decides to use Cortex Analyst tool | v cortex_analyst_query <-- calls POST /api/v2/cortex/analyst/message | v Cortex Analyst (Snowflake LLM) <-- generates SQL + executes it | v Claude (Bedrock) <-- reasons over results | v User response ``` **Implementation effort:** Low - a single tool handler (~100 lines), using existing Snowflake credentials. The semantic model YAML path would be a config value. **Auth:** Coda's existing key-pair auth should work with the Cortex Analyst REST API, assuming the service role has `CORTEX_USER` database role granted. ### Option B: Call Cortex SQL functions directly Use Coda's existing `snowflake_query` tool to call Cortex functions like `SNOWFLAKE.CORTEX.COMPLETE()` or `SNOWFLAKE.CORTEX.SUMMARIZE()` directly in SQL. **Implementation effort:** Zero - just needs the right grants on the service role. **Limitation:** This uses Cortex LLM functions, not Cortex Analyst specifically. No semantic model awareness. ### Option C: Adopt the semantic model concept (no Cortex Analyst) Extract the useful idea from Cortex Analyst - the semantic model - and apply it within Coda's existing architecture. This means encoding metric definitions, dimension hierarchies, and business terms either in: - The system prompt (already done for revenue fields) - View-level comments in Snowflake (already supported via schema index) - A structured metadata file that the schema index ingests **Implementation effort:** Medium - depends on how much business logic needs to be formalized beyond what's already in the system prompt and views. --- ## 5. Comparison | Factor | Coda (current) | Coda + Cortex Analyst | Cortex Analyst standalone | | --------------------------- | ---------------------------------------------------------- | ------------------------------------------------------------------ | ----------------------------------------------------- | | **SQL generation** | Claude (Bedrock) - single LLM call | Two LLM calls (Claude decides to ask Cortex, Cortex generates SQL) | Cortex LLM - single call | | **Conversation context** | Full history, account identity, prior tool results | Claude has context, Lorelai does not | Single question in isolation | | **Multi-service data** | Combines Snowflake + 6 APIs | Same (Cortex Analyst only covers Snowflake portion) | Snowflake only | | **Output richness** | Text, Excel, PDF, sources, deep links, suggestions | Same (Cortex Analyst results fed back to Claude) | Tables only | | **Latency** | 1 LLM inference + 1 Snowflake query | 2 LLM inferences + 1 Snowflake query | 1 LLM inference + 1 Snowflake query | | **Cost** | Bedrock inference + Snowflake compute | Bedrock inference + Cortex inference + Snowflake compute | Cortex inference + Snowflake compute (Cortex credits) | | **Business logic** | System prompt + curated views | System prompt + curated views + semantic model | Semantic model only | | **RBAC** | Session variables + source-team policies + Snowflake roles | Same (Cortex Analyst runs under same service role) | Depends on implementation | | **Data stays in Snowflake** | No - query results sent to Bedrock (AWS) for reasoning | Same | Yes - all processing in Snowflake | | **Setup / maintenance** | Views + system prompt + schema index | All of the above + Lorelai semantic model YAML + Cortex grants | Semantic model YAML + Streamlit app (Lorelai) | --- ## 6. Pros of integrating Lorelai 1. **Semantic model as structured business logic.** If the Lorelai team has a well-maintained semantic model with metric definitions, dimension hierarchies, and join paths, Coda could benefit from that structured knowledge - even if Claude generates the SQL itself rather than delegating to Cortex. 2. **Data residency.** Lorelai processes data entirely within Snowflake. For sensitive datasets where sending query results to Bedrock is a concern, Lorelai could generate summarized answers within Snowflake before results leave the platform. 3. **Leverage existing work.** If the Lorelai team has already invested in building and validating a semantic model, integrating it avoids duplicating that effort in Coda's system prompt or views. 4. **Fallback for complex analytical queries.** For questions that require complex joins or aggregations that Claude's SQL generation struggles with, Lorelai's semantic-model-aware SQL generation might produce better queries. --- ## 7. Cons of integrating Lorelai 1. **Redundant LLM layer.** Claude already generates SQL. Adding Lorelai means two LLMs in the loop - Claude decides what to ask, Lorelai generates SQL, results come back to Claude for formatting. This adds latency and cost with no guarantee of better SQL. 2. **Loss of conversation context.** Lorelai receives each question in isolation. It doesn't know the user just asked about a specific account, or that "last period" refers to a particular `statement_period_id`. Claude would need to heavily rewrite the user's question to make it self-contained before sending to Lorelai. 3. **Snowflake-only.** Lorelai can only answer questions about data in Snowflake. Most Coda questions require combining Snowflake data with contract terms, payment schedules, ledger balances, or product metadata from other services. Lorelai can't help with any of that. 4. **Additional cost.** Lorelai consumes Snowflake credits for Cortex LLM inference on top of warehouse compute. This is in addition to Bedrock costs, not instead of. 5. **Semantic model maintenance.** The YAML semantic model must be kept in sync with underlying table schemas. If the Lorelai team's model drifts or covers a different subset of data than Coda needs, integration creates a dependency on their maintenance cadence. 6. **No differentiated capability.** For the data Coda already has access to, Lorelai doesn't produce meaningfully better results than Claude generating SQL directly with schema context. The semantic model concept is valuable, but can be adopted without Lorelai (see Option C). --- ## 8. If we do integrate (implementation sketch) For reference, if the decision is made to integrate despite the above: ```typescript // apps/server/src/ai/tools/snowflake/cortex-analyst.ts const CORTEX_ANALYST_TOOL = { name: "cortex_analyst_query", description: "Ask a natural language question against the Snowflake semantic model", input_schema: { type: "object", properties: { question: { type: "string", description: "A self-contained question (include all context - account IDs, date ranges, metric names)", }, }, required: ["question"], }, }; ``` **Requirements:** - Grant `CORTEX_USER` database role to `OWS_CODA_SVC` - Stage the semantic model YAML in an accessible location - Add `SNOWFLAKE_SEMANTIC_MODEL_PATH` to config - Implement the handler as a Snowflake REST API call (not a SQL query) - Add the tool to the deferred tools list (not loaded by default - only when explicitly needed) **Estimated effort:** 1-2 days for a basic integration, plus testing and validation against the semantic model's coverage.