Overview

Festival Operations Demo Dataset

IMPORTANT - Demo Data Disclaimer: All business data, financial figures, and organizational information in this demo are entirely fictitious and created for demonstration purposes only. This includes financial figures, project timelines, employee data, and business scenarios.

What is Openflow

What is Snowflake Intelligence

What You Will Learn

What You Will Build

Prerequisites

IMPORTANT:

Google Service Account Setup: You'll need to create a Google Service Account (GSA) with appropriate permissions. Follow the official Google Cloud documentation to:

1. Create a Service Account in Google Cloud Console
2. Download the JSON key file
3. Enable domain-wide delegation for the service account
4. Grant necessary Google Drive API scopes

For detailed instructions, see: Creating and managing service accounts

Required Google Drive API Scopes:

• https://www.googleapis.com/auth/drive.readonly
• https://www.googleapis.com/auth/drive.metadata.readonly

Setup Environment

Clone the QuickStart Repository

git clone https://github.com/Snowflake-Labs/sfguide-getting-started-openflow-unstructured-data-pipeline.git
cd sfguide-getting-started-openflow-unstructured-data-pipeline

Alternative: Use Snowflake Workspaces with Git Integration

IMPORTANT: You still need to clone or download the repository locally to access the sample-data/google-drive-docs/ files for uploading to Google Drive. The Git integration in Workspaces provides access to SQL scripts but not binary document files (PDF, DOCX, PPTX, JPG) needed for the demo.

Create Database and Schema

IMPORTANT:

• If you use different names for objects created in this section, be sure to update scripts and configurations in the following sections accordingly.

• For each SQL script block below, select all the statements in the block and execute them top to bottom.

-- Create role and warehouse
USE ROLE ACCOUNTADMIN;
CREATE ROLE IF NOT EXISTS FESTIVAL_DEMO_ROLE;
CREATE WAREHOUSE IF NOT EXISTS FESTIVAL_DEMO_S
  WAREHOUSE_SIZE = SMALL
  AUTO_SUSPEND = 300
  AUTO_RESUME = TRUE;
GRANT USAGE ON WAREHOUSE FESTIVAL_DEMO_S TO ROLE FESTIVAL_DEMO_ROLE;

-- Create database and grant ownership
CREATE DATABASE IF NOT EXISTS OPENFLOW_FESTIVAL_DEMO;
GRANT OWNERSHIP ON DATABASE OPENFLOW_FESTIVAL_DEMO TO ROLE FESTIVAL_DEMO_ROLE;

-- Grant role to current user
SET CURR_USER=(SELECT CURRENT_USER());
GRANT ROLE FESTIVAL_DEMO_ROLE TO ROLE IDENTIFIER($CURR_USER);

-- Switch to demo role and create schema
USE ROLE FESTIVAL_DEMO_ROLE;
USE DATABASE OPENFLOW_FESTIVAL_DEMO;
CREATE SCHEMA IF NOT EXISTS FESTIVAL_OPS;

TIP: These commands are also available in sql/setup.sql in the repository for easy execution.

Enable Required Services (Optional)

NOTE: If your Snowflake account is in us-west-2, Cortex services are already available and this step can be skipped.

-- Check current Cortex cross-region setting (requires ORGADMIN role)
SHOW PARAMETERS LIKE 'CORTEX_ENABLED_CROSS_REGION' IN ACCOUNT;

-- Enable cross-region Cortex access if needed (requires ORGADMIN role)
-- This allows your account to use Cortex services from us-west-2
ALTER ACCOUNT SET CORTEX_ENABLED_CROSS_REGION = 'AWS_US';

-- Verify the setting was applied
SHOW PARAMETERS LIKE 'CORTEX_ENABLED_CROSS_REGION' IN ACCOUNT;

TIP: Contact your Snowflake administrator if you need assistance with ORGADMIN privileges or Cortex enablement.

Setup External Access Integration

-- Create schema for network rules
USE ROLE ACCOUNTADMIN;
USE DATABASE OPENFLOW_FESTIVAL_DEMO;
CREATE SCHEMA IF NOT EXISTS NETWORKS;

-- Create network rule for Google APIs
CREATE OR REPLACE NETWORK RULE google_network_rule
  MODE = EGRESS
  TYPE = HOST_PORT
  VALUE_LIST = (
    'admin.googleapis.com',
    'oauth2.googleapis.com',
    'www.googleapis.com',
    'google.com'
  );

-- Verify the network rule was created successfully
DESC NETWORK RULE google_network_rule;

Optional: Add network rule for your Google Workspace domain

-- Create network rule for your Google Workspace domain
-- Replace 'your-domain.com' with your actual Google Workspace domain
CREATE OR REPLACE NETWORK RULE your_workspace_domain_network_rule
  MODE = EGRESS
  TYPE = HOST_PORT
  VALUE_LIST = ('your-domain.com');

-- Example: For a domain like kamesh.dev
-- CREATE OR REPLACE NETWORK RULE kameshs_dev_network_rule
--   MODE = EGRESS
--   TYPE = HOST_PORT
--   VALUE_LIST = ('kameshs.dev');

-- Verify the domain network rule was created successfully
DESC NETWORK RULE your_workspace_domain_network_rule;

Create the External Access Integration

-- Create external access integration with Google API access
-- If you created a workspace domain rule, add it to ALLOWED_NETWORK_RULES
CREATE OR REPLACE EXTERNAL ACCESS INTEGRATION festival_ops_access_integration
  ALLOWED_NETWORK_RULES = (
    OPENFLOW_FESTIVAL_DEMO.NETWORKS.google_network_rule
    -- Add your workspace domain rule if created:
    -- , OPENFLOW_FESTIVAL_DEMO.NETWORKS.your_workspace_domain_network_rule
  )
  ENABLED = TRUE
  COMMENT = 'Used for Openflow Snowflake Deployments runtime to access Google Drive';

-- Verify the external access integration was created with correct settings
DESC EXTERNAL ACCESS INTEGRATION festival_ops_access_integration;

TIP: If you created a workspace domain network rule, uncomment and add it to the ALLOWED_NETWORK_RULES list in the external access integration.

Grant Permissions

-- Grant access to the external access integration
GRANT USAGE ON DATABASE OPENFLOW_FESTIVAL_DEMO TO ROLE OPENFLOW_ADMIN;
GRANT USAGE ON SCHEMA OPENFLOW_FESTIVAL_DEMO.NETWORKS TO ROLE OPENFLOW_ADMIN;
GRANT USAGE ON INTEGRATION festival_ops_access_integration TO ROLE OPENFLOW_ADMIN;

-- Verify grants
SHOW GRANTS TO ROLE OPENFLOW_ADMIN;

NOTE:

• The OPENFLOW_ADMIN role is created automatically during Openflow Snowflake Deployment setup
• No compute pools needed - Openflow Snowflake Deployments manages compute resources automatically
• No manual stages or tables - All document storage objects are automatically created by the Openflow connector
• SQL snippets available in sql/network.sql in the repository

IMPORTANT: Make note of the database, schema, and warehouse names as you'll need them for Openflow configuration.

Openflow Configuration

IMPORTANT: Before configuring connectors, you must complete the Openflow Snowflake Deployments setup. This is a one-time configuration that establishes the foundation for all your Openflow data pipelines.

Complete Openflow Snowflake Deployments Setup

TIP: The quickstart includes downloadable SQL scripts and Jupyter notebooks for automated setup. You can use these scripts to accelerate your deployment process.

Alternative: Use Existing Openflow Infrastructure

Access Openflow Interface

Configure Runtime for Festival Operations

Option A: Create New Runtime (Recommended for Demo)

NOTE: Ensure the festival_ops_access_integration is accessible to FESTIVAL_DEMO_ROLE:

USE ROLE ACCOUNTADMIN;
GRANT USAGE ON INTEGRATION festival_ops_access_integration TO ROLE FESTIVAL_DEMO_ROLE;

Option B: Use Existing Runtime

RESOURCE MANAGEMENT:

• Openflow Snowflake Deployments automatically manages compute resources and scaling
• No manual compute pool configuration is required
• Database and schema access is configured at the runtime level
• Connector-specific settings are configured when adding the Google Drive connector

Add Google Drive Connector

NOTE: After adding the connector to the runtime, you may see authorization prompts. Accept these prompts to allow the connector to access the runtime and required resources.

Prepare Google Drive Location

TIP: Keep your Google Drive browser tab open - you'll need the shared drive ID and folder name for the next configuration step.

Configure Document Ingestion Pipeline

Configure Source Parameters

SECURITY BEST PRACTICE: For production environments, consider using a SecretManagerParameterProvider (such as AwsSecretsManagerParameterProvider) to securely manage sensitive credentials like the GCP Service Account JSON. This approach stores secrets in a dedicated secrets manager instead of directly in the connector configuration, providing better security and easier credential rotation.

Configure Destination Parameters

Configure Ingestion Parameters

NOTE: By default, this section inherits parameters from "Configure Source Parameters" and "Configure Destination Parameters" sections above. For clarity in this quickstart, we'll turn off inheritance and configure only the required ingestion-specific parameters.

Test and Run the Pipeline

What Happens Next

Prepare Sample Documents

Verify Document Collection

sample-data/google-drive-docs/
├── Analysis/
│   └── Post-Event-Analysis-Summer-2024.pptx
├── Compliance/
│   └── Health-Safety-Policy.pdf
├── Executive Meetings/
│   └── Board-Meeting-Minutes-Q4-2024.docx
├── Financial Reports/
│   └── Q3-2024-Financial-Analysis.pdf
├── Operations/
│   ├── Venue-Setup-Operations-Manual-0.jpg
│   ├── Venue-Setup-Operations-Manual-1.jpg
│   ├── Venue-Setup-Operations-Manual-2.jpg
│   └── Venue-Setup-Operations-Manual-3.jpg
├── Projects/
│   └── Sound-System-Modernization-Project-Charter.docx
├── Strategic Planning/
│   ├── 2025-Festival-Expansion-Strategy-0.jpg
│   ├── 2025-Festival-Expansion-Strategy-1.jpg
│   ├── 2025-Festival-Expansion-Strategy-2.jpg
│   ├── 2025-Festival-Expansion-Strategy-3.jpg
│   └── 2025-Festival-Expansion-Strategy-4.jpg
├── Training/
│   └── Customer-Service-Training-Guide.pptx
└── Vendors/
    └── Audio-Equipment-Service-Agreement.pdf

NOTE: The .md files in the repository are source templates. The demo uses the converted formats shown above.

Google Drive Setup

TIP: Maintain the same folder structure in Google Drive as in sample-data/google-drive-docs/ to organize documents by category. The connector's Recursive mode will scan all subfolders automatically.

Verify Document Upload

Monitor Pipeline Execution

NOTE: Pipeline statistics automatically reset every 5 minutes, providing a rolling view of recent activity.

Data Pipeline Results

Verify Document Ingestion

-- Switch to the correct role and database
USE ROLE FESTIVAL_DEMO_ROLE;
USE WAREHOUSE FESTIVAL_DEMO_S;
USE DATABASE OPENFLOW_FESTIVAL_DEMO;
USE SCHEMA FESTIVAL_OPS;

-- Show all tables created by Openflow connector
SHOW TABLES;

-- Show all stages created by Openflow connector
SHOW STAGES;

Check Auto-Created Tables

-- Describe the auto-created tables
DESC TABLE docs_chunks;      -- Document content chunks
DESC TABLE docs_groups;      -- Document groupings
DESC TABLE docs_perms;       -- Document permissions
DESC TABLE doc_group_perms;  -- Group permissions
DESC TABLE file_hashes;      -- File tracking and metadata
DESC TABLE perms_groups;     -- Permission groups

-- View file tracking information
SELECT * FROM file_hashes;

Explore Document Content

-- View document chunks
SELECT * FROM docs_chunks LIMIT 10;

-- Get distinct document IDs and filenames
SELECT DISTINCT 
    METADATA:id::string as id, 
    METADATA:fullName::string as filename 
FROM docs_chunks;

-- Check specific document categories
SELECT COUNT(DOC_ID) 
FROM file_hashes 
WHERE LOWER(DOC_ID) LIKE '%strategy%';

Comprehensive Document Verification

-- Comprehensive document verification by demo category
SELECT 
    COUNT(*) as total_docs,

    -- Strategic Planning Documents (Expected: 7)
    COUNT(CASE WHEN 
        LOWER(DOC_ID) LIKE '%strategy%' OR 
        LOWER(DOC_ID) LIKE '%board%meeting%' OR 
        LOWER(DOC_ID) LIKE '%meeting%minutes%' OR
        LOWER(DOC_ID) LIKE '%financial%analysis%' OR
        LOWER(DOC_ID) LIKE '%q3%2024%financial%'
    THEN 1 END) as strategic_docs,

    -- Operations Excellence Documents (Expected: 5)
    COUNT(CASE WHEN 
        (LOWER(DOC_ID) LIKE '%operation%manual%' OR LOWER(DOC_ID) LIKE '%venue%setup%') OR
        (LOWER(DOC_ID) LIKE '%sound%system%' AND LOWER(DOC_ID) LIKE '%project%') OR
        (LOWER(DOC_ID) LIKE '%post%event%analysis%')
    THEN 1 END) as operations_docs,

    -- Compliance & Risk Documents (Expected: 3)
    COUNT(CASE WHEN 
        (LOWER(DOC_ID) LIKE '%health%safety%' OR LOWER(DOC_ID) LIKE '%safety%policy%') OR
        (LOWER(DOC_ID) LIKE '%service%agreement%' OR LOWER(DOC_ID) LIKE '%audio%equipment%') OR
        (LOWER(DOC_ID) LIKE '%post%event%analysis%')
    THEN 1 END) as compliance_docs,

    -- Knowledge Management Documents (Expected: 1)
    COUNT(CASE WHEN 
        LOWER(DOC_ID) LIKE '%training%guide%' OR 
        LOWER(DOC_ID) LIKE '%customer%service%training%'
    THEN 1 END) as training_docs,

    -- Document format breakdown
    COUNT(CASE WHEN LOWER(DOC_ID) LIKE '%.jpg' THEN 1 END) as jpg_files,
    COUNT(CASE WHEN LOWER(DOC_ID) LIKE '%.pdf' THEN 1 END) as pdf_files,
    COUNT(CASE WHEN LOWER(DOC_ID) LIKE '%.docx' THEN 1 END) as docx_files,
    COUNT(CASE WHEN LOWER(DOC_ID) LIKE '%.pptx' THEN 1 END) as pptx_files

FROM file_hashes;

Check Document Stage

-- List files in the documents stage
LS @documents;

TIP: All verification queries are available in sql/checks.sql in the repository for easy execution.

Expected Document Collection

NOTE: All document counts and content are based on the Festival Operations demo dataset from sample-data/google-drive-docs/.

Cortex Search

Automatic Cortex Search Service Creation

How It Works

Verify Automatic Service Creation

-- Check for the auto-created service
SHOW CORTEX SEARCH SERVICES;

-- The service will be named: CORTEX_SEARCH_SERVICE (default name)
DESC CORTEX SEARCH SERVICE CORTEX_SEARCH_SERVICE;

Test Semantic Search

-- Search for strategic planning documents
SELECT PARSE_JSON(
  SNOWFLAKE.CORTEX.SEARCH_PREVIEW(
      'CORTEX_SEARCH_SERVICE',
      '{"query": "2025 expansion plans target markets strategic planning", "limit": 5}'
  )
)['results'] as strategic_documents;

-- Search for technology modernization projects  
SELECT PARSE_JSON(
  SNOWFLAKE.CORTEX.SEARCH_PREVIEW(
      'CORTEX_SEARCH_SERVICE',
      '{"query": "technology modernization sound system upgrade budget 2.8M", "limit": 5}'
  )
)['results'] as technology_projects;

-- Search for health and safety policies
SELECT PARSE_JSON(
  SNOWFLAKE.CORTEX.SEARCH_PREVIEW(
      'CORTEX_SEARCH_SERVICE',
      '{"query": "health safety policies emergency protocols compliance", "limit": 5}'
  )
)['results'] as safety_policies;

Additional Search Capabilities

Sample Questions by Category

Strategic Planning & Executive Intelligence

"What are our 2025 expansion plans and target markets?"

"Show me all financial analysis and revenue projections"

"What decisions were made in the latest board meeting?"

"Find all budget allocations and investment strategies"

Operations Excellence & Technology

"Find all technology modernization projects and their budgets"

"What is our $2.8M sound system upgrade timeline?"

"Show me all equipment management protocols"

"What post-event analysis recommendations exist?"

Compliance & Risk Management

"What health and safety policies are currently in effect?"

"Show me all vendor contracts and service agreements"

"Find emergency response procedures"

"What regulatory compliance requirements exist?"

Knowledge Management & Training

"Find all training materials and staff development programs"

"What customer service standards are documented?"

"Show me onboarding procedures for new staff"

"What training frameworks are currently in use?"

Advanced Cross-Category Queries

"What are our 2025 expansion plans across all document formats - show me visual charts, meeting decisions, and financial projections"

"Find all technology modernization projects with their business cases, budgets, and visual diagrams"

"What health and safety policies are in effect across all formats - show me formal policies, vendor agreements, and visual guides"

TIP: These sample questions are designed specifically for the Festival Operations dataset. Use them as templates and adapt the language to match your organization's terminology and business context.

Snowflake Intelligence

IMPORTANT: All queries from Snowflake Intelligence use the user's credentials. Role-based access control and data-masking policies automatically apply to all agent interactions.

Prerequisites

Initial Setup

-- Use ACCOUNTADMIN role for setup
USE ROLE ACCOUNTADMIN;

-- Create database for Snowflake Intelligence
CREATE DATABASE IF NOT EXISTS snowflake_intelligence;
GRANT USAGE ON DATABASE snowflake_intelligence TO ROLE PUBLIC;

-- Create agents schema
CREATE SCHEMA IF NOT EXISTS snowflake_intelligence.agents;
GRANT USAGE ON SCHEMA snowflake_intelligence.agents TO ROLE PUBLIC;

-- Grant agent creation privileges to your role
GRANT CREATE AGENT ON SCHEMA snowflake_intelligence.agents TO ROLE FESTIVAL_DEMO_ROLE;

Create the Agent

IMPORTANT: Before creating the agent, ensure you are using the FESTIVAL_DEMO_ROLE role in Snowsight. This ensures the agent is owned by the correct role and has proper access to resources. You can switch roles using the role selector in the top-right corner of Snowsight.

Access Agent Creation Interface

Configure Agent Basics

IMPORTANT: As you configure each section below (About, Tools, Orchestration, Access), remember to click "SAVE" after completing all configurations to ensure your changes are preserved.

"What are our 2025 expansion plans and target markets?"

"Find all technology modernization projects and their budgets"

"What health and safety policies are currently in effect?"

"Find all training materials and staff development programs"

"Which documents have the most collaboration and strategic importance?"

Configure Agent Tools

Add Cortex Search Service

TIP: The search service name follows the pattern DATABASE_NAME.SCHEMA_NAME.SERVICE_NAME. In this case: OPENFLOW_FESTIVAL_DEMO.FESTIVAL_OPS.CORTEX_SEARCH_SERVICE

Configure Orchestration

Whenever you can answer visually with a chart, always choose to generate a chart even if the user didn't specify to. Respond in the same language as the question wherever possible.

Always provide specific document references when citing information. 
Focus on actionable insights and business value in your responses.

TIP: Use auto (default) to let Snowflake automatically select the best available model for your region and query type. Supported models include Claude 4.0, Claude 3.7, Claude 3.5, and GPT 4.1.

Set Access Controls

NOTE: All queries use the user's credentials. Ensure users have appropriate access to the Cortex Search service, source database/schema, and underlying data.

Test Your Agent

Getting Started with Queries

Sample Questions by Category

"What are our 2025 expansion plans and target markets?"

"What strategic initiatives are mentioned in board meeting minutes?"

"Find all technology modernization projects and their budgets"

"What are the key takeaways from the post-event analysis?"

"What health and safety policies are currently in effect?"

"What are the terms and conditions in our vendor service agreements?"

"Find all training materials and staff development programs"

"What customer service training resources are available?"

Advanced Use Cases

Multi-Document Analysis

"Compare our Q3 2024 financial performance with the strategic goals outlined in our 2025 expansion plan. What gaps exist and what actions are recommended?"

Trend Analysis

"What trends do you see in customer complaints and incident reports over the past year? What preventive measures have been implemented?"

Knowledge Discovery

"What vendor performance issues are mentioned across different documents, and how do they relate to our operational challenges?"

Executive Briefing

"Prepare an executive summary of key issues and decisions from our Q4 2024 board meeting, including action items and their current status based on other documents."

Compliance Monitoring

"Review all our safety policies and incident reports to identify any compliance gaps or policy updates needed."

Create Custom Views with AI Summarization

Create AI-Enhanced Document View

-- View with AI-generated document summaries
-- Uses AI_COMPLETE to generate concise, intelligent summaries
CREATE OR REPLACE VIEW document_summaries AS
SELECT 
    DOC_ID,
    METADATA:fullName::string as full_name,
    METADATA:webUrl::string as web_url,
    METADATA:lastModifiedDateTime::timestamp as last_modified_date_time,
    chunk as original_chunk,
    -- AI-powered summary generation
    SNOWFLAKE.CORTEX.AI_COMPLETE(
        'mistral-large2',
        CONCAT(
            'Summarize this document content in 2-3 sentences, focusing on key information: ',
            chunk
        )
    ) as ai_summary,
    user_emails,
    user_ids
FROM docs_chunks;

NOTE: AI_COMPLETE uses Mistral Large 2 to generate intelligent summaries. You can also use mistral-large, llama3.1-70b, or other supported models.

Create Custom Cortex Search Service

-- Create a custom Cortex Search service using the AI-enhanced view
CREATE OR REPLACE CORTEX SEARCH SERVICE festival_ai_summaries_search
ON ai_summary
ATTRIBUTES full_name, web_url, last_modified_date_time, user_emails
WAREHOUSE = FESTIVAL_DEMO_S
TARGET_LAG = '1 day'
AS (
    SELECT 
        DOC_ID,
        ai_summary,
        full_name,
        web_url,
        last_modified_date_time,
        user_emails
    FROM document_summaries
);

-- Verify the new service
SHOW CORTEX SEARCH SERVICES;
DESC CORTEX SEARCH SERVICE festival_ai_summaries_search;

Test the AI-Enhanced Search Service

-- Search using AI-generated summaries
SELECT PARSE_JSON(
  SNOWFLAKE.CORTEX.SEARCH_PREVIEW(
      'festival_ai_summaries_search',
      '{"query": "strategic planning expansion markets", "limit": 5}'
  )
)['results'] as summarized_strategic_docs;

-- Compare with original search service
SELECT PARSE_JSON(
  SNOWFLAKE.CORTEX.SEARCH_PREVIEW(
      'CORTEX_SEARCH_SERVICE',
      '{"query": "strategic planning expansion markets", "limit": 5}'
  )
)['results'] as original_strategic_docs;

CUSTOMIZATION TIP: Create multiple specialized views for different departments (e.g., executive_summaries, compliance_summaries, operations_summaries) and corresponding Cortex Search services for targeted intelligence.

Building Custom Documents with Taskfile

Prerequisites

# macOS
brew install go-task pandoc

# Ubuntu/Debian
sudo snap install task --classic
sudo apt-get install pandoc

# Windows (via Chocolatey)
choco install go-task pandoc

Available Document Conversion Tasks

task convert-to-pdf

task convert-to-pptx

task convert-to-docx

task convert-to-jpg

task convert-all-docs

Creating Custom Documents

TIP: The Taskfile demonstrates how to create multi-format document collections for comprehensive testing of Openflow's document intelligence capabilities.

Cleanup

Stop the Google Drive Connector

TIP: Stopping the connector preserves your configuration while preventing further document processing and associated compute costs.

Drop the Snowflake Intelligence Agent (Optional)

-- Switch to the Snowflake Intelligence database
USE DATABASE snowflake_intelligence;
USE SCHEMA agents;

-- Drop the agent
DROP AGENT IF EXISTS FESTIVAL_DOC_INTELLIGENCE;

Drop the Demo Database (Optional)

-- Switch to ACCOUNTADMIN role
USE ROLE ACCOUNTADMIN;

-- Drop the entire demo database (includes all tables, stages, and search services)
DROP DATABASE IF EXISTS OPENFLOW_FESTIVAL_DEMO;

-- Drop the demo warehouse
DROP WAREHOUSE IF EXISTS FESTIVAL_DEMO_S;

-- Drop the demo role
DROP ROLE IF EXISTS FESTIVAL_DEMO_ROLE;

WARNING: Dropping the database will permanently delete all ingested documents, Cortex Search services, and pipeline configurations. Only perform this if you no longer need the demo.

Conclusion And Resources

What You Accomplished

Key Benefits Realized

Next Steps

Related Resources

Community and Support