Data Cleansing¶

Implementing data cleansing rules and monitoring overall data quality.

In this module: data validation rules · data selection rules · data transformation · monitoring and continuous improvement

What is data cleansing, and why does it matter?¶

Data cleansing is identifying, correcting or removing inaccurate, incomplete or irrelevant data. It is the step that makes data used in analytics and reporting trustworthy. High-quality data unlocks accurate insight, supports regulatory compliance and reduces the risk of costly business errors.

Where it fits in the workflow:

Data validation¶

Validation checks each piece of data against your organization's standards: unique primary keys, referential integrity between related tables, and custom field-level rules.

• Primary key validation removes or flags duplicates, empty keys and missing identifiers, preventing analytical errors and keeping every record unique and traceable.
• Referential integrity ensures relationships between tables are correct — every sales record must match an existing customer. Invalid records are flagged or removed and can be logged. Physically enforced relations also apply those rules in downstream databases and tools.
• Validation rules let you define criteria such as Is Not Null, Min Length or In List. Violations are flagged as an error or a warning, so only business-ready data moves forward.

Strong validation stops bad data spreading across systems, reducing rework and letting stakeholders rely on the output for operational and regulatory needs.

Primary key validation¶

Timextender can check or remove duplicate and empty primary keys, and show the results. By default, primary key behaviour is set to error at instance level, which removes any record with a null or duplicate primary key.

Instance level — affects every table in the instance:

1. Right-click the MDW instance → Edit Instance.
2. Under Primary key violation, select the option you want.

Table level — overrides the instance setting for one table:

1. Right-click the table → Table Settings.
2. On the General tab, select the primary key behaviour.

Reference: Primary Keys · Set up Primary Key Rules

Referential integrity¶

Timextender can use defined relations to perform a referential integrity — foreign key constraint — check. During execution it compares primary and foreign key values to confirm a match exists. If not, the record is invalid. Four options control the handling:

Instance level: right-click the MDW instance → Edit Instance → under Relation, select the option.

Table level: at the bottom of a table's field list, find the relation, right-click it → Relationship Type, and select the type for your use case.

Reference: Table relations

Validation rules¶

Validation rules discover invalid data and keep accuracy and reliability high. They apply at field level, and can be made conditional so they only apply in specific situations.

Supported operators:

For every rule you must also classify the severity of a violation:

• Error — critical to data quality and requires immediate attention. The data is considered invalid and is not made available to end users.
• Warning — not critical and does not require immediate attention. The data is still considered valid and remains available.

Reference: Data Validation Rules · Data Selection Rules · Batch Data Cleansing

Next in the course¶

Optimization · Table Transformations