Snowflake AI Data Copilot: Trusted Numbers, AI Answers, Lower Costs

The problem

The same five requests dominate Snowflake job posts: "which number is right, the report doesn't match NetSuite", "our AI assistant keeps querying the wrong table", "the pipeline broke and nobody noticed for three days", "why is the Snowflake bill so high" and "sales, marketing and billing all show different revenue". The market research behind that list is in the repository. Instead of five proposals, I built one working product that answers all five, so a client can see the result before hiring: a demo SaaS with two tenants (Northwind Provisions on NetSuite and operational data, Arcadia Cloud on Salesforce, HubSpot and Stripe), seeded deterministically so that every number on every screen is real output of the running app.

The product in use

What I built

AI Analyst. Anyone can ask a question in plain English ("Who are our top 10 customers?") and get the answer, a chart and the exact SQL behind it. Claude writes the SQL only from a semantic model of certified metrics and verified queries (the same idea as Cortex Analyst's verified query repository), so "revenue" means the same thing every time. Switching roles shows the e-mail column masked, rows limited to a region and tables blocked, mirroring the MASK_EMAIL and RAP_REGION policies of the Snowflake DDL. Without an API key the analyst answers the eight verified example questions; with one it accepts any question.

Data Trust and reconciliation. A month-by-month tie-out of the warehouse income statement against NetSuite with four seeded defects: a duplicate join, a soft-delete leak, a timezone period shift and an unmapped account. 100 % of the variance is attributed to those defects, dbt-style tests catch each one and a one-click fix rebuilds the models, so finance can sign off on the numbers instead of arguing about them.

Pipeline Studio. Bronze, silver and gold with watermark + MERGE incremental loads, soft deletes, an idempotent API load, freshness SLAs and run history. Additive schema drift evolves the target automatically; a breaking type change fails loudly and holds the watermark instead of quietly filling reports with bad data.

FinOps Command Center. Credits by warehouse and team, idle burn, the most expensive query patterns and recommendations ranked by monthly savings (about $7,800 a month in the demo dataset). A what-if simulator shows the effect of a smaller warehouse or a shorter auto-suspend on cost and speed before anything changes in production. The equivalent queries against the real ACCOUNT_USAGE views ship with the repository.

RevOps Revenue Engine. The second tenant joins Salesforce, HubSpot and Stripe into one golden account per domain and feeds the lead-to-won funnel, the quarter forecast and coverage, first-touch, last-touch, linear and W-shaped attribution with ROAS, the ARR waterfall, NRR and GRR and churn risk. CRM-versus-billing checks find duplicates, revenue leakage (deals won but never billed), untracked ARR and booking mismatches, and a diff-based reverse ETL writes the health score and billed ARR back to Salesforce.

Guardrails for AI-generated SQL

Every query the analyst produces goes through six checks, in order, before it runs: the SQL is parsed with sqlglot and must be exactly one read-only statement; there is no DDL or DML anywhere in the tree and no file or system table functions; every table must be granted to the active role, with bare names resolved through the semantic model; governed tables are rewritten into policy subqueries that mask the e-mail column and apply the region filter; a LIMIT 1000 is added and the query is transpiled to the engine dialect; and in demo mode the DuckDB connection itself has external file and network access disabled.

Architecture

A Next.js 14 front end (TypeScript, Tailwind, Recharts) proxies the API calls to a FastAPI backend. A warehouse engine interface runs on DuckDB laid out like a Snowflake account (bronze, silver, gold and account_usage schemas) in demo mode, or on the Snowflake connector in live mode, where the native policies enforce the masking. The seed module builds deterministic NetSuite, operational and usage data in about 20 seconds on first start, with no credentials; the semantic model (tables, certified metrics, verified queries), the security module (roles, masking and row-access policies), the SQL guard and the five scenario modules sit on top. The same design ships as real Snowflake DDL: database and warehouses, least-privilege roles and a key-pair service user, masking and row-access policies, the incremental MERGE task, the semantic view, the FinOps queries against ACCOUNT_USAGE and the RevOps model. Backed by 48 backend tests and a TypeScript check; one make dev or docker compose up runs it.