Table of Contents

• An Unified Data Stack on Snowflake
  • Architecture
  • Key Features
  • Demo Vignettes
    • 1. OpenFlow-Based Slack Ingestion
    • 2. dbt Projects on Snowflake
    • 3. AI/SQL Integration within dbt Models
      • Unstructured Text Processing
      • Image Processing
    • 4. Streamlit Integration
    • 5. End-to-End Workflow Example
  • Data Models
    • Landing Zone
    • Curated Zone
  • Project Structure
  • Setup
    • Prerequisites
    • Installation
    • Setup Slack Connector (OpenFlow)
  • General Usage
    • Running the Dashboard
    • Dashboard Features
    • Managing Incidents via Slack
  • Additional Resources
  • Questions, Feedback, and Contribution

An Unified Data Stack on Snowflake

This project demonstrates a modern data stack for an example real-life situaton that of incident management that typically requires multiple steps, and close collaboration between representatives using tools like Slack. This project uses a modern stack with Snowflake to bring AI-powered automation and real-time analytics to this known business process that can benefit from AI inclusion.

An end-to-end incident management platform* built on Snowflake that demonstrates modern data engineering practices using dbt, AI/ML capabilities, and real-time visualization through Streamlit. The system ingests incident data from Slack via Snowflake's OpenFlow connector and provides intelligent categorization, tracking, and reporting capabilities.

*a small cross-section of a much larger process to show the art of possible

Architecture

┌─────────────────┐    ┌──────────────┐    ┌─────────────────┐
│   Slack App     │───▶│   OpenFlow   │───▶│   Snowflake     │
│                 │    │  Connector   │    │   Landing Zone  │
└─────────────────┘    └──────────────┘    └─────────────────┘
                                                     │
                                                     ▼
┌─────────────────┐    ┌─────────────────┐    ┌──────────────────┐
│   Streamlit     │◀───│   Snowflake     │◀───│   dbt Trans-     │
│   Dashboard     │    │ Curated Zone    │    | - formations     │
└─────────────────┘    └─────────────────┘    └──────────────────┘

Key Features

• AI-Powered Classification: Automatically categorizes incidents using Snowflake's AI functions
• Image Processing: AI analysis of attached images for incident categorization
• Real-time Data Pipeline: Ingests Slack messages and converts them to structured incident records
• Interactive Dashboard: Streamlit-based dashboard for monitoring and managing incidents
• dbt Data Transformations: Data modeling using one of the most popular build tool - dbt - on Snowflake provided runtime engine

Demo Vignettes

This project demonstrates several key modern data platform capabilities through realistic incident management scenarios.

1. OpenFlow-Based Slack Ingestion

Scenario: Real-time incident reporting through Slack

• Users report incidents in dedicated Slack channels
• OpenFlow connector automatically ingests messages to Snowflake
• System processes both text and image attachments
• Demonstrates: Real-time data ingestion, unstructured data handling

Demo Flow:

1. Post incident message in Slack: "Payment gateway is returning 500 errors"
2. Attach screenshot of error page (use the sample image JPEG provided)
3. If Tasks are not setup, then run the dbt Project end-to-end
4. Watch as system creates structured incident record with AI classification

2. dbt Projects on Snowflake

Scenario: Working with dbt Projects in Snowflake

• Remote deployment and execution of dbt Projects using Snowflake CLI
• Using variables in dbt_project.yml
• Support for dbt command options as "select"
• Passing arguments using Task config to dbt commands executed using EXECUTE DBT PROJECT

Scenario: Scalable data transformation pipeline

• Raw Slack data transformed into analytics-ready models
• Incremental processing for performance
• Data quality testing and documentation
• Demonstrates: Modern data engineering practices, data lineage

Key Models:

• landing_zone.incidents: AI-enhanced incident processing with image analysis
• curated_zone.active_incidents: Business-ready incident tracking
• curated_zone.monthly_incident_trends: Aggregated analytics

3. AI/SQL Integration within dbt Models

Scenario: Intelligent incident classification and processing

Unstructured Text Processing

-- Automatic incident categorization from Slack messages
ai_classify(sri.text, ['payment gateway error', 'login error', 'other']):labels[0] as category

-- Smart incident matching using natural language
ai_filter(
  prompt('The text category {0} is logically relatable to this record\'s category {1}', 
         text_category, roi.category)
)

Image Processing

-- AI analysis of attached screenshots
case 
    when sri.attachment_file is not null then 
        ai_classify(sri.attachment_file, ['payment gateway error', 'login error', 'other']):labels[0]
    else ai_classify(sri.text, ['payment gateway error', 'login error', 'other']):labels[0]
end as category

Demonstrates: AI-native data processing, multimodal analysis, intelligent data enrichment

See the complete implementation in incidents.sql

4. Streamlit Integration

Scenario: Real-time operational dashboard

• Live incident monitoring and management
• Interactive attachment viewing
• Trend analysis and reporting
• Demonstrates: Data visualization, real-time analytics, user experience

Dashboard Features:

• Real-time incident metrics with priority-based color coding
• Sample trend charts powered by Snowflake analytics
• In-app image viewing for incident attachments

5. End-to-End Workflow Example

Complete Incident Lifecycle:

1. Report: "Our login page is showing 'Invalid credentials' for valid users" + screenshot
2. Ingest: OpenFlow captures message and image from Slack
3. Process: dbt transforms raw data:
   • AI classifies as "login error" (critical priority)
   • Image analysis confirms authentication issue
   • Creates incident record INC-2025-XXX
4. Monitor: Streamlit dashboard shows new critical incident
5. Resolve: Team addresses issue, updates status
6. Analyze: System tracks resolution time, updates trends

This demonstrates a complete modern data stack handling real-world operational scenarios with AI enhancement and real-time visibility.

Data Models

Landing Zone

• incidents: Core incident records with AI-powered classification
• incident_comment_history: Chronological updates and comments
• incident_attachments: File attachments with metadata (as ingested from Slack)
• users: User directory for assignees and reporters

Curated Zone

• active_incidents: Currently open incidents with enriched data
• closed_incidents: Resolved incidents with resolution metrics
• monthly_incident_trends: Aggregated monthly statistics
• weekly_incident_trends: Weekly trend analysis

Project Structure

incident-management/
├── data/                          # Sample data and test files
│   ├── csv/                      # CSV seed data
│   │   ├── incidents.csv         # Sample incident records
│   │   ├── incident_comment_history.csv
│   │   └── users.csv             # User directory
│   └── images/                   # Sample incident attachments
│       ├── invalid_credentials.jpeg
│       └── payment_gateway_outage.jpeg
├── src/
│   ├── incident_management/      # dbt project
│   │   ├── models/
│   │   │   ├── landing_zone/     # Raw data models
│   │   │   │   ├── incidents.sql        # Core incident processing
│   │   │   │   ├── incident_attachments.sql
│   │   │   │   ├── users.sql
│   │   │   │   └── v_*.sql              # Slack message views
│   │   │   ├── curated_zone/            # Analytics-ready models
│   │   │   │   ├── active_incidents.sql
│   │   │   │   ├── closed_incidents.sql
│   │   │   │   ├── monthly_incident_trends.sql
│   │   │   │   └── weekly_incident_trends.sql
│   │   │   └── schema.yml        # Model documentation
│   │   ├── macros/               # Custom dbt macros
│   │   ├── dbt_project.yml       # dbt configuration
│   │   └── profiles.yml          # Database connections
│   ├── scripts/                  # Deployment and setup scripts
│   │   ├── sqlsetup.sh          # Snowflake infrastructure setup
│   │   ├── dbtdeploy.sh         # dbt deployment automation
│   │   ├── dbtexec.sh           # dbt execution wrapper
│   │   └── snowflake.yml.template
│   ├── sql/                      # Raw SQL scripts
│   │   ├── 00_roles.sql         # Role and permission setup
│   │   ├── 01_before_slack_connector.sql
│   │   └── 02_orchestration.sql
│   └── streamlit/                # Dashboard application
│       ├── main.py              # Main dashboard
│       ├── app_utils.py         # Utility functions
│       └── snowflake.png        # Assets
├── requirements.txt              # Python dependencies
└── README.md                    # This file

Setup

Prerequisites

• Snowflake account with access to ACCOUNTADMIN privilege to create user and roles.
• Snowflake CLI (latest version)
• Python 3.9+
• Streamlit
• Git

Installation

This project demonstrates dbt Projects deployment and execution from local dev machine. But once deployed, it is easy to switch to Snowflake Workspaces to manage further redeployments and test executions.

1. Clone this repo

   git clone <git url>

2. Install Python Dependencies

   uv venv
   source .venv/bin/activate
   uv pip install -r requirements.txt

   See requirements.txt for the complete list of dependencies.

3. Setup Snowflake Infrastructure

   3.1 Create service user and role :

   • Use the script 00_roles.sql* to create role for dbt Projects service account

   cd src/sql
   snow sql --connection <named connection in TOML> -f 00_roles.sql

   ⚠️ NOTE

   • This will need a user with ACCOUNTADMIN privilege assigned.

   Best Practice

   • The script generates a PAT for the service user and ties it to the role created above.This executes as the last step.Note and copy the token from the output for use during authentication.

   3.2 Generate a key-pair for this user following the steps here and ALTER USER to update the public key.

   ALTER USER <user name>
   SET rsa_public_key = <rsa public key>

   3.3 Update the environment variables and generate required YAML files

     3.3.1. Configure all variable in the `.env` file using the [`.env.template`](.env.template)
   
     3.3.2. Generate snowflake.yml file for use with Snowflake CLI 
     ```bash
     cd src/scripts
     ./create_snowflake_yaml.sh -e <path to .env file>
     ```
     Verify that the snowflake.yml is created under `sql` dir
     
     3.3.3. Generate profile.yml file for creating the dbt Project
     ```bash
     cd src/scripts
     ./create_profiles_yml.sh -e <path to .env file>
     ```
   
     3.3.4 Because profiles.yml is generated on the fly, check it into Git repo that will be used to create the dbt Projects
   

   3.3 Grant usage on the GIT API INTEGRATION and EXTERNAL_ACCESS_INTEGRATION objects configured in the env file to the user above.

   3.4 Run the setup script to create necessary Snowflake objects:

   cd src/scripts
   ./sqlsetup.sh -e ../path/to/your/.env

   This script will:

   • Execute 01_before_slack_connector.sql to create initial database structure
   • Execute 02_orchestration.sql to set up dbt Projects orchestration components
   • Load sample CSV data for each of the tables using the Snowsight table loader UI.

4. Verify Setup

   • Check that Snowflake objects were created successfully
   • Verify dbt models compile and run correctly
   • Test Streamlit connectivity by running the dashboard

   cd src/streamlit
   streamlit run main.py

Setup Slack Connector (OpenFlow)

1. Follow the official Snowflake documentation to configure a Slack app and set up the OpenFlow Slack connector.

   Best Practice

   • Create a dedicated SERVICE account for the Slack app
   • Use Key-Pair authentication with an encrypted private key
   • Configure appropriate channel permissions for incident reporting

   ⚠️NOTE

   • The SERVICE user that you will create for the Slack connector to assume, should have grants to operate on the landing zone namespace to:
     • create tables
     • insert into tables
     • create stages
     • write to stages

2. Verify that you see the following tables and stages in your landing zone to confirm Slack connector has been configured correctly:

   • SLACK_MEMBERS
   • SLACK_MEMBERS_STAGING
   • DOC_METADATA
   • FILE_HASHES
   • SLACK_MESSAGES

3. Drop a test message in your Slack channel that has your test app added to it and verify that you can see a record for it created in the SLACK_MESSAGES table (might take a few seconds).

General Usage

Running the Dashboard

1. Start the Streamlit Application

   cd src/streamlit
   streamlit run main.py

   See main.py for the complete dashboard implementation.

2. Access the Dashboard

   • Open your browser to http://localhost:8501
   • The dashboard will display real-time incident metrics, trends, and detailed tables

Dashboard Features

• Metrics Overview: Critical, high-priority, active, and recently closed incident counts
• Active Incidents Table: Real-time view of open incidents with priority indicators
• Recently Closed Incidents: Track resolution times and outcomes
• Attachment Viewer: Click on incidents with attachments to view images
• Trend Analysis: Monthly and weekly incident patterns (when data is available)

Managing Incidents via Slack

1. Report New Incidents: Post messages in configured Slack channels
2. Automatic Processing: The system will:
   • Extract incident details from messages
   • Classify incident type and priority using AI
   • Process attached images for additional context
   • Create structured incident records
3. Track Progress: Monitor incidents through the Streamlit dashboard

Additional Resources

• Snowflake AI Functions Documentation
• dbt Projects on Snowflake
• Streamlit Documentation
• OpenFlow Connectors

---

Questions, Feedback, and Contribution

Please feel free to reach out with any questions to [email protected]

Feedback and contributions are very welcome:

• GitHub Issues for bug reports and feature requests
• Pull Requests for direct contributions

---