To my surprise sometime while I was buried in SQL cleaning data, configuring unique record constraints, primary keys, clustered indexes, and referential integrity Microsoft built Master Data Services and Data Quality Services.

Master Data Warehouse: When Microsoft SQL Server 2008 Enterprises was released it came with a new product named "Master Data Services". From my perspective Master data services is basically a user interface to procedures performing many of the tasks I would handle as a DBA\Developer. It provides an area to bring all your data together into one spot and the means to easily add linking data from different sources, add missing look-up data needed for reporting, track all changes to the data to support historical reporting and more all with a nice web interface.

Data Anomaly Detection: Before Microsoft SQL Server 2012 Enterprise data anomaly detection and cleanup was done by someone like myself writing a lot of T-SQL to find and fix data quality issues so that databases could eventually be configured with the four mandatory table design rules. While these rules prevent numerous data issues they don't address data cleanup issues such as missing or inaccurate loan dates or someone with a drivers licenses with their age specified as 3 years old. Contracts associated with the mortgage and loan collection industries have a myriad of start dates, cutoff dates, interest modification that can only occur after predefined periods of time and basically a multitude of dependencies that are quite easy to make mistakes with. In comes Data Quality Services to save the day! A DBA is still needed to configure the table schema correctly however with some training much of the data cleanup can be handled by SMEs(Subject matter experts) Now SMEs can be data stewards for their own data.

I've posted a few youtube videos below which explain both new products in detail but this one "How to Integrate DQS, MDS and Your Data Warehouse" provides a great big picture of how they all work together.

The best place to start with DQS and MDS appears to be the EIM Tutorial.docx

Enterprise Information Management Tutorials - download

Enterprise Information Management Tutorials

Then download and take a look at the SSIS package Cleanse and Curate Master Data.

It needs more documentation but looks like the automated way to cleanse large amounts of data.