#049: How to use SnowSQL | Install, Config & Query

SnowSQL is the command line client for Snowflake. You install it on your machine, store your account, user and connection defaults in a config file at ~/.snowsql/config, then type snowsql to open a session and run the same SQL you'd run in the web UI. It handles DDL and DML, loading and unloading data, and it's the tool you reach for when you want to script Snowflake work or push files into an internal stage. Setup takes about ten minutes. The two places people get stuck are the shell profile the installer writes to and the plain text config file, and both are covered below.

Install SnowSQL

Most of the time you work with Snowflake in the web UI, writing queries and browsing database objects. SnowSQL is a different way in: a terminal application that connects with the same credentials.

The command line can feel intimidating, so a lot of people avoid it. Once it's set up, though, it's the same Snowflake, just typed instead of clicked.

Download the client

• In the Snowflake UI, go to Help, then Download. The CLI client, SnowSQL, is the first option.
• Open the Snowflake repository and find the build for your machine. Windows and Linux are labelled as you'd expect. Darwin is the Mac build.
• Pick the most recently updated version and run the package installer.

The installer tells you two things worth noticing. It's going to add SnowSQL to your shell profile, and it's going to create a config file in a hidden directory called .snowsql. Both come up again below.

When it finishes, you get a success message and a command you can run to test the connection.

Fix the command not found error

Open a new terminal and type snowsql. Ideally it runs. On my Mac it came back with command not found, and the reason is a shell mismatch worth knowing about.

The installer said it would add the path to the bash profile. The default shell on a Mac is zsh, so it actually wrote the line into .zshrc. I run bash and didn't have a .bash_profile at all, so bash had no idea where SnowSQL lived.

The fix is to create the profile bash reads and copy the path line into it:

touch ~/.bash_profile

Open ~/.zshrc, find the line the installer added, and paste it into the new file. It looks like this:

export PATH=/Applications/SnowSQL.app/Contents/MacOS:$PATH

Quit and restart the terminal, then run snowsql again. A short delay means it's working. If your shell found the command on the first try, skip this step entirely.

On that first run you'll probably see an error about the log file. That's expected, and it's the first thing we fix in the config.

Set up the config file

Go to your home directory, the same place you created the bash profile. The .snowsql directory is hidden, so if you're browsing in Finder press Shift, Command and period to toggle hidden files. Inside it is a file called config.

Everything in this file is plain text, including your password. The file says so right at the top. Treat it as local only, never commit it to version control, and be careful about who can read your machine.

Fix the log file error

Scroll down to the log_file setting. By default it points one directory up with two dots, which is what triggers the error. Remove one of the dots:

log_file = ./snowsql_rt.log

Save, run snowsql again, and the error is gone.

Set your default connection

The connections section holds your defaults. Every line starts commented out with a pound sign. If you only need one way to connect, this is the only section you fill in.

[connections]
accountname = your_account_id
region = east-us-2.azure
username = your_username
password = your_password
dbname = analytics
schemaname = marts
warehousename = developer
rolename = accountadmin

A few notes on the values:

• Account name is the account identifier from your Snowflake URL, not the full hostname.
• Region includes the cloud provider. Mine is East US 2 on Azure, so east-us-2.azure.
• Database, schema and warehouse are optional defaults. I set analytics, marts and a developer warehouse so short queries don't need everything qualified.
• Role is the role the session starts in. Pick the least privileged one that does the job.

Then remove the pound signs from each line you set, so they're live rather than comments. Leave the rest of the file alone for now.

The password has to be real

I tried saving the file with the password masked and connecting. It failed. SnowSQL reads the file literally, so the password has to be there in full. Add it, save, and run snowsql.

This time it connects. You see the SnowSQL version, the user you're logged in as, and the warehouse, database and schema from your defaults. You're in.

Run SQL commands

From here it's as if you were in the web UI with the same credentials. It's just a different way of working with the same objects.

A select statement

Start with a query against something in your default schema:

select * from analytics.marts.customers;

Always close the statement with a semicolon. Without one, I've had SnowSQL sit there and hang, because it doesn't know the statement has ended. The results come back formatted as a table in the terminal.

Create an object

You probably won't be running day to day selects this way, the UI is better for that. But any DDL works too. As a sample:

create database snowsql_test;

It reports success. Refresh the object browser in the UI and SNOWSQL_TEST is sitting there, created from the command line.

Named connections

The defaults cover one account and one user. If you connect in different ways for different purposes, add a named connection block back in the config file:

[connections.my_example_connection]
accountname = another_account_id
username = another_user
password = another_password

Then start SnowSQL with the -c flag and the connection name:

snowsql -c my_example_connection

Anything set in the named block replaces the default for that session. Anything you leave out falls back to the defaults.

Exit the session

!exit

Commands that start with an exclamation point are SnowSQL commands rather than SQL.

Where SnowSQL earns its place

Running a select from the terminal is a demo, not the use case. Three places it actually pays off:

• Automation. Anything you can type into SnowSQL you can put in a script and run on a schedule or from a pipeline step.
• Loading data. The PUT command pushes local files into an internal stage, and that only works from a client like SnowSQL, not the web UI.
• Multiple sessions. Named connections make it easy to switch between accounts, users and roles without re-entering anything.

And one reminder: everything that makes those convenient is sitting in a plain text file in your home directory. Keep it there, keep it out of git, and you'll be fine.

Related reading

• Why Data Teams Need Version Control
• The 4-Role Security Model for Your Data Warehouse
• Data Automation (CI/CD) with a Real Life Example
• How to Run Queries from the Terminal (dbt Show Command)

Final takeaway

Most teams I work with never leave the Snowflake UI until the first bulk file load or scheduled script forces the command line on them in a hurry. Set SnowSQL up once, on a quiet afternoon, and keep that config file private. The day you need it, it's a one word command.