Using Data Sync as a Data Quality Tool, Not Just a Sync Tool
Most teams use Data Sync to move data from A to B. But the same comparison engine that drives synchronisation can identify problems, flag anomalies, and clean up bad data before it causes downstream damage.
When most people think about Data Sync, they think about moving data. Connect a source, connect a target, map the fields, run the sync. That's what it says on the tin and it's what most customers use it for.
But the engine underneath that workflow is doing something more interesting than just copying records. It's comparing them field by field, row by row, and identifying exactly what has changed, what's missing, and what doesn't match. That comparison works the same whether you're syncing data to a new system or running it against itself to find problems.
With the right setup, Data Sync becomes a data quality layer: something you run before a migration, after an import, on a schedule against a live system, or as a one-off investigation into why downstream reports are producing unexpected results.
The Core Idea: Source and Target Don't Have to Be Different Systems
The most important thing to understand about using Data Sync for data quality is that the source and the target can be the same system, or even the same table.
When you point both the source and target at the same data, you're not moving anything. Instead, you're using calculated columns and transformations to describe what the data should look like, and letting the comparison engine show you the difference between the current state and the desired state. You can also preview every change before applying anything.
This is the foundation for cleaning, validating, and repairing data without writing SQL update scripts or risking bulk changes you can't easily undo.
One reassuring detail worth knowing: Delete is disabled by default in Data Sync. Even if your comparison identifies records that would be removed, nothing is deleted unless you explicitly enable it. For data quality work against live systems it's a useful safety net.
Previewing Changes Before Committing
One of the most underused aspects of Data Sync for data quality work is the compare view. Before any sync runs, Data Sync shows you exactly what would change: which records would be updated, which fields would be modified, and what the before and after values look like. Changed values are highlighted in yellow so you can see at a glance what will be affected.
You can set up a cleaning or validation project, run the comparison, and review the results without touching the underlying data. If something looks wrong, maybe a transformation is producing unexpected results for certain records, you can catch it before it's applied. You can also uncheck individual rows or clear entire change sets and select specific rows before committing to the sync.
You can also export the compare results directly to Excel or XML. For data quality work, this means you can produce a clean report of every record that needs attention: whether that's records with missing fields, formatting issues, or anomalous values, and hand it to whoever needs to act on it without them needing to open Data Sync at all.
This is something SQL update scripts don't give you easily. Running UPDATE statements against a production database requires you to either trust the query completely or write a separate SELECT first and manually compare. Data Sync makes the preview the default step, not an afterthought.
If the data transformation will run regularly, running it inside Ouvvi can give you the changeset history (when enabled), and version history of the project configuration. So you can always look back and see the changes that happened on each run.
Removing Duplicate Records
Duplicate data is one of the most common data quality problems, and one of the hardest to clean up manually at scale. By default, if there are duplicates in your dataset, Data Sync will return the first instance and exclude the rest from the sync. However if you need to manage duplicates, Data Sync has a dedicated Duplicate Removal connector specifically for this.
You point the connector at an existing Data Sync project that defines your source data, and choose one of two removal modes: All removes every instance of a duplicate, or AllButFirst removes all but the first occurrence, keeping one clean record.
If you want to review all duplicates, you would run the connector in All mode and export the delete results from the comparison to a file for review. That gives you a list of only the duplicates, rather than having to work through the full dataset. From there you can pick which records you want to keep, build that into a fresh list, and sync it back as your clean dataset. This way you're only ever looking at the problem records.
This makes deduplication a repeatable, traceable process. You can run it before a migration to clean up years of accumulated duplicates or just run it periodically.
Identifying and Fixing Formatting Problems
A common starting point for data quality work is formatting inconsistencies. Phone numbers stored with dashes, spaces, with and without country codes. Email addresses with trailing whitespace. Names in inconsistent case. Postcodes mixed between formats.
Data Sync's calculated columns let you define what the cleaned version of each field should look like, using expressions that run as compiled .NET code, which means they're fast even across large datasets. Strip dashes from a phone number, trim whitespace from an email address, standardise a postcode format. These are straightforward expressions that produce a clean value in a new column. The comparison then shows you every record that would change, and you can apply the fixes in bulk or review them individually before committing.
We've covered specific examples of this in earlier posts, including formatting telephone numbers, validating email addresses, validating domain names, and using regular expressions. The broader point is that the same approach applies to any field where you can define what "correct" looks like as an expression.
Detecting Anomalies Before They Cause Problems
Beyond format validation, Data Sync is useful for detecting records that are technically valid but logically wrong, the kind of problems that pass any basic format check but produce bad results downstream.
Some examples: a date field where the value is in the future but shouldn't be. A price field where the value is zero or negative. A status field that contains a value not in the expected set. A record where related fields are inconsistent, such as a contact marked as active but linked to an account marked as closed.
The way this works in Data Sync is through calculated columns. Rather than modifying existing data directly, you add a new column that evaluates each record and returns a value based on your rule, for example an IF statement checking whether a date field is greater than today, returning true or false.
There are a few ways to approach this depending on how comfortable you are with coding. If you want to keep things simple, you can write the full logic in a single calculated column: the IF statement both checks the condition and sets the correct value in one go, so only the rows that need changing are affected. If you'd rather break it down into smaller steps, you can stack calculated columns: one column flags the problem, and a second references that flag to set the correct value for affected rows only, leaving clean records untouched. For those comfortable with C#, dynamic columns give you the most flexibility and let you handle more complex logic entirely in code.
For relationship inconsistencies like the active/inactive account example, a lookup into the account table or system lets you pull the current account status and map it to the contact record directly. The lookup returns the right value and the sync keeps both sides in step, without needing to flag and fix as two separate steps.
From there you can export the results for review before committing, or run the sync to apply the corrections in bulk.
Using Data Sync as a Pre-Migration Health Check
Migrations are one of the highest-risk moments for data quality problems. Data that has accumulated inconsistencies over years gets moved into a new system that has stricter validation, different field lengths, or mandatory fields that didn't exist before.
The general approach is to set up a Data Sync project per table you're migrating. Rather than just mapping the columns across, you use the project to do the quality work at the same time: add calculated columns to check your rules, use lookups to pull in related data from other tables, transform values into the format the target system expects, and include only the columns you actually need. One project, one table, doing the checking and the shaping in the same pass.
For the target, a SQL staging table is a solid choice as it gives you something you can query, filter, and update as issues get fixed. Once there is data in the table, the comparison will show you adds, updates and deletes as your source data changes. If you don't want to set up a staging database, Ouvvi Apps work in the same way and act as a lightweight staging target without needing a separate SQL environment. Exporting to Excel or CSV also works well if you just need a snapshot report to share with a team, and bear in mind if you use a null/ empty target it will only show adds, since there's nothing to compare against for updates and deletes.
Once the project is clean and the comparison shows no unexpected changes, you can have confidence the data is ready. At that point the same project, or a version of it, becomes the migration itself with the target set to the final system.
If you're migrating multiple systems or running the same checks across several similar datasets, Ouvvi is worth bringing in to orchestrate the runs. For a genuinely one-off migration where each project is different, running them manually in Data Sync is usually fine.
We covered the broader risks in Top Ten System Migration Traps. Many of those traps are exactly the kind of data quality issues this kind of project would catch before they become a problem.
Making Data Quality an Ongoing Process
Running a data quality check once before a migration is useful. Running it on a schedule against a live system, with results written to a report or a monitoring table, is considerably more valuable.
With Ouvvi, a Data Sync quality check project can run automatically on a schedule or triggered by a data change. There are a few ways to handle notification and reporting depending on how you've set things up.
The simplest is the Ouvvi Project Status Report step, which sends an email when a project completes. You can set the run condition to fire on any result, on success only, on success with data changes, or on failure only. If your quality check project is structured so it fails when it finds problems, setting the condition to Failure means you only hear about it when something needs attention.
For a more detailed report, the Report to Email step can pull a filtered view from an Ouvvi App and send it as an HTML email. So if your quality check results are being written to an Ouvvi App, you can use the API filtering to return only the records with specific flags and email that directly to whoever needs to act on it. The Ouvvi Apps API docs covers how filtering works, and the Report to Email documentation covers the step setup.
For systems where data quality matters, such as CRM, ERP, or HR, catching problems early means they don't have time to spread into reports, integrations, or customer-facing processes.
Where to Start
If you're looking at a data quality problem and wondering whether Data Sync can help, the answer is usually yes, as long as you can describe what "correct" looks like for each field. That's really the only requirement. If you can define the rule, Data Sync can check it and fix the results.
A good starting point is the update, clean and repair data post, which shows the basic pattern of pointing source and target at the same system and using calculated columns to define the cleaned result. For more information on the duplicate connector the Duplicate Removal connector documentation covers how to set it up.
If you're working on something more complex, such as a migration health check, ongoing anomaly detection, or a multi-system consistency check, reach out to us and we're happy to talk through the approach.