description:

How the assistant works

Using the SMA AI assistant¶

You can use the SMA AI assistant to analyze and answer questions based exclusively on assessment data and documentation produced during the SMA migration assessment process.

How the assistant works¶

The assistant works on top of the assessment reports generated by the SMA (Snowflake Migration Accelerator). The process is divided into four main phases:

• Activation
• Processing questions and generating SQL
• Retrieving answers
• Formatting and delivering response

Phase 1: Activation and data ingestion¶

1. Activate the assistant.

   The assistant is launched directly from the SMA application. Upon launch, you’ll be prompted to enter your Snowflake account credentials.

   

2. Configure authentication.

   Select the appropriate authentication method for connecting to the Snowflake account. The assistant supports multiple authentication types to accommodate different security requirements.

   

3. Test the connection.

   Before proceeding, test the connection to ensure proper access to the Snowflake account. A successful connection test is required to activate the assistant, which then creates the necessary infrastructure (tables, schemas, etc.) in your Snowflake environment.

   

4. Provision infrastructure.

   Once the connection is validated, the assistant automatically provisions a dedicated table structure within your Snowflake account for storing and managing documentation.

   

5. Ingest assessment data. Upload all relevant assessment reports generated by the SMA. The assistant accepts both .csv and .docx files. Each uploaded file is mapped to a separate table in Snowflake, maintaining a one-to-one relationship between source files and database tables.

   

Phase 2: Question processing and SQL generation (RAG)¶

Once the infrastructure is ready, you can begin asking questions through the chat interface. Each question triggers a sophisticated retrieval-augmented generation (RAG) process powered by Snowflake Cortex.

LLM selection¶

The assistant interface allows you to select from different Large Language Models (LLMs) to power the question-answering process. The available LLM options vary depending on the region where your Snowflake account is hosted, as Snowflake Cortex model availability differs across geographic regions.

1. Semantic documentation search

   The assistant performs a semantic search against a vectorized documentation table in the Snowflake account to identify most relevant documents and data sources that can potentially answer your query.

2. Context-aware SQL generation

   Using the identified documentation as context, Snowflake Cortex generates a targeted SQL query designed to extract the precise information needed to answer your question.

3. SQL validation (self-correction loop 1)

   The generated SQL query undergoes validation by passing it back to Snowflake Cortex along with the original question to ensure the query logically produces an answer that addresses the user’s intent.

Phase 3: Answer retrieval and refinement¶

The answer retrieval process adapts based on the SQL validation results, ensuring the most accurate response possible.

The following scenarios describe what might happen.

Scenario A: Valid SQL query¶

• The validated SQL query is executed against the assessment data tables.
• The query results are extracted and prepared as the preliminary answer.

Scenario B: Invalid or insufficient SQL query¶

• When SQL generation is not viable, the assistant falls back to the summary .docx file as context.
• Snowflake Cortex generates a preliminary answer based on the documentation content rather than structured data.

Final answer verification (self-correction loop 2)¶

Before presenting the answer, the assistant performs a final quality check:

• The preliminary answer is evaluated by Snowflake Cortex to verify it logically addresses the original question.
  • If validated: The answer is accepted and moves to formatting.
  • If not validated: The question is posed directly to Snowflake Cortex as a general inquiry (without specific documentation context), and this response becomes the final answer.

Phase 4: Response formatting and delivery¶

Natural language formatting¶

The final answer is processed one last time by Snowflake Cortex to transform it into a clear, conversational response that’s easy to understand and properly formatted for the chat interface.

The formatted response is then displayed in the chat window, complete with proper structure, context, and any relevant details extracted from the assessment data.

Assessment report files and artifacts¶

The following files are uploaded to your account and serve as the data sources for the assistant:

Database schema reference¶

This section describes the database tables created in your Snowflake account when using the assistant. These tables store migration assessment data, code inventories, and metadata used by the assistant to provide context-aware responses.

Schema overview¶

The assistant system creates tables to store:

• Migration assessment data: Results from code analysis including dependencies, issues, and usage patterns
• Code inventories: Detailed tracking of various code elements (imports, functions, DataFrames, etc.)
• Documentation metadata: Vector embeddings for semantic search capabilities
• Execution tracking: Records of tool executions and their results

Most tables include an EXECUTIONID column to associate records with specific migration assessment runs.

Table reference¶

DOCUMENTATION_METADATA¶

Stores documentation text with vector embeddings for semantic search capabilities. Used by the assistant to find relevant context based on user questions using vector similarity.

ARTIFACTDEPENDENCYINVENTORIES¶

Lists the artifact dependencies of each file analyzed by the SMA. This inventory allows the user to determine which artifacts are needed for the file to work properly in Snowflake.

The following are considered artifacts: a third-party library, SQL entity, source of a read or write operation, and another source code file in the workload.

CHECKPOINTSINVENTORIES¶

Lists the generated checkpoints for the user workload. These checkpoints are completely capable of being used in the Checkpoints Feature from the Snowflake Extension.

DATAFRAMESINVENTORIES¶

Lists the DataFrame assignments to use when generating checkpoints for the user workload

DBXELEMENTSINVENTORIES¶

Lists the DBX (Databricks) elements found inside notebooks.

DETAILEDREPORTS¶

Stores detailed migration assessment reports for each execution. Used by the assistant to provide context-specific answers about migration assessments.

EXECUTIONFLOWINVENTORIES¶

Lists the relations between the different workload scopes, based on the function calls found. This inventory’s main purpose is to serve as the base for the entry points identification.

IMPORTUSAGESINVENTORIES¶

Contains all the referenced import calls in the codebase. An import is classified as an external library that gets imported at any point in the file.

INPUTFILESINVENTORIES¶

Similar to the files inventory, lists every file by filetype and size.

IOFILESINVENTORIES¶

Lists all external elements that are being read from or written to in the codebase.

ISSUES¶

Lists every conversion issue found in the codebase. A description, the exact location of the issue in the file, and a code associated with that issue will be reported.

JOINSINVENTORIES¶

Contains an inventory of all DataFrame joins done in the codebase.

NOTEBOOKCELLSINVENTORIES¶

Gives an inventory of all cells in a notebook based on the source code for each cell and the lines of code in that cell.

NOTEBOOKSIZEINVENTORIES¶

Lists the size in lines of code of different source languages present in notebook files.

PACKAGESINVENTORIES¶

Tracks package usage in the codebase.

PANDASUSAGESINVENTORIES¶

[Python Only] Lists every reference to the Pandas API present in the scanned codebase.

SPARKUSAGESINVENTORIES¶

Shows the exact location and usage for each reference to the Spark API. This information is used to build the Readiness Score.

SQLEMBEDDEDUSAGEINVENTORIES¶

Contains a count of SQL keywords present in SQL Spark elements.

SQLFUNCTIONSINVENTORIES¶

Inventories SQL functions used in the code with their categories and migration status.

THIRDPARTYUSAGESINVENTORIES¶

Tracks third-party library and package usage found during code analysis.

TOOLEXECUTIONS¶

Tracks tool execution metadata including timing, results, and version information.

Assessment files to table mapping¶

The following CSV files from assessment exports map to their corresponding database tables:

Schema notes¶

• The DOCUMENTATION_METADATA table uses vector embeddings (768 dimensions) for semantic search.
• Tables are created with CREATE OR REPLACE to ensure clean setup on each initialization.

Stored procedures¶

The assistant creates several stored procedures in your Snowflake account to power the natural language query processing capabilities. These procedures leverage Snowflake Cortex AI to interpret questions, generate SQL, and format responses.

GET_CHATBOT_RESPONSE¶

The primary stored procedure that processes natural language questions and returns human-readable answers using Snowflake Cortex AI.

Signature¶

GET_CHATBOT_RESPONSE(IA_MODEL VARCHAR, QUESTION VARCHAR, EXECUTION_ID VARCHAR)
RETURNS VARCHAR

Parameters¶

Returns¶

VARCHAR - A human-readable answer to the user’s question

Workflow¶

The procedure implements a multi-step process:

1. Context Retrieval (RAG)

   • Performs vector similarity search on DOCUMENTATION_METADATA table.
   • Uses SNOWFLAKE.CORTEX.EMBED_TEXT_768 with the ‘e5-base-v2’ model.
   • Retrieves the most relevant documentation using VECTOR_COSINE_SIMILARITY.

2. SQL Generation

   • Constructs a prompt with the retrieved context and user question.
   • Calls SNOWFLAKE.CORTEX.COMPLETE to generate a SQL query.
   • The generated query is scoped to the specific EXECUTION_ID.

3. SQL Validation (Self-Correction Loop 1)

   • Validates whether the generated SQL logically answers the question.
   • Uses Snowflake Cortex to perform a yes/no validation check.

4. Fallback Path (if SQL is invalid)

   • Retrieves full report context from DETAILEDREPORTS table.
   • Attempts to answer using the detailed report as context.
   • Performs another validation check on the answer quality.
   • Falls back to general knowledge if context-based answer is insufficient.

5. SQL Execution (if SQL is valid)

   • Executes the generated SQL query dynamically.
   • Stores results in a temporary table for processing.
   • Handles single results, multiple results (aggregates up to 10), and empty results.

6. Response Formatting

   • Creates a final prompt combining the question and query results.
   • Calls Snowflake Cortex again to format the answer as a friendly, natural sentence.
   • Returns the human-readable response.

Error Handling¶

• Returns "I could not find any data that matched your request." when query returns zero rows.
• Automatically falls back through multiple strategies to ensure a meaningful answer.

Example¶

CALL GET_CHATBOT_RESPONSE(
    'llama3.1-70b',
    'How many Python files were analyzed?',
    'ABC123XYZ'
);

GET_CURRENT_REGION_DETAILS¶

Retrieves information about the current Snowflake account’s region, including cloud provider and region display name.

Signature¶

GET_CURRENT_REGION_DETAILS()
RETURNS TABLE (
    CLOUD_PROVIDER VARCHAR,
    CLOUD_REGION_NAME VARCHAR,
    CLOUD_REGION_DISPLAY_NAME VARCHAR
)

Parameters¶

None

Returns¶

A table with three columns:

Workflow¶

1. Executes SHOW REGIONS to populate the result set cache.
2. Queries the result using RESULT_SCAN(LAST_QUERY_ID()).
3. Filters to only the region matching CURRENT_REGION().
4. Returns the filtered result as a table.

Purpose¶

This procedure is used to determine which Snowflake Cortex AI models are available, as model availability varies by region. The assistant uses this information to populate the LLM selection dropdown with region-specific options.

Example¶

CALL GET_CURRENT_REGION_DETAILS();

Output¶

Stored procedure notes¶

• Both procedures are created with CREATE OR REPLACE to ensure clean setup.
• GET_CHATBOT_RESPONSE executes as OWNER to access the necessary tables and Snowflake Cortex functions.
• The procedures use Snowflake’s dynamic SQL capabilities (EXECUTE IMMEDIATE) for flexible query execution.
• Vector embeddings use the ‘e5-base-v2’ model with 768 dimensions for semantic search.
• The multi-step validation process ensures high-quality, contextually relevant answers.

Troubleshooting¶

Resetting the assistant infrastructure¶

If you need to re-create the assistant infrastructure from scratch, you must first delete the local configuration file.

Configuration file location¶

• macOS/Linux: ~/.smachatbot/config.json
• Windows: %USERPROFILE%.smachatbotconfig.json

This JSON configuration file contains a composite key consisting of:

• snowflake_identifier
• snowflake_user
• snowflake_role

To reset the assistant:

1. Delete the config.json file from the appropriate directory:

   • macOS/Linux: ~/.smachatbot/config.json
   • Windows: %USERPROFILE%.smachatbotconfig.json

2. Re-run the assistant initialization process

   Note

   Deleting this file will remove all stored connection settings and require you to reconfigure the assistant with your Snowflake credentials.

Known issues / FAQs¶

Snowflake connection caching with VPN¶

Issue¶

When testing the connection to Snowflake while a VPN is required but not yet connected, the Snowflake driver caches the failed connection state. Even after successfully connecting to the VPN, subsequent connection attempts may still fail due to this cached state.

Workaround¶

If you experience connection failures after connecting to your VPN:

1. Close and restart the SMA application.

2. Attempt the connection test again with the VPN active from the start.

   Tip

   Always ensure your VPN is connected before initiating any Snowflake connection tests to avoid this caching behavior.