HomeDocumentationChangelog
HomeRequest DemoContact
HomeRequest DemoContact

Snowflake

Create a Snowflake Source

Prepare credentials and permission in Snowflake

Certain credentials and permission are required for Validio to validate your data.

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 Source 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 alread existing role.
<validio_user>                                       -- Name of validio-user. It's not recommended to use an alread existing user.
<some_password>                                      -- Password for vaidio-user.
<validio_warehouse>                                  -- Name of validio-warehouse. It's not recommended to use an alread existing warehouse.
<database_to_monitor> [, <database2_to_monitor>...]  -- Optionally filter access to one or more databases

*/------------------------------------------------ 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 to separate workload and costs. Increase warehouse size if needed.
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>;

-- Use role accountadmin (or equivalent) for account steps
USE ROLE accountadmin;

-- Grant access to the SNOWFLAKE database. Validio uses this information to be able to provide comprehensive and optimized data quality monitoring functionality.
GRANT IMPORTED PRIVILEGES ON DATABASE snowflake TO ROLE <validio_role>;

-- Grant access to the data to validate. You can either write your own statements, or to simplify the process use the following query which generates grant statements for all
-- existing and future tables etc. in your account, or filtered to specific databases if you like. Run the query and then copy&paste the result (the generated statements) into 
-- a worksheet. Review the generated statements before you run them, to ensure the grants will apply as you prefer.
-- Note that future grants are set on database-level. This means they have no effect in case future grants for the same object types also exist on schema-level. In such case we recommend 
-- you to manually tailor each future grant statement as you wish. More info here: https://docs.snowflake.com/en/sql-reference/sql/grant-privilege#considerations
SELECT p || DATABASE_NAME || IFF(o = 0, ':    --------------', ' TO ROLE <validio_role>;') AS statement
FROM (
    SELECT 0 AS o, '--------------    Privileges for database ' AS p UNION ALL
    SELECT 1 AS o, 'GRANT USAGE ON DATABASE ' AS p UNION ALL
    SELECT 2 AS o, 'GRANT USAGE ON ALL SCHEMAS IN DATABASE ' AS p UNION ALL
    SELECT 3 AS o, 'GRANT SELECT ON ALL TABLES IN DATABASE ' UNION ALL
    SELECT 4 AS o, 'GRANT SELECT ON ALL VIEWS IN DATABASE ' UNION ALL
    SELECT 5 AS o, 'GRANT SELECT ON ALL EXTERNAL TABLES IN DATABASE ' UNION ALL
    SELECT 6 AS o, 'GRANT SELECT ON ALL STREAMS IN DATABASE ' UNION ALL
    SELECT 7 AS o, 'GRANT SELECT ON ALL MATERIALIZED VIEWS IN DATABASE ' UNION ALL
    SELECT 8 AS o, 'GRANT USAGE ON FUTURE SCHEMAS IN DATABASE ' UNION ALL
    SELECT 9 AS o, 'GRANT SELECT ON FUTURE TABLES IN DATABASE ' UNION ALL
    SELECT 10 AS o, 'GRANT SELECT ON FUTURE VIEWS IN DATABASE ' UNION ALL 
    SELECT 11 AS o, 'GRANT SELECT ON FUTURE EXTERNAL TABLES IN DATABASE ' UNION ALL
    SELECT 12 AS o, 'GRANT SELECT ON FUTURE STREAMS IN DATABASE ' UNION ALL
    SELECT 13 AS o, 'GRANT SELECT ON FUTURE MATERIALIZED VIEWS IN DATABASE '
),
SNOWFLAKE.INFORMATION_SCHEMA.DATABASES
--    WHERE DATABASE_NAME IN ('<database_to_monitor>' [, '<database2_to_monitor>'...]) -- Uncomment and edit this line to limit the result to specific databases
ORDER BY DATABASE_NAME, o
;

Create the Source in Validio

Credential parameters

FieldRequiredDescription
NameIdentifier for the credentials. Used when accessing Sources.
AccountEnsure sure that the account follows the correct format for the region. For more information, see Snowflake documentation account identifier.
UserUsername of the Snowflake user to use with Validio
PasswordPassword of the Snowflake user to use with Validio

Configuration parameters

FieldRequiredDescription
NameIdentifier for the Source. Used when setting up validators
RoleRole used to access data
WarehouseSnowflake warehouse used to process data
DatabaseName of a Snowflake database with data to validate
SchemaName of a Snowflake schema with data to validate
TableName of a Snowflake table, view, or equivalent with data to validate
Cron presetDetermines how often to query the table for new data based on a preset option.

Select custom to use your own cron expression.
Cron expressionDetermines how often to query the table for new data based on cron expression.

Expression of cron presets are displayed here.

Used to enter your own cron expression.

Remove credentials and permissions

You can use this SQL script to remove configured permissions for the Source 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.

*/------------------------------------------------ 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 ROLE IF EXISTS <validio_role>;'  UNION ALL      	-- Drop-statement for Validio-role
SELECT 'DROP USER IF EXISTS <validio_user>;'  UNION ALL      	-- Drop-statement for Validio-user
SELECT 'DROP WAREHOUSE IF EXISTS <validio_warehouse>;'; 			-- Drop-statement for Validio-warehouse