SQL for analytics | Snowflake course

Picture it this way

A worksheet is a kitchen counter. SELECT is asking for a plate of food. GROUP BY is stacking identical snacks. A window function is writing a rank on each plate without throwing the other plates away. JSON is a lunchbox inside a lunchbox, and FLATTEN opens the inner boxes into rows.

The statement shape you already know

Standard SQL is on every edition, including most SQL:1999 DDL and DML. MERGE, multi-table INSERT, and multi-statement transactions are included. Primary keys and other constraints can be declared, and Snowflake documents them as not enforced. They are notes for people and tools, not a guarantee the engine will reject a bad row.

Use a warehouse, a database, and a schema before you create objects. QUALIFY filters on a window function without wrapping the query in another SELECT.

SELECT region,
       SUM(amount) AS amount_sum,
       ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) AS rank_in_region
FROM orders
QUALIFY rank_in_region <= 3;

VARIANT, colon notation, and FLATTEN

JSON, Avro, ORC, Parquet, and XML can land in a VARIANT column. The colon reaches a field. :: casts it. LATERAL FLATTEN turns an array into rows. Missing keys become NULL. They do not fail the query.

Store the raw document while you are still learning the shape, then project the fields you actually filter on into typed columns. Typed columns cluster and prune more predictably than a deep VARIANT path.

SELECT raw:order_id::NUMBER AS order_id,
       raw:customer.name::STRING AS customer_name,
       line.value:sku::STRING AS sku,
       line.value:qty::NUMBER AS qty
FROM stage_orders,
LATERAL FLATTEN(input => raw:lines) line;

Read the query profile before you resize

After a query, open its profile in Snowsight. Look at partitions scanned against partitions total, bytes spilled, and whether the result came from cache. A warehouse cache (local disk on the compute cluster) helps a repeated scan. A result cache can skip the warehouse entirely when Snowflake still considers the underlying data unchanged.

If most partitions are pruned, a larger warehouse often wastes credits. If the profile shows a huge scan and a long operator, then a larger size is the experiment. Snowflake's own guidance: per-second billing means you can try a larger size and suspend it, then compare elapsed time, not just the size label.

From raw rows to a fast answer

Filter early, open nested JSON into rows, then let the query profile tell you what was slow.

orders tableTyped columns + a JSON items column
Your queryWHERE, FLATTEN, GROUP BY
ResultRows you can chart
Query profileWhere the time went

Step 1 of 3A WHERE clause on a typed column such as order_date lets Snowflake skip micro-partitions that cannot match, so less data is read.

SELECT * FROM orders WHERE order_date >= '2026-10-01';

orders tableTyped columns + a JSON items column
Your queryWHERE, FLATTEN, GROUP BY
ResultRows you can chart
Query profileWhere the time went

Step 2 of 3LATERAL FLATTEN turns a JSON array into one row per element, so ordinary SQL can count and group the items inside each order.

SELECT o.id, i.value:sku::STRING AS sku
FROM orders o, LATERAL FLATTEN(input => o.items) i;

Lab: a typed table and a JSON array

This builds a small orders table and a VARIANT payload. The FLATTEN query should return one row per line. Drop the objects later if you want the trial storage back.

USE WAREHOUSE LEARN_WH;
USE DATABASE LEARN_DB;
USE SCHEMA PUBLIC;

CREATE OR REPLACE TABLE orders (
  order_id NUMBER,
  region STRING,
  amount NUMBER(10, 2)
);
INSERT INTO orders VALUES
  (1, 'west', 20),
  (2, 'west', 50),
  (3, 'east', 15);

CREATE OR REPLACE TABLE stage_orders (raw VARIANT);
INSERT INTO stage_orders
SELECT PARSE_JSON('{"order_id": 10, "customer": {"name": "Asha"}, "lines": [{"sku": "pen", "qty": 2}, {"sku": "book", "qty": 1}]}');

SELECT raw:order_id::NUMBER AS order_id,
       line.value:sku::STRING AS sku
FROM stage_orders,
LATERAL FLATTEN(input => raw:lines) line;

Concept summary

SELECT, JOIN, GROUP BY, MERGE, and transactions behave like standard SQL.
Declared constraints are informational. Snowflake does not enforce them.
VARIANT plus FLATTEN queries nested JSON without a separate parser.
The query profile tells you whether to prune, cache, or resize.

Knowledge check

What does Snowflake do with a declared primary key?

What does LATERAL FLATTEN do to a JSON array?

A result-cache hit means what for the warehouse?

Official sources

Checked against Snowflake documentation in October 2026. Product behavior still depends on your edition, cloud, and region.