1. User Guide
  2. Data Quality Framework
  3. Data Quality Rules
  4. Data Check Definitions
  5. Single Column Data Check Definitions

Single Column Data Check Definitions

Single Column Data Checks help to perform data quality check on only one column selected during DQ Rule creation.

You can include the following Data Quality checks in the DQ Rule, if the check type is set to Single Column Check.

  • Range Check - Range Check identifies if the base column data falls outside a specified range of Minimum and Maximum value. Range check can be enabled only if the base column has date or number value.
    • Select the check-box to enable the Range check.
    • Set the warning level to Severity, Warning or Information.
    • If the selected Base Column is of Date type, select Minimum and Maximum date range. If the selected base column is of Number type, enter the Range value. You can specify numeric, decimal, and negative values for number Data type.
    • Check the Inclusive check-box, to include the specified date/value during the data check.
    • Click Edit to add specific filter expressions, as additional conditions. For more information, refer to Creating Expressions.
    • Select the Assignment option. The Assignment option is enabled only if Warning/Information is selected as the Warning level.
      • Select the Assignment Type from the drop-down list. For more information, see Assignment Types.
      • Specify the Assignment Value.
      • Select the Message Severity as 1 or 2 from the drop-down list.
      • Select a pre-defined Message to be displayed from the drop-down list.

        To enter a specific message other than the listed pre-defined messages, select Custom Message, in the Message drop-box and enter the required Custom Message.

  • Null Value Check -Null Value Check checks identifies if there is any null value in the selected column.
    • Select the check-box to enable the Null Value check.
    • Set the warning level to Severity, Warning or Information.
    • Click Edit to add specific filter expressions, as additional conditions.
    • Select the Assignment option. The Assignment option is enabled only if Warning/Information is selected as the Warning level.
      • Select the Assignment Type from the drop-down list. For more information, see Assignment Types.
      • Specify the Assignment Value.
      • Select the Message Severity as 1 or 2 from the drop-down list.
      • Select a pre-defined Message to be displayed from the drop-down list.

        To enter a specific message other than the listed pre-defined messages, select Custom Message, in the Message drop-box and enter the required Custom Message.

  • Blank Value Check -Null Value Check checks identifies if there is any entry in the selected column is blank.
    • Select the check-box to enable the Blank Value check.
    • Set the warning level to Severity, Warning or Information.
    • Click Edit to add specific filter expressions, as additional conditions.
    • Select the Assignment option. The Assignment option is enabled only if Warning/Information is selected as the Warning level.
      • Select the Assignment Type from the drop-down list. For more information, see Assignment Types.
      • Specify the Assignment Value.
      • Select the Message Severity as 1 or 2 from the drop-down list.
      • Select a pre-defined Message to be displayed from the drop-down list.

        To enter a specific message other than the listed pre-defined messages, select Custom Message, in the Message drop-box and enter the required Custom Message.

  • Data Length Check -Data Length Check checks for the length of the base column data using a minimum and maximum value and identifies if it falls outside the specified range.
    • Select the check-box to enable the Data Length check.
    • Set the warning level to Severity, Warning or Information.
    • Enter the Minimum and maximum values for validation.
    • Click Edit to add specific filter expressions, as additional conditions.
  • Duplicate Check - Duplicate Check can be used when a combination of column is unique and identifies all the duplicate data of the base table in terms of the columns selected for the duplicate check.
    • Select the check-box to enable the Duplicate Check.
    • Set the warning level to Severity, Warning or Information.
    • Click Edit to add specific filter expressions, as additional conditions.
    • Click Edit and select the required column to be added to the Column List, for duplicate check validation.
  • Custom Check/Business Check- Custom Check/Business Check is a valid SQL query to identify the data with the query specified as the Custom/business SQL. You can define the SQL, but the Select clause of the query has to follow the order as specified in the template of the Custom Check panel.

    Sample Template : "SELECT 'N_COUNTRY_SKEY' PKNAMES, N_COUNTRY_SKEY PK1, null PK2, null PK3, null PK4, null PK5, null PK6, null PK7, null PK8, V_COUNTRY_DESC ERRORCOL FROM DIM_COUNTRY WHERE N_COUNTRY_SKEY >50"

    • Select the check-box to enable the Custom Check.
    • Set the warning level to Severity, Warning or Information.
    • Enter the SQL Query to perform the custom check.
  • Column Reference/Specific Value Check - Column Reference / Specific Value Check compares the base column data with another column of the base table or with a specified direct value using the list of pre-defined operators.
    • Select the check-box to enable the Column Reference check.
    • Set the warning level to Severity, Warning or Information. Column reference check can be enabled only if the base column has date or number value.
    • Select the Mathematical Operator from the drop-down list.
    • Select the Filter Type as one of the following:
      • Select Specific Value and specify the Value. You can specify numeric, decimal, and negative values for number Data type.
      • Select Another Column and select Column Name from the drop-down list.
    • Click Edit to add specific filter expressions, as additional conditions.
    • Select the Assignment option. The Assignment option is enabled only if Warning/Information is selected as the Warning level.
      • Select the Assignment Type from the drop-down list. For more information, see Assignment Types.
      • Specify the Assignment Value.
      • Select the Message Severity as 1 or 2 from the drop-down list.
      • Select a pre-defined Message to be displayed from the drop-down list.

        To enter a specific message other than the listed pre-defined messages, select Custom Message, in the Message drop-box and enter the required Custom Message.

  • List of Value - List of Value Check verifies the values where a dimension / master table is not present. This check identifies if the base column data is not matching with any value or code specified in a list of values.
    • Select the check-box to enable the List of Value check.
    • Set the warning level to Severity, Warning or Information.
    • Select Input Values and specify the List of Values. You can specify numeric or String values.
    • Click Edit to add specific filter expressions, as additional conditions.
    • Select the Assignment option. The Assignment option is enabled only if Warning/Information is selected as the Warning level.
      • Select the Assignment Type from the drop-down list. For more information, see Assignment Types.
      • Specify the Assignment Value.
      • Select the Message Severity as 1 or 2 from the drop-down list.
      • Select a pre-defined Message to be displayed from the drop-down list.

        To enter a specific message other than the listed pre-defined messages, select Custom Message, in the Message drop-box and enter the required Custom Message.

  • Referential Integrity Check - Referential Integrity Check identifies all base column data which has not been referenced by the selected column of the referenced table. Here, the reference table and columns are user specified.
    • Select the check-box to enable the Referential Integrity Check.
    • Set the warning level to Severity, Warning or Information. Column reference check can be enabled only if the base column has date or number value.
    • Select the Table (Referential Integrity Check dimension table) from the drop-down list. The base table selected under the Select grid is excluded from the drop-down list.
    • Select the Column from the drop-down list. The list displays those columns that have the same Data Type as that of the Base Column selected under Select grid.
    • Select the Is Composite Key check-box if the base column is part of a Composite Key.
    • Click Edit to add specific filter expressions, as additional conditions.