Snowflake destination

-- ============================================================================
-- Renta ETL - Snowflake Initial Setup Script
-- ============================================================================
-- Description: This script sets up the initial Snowflake environment for
--              Renta ETL service including roles, users, warehouses, 
--              database, schemas, and permissions.
--
-- Prerequisites:
--   - Execute this script with ACCOUNTADMIN privileges
--   - Review and update variable values according to your requirements
-- ============================================================================


-- ============================================================================
-- SECTION 1: CONFIGURATION VARIABLES
-- ============================================================================
-- Define all configuration variables for the Renta ETL environment
-- Modify these values according to your organization's naming conventions

SET role_name       = 'RENTA_ROLE';
SET user_name       = 'RENTA_USER';
SET user_password   = 'TempP@ssw0rd123';    -- IMPORTANT: Change after first login
SET warehouse_name  = 'RENTA_WAREHOUSE';
SET database_name   = 'RENTA_DATABASE';
SET schema_name     = 'RENTA';


-- ============================================================================
-- SECTION 2: ROLE AND USER CREATION
-- ============================================================================
-- Create a dedicated role for Renta ETL operations and a service user

USE ROLE securityadmin;

-- Create Renta ETL role
CREATE ROLE IF NOT EXISTS IDENTIFIER($role_name)
  COMMENT = 'Role for Renta ETL service operations';

-- Grant role to SYSADMIN for role hierarchy
GRANT ROLE IDENTIFIER($role_name) TO ROLE SYSADMIN;

-- Create Renta ETL service user
CREATE USER IF NOT EXISTS IDENTIFIER($user_name)
  PASSWORD = $user_password
  DEFAULT_ROLE = $role_name
  DEFAULT_WAREHOUSE = $warehouse_name
  MUST_CHANGE_PASSWORD = FALSE
  COMMENT = 'Service account for Renta ETL operations';

-- Assign role to user
GRANT ROLE IDENTIFIER($role_name) TO USER IDENTIFIER($user_name);


-- ============================================================================
-- SECTION 3: VIRTUAL WAREHOUSE CREATION
-- ============================================================================
-- Create a dedicated compute warehouse for Renta ETL workloads

USE ROLE sysadmin;

CREATE WAREHOUSE IF NOT EXISTS IDENTIFIER($warehouse_name)
  WITH 
    WAREHOUSE_SIZE = 'XSMALL'           -- Adjust size based on workload requirements
    AUTO_SUSPEND = 300                  -- Auto-suspend after 5 minutes of inactivity
    AUTO_RESUME = TRUE                  -- Auto-resume on query submission
    INITIALLY_SUSPENDED = TRUE          -- Start in suspended state
    COMMENT = 'Dedicated warehouse for Renta ETL operations';

-- Grant warehouse privileges to Renta role
GRANT USAGE ON WAREHOUSE IDENTIFIER($warehouse_name) TO ROLE IDENTIFIER($role_name);
GRANT OPERATE ON WAREHOUSE IDENTIFIER($warehouse_name) TO ROLE IDENTIFIER($role_name);


-- ============================================================================
-- SECTION 4: DATABASE AND SCHEMA CREATION
-- ============================================================================
-- Create database and schema structure for Renta ETL data

-- Switch to ACCOUNTADMIN for database creation
USE ROLE accountadmin;

-- Create Renta database
CREATE DATABASE IF NOT EXISTS IDENTIFIER($database_name)
  COMMENT = 'Database for Renta ETL data and transformations';

-- Transfer ownership to SYSADMIN for operational management
GRANT OWNERSHIP ON DATABASE IDENTIFIER($database_name) TO ROLE sysadmin COPY CURRENT GRANTS;

-- Switch back to SYSADMIN
USE ROLE sysadmin;

-- Set database context
SET use_db_stmt = 'USE DATABASE ' || $database_name;
EXECUTE IMMEDIATE $use_db_stmt;

-- Create main schema
CREATE SCHEMA IF NOT EXISTS IDENTIFIER($schema_name)
  COMMENT = 'Main schema for Renta ETL tables and views';


-- ============================================================================
-- SECTION 5: PERMISSION GRANTS
-- ============================================================================
-- Grant necessary permissions to Renta ETL role

-- Database-level permissions
GRANT USAGE ON DATABASE IDENTIFIER($database_name) TO ROLE IDENTIFIER($role_name);
GRANT CREATE SCHEMA ON DATABASE IDENTIFIER($database_name) TO ROLE IDENTIFIER($role_name);

-- Schema-level permissions for object creation
GRANT USAGE ON SCHEMA IDENTIFIER($schema_name) TO ROLE IDENTIFIER($role_name);
GRANT CREATE TABLE ON SCHEMA IDENTIFIER($schema_name) TO ROLE IDENTIFIER($role_name);
GRANT CREATE VIEW ON SCHEMA IDENTIFIER($schema_name) TO ROLE IDENTIFIER($role_name);
GRANT CREATE STAGE ON SCHEMA IDENTIFIER($schema_name) TO ROLE IDENTIFIER($role_name);
GRANT CREATE FILE FORMAT ON SCHEMA IDENTIFIER($schema_name) TO ROLE IDENTIFIER($role_name);
GRANT CREATE SEQUENCE ON SCHEMA IDENTIFIER($schema_name) TO ROLE IDENTIFIER($role_name);
GRANT CREATE FUNCTION ON SCHEMA IDENTIFIER($schema_name) TO ROLE IDENTIFIER($role_name);
GRANT CREATE PROCEDURE ON SCHEMA IDENTIFIER($schema_name) TO ROLE IDENTIFIER($role_name);
GRANT CREATE PIPE ON SCHEMA IDENTIFIER($schema_name) TO ROLE IDENTIFIER($role_name);
GRANT CREATE STREAM ON SCHEMA IDENTIFIER($schema_name) TO ROLE IDENTIFIER($role_name);
GRANT CREATE TASK ON SCHEMA IDENTIFIER($schema_name) TO ROLE IDENTIFIER($role_name);

-- Permissions on all existing tables in schema
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA IDENTIFIER($schema_name) 
  TO ROLE IDENTIFIER($role_name);

-- Permissions on all existing views in schema
GRANT SELECT ON ALL VIEWS IN SCHEMA IDENTIFIER($schema_name) 
  TO ROLE IDENTIFIER($role_name);

-- Permissions on future tables (automatically apply to new tables)
GRANT SELECT, INSERT, UPDATE, DELETE ON FUTURE TABLES IN SCHEMA IDENTIFIER($schema_name) 
  TO ROLE IDENTIFIER($role_name);

-- Permissions on future views (automatically apply to new views)
GRANT SELECT ON FUTURE VIEWS IN SCHEMA IDENTIFIER($schema_name) 
  TO ROLE IDENTIFIER($role_name);


-- ============================================================================
-- SECTION 6: VERIFICATION
-- ============================================================================
-- Verify that all objects were created successfully

-- Display created objects
SHOW ROLES LIKE 'RENTA_ROLE';
SHOW USERS LIKE 'RENTA_USER';
SHOW WAREHOUSES LIKE 'RENTA_WAREHOUSE';
SHOW DATABASES LIKE 'RENTA_DATABASE';

-- Display granted permissions
SHOW GRANTS TO ROLE IDENTIFIER($role_name);

-- Verify schema exists
SHOW SCHEMAS IN DATABASE IDENTIFIER($database_name);


-- ============================================================================
-- SETUP COMPLETE
-- ============================================================================