Copy-- Real scenario: Which regions had over $50k revenue last 30 days? -- Table: sales(id, rep_name, region, product, amount, sale_date, status) SELECT region, COUNT(*) AS total_orders, SUM(amount) AS total_revenue, ROUND(AVG(amount), 2) AS avg_order_value, MAX(amount) AS largest_order FROM sales WHERE sale_date >= CURRENT_DATE - INTERVAL '30 days' AND status = 'COMPLETED' GROUP BY region HAVING SUM(amount) > 50000 ORDER BY total_revenue DESC;

Copy-- Scenario: Top 3 sales reps per region -- Alice=$10k, Bob=$10k (tied), Carol=$8k, Dave=$7k in East region SELECT * FROM ( SELECT rep_name, region, SUM(amount) AS total_sales, ROW_NUMBER() OVER(PARTITION BY region ORDER BY SUM(amount) DESC) AS rn, RANK() OVER(PARTITION BY region ORDER BY SUM(amount) DESC) AS rnk, DENSE_RANK() OVER(PARTITION BY region ORDER BY SUM(amount) DESC) AS drnk FROM sales GROUP BY rep_name, region ) t WHERE drnk <= 3; -- East region results: -- Alice: ROW_NUMBER=1 RANK=1 DENSE_RANK=1 ($10k) -- Bob: ROW_NUMBER=2 RANK=1 DENSE_RANK=1 ($10k — tied) -- Carol: ROW_NUMBER=3 RANK=3 DENSE_RANK=2 ($8k — RANK skips 2!) -- Dave: ROW_NUMBER=4 RANK=4 DENSE_RANK=3 ($7k)

Copy-- LEFT JOIN: All customers + their orders (NULL for non-buyers) SELECT c.customer_id, c.name, COUNT(o.order_id) AS total_orders, COALESCE(SUM(o.amount), 0) AS lifetime_value FROM customers c LEFT JOIN orders o ON c.customer_id = o.customer_id GROUP BY c.customer_id, c.name; -- Data quality check: find orphan orders (no matching customer) SELECT o.order_id, o.customer_id FROM orders o LEFT JOIN customers c ON o.customer_id = c.customer_id WHERE c.customer_id IS NULL; -- these rows have no customer record

Copy-- CTE: break complex query into readable named steps WITH monthly AS ( SELECT rep_name, region, DATE_TRUNC('month', sale_date) AS month, SUM(amount) AS revenue FROM sales GROUP BY rep_name, region, DATE_TRUNC('month', sale_date) ), enriched AS ( SELECT *, SUM(revenue) OVER(PARTITION BY rep_name ORDER BY month) AS running_total, LAG(revenue, 1, 0) OVER(PARTITION BY rep_name ORDER BY month) AS prev_month, DENSE_RANK() OVER(PARTITION BY region, month ORDER BY revenue DESC) AS rank_in_region FROM monthly ) SELECT *, revenue - prev_month AS mom_change, ROUND((revenue - prev_month) * 100.0 / NULLIF(prev_month, 0), 1) AS mom_pct_change FROM enriched ORDER BY rep_name, month;

Copy-- Step 1: Find which records are duplicated SELECT customer_id, order_date, COUNT(*) AS cnt FROM orders GROUP BY customer_id, order_date HAVING COUNT(*) > 1 ORDER BY cnt DESC; -- Step 2: Keep only the latest row per duplicate group (ROW_NUMBER method) WITH deduped AS ( SELECT *, ROW_NUMBER() OVER( PARTITION BY customer_id, order_date -- define what "duplicate" means ORDER BY created_at DESC -- keep the most recent ) AS rn FROM orders ) SELECT * FROM deduped WHERE rn = 1; -- Snowflake: create clean table directly CREATE TABLE orders_clean AS SELECT * EXCLUDE(rn) FROM ( SELECT *, ROW_NUMBER() OVER(PARTITION BY order_id ORDER BY load_ts DESC) rn FROM orders_raw ) WHERE rn = 1;

Copy-- SCD Type 2: Customer C001 moved from New York to Chicago -- Step 1: Expire the current record UPDATE dim_customer SET end_date = CURRENT_DATE - 1, is_current = FALSE WHERE customer_id = 'C001' AND is_current = TRUE; -- Step 2: Insert new record with the new city INSERT INTO dim_customer (customer_key, customer_id, name, city, state, start_date, end_date, is_current) VALUES (5001, 'C001', 'Alice Smith', 'Chicago', 'IL', CURRENT_DATE, '9999-12-31', TRUE); -- Result: old NYC record is expired. New Chicago record is active. -- Fact rows from before today still JOIN to the NYC record. -- New fact rows JOIN to the Chicago record. -- Historical accuracy is perfect.

Copy-- Create separate warehouses for ETL and reporting (they don't compete) CREATE OR REPLACE WAREHOUSE etl_wh WAREHOUSE_SIZE = 'MEDIUM' MAX_CLUSTER_COUNT = 3 -- scale out for concurrent ETL AUTO_SUSPEND = 300 -- suspend after 5 min idle — save money! AUTO_RESUME = TRUE -- start automatically when query arrives INITIALLY_SUSPENDED = TRUE; -- Scale up for heavy load, then back down ALTER WAREHOUSE etl_wh SET WAREHOUSE_SIZE = 'LARGE'; -- ... run the heavy ETL job ... ALTER WAREHOUSE etl_wh SET WAREHOUSE_SIZE = 'SMALL'; -- Check credit consumption per warehouse SELECT warehouse_name, credits_used, start_time FROM SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY WHERE start_time >= DATEADD('day', -7, CURRENT_TIMESTAMP) ORDER BY credits_used DESC;

Copy-- Step 1: Create storage integration (IAM role — no keys in code!) CREATE OR REPLACE STORAGE INTEGRATION s3_integration TYPE = EXTERNAL_STAGE STORAGE_PROVIDER = 'S3' ENABLED = TRUE STORAGE_AWS_ROLE_ARN = 'arn:aws:iam::123456:role/SnowflakeRole' STORAGE_ALLOWED_LOCATIONS = ('s3://my-company-data-bucket/'); -- Step 2: Create external stage pointing to S3 CREATE OR REPLACE STAGE s3_sales_stage URL = 's3://my-company-data-bucket/sales/' STORAGE_INTEGRATION = s3_integration FILE_FORMAT = (TYPE = 'PARQUET'); -- Step 3: COPY INTO — bulk load from S3 to Snowflake table COPY INTO raw.sales_staging FROM @s3_sales_stage PATTERN = '.*sales_2024_.*\.parquet' -- only files matching this regex ON_ERROR = 'CONTINUE' -- skip bad rows, keep loading PURGE = FALSE -- keep source files in S3 FORCE = FALSE; -- don't re-load already loaded files -- Check what was loaded and if any errors occurred SELECT * FROM TABLE(INFORMATION_SCHEMA.COPY_HISTORY( TABLE_NAME => 'SALES_STAGING', START_TIME => DATEADD('hours', -24, CURRENT_TIMESTAMP) ));

Copy-- TIME TRAVEL: query data as it was at any past point (up to 90 days) -- See table as it was 1 hour ago SELECT * FROM sales AT(OFFSET => -3600); -- See table at exact timestamp SELECT * FROM sales AT(TIMESTAMP => '2024-01-15 09:00:00'::TIMESTAMP); -- Recover accidentally deleted rows INSERT INTO sales SELECT * FROM sales AT(OFFSET => -3600) WHERE sale_id NOT IN (SELECT sale_id FROM sales); -- ZERO-COPY CLONING: instant copy sharing same storage (no data copied!) -- Clone entire production database for dev team in seconds CREATE DATABASE dev_db CLONE prod_db; -- Clone table before a risky migration CREATE TABLE sales_backup CLONE sales; -- Clone table from yesterday for comparison CREATE TABLE sales_yesterday CLONE sales AT(OFFSET => -86400);

Copy-- Create Stream to capture all changes on staging table CREATE OR REPLACE STREAM sales_changes ON TABLE raw.sales_staging; -- Create Task to process the stream every 5 minutes CREATE OR REPLACE TASK process_sales_changes WAREHOUSE = etl_wh SCHEDULE = '5 MINUTE' WHEN SYSTEM$STREAM_HAS_DATA('sales_changes') -- only run if data exists AS MERGE INTO core.fact_sales tgt USING ( SELECT sale_id, customer_id, amount, sale_date FROM sales_changes WHERE METADATA$ACTION = 'INSERT' ) src ON tgt.sale_id = src.sale_id WHEN MATCHED THEN UPDATE SET tgt.amount = src.amount, tgt.updated_at = CURRENT_TIMESTAMP WHEN NOT MATCHED THEN INSERT (sale_id, customer_id, amount, sale_date) VALUES (src.sale_id, src.customer_id, src.amount, src.sale_date); -- Start the task (tasks are created suspended by default) ALTER TASK process_sales_changes RESUME; -- Monitor task execution history SELECT name, state, scheduled_time, completed_time, error_message FROM TABLE(INFORMATION_SCHEMA.TASK_HISTORY()) ORDER BY scheduled_time DESC;

Copy// FIXED: Customer total revenue — ignores product filter in the view Customer Total = {FIXED [Customer ID] : SUM([Revenue])} // FIXED: Product revenue as % of that customer's total spend Pct of Customer Spend = SUM([Revenue]) / {FIXED [Customer ID] : SUM([Revenue])} // FIXED: First ever purchase date per customer First Purchase Date = {FIXED [Customer ID] : MIN([Order Date])} // INCLUDE: avg order count per customer, then average that in the view Avg Orders Per Customer = {INCLUDE [Customer ID] : COUNT([Order ID])} // EXCLUDE: regional total shown even when view is drilled down to city Region Total Revenue = {EXCLUDE [City] : SUM([Revenue])} // Running total (Table Calculation — not LOD) Running Revenue Total = RUNNING_SUM(SUM([Revenue])) // Year over Year growth % YoY Growth = (SUM([Revenue]) - LOOKUP(SUM([Revenue]), -1)) / ABS(LOOKUP(SUM([Revenue]), -1)) // Conditional performance flag (for color coding) Performance Status = IF SUM([Revenue]) >= [Target] THEN "On Track" ELSEIF SUM([Revenue]) >= [Target]*0.8 THEN "At Risk" ELSE "Behind" END