New SQL Features like joins or IN Conditions

[etellezgh]

Hi Team:

First of aLL congratulations for the DQX Framework. is awesome to work with this

Suggestion regarding SQL_EXPRESSION. It would be very convenient if in this type of clauses you could do things like check a field within another table with filters or the ability to execute joins to bring the content of fields. I am currently translating rules that we have in an internal Data Quality engine and the sentences are like these: MaterialId in (SELECT DISTINCT Field A FROM .

WHERE somefield= 'X'). When parsing the JSON string I receive the message Invalid JSON: Expecting ',' delimiter: line 92 column 120 (char 2567)

[mwojtyczka]

Thank you for your kind feedback :)

SELECT DISTINCT Field A FROM . WHERE somefield= 'X'
Is this to check if you have duplicate values? if yes you can use is_unique check for this.

To achieve max performance the checks are implemented as column expressions that expect a single dataframe. It is currently allowed to apply one dataframe for row level checks. If you want to perform joins or aggregations as part of the checks you could create the transformation before and apply checks on the result:

Example:

df = spark.sql("""
    SELECT t2.*
    FROM (
        SELECT DISTINCT fieldA FROM table1 WHERE somefield = 'X'
    ) AS filtered
    JOIN table2 AS t2
    ON filtered.fieldA = t2.fieldA
""")

checked = dq_engine.apply_checks_by_metadata(df, checks)

Would this work for you?

We will be working on ability to use multiple dataframes with a focus to compare them:
#74

We will also be soon adding ability to define data set level checks (aggregation level checks applied on the whole dataframe and not individual rows only):
#43

[mwojtyczka]

#316

[mwojtyczka]

This is now available with foreign_key or sql_query checks: https://databrickslabs.github.io/dqx/docs/reference/quality_rules/#dataset-level-checks-reference