Snowflake Cheat Sheet

CREATE OR REPLACE TABLE orders (
  id NUMBER AUTOINCREMENT,
  customer_id NUMBER,
  total NUMBER(10,2),
  created_at TIMESTAMP_NTZ DEFAULT CURRENT_TIMESTAMP()
);

CREATE OR REPLACE TABLE recent_orders AS
SELECT * FROM orders WHERE created_at > DATEADD(day, -30, CURRENT_TIMESTAMP());

CREATE TABLE orders_backup CLONE orders;

ALTER TABLE orders RENAME TO customer_orders;

ALTER TABLE orders ADD COLUMN status VARCHAR DEFAULT 'new';

ALTER TABLE orders RENAME COLUMN total TO amount;

ALTER TABLE orders DROP COLUMN status;

ALTER TABLE orders ALTER COLUMN total SET DATA TYPE NUMBER(12,2);

TRUNCATE TABLE orders;

DROP TABLE IF EXISTS orders_backup;
UNDROP TABLE orders_backup;  -- within the Time Travel window

SELECT * FROM orders AT (OFFSET => -60 * 60);  -- one hour ago

DESC TABLE orders;

COMMENT ON TABLE orders IS 'One row per customer order.';

INSERT INTO orders (customer_id, total)
VALUES (1, 49.90), (2, 12.00);

UPDATE orders SET status = 'shipped' WHERE id = 42;

UPDATE orders o
SET total = t.amount
FROM order_totals t
WHERE t.order_id = o.id;

DELETE FROM orders WHERE created_at < DATEADD(year, -2, CURRENT_TIMESTAMP());

MERGE INTO orders t
USING orders_staging s ON t.id = s.id
WHEN MATCHED THEN UPDATE SET t.total = s.total
WHEN NOT MATCHED THEN INSERT (id, total) VALUES (s.id, s.total);

CREATE OR REPLACE TABLE orders AS
SELECT * FROM orders
QUALIFY ROW_NUMBER() OVER (PARTITION BY id ORDER BY created_at DESC) = 1;

COPY INTO orders
FROM @my_stage/orders/
FILE_FORMAT = (TYPE = CSV SKIP_HEADER = 1);

CREATE OR REPLACE VIEW recent_orders AS
SELECT * FROM orders WHERE created_at > DATEADD(day, -30, CURRENT_TIMESTAMP());

SELECT * FROM orders
QUALIFY ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at DESC) = 1;

SELECT total::VARCHAR,
       TRY_CAST(raw_amount AS NUMBER)  -- NULL instead of an error
FROM orders;

SELECT CURRENT_DATE(), CURRENT_TIMESTAMP(), SYSDATE();

SELECT DATEADD(day, 7, created_at)                 AS due,
       DATEDIFF(hour, created_at, delivered_at)    AS hours
FROM orders;

SELECT DATE_TRUNC('month', created_at) AS month, COUNT(*)
FROM orders GROUP BY 1 ORDER BY 1;

SELECT TO_CHAR(created_at, 'YYYY-MM-DD HH24:MI') FROM orders;

SELECT CONVERT_TIMEZONE('UTC', 'Europe/Helsinki', created_at) FROM orders;

SELECT e.value:sku::VARCHAR AS sku
FROM orders o, LATERAL FLATTEN(input => o.payload:items) e;

SELECT SPLIT_PART(email, '@', 2) AS domain,
       SPLIT(tags, ',')          AS tag_array
FROM customers;

SELECT customer_id, LISTAGG(sku, ', ') WITHIN GROUP (ORDER BY sku)
FROM order_items GROUP BY customer_id;

SELECT * FROM (SELECT customer_id, status, total FROM orders)
PIVOT (SUM(total) FOR status IN ('new', 'shipped', 'cancelled'));

USE WAREHOUSE analytics_wh;
USE DATABASE analytics;
USE SCHEMA public;

SHOW TABLES;

SELECT table_name FROM information_schema.tables
WHERE table_schema = CURRENT_SCHEMA();

CREATE SCHEMA IF NOT EXISTS analytics.staging;

CREATE SEQUENCE order_seq START = 1 INCREMENT = 1;
SELECT order_seq.NEXTVAL;

GRANT USAGE ON SCHEMA analytics.public TO ROLE analyst;
GRANT SELECT ON ALL TABLES IN SCHEMA analytics.public TO ROLE analyst;

SELECT table_name, row_count, bytes / POWER(1024, 3) AS gb
FROM information_schema.tables
WHERE table_schema = CURRENT_SCHEMA();

SELECT query_text, total_elapsed_time / 1000 AS seconds, bytes_scanned
FROM TABLE(information_schema.query_history())
ORDER BY start_time DESC LIMIT 20;

SELECT SYSTEM$CANCEL_QUERY('01a2b3c4-0000-0000-0000-000000000000');

SELECT CURRENT_VERSION(), CURRENT_ROLE(), CURRENT_WAREHOUSE(), CURRENT_DATABASE();