HomeDocumentationChangelog
HomeRequest DemoContact
Documentation
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 in Snowflake documentation.

You can use the following 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 already existing role.
<validio_user>                                       -- Name of validio-user. It's not recommended to use an already existing user.
<some_password>                                      -- Password for vaidio-user.
<validio_warehouse>                                  -- Name of validio-warehouse. It's not recommended to use an already 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
;

🚧

Important

The Snowflake configuration parameter QUOTED_IDENTIFIERS_IGNORE_CASE must be set to false (the default) to ensure interoperability with Validio. To ensure that the parameter is set correctly, you can configure the Validio user you create in Snowflake to override this setting to false using the following SQL statement:

ALTER USER <validio_user> SET QUOTED_IDENTIFIERS_IGNORE_CASE = FALSE

Create the Source in Validio

Credential Parameters

FieldRequiredDescription
Credential TypeSnowflake Credential.
NameIdentifier for the credentials. Used when accessing Sources.
AccountThe account name in Snowflake.

Note: Ensure sure that the account follows the correct format for the region. For more information, see Snowflake documentation.
RoleRole used to access data.
WarehouseSnowflake warehouse used to process data.
Authentication TypeSelect Key-pair or User and password to authenticate to your Snowflake account.
Use for CatalogCheck this option to add assets from this credential to the Catalog.

Key-pair Authentication

Key pair authentication provides a more secure alternative to the basic username and password authentication. For more information, see Key pair authentication and rotation in Snowflake documentation.

For Key-pair authentication, configure the following parameters:

FieldRequiredDescription
UserUsername of the Snowflake user to use with Validio.
Private KeyProvide the private key by pasting the contents of the PEM-file as plain-text into the field or uploading the PEM-file (using drag and drop or browse).
Private Key PassphraseEnter the passphrase if you are using an encrypted private key.

User and Password Authentication

For User and password authentication, configure the following parameters:

FieldRequiredDescription
UserUsername of the Snowflake user to use with Validio.
PasswordPassword of the Snowflake user to use with Validio.

Source 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. For more information, refer to Crontab Guru documentation.

Remove Credentials and Permissions

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

  1. Copy the following 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