TO BE REVIEWED: DQ Rule Creation: Simple vs. Freeform vs. Native

Customer Question: “Please advise if this type of rule can be written as a Simple Rule, or must it be written as Freeform SQL or as a Native SQL rule?: BP_HIGH_IND is NULL AND BP_IND IS NULL AND ADDR_LINE3_TX is NOT NULL AND (length(ADDR_Line1_TX) > 40 or ADDR_LINE3_TX NOT rlike (’^[a-zA-Z0-9,#&.-/’ ]+$’))”

Our response (based on the docs: https://dq-docs.collibra.com/dq-visuals/rules, and the Rules > [Example] tab examples in Collibra DQ)

1. Simple: What you input as an expression is what comes after the WHERE in a query:

STATE_CD not in (‘MD’, ‘PA’, ‘NY’)

2. Freeform SQL: " allows you to input freeform SQL" for example:

Freeform SQL variable that infers date of DQ Job @runid

SELECT * FROM @primary p, @secondary s WHERE p.uid = s.uid AND p.dateColumn = @runId and s.dateColumn = @runId AND ( ( (p.orders / s.orders ) - 1 ) > 0.50)

Freeform SQL variable that checks against previous run @t1

SELECT * FROM @dataset today, @t1 yesterday WHERE today.uid = yesterday.uid AND (( (today.price / yesterday.price) - 1 ) > 0.01)

Freeform SQL example referencing another dataset truth set

select * from @customerdata where zip not in (select zip from @zip2StateTruthSet )

Using the Freeform SQL option. Allows you to pass in a regex pattern.

This expression matches a hyphen separated US Phone_Number.

select * from @customer_data where @customer_data.phone_num rlike ‘\d{3}-\d{3}-\d{4}’

3. Native SQL rule is for this case: “If you have rules already written in Oracle, Sybase, or DB2 syntax - Copy/Paste the rule directly into the Native SQL section.” Also if you are using pushdown, Native SQL is better.

Now regarding your case, since there are no JOINs or other complexities, and it is just a set of checks with AND and OR, Brian Mearns here said we should be able to run this rule as a Simple rule. The SQL that you have would come right after the WHERE, and this is the Simple SQL rule sytax. He also recommended testing it first without the RegEx, since RegEx makes it more complex. If you can get the rule to run with the RegEx check, add it in, and then we can see it all flow together.

If there is a problem is getting the Simple SQL rule to run, we can try to move to the Freeform SQL format.