HomeDocumentationChangelog
HomeRequest DemoContact
HomeRequest DemoContact

Snowflake

Create a Snowflake Destination

Prepare credentials and permission in Snowflake

Certain credentials and permission need to be in place for Validio to egress 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:

  1. Copy this script to a SQL worksheet.
  2. 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.
<egress_database>     -- Database for egress.
<egress_schema>       -- Schema for egress.

*/------------------------------------------------ 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 egress database and schema
GRANT USAGE ON DATABASE <egress_database> TO ROLE <validio_role>;
GRANT USAGE ON SCHEMA <egress_database>.<egress_schema> TO ROLE <validio_role>;
GRANT CREATE STAGE ON SCHEMA <egress_database>.<egress_schema> TO ROLE <validio_role>;  -- (A STAGE is used to upload data more efficiently)
GRANT CREATE TABLE ON SCHEMA <egress_database>.<egress_schema> TO ROLE <validio_role>;

Create the Destination in Validio

Credential parameters

FieldRequiredDescription
NameIdentifier for the Credentials. Used when accessing Destinations.
AccountSnowflake account identifier.
UserUsername of Snowflake account with read access to the desired table.
PasswordPassword of the specified Snowflake user.

Configuration parameters

FieldRequiredDescriptionExample
NameIdentifier for the Destination. Used when setting up egress in Validators.
RoleRole of the user account with access to the destination where data is egressed to.
WarehouseName of the Snowflake warehouse to egress data to.
DatabaseName of the Snowflake database to egress data to.
SchemaSchema that the Snowflake table resides in.
TableName 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:

  1. Copy this script to a SQL worksheet.
  2. 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.
<egress_database>     -- Name of egress-database where you want to remove all egress tables and stages.
<egress_schema>       -- Name of egress-schema where you want to remove all egress 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 <egress_database>.information_schema.tables WHERE table_owner = '<validio_role>' AND table_schema = '<egress_schema>; -- Remove this row if you want to keep egress tables' UNION ALL   -- Drop-statements for egress-tables.
SELECT 'DROP STAGE IF EXISTS ' || stage_catalog || '.' || stage_schema || '.' || stage_name || ';' FROM <egress_database>.information_schema.stages WHERE stage_owner = '<validio_role>' AND stage_schema = '<egress_schema>; -- Remove this row if you want to keep egress stages' UNION ALL                   -- Drop-statements for egress-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