Lesson 2 of 5
Cleansing rules and owners
9 min
A profiling finding becomes useful when someone decides what to do about it. That decision is a cleansing rule: a written instruction that says which records are affected, what changes, where the change is made and who approved it. Cleansing rules turn opinions ("the customer data is a mess") into work that can be planned, done, tested and signed.
Kinds of cleansing rule
| Rule type | What it does | Contoso example |
|---|---|---|
| Standardize | Bring values into one format | Mexican postal codes to five digits, restoring lost leading zeros; names in agreed capitalization |
| Complete or enrich | Fill a missing value from a trusted source | Missing postal codes collected by the sales team for the 57 customers |
| Correct | Replace a wrong value | Region codes corrected to match the postal address for 31 customers |
| Merge | Combine duplicates into one surviving record | Three Flotillas Monterrey records become one business partner |
| Exclude | Keep records out of scope | Customers inactive for 24 months with no open items are not migrated |
| Flag or block | Migrate with a marker that limits use | Suppliers with unverified bank details migrated with a payment block |
Common cleansing rule types with a Contoso example each.
Where to cleanse
There are three places to change data, and they are not equal. Cleansing at source fixes the old system, so every later extract is already clean and the source agrees with the target for reconciliation. Cleansing in transformation changes data on its way into staging, which is fast and repeatable but means the source and target now differ on purpose, and every difference must be explained. Cleansing in the target after the load is a last resort for small, late findings, done under an agreed procedure.
| Where | Strengths | Watch out for | Good fit |
|---|---|---|---|
| At source (old system) | Clean in every extract; reconciliation stays simple; business sees the fix | Needs business time; the live system keeps creating new bad records | Missing and wrong values the business must supply |
| In transformation | Automated, repeatable, no change to the live system | Source and target differ by design; rules need testing and documentation | Format standardization, code conversions, merges |
| In target after load | Handles a few late findings | Manual, hard to evidence, can miss the cutover window | A handful of records under an approved procedure |
Owners, stewards and builders
A cleansing rule needs three kinds of people. The data owner is accountable for the business object and approves the rule: Maria for customer sales data, Dan for financial data such as payment terms and reconciliation accounts. A data steward does the cleansing work in the source. The migration specialist builds rules that run in transformation. The tester proves that each rule did what it says. Developers and testers should never decide on their own which of three duplicate customers survives, because that decision changes who gets invoiced and dunned.
Merges need survivorship
Merging duplicates is the most delicate rule because it changes identity. Write down survivorship: which record survives (for example the one with the most recent sales activity), which attributes come from which record (address from the one confirmed by the customer, bank details from the one used for the last payment), and what happens to everything that pointed at the losing records: open items, open orders, contracts and price conditions must be re-pointed to the survivor. Keep a cross-reference of old to new numbers so that reconciliation and users can trace history. Where SAP Master Data Governance consolidation is available, its matching and best-record calculation can support this, but the survivorship rules are still a business decision.
- FindingFrom the profiling log, with counts and examples
- Rule draftedType, scope, action, where applied
- Owner approvesVersion and date recorded
- AppliedSteward cleanses at source or specialist builds the transformation
- Re-profiledNext extract measured with the same scripts
- ClosedClosed only when the next mock confirms the effect
What would you do?
Two weeks before Wave 2 Mock 2, Maria says the 57 customers without postal codes can be fixed "in SAP S/4HANA after go-live, when we have time". The business partner load in Mock 1 rejected those records because address checks need a postal code. What should the migration specialist propose?
- A cleansing rule states affected records, action, place of change and approving owner.
- Prefer cleansing at source; use transformation for mechanical rules; keep target fixes for a few late records.
- Prevent new bad records in the live source, or every mock brings the problem back.
- Merges need written survivorship rules and re-pointing of dependent records.
Check your understanding · not graded
Lesson check
1. Who approves a rule that merges duplicate customers?
2. What is the main drawback of cleansing in transformation rather than at source?
3. Why does cleansing at source need a prevention step?
4. A merge rule should define which of the following?