Text-to-SQL LLM App with Snowflake Cortex

Snowflake’s Cortex Analyst is an AI feature in Snowflake that allows you quickly create apps that translate natural language into SQL queries.

Not a medium member? Click here to read for free!

In this article, I’ll replicate a text-to-SQL app I built a few weeks ago for a small hackathon, although I have made minor changes in my approach here such as using the semantic model generator. It will have the following:

• Public data sourced from the City of Toronto: Short Term Rentals Registration
• Snowflake table to store this data
• Streamlit user interface, deployed within Snowflake, with which to query Cortex Analyst API
• A semantic model (or data dictionary) to guide Cortex Analyst

I’m assuming you have familiarity with the Snowflake interface so there will not be detailed breakdowns of basic steps. This demo is adapted from Snowflake Quickstart documentation (refer to references at the bottom for more).

Set Up Cortex

Ideally, you should create a cortex user role for your deployment:

USE ROLE ACCOUNTADMIN;

CREATE ROLE cortex_user_role;
GRANT DATABASE ROLE SNOWFLAKE.CORTEX_USER TO ROLE cortex_user_role;

GRANT ROLE cortex_user_role TO USER some_user;

In my case I have an existing database called supernova and I have created a schema called housing.