Example: Data Profiles in Microsoft SQL Server
In Microsoft SQL Server 2008 Integration Services the Data Profile Task offers the following profiles:

Basic statistics link min, max etc.
Empty columns.
Values and their usage counts.
Lengths of the values.
Patterns in the values.
Potential candidate keys for a primary key.
Functional dependancies within a table (= "hierarchy detector").
Foreign key relationships detection.

Data Quality Measures

Up until 3-4 years ago there were no software tools besides SQL commands available to help analysts understand the data quality issues at hand in a given database, for example. That was cumbersome and left the job only half (or less) done.

Data Profiling Tools

Today there are fortunately a host of tools available to help you dig into the data and discover their problems, easily. These products are in the category called Data Profiling tools. The vendor list includes the database vendors, the ETL-vendors, specialised companies and even the data modelling people are now also onboard.

Generally there are 4 levels of functionality.

Basic functions

This includes all the generic measures and controls, which can be applied to any source of data. It is simple stuff like column lengths, empty values, values used, data types, patterns (eg. phone numbers etc.), functional dependancies between attributes and possibly also relationships between tables. See for example the list of controls in the Microsoft offering in SQL Server 2008 SSIS, Data Profile Task, in the sidebar to the right.
Some offer "guided analysis" (meaning highlighting potential problems in red color and so forth for you to see), but certainly not all.

Business rules

Validation of data against specified business rules is found in some of the products. Certainly it can be very useful, but the basic functionality illustrated to the right will by itself take you far.


A few of the products offer collaboration facilities. This means that a group of people can work together towards a shared data profile repository. They can make notes, the notes as well as information about who has inspected what and when is available for all to see. May also include ToDo lists, assignment functions etc. Great stuff for very large environments.

Automation and trend analysis

A select few of the products offer the possibility of automating suites of data profiling tasks with the purpose of rerunning them regularly, eg. once a month or so. They offer not only that but also are able to automatically compare the results of previous run with the latest run and tell you the differences.
The pricing is very different across this market segment.

The recommandation

"Do not leave home without a data profiling tool"! Seriously: You should use them:
  • Early on in the analysis stages in order to get an overview of data quality challenges - before you size and scope the implementation project!
  • Repeatedly on a regular basis in order to verify that the organisation is indeed improving its quality of the data asset.
Start today! You will be amazed with what you find!
For inspiration, here is a sample screenshot from the Microsoft offering: Microsoft Column Length Profile.