Snowflake SQL

Complete Notes with Examples

Query Syntax • DDL • DML • Functions • Semi-Structured • Scripting

6 6 60+ Detailed
Major Topic Topics Examples
Sections Categories Covered per Topic

SELECT, WHERE, JOINs, GROUP BY, CTEs, UNION, QUALIFY,

01 Query Syntax & Operators Subqueries

CREATE/ALTER/DROP: Databases, Tables, Views, Sequences,

02 DDL — Data Definition Clone

03 DML — Data Manipulation INSERT, UPDATE, DELETE, MERGE, COPY INTO, TRUNCATE

String, Date, Numeric, Aggregate, Window, Conditional, Type

04 Functions Conversion

VARIANT, JSON, ARRAY, OBJECT, FLATTEN, PARSE_JSON,

05 Semi-Structured Data OBJECT_KEYS

Snowflake SQL — Complete Notes Page 1

 Variables, IF/LOOP, Exceptions, Stored Procedures, Tasks,
06 Scripting & Automation Streams

Snowflake SQL — Complete Notes Page 2

 Section 1 — Query Syntax & Operators

Basic SELECT Statement

■ What it does
The SELECT statement retrieves rows from one or more tables. You can select specific columns, apply
aliases, and filter rows using WHERE.
■ Syntax

SELECT column1, column2, ...

FROM table_name
WHERE condition
ORDER BY column1 [ASC|DESC]
LIMIT n;

■ Example

SELECT employee_id, first_name, salary

FROM employees
WHERE department = 'Engineering'
ORDER BY salary DESC
LIMIT 10;

SELECT DISTINCT
■ What it does
DISTINCT eliminates duplicate rows from the result set, returning only unique combinations of the selected
columns.
■ Syntax

SELECT DISTINCT column1, column2

FROM table_name;

■ Example

SELECT DISTINCT country, city

FROM customers
ORDER BY country;

Snowflake SQL — Complete Notes Page 3

 WHERE Clause & Operators
■ What it does
The WHERE clause filters rows using comparison operators (=, !=, <, >, <=, >=), logical operators (AND, OR,
NOT), IN, BETWEEN, LIKE, and IS NULL.
■ Syntax

WHERE column = value

WHERE column IN (v1, v2, v3)
WHERE column BETWEEN low AND high
WHERE column LIKE 'pattern%'
WHERE column IS NULL

■ Example

SELECT * FROM orders

WHERE status IN ('pending', 'processing')
AND order_date BETWEEN '2024-01-01' AND '2024-12-31'
AND customer_name LIKE 'A%'
AND discount IS NOT NULL;

GROUP BY and HAVING

■ What it does
GROUP BY groups rows by one or more columns for aggregate calculations. HAVING filters the grouped
results (similar to WHERE but applied after aggregation).
■ Syntax

SELECT column, AGG_FUNC(col)

FROM table_name
GROUP BY column
HAVING AGG_FUNC(col) condition;

■ Example

SELECT department, COUNT(*) AS emp_count, AVG(salary) AS avg_sal

FROM employees
GROUP BY department
HAVING COUNT(*) > 5
ORDER BY avg_sal DESC;

Snowflake SQL — Complete Notes Page 4

 JOIN Types
■ What it does
JOINs combine rows from multiple tables. INNER JOIN returns matching rows; LEFT JOIN keeps all left rows;
RIGHT JOIN keeps all right rows; FULL OUTER JOIN keeps all rows from both sides.
■ Syntax

-- INNER JOIN
SELECT [Link], [Link] FROM a JOIN b ON [Link] = [Link];
-- LEFT JOIN
SELECT [Link], [Link] FROM a LEFT JOIN b ON [Link] = [Link];
-- FULL OUTER JOIN
SELECT [Link], [Link] FROM a FULL OUTER JOIN b ON [Link] = [Link];

■ Example

SELECT e.first_name, d.department_name, [Link]

FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id
LEFT JOIN locations l ON d.location_id = l.location_id
WHERE [Link] = TRUE;

Subqueries
■ What it does
A subquery is a query nested inside another query. Subqueries can appear in SELECT, FROM, WHERE, or
HAVING clauses. Correlated subqueries reference the outer query.
■ Syntax

-- Subquery in WHERE
SELECT col FROM t WHERE col IN (SELECT col FROM t2);
-- Subquery in FROM (derived table)
SELECT * FROM (SELECT col, AGG(col2) FROM t GROUP BY col) sub;

■ Example

SELECT employee_id, salary

FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees)
ORDER BY salary DESC;

-- Derived table example

SELECT dept, avg_sal
FROM (SELECT department AS dept, AVG(salary) AS avg_sal
FROM employees GROUP BY department) sub
WHERE avg_sal > 80000;

Snowflake SQL — Complete Notes Page 5

 CTEs (Common Table Expressions)
■ What it does
A CTE (WITH clause) defines a named temporary result set that can be referenced within the same query.
CTEs improve readability and can be referenced multiple times. Snowflake supports recursive CTEs.
■ Syntax

WITH cte_name AS (
SELECT ...
)
SELECT * FROM cte_name;

-- Multiple CTEs
WITH cte1 AS (...), cte2 AS (...)
SELECT * FROM cte1 JOIN cte2 ...;

■ Example

WITH high_earners AS (
SELECT department, AVG(salary) AS avg_sal
FROM employees
GROUP BY department
),
dept_info AS (
SELECT d.dept_name, h.avg_sal
FROM departments d
JOIN high_earners h ON d.dept_id = [Link]
)
SELECT * FROM dept_info WHERE avg_sal > 90000;

Snowflake SQL — Complete Notes Page 6

 UNION, INTERSECT, EXCEPT
■ What it does
Set operators combine results from two queries. UNION removes duplicates; UNION ALL keeps all rows.
INTERSECT returns rows in both results. EXCEPT returns rows in the first but not the second.
■ Syntax

SELECT col FROM t1

UNION [ALL]
SELECT col FROM t2;

INTERSECT / EXCEPT work the same way.

■ Example

-- All customers who bought in 2023 OR 2024

SELECT customer_id FROM orders WHERE YEAR(order_date) = 2023
UNION
SELECT customer_id FROM orders WHERE YEAR(order_date) = 2024;

-- Customers in 2023 but NOT 2024

SELECT customer_id FROM orders WHERE YEAR(order_date) = 2023
EXCEPT
SELECT customer_id FROM orders WHERE YEAR(order_date) = 2024;

QUALIFY (Window Function Filter)

■ What it does
QUALIFY filters rows after window functions are computed. It is unique to Snowflake and replaces the need to
wrap a query in a subquery just to filter on a window result.
■ Syntax

SELECT col, ROW_NUMBER() OVER (PARTITION BY x ORDER BY y) AS rn

FROM table
QUALIFY rn = 1;

■ Example

-- Get the most recent order per customer

SELECT customer_id, order_id, order_date
FROM orders
QUALIFY ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) =
1;

Snowflake SQL — Complete Notes Page 7

 CASE Expression
■ What it does
CASE is a conditional expression that evaluates conditions and returns a value. It can be used in SELECT,
WHERE, ORDER BY, and GROUP BY clauses.
■ Syntax

CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
ELSE default_result
END

■ Example

SELECT order_id,
CASE
WHEN amount >= 1000 THEN 'Large'
WHEN amount >= 500 THEN 'Medium'
ELSE 'Small'
END AS order_size
FROM orders;

Lateral JOIN & FLATTEN

■ What it does
LATERAL JOIN lets each row reference columns from previous FROM items. Combined with FLATTEN, it is
used to explode semi-structured (ARRAY/VARIANT) data into rows.
■ Syntax

SELECT [Link], [Link]

FROM table t,
LATERAL FLATTEN(input => t.array_col) f;

■ Example

SELECT order_id, [Link]:product_name::STRING AS product

FROM orders,
LATERAL FLATTEN(input => items_array) item
WHERE [Link]:qty::INT > 2;

Snowflake SQL — Complete Notes Page 8

 Section 2 — DDL: Data Definition Language

CREATE DATABASE & SCHEMA

■ What it does
Databases are top-level containers in Snowflake. Schemas sit inside databases and hold tables, views, and
other objects. Use IF NOT EXISTS to avoid errors on re-runs.
■ Syntax

CREATE DATABASE [IF NOT EXISTS] db_name

[DATA_RETENTION_TIME_IN_DAYS = n];

CREATE SCHEMA [IF NOT EXISTS] schema_name

[DATA_RETENTION_TIME_IN_DAYS = n];

■ Example

CREATE DATABASE analytics

DATA_RETENTION_TIME_IN_DAYS = 7;

CREATE SCHEMA [Link]

DATA_RETENTION_TIME_IN_DAYS = 14;

CREATE TABLE
■ What it does
Creates a permanent table. Snowflake supports all standard SQL data types plus VARIANT, ARRAY,
OBJECT for semi-structured data. Column constraints include NOT NULL, UNIQUE, DEFAULT, and
PRIMARY KEY.
■ Syntax

CREATE [OR REPLACE] TABLE table_name (

col1 datatype [NOT NULL] [DEFAULT val],
col2 datatype,
PRIMARY KEY (col1)
);

■ Example

CREATE OR REPLACE TABLE customers (

customer_id NUMBER NOT NULL PRIMARY KEY,
first_name VARCHAR(100) NOT NULL,
email VARCHAR(255) UNIQUE,
signup_date DATE DEFAULT CURRENT_DATE,
metadata VARIANT,
is_active BOOLEAN DEFAULT TRUE
);

Snowflake SQL — Complete Notes Page 9

 CREATE TABLE AS SELECT (CTAS)
■ What it does
Creates a new table from the result of a SELECT query. The table structure is derived automatically from the
query output.
■ Syntax

CREATE [OR REPLACE] TABLE new_table AS

SELECT col1, col2 FROM existing_table [WHERE ...];

■ Example

CREATE OR REPLACE TABLE active_customers AS

SELECT customer_id, first_name, email, signup_date
FROM customers
WHERE is_active = TRUE
AND signup_date >= '2023-01-01';

ALTER TABLE — Add / Modify / Drop Column

■ What it does
ALTER TABLE modifies an existing table: adding columns, changing data types, renaming columns, or
dropping them. Snowflake allows changing column type if the new type is compatible.
■ Syntax

ALTER TABLE t ADD COLUMN col datatype;

ALTER TABLE t MODIFY COLUMN col SET DEFAULT val;
ALTER TABLE t RENAME COLUMN old TO new;
ALTER TABLE t DROP COLUMN col;

■ Example

ALTER TABLE customers ADD COLUMN phone VARCHAR(20);

ALTER TABLE customers MODIFY COLUMN first_name VARCHAR(150);

ALTER TABLE customers RENAME COLUMN phone TO phone_number;

ALTER TABLE customers DROP COLUMN phone_number;

Snowflake SQL — Complete Notes Page 10

 DROP, TRUNCATE & UNDROP TABLE
■ What it does
DROP TABLE permanently removes a table. TRUNCATE removes all rows but keeps the structure. UNDROP
restores a dropped table using Snowflake's Time Travel (within the retention window).
■ Syntax

DROP TABLE [IF EXISTS] table_name;

TRUNCATE TABLE table_name;
UNDROP TABLE table_name;

■ Example

TRUNCATE TABLE temp_staging;

DROP TABLE IF EXISTS old_customers;

-- Oops! Restore it within retention period

UNDROP TABLE old_customers;

CREATE VIEW
■ What it does
A view is a named SQL query stored in the database. Views simplify complex queries, enforce security by
exposing only certain columns, and do not store data themselves.
■ Syntax

CREATE [OR REPLACE] VIEW view_name AS

SELECT col1, col2
FROM table_name
[WHERE condition];

■ Example

CREATE OR REPLACE VIEW vw_active_orders AS

SELECT o.order_id, o.order_date, [Link], o.total_amount
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE [Link] != 'cancelled';

Snowflake SQL — Complete Notes Page 11

 MATERIALIZED VIEW
■ What it does
Materialized views precompute and store the query result. They are automatically refreshed by Snowflake
when the underlying data changes, making aggregation queries much faster.
■ Syntax

CREATE MATERIALIZED VIEW mv_name AS

SELECT agg_cols
FROM table_name
[WHERE ...];

■ Example

CREATE MATERIALIZED VIEW mv_daily_sales AS

SELECT DATE_TRUNC('day', order_date) AS sale_date,
SUM(amount) AS total_sales,
COUNT(*) AS order_count
FROM orders
WHERE status = 'completed'
GROUP BY 1;

CREATE SEQUENCE
■ What it does
A sequence generates unique sequential numbers, typically used for surrogate primary keys. NEXTVAL
generates the next value; CURRVAL retrieves the current value.
■ Syntax

CREATE SEQUENCE seq_name

START = 1 INCREMENT = 1;

-- Use in INSERT
seq_name.NEXTVAL

■ Example

CREATE SEQUENCE order_seq START = 1000 INCREMENT = 1;

INSERT INTO orders (order_id, customer_id, order_date)

VALUES (order_seq.NEXTVAL, 42, CURRENT_DATE);

Snowflake SQL — Complete Notes Page 12

 CLONE (Zero-Copy Clone)
■ What it does
Snowflake's CLONE creates an instant copy of a database, schema, or table without duplicating storage. The
clone initially shares data blocks with the source and diverges only when data changes.
■ Syntax

CREATE TABLE new_table CLONE source_table;

CREATE SCHEMA new_schema CLONE source_schema;
CREATE DATABASE new_db CLONE source_db;

-- Clone at a point in time

CREATE TABLE t_backup CLONE t AT (TIMESTAMP => '2024-01-01');

■ Example

-- Quick dev copy of production table

CREATE TABLE orders_dev CLONE orders;

-- Clone before a migration for safety

CREATE TABLE customers_backup CLONE customers
AT (OFFSET => -3600); -- 1 hour ago

TEMPORARY & TRANSIENT Tables

■ What it does
Temporary tables exist only for the current session and are automatically dropped when it ends. Transient
tables persist across sessions but have limited Time Travel (max 1 day) and no Fail-safe, reducing storage
costs.
■ Syntax

CREATE TEMPORARY TABLE t (col datatype);

CREATE TRANSIENT TABLE t (col datatype);

■ Example

-- Temp table for session-only intermediate work

CREATE TEMPORARY TABLE temp_calc AS
SELECT customer_id, SUM(amount) AS total
FROM orders GROUP BY 1;

-- Transient for staging (cost-efficient)

CREATE TRANSIENT TABLE stg_orders (
order_id NUMBER, raw_data VARIANT
);

Snowflake SQL — Complete Notes Page 13

 Section 3 — DML: Data Manipulation Language

INSERT — Single & Multi-Row

■ What it does
INSERT adds new rows to a table. You can insert a single row with VALUES, multiple rows in one statement,
or insert the results of a SELECT query.
■ Syntax

-- Single row
INSERT INTO table (col1, col2) VALUES (v1, v2);

-- Multiple rows
INSERT INTO table (col1, col2) VALUES (v1,v2), (v3,v4);

-- Insert from SELECT

INSERT INTO table SELECT col1, col2 FROM other_table;

■ Example

INSERT INTO products (product_id, name, price)

VALUES (1, 'Laptop', 999.99),
(2, 'Mouse', 29.99),
(3, 'Keyboard', 79.99);

-- Load from staging

INSERT INTO orders
SELECT order_id, customer_id, amount, order_date
FROM stg_orders
WHERE order_date >= '2024-01-01';

Snowflake SQL — Complete Notes Page 14

 UPDATE
■ What it does
UPDATE modifies existing rows in a table. Always use a WHERE clause to avoid updating every row.
Snowflake supports updating from a subquery or JOIN.
■ Syntax

UPDATE table_name
SET col1 = val1, col2 = val2
WHERE condition;

-- Update from another table

UPDATE t SET [Link] = [Link] FROM source s WHERE [Link] = [Link];

■ Example

-- Update single column

UPDATE products
SET price = price * 1.10
WHERE category = 'Electronics';

-- Update from another table

UPDATE employees e
SET [Link] = s.new_salary
FROM salary_updates s
WHERE e.employee_id = s.employee_id;

DELETE
■ What it does
DELETE removes specific rows from a table based on a WHERE condition. Without WHERE, it removes all
rows (use TRUNCATE instead for a full wipe, as it is faster).
■ Syntax

DELETE FROM table_name

WHERE condition;

-- Delete using a subquery

DELETE FROM t WHERE id IN (SELECT id FROM t2 WHERE ...);

■ Example

DELETE FROM orders

WHERE status = 'cancelled'
AND order_date < DATEADD('year', -2, CURRENT_DATE);

-- Delete rows that exist in another table

DELETE FROM customers
WHERE customer_id IN (
SELECT customer_id FROM churn_list
);

Snowflake SQL — Complete Notes Page 15

 MERGE (Upsert)
■ What it does
MERGE combines INSERT, UPDATE, and DELETE in a single statement. It is ideal for incremental loads —
inserting new rows and updating existing ones based on a matching key.
■ Syntax

MERGE INTO target t

USING source s ON [Link] = [Link]
WHEN MATCHED THEN
UPDATE SET [Link] = [Link]
WHEN NOT MATCHED THEN
INSERT (col1, col2) VALUES (s.col1, s.col2)
WHEN MATCHED AND condition THEN DELETE;

■ Example

MERGE INTO customers tgt

USING customer_updates src
ON tgt.customer_id = src.customer_id
WHEN MATCHED AND src.is_deleted = TRUE THEN
DELETE
WHEN MATCHED THEN
UPDATE SET
[Link] = [Link],
[Link] = [Link],
tgt.updated_at = CURRENT_TIMESTAMP
WHEN NOT MATCHED THEN
INSERT (customer_id, email, phone, created_at)
VALUES (src.customer_id, [Link], [Link], CURRENT_TIMESTAMP);

Snowflake SQL — Complete Notes Page 16

 COPY INTO — Bulk Load
■ What it does
COPY INTO loads data from a Snowflake stage (internal or external S3/Azure/GCS) into a table. It is the
primary bulk-load mechanism and is highly parallelized.
■ Syntax

COPY INTO table_name

FROM @stage_name/path/
FILE_FORMAT = (TYPE = 'CSV' FIELD_DELIMITER = ',' SKIP_HEADER = 1)
[ON_ERROR = 'CONTINUE' | 'ABORT'];

■ Example

COPY INTO orders

FROM @my_s3_stage/data/orders/
FILE_FORMAT = (TYPE = 'CSV'
FIELD_DELIMITER = ','
NULL_IF = ('NULL', 'null', '')
SKIP_HEADER = 1
EMPTY_FIELD_AS_NULL = TRUE)
ON_ERROR = 'CONTINUE';

TRUNCATE TABLE
■ What it does
TRUNCATE removes all rows from a table instantly without logging individual row deletions. It is much faster
than DELETE for large tables but cannot be rolled back (no DML transaction).
■ Syntax

TRUNCATE TABLE table_name;

TRUNCATE TABLE IF EXISTS table_name;

■ Example

-- Clear staging before reload

TRUNCATE TABLE stg_daily_sales;

-- Load fresh data

COPY INTO stg_daily_sales
FROM @daily_stage/sales/
FILE_FORMAT = (TYPE = 'PARQUET');

Snowflake SQL — Complete Notes Page 17

 Section 4 — Functions

String: UPPER / LOWER / TRIM

■ What it does
UPPER and LOWER convert string case. TRIM removes leading/trailing whitespace (or specific characters).
LTRIM and RTRIM trim only one side.
■ Syntax

UPPER(str) / LOWER(str)
TRIM(str [, chars])
LTRIM(str [, chars]) / RTRIM(str [, chars])

■ Example

SELECT UPPER('hello world'), -- 'HELLO WORLD'

LOWER('SNOWFLAKE'), -- 'snowflake'
TRIM(' spaces '), -- 'spaces'
TRIM('***hello***', '*'); -- 'hello'

String: CONCAT / CONCAT_WS

■ What it does
CONCAT joins strings together. CONCAT_WS (Concat With Separator) joins strings with a delimiter,
automatically skipping NULL values.
■ Syntax

CONCAT(str1, str2, ...) or str1 || str2

CONCAT_WS(separator, str1, str2, ...)

■ Example

SELECT CONCAT(first_name, ' ', last_name) AS full_name,

first_name || ' ' || last_name AS full_name2,
CONCAT_WS(', ', city, state, country) AS address
FROM customers;

Snowflake SQL — Complete Notes Page 18

 String: SUBSTR / LEFT / RIGHT
■ What it does
SUBSTR (or SUBSTRING) extracts part of a string by position and length. LEFT returns the first N characters;
RIGHT returns the last N characters.
■ Syntax

SUBSTR(str, start [, length])

LEFT(str, n) / RIGHT(str, n)

■ Example

SELECT SUBSTR('Snowflake', 1, 4), -- 'Snow'

LEFT('Snowflake', 4), -- 'Snow'
RIGHT('Snowflake', 5), -- 'flake'
SUBSTR(phone, 1, 3) AS area_code
FROM contacts;

String: REPLACE / REGEXP_REPLACE

■ What it does
REPLACE substitutes all occurrences of a substring. REGEXP_REPLACE uses a regular expression pattern
for more flexible substitutions.
■ Syntax

REPLACE(str, search, replacement)

REGEXP_REPLACE(str, pattern [, replacement])

■ Example

SELECT REPLACE('Hello World', 'World', 'Snowflake'),

-- 'Hello Snowflake'

REGEXP_REPLACE(phone_number, '[^0-9]', '') AS digits_only

-- removes all non-digit characters
FROM contacts;

Snowflake SQL — Complete Notes Page 19

 String: SPLIT / SPLIT_PART
■ What it does
SPLIT splits a string on a delimiter and returns an ARRAY. SPLIT_PART returns a specific part (1-indexed)
after splitting.
■ Syntax

SPLIT(str, delimiter) -- returns ARRAY

SPLIT_PART(str, delimiter, n) -- returns nth part

■ Example

SELECT SPLIT('a,b,c', ','), -- ['a','b','c']

SPLIT_PART('2024-07-15', '-', 1), -- '2024'
SPLIT_PART('2024-07-15', '-', 2), -- '07'
SPLIT_PART(email, '@', 2) AS domain
FROM users;

String: LIKE / ILIKE / REGEXP

■ What it does
LIKE matches patterns with % (any chars) and _ (one char). ILIKE is case-insensitive. REGEXP and RLIKE
match regular expression patterns.
■ Syntax

col LIKE 'pattern%'

col ILIKE '%pattern%'
col REGEXP 'regex_pattern'
col RLIKE 'regex_pattern'

■ Example

SELECT name FROM products WHERE name ILIKE '%snowflake%';

SELECT email FROM users

WHERE email LIKE '%@[Link]'
OR email REGEXP '^[a-z]+\.[a-z]+@company\.com$';

Snowflake SQL — Complete Notes Page 20

 Date: CURRENT_DATE / CURRENT_TIMESTAMP
■ What it does
Returns the current date or timestamp in the session's timezone. GETDATE() is an alias for
CURRENT_TIMESTAMP. SYSDATE() returns UTC.
■ Syntax

CURRENT_DATE -- date only

CURRENT_TIMESTAMP -- date + time with timezone
GETDATE() -- same as CURRENT_TIMESTAMP
SYSDATE() -- UTC timestamp

■ Example

SELECT CURRENT_DATE,
CURRENT_TIMESTAMP,
SYSDATE()
;

-- Filter today's records

SELECT * FROM orders WHERE order_date = CURRENT_DATE;

Date: DATEADD / DATEDIFF

■ What it does
DATEADD adds or subtracts a time interval from a date or timestamp. DATEDIFF calculates the difference
between two dates in the specified unit.
■ Syntax

DATEADD(unit, n, date_expr)
DATEDIFF(unit, start_date, end_date)
Units: year, quarter, month, week, day, hour, minute, second

■ Example

SELECT DATEADD('day', 30, CURRENT_DATE), -- 30 days from now

DATEADD('month', -3, CURRENT_DATE), -- 3 months ago
DATEDIFF('day', '2024-01-01', '2024-12-31'), -- 365
DATEDIFF('month', hire_date, CURRENT_DATE) AS months_employed
FROM employees;

Snowflake SQL — Complete Notes Page 21

 Date: DATE_TRUNC
■ What it does
DATE_TRUNC truncates a date or timestamp to the specified precision (year, quarter, month, week, day,
hour, etc.), setting lower-order parts to their minimum values.
■ Syntax

DATE_TRUNC('unit', date_expr)

■ Example

SELECT DATE_TRUNC('month', CURRENT_DATE), -- first of current month

DATE_TRUNC('year', order_date), -- Jan 1 of that year
DATE_TRUNC('week', event_ts), -- Monday of that week

-- Monthly revenue
SELECT DATE_TRUNC('month', order_date) AS month,
SUM(amount) AS revenue
FROM orders GROUP BY 1 ORDER BY 1;

Date: EXTRACT / DATE_PART

■ What it does
EXTRACT and DATE_PART retrieve a specific component (year, month, day, hour, etc.) from a date or
timestamp. They are functionally equivalent.
■ Syntax

EXTRACT(unit FROM date_expr)

DATE_PART('unit', date_expr)

■ Example

SELECT EXTRACT(year FROM CURRENT_DATE) AS yr,

EXTRACT(month FROM CURRENT_DATE) AS mo,
DATE_PART('dow', CURRENT_DATE) AS day_of_week,
DATE_PART('hour', CURRENT_TIMESTAMP) AS hr
;

Snowflake SQL — Complete Notes Page 22

 Date: TO_DATE / TO_TIMESTAMP
■ What it does
Converts strings or numbers to DATE or TIMESTAMP types. A format model can be provided when the string
is in a non-standard format.
■ Syntax

TO_DATE(str [, format])
TO_TIMESTAMP(str [, format])
TO_TIMESTAMP_NTZ(str) -- no timezone
TO_TIMESTAMP_TZ(str) -- with timezone

■ Example

SELECT TO_DATE('2024-07-15'),
TO_DATE('15/07/2024', 'DD/MM/YYYY'),
TO_TIMESTAMP('2024-07-15 08:30:00'),
TO_TIMESTAMP_NTZ('2024-07-15T08:30:00Z')
;

Numeric: ROUND / CEIL / FLOOR / TRUNC

■ What it does
ROUND rounds to N decimal places. CEIL rounds up to the nearest integer (or decimal). FLOOR rounds
down. TRUNC truncates (removes fractional part) without rounding.
■ Syntax

ROUND(n [, d]) -- round to d decimal places

CEIL(n) / FLOOR(n)
TRUNCATE(n [, d]) or TRUNC(n [, d])

■ Example

SELECT ROUND(3.14159, 2), -- 3.14

ROUND(3.567), -- 4
CEIL(3.1), -- 4
FLOOR(3.9), -- 3
TRUNC(3.987, 1) -- 3.9
;

Snowflake SQL — Complete Notes Page 23

 Numeric: ABS / MOD / POWER / SQRT
■ What it does
ABS returns the absolute value. MOD returns the remainder of division. POWER raises a number to a power.
SQRT returns the square root.
■ Syntax

ABS(n) / MOD(n, d) / POWER(n, exp) / SQRT(n)

■ Example

SELECT ABS(-42), -- 42
MOD(17, 5), -- 2
POWER(2, 10), -- 1024
SQRT(144) -- 12
;

SELECT order_id, ABS(balance) AS outstanding

FROM accounts WHERE balance < 0;

Numeric: DIV0 / NULLIF

■ What it does
DIV0 divides two numbers but returns 0 (instead of an error) if the divisor is zero. NULLIF returns NULL when
two values are equal, useful for preventing division-by-zero.
■ Syntax

DIV0(numerator, denominator) -- returns 0 if denominator = 0

NULLIF(expr1, expr2) -- returns NULL if expr1 = expr2

■ Example

SELECT DIV0(total_sales, num_orders) AS avg_order_value,

total_sales / NULLIF(num_orders, 0) AS avg_order_value2
FROM sales_summary;

Snowflake SQL — Complete Notes Page 24

 Aggregate: COUNT / SUM / AVG / MIN / MAX
■ What it does
Core aggregate functions. COUNT(*) counts all rows; COUNT(col) skips NULLs. SUM, AVG, MIN, MAX
operate on numeric or comparable columns. All ignore NULLs except COUNT(*).
■ Syntax

COUNT(*) / COUNT(col) / COUNT(DISTINCT col)

SUM(col) / AVG(col) / MIN(col) / MAX(col)

■ Example

SELECT department,
COUNT(*) AS headcount,
COUNT(DISTINCT manager_id) AS managers,
SUM(salary) AS total_payroll,
ROUND(AVG(salary),2) AS avg_salary,
MIN(hire_date) AS earliest_hire,
MAX(salary) AS top_salary
FROM employees
GROUP BY department;

Aggregate: LISTAGG (String Aggregation)

■ What it does
LISTAGG concatenates string values from multiple rows into a single delimited string, optionally ordered.
Similar to GROUP_CONCAT in MySQL.
■ Syntax

LISTAGG(col, delimiter) WITHIN GROUP (ORDER BY col)

■ Example

SELECT department,
LISTAGG(first_name, ', ') WITHIN GROUP (ORDER BY first_name)
AS employee_list
FROM employees
GROUP BY department;

-- Result: Engineering | Alice, Bob, Carol

Snowflake SQL — Complete Notes Page 25

 Aggregate: PERCENTILE_CONT / MEDIAN
■ What it does
PERCENTILE_CONT computes a percentile value using linear interpolation. MEDIAN is a shorthand for the
50th percentile.
■ Syntax

PERCENTILE_CONT(p) WITHIN GROUP (ORDER BY col)

MEDIAN(col)

■ Example

SELECT department,
MEDIAN(salary) AS median_salary,
PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY salary) AS p25,
PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY salary) AS p75
FROM employees
GROUP BY department;

Window: ROW_NUMBER / RANK / DENSE_RANK

■ What it does
ROW_NUMBER assigns a unique sequential integer to each row. RANK assigns the same rank to ties but
skips numbers. DENSE_RANK assigns the same rank to ties without skipping.
■ Syntax

ROW_NUMBER() OVER (PARTITION BY col ORDER BY col)

RANK() OVER (PARTITION BY col ORDER BY col)
DENSE_RANK() OVER (PARTITION BY col ORDER BY col)

■ Example

SELECT employee_id, department, salary,

ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_
num,
RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rnk,
DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dens
e_rnk
FROM employees;

-- Get top earner per department

SELECT * FROM (
SELECT *, ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) rn
FROM employees
) WHERE rn = 1;

Snowflake SQL — Complete Notes Page 26

 Window: LAG / LEAD
■ What it does
LAG accesses a value from a previous row. LEAD accesses a value from a following row. Both take an
optional offset (default 1) and a default value for when no row exists.
■ Syntax

LAG(col [, offset [, default]]) OVER (PARTITION BY ... ORDER BY ...)

LEAD(col [, offset [, default]]) OVER (PARTITION BY ... ORDER BY ...)

■ Example

SELECT order_date, revenue,

LAG(revenue, 1, 0) OVER (ORDER BY order_date) AS prev_revenue,
LEAD(revenue, 1, 0) OVER (ORDER BY order_date) AS next_revenue,
revenue - LAG(revenue) OVER (ORDER BY order_date) AS delta
FROM daily_sales;

Window: SUM / AVG with Frame Clause

■ What it does
Running totals and moving averages use window aggregate functions with a ROWS or RANGE frame clause,
specifying how many preceding/following rows to include.
■ Syntax

SUM(col) OVER (ORDER BY col ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
AVG(col) OVER (ORDER BY col ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)

■ Example

SELECT order_date, daily_sales,

SUM(daily_sales) OVER (ORDER BY order_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative,
AVG(daily_sales) OVER (ORDER BY order_date
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS rolling_7d_avg
FROM daily_sales;

Snowflake SQL — Complete Notes Page 27

 Window: FIRST_VALUE / LAST_VALUE / NTH_VALUE
■ What it does
FIRST_VALUE and LAST_VALUE return the first or last value in a window frame. NTH_VALUE returns the
value at a specific position. Useful for per-group comparisons.
■ Syntax

FIRST_VALUE(col) OVER (PARTITION BY x ORDER BY y)

LAST_VALUE(col) OVER (PARTITION BY x ORDER BY y
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
NTH_VALUE(col, n) OVER (...)

■ Example

SELECT employee_id, department, salary,

FIRST_VALUE(salary) OVER (PARTITION BY department ORDER BY salary DESC)
AS dept_max_salary,
salary / FIRST_VALUE(salary) OVER (PARTITION BY department
ORDER BY salary DESC) AS pct_of_max
FROM employees;

Window: NTILE
■ What it does
NTILE divides the rows in an ordered partition into N approximately equal groups (buckets) and assigns each
row a bucket number from 1 to N.
■ Syntax

NTILE(n) OVER (PARTITION BY col ORDER BY col)

■ Example

-- Segment customers into 4 quartiles by spend

SELECT customer_id, total_spend,
NTILE(4) OVER (ORDER BY total_spend) AS spend_quartile
FROM customer_summary;

-- Q1 = lowest 25%, Q4 = top 25%

Snowflake SQL — Complete Notes Page 28

 Conditional: COALESCE / NVL / IFF / NULLIF
■ What it does
COALESCE returns the first non-NULL value from a list. NVL is a 2-arg shorthand. IFF is a single-line
IF-ELSE. NULLIF returns NULL when two values match.
■ Syntax

COALESCE(val1, val2, ..., fallback)

NVL(val, fallback)
IFF(condition, true_val, false_val)
NULLIF(expr1, expr2)

■ Example

SELECT customer_id,
COALESCE(phone_mobile, phone_home, phone_work, 'N/A') AS contact,
NVL(discount, 0) AS discount_clean,
IFF(amount > 1000, 'High', 'Low') AS value_tier,
NULLIF(comments, '') AS comments_clean
FROM orders;

Type Conversion: CAST / TO_CHAR / TRY_CAST

■ What it does
CAST converts a value to another data type. TO_CHAR formats numbers or dates as strings. TRY_CAST is
the safe version — returns NULL instead of an error on failure.
■ Syntax

CAST(expr AS datatype) or expr::datatype

TO_CHAR(num_or_date [, format])
TRY_CAST(expr AS datatype) -- NULL on failure

■ Example

SELECT CAST('42' AS INTEGER),

'3.14'::FLOAT,
TO_CHAR(CURRENT_DATE, 'YYYY-MM-DD'),
TO_CHAR(99999.99, '$999,999.99'),
TRY_CAST('not_a_number' AS INTEGER) -- returns NULL
;

Snowflake SQL — Complete Notes Page 29

 Section 5 — Semi-Structured Data

VARIANT Data Type

■ What it does
VARIANT is Snowflake's universal semi-structured type that can hold JSON, Avro, ORC, Parquet, or XML.
Any structured value can be stored. Use :: to cast extracted values to a SQL type.
■ Syntax

col:field -- access field from VARIANT

col:field::datatype -- cast to SQL type
col['field'] -- bracket notation

■ Example

CREATE TABLE events (

event_id NUMBER,
raw_data VARIANT
);

-- Query fields from VARIANT

SELECT raw_data:user_id::INT AS user_id,
raw_data:event_name::STRING AS event_name,
raw_data:properties:page::STRING AS page
FROM events;

PARSE_JSON — Load JSON Strings

■ What it does
PARSE_JSON converts a JSON string into a Snowflake VARIANT. Useful when JSON is stored as a
VARCHAR column or provided as a literal string.
■ Syntax

PARSE_JSON(json_string) -- returns VARIANT

■ Example

SELECT PARSE_JSON('{"name": "Alice", "age": 30}') AS parsed,

PARSE_JSON('{"name": "Alice"}'):name::STRING AS name
;

-- Column stored as string, query as JSON

SELECT PARSE_JSON(json_col):status::STRING AS status
FROM raw_events;

Snowflake SQL — Complete Notes Page 30

 TO_JSON — VARIANT to JSON String
■ What it does
TO_JSON converts a VARIANT value back to a JSON-formatted string. Useful for exporting or comparing
structured data.
■ Syntax

TO_JSON(variant_expr) -- returns VARCHAR

■ Example

SELECT TO_JSON(PARSE_JSON('{"a":1,"b":2}')),
-- '{"a":1,"b":2}'

TO_JSON(OBJECT_CONSTRUCT('name', first_name, 'dept', dept))

AS user_json
FROM employees;

OBJECT_CONSTRUCT
■ What it does
OBJECT_CONSTRUCT builds a Snowflake OBJECT (JSON object) from key-value pairs. Keys must be string
literals; values can be any expression. NULL values are omitted by default.
■ Syntax

OBJECT_CONSTRUCT('key1', val1, 'key2', val2, ...)

OBJECT_CONSTRUCT_KEEP_NULL(...) -- keeps NULL values

■ Example

SELECT OBJECT_CONSTRUCT(
'employee_id', employee_id,
'name', first_name || ' ' || last_name,
'department', department,
'salary', salary
) AS employee_json
FROM employees
WHERE department = 'Engineering';

Snowflake SQL — Complete Notes Page 31

 ARRAY_CONSTRUCT & ARRAY Functions
■ What it does
ARRAY_CONSTRUCT builds an ARRAY literal. Other functions: ARRAY_SIZE (length), ARRAY_CONTAINS
(membership test), ARRAY_APPEND / ARRAY_PREPEND (add elements), ARRAY_SLICE (subset).
■ Syntax

ARRAY_CONSTRUCT(v1, v2, ...) -- [v1, v2, ...]

ARRAY_SIZE(arr) -- number of elements
ARRAY_CONTAINS(val, arr) -- TRUE/FALSE
ARRAY_APPEND(arr, val) -- add to end
ARRAY_SLICE(arr, from, to) -- subset

■ Example

SELECT ARRAY_CONSTRUCT(1, 2, 3, 4), -- [1,2,3,4]

ARRAY_SIZE(ARRAY_CONSTRUCT('a','b','c')), -- 3
ARRAY_CONTAINS('b', ARRAY_CONSTRUCT('a','b')), -- TRUE
ARRAY_APPEND(ARRAY_CONSTRUCT(1,2), 3), -- [1,2,3]
ARRAY_SLICE(ARRAY_CONSTRUCT(10,20,30,40), 1, 3) -- [20,30]
;

FLATTEN — Explode Arrays to Rows

■ What it does
FLATTEN is a table function that converts an array or object into rows. Each array element becomes a
separate row. Use the VALUE column to access the element.
■ Syntax

FROM table, LATERAL FLATTEN(input => array_col) f

-- or
FROM table t, TABLE(FLATTEN(t.array_col)) f

-- Key columns: [Link], [Link], [Link], [Link]

■ Example

-- Explode tags array into rows

SELECT p.product_id,
[Link]::STRING AS tag
FROM products p,
LATERAL FLATTEN(input => [Link]) f;

-- Explode nested JSON array

SELECT e.event_id,
[Link]:product_id::INT AS product_id,
[Link]:qty::INT AS quantity
FROM events e,
LATERAL FLATTEN(input => e.raw_data:items) item;

Snowflake SQL — Complete Notes Page 32

 GET / GET_IGNORE_CASE / GET_PATH
■ What it does
GET extracts a field from a VARIANT/OBJECT/ARRAY by key or index. GET_IGNORE_CASE does a
case-insensitive key lookup. GET_PATH extracts deeply nested fields.
■ Syntax

GET(variant, 'key') -- same as variant:key

GET(array, index) -- same as array[n]
GET_IGNORE_CASE(variant, 'KEY')
GET_PATH(variant, 'a.b.c') -- nested path

■ Example

SELECT GET(raw_data, 'status')::STRING AS status,

GET(raw_data, 0) AS first_element,
GET_IGNORE_CASE(raw_data, 'UserID')::INT AS user_id,
GET_PATH(raw_data, '[Link]')::STRING AS city
FROM events;

ARRAY_AGG — Aggregate Rows into Array

■ What it does
ARRAY_AGG collects values from multiple rows into an ARRAY. Useful for grouping child records under a
parent, or creating JSON-like nested structures.
■ Syntax

ARRAY_AGG(col) WITHIN GROUP (ORDER BY col)

■ Example

SELECT customer_id,
ARRAY_AGG(product_id) WITHIN GROUP (ORDER BY order_date)
AS purchased_products
FROM order_items
GROUP BY customer_id;

-- Result per customer: [101, 203, 88, 310, ...]

Snowflake SQL — Complete Notes Page 33

 CHECK_JSON / CHECK_XML
■ What it does
CHECK_JSON validates whether a string is valid JSON — returns NULL if valid, or an error message string if
invalid. CHECK_XML does the same for XML strings.
■ Syntax

CHECK_JSON(str) -- NULL if valid JSON, else error string

CHECK_XML(str) -- NULL if valid XML, else error string

■ Example

SELECT raw_payload,
CHECK_JSON(raw_payload) AS json_error
FROM raw_events
WHERE CHECK_JSON(raw_payload) IS NOT NULL;

-- Only process valid JSON rows

INSERT INTO clean_events
SELECT PARSE_JSON(raw_payload) FROM raw_events
WHERE CHECK_JSON(raw_payload) IS NULL;

OBJECT_KEYS & TYPEOF

■ What it does
OBJECT_KEYS returns an ARRAY of all keys in a VARIANT object. TYPEOF returns the data type of a
VARIANT value as a string (e.g., 'OBJECT', 'ARRAY', 'INTEGER').
■ Syntax

OBJECT_KEYS(variant_object) -- returns ARRAY of key names

TYPEOF(variant_expr) -- returns type string

■ Example

SELECT raw_data,
OBJECT_KEYS(raw_data) AS keys,
TYPEOF(raw_data) AS outer_type,
TYPEOF(raw_data:items) AS items_type,
TYPEOF(raw_data:count) AS count_type
FROM events
LIMIT 5;

Snowflake SQL — Complete Notes Page 34

 Section 6 — Scripting & Automation

Snowflake Scripting Block (BEGIN...END)

■ What it does
Snowflake Scripting allows procedural SQL code in anonymous blocks or stored procedures. An anonymous
block starts with BEGIN and ends with END. Variables, control flow, and exception handling are all supported.
■ Syntax

DECLARE
var_name datatype [DEFAULT value];
BEGIN
-- statements
END;

■ Example

DECLARE
row_cnt INTEGER DEFAULT 0;
BEGIN
SELECT COUNT(*) INTO :row_cnt FROM orders;
RETURN 'Row count: ' || row_cnt;
END;

Snowflake SQL — Complete Notes Page 35

 Variables: LET & SET
■ What it does
Inside scripting blocks, LET declares and assigns variables. In SQL sessions (outside blocks), SET assigns
session variables and GETVARIABLE or $var retrieves them.
■ Syntax

-- Session variables
SET var_name = value;
SELECT $var_name;

-- Scripting block variables

LET var_name := expression;
LET var_name datatype := expression;

■ Example

-- Session variables
SET my_date = '2024-01-01';
SELECT * FROM orders WHERE order_date >= $my_date;

-- Scripting block
DECLARE
greeting VARCHAR;
BEGIN
LET name VARCHAR := 'Snowflake';
greeting := 'Hello, ' || name || '!';
RETURN greeting;
END;

Snowflake SQL — Complete Notes Page 36

 IF / ELSEIF / ELSE
■ What it does
Conditional branching in Snowflake Scripting. IF evaluates a boolean condition and executes a block. Multiple
conditions use ELSEIF. The optional ELSE handles all other cases.
■ Syntax

IF (condition) THEN
-- statements
ELSEIF (condition2) THEN
-- statements
ELSE
-- statements
END IF;

■ Example

DECLARE
score INT DEFAULT 85;
grade VARCHAR;
BEGIN
IF (score >= 90) THEN
grade := 'A';
ELSEIF (score >= 80) THEN
grade := 'B';
ELSEIF (score >= 70) THEN
grade := 'C';
ELSE
grade := 'F';
END IF;
RETURN grade; -- Returns 'B'
END;

Snowflake SQL — Complete Notes Page 37

 LOOP, WHILE, FOR Loops
■ What it does
Snowflake Scripting supports LOOP (infinite until BREAK), WHILE (condition-based), and FOR (iterate over a
resultset or range). Use these for iterative data processing.
■ Syntax

-- WHILE
WHILE (condition) DO ... END WHILE;

-- FOR over query result

FOR rec IN (SELECT ...) DO ... END FOR;

-- LOOP with BREAK

LOOP ... IF (cond) THEN BREAK; END IF; END LOOP;

■ Example

DECLARE
total INT DEFAULT 0;
BEGIN
FOR rec IN (SELECT amount FROM orders WHERE status = 'pending')
DO
total := total + [Link];
END FOR;
RETURN total;
END;

-- WHILE loop
DECLARE
i INT DEFAULT 1;
BEGIN
WHILE (i <= 5) DO
INSERT INTO log_table VALUES (i, CURRENT_TIMESTAMP);
i := i + 1;
END WHILE;
END;

Snowflake SQL — Complete Notes Page 38

 CASE Statement (Scripting)
■ What it does
The CASE statement in scripting (not to be confused with the CASE expression in SQL) evaluates a variable
against multiple values and executes the matching branch.
■ Syntax

CASE variable
WHEN val1 THEN -- statements;
WHEN val2 THEN -- statements;
ELSE -- statements;
END CASE;

■ Example

DECLARE
status VARCHAR DEFAULT 'active';
msg VARCHAR;
BEGIN
CASE status
WHEN 'active' THEN msg := 'User is active';
WHEN 'inactive' THEN msg := 'User is inactive';
WHEN 'banned' THEN msg := 'User is banned';
ELSE msg := 'Unknown status';
END CASE;
RETURN msg;
END;

Snowflake SQL — Complete Notes Page 39

 EXCEPTION Handling
■ What it does
Snowflake Scripting supports structured exception handling. The EXCEPTION block catches errors by name
or catches all errors with WHEN OTHER. Use RAISE to re-throw or throw custom exceptions.
■ Syntax

BEGIN
-- risky statements
EXCEPTION
WHEN exception_name THEN -- handle;
WHEN OTHER THEN
RAISE; -- re-throw
END;

■ Example

BEGIN
INSERT INTO audit_log VALUES (1, 'start', CURRENT_TIMESTAMP);
-- This might fail
INSERT INTO orders VALUES (NULL, NULL, NULL);
EXCEPTION
WHEN OTHER THEN
INSERT INTO error_log
VALUES (SQLCODE, SQLERRM, CURRENT_TIMESTAMP);
RAISE;
END;

Snowflake SQL — Complete Notes Page 40

 Stored Procedures — CREATE PROCEDURE
■ What it does
Stored procedures encapsulate reusable logic. In Snowflake, procedures can be written in JavaScript,
Snowpark (Python/Java/Scala), or SQL Scripting. They can return a value and accept parameters.
■ Syntax

CREATE OR REPLACE PROCEDURE proc_name(param1 TYPE, ...)

RETURNS return_type
LANGUAGE SQL
AS
BEGIN
-- body
RETURN value;
END;

CALL proc_name(arg1);

■ Example

CREATE OR REPLACE PROCEDURE archive_old_orders(cutoff_date DATE)

RETURNS VARCHAR
LANGUAGE SQL
AS
BEGIN
INSERT INTO orders_archive
SELECT * FROM orders WHERE order_date < cutoff_date;

DELETE FROM orders WHERE order_date < cutoff_date;

RETURN 'Archived orders before ' || cutoff_date;

END;

CALL archive_old_orders('2022-01-01');

Snowflake SQL — Complete Notes Page 41

 Stored Procedures — JavaScript Runtime
■ What it does
Snowflake stored procedures can be written in JavaScript using the Snowflake JS API. Use
[Link]() to run SQL, and getColumnValue() to read results.
■ Syntax

CREATE OR REPLACE PROCEDURE proc()

RETURNS VARIANT
LANGUAGE JAVASCRIPT
AS
$$
var result = [Link]({sqlText: 'SELECT ...'});
[Link]();
return [Link](1);
$$;

■ Example

CREATE OR REPLACE PROCEDURE get_row_count(table_name VARCHAR)

RETURNS FLOAT
LANGUAGE JAVASCRIPT
AS
$$
var sql = 'SELECT COUNT(*) FROM ' + TABLE_NAME;
var stmt = [Link]({sqlText: sql});
var result = [Link]();
[Link]();
return [Link](1);
$$;

CALL get_row_count('orders');

Snowflake SQL — Complete Notes Page 42

 Tasks — Scheduled SQL Execution
■ What it does
Tasks automate SQL or stored procedure execution on a schedule (using CRON syntax or interval). Tasks
can be chained to build pipelines. They require a warehouse or can use serverless compute.
■ Syntax

CREATE TASK task_name

WAREHOUSE = warehouse_name
SCHEDULE = 'USING CRON 0 * * * * UTC'
AS
-- SQL statement or CALL proc;

ALTER TASK task_name RESUME;

■ Example

CREATE OR REPLACE TASK daily_refresh

WAREHOUSE = compute_wh
SCHEDULE = 'USING CRON 0 2 * * * UTC' -- 2am UTC daily
AS
CALL refresh_sales_summary();

ALTER TASK daily_refresh RESUME;

-- Check task history

SELECT * FROM TABLE(INFORMATION_SCHEMA.TASK_HISTORY())
WHERE NAME = 'DAILY_REFRESH'
ORDER BY SCHEDULED_TIME DESC LIMIT 10;

Snowflake SQL — Complete Notes Page 43

 Streams — Change Data Capture
■ What it does
A stream tracks DML changes (INSERT, UPDATE, DELETE) on a table. Querying the stream returns new
changes with metadata columns: METADATA$ACTION, METADATA$ISUPDATE, METADATA$ROW_ID.
■ Syntax

CREATE STREAM stream_name ON TABLE table_name;

-- Consume stream (DML that consumes the offset)

INSERT INTO target SELECT * FROM stream_name WHERE METADATA$ACTION='INSERT';

■ Example

CREATE STREAM orders_stream ON TABLE orders;

-- See pending changes

SELECT * FROM orders_stream;

-- Process new inserts into a target

INSERT INTO orders_processed
SELECT order_id, customer_id, amount, CURRENT_TIMESTAMP AS processed_at
FROM orders_stream
WHERE METADATA$ACTION = 'INSERT';

-- Check if stream has unconsumed data

SELECT SYSTEM$STREAM_HAS_DATA('orders_stream');

Snowflake SQL — Complete Notes Page 44

 Dynamic Tables
■ What it does
Dynamic tables automatically refresh their contents based on a target lag time (how stale the data can be).
They declaratively define a transformation and Snowflake handles incremental refresh.
■ Syntax

CREATE DYNAMIC TABLE dyn_table_name

TARGET_LAG = 'n minutes'
WAREHOUSE = wh_name
AS
SELECT ... FROM source_table;

■ Example

CREATE OR REPLACE DYNAMIC TABLE daily_sales_summary

TARGET_LAG = '1 hour'
WAREHOUSE = compute_wh
AS
SELECT DATE_TRUNC('day', order_date) AS sale_date,
product_id,
SUM(amount) AS total_revenue,
COUNT(*) AS order_count
FROM orders
GROUP BY 1, 2;

Snowflake SQL — Complete Notes Page 45