Issues with DQDL CustomSQL syntax

Question

I'm trying to do something really basic with a CustomSQL but can't figure out how to get DQDL to parse and save the ruleset. Let's say we have columns a and b and we want to make sure that whenever the value of column a is "foo" the column b should contain "bar". I tried to solve the problem with the following ruleset

```
Rules = [
    CustomSql "select b from primary where a = 'foo'" = "bar"
]
```

This results in a "InvalidInputException (status: 400): DataQuality rules cannot be parsed". Am I doing something wrong or is this just not possible to do?

Accepted Answer

CustomSQL commands in DQDL are not meant for giving a row by row data quality indication , rather they should calculate an overall data quality for the whole dataset. SQL queries should return a single numeric value (ref. documentation).

To achieve what you want, you need to write a SQL query to count all the rows where a is equal to 'foo' and b is not equal to 'bar' and then you need to check this count against a numeric threshold (zero for all compliant data) in DQDL rule.

Answer

There are two types of use cases for the CustomSQL rule. In my case, I want to use a rule of the Custom SQL to retrieve row-level results type. For example: CustomSql "select Name from primary where Age > 18".

However, every time I use a rule of this type, I get the following error: 'No condition provided for rule with non-boolean rule type: CustomSql'. I can't even save the ruleset.

When using rules of the type: CustomSql "select count(*) from primary" between 10 and 20, I don't encounter any issues.

I don't understand why it's giving an error if it's a use case mentioned in the documentation. I've already tried it with different databases but still the same problem... :(

Issues with DQDL CustomSQL syntax

관련 콘텐츠