Every data warehouse, ETL architecture can benefit from an easy to produce data profiling of source data to determine data quality issues already present before the ETL processing begins or even as a periodic data quality assessment of systems of record.
What's in the box?
The Data Profiling Tool Kit contains the basic data structures to capture table and column-specific summary data profiling information. Included are: DDL to generate a data profiling metadata control table and the data profiling table/column summary repository that will contain basic column-specific metrics such as: table name, table comment, total number of rows in the table, column: name, comment, defined data type, defined data length, defined data precision, allows nulls?, number of null values, number of distinct/unique values found, column density, min/max/avg actual data value length, min/max date value, min/max nmeric values, min/max alphanumeric value, count of numeric values, count of date values, count of alphanumeric values, count of non-printable values, and that's the BASIC data profiling. More will be forthcoming, so stay tuned sports fans!
|
For SQL Server:
CREATE TABLE dbo.PROFILE_META
(
PROFILE_META_KEY int IDENTITY(1,1)
, PROFILE_DATE datetime NOT NULL CONSTRAINT DF_ETL_META_COL_SUM_CDTE DEFAULT (GETDATE())
, PROFILE_BY varchar(128) NOT NULL CONSTRAINT DF_ETL_META_COL_SUM_CBY DEFAULT (USER)
, PROFILE_VERSION decimal(10, 5) NOT NULL
, PROFILE_REASON varchar(255) NOT NULL
, PROFILE_DESCRIPTION varchar(2048) NOT NULL
);
ALTER TABLE dbo.PROFILE_META
ADD CONSTRAINT PKProfile_Meta
PRIMARY KEY CLUSTERED (PROFILE_META_KEY);
CREATE TABLE dbo.PROFILE_COL_SUM
(
PROFILE_COL_SUM_KEY INT IDENTITY(1,1)
, TNAME varchar (255) NOT NULL
, CNAME varchar (255) NOT NULL
, DATA_TYPE varchar (50) NOT NULL
, DATA_LENGTH int NULL
, DATA_PRECISION int NULL
, DATA_SCALE int NULL
, NULLABLE varchar (3) NULL
, NUM_TABLE_ROWS decimal(18, 0) NULL
, NUM_NULLS decimal(18, 0) NULL
, NUM_DISTINCT_VALUES decimal(18, 0) NULL
, DENSITY decimal(18, 5) NULL
, MIN_DATA_LENGTH int NULL
, MAX_DATA_LENGTH int NULL
, AVG_DATA_LENGTH int NULL
, MIN_DATE_VALUE datetime NULL
, MAX_DATE_VALUE datetime NULL
, MIN_NUMERIC_VALUE decimal(18, 5) NULL
, MAX_NUMERIC_VALUE decimal(18, 5) NULL
, MIN_ALPHANUM_VALUE varchar (2048) NULL
, MAX_ALPHANUM_VALUE varchar (2048) NULL
, NUMERIC_COUNT decimal(18) NULL
, DATE_COUNT decimal(18) NULL
, ALPHANUM_COUNT decimal(18) NULL
, NOPRINT_COUNT decimal(18) NULL
, TABLE_COMMENT varchar (512) NULL
, COLUMN_COMMENT varchar (512) NULL
, PROFILE_META_KEY int NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE dbo.PROFILE_COL_SUM
ADD CONSTRAINT PKProfile_Col_Sum
PRIMARY KEY CLUSTERED (PROFILE_COL_SUM_KEY)
GO
For Oracle:
CREATE TABLE DATA_PROFILE_COL_SUM
(
TNAME VARCHAR2(50 BYTE),
CNAME VARCHAR2(50 BYTE),
DATA_TYPE VARCHAR2(50 BYTE),
DATA_LENGTH INTEGER,
DATA_PRECISION INTEGER,
DATA_SCALE INTEGER,
NULLABLE CHAR(1 BYTE),
NUM_DISTINCT NUMBER(20),
DENSITY NUMBER(20,8),
NUM_NULLS NUMBER(20),
COL_LENGTH INTEGER,
AVG_COL_LENGTH NUMBER(20,2),
CHAR_LENGTH INTEGER,
TABLE_COMMENTS VARCHAR2(2048 BYTE),
COLUMN_COMMENTS VARCHAR2(2048 BYTE),
RCOUNT NUMBER(20),
MIN_DATE DATE,
MAX_DATE DATE,
MIN_NUMBER NUMBER(30,10),
MAX_NUMBER NUMBER(30,10),
MIN_INTEGER INTEGER,
MAX_INTEGER INTEGER,
MIN_VARCHAR2 VARCHAR2(2048 BYTE),
MAX_VARCHAR2 VARCHAR2(2048 BYTE),
MIN_LEN INTEGER,
MAX_LEN INTEGER,
LAST_UPDATED DATE DEFAULT SYSDATE
);
create table DATA_PROFILE_COL_DTL
(
tname varchar2(255) NOT NULL
, cname varchar2(255) NOT NULL
, freq_rank integer NOT NULL
, date_column_value date
, integer_column_value integer
, number_column_value number
, varchar2_column_value varchar2(2048)
, occurs integer
, total_rows decimal(18,0)
, occurs_percent decimal(18,5)
, capture_date date default sysdate
);
|
