Home / Data Platform / Warehouses

Data PlatformHow-toBeginner

Snowflake basics: warehouses, roles and your first queries without wasting credits

A beginner's guide to Snowflake - how warehouses, databases, schemas and roles fit together, run your first queries, load a small file, set up access properly and avoid leaving compute running.

Deepak Bhardwaj5 min read

Applies toSnowflake (all editions), Snowsight, Snowflake CLI

The goal

You've been given a Snowflake login and need to run queries and support a small team. This post explains the handful of ideas that cause most early confusion, along with SQL you can try right away.

Four ideas to learn first

Idea What it is Why it matters
Warehouse The compute that runs queries It uses credits while running. Storage is separate
Database and schema Folders that hold tables Full names look like DB.SCHEMA.TABLE
Role A set of permissions You act as a role, not as a user
Context Your current role, warehouse, database and schema Wrong context causes most "doesn't exist" errors

Step 1: see where you are

Run this in a Snowsight worksheet, or with the Snowflake CLI (snow sql -q "..."):

SELECT CURRENT_USER(), CURRENT_ROLE(), CURRENT_WAREHOUSE(),
       CURRENT_DATABASE(), CURRENT_SCHEMA(), CURRENT_REGION();

SHOW GRANTS;

Roles you may be able to use:

SELECT CURRENT_AVAILABLE_ROLES();
USE ROLE SYSADMIN;

Step 2: create a small warehouse

USE ROLE SYSADMIN;

CREATE WAREHOUSE IF NOT EXISTS WH_LEARN
  WAREHOUSE_SIZE = 'XSMALL'
  AUTO_SUSPEND = 60
  AUTO_RESUME = TRUE
  INITIALLY_SUSPENDED = TRUE;

USE WAREHOUSE WH_LEARN;
  • AUTO_SUSPEND = 60 stops it after 60 idle seconds, so it stops using credits.
  • AUTO_RESUME = TRUE starts it again when a query arrives.
  • Each size up doubles the credits per hour. Start small, and go up only if a query really needs it.

Learn more in warehouse sizing.

Step 3: run your first queries

Many accounts include read-only sample data:

SELECT O_ORDERSTATUS, COUNT(*) AS ORDERS, SUM(O_TOTALPRICE) AS TOTAL
FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.ORDERS
GROUP BY O_ORDERSTATUS
ORDER BY ORDERS DESC;

If that database isn't available, an admin may need to add it, or you can skip to Step 4.

Run the same query again. It should come back almost immediately from the result cache, without using the warehouse.

Useful commands for looking around:

SHOW DATABASES;
SHOW SCHEMAS IN DATABASE SNOWFLAKE_SAMPLE_DATA;
SHOW TABLES IN SCHEMA SNOWFLAKE_SAMPLE_DATA.TPCH_SF1;
DESCRIBE TABLE SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.ORDERS;
SELECT * FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.ORDERS LIMIT 10;

Use LIMIT or SAMPLE while exploring, rather than SELECT * on large tables.

Step 4: create a table and load data

CREATE DATABASE IF NOT EXISTS LEARN_DB;
CREATE SCHEMA IF NOT EXISTS LEARN_DB.RAW;
USE SCHEMA LEARN_DB.RAW;

CREATE OR REPLACE TABLE ORDERS (
  ORDER_ID   NUMBER,
  CUSTOMER   STRING,
  AMOUNT     NUMBER(10,2),
  ORDER_DATE DATE
);

CREATE OR REPLACE STAGE ORDERS_STAGE;

Upload a local CSV with a client that supports PUT, such as the Snowflake CLI, then load it:

snow sql -q "PUT file://./orders.csv @LEARN_DB.RAW.ORDERS_STAGE AUTO_COMPRESS=TRUE"

COPY INTO ORDERS
FROM @ORDERS_STAGE
FILE_FORMAT = (TYPE = CSV SKIP_HEADER = 1 FIELD_OPTIONALLY_ENCLOSED_BY = '"')
ON_ERROR = 'ABORT_STATEMENT';

SELECT COUNT(*) FROM ORDERS;

Snowflake remembers which files it has loaded, so running COPY INTO again won't load the same file twice. See load errors if it fails.

Tip

In Snowsight, you can also load a small file through the table's Load Data option, with no client needed.

Step 5: give a teammate access

Grant privileges to a role, then give the role to people. Avoid granting directly to users.

USE ROLE SECURITYADMIN;

CREATE ROLE IF NOT EXISTS ANALYST_RO;

GRANT USAGE ON WAREHOUSE WH_LEARN TO ROLE ANALYST_RO;
GRANT USAGE ON DATABASE LEARN_DB TO ROLE ANALYST_RO;
GRANT USAGE ON SCHEMA LEARN_DB.RAW TO ROLE ANALYST_RO;
GRANT SELECT ON ALL TABLES IN SCHEMA LEARN_DB.RAW TO ROLE ANALYST_RO;
GRANT SELECT ON FUTURE TABLES IN SCHEMA LEARN_DB.RAW TO ROLE ANALYST_RO;

GRANT ROLE ANALYST_RO TO USER PRIYA;
GRANT ROLE ANALYST_RO TO ROLE SYSADMIN;

ALTER USER PRIYA SET DEFAULT_ROLE = ANALYST_RO, DEFAULT_WAREHOUSE = WH_LEARN;

All three USAGE grants are needed. SELECT alone isn't enough, because the role must also be able to reach the database and schema. Granting a custom role to SYSADMIN keeps admins able to manage what it owns. Depending on who owns the objects, you may need a role with the right to grant on them.

Warning

Don't use ACCOUNTADMIN for everyday work. Keep it for account-level tasks, and require MFA on it. Objects you create under it are owned by it, and other roles then can't see them.

Common first errors

Error Usual cause Fix
Object does not exist or not authorized Wrong role or context, or a typo Use the full name and check your role. See the full guide
No active warehouse selected No warehouse in your context USE WAREHOUSE WH_LEARN;
Insufficient privileges to operate on warehouse Your role lacks USAGE Ask for a grant
Table is missing, though a colleague sees it They used another role Try the same role
Lowercase table name not found It was created in double quotes Snowflake stores unquoted names in uppercase; use the same quotes

Keep an eye on spending

SELECT WAREHOUSE_NAME, SUM(CREDITS_USED) AS CREDITS
FROM SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY
WHERE START_TIME > DATEADD(day, -7, CURRENT_TIMESTAMP())
GROUP BY 1
ORDER BY 2 DESC;

This view can lag behind by a few hours, and you need a role with access to it. See what you've run lately with the Account Usage cheat sheet.

Undo a mistake

Time Travel lets you read old data or restore a dropped table within the retention period:

SELECT * FROM ORDERS AT (OFFSET => -60*10);  -- 10 minutes ago
DROP TABLE ORDERS;
UNDROP TABLE ORDERS;

More in UNDROP and Time Travel.

Prevent it next time

  • Set AUTO_SUSPEND on every warehouse, and add a resource monitor.
  • Give each team its own warehouse, so spending is easy to see.
  • Follow the account baseline before real data arrives.

Related fixes