Page tree

Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Comment: Published by Scroll Versions from space DEV and version next

D toc

You can create rules to validate the quality of the data in your sample. When created, these rules allow you to highlight exceptions to the rule to assist in building your data cleansing recipe steps.

  • data quality rule evaluates the values in one or more columns against a test criteria that you define. 
    • D s product
      rtrue
       includes a library of pre-defined data quality rule types. For more information, see Data Quality Rules Reference.
    • You can also create a custom rule using functions in the language. 
  • Data quality rules are one of several features available for monitoring data quality during import, transformation, and export of your datasets. For more information, see Overview of Data Quality.
Info

NOTE: Data quality rules are not transformation steps. They assess the current state of the sampled data in the Transformer page.


Info

NOTE: As you apply transformation steps to the data, the state of your data quality rules is automatically updated to reflect the changes. If you delete columns or other elements referenced in the data quality rules, errors are generated in the Transformer page.

Add Rule

You can add a rule from inside the Transformer page.

Steps:

  1. You create rules inside the Transformer page. In the toolbar at the top of the screen, click the Data Quality Rules icon on the right side of the toolbar. 
  2. The Data Quality rules panel opens in the context panel. For more information, see Data Quality Rules Panel.
  3. If you have not created any rules, the panel is empty. To create a new rule, click Add Rule.
  4. The available types of data quality rules are displayed. Select your rule type. 
    1. A simple one is Not Null. See Examples below.
    2. You can also add custom rules based on formulas that you specify. See "Add Custom Rule" below.
  5. Select the column or columns to which the rule applies. 

    Tip

    Tip: Some rules can be applied to multiple columns.

  6. Specify the other parameters as needed.
  7. Review the previewed results.

    Tip

    Tip: To simplify the preview, click the Show Only Affected Columns checkbox in the status bar.

  8. When finished, click Add to add the rule.

The new rule is displayed in the Data Quality Rules panel. In the data quality bar for the rule, green indicates the row values that have passed the rule, and red indicates the row values that failed.

  • Hover over either color to see the row counts and percentage. 
  • Select either color to highlight the indicated rows in the data grid.
Tip

Tip: After creating a rule, you can jump back and forth between the Recipe panel and this panel to review how your changes to your recipe steps affect the data quality bars for your rules.

Additional options are available in the context menu for the rule. For more information, see Data Quality Rules Panel.

Examples

Example - storeAddress column is Not Missing

The following data quality rule tests the values in the storeAddress column to see if they are missing (empty) values.

D trans
Typedq
p01NameColumn
p01ValuestoreAddress
SearchTermNot Missing

Example - primaryKey column is Unique

The following rule evaluates the primaryKey column to determine if all values in it are unique.

D trans
Typedq
p01NameColumn
p01ValueprimaryKey
SearchTermUnique

Example - SKU column matches pattern of SKU + 6 digits

Suppose the values of your SKUs must be in the form of "SKU + 6 digits".

Following uses 

D s item
itempatterns
 to perform the match. For more information on 
D s item
itempatterns
, see Text Matching.

D trans
p03Valuefalse
Typedq
p01NameColumn
p01ValueSKU
p02NameMatches pattern
p02Value`SKU{digit}{6}`
p03NameIgnore case
SearchTermMatch

Example - orderColor must be "Blue", "Yellow" or "Green"

This rule tests the values in the orderColor column to verify that all values are BlueYellow, or Green.

In the following, the Acceptable values must be formatted as an array. See below.

D trans
Typedq
p01NameColumn
p01ValueorderColor
p02NameAcceptable values
p02Value['Blue','Yellow','Green']
SearchTermIn Set

Add Custom Rule

You can add custom rules using formulas containing 

D s lang
 functions. 

D s lang
 is the proprietary language used to transform your data. You can also apply the functions of the language to your data quality rules. For more information, see Wrangle Language.

Steps:

  1. In the Data Quality Rules panel, click Add Rule.
  2. Under Other Rules, select Formula.
  3. In the Formula textbox, enter the 

    D s lang
     formula to test your data. 

    Info

    NOTE: The formula that you provide must evaluate to true or false. true values are highlighted in green in the data quality bar for the rule.

  4. For aggregation functions, you can group the evaluation of your rule based on the values in your grouping column. 

    Tip

    Tip: You can group by multiple columns. The first column is the outermost grouping.

  5. To add the rule, click Add.

Examples

Example - sum of daily sales >= 100

You can use data quality rules to perform some data analysis functions. For example, suppose you want to flag the dates where the total sales of all of your orders was less than 100. 

D trans
Typedq
p01NameFormula
p01ValueSUM(mySales) >= 100
p02NameGroup
p02ValuemyDate
SearchTermFormula

When this rule is added, the rows whose date total is less than 100 are flagged in red.

Edit Rule

To edit a rule, select Edit rule from the context menu for the rule in the panel.

Delete Rule

To delete a rule, select Delete rule from the context menu for the rule in the panel.

Export Rules

Job results

When you generate a profile as part of your job results, you can download the profile in JSON or PDF format. 

When you download the profile in JSON format, the set of rules for the job are also included. Search for profilerRules in the JSON file. 

For more information, see Job Details Page.

Flows

When flows are exported and imported, the rule definitions for the recipes in the flow are also exported. For more information, see Export Flow.