🚀 Snowflake Data Destination Setup Guide

-- Set variables (Replace placeholders with your actual values)
SET openbridge_role = 'YOUR_OPENBRIDGE_ROLE';          -- e.g., 'BUXZZ_ROLE'
SET openbridge_username = 'YOUR_OPENBRIDGE_USER';      -- e.g., 'BUXZZ_USER'
SET openbridge_password = 'YOUR_PASSWORD';             -- e.g., '12BUXZZ!@'
SET openbridge_warehouse = 'YOUR_OPENBRIDGE_WAREHOUSE';-- e.g., 'BUXZZ_WAREHOUSE'
SET openbridge_database = 'YOUR_OPENBRIDGE_DATABASE';  -- e.g., 'BUXZZ_DATABASE'
SET openbridge_schema = 'YOUR_OPENBRIDGE_SCHEMA';      -- e.g., 'BUXZZ_SCHEMA'
SET openbridge_stage = 'YOUR_OPENBRIDGE_STAGE';        -- e.g., 'BUXZZ_STAGE'
SET openbridge_namespace = $openbridge_database || '.' || $openbridge_schema;

-- Use the ACCOUNTADMIN role to ensure sufficient privileges
USE ROLE ACCOUNTADMIN;

-- Step 1: Setup
-- Create the Openbridge role if it doesn't exist
CREATE ROLE IF NOT EXISTS IDENTIFIER($openbridge_role);

-- Create the Openbridge user if it doesn't exist
CREATE USER IF NOT EXISTS IDENTIFIER($openbridge_username)
  PASSWORD = $openbridge_password
  DEFAULT_ROLE = $openbridge_role
  DEFAULT_WAREHOUSE = $openbridge_warehouse
  DEFAULT_NAMESPACE = $openbridge_namespace; -- Use pre-built namespace

-- Grant the role to the user
GRANT ROLE IDENTIFIER($openbridge_role) TO USER IDENTIFIER($openbridge_username);

-- Assign the current user's name to a variable
SET current_username = CURRENT_USER();

-- Grant the role to your current user (for testing purposes)
GRANT ROLE IDENTIFIER($openbridge_role) TO USER IDENTIFIER($current_username);

-- Create the warehouse if it doesn't exist
CREATE WAREHOUSE IF NOT EXISTS IDENTIFIER($openbridge_warehouse)
  WAREHOUSE_SIZE = 'XSMALL'
  WAREHOUSE_TYPE = 'STANDARD'
  AUTO_SUSPEND = 60
  AUTO_RESUME = TRUE
  INITIALLY_SUSPENDED = TRUE;

-- Grant usage on the warehouse to the role
GRANT USAGE ON WAREHOUSE IDENTIFIER($openbridge_warehouse) TO ROLE IDENTIFIER($openbridge_role);

-- Create the database if it doesn't exist
CREATE DATABASE IF NOT EXISTS IDENTIFIER($openbridge_database);

-- Grant usage on the database to the role (without CREATE SCHEMA privilege)
GRANT USAGE ON DATABASE IDENTIFIER($openbridge_database) TO ROLE IDENTIFIER($openbridge_role);

-- Use the new database
USE DATABASE IDENTIFIER($openbridge_database);

-- Create the OAuth2 integration
CREATE SECURITY INTEGRATION IF NOT EXISTS
  openbridge_oauth2
  TYPE = OAUTH
  OAUTH_CLIENT = CUSTOM
  OAUTH_CLIENT_TYPE = 'CONFIDENTIAL'
  OAUTH_REDIRECT_URI = 'https://oauth.api.openbridge.io/oauth/callback'
  ENABLED = TRUE
  OAUTH_ISSUE_REFRESH_TOKENS = TRUE
  OAUTH_REFRESH_TOKEN_VALIDITY = 7776000
  COMMENT = 'Openbridge OAuth';

-- Create the stage
CREATE STAGE IDENTIFIER($openbridge_stage)
  FILE_FORMAT = (TYPE = 'PARQUET');
GRANT ALL PRIVILEGES ON STAGE IDENTIFIER($openbridge_stage) TO ROLE IDENTIFIER($openbridge_role);

-- Create a schema
CREATE SCHEMA IF NOT EXISTS IDENTIFIER($openbridge_schema); 

-- Grant privileges on the schema to the role
GRANT USAGE ON SCHEMA IDENTIFIER($openbridge_schema) TO ROLE IDENTIFIER($openbridge_role);
GRANT CREATE TABLE, CREATE VIEW, CREATE STAGE, CREATE FILE FORMAT ON SCHEMA IDENTIFIER($openbridge_schema) TO ROLE IDENTIFIER($openbridge_role);

-- Step 2: Switch to the Openbridge role for testing
USE ROLE IDENTIFIER($openbridge_role);

-- Set the context to use the Openbridge warehouse, database, and schema
USE WAREHOUSE IDENTIFIER($openbridge_warehouse);
USE DATABASE IDENTIFIER($openbridge_database);
USE SCHEMA IDENTIFIER($openbridge_schema);

-- Step 3: Test Operations

-- Test 1: Create a table
CREATE OR REPLACE TABLE test_table (
  id INT AUTOINCREMENT PRIMARY KEY,
  name STRING,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP()
);

-- Test 2: Insert data into the table
INSERT INTO test_table (name) VALUES
  ('Alice'),
  ('Bob'),
  ('Charlie');

-- Test 3: Query data from the table
SELECT * FROM test_table;

-- Test 4: Update data in the table
UPDATE test_table
SET name = 'Alice Updated'
WHERE name = 'Alice';

-- Test 5: Delete data from the table
DELETE FROM test_table
WHERE name = 'Bob';

-- Test 6: Drop the table
DROP TABLE test_table;

-- Retrieve client ID, client secret
SELECT SYSTEM$SHOW_OAUTH_CLIENT_SECRETS('OPENBRIDGE_OAUTH2');

SELECT SYSTEM$SHOW_OAUTH_CLIENT_SECRETS('OPENBRIDGE_OAUTH2');

https://<your-account>.snowflakecomputing.com