BROLLY ACADEMY — Snowflake Query Optimization: SQL, Query Profile and Validation
Documentation-reviewed examples; not executed in a connected account.
Run one labeled phase at a time. Replace prerequisites/placeholders first.
Cleanup commands are optional and must be reviewed.

-- PHASE 1
USE DATABASE YOUR_LAB_DATABASE;
USE WAREHOUSE YOUR_LAB_WAREHOUSE;
CREATE SCHEMA BA_QO_LAB;
USE SCHEMA BA_QO_LAB;
SELECT CURRENT_ROLE(),CURRENT_WAREHOUSE(),CURRENT_DATABASE(),CURRENT_SCHEMA();
SHOW PARAMETERS LIKE 'USE_CACHED_RESULT' IN SESSION;
SHOW PARAMETERS LIKE 'QUERY_TAG' IN SESSION;

-- PHASE 2
CREATE TABLE DIM_CUSTOMER AS
SELECT n AS customer_id,
 CASE MOD(n,4) WHEN 0 THEN 'East' WHEN 1 THEN 'West'
 WHEN 2 THEN 'North' ELSE 'South' END AS region
FROM (SELECT ROW_NUMBER() OVER(ORDER BY SEQ4()) AS n
 FROM TABLE(GENERATOR(ROWCOUNT=>100)));

CREATE TABLE FACT_ORDER AS
SELECT n AS order_id,MOD(n,100)+1 AS customer_id,
 DATEADD('day',MOD(n,100),'2026-01-01'::TIMESTAMP_NTZ) AS order_ts,
 (MOD(n,10)+1)::NUMBER(12,2) AS amount
FROM (SELECT ROW_NUMBER() OVER(ORDER BY SEQ4()) AS n
 FROM TABLE(GENERATOR(ROWCOUNT=>100000)));

SELECT COUNT(*) AS orders,COUNT(DISTINCT order_id) AS distinct_orders
FROM FACT_ORDER;
SELECT customer_id,COUNT(*) AS copies FROM DIM_CUSTOMER
GROUP BY customer_id HAVING COUNT(*)>1;
SELECT COUNT(*) AS missing_customers FROM FACT_ORDER f
LEFT JOIN DIM_CUSTOMER c USING(customer_id) WHERE c.customer_id IS NULL;

-- PHASE 3
SELECT COUNT(*) AS january_orders,SUM(amount) AS january_amount
FROM FACT_ORDER
WHERE order_ts>='2026-01-01'::TIMESTAMP_NTZ
 AND order_ts<'2026-02-01'::TIMESTAMP_NTZ;

-- PHASE 4
ALTER SESSION SET USE_CACHED_RESULT=FALSE;
ALTER SESSION SET QUERY_TAG='BA_QO_BASELINE';
SELECT c.region,COUNT(*) AS order_count,SUM(f.amount) AS amount
FROM FACT_ORDER f JOIN DIM_CUSTOMER c USING(customer_id)
WHERE CAST(f.order_ts AS DATE) BETWEEN '2026-01-01'::DATE AND '2026-01-31'::DATE
GROUP BY c.region ORDER BY c.region;
SET baseline_qid=LAST_QUERY_ID();
-- Save the SELECT output outside the measured SELECT itself.
CREATE TEMP TABLE BASELINE_RESULT AS
SELECT * FROM TABLE(RESULT_SCAN($baseline_qid));

-- PHASE 5
SELECT * FROM TABLE(GET_QUERY_OPERATOR_STATS($baseline_qid));
SELECT query_id,query_tag,warehouse_size,total_elapsed_time,
 compilation_time,execution_time,queued_overload_time,
 bytes_scanned,partitions_scanned,partitions_total,
 bytes_spilled_to_local_storage,bytes_spilled_to_remote_storage
FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY_BY_SESSION(RESULT_LIMIT=>100))
WHERE query_id=$baseline_qid;

-- PHASE 6
ALTER SESSION SET QUERY_TAG='BA_QO_CANDIDATE';
WITH customer_month AS (
 SELECT customer_id,COUNT(*) AS order_count,SUM(amount) AS amount
 FROM FACT_ORDER
 WHERE order_ts>='2026-01-01'::TIMESTAMP_NTZ
  AND order_ts<'2026-02-01'::TIMESTAMP_NTZ
 GROUP BY customer_id
)
SELECT c.region,SUM(m.order_count) AS order_count,SUM(m.amount) AS amount
FROM customer_month m JOIN DIM_CUSTOMER c USING(customer_id)
GROUP BY c.region ORDER BY c.region;
SET candidate_qid=LAST_QUERY_ID();
CREATE TEMP TABLE CANDIDATE_RESULT AS
SELECT * FROM TABLE(RESULT_SCAN($candidate_qid));

-- PHASE 7
SELECT 'BASELINE_ONLY' AS issue,* FROM (
 SELECT * FROM BASELINE_RESULT MINUS SELECT * FROM CANDIDATE_RESULT
)
UNION ALL
SELECT 'CANDIDATE_ONLY' AS issue,* FROM (
 SELECT * FROM CANDIDATE_RESULT MINUS SELECT * FROM BASELINE_RESULT
);
SELECT 'BASELINE' AS version,SUM(order_count) AS orders,SUM(amount) AS amount
FROM BASELINE_RESULT
UNION ALL
SELECT 'CANDIDATE',SUM(order_count),SUM(amount) FROM CANDIDATE_RESULT;

-- PHASE 8
SELECT region,COUNT(*) FROM BASELINE_RESULT GROUP BY region HAVING COUNT(*)>1;
SELECT region,COUNT(*) FROM CANDIDATE_RESULT GROUP BY region HAVING COUNT(*)>1;

-- PHASE 9
INSERT INTO FACT_ORDER VALUES
 (100001,1,'2026-02-01 00:00:00'::TIMESTAMP_NTZ,9999.00);
-- Rerun both January report SELECTs: still 31,000 and 166,000.00.
-- After recording the boundary test:
DELETE FROM FACT_ORDER WHERE order_id=100001;

-- PHASE 10
SELECT customer_id,COUNT(*) AS copies FROM DIM_CUSTOMER
GROUP BY customer_id HAVING COUNT(*)>1;
SELECT COUNT(*) AS joined_rows
FROM FACT_ORDER f JOIN DIM_CUSTOMER c USING(customer_id);

-- PHASE 11
ALTER SESSION SET USE_CACHED_RESULT=TRUE;
ALTER SESSION UNSET QUERY_TAG;
-- After saving evidence, remove only the disposable lab:
DROP SCHEMA BA_QO_LAB;