Snowflake

-- Create role
CREATE ROLE PLUMI_READONLY;

-- Grant warehouse usage
GRANT USAGE ON WAREHOUSE COMPUTE_WH TO ROLE PLUMI_READONLY;

-- Grant database access
GRANT USAGE ON DATABASE ANALYTICS TO ROLE PLUMI_READONLY;
GRANT USAGE ON ALL SCHEMAS IN DATABASE ANALYTICS TO ROLE PLUMI_READONLY;
GRANT SELECT ON ALL TABLES IN DATABASE ANALYTICS TO ROLE PLUMI_READONLY;

-- Grant future table access
GRANT SELECT ON FUTURE TABLES IN DATABASE ANALYTICS TO ROLE PLUMI_READONLY;

-- Create user
CREATE USER PLUMI_USER
  PASSWORD = 'your_secure_password'
  DEFAULT_ROLE = PLUMI_READONLY
  DEFAULT_WAREHOUSE = COMPUTE_WH;

-- Assign role to user
GRANT ROLE PLUMI_READONLY TO USER PLUMI_USER;

-- Date functions
SELECT
  DATE_TRUNC('month', order_date) as month,
  SUM(total) as revenue
FROM orders
WHERE order_date >= '2024-01-01'
GROUP BY month;

-- Semi-structured data (JSON)
SELECT
  data:customer:name::string as customer_name,
  data:order:total::number as total
FROM orders_json;