Snowflake SQL Tutorial: Step-by-Step Queries for Beginners

This Snowflake SQL tutorial teaches you to create a small practice dataset, filter orders, handle NULLs, summarize sales, join customer data and select the latest order per customer. Run the commands in sequence, compare the expected results and explain each business rule. You need an authorized Snowflake environment and basic familiarity with tables.

The deliverable is a reusable SQL worksheet and a checked customer-sales report, not a screenshot of an unexplained query. The examples suit freshers, Excel users and learners moving from another SQL database. Python, external cloud storage and a production dataset are unnecessary. If basic table terminology is unfamiliar, use the Snowflake prerequisites guide first.

This page concentrates on writing queries. It does not replace the broader Snowflake learning roadmap or repeat a file-ingestion project. Match your current starting level to the Snowflake course syllabus if you need structured instruction. All records below are synthetic, and expected outputs are calculated from them; no connected-account Snowflake execution is claimed.

A small shop wants paid sales by customer, a list of orders without known customers, and the latest order for each identified customer. One order is one row. The amount is the order total, not an item price; therefore multiplying it by an invented quantity would be wrong. A separate customer table contains one row per customer ID.

The reporting rule includes paid orders only. Pending and refunded rows remain in the source but do not contribute to this particular report. That is an exercise policy; a net-revenue report could use a different refund rule. State the question before changing SQL. “The total changed” is not an explanation unless you can identify which rows should change.

Conceptual workflow: synthetic tables → source checks → paid-order filter → customer join → aggregate → reconciliation. The sequence matters because a join can duplicate facts and an early filter can remove evidence. Keep the raw rows available while you inspect a summary.

Use an assigned small warehouse and a dedicated practice database/schema. Replace every YOUR_ identifier with names provided by your account owner. The role needs USAGE on the context objects and appropriate table-creation access. These are temporary tables: run the walkthrough in the same session and save the setup script before reconnecting.

USE WAREHOUSE YOUR_PRACTICE_WAREHOUSE;
USE DATABASE YOUR_PRACTICE_DATABASE;
USE SCHEMA YOUR_PRACTICE_SCHEMA;
SELECT CURRENT_ROLE(),CURRENT_WAREHOUSE(),CURRENT_DATABASE(),CURRENT_SCHEMA();
CREATE TEMP TABLE SQL_CUSTOMERS
 (customer_id NUMBER,customer_name VARCHAR,region VARCHAR);
INSERT INTO SQL_CUSTOMERS VALUES
 (1,'Asha','South'),(2,'Ravi','North'),(3,'Meena','South');
CREATE TEMP TABLE SQL_ORDERS
 (order_id NUMBER,customer_id NUMBER,order_date DATE,
  amount NUMBER(10,2),status VARCHAR);
INSERT INTO SQL_ORDERS VALUES
 (101,1,'2026-10-01',100,'paid'),
 (102,1,'2026-10-02',150,'paid'),
 (103,2,'2026-10-02',200,'paid'),
 (104,2,'2026-10-03',50,'refunded'),
 (105,3,'2026-10-03',75,'pending'),
 (106,NULL,'2026-10-04',25,'paid');

CREATE intentionally fails if these names already exist. Inspect earlier work or choose new names rather than using CREATE OR REPLACE to overwrite an object. Confirm three customers and six orders. If your interface opens a new execution session for each worksheet, use one worksheet/session or ask for an isolated persistent lab; temporary objects do not survive every reconnect.

Keep warehouse auto-suspend enabled and check trial/training account limits. Small inputs do not eliminate compute costs. Nothing here needs ACCOUNTADMIN, AI functions or a new AWS account.

SELECT order_id,customer_id,order_date,amount,status
FROM SQL_ORDERS ORDER BY order_id;
SELECT order_id,amount FROM SQL_ORDERS
WHERE status='paid' AND amount>=100
ORDER BY amount DESC,order_id;

SELECT chooses the output columns; WHERE decides which source rows qualify. ORDER BY controls presentation. The second condition excludes paid order 106 because its amount is only 25, and it excludes refunded/pending rows regardless of amount. SQL does not promise the same display order without ORDER BY, even when a tiny table appears stable.

Compare the three returned IDs with the seed before trusting a count. Use a deterministic tie-breaker such as order_id when business values can tie. LIMIT is useful for inspection, but a limited preview is not a complete validation of the input. Keep semicolons between statements and run only the intended selection in your editor.

Reference: Snowflake SELECT syntax.

SELECT o.order_id,o.customer_id,c.customer_name,c.region,o.amount
FROM SQL_ORDERS o
LEFT JOIN SQL_CUSTOMERS c ON o.customer_id=c.customer_id
WHERE o.status='paid' ORDER BY o.order_id;
SELECT customer_id,COUNT(*) AS copies FROM SQL_CUSTOMERS
GROUP BY customer_id HAVING COUNT(*)>1;

The LEFT JOIN keeps four paid orders, including order 106 with NULL customer details. An INNER JOIN would return three and remove that exception. Neither join is universally correct: choose according to whether unmatched facts must remain visible. The duplicate-customer check should return no rows for this seed. If a customer key repeats, the join can multiply that customer’s orders.

Reconcile the paid-order count and amount before and after the join. Adding DISTINCT to suppress extra rows can conceal the key problem and discard legitimate facts. Also note that filtering c.region in WHERE after a LEFT JOIN removes unmatched rows; a condition on the optional side changes which records remain.

Reference: Working with joins.

WITH paid_orders AS (
 SELECT order_id,customer_id,amount FROM SQL_ORDERS WHERE status='paid'
)
SELECT COALESCE(c.customer_name,'Unknown customer') AS customer_name,
 COUNT(*) AS paid_orders,SUM(p.amount) AS paid_sales
FROM paid_orders p LEFT JOIN SQL_CUSTOMERS c
 ON p.customer_id=c.customer_id
GROUP BY c.customer_name ORDER BY customer_name;

A CTE names a query block for the statement that follows; it does not automatically create a stored reporting table. Separating paid_orders makes the inclusion rule easy to review. This example groups by names only because the tiny seed has unique names. In a real report, group by the stable customer key as well: two people can share a name.

COALESCE makes the unknown group readable while the underlying exception query remains available. Preserve order_id in the CTE so you can investigate a specific amount. Readability is a reason to use a CTE; it is not a guarantee of faster execution or materialization. Compare correct results before discussing tuning.

Reference: Common table expressions.

SELECT customer_id,order_id,order_date,status,
 ROW_NUMBER() OVER (
  PARTITION BY customer_id ORDER BY order_date DESC,order_id DESC
 ) AS row_num
FROM SQL_ORDERS WHERE customer_id IS NOT NULL
QUALIFY row_num=1 ORDER BY customer_id;

PARTITION BY starts a separate ranking for each customer. ORDER BY chooses the most recent date, then the greatest order ID as an explicit tie-breaker. QUALIFY filters after the window function is computed. Here latest means latest of all statuses. Customer 2’s latest order is refunded, which is useful information rather than an error.

To find the latest paid order, add status = paid to the source WHERE condition. Then customer 2 returns 103 and customer 3 has no result. This is a different business question, not a cosmetic rearrangement of clauses. QUALIFY is Snowflake-specific SQL syntax and is not part of ANSI SQL; other engines may need a subquery around the ranking.

Reference: QUALIFY reference.

Reference: ROW_NUMBER reference.

Run these checks before showing the report. A correct total alone can hide duplicate and missing records that offset each other. Inspect keys, counts, exceptions and business rules together. Save actual output from your own authorized account; the values shown here are calculated expectations, not execution screenshots.

-- Only in this disposable lab, after confirming order 105 is unique.
SELECT * FROM SQL_ORDERS WHERE order_id=105;
UPDATE SQL_ORDERS SET status='paid' WHERE order_id=105 AND status='pending';
SELECT COUNT(*),SUM(amount) FROM SQL_ORDERS WHERE status='paid';
-- Expected: 5 paid rows and 550.00. Save evidence, then restore.
UPDATE SQL_ORDERS SET status='pending' WHERE order_id=105 AND status='paid';

During the test, Meena contributes 75.00; the known-customer subtotal becomes 525.00. After restoration, totals return to 475.00 and 450.00. The latest-order IDs do not change because status changed, not date or ID. This separates a reporting-policy change from a ranking change. Verify restoration rather than assuming the final UPDATE ran.

Run one block at a time and retain the error text plus query context. Unquoted identifiers are resolved differently from deliberately quoted mixed-case names. Use simple unquoted names for this starter and avoid introducing case-sensitive object names accidentally. A semicolon splits statements, while a missing comma changes the syntax within a statement.

If a query runs but the answer is wrong, compare the included IDs with the seed before changing warehouse size. Faster compute cannot repair a wrong join or status rule. Continue to the query optimization guide only after equivalent correct results are established.