Snow SQL

 What is Snow SQL?

Snow SQL is CLI (Command line interface for Snowflake). 

Note : when you use snow SQL, for each command you execute , you will have a different session Id.

Snow SQL Configuration :

we need to configure CLI for not to keep on asking us for username and password.

go to config file in your system

C:\Users\<userName>\.snowsql\config

Ex : 
[connections.training]
#Can be used in SnowSql as #connect example
accountname = xxxxxx-xx1111                          

username = <username>
password = <password>
dbname = <database name>
schemaname = <schemaname>

now we can connect using the command as below in powershell or cmd

snowflake -c training

What is the purpose of Snow SQL?

Querying Data: SnowSQL allows users to write and execute SQL queries against Snowflake databases. It provides a command-line interface for running SELECT, INSERT, UPDATE, DELETE, and other SQL statements, enabling users to retrieve and manipulate data.

Database Administration: SnowSQL provides functionality for managing database objects and performing administrative tasks. Users can create and modify database schemas, tables, views, stored procedures, and other database entities using SnowSQL commands.

Scripting and Automation: SnowSQL supports scripting capabilities, allowing users to write scripts to automate repetitive tasks or complex data operations. Users can create scripts that include multiple SQL statements and execute them using SnowSQL in batch mode.

Data Loading and Unloading: SnowSQL provides features for loading data into Snowflake from various file formats and unloading data from Snowflake to files. Users can use SnowSQL commands to efficiently load and unload data, transforming it as needed during the process.

we can load files into staging using commands as below in snow sql

put file://<source_path>//<Source file Name>  @~; 

list @~; 

Managing Security: SnowSQL enables users to manage security aspects of Snowflake databases. Users can create and manage user accounts, roles, and privileges using SnowSQL commands, ensuring secure access and data protection within the Snowflake environment.

 SnowSQL, establishes a new session for each query execution. There are a few reasons for this

Having a different session ID for each query in Snowflake allows for efficient query isolation, session state management, query monitoring, and resource allocation, enabling better performance, scalability, and resource utilization in a multi-tenant environment.

More reading on this :

Query Isolation: Snowflake provides a multi-tenant architecture where multiple users and queries can run concurrently on the same cluster. Assigning a unique session ID to each query allows Snowflake to isolate the resources and results of different queries. This ensures that queries from different sessions do not interfere with each other and can execute independently.

Session State Management: Each session in Snowflake maintains its own state and context, including temporary tables, session variables, transaction state, and query history. By assigning a unique session ID to each query, Snowflake can manage and maintain the state and context specific to that session accurately.

Query Monitoring and Tracking: Having distinct session IDs for each query helps in monitoring and tracking the execution of queries. The session ID allows Snowflake to identify the specific session associated with a query, facilitating query logging, performance monitoring, and troubleshooting.

Resource Allocation and Management: Snowflake allocates resources such as CPU, memory, and storage on a per-session basis. By assigning a unique session ID to each query, Snowflake can accurately allocate and manage resources for each query independently based on the workload and resource usage of that specific session.