Install Data Quality Gates for Snowflake

Follow this guide to install Ataccama Data Quality Gates for Snowflake.

Once the installation is finished, DQ firewalls are transformed into Snowflake user-defined functions (UDFs) named atc_dq_<firewall-name> and immediately available for use in DQ evaluation.

For instructions about synchronizing DQ firewall updates with Snowflake, see Synchronize Firewalls in Snowflake with ONE.

Supported ONE versions

DQ Gates are supported in the following versions of Ataccama ONE:

Ataccama ONE 16.1.0 and later.

Before you begin

Required knowledge

You should be familiar with:

Python: Basic understanding of Python development and package management (pip, virtual environments).
Snowflake: Understanding Snowflake authentication options, SQL operations, and ability to validate the existence of UDFs in Snowflake.

Prepare your environment

System requirements

Local environment capable of running Python 3.9-3.12 (any platform: Windows, macOS, Linux) or a virtual machine that follows the same requirements.

Python 3.13 is not supported as Snowpark doesn’t support it yet.
Network connectivity to both your Ataccama ONE instance and Snowflake account.
Ability to install Python packages and run Jupyter notebooks.

Prepare Ataccama ONE

Account and permissions

An active Ataccama ONE account with access to the DQ firewalls you want to deploy to Snowflake.

Access to Keycloak in your ONE instance.

Authentication setup

You can authenticate to ONE using the following authentication methods:

OpenID Connect (OIDC). Recommended for production environments.

This method uses Keycloak for identity and access management with the OIDC protocol. Follow the instructions in this section to configure Keycloak authentication using OpenID Connect:
1. Create a dedicated client in your Keycloak realm.
2. Assign required roles to the client.
3. Copy Ataccama ONE credentials for authentication.
Basic Authentication. Can be used for testing and debugging.

This method does not require creating a Keycloak client. Instead, provide the same credentials (username and password) you use to access Ataccama ONE via web interface.

Create Keycloak client

To create a new Keycloak client:

Navigate to your Keycloak Admin Console.
Select the Ataccama ONE realm: ataccamaone.
Select Clients > Create client.
Configure client settings:
- Client type: OpenID Connect.
- Client ID: A unique identifier (for example, snowflake-dq-gates-client).
- Name: Client display name.
Select Next.
Configure authentication:
- Client authentication: Enable this option.
- Authorization: Leave turned off.
- Authentication flow: Enable Standard flow and Service accounts roles. Leave other fields disabled.
Select Next.
Keep the Login settings screen as it is, and select Save.

Assign required roles

To assign required roles to the Keycloak client:

Navigate to the Service accounts roles tab of your client.
Select Assign role.
Filter by realm roles and select the required role: MMM_admin.
Select Assign to confirm.

Copy Ataccama ONE credentials

Configure the following authentication credentials and provide them to the person responsible for the installation. These credentials are later used to fill in the .env file in the Configure the .env file step.

Ataccama credentials in the .env file

ATACCAMA_CLIENT_ID="myclient"
ATACCAMA_CLIENT_SECRET="secret"
ATACCAMA_KEYCLOAK_HOST="keycloak-worker.ataccama.cloud"
ATACCAMA_KEYCLOAK_REALM="myrealm"
# Alternatively, use Basic Auth
# ATACCAMA_CLIENT_USERNAME="ataccama username"
# ATACCAMA_CLIENT_PASSWORD="password"

To find the authentication credentials:

In Keycloak Admin Console, navigate to your client:
- Settings tab: Copy Client ID (ATACCAMA_CLIENT_ID).
- Credentials tab: Copy Client secret (ATACCAMA_CLIENT_SECRET).
Extract ATACCAMA_KEYCLOAK_HOST from your Keycloak Admin Console URL.

The format is: your-keycloak-host.domain.com/ (without https:// and /auth/).

For example: one-m2ne4.worker-01-cac1.prod.ataccama.link/.
For ATACCAMA_KEYCLOAK_REALM: Select the realm name from the dropdown in the upper-left corner of the Keycloak Admin Console.

Use the technical realm name, not the display name (for example, ataccamaone instead of Ataccama | ONE).

You can also find this name in Realm settings > Realm ID value.

Prepare Snowflake

Prepare your Snowflake according to Requirements overview.

The following sections also include detailed instructions for:

Creating Snowflake objects.
Finding the Snowflake connection details.
Setting up a Snowflake user account.

If Snowflake preparation is done by another person, send them these requirements and ask them to provide you the results.

Requirements overview

To deploy DQ firewalls to Snowflake, you need the following:

An active Snowflake account.
A dedicated Snowflake warehouse, database, schema, and stage. This is where UDFs are going to live.

These objects must exist in your Snowflake account. We recommend having a dedicated schema for the Ataccama UDF functions.

You can either:
- Use existing objects.
- Create these objects. See How to create Snowflake objects.
A user account with access to the objects listed here and the following Snowflake privilege: CREATE FUNCTION. This privilege allows creating UDFs within a schema. See How to set up Snowflake user account.
Snowflake credentials and connection details. These are later used to fill in the .env file in the Configure the .env file step:
- Snowflake credentials (username and key file, or username and password) for the user account from the previous step.
- Connection details listed in the .env file: Snowflake account, username, role, Snowflake warehouse, database, schema and stage. See How to find Snowflake connections details.
  Snowflake credentials and connection details in the .env file
  SNOWFLAKE_ACCOUNT="account" SNOWFLAKE_USER="username" SNOWFLAKE_PRIVATE_KEY_FILE="path/to/key/file" # Alternatively, use password authentication # SNOWFLAKE_PASSWORD="psw" SNOWFLAKE_ROLE="role" SNOWFLAKE_WAREHOUSE="WAREHOUSE" SNOWFLAKE_DATABASE="DB" SNOWFLAKE_SCHEMA="SCHEMA" SNOWFLAKE_STAGE="@STAGE"

How to create Snowflake objects

To create the required Snowflake objects:

Make sure you have the following privileges: CREATE WAREHOUSE, CREATE DATABASE, CREATE SCHEMA, CREATE STAGE.

If you don’t have these privileges, contact your Snowflake system administrator to create the objects for you or grant you the necessary permissions. For more details, refer to the Snowflake documentation.
Create the objects according to instructions in Snowflake documentation:

How to set up Snowflake user account

To set up a Snowflake user:

Create a Snowflake user according to your selected authentication method:
- Key-pair authentication (recommended): Create a user configured for key-pair authentication. For instructions, see Key-pair authentication and key-pair rotation.
- Basic authentication (to be deprecated by Snowflake): Create a user with a password.
Make sure this user has access to all the relevant objects and the CREATE FUNCTION privilege (see Snowflake documentation for details) in the installation schema.

How to find Snowflake connections details

To find Snowflake connection details:

In Snowflake, select your account in the lower-left corner, then Connect a tool to Snowflake.
In the Account Details dialog:
- Account tab: Copy Account Identifier (SNOWFLAKE_ACCOUNT), User Name (SNOWFLAKE_USER), and Role (SNOWFLAKE_ROLE) values.
- Config File tab: Select your warehouse, database and schema, then copy warehouse (SNOWFLAKE_WAREHOUSE), database (SNOWFLAKE_DATABASE), and schema (SNOWFLAKE_SCHEMA) values.
For SNOWFLAKE_STAGE: Use the name of your dedicated Snowflake stage. You can also find it under Data > Databases, after selecting your database and schema.

Security considerations

Lookups in UDFs are currently visible to everyone with access to UDF. Take this into consideration when deploying DQ firewalls that contain lookup tables with sensitive information.

We recommend:

Reviewing lookup data for sensitive information before deploying DQ firewalls containing lookups.
Applying appropriate access control (permissions) to schemas containing DQ Gates UDFs to ensure that only authorized users can view or execute them.

Install DQ Gates

Follow these steps to install DQ Gates.

Download DQ Gates package

Download ataccama-one-snowflake-<version>.zip to your local environment and extract the content.

The package contains the necessary components to set up and use the Ataccama ONE SDK for Snowflake within your Snowflake environment:

ataccama_one-<version>-py3-none-any.whl: Python SDK for Ataccama ONE.
ataccama_one_expressions-<version>-py3-none-any.whl: Python SDK for Ataccama ONE Expressions.
firewall_to_snowflake_udf.ipynb: Jupyter Notebook with installation instructions and demonstrations of how to use DQ Gates.
requirements.txt: List of dependencies required to run the Jupyter Notebook guide.
.env.snowflake.example: An example file containing the environment variables for the Jupyter Notebook. These variables store the credentials for the Snowflake and Ataccama ONE environments.
ataccama-one-snowflake.py: Snowflake integration Python module for DQ Gates.
README.md: Documentation file.

Install dependencies

Install the dependencies needed to deploy DQ firewalls to Snowflake using one of the following options:

Install all dependencies at once using the requirements.txt file.
```
pip install -r requirements.txt
```

Install directly (replace 0.0.0 with your actual .whl file version):

pip install ataccama_one-0.0.0-py3-none-any.whl snowflake-snowpark-python python-dotenv jupyter

Dependencies needed to deploy DQ Firewalls to Snowflake:

ataccama_one-<version>-py3-none-any.whl (from the downloaded package): Python SDK for Ataccama ONE.
ataccama_one_expressions-<version>-py3-none-any.whl (from the downloaded package): Python SDK for Ataccama ONE Expressions.

snowflake-snowpark-python: Python SDK for Snowflake Snowpark. Used to interact with Snowflake and perform data processing within Snowflake using Python.

Why is Snowpark required?

DQ Gates for Snowflake use Snowpark to integrate with Snowflake. Snowpark handles authentication and connection to Snowflake. It is also required for deploying UDFs to your Snowflake account.

For alternative authentication approaches, see Snowpark documentation.

python-dotenv: Python package used for loading environment variables from the .env file.
Jupyter: Used for running the provided Jupyter Notebook that guides you through the installation process. If you prefer to run the code manually, Jupyter is not required.

Upload Ataccama ONE Python libraries to Snowflake

Upload the Ataccama ONE Python wheel files to a Snowflake stage so that deployed UDFs can access and use the Ataccama ONE Python libraries inside Snowflake.

Select one of the following methods:

Via the Snowflake web interface: Upload files using the UI.
Using the PUT command in SnowSQL or your preferred driver: Upload files using SnowSQL.

Ataccama wheel files (from the downloaded package):

ataccama_one-<version>-py3-none-any.whl
ataccama_one_expressions-<version>-py3-none-any.whl.

Configure the .env file

Create an .env file in your project directory using .env.snowflake.example as a template. This file stores the environment variables needed to establish the connections between your local environment and Ataccama ONE and Snowflake in the Run installation step.

env file template

ATACCAMA_INSTANCE_URL="https://myenv.ataccama.one/"
ATACCAMA_PLATFORM_VERSION="16.1" # you can use 'saas', 'x.y'
ATACCAMA_CLIENT_ID="myclient"
ATACCAMA_CLIENT_SECRET="secret"
ATACCAMA_KEYCLOAK_HOST="keycloak-worker.ataccama.cloud"
ATACCAMA_KEYCLOAK_REALM="myrealm"
# Alternatively, use Basic Auth
# ATACCAMA_CLIENT_USERNAME="ataccama username"
# ATACCAMA_CLIENT_PASSWORD="password"

SNOWFLAKE_ACCOUNT="account"
SNOWFLAKE_USER="username"
SNOWFLAKE_PRIVATE_KEY_FILE="path/to/key/file"
# Alternatively, use password authentication
# SNOWFLAKE_PASSWORD="psw"
SNOWFLAKE_ROLE="role"
SNOWFLAKE_WAREHOUSE="WAREHOUSE"
SNOWFLAKE_DATABASE="DB"
SNOWFLAKE_SCHEMA="SCHEMA"
SNOWFLAKE_STAGE="@STAGE"

Ataccama ONE connection

Set the following connection details:
- ATACCAMA_INSTANCE_URL: Ataccama instance URL (for example,https://myenv.ataccama.one/).
- ATACCAMA_PLATFORM_VERSION: Platform version (for example, 16.1).
Select one of the following authentication methods:
- OpenID Connect (OIDC). Recommended for production environments.
  
  Configure your Keycloak client with ATACCAMA_CLIENT_ID, ATACCAMA_CLIENT_SECRET, ATACCAMA_KEYCLOAK_HOST, and ATACCAMA_KEYCLOAK_REALM.
  ATACCAMA_CLIENT_ID="myclient" ATACCAMA_CLIENT_SECRET="secret" ATACCAMA_KEYCLOAK_HOST="keycloak-worker.ataccama.cloud" ATACCAMA_KEYCLOAK_REALM="myrealm"
  Contact your Keycloak administrator if you need these values. The admin can find these variables according to instructions in Copy Ataccama ONE credentials.
- Basic Authentication. Can be used for testing and debugging.
  1. Configure ATACCAMA_CLIENT_USERNAME and ATACCAMA_CLIENT_PASSWORD with the same credentials you use to access Ataccama ONE via web interface.
    
    ATACCAMA_CLIENT_USERNAME="ataccama username" ATACCAMA_CLIENT_PASSWORD="password"
  2. Comment out the variables used for the OIDC authentication:
    
    # ATACCAMA_CLIENT_ID="myclient" # ATACCAMA_CLIENT_SECRET="secret" # ATACCAMA_KEYCLOAK_HOST="keycloak-worker.ataccama.cloud" # ATACCAMA_KEYCLOAK_REALM="myrealm"

Snowflake connection

Set the following connection details:
- SNOWFLAKE_ACCOUNT, SNOWFLAKE_USER, SNOWFLAKE_ROLE
  SNOWFLAKE_ACCOUNT="account" SNOWFLAKE_USER="username" SNOWFLAKE_ROLE="role"
- SNOWFLAKE_WAREHOUSE,SNOWFLAKE_DATABASE, SNOWFLAKE_SCHEMA, SNOWFLAKE_STAGE
  SNOWFLAKE_WAREHOUSE="WAREHOUSE" SNOWFLAKE_DATABASE="DB" SNOWFLAKE_SCHEMA="SCHEMA" SNOWFLAKE_STAGE="@STAGE"
Contact your Snowflake administrator if you need these values. The admin can find these variables according to instructions in How to find Snowflake connections details.
Select one of the following authentication methods:
- Key-pair authentication (recommended).
  
  Configure SNOWFLAKE_PRIVATE_KEY_FILE.
  
  Contact your Snowflake administrator if you need this value. The admin can find these variables according to instructions in How to find Snowflake connections details.
- Basic authentication. To be deprecated by Snowflake.
  1. Configure SNOWFLAKE_PASSWORD with your Snowflake account password.
    
    SNOWFLAKE_PASSWORD="psw"
  2. Comment out the SNOWFLAKE_PRIVATE_KEY_FILE property.
    
    # SNOWFLAKE_PRIVATE_KEY_FILE="path/to/key/file"

Run installation

Run Jupyter Notebook and follow the instructions in it to register DQ firewalls as Snowflake UDFs.

The notebook guides you through these steps:

Set up your local Python environment and connect to Ataccama ONE.
Fetch and download DQ firewalls from Ataccama ONE.
Connect to Snowflake.
Upload the firewalls to Snowflake and convert them to Snowflake UDFs.

Troubleshooting

Here are some common issues you might encounter while installing DQ Gates, along with suggested solutions.

SSL certificate verification failure

Problem

When deploying UDFs using Jupyter Notebook, SSL certificate verifications fails with the following error:

Max retries exceeded with url: /graphql (Caused by SSLError(SSLCertVerificationError(1, '[SSL: CERTIFICATE_VERIFY_FAILED] certificate verify failed: self signed certificate in certificate chain (_ssl.c:997)'))) python-BaseException

Cause

This occurs because Python does not trust self-signed certificates by default. It uses its own certificate store (via the certifi package), which might not include internal or custom Certificate Authorities.

Solution

Install the pip-system-certs library to make Python use your system’s root certificates instead of its default bundle:

pip install pip-system-certs

Was this page useful?