Introduction to Snowflake

Snowflake is a cloud-based data platform that allows storage, processing, and analytic solutions for structured and semi-structured data.

Snowflake Basics

Databases, Schemas & Tables

-- Create database
CREATE DATABASE SalesDB;

-- Create schema
CREATE SCHEMA SalesSchema;

-- Create table
CREATE TABLE SalesSchema.Customers(
    CustomerID INT,
    Name STRING,
    Region STRING,
    TotalSpent FLOAT
);

-- Switch database and schema
USE DATABASE SalesDB;
USE SCHEMA SalesSchema;

-- List tables
SHOW TABLES;
      

Basic CRUD Operations

-- Insert data
INSERT INTO Customers (CustomerID, Name, Region, TotalSpent)
VALUES (1, 'Alice', 'North', 1200.50);

-- Select data
SELECT * FROM Customers;

-- Update data
UPDATE Customers SET TotalSpent=1300.75 WHERE CustomerID=1;

-- Delete data
DELETE FROM Customers WHERE CustomerID=1;
      

Snowflake Queries

Filtering & Sorting

SELECT * FROM Customers WHERE Region='North';
SELECT * FROM Customers ORDER BY TotalSpent DESC;
      

Aggregate Functions

SELECT COUNT(*) FROM Customers;
SELECT SUM(TotalSpent) FROM Customers;
SELECT AVG(TotalSpent) FROM Customers;
      

Grouping

SELECT Region, COUNT(*) FROM Customers GROUP BY Region;
      

Joins & Relationships

Inner Join

SELECT C.Name, O.OrderID
FROM Customers C
INNER JOIN Orders O ON C.CustomerID=O.CustomerID;
      

Left Join

SELECT C.Name, O.OrderID
FROM Customers C
LEFT JOIN Orders O ON C.CustomerID=O.CustomerID;
      

Advanced Snowflake

Views

CREATE OR REPLACE VIEW TopCustomers AS
SELECT Name, TotalSpent FROM Customers WHERE TotalSpent > 1000;

SELECT * FROM TopCustomers;
      

Cloning & Time Travel

-- Clone a table
CREATE TABLE Customers_Clone CLONE Customers;

-- Query historical data (Time Travel)
SELECT * FROM Customers AT (OFFSET => -3600);
      

Tasks & Streams

-- Create stream to track changes
CREATE OR REPLACE STREAM CustomerStream ON TABLE Customers;

-- Create task for scheduled processing
CREATE OR REPLACE TASK CustomerTask
  WAREHOUSE = my_warehouse
  SCHEDULE = 'USING CRON 0 12 * * *'
AS
INSERT INTO AuditTable SELECT * FROM CustomerStream;