Skip to content
English
  • There are no suggestions because the search field is empty.

Data Quality & Exception Management - Technical implementation

This article covers the automation of the Data Quality rules within Zetaris.

This page details the automation of the Data Quality (DQ) rules within Zetaris. The implementation enables the user to add new DQ rules to objects/tables, retrieve DQ results for the applied rules and produce the DQ exception report in Zetaris. The output of this DQ analysis is shown on the DQ Tableau dashboard, which enables the user to visually track, analyse and display key performance indicator metrics to understand the quality of data on a daily basis. This enables the user to identify the exact records for the objects where an applied DQ rule is failing.

As part of the automation, the user can add new DQ rules or deactivate existing DQ rules by maintaining that information in the Zetaris NDP File System.

Data Quality Pipelines

Zetaris provides a Data Quality pipeline node, SimpleDQ, which automatically calculates results based on different DQ dimensions (for example Uniqueness and Completeness).

The DQ pipelines are grouped in a Data Quality container. To view them, go to Workbench > Pipeline and open the container.

Data Quality pipelines within Zetaris

The pipelines with the suffix _dq are the data quality pipelines created for a specific table, for example crash_dq, customer_dq and nation_dq. Each pipeline applies the DQ rules to one table using the SimpleDQ node, which automatically calculates the DQ results for that table.

To create a DQ pipeline for a table:

1. Open the Data Quality container and click New Pipeline. Enter a name with the suffix _dq and click Create.

2. Drag the source table from Data Explorer onto the canvas.

3. From Node Blocks > Data Quality, drag SimpleDQ onto the canvas and connect the source table to it.

4. Select the SimpleDQ node. On the Single Column Rules tab, select the rules for each column (Uniqueness, Completeness, Size, Contained In, Histogram Values, Distinct Values, Approx Quantile). Use Composite Column Rules for rules that span multiple columns.

An example of a DQ pipeline (crash_dq):

Example of a Data Quality pipeline within Zetaris

It contains the crash object from the NY_VEHICLE_COLLISIONS database along with the SimpleDQ node with its rules applied. To view the result, click Preview node output on the SimpleDQ node. The result of the crash_dq pipeline is as follows:

Result for the crash_dq DQ pipeline

This is how the DQ pipelines are created for each object.

Alldq_Table Pipeline

The Alldq_Table pipeline performs the union of all the DQ pipelines and provides the aggregate DQ result for all of these objects. The result is written by a Sink node to the alldq table.

The result for SELECT * FROM alldq in Workbench > SQL Workspace is as follows:

Example of the DQ result stored in Zetaris

DQ Exception Report - Exception report at the record level for DQ rule failure

The dq_exception_details table provides the DQ exception report for the DQ rules applied to the objects. It enables the user to identify the exact records where a DQ rule is failing. The primary key of each failing record is stored in the spid column. The DQ batch runs daily (scheduled with Schedule on the pipeline) and stores the exception report in this table, which helps the user to identify DQ failures at the record level.

Example of the DQ exception result in Zetaris

DQ Tableau Report - Visual representation of key performance metrics

The dataquality_data_mart contains all objects that are used for the DQ exception reporting in the Tableau dashboard.

This dashboard uses the alldq and dq_exception_details objects (described in the sections above) to represent the data quality results through different visuals. It enables the user to visually track, analyse and display key performance indicator metrics. The report consists of the following dashboards:

Data Profiling Dashboard: This acts as the home page of the DQ Tableau report. Here the user can view the DQ results based on three DQ dimensions (Completeness, Uniqueness and Validity).

Users can view the overall score of each DQ dimension through the doughnut charts. These charts are clickable and navigate to the respective DQ exception report.

Users can view the overall score of each object for these three dimensions.

Users can drill down into the tables and view these scores for their respective columns.

Data Profiling Dashboard (Home Page)

Uniqueness Exception Report: When the user clicks the Uniqueness doughnut chart on the Data Profiling home page, the user is navigated to the Uniqueness Exception Report page. This dashboard shows the following information:

Overall Uniqueness: the overall uniqueness score for the data.

Attribute Count: the record count where the applied DQ rule is failing.

Exception Attribute List: the attributes at record level for each object where the DQ rule failed. This helps the user to find the primary key of the record where the DQ rule failed and fix the issue.

Uniqueness Exception Report

There are two other exception reports, for Completeness and Validity, which present the corresponding exceptions in the same way as the Uniqueness Exception Report.

Automation of DQ rules - Easy way of adding/deleting the DQ rules in Zetaris

The Data Quality implementation allows the user to add or deactivate DQ rules by maintaining that information in the Business_Rules.csv file in the Zetaris NDP File System. The DQ automation then applies those rules when calculating the DQ results.

Addition of new DQ Rules

The DQ implementation allows the user to add new rules to the objects/tables. To do this, add the rules to the Business_Rules.csv file and store it in the NDP File System at the following path:

/file-store/DQ_EXTERNAL_FILES

1. Go to NDP File System and open file-store > DQ_EXTERNAL_FILES.

2. Click Upload, select the updated Business_Rules.csv and click Upload to confirm.

Uploading Business_Rules.csv to the NDP File System

Business_Rules.csv within the NDP File System

Once the rules are added to Business_Rules.csv, they automatically become part of the DQ analysis from the next day, as the DQ batch runs nightly. A newly added DQ rule must have Y as the value of the rule_active column.

An example of adding/deleting DQ rules in the Business_Rules.csv file:

Example showing the addition/deletion of DQ rules in the Business_Rules.csv file

Deleting/Inactivating an existing DQ Rule

To deactivate an existing DQ rule, add that DQ rule with I as the value of the rule_active column. An example of deactivating an existing rule is shown in the image above.