Snowflake

Adding and configuring a Snowflake connection within Qualytics empowers the platform to build a symbolic link with your schema to perform operations like data discovery, visualization, reporting, syncing, profiling, scanning, anomaly surveillance, and more.

This documentation provides a step-by-step guide on how to add Snowflake as both a source and enrichment datastore in Qualytics. It covers the entire process, from initial connection setup to testing and finalizing the configuration.

By following these instructions, enterprises can ensure their Snowflake environment is properly connected with Qualytics, unlocking the platform's potential to help you proactively manage your full data quality lifecycle.

Let’s get started 🚀

Snowflake Setup Guide

The Snowflake Setup Guide provides step-by-step instructions for configuring warehouses and roles, ensuring efficient data management and access control. It explains how to create a warehouse with minimal requirements and the setup of a default warehouse for a user. It also explains how to create custom read-only and read-write roles and grant the necessary privileges for data access and modification.

This guide is designed to help you optimize your Snowflake environment for performance and security, whether setting it up for the first time or refining your configuration.

Warehouse & Role Configuration

This section provides instructions for configuring Snowflake warehouses and roles. It includes creating a warehouse with minimal requirements, assigning a default warehouse for a user, creating custom read-only and read-write roles, and granting privileges to these roles for data access and modification.

Create a Warehouse

Use the following command to create a warehouse with minimal requirements:

CREATE WAREHOUSE qualytics_wh
WITH
    WAREHOUSE_SIZE = 'XSMALL'
    AUTO_SUSPEND = 60
    AUTO_RESUME = TRUE;

Set a specific warehouse as the default for a user:

ALTER USER <username> SET DEFAULT_WAREHOUSE = qualytics_wh;

Source Datastore Privileges and Permissions

Create a new role called qualytics_read_role and grant it privileges:

CREATE ROLE qualytics_read_role;
GRANT USAGE ON WAREHOUSE qualytics_wh TO ROLE qualytics_read_role;
GRANT USAGE ON DATABASE <database_name> TO ROLE qualytics_read_role;
GRANT USAGE ON SCHEMA <database_name>.<schema_name> TO ROLE qualytics_read_role;
GRANT SELECT ON TABLE <database_name>.<schema_name>.<table_name> TO ROLE qualytics_read_role;
GRANT SELECT ON ALL TABLES IN SCHEMA <database_name>.<schema_name> TO ROLE qualytics_read_role;
GRANT SELECT ON ALL VIEWS IN SCHEMA <database_name>.<schema_name> TO ROLE qualytics_read_role;
GRANT SELECT ON FUTURE TABLES IN SCHEMA <database_name>.<schema_name> TO ROLE qualytics_read_role;
GRANT SELECT ON FUTURE VIEWS IN SCHEMA <database_name>.<schema_name> TO ROLE qualytics_read_role;
GRANT ROLE qualytics_read_role TO USER <user_name>;

Enrichment Datastore Privileges and Permissions

Create a new role called qualytics_readwrite_role and grant it privileges:

CREATE ROLE qualytics_readwrite_role;
GRANT USAGE ON WAREHOUSE qualytics_wh TO ROLE qualytics_readwrite_role;
GRANT USAGE, MODIFY ON DATABASE <database_name> TO ROLE qualytics_readwrite_role;
GRANT USAGE, MODIFY ON SCHEMA <database_name>.<qualytics_schema> TO ROLE qualytics_readwrite_role;
GRANT CREATE TABLE ON SCHEMA <database_name>.<qualytics_schema> TO ROLE qualytics_readwrite_role;
GRANT SELECT ON FUTURE VIEWS IN SCHEMA <database_name>.<qualytics_schema> TO ROLE qualytics_readwrite_role;
GRANT SELECT ON FUTURE TABLES IN SCHEMA <database_name>.<qualytics_schema> TO ROLE qualytics_readwrite_role;
GRANT SELECT ON ALL TABLES IN SCHEMA <database_name>.<qualytics_schema> TO ROLE qualytics_readwrite_role;
GRANT SELECT ON ALL VIEWS IN SCHEMA <database_name>.<qualytics_schema> TO ROLE qualytics_readwrite_role;
GRANT ROLE qualytics_readwrite_role TO USER <user_name>;

Authentication Changes in Snowflake

Snowflake has announced a migration plan to phase out Basic authentication (username and password) for service accounts in favor of Key-Pair authentication. While basic authentication is still supported, organizations should begin planning their migration to ensure uninterrupted service.

User Type Classification

Snowflake differentiates between user types based on their intended purpose:

Migration Timeline

Snowflake's migration plan includes:

1. Current Phase: Basic authentication still supported for service accounts
2. Transition Phase: LEGACY_SERVICE user type available for organizations needing additional migration time
3. Future Phase: Basic authentication will be fully deprecated for service users

Recommended Actions

To prepare for this transition:

• New connections: Use Key-Pair authentication when creating new Snowflake datastores
• Existing connections: Plan migration from Basic to Key-Pair authentication
• Service accounts: Ensure proper user type classification (TYPE=SERVICE)

Additional Resources

For detailed information on the migration plan and implementation:

• Snowflake Security MFA Rollout (User Types & Deprecation)
• Snowflake Key-Pair Authentication Guide

Troubleshooting Common Errors

Detailed Troubleshooting Notes

Authentication Errors

The error Incorrect username or password or Private key provided is invalid indicates that the credentials are incorrect.

Common causes:

• Incorrect password: the password does not match the one set for the user.
• Account identifier wrong: the Snowflake host format must be <account>.<region>.snowflakecomputing.com. An incorrect account identifier will fail to connect.
• Key-Pair format: the private key must be in PKCS#8 PEM format. DER or PKCS#1 formats are not supported.
• Passphrase mismatch: if the private key is encrypted, the passphrase provided does not match.

Permission Errors

The error Insufficient privileges or Object does not exist or not authorized means the role authenticated successfully but lacks the necessary grants.

Common causes:

• Wrong role: the user's current role does not have the required privileges. Verify the role with SELECT CURRENT_ROLE().
• Missing USAGE on warehouse: the role cannot execute queries without warehouse access.
• Missing USAGE on database/schema: the role cannot browse objects in the database or schema.
• Future grants not set: new tables created after the initial grant are not automatically accessible. Use GRANT SELECT ON FUTURE TABLES IN SCHEMA to fix this.

Connection Errors

The error No active warehouse selected or Warehouse is suspended indicates a compute resource issue.

Common causes:

• Warehouse does not exist: the warehouse name in the connection form was misspelled or the warehouse was dropped.
• Warehouse suspended: the warehouse is suspended and AUTO_RESUME = FALSE. Resume it manually or enable auto-resume.
• No default warehouse: the user does not have a default warehouse assigned with ALTER USER ... SET DEFAULT_WAREHOUSE.

Identifying Qualytics in Snowflake

Qualytics tags every Snowflake JDBC connection with application=Qualytics. The tag appears in Snowflake's QUERY_HISTORY, LOGIN_HISTORY, and Snowsight monitoring under the Client Application column, making it easy to filter and analyze Qualytics activity in your account. No configuration is required on your side.

Add a Source Datastore

A source datastore is a storage location Qualytics connects to so it can profile, scan, and monitor data. Adding Snowflake as a source lets Qualytics query it through the Snowflake JDBC driver and run quality operations on the tables it discovers.

Before you start, set up the role and warehouse described in Warehouse & Role Configuration.

Field reference

The Add Datastore page shows the sections below when Snowflake is selected. When reusing an existing connection, the Connection Properties and Secrets Management sections come already filled in and read-only: Qualytics has already validated those credentials, so you fill in only the Datastores Extraction and the Datastore Properties. To change a saved connection's credentials, edit the connection through the Manage Connections page; edits there apply to every datastore that reuses the connection.

Connection Properties

These fields define the Snowflake account Qualytics connects to and the role and warehouse it uses. They belong to the connection: when reusing an existing connection, they come already filled in and read-only.

Authentication

Choose how Qualytics authenticates to Snowflake. Setting Type changes the credential fields shown below it, so pick the tab that matches your choice. These fields also belong to the connection: already filled in and read-only when reusing one.

Secrets Management

This group is optional: use it only if you want Qualytics to pull credentials from a secrets manager instead of typing them into the form. Turn on HashiCorp Vault to show the fields below. Despite the label, any secrets manager that exposes a compatible REST API works, not only HashiCorp Vault; see Secrets Management. It also belongs to the connection: read-only when reusing an existing connection.

Datastores Extraction

Pick the database and the schema or schemas Qualytics should read from. You fill these in on both flows.

Datastore Properties

Common fields for every source datastore, shown below the Datastores Extraction section. You fill these in on both flows.

Steps

There are two ways to set up the connection: reuse a connection you already saved (Existing Connection) or create a new one from scratch (New Connection). The tabs below walk through each option; pick the one you want to follow. Each field is described in the Field reference above.

Add Enrichment Datastore

An enrichment datastore is where Qualytics writes what it finds: anomalies, remediation tables, and record enrichment. Snowflake is supported for this role, so the same instance can hold both the data you monitor and the results.

Field reference

The Enrichment Datastore step shows the sections below when Snowflake is selected. When reusing an existing connection, the Connection Properties and Secrets Management sections come already filled in and read-only.

Connection Properties

These fields define the Snowflake account Qualytics connects to and the role and warehouse it uses. They are the same fields as on the source datastore flow, repeated here so this section stands on its own.

Authentication

Choose how Qualytics authenticates to Snowflake. Setting Type changes the credential fields shown below it, so pick the tab that matches your choice. These fields also belong to the connection: already filled in and read-only when reusing one. They are the same fields as on the source datastore flow, repeated here so this section stands on its own.

Secrets Management

This group is optional: use it only if you want Qualytics to pull credentials from a secrets manager instead of typing them into the form. Turn on HashiCorp Vault to show the fields below. Despite the label, any secrets manager that exposes a compatible REST API works, not only HashiCorp Vault; see Secrets Management. It also belongs to the connection: read-only when reusing an existing connection.

Enrichment Extraction

Where Qualytics writes the enrichment tables.

Enrichment Properties

Advanced Options

Collapsed by default. Expand it to change how anomalous source records are replicated.

Steps

A Snowflake enrichment datastore can be created from two places: as the second step of creating a source datastore, or on its own from the Enrichment Datastores page. Either way you choose between creating a connection from scratch (New Connection) or reusing a saved one (Existing Connection). The tabs below cover both entry points for each option; each field is described in the Field reference above.

API Payload Examples

This section provides detailed examples of API payloads to guide you through the process of creating and managing datastores using Qualytics API. Each example includes endpoint details, sample payloads, and instructions on how to replace placeholder values with actual data relevant to your setup.

Creating a Source Datastore

This section provides sample payloads for creating a Snowflake datastore. Replace the placeholder values with actual data relevant to your setup.

Endpoint: /api/datastores (post)

{
    "name": "your_datastore_name",
    "teams": ["Public"],
    "database": "snowflake_database",
    "schema": "snowflake_schema",
    "enrichment_only": false,
    "trigger_sync": true,
    "connection": {
        "name": "your_connection_name",
        "type": "snowflake",
        "host": "snowflake_host",
        "username": "snowflake_username",
        "password": "snowflake_password",
        "passphrase": "key_passphrase",
        "parameters": {
            "role": "snowflake_read_role",
            "warehouse": "qualytics_wh",
            "authentication_type": "KEYPAIR"
        }
    }
}

{
    "name": "your_datastore_name",
    "teams": ["Public"],
    "database": "snowflake_database",
    "schema": "snowflake_schema",
    "enrichment_only": false,
    "trigger_sync": true,
    "connection_id": 123
}

# Step 1: Create a Connection
qualytics connections create \
    --type snowflake \
    --name "your_connection_name" \
    --host ${SNOWFLAKE_HOST} \
    --username ${SNOWFLAKE_USER} \
    --password ${SNOWFLAKE_PASSWORD} \
    --parameters '{"role": "qualytics_role", "warehouse": "qualytics_wh"}'

# Step 2: Create a Source Datastore
qualytics datastores create \
    --name "your_datastore_name" \
    --connection-name "your_connection_name" \
    --database your_database \
    --schema your_schema

Creating an Enrichment Datastore

This section provides sample payloads for creating an enrichment datastore. Replace the placeholder values with actual data relevant to your setup.

Endpoint: /api/datastores (post)

{
    "name": "your_datastore_name",
    "teams": ["Public"],
    "database": "snowflake_database",
    "schema": "snowflake_schema",
    "enrichment_only": true,
    "connection": {
        "name": "your_connection_name",
        "type": "snowflake",
        "host": "snowflake_host",
        "username": "snowflake_username",
        "password": "snowflake_password",
        "parameters": {
            "role": "snowflake_readwrite_role",
            "warehouse": "qualytics_wh"
        }
    }
}

{
    "name": "your_datastore_name",
    "teams": ["Public"],
    "database": "snowflake_database",
    "schema": "snowflake_schema",
    "enrichment_only": true,
    "connection_id": 123
}

# Step 1: Create a Connection
qualytics connections create \
    --type snowflake \
    --name "your_connection_name" \
    --host ${SNOWFLAKE_HOST} \
    --username ${SNOWFLAKE_USER} \
    --password ${SNOWFLAKE_PASSWORD} \
    --parameters '{"role": "qualytics_role", "warehouse": "qualytics_wh"}'

# Step 2: Create an Enrichment Datastore
qualytics datastores create \
    --name "your_datastore_name" \
    --connection-name "your_connection_name" \
    --database your_database \
    --schema your_enrichment_schema \
    --enrichment-only

Link an Enrichment Datastore to a Source Datastore

Use the provided endpoint to link an enrichment datastore to a source datastore:

Endpoint Details: /api/datastores/{datastore-id}/enrichment/{enrichment-id} (patch)