How to Leverage Snowflake Cloud-Native Features

Why How to Leverage Snowflake Cloud-Native Features is important

-- Retrieve top 5 customers by spend this year
SELECT   c.id,
         c.name,
         SUM(o.total_amount) AS lifetime_value
FROM     Customers c
JOIN     Orders o ON o.customer_id = c.id
WHERE    YEAR(o.order_date) = YEAR(CURRENT_DATE())
GROUP BY c.id, c.name
ORDER BY lifetime_value DESC
LIMIT    5;

-- Create an ecommerce database
CREATE DATABASE IF NOT EXISTS ecommerce;

-- Create a warehouse sized for BI workloads
CREATE OR REPLACE WAREHOUSE bi_wh
  WAREHOUSE_SIZE = 'MEDIUM'
  AUTO_SUSPEND = 60  -- seconds
  AUTO_RESUME = TRUE
  INITIALLY_SUSPENDED = TRUE;

-- Create tables
CREATE OR REPLACE TABLE Customers (
  id            NUMBER,
  name          STRING,
  email         STRING,
  created_at    TIMESTAMP
);

CREATE OR REPLACE TABLE Orders (
  id            NUMBER,
  customer_id   NUMBER,
  order_date    DATE,
  total_amount  NUMBER(10,2)
);

CREATE OR REPLACE TABLE Products (
  id     NUMBER,
  name   STRING,
  price  NUMBER(10,2),
  stock  NUMBER
);

CREATE OR REPLACE TABLE OrderItems (
  id         NUMBER,
  order_id   NUMBER,
  product_id NUMBER,
  quantity   NUMBER
);

-- Load data from an S3 bucket
COPY INTO Customers
  FROM 's3://company-data/customers/'
  FILE_FORMAT = (TYPE = CSV SKIP_HEADER = 1);

-- Time-travel query (7-day default)
SELECT *
  FROM Orders AT (OFFSET => -60*24) -- 24 hours ago;