How does AWS Glue data quality custom sql work with no unique column?

Question

My dataframe has 2 columns - name and age. If there is name Manish with 2 rows one with age 16 and another with age 23 , will AWS data quality fail both, pass both or one fail one pass. for below custom sql

"select Name from primary where Age > 18"

In documentation. that the choice of column name, in the select clause should be a unique column, is not mentioned anywhere. 
https://docs.aws.amazon.com/glue/latest/dg/dqdl.html#dqdl-rule-types-CustomSql

Answer

For the custom SQL rule question, when AWS Glue Data Quality evaluates the custom SQL rule:

It will run the SQL query on the dataframe. In this case "select Name from primary where Age > 18".

It will return the rows that satisfy the condition i.e Age > 18.

If there are multiple rows for the same Name that satisfy the condition, it will return both rows. For example, if there are two rows for name "Manish", one with age 16 and other with age 23, it will return the row with age 23 since that satisfies the condition but not the row with age 16.

So in this case, it will pass the row with name "Manish" and age 23 but fail the row with the same name "Manish" but age 16.

The documentation does not explicitly mention the behavior for multiple rows with same value, but based on how custom SQL rules work, it is expected to behave in this way.

How does AWS Glue data quality custom sql work with no unique column?

相關內容