Templates

Templates are custom and out-of-the-box reusable rules that are ideal for rules that apply to more than one dataset. Because Templates are dataset independent, you can write a rule once and then run it against any dataset in your Collibra DQ environment as many times as necessary. This can eliminate the need to write new rules for frequently used compliance checks from one dataset to the next, and can also reduce the amount of time you spend writing complex rules, such as ones that include regex and cast operations.

image of the template rules page

The Templates page shows a list of all template rules in your Collibra DQ environment and lets you add new ones with the Add Rule Template wizard.

Adding a Template Rule

  1. On the sidebar menu, click Rules wrench icon Rules, then click Templates.
    2. The Templates page opens.
  2. In the upper right corner of the Templates page, click Add Rule Template.
    3. The Add Rule Template form opens.
  3. Enter the required information.
    4. Option Description Required
    Name The name of your template rule. Enter a unique name to easily identify your template rule.

    Yes

    Type

    The rule type of your template rule. Only Simple and Freeform are eligible rule types for templates.

    Select an option from the dropdown menu.

    Note If you select Freeform (SQLF), you can use a more complex SQL expression that includes regex, but the validation function will be unavailable.

    Yes

    Is Regex?

    Select this option if the Value field contains a regular expression (regex). When you select Is Regex, the Type is SQLG and contains just the regex value, and neither select * from statement nor quotes are required for the rule to run. When Is Regex is not selected, the Type is SQLF and requires a select * from statement to run.

    Important 
    When you select Is Regex, the Type is always SQLG. The format of your regex must be just the expression itself without quotes or any other SQL.

    No

    Value/Regex

    The query or regex that your rule template applies to run against a dataset.

    Enter a query or expression for your rule template to run against a dataset.

    Note Value displays when Is Regex is not selected, and Regex displays when Is Regex is selected.

    Yes

    Input/Validate Input

    Because regex treats inputs as one continuous string, the input field informs the system to treat each line separately.

    Optionally enter a value or a comma delimited list of values, then click Validate Input to test them against your regex.

    Note Input only displays when Is Regex is selected.

    No

    Description The description that helps identify your template rule. It is best practice to base your description on what the Value field queries.

    No

  4. Click Save.
  5. Your template rule is available on the Templates page and on the Rule Workbench when you select it from the Templates section of the Type dropdown menu.

Using a Template Rule on the Rule Workbench

To use a data class rule to run against a dataset, follow these steps.

  1. Click the Rules wrench icon in the sidebar menu, then click Rule Builder.
    2. The Dataset Rules page opens.
  2. There are two ways to open the Rule Workbench.
    1. From an existing rule on the Rules tab, click Actions, then click Edit.
      The Rule Workbench page opens with the preferences of your existing rule.
    2. Click Add Rule in the upper right corner to create a new rule.
      The Rule Workbench page opens.
  3. Click the Type dropdown menu and scroll down to the Template section, then select your Template rule.
    4. The Template rule populates in the query builder.
  4. Enter a name for your rule in the Rule Name input field.

    5. Important Rule names cannot contain spaces.

  5. Select a primary column from the Primary Col dropdown menu.

    6. Note Primary Column is required when you use Template rules.

  6. Optionally click to add a secondary dataset.
    1. Enter the name of your secondary dataset in the search bar when the Add Secondary Dataset dialog appears, then click Add Secondary Dataset.
  7. Click validate rule icon Validate to validate your expression.
  8. Optionally click prettify sql icon Beautify to format your SQL query in a more readable layout.
  9. Click Save to apply your rule to your dataset.

Out-of-the-box Templates

Collibra DQ has 12 out-of-the-box (OOTB) templates for common rule checks. OOTB templates cannot be edited or deleted.

Name Value Description
DECIMAL_3_rule

^\d{4}-\d{2}-\d{2}\s\d{2}:\d{2}:\d{2}.\d{1}$

The rule breaks when a column value does not contain a sequence of 3 digits before a decimal, and three digits after the decimal.
Having_Count_Greater_Than_One

select count(*) as cnt, $colNm from dataset group by $colNm having cnt > 1

Checks if the count of an exact match value occurs more than once.
is_DATE

$colNm rlike '^\\d{4}-\\d{2}-\\{2}'

The rule breaks when a column value is not in YYYY-MM-DD format.
less_than_200

$colNm < 200

Checks for any column values less than 200.
Not_In_Current_run

select $colNm from @t1 where $colNm not in ( select distinct($colNm) from dataset )

Checks if column values are not present in the current run.
Not_In_Previous_run

select $colNm from dataset where $colNm not in ( select distinct($colNm) from @t1 )

Checks if column values are not present in the previous run.
Percent_Move_5

select i.$colNm, s.averg from dataset i , (select avg(cast($colNm as double ) ) as averg from dataset ) s where i.$colNm < (s.averg * 0.95 ) or i.$colNm > (s.averg * 1.05)

Checks if there are column values present that are 5% greater or less than the average value in that column.
Percent_Move_10

select i.$colNm, s.averg from dataset i , (select avg(cast($colNm as double ) ) as averg from dataset ) s where i.$colNm < (s.averg * 0.90 ) or i.$colNm > (s.averg * 1.10)

Checks if there are column values present that are 10% greater or less than the average value in that column.
Percent_Move_20

select i.$colNm, s.averg from dataset i , (select avg(cast($colNm as double ) ) as averg from dataset ) s where i.$colNm < (s.averg * 0.80 ) or i.$colNm > (s.averg * 1.20)

Checks if there are column values present that are 20% greater or less than the average value in that column.
Percent_Move_50

select i.$colNm, s.averg from dataset i , (select avg(cast($colNm as double ) ) as averg from dataset ) s where i.$colNm < (s.averg * 0.50 ) or i.$colNm > (s.averg * 1.50)

Checks if there are column values present that are 50% greater or less than the average value in that column.
Two_Decimal_Places

select d[1] as rs from ( select SPLIT($colNm, '\\.') as d from dataset ) where length(d[1]) > 2

Checks if the column decimal place precision exceeds a length of 2.
USTAX_rule

$colNm NOT rlike 'USTAX'

The rule breaks when the specified column does not contain "USTAX".