> ## Documentation Index
> Fetch the complete documentation index at: https://docs.validio.io/llms.txt
> Use this file to discover all available pages before exploring further.

# Snowflake

> Connect Validio to Snowflake to monitor and validate your data warehouse tables, views, and streams.

Snowflake is a cloud-based data warehouse that supports structured and semi-structured data. Integrate Validio with your Snowflake account to monitor, analyze, and validate your data assets.

## Prepare credentials and permissions in Snowflake

Certain [credentials](https://docs.validio.io/v11.1/docs/credentials.md) and permissions are required for Validio to validate your data as well as generate [catalog](https://docs.validio.io/v11.1/docs/catalog.md) and [lineage](https://docs.validio.io/v11.1/docs/lineage.md) relationships. For detailed information about permissions in Snowflake, refer to [Overview of Access Control](https://docs.snowflake.com/en/user-guide/security-access-control-overview) in Snowflake documentation.

> [!WARNING]
>
> **Credential Permission Requirements**
>
> Validio Credentials **require** `VIEWER` access rights when connecting to sources to read and access data. Admins must ensure that they do not provide `EDITOR` access rights to their credentials.

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.

```sql title="SQL" theme={null}
/*--------------------------------------- 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.
<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 DYNAMIC TABLES IN DATABASE ' UNION ALL
    SELECT 5 AS o, 'GRANT SELECT ON ALL SEMANTIC TABLES IN DATABASE ' UNION ALL
    SELECT 6 AS o, 'GRANT SELECT ON ALL ICEBERG TABLES IN DATABASE ' UNION ALL
    SELECT 7 AS o, 'GRANT SELECT ON ALL EVENT TABLES IN DATABASE ' UNION ALL
    SELECT 8 AS o, 'GRANT SELECT ON ALL HYBRID TABLES IN DATABASE ' UNION ALL
    SELECT 9 AS o, 'GRANT SELECT ON ALL VIEWS IN DATABASE ' UNION ALL
    SELECT 10 AS o, 'GRANT SELECT ON ALL EXTERNAL TABLES IN DATABASE ' UNION ALL
    SELECT 11 AS o, 'GRANT SELECT ON ALL STREAMS IN DATABASE ' UNION ALL
    SELECT 12 AS o, 'GRANT SELECT ON ALL MATERIALIZED VIEWS IN DATABASE ' UNION ALL
    SELECT 13 AS o, 'GRANT SELECT ON ALL SEMANTIC VIEWS IN DATABASE ' UNION ALL
    SELECT 14 AS o, 'GRANT USAGE ON FUTURE SCHEMAS IN DATABASE ' UNION ALL
    SELECT 15 AS o, 'GRANT SELECT ON FUTURE TABLES IN DATABASE ' UNION ALL
    SELECT 16 AS o, 'GRANT SELECT ON FUTURE DYNAMIC TABLES IN DATABASE ' UNION ALL
    SELECT 17 AS o, 'GRANT SELECT ON FUTURE SEMANTIC TABLES IN DATABASE ' UNION ALL
    SELECT 18 AS o, 'GRANT SELECT ON FUTURE ICEBERG TABLES IN DATABASE ' UNION ALL
    SELECT 19 AS o, 'GRANT SELECT ON FUTURE EVENT TABLES IN DATABASE ' UNION ALL
    SELECT 20 AS o, 'GRANT SELECT ON FUTURE HYBRID TABLES IN DATABASE ' UNION ALL
    SELECT 21 AS o, 'GRANT SELECT ON FUTURE VIEWS IN DATABASE ' UNION ALL 
    SELECT 22 AS o, 'GRANT SELECT ON FUTURE EXTERNAL TABLES IN DATABASE ' UNION ALL
    SELECT 23 AS o, 'GRANT SELECT ON FUTURE STREAMS IN DATABASE ' UNION ALL
    SELECT 24 AS o, 'GRANT SELECT ON FUTURE MATERIALIZED VIEWS IN DATABASE ' UNION ALL
    SELECT 25 AS o, 'GRANT SELECT ON FUTURE SEMANTIC 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
;
```

> [!WARNING]
>
> **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`

### Required usage permissions

The `USAGE` permissions are required for tracking users and their queries. This information makes it possible to track which users ran the most queries against catalog assets, as well as connect these users to internal users in Validio.

### Shared database permissions

Validio uses certain schemas and tables in the `SNOWFLAKE` database (for example, `ACCOUNT_USAGE` and `INFORMATION_SCHEMA`) for features such as lineage and catalog. Because this is a shared database, you need to use the `ACCOUNTADMIN` role to grant imported privileges,  which allows you to import the required permissions for sharing. For more information, refer to [Granting Privileges on an Imported Database](https://docs.snowflake.com/en/user-guide/data-share-consumers#label-granting-privileges-on-shared-database) in Snowflake documentation.

### Key-pair and basic authentication

Snowflake supports key-pair authentication, basic authentication with username and password, or programmatic access tokens. You can configure your Validio credential for Snowflake with any of these authentication types. Key-pair authentication provides a more secure alternative to basic authentication. You should follow the steps provided by Snowflake to configure key-pair authentication. For more information, see [Key-pair authentication and rotation](https://docs.snowflake.com/en/user-guide/key-pair-auth) in Snowflake documentation.

## Add a Snowflake credential

To add a credential for Snowflake,

1. Navigate to **Credentials** and click **+ New credential**.
2. Under **Namespace**, select a namespace where the resources will be created.
3. For **Credential Type**, select **Snowflake Credential**.
4. Fill in the **Configuration** parameter fields. Refer to the [Snowflake Credential Parameters](#snowflake-credential-parameters) table.
5. (Optional) Click **Test credential** to validate that Validio can successfully access the Snowflake account. If validation fails, check that you provided the correct parameter values.
6. (Optional) Check **Use for catalog and schema checks** to automatically discover assets from this credential and add them to the Catalog page.
7. Click **Create credential**.

Validio will automatically start fetching data and you will be able to view Snowflake assets and their relationships in the [Catalog](https://docs.validio.io/v11.1/docs/catalog.md) (if selected) and [Lineage](https://docs.validio.io/v11.1/docs/lineage.md) pages.

Once the credential is created, you can add a [source](https://docs.validio.io/v11.1/docs/sources.md) to monitor Snowflake data.

### Snowflake credential parameters

| Field | Description |
| :- | :- |
| Credential Type | Snowflake Credential |
| Name | Identifier for the credentials. Used when accessing Sources. |
| Account | The account name in Snowflake. Ensure that the account follows the correct format for the region. For more information, see [Snowflake documentation](https://docs.snowflake.com/en/user-guide/admin-account-identifier.html). |
| Role | Role used to access data. |
| Warehouse | (Optional) Snowflake warehouse used to process data. This field is not required if you have a default warehouse configured directly on your Snowflake service account. However, this field is required if you want to configure a Snowflake source using Custom SQL. |
| Authentication Type | Select **Key-pair**, **User and password**, or **Programmatic access token** to authenticate to your Snowflake account. |

#### Key-pair authentication

For Key-pair authentication, configure the following parameters:

| Field | Description |
| :- | :- |
| User | Username of the Snowflake user for 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 | (Optional) 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 | Description |
| :- | :- |
| User | Username of the Snowflake user for Validio. |
| Password | Password of the Snowflake user for Validio. |

#### Programmatic access token authentication

For authentication with a programmatic access token, configure the following parameters:

| Field | Description |
| :- | :- |
| User | Username of the Snowflake user for Validio. |
| Programmatic access token | Enter or paste in the access token. |

## Add a Snowflake source

To add a source for Snowflake,

1. Navigate to **Sources** and click **+ New source**.
2. Under **Source type**, select **Snowflake**.
3. Under **Config**,
   1. Select the valid **Credential** or create a new credential to authenticate your connection to the data warehouse.
   2. You have two options for specifying the dataset and tables to monitor:
      1. **Select an existing table** -- Enter the **Role**, **Warehouse**, **Database**, **Schema**, and **Table**to specify where the data comes from. Selecting more than one table will create a new source for each table. Refer to the [Source Configuration Parameters](#source-configuration-parameters) table.
      2. **Use Custom SQL** -- Write a valid SQL query to specify the tables to monitor.
   3. Set the **Polling schedule**, which is how frequently the validators on the source will check for changes.
4. Under **Schema**, click **Continue** to automatically infer the schema fields from the tables you selected. If you select many tables, this operation can take a few minutes to complete.
5. Under **Source details**,
   1. Add **Tags** to help group related sources or to use for routing notifications.
   2. Add an **Owner** who will be the contact for incident notifications.
   3. Assign a **Priority**, which indicates the importance of incidents detected on this source.
6. (Optional) Under **Validators**, check each of the recommended metadata validators that you want to add to your source.
7. Click **Continue** to create the source.
   Source names are generated automatically and  will be displayed when the source creation completes. If there are more than 5 sources, you will see the names for the first five and a count of the remaining sources.

### Source configuration parameters

| Field | Description |
| :- | :- |
| 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 |

## 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.

```sql title="SQL" theme={null}
/*--------------------------------------- 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
```