dbt MCP + Snowflake Cortex Chat Analytics

This repository contains a Streamlit application that connects Snowflake Cortex to dbt’s remote MCP (Model Context Protocol) server, enabling a powerful “chat-with-your-data” experience for analytics. Users can query their dbt Semantic Layer using natural language and receive intelligent, context-aware responses about their data.

Who is this project for?

This project is designed for data teams who have:

• A commercial version of dbt Platform (required for MCP server access)
• Snowflake with Cortex enabled
• A dbt project with Semantic Layer models and metrics configured

This serves as an excellent example of building AI-driven analytics solutions that provide better context and results from your data warehouse through conversational interfaces. We have used this content as part of a hands-on-workshop, but it could be a great starting point for building such an appliction (with a few tweaks).

Architecture:

This project contains a Streamlit (SiS - Streamlit in Snowflake - see streamlit.py) app that leverages Cortex LLM functions (COMPLETE) along with dbt’s remote MCP server to provide a lightweight chat-based analytics interface. The Streamlit app has stripped down MCP capabilities and only uses Semantic Layer tools: list_metrics, get_dimensions, get_entities, and query_metrics. The LLM can orchestrate multiple tool calls to the MCP server and is fairly stable.

Example workflow for list_metrics (Ie. ‘What are my metrics?’) - workflow would be longer for additional toolcalls like query_metrics

Setup Steps to Deploy to New Snowflake/dbt Account:

1. Set up dbt project w/ Semantic Layer Models.
   • See provided repo if needed, but this will work with any dbt SL configuration (it must contain metrics)
2. Set up Semantic Layer Config and add service token with warehouse credentials (don’t forget to copy info)
   • Service token should have Semantic Layer Only, Metadata Only, and Developer permissions
3. Snowflake setup:

   • setup warehouse, roles, db/schema/data

     For our example, we use a role, dbt_cloud_user_role for all students

   • Setup for Streamlit/MCP

     Please make the appropriate substitutions before running the following sql:

     use role accountadmin;
     
     -- if deploying to a region that does not have claude models available
     ALTER ACCOUNT SET CORTEX_ENABLED_CROSS_REGION = 'ANY_REGION';
     
     -- db/schema setup --
     create database if not exists streamlit;
     create schema if not exists public;
     
     -- NOTE: when creating your streamlit app, add to streamlit.public so it can access external access integration (see below)
     
     -- user / role setup --
     use role accountadmin;