DQP Configuration for Snowflake

Follow the steps below to prepare DQP for Snowflake sources.

Configure Snowflake DMF Information Query

For Snowflake sources, DQP requires you to create a Snowflake DMF Information Query. This is a published query in Alation with a query ID that is used by DQP to obtain a list of DMFs.

  1. Login to your Alation catalog, navigate to your Snowflake data source and click Open With Compose

  2. In Compose, login using a Snowflake service account that has privileges to list all DMFs in your entire Snowflake account.

  3. Enter the following query and click Run. For more information on this query please refer to Snowflake’s documentation

SHOW DATA METRIC FUNCTIONS IN ACCOUNT

  1. Click the Publish button in Compose.

  2. Give the query a title, for example Snowflake DMF Information Query. Enter an optional description, for example Data Quality query used by DQP

  3. Click Save and make a note of the numeric query ID displayed in the URL. For example, if the URL is https://my.alationcloud.com/compose/query/34/ then your query ID would be 34.

  4. Return to Alation Service Manager (ASM) and click Data Quality Processor > DQP Settings in the left navigation.

  5. The DQP Settings modal is displayed. In the Snowflake DMF Information Query ID section enter your query ID obtained above:

../../../_images/FDEdqpDMFquery.png

  1. Click Test and Save. You will see the message Configuration updated confirming the DMF query has been configured successfully.

Configure Snowflake DMF Results Query

  1. Login to your Alation catalog, navigate to your Snowflake data source and click Open With Compose.

  2. In Compose, login using a Snowflake service account that has privileges to read the snowflake.local.data_quality_monitoring_results view.

  3. Enter the following query and click Run. The query returns all the DQ event data produced by the DMFs in your Snowflake account. For more information please refer to Snowflake’s documentation

SELECT TABLE_DATABASE,
     TABLE_SCHEMA,
     TABLE_NAME,
     ARRAY_TO_STRING(ARGUMENT_NAMES, ',') AS COLUMN_NAME,
     ARRAY_TO_STRING(ARGUMENT_TYPES, ',') AS OBJECT_TYPE,
     MEASUREMENT_TIME AS START_TIME,
     CONCAT(METRIC_DATABASE, '.', METRIC_SCHEMA, '.', METRIC_NAME) AS METRIC_TYPE,
     value AS METRIC_VALUE
FROM snowflake.local.data_quality_monitoring_results
QUALIFY ROW_NUMBER() OVER (PARTITION BY TABLE_NAME, TABLE_SCHEMA, TABLE_DATABASE, METRIC_ID, METRIC_NAME, METRIC_SCHEMA, METRIC_DATABASE, METRIC_RETURN_TYPE, ARGUMENT_TYPES, ARGUMENT_NAMES ORDER BY SCHEDULED_TIME DESC) = 1;

Once executed the results will appear in Compose after a few seconds. Example output:

../../../_images/FDEdqpSFEventsResultset.png

  1. Click the Publish button in Compose.

  2. Give the query a title, for example Snowflake DQ Events Query. Enter an optional description, for example Data Quality query used by DQP

  3. Click Save and make a note of the numeric query ID displayed in the URL. For example, if the URL is https://my.alationcloud.com/compose/query/23/ then your query ID would be 23.

Note

Scheduling this query to run on a regular basis is recommended, but the scheduling of the compose query should be no more frequent than the DMFs running on Snowflake. Use the Schedule Settings button in Compose to automate your query execution. Ensure your Compose query runs after the DMFs have completed in Snowflake.

  1. Return to Alation Service Manager (ASM) and click Data Quality Processor > Define in the left navigation.

  2. Locate the Snowflake - Sync Snowflake Data Metric Function results with Alation option and click Start

  3. Enter the query ID from the step above and click Test, then click Start:

../../../_images/FDEdqpResults1.png

  1. On the Alation Data Quality Processor - Define New Rules page any DQ rules already created are displayed. Click the Deploy Rules button:

../../../_images/FDEdqpResults2.png

  1. The Ready to deploy modal is displayed and you are prompted to enter a cron schedule expression. For examples of cron schedule expressions please refer to For example, to pull DQ results once daily at midnight use 0 0 * * * as shown:

../../../_images/FDEdqpResults3.png

  1. Click Deploy

You have now configured Alation DQP with your data source. Data Quality information will appear in Alation’s Data Health tabs, table pages, column pages, and lineage diagrams. Here’s an example Data Health tab:

../../../_images/FDEdqpDataHealthTab.png

Now proceed to the DQP Operation documentation.