Prepare credentials and permission in Snowflake

Certain credentials and permission need to be in place for Validio to write data to your Destination.

For detailed information about permissions in Snowflake, refer to Overview of Access Control.

You can use this SQL script to set up a User, Role, and Warehouse, and grant the necessary permissions for the Destination in Validio:

Copy this script to a SQL worksheet.
Follow the steps described in the script.

/*--------------------------------------- Parameters used in this script -------------------------------------------------
Search/replace all instances of these parameters in the script with names of your choice.

<validio_role>          -- Name of validio-role. It's not recommended to use an already existing role.
<validio_user>          -- Name of validio-user. It's not recommended to use an already existing user.
<some_password>         -- Password for validio-user.
<validio_warehouse>     -- Name of validio-warehouse. It's not recommended to use an already existing warehouse.
<destination_database>  -- Database for writing our data.
<destination_schema>    -- Schema for writing our data.

*/------------------------------------------------ Script -----------------------------------------------------

-- Use role useradmin (or equivalent) for user / role steps
USE ROLE useradmin;

-- Create Validio role
CREATE ROLE IF NOT EXISTS <validio_role>;
GRANT ROLE <validio_role> TO ROLE sysadmin;

-- Create Validio user
CREATE USER IF NOT EXISTS <validio_user>
PASSWORD = <some_password>
DEFAULT_ROLE = <validio_role>
DEFAULT_WAREHOUSE = <validio_warehouse>;
GRANT ROLE <validio_role> TO USER <validio_user>;

-- Use role sysadmin (or equivalent) for warehouse steps
USE ROLE sysadmin;

-- Create Validio warehouse in order to separate workload and costs
CREATE WAREHOUSE IF NOT EXISTS <validio_warehouse> WAREHOUSE_SIZE='XSMALL' INITIALLY_SUSPENDED=TRUE AUTO_SUSPEND = 60 AUTO_RESUME = TRUE;
GRANT USAGE ON WAREHOUSE <validio_warehouse> TO ROLE <validio_role>;

-- Grant permissions to create tables and stages in the destination database and schema
GRANT USAGE ON DATABASE <destination_database> TO ROLE <validio_role>;
GRANT USAGE ON SCHEMA <destination_database>.<destination_schema> TO ROLE <validio_role>;
GRANT CREATE STAGE ON SCHEMA <destination_database>.<destination_schema> TO ROLE <validio_role>;  -- (A STAGE is used to upload data more efficiently)
GRANT CREATE TABLE ON SCHEMA <destination_database>.<destination_schema> TO ROLE <validio_role>;

Create the Destination in Validio

Credential parameters

Field	Required	Description
Name	✅	Identifier for the Credentials. Used when accessing Destinations.
Account	✅	Snowflake account identifier.
User	✅	Username of Snowflake account with read access to the desired table.
Password	✅	Password of the specified Snowflake user.

Configuration parameters

Field	Required	Description
Name	✅	Identifier for the Destination.
Role	✅	Role of the user account with write access to the destination.
Warehouse	✅	Name of the Snowflake warehouse.
Database	✅	Name of the Snowflake database to write data to.
Schema	✅	Schema that the Snowflake table resides in.
Table	✅	Name of the table created when initializing the Destination connector.

Remove credentials and permissions

You can use this SQL script to remove configured permissions for the Destination in Validio:

Copy this script to a SQL worksheet.
Follow the steps described in the script.

/*--------------------------------------- Parameters used in this script -------------------------------------------------
Search/replace all instances of these parameters in the script with names of your choice.

<validio_role>           -- Name of validio-role you want to remove.
<validio_user>           -- Name of validio-user you want to remove.
<validio_warehouse>      -- Name of validio-warehouse you want to remove.
<destination_database>   -- Name of destination database where you want to remove all destination tables and stages.
<destination_schema>     -- Name of destination schema where you want to remove all destination tables and stages.

*/------------------------------------------------ Script -----------------------------------------------------

-- Use role accountadmin (or equivalent) to when removing
USE ROLE accountadmin;

-- This script generates statements to remove Validio. Run the script, then copy&paste the statements into a worksheet and run them to remove all Validio-related entities (see inline-comments below for more details) from this Snowflake-account. We recommend you to carefully look through the statements before you run then to make sure you really want to remove each entity.
SELECT 'DROP TABLE IF EXISTS ' || table_catalog || '.' || table_schema || '.' || table_name || ';' AS drop_statement FROM <destination_database>.information_schema.tables WHERE table_owner = '<validio_role>' AND table_schema = '<destination_schema>; -- Remove this row if you want to keep destination tables' UNION ALL   -- Drop-statements for destination-tables.
SELECT 'DROP STAGE IF EXISTS ' || stage_catalog || '.' || stage_schema || '.' || stage_name || ';' FROM <destination_database>.information_schema.stages WHERE stage_owner = '<validio_role>' AND stage_schema = '<destination_schema>; -- Remove this row if you want to keep destination stages' UNION ALL                   -- Drop-statements for destination-stages.
SELECT 'DROP ROLE IF EXISTS <validio_role>; -- Ignore this row if you use the same role for a source connector'  UNION ALL          -- Drop-statement for Validio-role
SELECT 'DROP USER IF EXISTS <validio_user>; -- Ignore this row if you use the same user for a source connector'  UNION ALL          -- Drop-statement for Validio-user
SELECT 'DROP WAREHOUSE IF EXISTS <validio_warehouse>; -- Ignore this row if you use the same warehouse for a source connector'; 	-- Drop-statement for Validio-warehouse