Create a custom SQL rule using SQL AI

You can use SQL AI to suggest custom SQL queries based on your plain-text input.

Prerequisites

  • You have an existing Data Quality Job with at least one run.
  • The Data Quality Pushdown Processing Edge capability is enabled for your data source.
  • You have the Data Quality Editor or Data Quality Manager resource role with the following resource permissions, depending on the actions you intend to perform:
    • Action Role and permission requirement
    Create a custom rule monitor You have the Data Quality Editor or Data Quality Manager resource role with the Data Quality Job > Create Monitor resource permission.
    Edit, suppress, or activate a monitor You have the Data Quality Editor or Data Quality Manager resource role with the Data Quality Job > Edit Monitor resource permission.
    Delete a rule monitor You have the Data Quality Editor or Data Quality Manager resource role with the Data Quality Job > Delete Monitor resource permission.
    To preview rule break records You have the Data Quality Editor or Data Quality Manager resource role with the Data Quality Job > Preview Rule Break Records resource permission.

Steps

  1. Select the job to which you want to add a rule from the Job drop-down list, or enter its name in the input field.
  2. Enter a unique name in the Rule name input field.

    3. Note You cannot use a name that is already in use by another rule in the job. Rule names can contain periods (.), underscores (_), and hyphens (-). All other special characters are not allowed.

  3. Optionally, select a primary column from the Primary column drop-down list. This allows Collibra to associate your rule with a column asset based on the selected primary column.
    4. Important When the Require primary column setting is enabled, this field is required.
  4. In the upper-right corner of the SQL editor, click AI icon SQL AI to allow Collibra to suggest a SQL query based on your plain-text input.
    The SQL AI chat box appears in the right side of the SQL editor.
  5. In the chat box input field, enter a plaint-text prompt.
    6. Example 
    • "high is more than 50"
    • "find all of the unique values in Column1 and show how many records exist for each"
    • "compare the 2 dates 2018-01-13 to 2018-01-14 - write 2 separate queries for each date, using trade date - write as a common table expression for tables a and b - join using symbol column - use a join to identify which values are in 2018-01-14 that are not in 2018-01-13 - the goal is to find values that are missing from the previous day"
  6. Click Send icon.
    A suggested SQL query is returned.
  7. Click Content copy icon to copy the suggested SQL to your clipboard, Assignment return outlined icon to copy the suggested SQL directly to the SQL editor, or enter a new prompt.
  8. Optionally, click Align left icon Format to automatically format the SQL for improved readability.
  9. Optionally, click Validate SQL to validate the syntax of your query.
  10. Optionally, click the Settings tab.
    The settings tab opens, where you can associate data quality dimensions with your rule, set a rule tolerance percentage, and add notifications and a description.
  11. Click Save.
    The rule is added to your job.

What's next

  • About rule-level settings
  • Go to the Job Details page to:
    • Manually re-run your job and view the output of the rule monitor in the Monitors tab.
    • View the output of the rule monitor in the Monitors tab after your scheduled job runs automatically.