We Built Cortex Analyst on Real Financial Data. Here's What Actually Happened.

Nobody writes about building Cortex Analyst on production data. The tutorials use toy datasets. The demos show clean schemas. The documentation assumes you're starting from scratch.

We weren't starting from scratch. We had 90 dbt models (at that point in the engagement), six product lines, 11 regional pricing multipliers, and production-validated revenue data across six product lines. We wanted to know if a finance stakeholder could ask "what's net revenue by product for Q3?" in plain English and get a correct answer — without writing SQL, without a data analyst in the loop, and without a demo schema that was designed to behave.

This is what we found out.

Quick answer: Cortex Analyst on real dbt financial data works, with meaningful caveats. Semantic view definitions require more precision than the docs suggest. Query results validated against Sigma — they matched. The biggest surprise wasn't the AI layer; it was how much work the semantic layer itself demands. Plan for that.

---

Why conversational analytics on financial data is worth attempting

The org we were working with — a cloud security company — had a working Sigma environment by the time we started this experiment. Dashboard loads that used to take 60 seconds now ran in under 3. Finance could update pricing without filing a Jira ticket. The migration was done.

But there was still a class of questions that required either a data analyst or someone patient enough to find the right Sigma workbook and apply the right filters. "How much of our Q3 revenue came from accounts in the POC segment?" "What's our effective discount rate across product categories this month?" Reasonable questions. Minutes to answer if you know where to look. Invisible if you don't.

Cortex Analyst's pitch for this scenario: you already have the dbt models. You build a semantic view on top of them — telling Snowflake what the facts, dimensions, and metrics mean — and Cortex translates natural language queries into SQL against that semantic view. The SQL runs in Snowflake. The results come back to whatever UI you've built.

The part nobody writes about: building a semantic view on a real financial schema is not like building one on orders and customers.

---

The semantic view, built on a real dbt mart

The source model was fct_product__plan_monthly_revenue_by_product_v2 — a mart-layer table with rows at the grain of account × plan × product × month. It was the output of a 51-model dbt architecture that had been validated to 0.002% accuracy against historical invoices.

We wrapped a Snowflake DDL macro around the semantic view definition. The full structure:

{% macro create_sv_product__plan_monthly_revenue() %}
  {% set sql %}
    CREATE OR REPLACE SEMANTIC VIEW {{ db }}.{{ schema }}.sv_product__plan_monthly_revenue

      TABLES (
        revenue AS {{ db }}.{{ schema }}.fct_product__plan_monthly_revenue_by_product_v2
          PRIMARY KEY (account_id, plan_id, period_month, product_id)
      )

      FACTS (
        revenue.revenue_gross AS revenue_gross,
        revenue.revenue_net AS revenue_net,
        revenue.usage_count AS usage_count
      )

      DIMENSIONS (
        revenue.account_id AS account_id,
        revenue.period_month AS period_month,
        revenue.plan_name AS plan_name,
        revenue.account_type AS account_type,
        revenue.account_segment AS account_segment,
        revenue.is_poc AS is_poc,
        revenue.product_name AS product_name,
        revenue.product_category AS product_category,
        revenue.team_name AS team_name
      )

      METRICS (
        revenue.total_gross_revenue AS SUM(revenue.revenue_gross),
        revenue.total_net_revenue AS SUM(revenue.revenue_net),
        revenue.total_ela_discount AS SUM(revenue.revenue_gross) - SUM(revenue.revenue_net),
        revenue.account_count AS COUNT(DISTINCT revenue.account_id),
        revenue.effective_discount_rate AS
          (SUM(revenue.revenue_gross) - SUM(revenue.revenue_net))
          / NULLIF(SUM(revenue.revenue_gross), 0) * 100
      )

      COMMENT = 'Plan-level monthly revenue by product.'
  {% endset %}

  {% do run_query(sql) %}
{% endmacro %}

The macro runs via dbt run-operation. It's idempotent — CREATE OR REPLACE means re-running it on schema changes is safe. We called it from a post-hook on the source mart so the semantic view stayed current as the model evolved.

---

The Streamlit app

We ran the Cortex Analyst interface as a Streamlit-in-Snowflake app — no external infrastructure, no API keys to manage outside Snowflake, native access to the semantic view.

The app structure was straightforward: a text input, the Cortex Analyst API call using the snowflake.cortex.complete inference pattern, and automatic chart rendering based on what came back.

import streamlit as st
import pandas as pd
from snowflake.snowpark.context import get_active_session

session = get_active_session()

def query_cortex_analyst(question: str, semantic_view: str) -> dict:
    """Send NL question to Cortex Analyst, get back SQL + results."""
    response = session.sql("""
        SELECT SNOWFLAKE.CORTEX.ANALYST(
            ?,
            OBJECT_CONSTRUCT('semantic_view', ?)
        ) AS response
    """, params=[question, semantic_view]).collect()
    return response[0]["RESPONSE"]

def render_chart(df: pd.DataFrame):
    """Auto-detect chart type: temporal → line, categorical → bar."""
    date_cols = [c for c in df.columns if any(t in c.lower() for t in ["month", "date", "period"])]
    revenue_cols = [c for c in df.columns if any(t in c.lower() for t in ["revenue", "amount", "total"])]

    if date_cols and revenue_cols:
        st.line_chart(df.set_index(date_cols[0])[revenue_cols])
    elif revenue_cols:
        cat_col = [c for c in df.columns if c not in revenue_cols][0] if len(df.columns) > 1 else None
        if cat_col:
            st.bar_chart(df.set_index(cat_col)[revenue_cols])

The auto-chart logic covered the most common financial query patterns without configuration. Temporal queries (revenue by month) got line charts. Categorical breakdowns (revenue by product, by segment) got bar charts. Everything else got a table.

---

What questions actually worked

Cortex Analyst handled aggregation and grouping questions well — the kind of queries that translate naturally into SUM ... GROUP BY:

"What is total gross revenue by month for the current year?"
"Show net revenue by product category"
"Which account segments have the highest revenue?"
"Compare effective discount rate by plan name"
"How many unique accounts are active this quarter?"

The generated SQL was readable. A representative example for "net revenue by product for 2025":

SELECT
    product_name,
    SUM(revenue_net) AS total_net_revenue
FROM sv_product__plan_monthly_revenue
WHERE YEAR(period_month) = 2025
GROUP BY product_name
ORDER BY total_net_revenue DESC

We validated these results against Sigma. They matched. That's the part that actually mattered — not whether the app looked impressive, but whether it could produce numbers a finance team would trust.

---

The gotchas nobody mentions

Custom calendar queries were unreliable. Our dbt models use a 4-4-5 retail calendar — quarters that don't match ISO quarters. The semantic view has no mechanism to communicate this. When a user asked "what's Q3 revenue?", Cortex Analyst used calendar Q3 (July–September), not the company's Q3. The SQL was syntactically correct. The number was wrong. We added a note to the Streamlit UI: "This tool uses calendar months. For company quarter numbers, use Sigma." Not elegant, but honest.

Complex filter combinations degraded quality. Simple group-by questions worked consistently. Multi-condition questions like "net revenue for POC accounts in the Detection product category excluding accounts with custom discounts" produced SQL that was sometimes correct and sometimes subtly wrong — usually a missing filter or an incorrect aggregation order. We couldn't predict when it would fail without running the output against a known answer. For exploratory queries, acceptable. For Finance sign-off on a number, not acceptable.

Derived metrics need explicit definition. Effective discount rate — (gross - net) / gross * 100 — worked because we defined it explicitly in the METRICS block. Any ratio or derived calculation that we didn't pre-define either produced incorrect SQL or caused Cortex Analyst to refuse the query with an "I can't answer that" response. The lesson: every metric a user might want to ask about needs to exist in the semantic view. You can't rely on Cortex inferring ratios from raw facts.

The semantic view is a snapshot. It reflects the mart schema at creation time. When we added a new dimension column to the underlying dbt model, the semantic view didn't update automatically. CREATE OR REPLACE fixed it, but that requires knowing the change happened. In a team environment without the post-hook pattern we used, semantic views can silently lag behind the data model.

Snowflake Cortex evolves fast. The behavior we observed was current as of our engagement (November 2025–March 2026). Cortex Analyst was still in relatively early availability. API response shapes, supported metric expressions, and query quality all change with Snowflake releases. Treat the specifics here as "what we observed at the time" — validate against current documentation before building on them.

---

What the semantic view definition actually requires

The most underestimated part of this work was writing the semantic view itself.

The METRICS block is where precision matters most. Snowflake's semantic view DDL is strict about metric expressions — you can't use arbitrary SQL functions, and the reference syntax (revenue.metric_name) has specific rules that differ from standard SQL aliases. We iterated on effective_discount_rate three times before the DDL accepted it without error:

-- First attempt — rejected (division not directly supported as top-level metric)
revenue.effective_discount_rate AS
  (revenue_gross - revenue_net) / revenue_gross * 100

-- Second attempt — rejected (column references without table alias)
revenue.effective_discount_rate AS
  (SUM(revenue_gross) - SUM(revenue_net)) / SUM(revenue_gross) * 100

-- Third attempt — accepted
revenue.effective_discount_rate AS
  (SUM(revenue.revenue_gross) - SUM(revenue.revenue_net))
  / NULLIF(SUM(revenue.revenue_gross), 0) * 100

The NULLIF matters: without it, accounts with zero gross revenue produce a division-by-zero error that surfaces as a confusing Cortex Analyst failure rather than a clear SQL error.

The COMMENT field is not cosmetic. Cortex Analyst uses the view-level and column-level comments to improve query generation. A view-level comment of 'Monthly revenue by product.' produced noticeably better results than no comment for ambiguous queries. We added comments to every dimension that had non-obvious business meaning:

DIMENSIONS (
  revenue.account_segment AS account_segment
    COMMENT 'Customer segment: Internal, External POC, External Upside, Unknown',
  revenue.is_poc AS is_poc
    COMMENT 'Boolean flag — true if this is a proof-of-concept account',
  ...
)

This is the same principle as good dbt model documentation — the AI layer is only as good as the metadata you give it.

---

The weekend it came together

Building the Streamlit prototype was a weekend project — the kind you start on a Saturday afternoon expecting to spend two hours on and end up deep in at midnight. The financial data we were querying had been through months of validation. Seeing it come back from a plain-English question was a different kind of experience.

"Not sure how to feel if data gives me joy during the weekend" — actual Slack message, actual Sunday.

That's the honest version of what Cortex Analyst felt like on real data that we trusted. The caveats are real. The fiscal calendar problem is real. The limit on complex filters is real. But so is watching a finance stakeholder type "which product had the highest net revenue last month?" and get the right number back without opening a SQL editor.

The self-service analytics work we'd done with Sigma input tables — described in detail in the B-8 post on empowering finance teams — was already changing who could answer questions in the org. The semantic layer was a further step in the same direction: not replacing analysts, but reducing the queue of questions that required one.

---

Lessons for production use

Build the semantic view on a validated mart, not raw tables. The data quality problem is separate from the NL-to-SQL problem. We had confidence in our answers because the source model had been validated to 0.002% accuracy. Without that foundation, you can't distinguish a Cortex Analyst failure from a data quality issue.

Pre-define every metric users will want. Don't assume Cortex will infer derived calculations. Gross margin, discount rates, ARR changes — anything that's a ratio, percentage, or multi-column calculation needs an explicit METRICS definition. If it's not in the semantic view, it either fails or produces wrong SQL silently.

Set scope expectations early. Cortex Analyst is not a replacement for a BI tool on complex queries. It's fast, flexible, and surprisingly accurate on aggregation questions. It's unreliable on queries that require multi-step logic or domain-specific calendar definitions that aren't in the schema. Sigma stayed in the stack. The semantic layer added a capability; it didn't replace an existing one.

Use the post-hook pattern. Running create_semantic_view() as a post-hook on the source mart keeps the semantic view current without a separate process. Without it, schema evolution quietly breaks the semantic layer until someone notices wrong answers.