[BUG]: SQL Expression Parsing Error, when expression includes quotes

[bullah12]

Is there an existing issue for this?

• I have searched the existing issues

Current Behavior

When defining a SQL expression rule within a yaml file, if the expression contains any quotation marks, the parser is unable to validate the checks using the dq_engine.load_checks_from_workspace_file(file) function.

For example, when applying the following YAML rule to dq_engine.load_checks_from_workspace_file():

 - name: example_test
   criticality: error
   check:
     function: sql_expression
     arguments:
       expression: col1 like "Team %"

It will return the following error:

ParserError: while parsing a flow mapping
  in "<unicode string>", line 1, column 45:
     ...  "sql_expression", "arguments": {"expression": "col1 like "Team %""}}
                                         ^
expected ',' or '}', but got '<scalar>'
  in "<unicode string>", line 1, column 70:
     ... ents": {"expression": "col1 like "Team %""}}
                                         ^
File <command-7538297306428071>, line 1
----> 1 checks = dq_engine.load_checks_from_workspace_file(workspace_path="/Workspace/Users/<user-name>/<rules_file.yml>")
File /local_disk0/.ephemeral_nfs/cluster_libraries/python/lib/python3.10/site-packages/yaml/parser.py:549, in Parser.parse_flow_mapping_key(self, first)
    547     else:
    548         token = self.peek_token()
--> 549         raise ParserError("while parsing a flow mapping", self.marks[-1],
    550                 "expected ',' or '}', but got %r" % token.id, token.start_mark)
    551 if self.check_token(KeyToken):
    552     token = self.get_token()

The same problem will occur if you used SQL expressions like:

• col1 = 'Team A'

Expected Behavior

The function should be able to expect quotation marks as part of the SQL expression argument, and ensure the quotation marks are carried over during JSON parsing.

Since it does not, you need to find a workaround to ensure the 'dq_engine.load_checks_from_workspace_file' function can ignore the quotation marks when parsing to JSON (Workaround is in Steps to Reproduce section)

Steps To Reproduce

As a temporary workaround, do the following:

1. Replace any quotation marks you would typically use in your SQL expression with the curly apostrophe () [Notes]. e.g: col1 like Team %`
2. Run your DQX load function. e.g: checks = dq_engine.load_checks_from_workspace_file(workspace_path="")
3. Define the following function in your notebook/workspace:

def replace_quotes(checks,symbol='`'):
    cleaned_checks = checks.copy()
    for check in cleaned_checks:
        if check['check']['function']=='sql_expression':
            if symbol in check['check']['arguments']['expression'].upper():
                check['check']['arguments']['expression'] = check['check']['arguments']['expression'].replace(symbol,'"')
    return cleaned_checks

4. Pass your checks variable through the function. e.g: checks = replace_quotes(checks)
5. Check JSON form of your checks to ensure the rule has been translated over correctly

Notes:
The curly apostrophe can be replaced with any other combination of symbols you'd like. You just need to ensure you change it within your replace_quotes function too.

Cloud

Azure

Operating System

Windows

Relevant log output

[mwojtyczka]

@bullah12 thank you for reporting this.
Fix should be merged and released this week: #165