Snowflake
SQL Learning Guide

USE ROLE ACCOUNTADMIN;
SHOW ROLES;
SHOW GRANTS TO ROLE SECURITYADMIN;

USE ROLE SYSADMIN;
CREATE DATABASE FILMS_DB;
CREATE SCHEMA FILMS_SCHEMA;
CREATE TABLE FILMS_SYSADMIN (
  ID           STRING,
  TITLE        STRING,
  RELEASE_DATE DATE,
  RATING       INT
);

USE ROLE SECURITYADMIN;
CREATE ROLE ANALYST;
GRANT USAGE ON DATABASE FILMS_DB TO ROLE ANALYST;
GRANT USAGE, CREATE TABLE ON SCHEMA FILMS_DB.FILMS_SCHEMA TO ROLE ANALYST;
GRANT USAGE ON WAREHOUSE COMPUTE_WH TO ROLE ANALYST;
GRANT ROLE ANALYST TO ROLE SYSADMIN;
GRANT ROLE ANALYST TO USER ADMIN;

GRANT USAGE ON FUTURE SCHEMAS IN DATABASE FILMS_DB TO ROLE ANALYST;
USE ROLE SYSADMIN;
CREATE SCHEMA MUSIC_SCHEMA;
CREATE SCHEMA BOOKS_SCHEMA;
USE ROLE USERADMIN;
CREATE USER Rajesh
  PASSWORD='temp'
  DEFAULT_ROLE = analyst
  DEFAULT_WAREHOUSE='COMPUTE_WH'
  MUST_CHANGE_PASSWORD=TRUE;
USE ROLE SECURITYADMIN;
GRANT ROLE analyst TO USER Rajesh;
USE ROLE SYSADMIN;
DROP DATABASE FILMS_DB;

USE ROLE MASKING_ADMIN;
CREATE OR REPLACE MASKING POLICY EMAIL_MASK
  AS (VAL STRING) RETURNS STRING ->
  CASE
    WHEN CURRENT_ROLE() IN ('ANALYST') THEN VAL
    ELSE REGEXP_REPLACE(VAL, '.+\@', '******@')
  END;
ALTER TABLE CUSTOMERS MODIFY COLUMN EMAIL
  SET MASKING POLICY EMAIL_MASK;

CREATE OR REPLACE ROW ACCESS POLICY CUSTOMER_POLICY
  AS (COUNTRY_CODE VARCHAR) RETURNS BOOLEAN ->
  'SYSADMIN' = CURRENT_ROLE()
    OR EXISTS (
      SELECT 1 FROM TITLE_COUNTRY_MAPPING
        WHERE TITLE = CURRENT_ROLE()
          AND COUNTRY_ISO_CODE = COUNTRY_CODE
    );
ALTER TABLE CUSTOMERS DROP ALL ROW ACCESS POLICIES;
ALTER TABLE CUSTOMERS ADD ROW ACCESS POLICY CUSTOMER_POLICY ON (COUNTRY_CODE);

CREATE OR REPLACE TAG business_unit
  ALLOWED_VALUES 'sales', 'HR', 'operations';
ALTER WAREHOUSE compute_wh
  SET TAG business_unit = 'sales';
CREATE DATABASE operations_db TAG (business_unit = 'operations');
ALTER TABLE customer
  ALTER COLUMN NAME
  SET TAG security_objects_db.security_objects_schema.protected_data = 'PII';

CREATE WAREHOUSE DATA_ANALYSIS_WAREHOUSE
  WAREHOUSE_SIZE = 'SMALL'
  AUTO_SUSPEND   = 600
  AUTO_RESUME    = TRUE
  INITIALLY_SUSPENDED = TRUE;
ALTER WAREHOUSE DATA_ANALYSIS_WAREHOUSE RESUME;
ALTER WAREHOUSE DATA_ANALYSIS_WAREHOUSE SET WAREHOUSE_SIZE = LARGE;
SHOW WAREHOUSES LIKE 'DATA_ANALYSIS_WAREHOUSE';

CREATE RESOURCE MONITOR MY_MONITOR
  WITH CREDIT_QUOTA = 100
  TRIGGERS
    ON 50  PERCENT DO NOTIFY
    ON 90  PERCENT DO SUSPEND
    ON 100 PERCENT DO SUSPEND_IMMEDIATE;
ALTER WAREHOUSE COMPUTE_WH
  SET RESOURCE_MONITOR = "MY_MONITOR";

CREATE OR REPLACE WAREHOUSE MULTI_CLUSTER_WH
  WAREHOUSE_SIZE    = 'XSMALL'
  AUTO_SUSPEND      = 600
  AUTO_RESUME       = TRUE
  MIN_CLUSTER_COUNT = 1
  MAX_CLUSTER_COUNT = 3
  SCALING_POLICY    = 'STANDARD';

INSERT INTO FILMS VALUES ('fi3p9f8hu8', 'Parasite', DATE('2019-05-30'));
INSERT INTO FILMS VALUES
  ('9x3wnr0zit', 'Citizen Kane',  DATE('1942-01-24')),
  ('2wyaojnzfq', 'Old Boy',        DATE('2004-10-15'));
INSERT INTO FILMS_2000 SELECT * FROM FILMS
  WHERE RELEASE_DATE > DATE('2000-01-01');
INSERT OVERWRITE INTO FILMS_2000 SELECT * FROM FILMS;

LIST @~;
LS @%FILMS;
CREATE STAGE FILMS_STAGE;
CREATE FILE FORMAT CSV_FILE_FORMAT
  TYPE = CSV
  SKIP_HEADER = 1;
SELECT $1, $2, $3 FROM @~/films.csv;

COPY INTO FILMS
FROM @FILMS_STAGE/films.csv
FILE_FORMAT = (TYPE='CSV' SKIP_HEADER=1);

-- Transformations during load
COPY INTO FILMS FROM
( SELECT $1, $2, to_date($3)
  FROM @%FILMS/films.csv)
FILE_FORMAT = CSV_FILE_FORMAT
FORCE = TRUE;

-- VALIDATION_MODE: preview rows without inserting
COPY INTO FILMS
FROM @FILMS_STAGE/films.csv
VALIDATION_MODE = 'RETURN_ROWS';

COPY INTO FILMS
FROM @FILMS_STAGE
FILE_FORMAT = CSV_FILE_FORMAT
PATTERN = '.*[.]csv'
ON_ERROR=CONTINUE
FORCE=true;

CREATE OR REPLACE TABLE FILMS_ELT (JSON_VARIANT VARIANT);
SELECT
  json_variant:id                      AS id,
  json_variant:title                   AS title,
  json_variant:release_date::date     AS release_date,
  json_variant:actors[0]                AS first_actor,
  json_variant:ratings.imdb_rating      AS imdb_rating
FROM FILMS_ELT;

-- FLATTEN arrays to rows
SELECT
  json_variant:title,
  L.value
FROM FILMS_ELT F,
LATERAL FLATTEN(INPUT => F.json_variant:ratings) L;

SELECT
  QUERY_ID, QUERY_TEXT, USER_NAME,
  ROUND(TOTAL_ELAPSED_TIME / 1000, 2) AS ELAPSED_SEC
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE ELAPSED_SEC > 3
ORDER BY ELAPSED_SEC DESC
LIMIT 10;

-- ① Metadata cache — no warehouse needed
SELECT COUNT(*) FROM CUSTOMER;

-- ② Results cache — run same query twice
SELECT C_CUSTKEY, C_NAME FROM CUSTOMER LIMIT 1000000;
SELECT C_CUSTKEY, C_NAME FROM CUSTOMER LIMIT 1000000;

-- Cache MISS — CURRENT_TIMESTAMP() busts cache
SELECT C_CUSTKEY, CURRENT_TIMESTAMP() FROM CUSTOMER LIMIT 1000000;

-- Disable/re-enable result caching
ALTER ACCOUNT SET USE_CACHED_RESULT = FALSE;
ALTER ACCOUNT SET USE_CACHED_RESULT = TRUE;

ALTER TABLE MY_TABLE CLUSTER BY (sale_date);
SELECT SYSTEM$CLUSTERING_INFORMATION('MY_TABLE', '(sale_date)');
SELECT * FROM SNOWFLAKE.ACCOUNT_USAGE.AUTOMATIC_CLUSTERING_HISTORY;

CREATE TABLE PERMANENT_TABLE (NAME STRING, AGE INT);
CREATE TEMPORARY TABLE TEMP_TABLE (NAME STRING, AGE INT);
CREATE TRANSIENT TABLE TRANSIENT_TABLE (NAME STRING, AGE INT);
ALTER TABLE PERMANENT_TABLE SET DATA_RETENTION_TIME_IN_DAYS = 90;
CREATE SECURE VIEW SECURE_VIEW AS SELECT * FROM PERMANENT_TABLE;
CREATE MATERIALIZED VIEW MAT_VIEW AS SELECT * FROM PERMANENT_TABLE;

CREATE OR REPLACE FUNCTION DAY_NAME_ON(num_of_days int)
RETURNS STRING AS
$$
  SELECT 'In ' || CAST(num_of_days AS STRING) || ' days it will be a '
    || dayname(dateadd(day, num_of_days, current_date()))
$$;

CREATE OR REPLACE PROCEDURE TRUNCATE_TABLES(DB STRING, SCH STRING)
RETURNS STRING
LANGUAGE JAVASCRIPT
EXECUTE AS OWNER AS
$$
  var rs = snowflake.execute({sqlText: 'SHOW TABLES IN ' + DB + '.' + SCH});
  while (rs.next()){ snowflake.execute({sqlText: 'TRUNCATE TABLE ' + rs.getColumnValue(2)}); }
  return 'Done';
$$;
CALL TRUNCATE_TABLES('MY_DB', 'MY_SCHEMA');

snowsql -a <account> -u <user> -d MY_DB -s MY_SCHEMA -r SYSADMIN -w COMPUTE_WH
snowsql ... -q 'SELECT * FROM CUSTOMER LIMIT 5'
snowsql ... -f /path/to/script.sql

-- Inside interactive mode:
!set variable_substitution=true
!define table_name=customer
SELECT * FROM &table_name LIMIT 10;
PUT file:///local/data.csv @MY_STAGE;

CREATE SHARE DEMO_SHARE;
GRANT USAGE ON DATABASE DEMO_DB TO SHARE DEMO_SHARE;
GRANT USAGE ON SCHEMA DEMO_DB.DEMO_SCHEMA TO SHARE DEMO_SHARE;
GRANT SELECT ON TABLE DEMO_DB.DEMO_SCHEMA.DEMO_TABLE TO SHARE DEMO_SHARE;
ALTER SHARE DEMO_SHARE ADD ACCOUNTS = <consumer_account>;

-- Consumer side
CREATE DATABASE SHARED_DATA FROM SHARE provider_acct.DEMO_SHARE;

CREATE OR REPLACE DYNAMIC TABLE cleaned_transactions
  TARGET_LAG   = '1 minute'
  WAREHOUSE    = compute_wh
  REFRESH_MODE = AUTO
  INITIALIZE   = ON_CREATE
AS
  SELECT transaction_id, customer_id, amount,
    transaction_date::DATE AS transaction_date
  FROM raw_transactions
  WHERE status = 'completed';

ALTER DYNAMIC TABLE cleaned_transactions REFRESH;
ALTER DYNAMIC TABLE cleaned_transactions SUSPEND;
ALTER DYNAMIC TABLE cleaned_transactions RESUME;

ALTER ACCOUNT SET  DATE_OUTPUT_FORMAT = 'YYYY/MM/DD';
ALTER USER admin SET DATE_OUTPUT_FORMAT = 'DD-MM-YYYY';
ALTER SESSION SET DATE_OUTPUT_FORMAT = 'MM, DD, YYYY'; -- wins
SHOW PARAMETERS LIKE 'DATE_OUTPUT_FORMAT';
ALTER TABLE  MY_TABLE  SET DATA_RETENTION_TIME_IN_DAYS = 5;
ALTER SCHEMA MY_SCHEMA SET DATA_RETENTION_TIME_IN_DAYS = 10;

SELECT * FROM MY_TABLE
AT(OFFSET => -60*5);  -- 5 minutes ago

SELECT * FROM MY_TABLE
AT(TIMESTAMP => DATEADD(minute, -15, current_timestamp()));

SELECT * FROM MY_TABLE
BEFORE(STATEMENT => '<query_id>');

UNDROP TABLE MY_TABLE;
UNDROP SCHEMA MY_SCHEMA;
UNDROP DATABASE MY_DB;

-- Recover: create table from historical state
CREATE TABLE MY_TABLE_RESTORED AS
  SELECT * FROM MY_TABLE
  BEFORE(STATEMENT => '<truncate_query_id>');

CREATE TABLE MY_TABLE_CLONE CLONE MY_TABLE;
CREATE DATABASE MY_DB_CLONE CLONE MY_DB;

-- Clone to a point in the past (combine with Time Travel)
CREATE OR REPLACE TABLE MY_TABLE_SNAPSHOT
  CLONE MY_TABLE
  AT(OFFSET => -60*2);