Data Quality Part – I

Since the blog is named data integrity, I figured I would look up what “data integrity” truly meant.  In searching for the definition on google, data integrity came to be defined as ‘data that is complete or whole. All characteristics of the data including business rules, rules for how pieces of data relate, dates, definitions and lineage must be correct for data to be complete’.  With that in mind, I am going to start a number of posts on Data Quality and Data Profiling over the coming weeks. 

Today, I am going to focus on a simple, and rarely used method of profiling data.  The create rule and bindrule in SQL can be used to specifies acceptable values that can be inserted/updated into that column.  Below, I have an example of a rule created that will only allow values between 1 and 10.  After the rule has been created, I’ll specifiy the table.field that the rule will be applied to, and run a few tests.

—————————————————

CREATE RULE range_rule

AS

@range>= 1 AND @range <11

CREATE TABLE dbo.TEST

(RULEFIELD varchar(50),

NAME varchar(50))

EXEC sp_bindrule ‘range_rule’, ‘TEST.RULEFIELD’

INSERT INTO dbo.TEST VALUES (‘1’, ‘ONE’)      –OK

INSERT INTO dbo.TEST VALUES (’11’, ‘ELEVEN’–ERROR

UPDATE TEST SET RULEFIELD = ’11’ WHERE RULEFIELD = ‘1’ –ERROR

UPDATE TEST SET RULEFIELD = ‘2’ WHERE RULEFIELD = ‘1’ –OK

—————————————————

 

Advertisements

Tags: , ,

One Response to “Data Quality Part – I”

  1. Data Quality Part II « data integrity Says:

    […] Quality Part II By dataintegrity In Part I of this series, I showed how to create rules around data in a table. This post will expand on the […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: