Snowflake-Labs/cortex-data-analysis-with-postgres-and-vector-search

Snowflake-Labs/cortex-data-analysis-with-postgres-and-vector-search

Language: Python

Stars: 3

Forks: 2

Open issues: 0

Created: 2025-09-24T14:01:55Z

Pushed: 2025-10-17T16:43:25Z

Default branch: master

Fork: yes

Parent repository: sfc-gh-cnantasenamat/cortex-data-analysis

Archived: no

README:

Snowflake Cortex + PostgreSQL Financial Analytics Demo

A full-stack demo app for financial analytics demonstrating the integration of Snowflake Cortex AI, PostgreSQL with pgvector, and Streamlit for intelligent data analysis and natural language interactions.

---

📊 PostgreSQL Integration

• Real-time Budget Dashboard - Track daily, weekly, and monthly spending with interactive visualizations
• Transaction Management - Approve, decline, or cancel pending transactions with full audit trail
• Financial Data Storage - Robust PostgreSQL backend with comprehensive data models
• Live Data Queries - Dynamic queries with SQLAlchemy ORM

🤖 Snowflake Cortex AI

• Natural Language to SQL - Convert plain English questions into PostgreSQL queries using Cortex Complete
• AI-Powered Financial Insights - Get intelligent spending recommendations and budget analysis
• Cortex Analyst Integration - Enterprise-grade natural language query interface
• Snowflake Data Visualization - Display and analyze data from Snowflake tables

💬 Intelligent Agent

• Cortex AI Agent - Interactive chat interface for financial queries
• Context-Aware Responses - Agent remembers conversation history and context
• Data Retrieval & Updates - Agent can both read and write to PostgreSQL
• Subscription Management Demo - Intelligent subscription analysis and cancellation recommendations

🔍 Advanced Search Demo

Showcase three progressively sophisticated search techniques: 1. ILIKE Pattern Matching - Basic SQL substring search 2. pg_trgm Fuzzy Search - Typo-tolerant trigram matching 3. pgvector Semantic Search - AI-powered contextual search with embeddings

🔗 OpenAI Integration

• Embedding Generation - Create vector embeddings for semantic search
• pgvector Storage - Store and query embeddings in PostgreSQL
• Intelligent Search - Find transactions by meaning, not just keywords

---

🎯 Architecture

┌─────────────────┐ ┌──────────────────┐ ┌─────────────────┐
│ Streamlit UI │ ◄─────► │ Python Backend │ ◄─────► │ PostgreSQL │
│ (Frontend) │ │ (Application) │ │ (Database) │
└─────────────────┘ └──────────────────┘ └─────────────────┘
│ │ │
│ ▼ │
│ ┌──────────────────┐ │
└─────────────────►│ Snowflake Cortex │ │
│ AI Services │ │
└──────────────────┘ │
│ │
▼ ▼
┌──────────────────┐ ┌─────────────────┐
│ Cortex Analyst │ │ pgvector │
│ Cortex Agent │ │ (Embeddings) │
│ Cortex Complete │ └─────────────────┘
└──────────────────┘ ▲
│
┌───────┴────────┐
│ OpenAI API │
│ (Embeddings) │
└────────────────┘

---

📋 Requirements

Snowflake Account

• Active Snowflake account with appropriate permissions
• Cortex Analyst enabled on your account
• Cortex AI Agent configured and deployed
• Personal Access Token (PAT) for API authentication
• Warehouse with sufficient compute resources

PostgreSQL Database

• PostgreSQL 16+ (cloud or self-hosted)
• pgvector extension installed (for semantic search)
• pg_trgm extension installed (for fuzzy search)
• SSL/TLS connection support recommended

Python Environment

• Python 3.11 or higher
• pip package manager
• Virtual environment (recommended)

API Keys

• OpenAI API Key (optional, for semantic search with embeddings)
• Get from: https://platform.openai.com/api-keys

Development Tools

• Git for version control
• Text editor or IDE
• Terminal/command line access

---

🚀 Quick Start

1. Clone the Repository

git clone
cd cortex-data-analysis-with-postgres

2. Set Up Python Environment

# Create virtual environment (recommended)
python3 -m venv venv
source venv/bin/activate # On Windows: venv\Scripts\activate

# Install dependencies
pip install -r requirements.txt

3. Configure PostgreSQL

Install Required Extensions

-- Connect to your PostgreSQL database
CREATE EXTENSION IF NOT EXISTS vector;
CREATE EXTENSION IF NOT EXISTS pg_trgm;

Create Database Tables

# Run the setup script
python3 scripts/setup_transaction_management.py

4. Configure Secrets

# Copy the template
cp .streamlit/secrets_template.toml .streamlit/secrets.toml

# Edit with your credentials
nano .streamlit/secrets.toml

Required Configuration:

# PostgreSQL Connection
[postgres]
host = "your-postgres-host.com"
port = 5432
database = "your_database"
user = "your_username"
password = "your_password"
sslmode = "require"

# Snowflake Connection
[connections.snowflake]
account = "YOUR_ACCOUNT"
user = "YOUR_USERNAME"
password = "YOUR_PASSWORD"
role = "ACCOUNTADMIN"
warehouse = "YOUR_WAREHOUSE"
database = "YOUR_DATABASE"
schema = "PUBLIC"

# Snowflake Cortex Agent
[agent]
SNOWFLAKE_PAT = "your-personal-access-token"
SNOWFLAKE_HOST = "YOUR_ACCOUNT.snowflakecomputing.com"

# OpenAI (Optional - for semantic search)
[openai]
api_key = "sk-proj-your-key-here"

5. Load Sample Data

Choose one of the following options:

# Option 1: Load sample transaction data
python3 scripts/load_sample_data.py

# Option 2: Load expanded dataset
python3 data_loaders/bulk_insert_expanded_data.py

# Option 3: Load from SQL backup (see Sample Data section below)
psql -h your-host -U your-user -d your-database -f docs/sample_data/postgres_sample_data.sql

6. Setup Semantic Search (Optional)

If you want to use the pgvector semantic search feature:

# Generate embeddings for existing transactions
python3 scripts/setup_embeddings.py

7. Run the Application

streamlit run streamlit_app.py

The app will open in your browser at http://localhost:8501

---

📦 Sample Data

PostgreSQL Sample Data

Load sample financial data into your PostgreSQL database:

Option 1: Python Script

python3 scripts/load_sample_data.py

Option 2: SQL Backup File

Download and restore the sample data backup:

# Restore to your database
psql -h your-host -U your-user -d your-database -f docs/sample_data/postgres_sample_data.sql

Included Data:

• 500+ sample transactions
• 5 account profiles...