Keeping Data Clean Across Multiple Systems

Why data gets messy across systems

A single system usually has clean data. It enforces its own formats, validates inputs, and prevents duplicates. The problems start when data flows between systems.

Each system has different validation rules, different format preferences, and different definitions of what constitutes a "complete" record. An address that's valid in your CRM might be incomplete in your shipping system. A customer name that works in your accounting software might have characters that break your email platform.

Multiply this by five or ten connected systems and data quality degrades fast.

Establishing a source of truth

The most important decision you can make: for every piece of data, designate one system as the master.

• Customer contact details: CRM is the master
• Product pricing: ERP or commerce platform is the master
• Financial transactions: Accounting system is the master
• Inventory levels: Warehouse management system is the master

This doesn't mean other systems can't store the data, it means they get it from the master and don't override it. If a customer updates their address, it happens in the CRM and syncs to other systems. Not the other way around.

Data standards that stick

Data standards are only useful if people follow them. Keep them simple and enforce them at the point of entry.

• Names: Capitalised (John Smith, not JOHN SMITH or john smith)
• Phone numbers: Include country code, no spaces or dashes (stored as +61412345678)
• Addresses: Use a validated address fields (not free-text), or an address autocomplete service
• Dates: ISO 8601 (YYYY-MM-DD) in databases, localised only in displays
• Currency: Always stored in cents/minor units as integers, formatted for display only

Automated quality checks

Don't rely on manual data audits. Build automated checks that run continuously:

• Duplicate detection: Scheduled checks comparing key fields (email, ABN, phone) across related records
• Completeness checks: Required fields that are empty or placeholder values
• Format validation: Phone numbers with wrong digit counts, emails without domains, postcodes that don't match states
• Cross-system consistency: Compare record counts and key field values between master and connected systems
• Staleness detection: Records that haven't been updated in an unreasonable timeframe

Practical data governance

Data governance doesn't need a committee and a 50-page policy document. It needs:

Ongoing maintenance

Data quality isn't something you fix once. It requires ongoing attention:

1. Weekly: Review automated quality check reports. Fix issues before they compound.
2. Monthly: Check cross-system consistency. Are record counts matching? Are sync jobs running on time?
3. Quarterly: Review data standards. Are new data elements being created without governance? Are integration changes introducing new quality issues?
4. Annually: Full data audit. Purge stale records, review archive policies, assess whether source-of-truth designations still make sense.