Data Quality Definition Language (DQDL) reference

Data Quality Definition Language (DQDL) is a domain specific language that you use to define rules for Amazon Glue Data Quality.

This guide introduces key DQDL concepts to help you understand the language. It also provides a reference for DQDL rule types with syntax and examples. Before you use this guide, we recommend that you have familiarity with Amazon Glue Data Quality. For more information, see Amazon Glue Data Quality.

DQDL syntax

A DQDL document is case sensitive and contains a ruleset, which groups individual data quality rules together. To construct a ruleset, you must create a list named Rules (capitalized), delimited by a pair of square brackets. The list should contain one or more comma-separated DQDL rules like the following example.

Rules = [
   IsComplete "order-id",
   IsUnique "order-id"
]

Rule structure

The structure of a DQDL rule depends on the rule type. However, DQDL rules generally fit the following format.

<RuleType> <Parameter> <Parameter> <Expression>

RuleType is the case-sensitive name of the rule type that you want to configure. For example, IsComplete, IsUnique, or CustomSql. Rule parameters differ for each rule type. For a complete reference of DQDL rule types and their parameters, see DQDL rule type reference.

Composite rules

You can use the same operator to connect multiple rules, so the following rule combination is allowed.

(Mean "Star_Rating" > 3) and (Mean "Order_Total" > 500) and (IsComplete "Order_Id")

You can combine the logical operators into a single expression. For example:

(Mean "Star_Rating" > 3) and ((Mean "Order_Total" > 500) or (IsComplete "Order_Id"))

You can also author more complex, nested rules.

(RowCount > 0) or ((IsComplete "colA") and (IsUnique "colA"))

How Composite rules work

By default, Composite Rules are evaluated as individual rules across the entire dataset or table and then the results are combined. In other words, it evaluates the entire column first and then applies the operator. This default behaviour is explained below with an example:

# Dataset

+------+------+
|myCol1|myCol2|
+------+------+
|     2|     1|
|     0|     3|
+------+------+

# Overall outcome

+----------------------------------------------------------+-------+
|Rule                                                      |Outcome|
+----------------------------------------------------------+-------+
|(ColumnValues "myCol1" > 1) OR (ColumnValues "myCol2" > 2)|Failed |
+----------------------------------------------------------+-------+
      

In the above example, Amazon Glue Data Quality first evaluates (ColumnValues "myCol1" > 1) which will result in a failure. Then it will evaluate (ColumnValues "myCol2" > 2) which will also fail. The combination of both results will be noted as FAILED.

However, if you prefer an SQL like behaviour, where you need the entire row to be evaluated, you have to explicitly set the ruleEvaluation.scope parameter as shown in additionalOptions in the code snippet below.

object GlueApp {
  val datasource = glueContext.getCatalogSource(
    database="<db>",
    tableName="<table>",
    transformationContext="datasource"
  ).getDynamicFrame()

  val ruleset = """
    Rules = [
        (ColumnValues "age" >= 26) OR (ColumnLength "name" >= 4)        
    ]
  """

  val dq_results = EvaluateDataQuality.processRows(
    frame=datasource,
    ruleset=ruleset,
    additionalOptions=JsonOptions("""
        {
          "compositeRuleEvaluation.method":"ROW"
        }
      """
    )
  )
}     
      

In Amazon Glue Data Catalog, you can easily configure this option in the user interface as shown below.

Once set, the composite rules will behave as a single rule evaluating the entire row. The following example illustrates this behaviour.

# Row Level outcome

+------+------+------------------------------------------------------------+---------------------------+
|myCol1|myCol2|DataQualityRulesPass                                        |DataQualityEvaluationResult|
+------+------+------------------------------------------------------------+---------------------------+
|2     |1     |[(ColumnValues "myCol1" > 1) OR (ColumnValues "myCol2" > 2)]|Passed                     |
|0     |3     |[(ColumnValues "myCol1" > 1) OR (ColumnValues "myCol2" > 2)]|Passed                     |
+------+------+------------------------------------------------------------+---------------------------+     
      

Some rules cannot be supported in this feature because their overall outcome rely on thresholds or ratios. They are listed below.

Rules relying on ratios:

Rules dependent on thresholds:

When the following rules include with threshold, they are not supported. However, rules that do not involve with threshold remain supported.

Expressions

If a rule type doesn't produce a Boolean response, you must provide an expression as a parameter in order to create a Boolean response. For example, the following rule checks the mean (average) of all the values in a column against an expression to return a true or false result.

Mean "colA" between 80 and 100

Some rule types such as IsUnique and IsComplete already return a Boolean response.

The following table lists expressions that you can use in DQDL rules.

Completeness "colA" = "1.0",
ColumnValues "colA" = "2022-06-30"

ColumnValues "colA" != "a",
ColumnValues "colA" != "2022-06-30"

ColumnValues "colA" > 10

ColumnValues "colA" < 1000,
ColumnValues "colA" < "2022-06-30"

ColumnValues "colA" >= 10

ColumnValues "colA" <= 1000

Mean "colA" between 8 and 100,
ColumnValues "colA" between "2022-05-31" and "2022-06-30"

ColumnValues "colA" not between "2022-05-31" and "2022-06-30"

ColumnValues "colA" in [ 1, 2, 3 ],
ColumnValues "colA" in [ "a", "b", "c" ]

ColumnValues "colA" not in [ 1, 2, 3 ],
ColumnValues "colA" not in [ "a", "b", "c" ]

ColumnValues "colA" matches "[a-zA-Z]*"

ColumnValues "colA" not matches "[a-zA-Z]*"

ColumnValues "load_date" > (now() - 3 days)

ColumnValues "colA" in ["A", "B"] with threshold > 0.8,
ColumnValues "colA" matches "[a-zA-Z]*" with threshold between 0.2 and 0.9
ColumnDataType "colA" = "Timestamp" with threshold > 0.9

Keywords for NULL, EMPTY and WHITESPACES_ONLY

If you want to validate if a string column has a null, empty or a string with only whitespaces you can use the following keywords:

For a numeric or date based expression, if you want to validate if a column has a null you can use the following keywords.

Filtering with Where Clause

You can filter your data when authoring rules. This is helpful when you want to apply conditional rules.

<DQDL Rule> where "<valid SparkSQL where clause> "

The filter must be specified with the where keyword, followed by a valid SparkSQL statement that is enclosed in quotes ("").

If the rule you wish to add the where clause to a rule with a threshold, the where clause should be specified before the threshold condition.

<DQDL Rule> where "valid SparkSQL statement>" with threshold <threshold condition>
        

With this syntax you can write rules like the following.

Completeness "colA" > 0.5 where "colB = 10"
ColumnValues "colB" in ["A", "B"] where "colC is not null" with threshold > 0.9
ColumnLength "colC" > 10 where "colD != Concat(colE, colF)"

We will validate that the SparkSQL statement provided is valid. If invalid, the rule evaluation will fail and we will throw the an IllegalArgumentException with the following format:

Rule <DQDL Rule> where "<invalid SparkSQL>" has provided an invalid where clause :
<SparkSQL Error>

Where clause behaviour when Row level error record identification is turned on

With Amazon Glue Data Quality, you can identify specific records that failed. When applying a where clause to rules that support row level results, we will label the rows that are filtered out by the where clause as Passed.

If you prefer to separately label the filtered out rows as SKIPPED, you can set the following additionalOptions for the ETL job.

object GlueApp {
  val datasource = glueContext.getCatalogSource(
    database="<db>",
    tableName="<table>",
    transformationContext="datasource"
  ).getDynamicFrame()

  val ruleset = """
    Rules = [
       IsComplete "att2" where "att1 = 'a'"        
    ]
  """

  val dq_results = EvaluateDataQuality.processRows(
    frame=datasource,
    ruleset=ruleset,
    additionalOptions=JsonOptions("""
        {
          "rowLevelConfiguration.filteredRowLabel":"SKIPPED"
        }
      """
    )
  )
}

As an example, refer to the following rule and dataframe:

IsComplete att2 where "att1 = 'a'"

Dynamic rules

You can now author dynamic rules to compare current metrics produced by your rules with their historical values. These historical comparisons are enabled by using the last() operator in expressions. For example, the rule RowCount > last() will succeed when the number of rows in the current run is greater than the most recent prior row count for the same dataset. last() takes an optional natural number argument describing how many prior metrics to consider; last(k) where k >= 1 will reference the last k metrics.

To form valid expressions use last(k), where k > 1 requires an aggregation function to reduce multiple historical results to a single number. For example, RowCount > avg(last(5)) will check whether the current dataset’s row count is strictly greater than the average of the last five row counts for the same dataset. RowCount > last(5) will produce an error because the current dataset row count can't be meaningfully compared to a list.

Supported aggregation functions:

Sample expressions

ColumnCorrelation

DistinctValuesCount

Most rule types with numeric conditions or thresholds support dynamic rules; see the provided table, Analyzers and Rules, to determine whether dynamic rules are supported for your rule type.

Excluding statistics from dynamic rules

Sometimes, you will need to exclude data statistics from your dynamic rule calculations. Lets say you did a historical data load and you don’t want that to impact your averages. To do this, open the job in Amazon Glue ETL and choose the Data Quality Tab, then choose Statistics and select the statistics that you want to exclude. You will be able to see a trend chart along with a table of statistics. Select the values you want to exclude and choose Exclude Statistics. Now the excluded statistics will not be included in the dynamic rule calculations.

Analyzers

DQDL rules use functions called analyzers to gather information about your data. This information is employed by a rule’s Boolean expression to determine whether the rule should succeed or fail. For example, the RowCount rule RowCount > 5 will use a row count analyzer to discover the number of rows in your dataset, and compare that count with the expression > 5 to check whether more than five rows exist in the current dataset.

Sometimes, instead of authoring rules, we recommend creating analyzers and then have them generate statistics that can be used to detect anomalies. For such instances, you can create analyzers. Analyzers differ from rules in the following ways.

Analyzers can independently exist without rules, so you can quickly configure them and progressively build data quality rules.

Some rule types can be input in the Analyzers block of your ruleset to run the rules required for analyzers and gather information without applying checks for any condition. Some analyzers aren't associated with rules and can only be input in the Analyzers block. The following table indicates whether each item is supported as a rule or a standalone analyzer, along with additional details for each rule type.

Example Ruleset with Analyzer

The following ruleset uses:

Analyzer metric results can be viewed in the Data Quality tab for your job run.

Rules = [
   RowCount > avg(last(3))
]
Analyzers = [
   DistinctValuesCount "Name",
   ColumnLength "Name"
]     
        

Amazon Glue Data Quality supports the following analyzers.

Comments

You can use the '#' character to add a comment to your DQDL document. Anything after the '#' character and until the end of the line is ignored by DQDL.

Rules = [
    # More items should generally mean a higher price, so correlation should be positive
    ColumnCorrelation "price" "num_items" > 0
]