1
2
3
4
5
6
# Install SnowSQL
pip install snowflake-snowsql

# Connect to Snowflake
snowsql -a <account_identifier> -u <username>

1
2
3
4
5
6
7
8
9
10
11
12
# Python (snowflake-connector-python)
import snowflake.connector

conn = snowflake.connector.connect(
    account='your_account',
    user='your_username',
    password='your_password',
    warehouse='your_warehouse',
    database='your_database',
    schema='your_schema'
)

1
2
3
4
5
6
7
8
9
10
-- Create database
CREATE DATABASE my_database;
CREATE DATABASE IF NOT EXISTS my_database;

-- Switch database context
USE DATABASE my_database;

-- Drop database
DROP DATABASE IF EXISTS my_database;

1
2
3
4
5
6
7
8
9
10
-- Create schema
CREATE SCHEMA my_schema;
CREATE SCHEMA IF NOT EXISTS my_schema;

-- Switch schema context
USE SCHEMA my_schema;

-- Drop schema
DROP SCHEMA IF EXISTS my_schema;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- Basic table creation
CREATE TABLE employees (
    id INT,
    name VARCHAR(100),
    salary DECIMAL(10,2),
    hire_date DATE
);

-- Table with constraints
CREATE TABLE departments (
    dept_id INT PRIMARY KEY,
    dept_name VARCHAR(50) NOT NULL,
    location VARCHAR(100)
);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- VARIANT: Flexible JSON-like storage
CREATE TABLE product_catalog (
    product_id INT,
    product_details VARIANT
);

-- Example VARIANT insertion
INSERT INTO product_catalog 
VALUES (1, 
    PARSE_JSON('{
        "name": "Laptop", 
        "specs": {"ram": "16GB", "storage": "512GB"}
    }')
);

-- OBJECT and ARRAY types
CREATE TABLE customer_info (
    customer_id INT,
    addresses ARRAY,
    preferences OBJECT
);

1
2
3
4
5
6
7
8
9
10
11
12
13
-- Basic SELECT
SELECT column1, column2 
FROM table_name 
WHERE condition;

-- Aggregations
SELECT 
    department, 
    AVG(salary) as avg_salary, 
    COUNT(*) as employee_count
FROM employees
GROUP BY department;

1
2
3
4
5
6
7
8
9
10
11
12
13
-- INSERT
INSERT INTO employees (id, name, salary)
VALUES (1, 'John Doe', 75000);

-- UPDATE
UPDATE employees 
SET salary = salary * 1.1 
WHERE performance_rating > 4;

-- DELETE
DELETE FROM employees 
WHERE termination_date IS NOT NULL;

1
2
3
4
5
6
7
8
9
10
11
MERGE INTO target_table t
USING source_table s
ON t.id = s.id
WHEN MATCHED THEN
    UPDATE SET 
        t.column1 = s.column1,
        t.column2 = s.column2
WHEN NOT MATCHED THEN
    INSERT (id, column1, column2)
    VALUES (s.id, s.column1, s.column2);

1
2
3
4
5
6
7
8
9
10
11
12
-- Extract JSON values
SELECT 
    product_details:name::STRING as product_name,
    product_details:specs.ram::STRING as ram
FROM product_catalog;

-- Flatten JSON arrays
SELECT 
    f.value:name::STRING as category_name
FROM product_catalog,
LATERAL FLATTEN(input => product_details:categories) f;

1
2
3
4
5
6
7
8
9
10
11
-- Create table from Parquet
CREATE TABLE parquet_data 
USING TEMPLATE (
    SELECT * FROM TABLE(
        INFER_SCHEMA(
            LOCATION=>'@my_stage/data.parquet',
            FILE_FORMAT=>'parquet_format'
        )
    )
);

1
2
3
4
5
6
7
8
9
10
11
12
13
-- Query historical data
SELECT * FROM employees 
AT (TIMESTAMP => '2023-01-01 12:00:00'::TIMESTAMP);

-- Restore table to previous state
CREATE TABLE employees_restored 
CLONE employees 
AT (TIMESTAMP => '2023-01-01 12:00:00'::TIMESTAMP);

-- Configure time travel retention
ALTER TABLE employees 
SET DATA_RETENTION_TIME_IN_DAYS = 14;

1
2
3
4
5
6
7
8
9
-- Zero-copy clone (metadata-only, no data duplication)
CREATE TABLE employees_test 
CLONE employees;

-- Clone with specific timestamp
CREATE TABLE employees_historical 
CLONE employees 
AT (TIMESTAMP => '2023-06-01 00:00:00'::TIMESTAMP);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- Create stream to track changes
CREATE STREAM employees_stream 
ON TABLE employees;

-- Create task for ETL
CREATE TASK load_processed_employees
WAREHOUSE = my_warehouse
SCHEDULE = '5 MINUTE'
WHEN SYSTEM$STREAM_HAS_DATA('employees_stream')
AS
INSERT INTO processed_employees
SELECT * FROM employees_stream;

-- Activate task
ALTER TASK load_processed_employees RESUME;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
-- Scalar UDF
CREATE FUNCTION tax_calculator(salary FLOAT)
RETURNS FLOAT
LANGUAGE SQL
AS
$$
    SELECT salary * 0.25
$$;

-- Stored Procedure
CREATE PROCEDURE update_salaries(percentage FLOAT)
RETURNS STRING
LANGUAGE SQL
AS
$$
BEGIN
    UPDATE employees 
    SET salary = salary * (1 + :percentage);
    
    RETURN 'Salaries updated successfully';
END;
$$;

1
2
3
4
5
6
7
8
-- Create clustered table
CREATE TABLE large_sales (
    sale_date DATE,
    product_id INT,
    amount DECIMAL(10,2)
)
CLUSTER BY (sale_date, product_id);

1
2
3
4
5
6
7
CREATE MATERIALIZED VIEW monthly_sales AS
SELECT 
    DATE_TRUNC('month', sale_date) as month,
    SUM(amount) as total_sales
FROM sales
GROUP BY month;

1
2
3
4
5
6
7
8
9
10
11
12
-- Create warehouse with auto-scaling
CREATE WAREHOUSE my_warehouse
WITH 
    WAREHOUSE_SIZE = 'MEDIUM'
    AUTO_SUSPEND = 60
    AUTO_RESUME = TRUE
    INITIALLY_SUSPENDED = TRUE;

-- Resize warehouse
ALTER WAREHOUSE my_warehouse 
SET WAREHOUSE_SIZE = 'LARGE';

1
2
3
4
5
6
7
-- Create role
CREATE ROLE data_analyst;

-- Grant privileges
GRANT SELECT ON DATABASE my_database TO ROLE data_analyst;
GRANT USAGE ON WAREHOUSE my_warehouse TO ROLE data_analyst;

1
2
3
4
5
6
7
8
9
10
-- Create share
CREATE SHARE sales_data_share;

-- Add database to share
ALTER SHARE sales_data_share 
ADD DATABASE sales_database;

-- Grant access to accounts
GRANT USAGE ON SHARE sales_data_share TO ACCOUNT = other_account;