​AmberProc for Snowflake

Welcome to AmberProc, a product designed to provide guidance and technical support for users of Snowflake Data Cloud. This tool utilizes OpenAI's GPT-4 and Gemini to assist users with general inquiries or with specific coding tasks in Snowflake. A diverse collection of pre-defined utilities and conversation templates are provided to simplify the querying process and enhance productivity of Snowflake users.

Key Features

 

• Intelligent Assistance: This tool provides real-time, context-aware suggestions for general inquiries and writing code with multiple model options like GPT4 and Gemini.

​​

• Comprehensive Utility Coverage: Includes detailed modules for Data Governance, Performance Optimization, Data Integration, AI, and more, ensuring users have access to extensive resources for managing and optimizing their Snowflake environment.

​​

• Advanced Coding Support: Offers specialized assistance for coding-related tasks including SQL and Python worksheets, User-Defined Functions, Snowpark integration, Streamlit Apps, and many more.

​​

• Dynamic Interaction Models: Features a selection of predefined conversation templates within each utility to provide targeted, relevant responses. This structured interaction model aids in quickly addressing user questions with precise and actionable information.

​​

• Security and Compliance: Ensures that interactions adhere to Snowflake's security standards, providing a safe and reliable resource for sensitive and critical data operations.

​​

Getting Started

To start using AmberProc for Snowflake, open up a SQL worksheet and follow these steps:

Initial Setup
1.    Assign Required Role:
       USE ROLE ACCOUNTADMIN;

2.    Create Database and Schema:
       CREATE DATABASE AMBERPROC_CONFIG;
       CREATE SCHEMA AMBERPROC_CONFIG.INIT_SCHEMA;

3.    Set Active Database and Schema:
        USE DATABASE AMBERPROC_CONFIG;
        USE SCHEMA INIT_SCHEMA;

4.    Grant Necessary Permissions: sql     GRANT CREATE NETWORK RULE, CREATE SECRET, USAGE ON SCHEMA AMBERPROC_CONFIG.INIT_SCHEMA TO ROLE ACCOUNTADMIN;

Configuration
1.    Network Rule and Secret Creation:
       CREATE OR REPLACE NETWORK RULE AMBERPROC_NETWORK_RULE
       MODE = EGRESS
       TYPE = HOST_PORT
       VALUE_LIST = ('13.233.247.11:3000');

​

       CREATE OR REPLACE SECRET AMBERPROC_API_KEY
       TYPE = GENERIC_STRING
       SECRET_STRING = '';
        SECRET_STRING value will be kept empty

2.    External Access Integration:
        CREATE OR REPLACE EXTERNAL ACCESS INTEGRATION AMBERPROC_EXT_INT
        ALLOWED_NETWORK_RULES = (AMBERPROC_NETWORK_RULE)
        ALLOWED_AUTHENTICATION_SECRETS = (AMBERPROC_API_KEY)
        ENABLED = TRUE;

3.    Grant Access to Application:
       GRANT USAGE ON DATABASE AMBERPROC_CONFIG TO APPLICATION AMBERPROC;
       GRANT USAGE ON SCHEMA AMBERPROC_CONFIG.INIT_SCHEMA TO APPLICATION AMBERPROC;
       GRANT USAGE ON INTEGRATION AMBERPROC_EXT_INT TO APPLICATION AMBERPROC;
       GRANT READ ON SECRET AMBERPROC_CONFIG.INIT_SCHEMA.AMBERPROC_API_KEY TO  APPLICATION AMBERPROC;

4.    Initialize Application:
       CALL AmberProc.functions.init_app(PARSE_JSON('{
       "secret_name": "AMBERPROC_CONFIG.INIT_SCHEMA.AMBERPROC_API_KEY",
       "external_access_integration_name": "AMBERPROC_EXT_INT",
        }'));
     

       Result:
       AmberProc is initialized successfully.

5.    Access Permissions for Streamlit: Launch the application and navigate to the Streamlit section. To utilize the app’s features, the EXECUTE TASK and EXECUTE MANAGED TASK permissions must be granted first. Once those necessary permissions have been enabled, the app will be ready for use.

Debugging Guide

When encountering issues during the setup or operation of AmberProc for Snowflake, follow these steps to diagnose and resolve common problems:

1.    Role Permissions: Ensure the ACCOUNTADMIN role is assigned to the user performing the setup. This role is necessary for creating databases, schemas, network rules, and secrets.
       USE ROLE ACCOUNTADMIN;

2.    Verify Configuration Source: Ensure that the correct Database AMBERPROC_CONFIG and Schema INIT_SCHEMA is selected. If a custom database and schema is opted, ensure the accurate selection of the intended database and schema for the application configuration settings throughout the setup process.
       USE DATABASE AMBERPROC_CONFIG;
       USE SCHEMA INIT_SCHEMA;

3.    Application Permissions: Verify that the application AmberProc has been granted the necessary permissions to access the database, schema, integration, and secrets. These permissions are crucial for the application’s ability to interact with Snowflake resources.

      GRANT USAGE ON DATABASE AMBERPROC_CONFIG TO APPLICATION AMBERPROC;
      GRANT USAGE ON SCHEMA AMBERPROC_CONFIG.INIT_SCHEMA TO APPLICATION AMBERPROC;
      GRANT USAGE ON INTEGRATION AMBERPROC_EXT_INT TO APPLICATION AMBERPROC;
      GRANT READ ON SECRET AMBERPROC_CONFIG.INIT_SCHEMA.AMBERPROC_API_KEY TO  APPLICATION AMBERPROC;

4.    Check Network Configuration: Confirm that the network rule AMBERPROC_NETWORK_RULE accurately reflects the required egress settings for accessing api.openai.com. Incorrect network configurations can prevent AmberProc from communicating with external services.
   

       CREATE OR REPLACE NETWORK RULE AMBERPROC_NETWORK_RULE
       MODE = EGRESS
       TYPE = HOST_PORT
       VALUE_LIST = ('13.233.247.11:3000');

5.    Application Initialization Parameters: Check the initialization parameters provided to the AMBERPROC_CONFIG.INIT_SCHEMA.INIT_APP procedure. Ensure that the secret_name and external_access_integration_name accurately reflect the created resources.

        CALL AmberProc.app_functions.init_app(PARSE_JSON('{
         "secret_name": "AMBERPROC_CONFIG.INIT_SCHEMA.AMBERPROC_API_KEY",
          "external_access_integration_name": "AMBERPROC_EXT_INT",
           }'));

For further assistance with setting up AmberProc, consult the documentation or reach out to our support team. 

Future Plans

AmberFlux is constantly working on enhancing AmberProc for Snowflake. Moving forward, the application’s functionalities will be upgraded in the following ways:

 

1. Dataset Expansion: To include broader aspects of Snowflake’s capabilities, enhancing the application’s utility and assisting users in other aspects of Snowflake and its sub applications.

2. Model Options: Additional Models using Snowflake Cortex like Llama3 and Claude3 will be integrated to offer customers the option to choose the model of their choice. All the models will be fine-tuned on our dataset to ensure quality assistance.