Ataccama ONE Agentic ONE Desktop ONE DQ&C ONE MDM ONE RDM ONE Runtime Server
latest (17.2.0) 17.1.0 17.0.0 16.3.x 16.2.0 16.1.0 16.0.0 15.4.0 15.3.0 15.2.0 15.1.0 14.5.x 13.9.x 13.8.x 13.7.x 13.6.x 13.5.0 13.4.x 13.3.x 13.2.x
Downloads

Snowflake Legacy Pushdown

Snowflake pushdown processing has two implementations, controlled by the Legacy pushdown setting on the connection.

Legacy pushdown is the older implementation. Keep it turned off unless your DQ rules rely on functions that are supported only in Legacy pushdown (see Functions supported only in Legacy pushdown).

This page describes what differs when Legacy pushdown is enabled and how to set it up. To learn about pushdown in Snowflake in general, see Pushdown processing on the Snowflake connection page.

Legacy pushdown compared to the current implementation

Compared to Legacy pushdown, the current implementation (with Legacy pushdown turned off):

Before switching between the implementations, verify that all DQ rules applied to catalog items and in monitoring projects on this connection are supported. See Verify rule support.

To verify which pushdown implementation ran a specific job, see DPM Admin Console.

Prerequisites

Legacy pushdown stores the stage, user-defined functions (UDFs), and lookup data in a working database in Snowflake. The working database is required for DQ evaluation. Profiling doesn’t use it.

ONE requires a Snowflake stage because the data quality processing configurations available in ONE are translated to Snowflake SQL, either by utilizing native Snowflake functions or creating new functions called user defined functions (UDFs). These, along with any lookup data used in rules, must be stored in the connected Snowflake account to be available for processing.

You can use the database that is being processed as the working database (default), or create a custom database (recommended). For a custom database, additional setup in Snowflake is required: see Create the working database in Snowflake.

For Legacy pushdown to function, the credentials added for this connection need to be for a Snowflake user with write permissions, as Legacy pushdown involves writing into the working database (including creating tables).

You also need to make sure that DPE allows for automatic synchronization of user-defined functions. See DPE Configuration.

Create the working database in Snowflake

In Snowflake, execute the following SQL commands to create the working database and grant access to defined user roles.

You must run these commands from a SYSADMIN or ACCOUNTADMIN Snowflake account. To change the role, use the USE ROLE ACCOUNTADMIN; command, or in the user interface, select your profile name in the upper-right corner and choose ACCOUNTADMIN from the role dropdown.

In Snowflake, select + Worksheet, and follow these instructions:

  1. Create a new database using the following SQL command. Use the name you provided in the connection settings.

    CREATE DATABASE IF NOT EXISTS <working_db>;
    The database can be deleted if you no longer want to use pushdown in ONE. Multiple users can reuse the same database if they have access to it.

  2. Create a stage called _ATC_ONE_STAGE:

    CREATE STAGE IF NOT EXISTS _ATC_ONE_STAGE;

    You can use a custom stage name, but if you do, you need to additionally run GRANT CREATE STAGE ON SCHEMA public TO ROLE <pushdown_role>; in step 6, and use your custom name rather than _ATC_ONE_STAGE in step 7.

  3. Create a role for Ataccama. This role gives ONE permission to load data into your database: it needs to have read permissions on your data and write permissions for the working database. We don’t recommend reusing this role for other operations.

    CREATE ROLE IF NOT EXISTS <pushdown_role>;
GRANT ROLE <read_data_role> TO ROLE <pushdown_role>;

    This role needs to be specified in the JDBC connection string, for example:

    jdbc:snowflake//<snowflake_url>?db=<database_name>&warehouse=<warehouse_name>&role=<pushdown_role>&<other_param>=<other_param_value>

  4. Assign your newly-created role to the relevant Snowflake users:

    GRANT ROLE <pushdown_role> TO USER <sample_user>;
GRANT ROLE <pushdown_role> TO USER <another_user>;

  5. Grant access on the database you created in step 1 to the role you created in step 3. If no roles are specified here, all users have the access to the database.

    GRANT USAGE ON DATABASE <working_db> TO ROLE <pushdown_role>;

  6. Grant access on the public schema of the database to the role you created in step 3.

    GRANT USAGE ON SCHEMA public TO ROLE <pushdown_role>;
GRANT CREATE TABLE ON SCHEMA public TO ROLE <pushdown_role>;
GRANT CREATE SEQUENCE ON SCHEMA public TO ROLE <pushdown_role>;
GRANT CREATE FUNCTION ON SCHEMA public TO ROLE <pushdown_role>;

    If you specified a schema in Working schema, make sure to update the schema name here accordingly. If you left it blank, public is used by default and no changes are required.

  7. Grant access on the stage of the database to the role you created in step 3.

    GRANT READ ON STAGE _ATC_ONE_STAGE TO ROLE <pushdown_role>;
GRANT WRITE ON STAGE _ATC_ONE_STAGE TO ROLE <pushdown_role>;

    If you are using a custom stage name and not _ATC_ONE_STAGE, make sure to modify these commands accordingly.

Enable Legacy pushdown

  1. On the Snowflake connection, enable pushdown processing, as described in Enable pushdown processing.

  2. In the Pushdown processing section, turn on Legacy pushdown.

  3. Provide the following:

    • Working database: The name of the database you want to use for storing the stage, functions, and lookup data. This can be either the database that is being processed (default) or the custom database you created (see Prerequisites).

    • Working schema (Optional): A schema of your choice. If you use a custom schema, use its name instead of public when granting access to the schema in Create the working database in Snowflake.

      If you leave this blank, the public schema is used as default.

Supported functions in Legacy pushdown

Operators are supported the same way in both implementations. See Supported functions and operators.

Functions not supported in Legacy pushdown

Aggregation rules are not supported in Legacy pushdown, and neither are the following aggregation functions:

  • avg

  • avgif

  • concatenateif

  • count

  • countDistinct

  • countDistinctif

  • countif

  • countUnique

  • first

  • maximum

  • maximumif

  • minimum

  • minimumif

  • sum

  • sumif

All other functions supported in Snowflake pushdown (see Supported functions and operators) are supported in both implementations.

Functions supported only in Legacy pushdown

The following functions are supported only when Legacy pushdown is turned on. Review and adjust any rules that rely on them before turning off Legacy pushdown.

Functions supported only in Legacy pushdown

  • bitand

  • bitneg

  • bitor

  • bitxor

  • capitalize

  • capitalizeWithException

  • coding.fromBase64

  • coding.md5

  • coding.toBase64

  • countNonAsciiLetters

  • cpConvert

  • diceCoefficient

  • distinct

  • doubleMetaphone

  • editDistance

  • encode.base64

  • encode.md5

  • geoDistance

  • getMilliseconds

  • getParameterValue

  • getRequestTime

  • getRuntimeVersion

  • hamming

  • jaccardCoefficient

  • jaroWinkler

  • levenshtein

  • metaphone

  • ngram

  • preserveCase

  • randomUUID

  • replicate

  • set.approxSymmetricDifference

  • set.contains

  • set.difference

  • set.differenceResult

  • set.intersection

  • set.intersectionResult

  • set.lcsDifference

  • set.lcsDifferenceResult

  • set.lcsIntersection

  • set.lcsIntersectionResult

  • set.lcsSymmetricDifference

  • set.lcsSymmetricDifferenceResult

  • set.symmetricDifference

  • set.symmetricDifferenceResult

  • set.union

  • set.unionResult

  • sortWords

  • soundex

  • substituteMany

  • transliterate

  • trashConsonants

  • trashDiacritics

  • trashVowels

  • wordCombinations

Troubleshooting Snowflake legacy pushdown

Invalid samples don’t match the reported rule failures

If the catalog item source can change between reads, for example, a view over INFORMATION_SCHEMA in a frequently updated environment, or a query that uses RANDOM() or CURRENT_TIMESTAMP, Legacy pushdown can return different rows for the rule evaluation and for invalid samples.

To make both read the same result, enable plugin.snowflake.ataccama.one.dq-pushdown.snowpark-cache.materialize-input in the DPE configuration. See Snowflake query pushdown processing.

In Ataccama Cloud deployments, contact Ataccama Support for configuration assistance.

See also

Was this page useful?