Snowflake is a cloud-based data warehousing platform that provides an SQL database engine. With SnowflakeExecutor, you can pass all the details needed to query your data in Snowflake with your deepset Cloud pipeline.

You can use SnowflakeExecutor on its own. Then, you need to provide an SQL query to it, and you'll get a database table formatted as Markdown as an output. However, a more common way is to use SnowflakeExecutor preceded by a PromptNode instructed to turn the user query into SQL, and followed by another PromptNode that answers the query based on the database tables SnowflakeExecutor retrieved.

Basic Information

Pipeline type: Used in query pipelines.
Nodes that can precede it in a pipeline: PromptNode instructed to generate SQL from the user query
Nodes that can follow it in a pipeline: PromptNode instructed to answer the query based on the tables SnowflakeExecutor fetched
Input: SQL query
Output: Database table

Usage Example

This is an example of SnowflakeExecutor being used between PromptNodes. The first PromptNode converts the user query into SQL and sends it to SnowflakeExecutor. SnowflakeExecutor contains all the database details. It connects to it and retrieves the table relevant to the SQL query. Then, it passes this table on to another PromptNode that answers the query based on it.

components:
  - name: sql_template #This is the template for the PromptNode that converts user query into SQL
    type: PromptTemplate
    params:
      prompt: |-
        You are a SQL expert working with Snowflake.
        Your task is to create a Snowflake SQL query for the given question.
        Refrain from explaining your answer. Your answer must be the SQL query in plain text format without using Markdown.
        Here are some relevant tables, a description about it, and their columns:
        {join(documents, delimiter=new_line, pattern=new_line+'Table Name: $table'+new_line+'Table Description: $content'+new_line+'Table Columns:'+new_line+'$table_columns')}
        User's question: {query}
        Generated SQL query:
  - name: PromptNodeSQLGenerator
    type: PromptNode
    params:
      default_prompt_template: sql_template
      max_length: 600 # The maximum number of tokens the generated SQL can have
      model_kwargs:
        # Specifies additional model settings
        temperature: 0
      model_name_or_path: gpt-4-turbo-preview
      truncate: false
  - name: SnowflakeExecutor
    type: SnowflakeExecutor
    params: # Pass all the database connection details here
      account: <SF-ACCOUNT>
      user: <SF-USER>
      warehouse: <SF-WAREHOUSE>
      database: <SF-DB>
      db_schema: <SF-DB-SCHEMA>
  - name: display_template # this is the prompt for a PromptNode that answers the query based on Snowflake database tables
    type: PromptTemplate
    params:
      output_parser:
        type: AnswerParser
      prompt: |-
        You are an expert data analyst.
        Your role is to answer the user's question {query} using the information in the table.
        You will base your response solely on the information provided in the table.
        Do not rely on your knowledge base; only the data that is in the table.
        Refrain from using the term "table" in your response, but instead, use the word "data"
        If the table is blank say:
        "The specific answer can't be found in the database. Try rephrasing your question."
        Additionally, you will present the table in a tabular format and provide the SQL query used to extract the relevant rows from the database in Markdown.
        If the table is larger than 10 rows, display the most important rows up to 10 rows. Your answer must be detailed and provide insights based on the question and the available data.
        SQL query:
        {results}
        Table:
        {table}
        Answer:
  - name: PromptNodeDisplay
    type: PromptNode
    params:
      default_prompt_template: display_template
      max_length: 4096 # The maximum number of tokens the generated answer can have
      model_kwargs:
        # Specifies additional model settings
        temperature: 0
      model_name_or_path: gpt-4-turbo-preview
      truncate: false
      
   pipelines:
      - name: PromptNodeSQLGenerator
        inputs: [Query]
      - name: SnowflakeExecutor
        inputs: [PromptNodeSQLGenerator]
      - name: PromptNodeDisplay
        inputs: [SnowflakeExecutor]

For an easy start, you can use one of the Text-to-SQL templates in deepset Cloud.

SnowflakeExecutor Parameters

Parameter	Type	Possible values	Description
`user`	String		Snowflake user login. Required.
`account`	String		Snowflake account identifier. Required.
`database`	String		Name of the database to use. Required.
`db_schema`	String		Name of the schema to use. Required.
`api_key`	String	Default: `None`	Snowflake account password. We recommend you set it on the Connections page in deepset Cloud. Optional.
`warehouse`	String	Default: `None`	Name of the warehouse to use. Optional.
`login_timeout`	Integer	Default: `60`	Timeout in seconds for login. By default, 60 seconds. Optional.