How to Set Up and Use Snowflake in 2026

#!/bin/bash
# Download and install SnowSQL (macOS/Linux)
wget https://repo1.maven.org/maven2/net/snowflake/snowsql/1.3.5/snowsql-1.3.5-linux_x86_64.tgz

tar -xzf snowsql-1.3.5-linux_x86_64.tgz

sudo mv snowsql/ /opt/snowsql/

# Configure ~/.snowsql/config
cat > ~/.snowsql/config << EOF
[connections]
accountname = xy12345.us-east-1
username = YOUR_USER
password = YOUR_PASSWORD
warehouse = COMPUTE_WH
rolename = SYSADMIN
EOF

# Test connection
/opt/snowsql/snowsql -a xy12345.us-east-1 -u YOUR_USER

USE ROLE SYSADMIN;

-- Suspend if exists
ALTER WAREHOUSE IF EXISTS MY_WH SUSPEND;

-- Create XS warehouse (1 credit/hour)
CREATE WAREHOUSE MY_WH
  WAREHOUSE_SIZE = 'XSMALL'
  AUTO_SUSPEND = 300
  AUTO_RESUME = TRUE
  MIN_CLUSTER_COUNT = 1
  MAX_CLUSTER_COUNT = 2
  SCALING_POLICY = 'STANDARD';

-- Use the warehouse
USE WAREHOUSE MY_WH;

-- Check status
SELECT * FROM TABLE(INFORMATION_SCHEMA.WAREHOUSE_LOAD_HISTORY(MY_WH));

USE ROLE SYSADMIN;

-- Create database with 7-day retention
CREATE DATABASE IF NOT EXISTS MY_DB
  DATA_RETENTION_TIME_IN_DAYS = 7;

USE DATABASE MY_DB;

-- Create schema
CREATE SCHEMA IF NOT EXISTS ANALYTICS;

USE SCHEMA ANALYTICS;

-- Table for sales data (clustered on date_v)
CREATE TABLE sales (
  id INT AUTOINCREMENT,
  product VARCHAR(100),
  sale_date NUMERIC(8,0),
  quantity INT,
  amount DECIMAL(10,2)
)
CLUSTER BY (sale_date);

-- Insert test data
INSERT INTO sales (product, sale_date, quantity, amount) VALUES
('Laptop', 20260115, 1, 1200.00),
('Mouse', 20260115, 10, 25.00);

-- Create internal stage
CREATE STAGE my_stage
  DIRECTORY = ( ENABLE = TRUE )
  ENCRYPTION = ( TYPE = 'SNOWFLAKE_SSE' );

-- Upload local file (via SnowSQL or Web UI)
PUT file:///path/to/sales.csv @my_stage AUTO_COMPRESS=TRUE;

-- Copy into table (default CSV format)
COPY INTO sales
FROM @my_stage/sales.csv.gz
FILE_FORMAT = (TYPE = CSV FIELD_OPTIONALLY_ENCLOSED_BY = '"' SKIP_HEADER = 1)
ON_ERROR = 'CONTINUE';

-- Verify
SELECT COUNT(*) FROM sales;

-- Sales per day with running total
SELECT
  sale_date,
  SUM(amount) as daily_total,
  SUM(SUM(amount)) OVER (ORDER BY sale_date ROWS UNBOUNDED PRECEDING) as running_total
FROM sales
GROUP BY sale_date;

-- JSON variant support
CREATE TABLE events (
  data VARIANT
);

INSERT INTO events SELECT PARSE_JSON('{"user_id":1, "action":"click"}');

SELECT data:user_id::INT as user_id, data:action
FROM events
WHERE data:action = 'click';

-- Recover table from -1h (if retention >0)
CREATE TABLE sales_backup AS
SELECT * FROM sales AT (OFFSET => -3600);

-- Zero-copy clone (instant, no storage duplication)
CREATE TABLE sales_prod CLONE sales;

-- Update clone (independent)
UPDATE sales_prod SET amount = amount * 1.1 WHERE id = 1;

-- Check metadata
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'sales_prod';

-- External S3 stage (AWS example)
CREATE STAGE my_s3_stage
  URL = 's3://my-bucket/'
  CREDENTIALS = (AWS_KEY_ID='xxx' AWS_SECRET_KEY='yyy');

-- Auto-ingest pipe
CREATE PIPE sales_pipe
  AUTO_INGEST = TRUE
  AS
  COPY INTO sales
  FROM @my_s3_stage
  FILE_FORMAT = (TYPE = 'CSV' FIELD_DELIMITER = ',' SKIP_HEADER=1);

-- S3 notification (Lambda or NSQ)
-- Config via AWS SNS: arn:aws:sns:...

-- Check pipes
SELECT * FROM TABLE(INFORMATION_SCHEMA.PIPE_STATUS('SALES_PIPE'));
SHOW PIPES;