OCDQ Blog

On Twitter, Henrik Liliendahl Sørensen commented:

“'ZIP Code' and 'Tax ID' are United States (US) specific field labels”

And I responded by asking:

Does that guarantee that the field values will be?

And Henrik tweeted back:

“Absolutely not. P.S.: Even Omikron uses 'ZIP Code' in the English version :-( ”

My final un-tweeted commentary:

Verifying data matches the metadata that describes it is one essential analytical task that data profiling can help us with, providing a much needed reality check for the perceptions and assumptions that we may have about our data.

Thanks for your feedback Ira,

I do not think that anything you have suggested is excessive.

I completely agree with you that data profiling (especially with large data sets) can be very expensive in terms of processing time and therefore you want to get as much out of that investment as you can. In fact, this is the very “grunt work” that data profiling tools automate for you and is one of the primary benefits for using one.

You can assume that all of the results shown throughout the series have been produced by the initial processing of the data source by the data profiling tool.

Some of the additional and extremely useful information that you have described will in fact be included in subsequent parts in the series. However, some of it will not be included only because, as I explained in the overview, I will not be attempting to cover every possible feature of a data profiling tool – mainly because I don’t want the series to be too long – currently I am planning on 5 parts.

Best Regards…

Jim

Thanks for your feedback and questions Vish – both are greatly appreciated.

I plan to post 2 parts per week. However, I might stretch it out a little on a few of the parts because I want to leave enough time between posts to allow for comments. This is one of the primary reasons that I am doing the series via a blog and not a whitepaper or a presentation. I feel that the dialogue and discussion greatly improve the content and allow some of my points to be made for me by those who take the time to comment as opposed to me just telling everyone how I see the world.

All of your questions are excellent. I could answer them all right now but it would defeat the goal of the series.

Remember the scenario – we are pretending to be an external consultant with no prior knowledge of the data or its expected characteristics – obviously this is far easier for you and the rest of my readers since you truly don’t have any knowledge of the data that I am using.

Many of your questions would need to be posed to those writing the business requirements or the subject matter experts for this data source. But again, in the series scenario, we do not have access to any of these resources.

Therefore, what the series is really asking is: What can just our analysis of the data tell us about it?

Data profiling at this stage of the process is often more about formulating a growing list of meaningful questions than about finding answers on our own. I would argue that this is not a waste of our time and in fact, is a necessary aspect of performing comprehensive data analysis.

I have been on many projects where I was told that I should delay data profiling until business requirements and subject matter experts are available.

I always disagree because I think that I can do a better job of evaluating the business requirements and preparing for my meetings with subject matter experts when I have spent some time looking at the data from a starting point of blissful ignorance and curiosity.

So not to be evasive, I can only say that we may be able to answer some of your questions by the end of the series – but we may also not be able to answer some of them.

At the very least, we will have a strong working theory before we engage the rest of team.

Best Regards…

Jim

Thanks for the additional questions Toby and Phil,

Good eye for detail, Toby. I agree that something potentially odd seems to be happening in our State Abbreviation field. An upcoming part in the series will be drilling down to those 72 distinct values and hopefully we will be able to answer all of your great questions. Stay tuned.

Great question Phil – front-end validation (or some other upstream process before data reaches wherever we happen to be in the data’s chain of custody) that replaces invalid values with NULL is a common approach to “cleansing” data.

However, as Bill Inmon recently discussed in his B-eye-Network article Some Perspectives on Quality : “…keeping incorrect data even when it is known to be incorrect often provides valuable clues as to how data needs to be corrected.”

Therefore, even when substituting NULL for invalid values is the business rule that gets implemented, I advocate at least tracking what the invalid value was for both auditing purposes and for sending an upstream request to prevent it from happening or otherwise resolve the issue before it becomes a downstream problem.

However, as Bill Inmon’s article also points out, there are multiple perspectives on what the best approach is and, as with so many other data issues, there is no one “right” answer.

As for the series, within the context of the scenario, we wouldn’t know but I will break my own rules (just a little) and tell you that no pre-cleansing has occurred on any of the fields in our data source.

However, that doesn’t mean that all is necessarily well with the Birth Date field :-)

Best Regards…

Jim