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 a source datastore in Qualytics. It covers the entire process, from initial connection setup to testing and finalizing the configuration. A Snowflake datastore can also be the place Qualytics writes to; see Use as an Enrichment Datastore.

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 Location and the General fields. 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.

Location

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

General

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

Below the form, an information banner lists the Public addresses and Private addresses your datastore connections originate from. Allow the ones that match your network setup through your security groups or firewall rules; see How Connections Work for where the addresses come from.

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.

Use as an Enrichment Datastore

An enrichment datastore is where Qualytics writes its findings: scan results, source record examples, remediation snapshots, materialized copies, and exports. Keep it on a dedicated schema or storage path, separate from the source data you govern. When a source datastore is linked to it, that source's writes land there. The destination can share the source's connection when those credentials have the required write access.

To use a Snowflake datastore as a destination, its connection user needs the write grants listed under Additional Permissions for an Enrichment Datastore. Then link it from the datastore that will write to it: open its Settings menu, click Enrichment , and either pick the Snowflake datastore under Use existing or create it there under Add new. The same choice is offered in the second step of the Add Datastore page. The flow is the same for every connector and is documented once, in Link an Enrichment Destination and Link Enrichment on Datastore Creation.

The rest of this section covers only what is specific to Snowflake: the fields you fill in when creating the destination on Snowflake from inside that dialog. For what Qualytics writes and how the outputs are named, see Enrichment Outputs.

Field reference

The Enrichment Destination 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.

Location

Where Qualytics writes the enrichment tables.

General

Write Behavior

Whether a Scan also writes a snapshot of the anomalous records, and whether Qualytics syncs and profiles the destination after each write.

Steps

Fill in these fields under Add new, either in the Enrichment Destination dialog of the datastore that will write here or in the second step of the Add Datastore page, then click Test connection and save. The steps are the same for every connector: see Link an Enrichment Destination or Link Enrichment on Datastore Creation.

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

Create 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 Destination

Use the provided endpoint to link a datastore to its enrichment destination:

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