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:
- 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 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 tofalse
(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 tofalse
using the following SQL statement:
ALTER USER <validio_user> SET QUOTED_IDENTIFIERS_IGNORE_CASE = FALSE
Create the Source in Validio
Credential Parameters
Field | Required | Description |
---|---|---|
Credential Type | ✅ | Snowflake Credential. |
Name | ✅ | Identifier for the credentials. Used when accessing Sources. |
Account | ✅ | The account name in Snowflake. Note: Ensure sure that the account follows the correct format for the region. For more information, see Snowflake documentation. |
Role | Role used to access data. | |
Warehouse | Snowflake warehouse used to process data. | |
Authentication Type | ✅ | Select Key-pair or User and password to authenticate to your Snowflake account. |
Use for Catalog | Check 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:
Field | Required | Description |
---|---|---|
User | ✅ | Username of the Snowflake user to use with Validio. |
Private Key | ✅ | Provide 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 Passphrase | Enter the passphrase if you are using an encrypted private key. |
User and Password Authentication
For User and password authentication, configure the following parameters:
Field | Required | Description |
---|---|---|
User | ✅ | Username of the Snowflake user to use with Validio. |
Password | ✅ | Password of the Snowflake user to use with Validio. |
Source Configuration Parameters
Field | Required | Description |
---|---|---|
Name | ✅ | Identifier for the Source. Used when setting up validators |
Role | Role used to access data | |
Warehouse | Snowflake warehouse used to process data | |
Database | ✅ | Name of a Snowflake database with data to validate |
Schema | ✅ | Name of a Snowflake schema with data to validate |
Table | ✅ | Name of a Snowflake table, view, or equivalent with data to validate |
Cron preset | Determines how often to query the table for new data based on a preset option. Select custom to use your own cron expression. | |
Cron expression | ✅ | Determines 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:
- Copy the following 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.
*/------------------------------------------------ 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
Updated about 1 month ago