When analyzing individual column data, at its most foundational level, column data can be classified by their fundamental use/characteristics. Granted, when you start rolling up the structure into multiple columns, table structure and table relationship, then other classifications/behaviors, such as keys (primary and foreign), indexes, and distribution come into play. However, many times when working with existing data sets it is essential to understand the nature the existing data to begin the modeling and information governance process.
Column Data Classification
Generally, individual columns can be classified into the classifications:
Identifier — A column/field which is unique to a row and/or can identify related data (e.g., Person ID, National identifier, ). Basically, think primary key and/or foreign key.
Indicator — A column/field, often called a Flag, that has a binary condition (e.g., True or False, Yes or No, Female or Male, Active or Inactive). Frequently used to identify compliance with complex with a specific business rule.
Code — A column/field that has a distinct and defined set of values, often abbreviated (e.g., State Code, Currency Code)
Temporal — A column/field that contains some type date, timestamp, time, interval, or numeric duration data
Quantity — A column/field that contains a numeric value (decimals, integers, etc.) and is not classified as an Identifier or Code (e.g., Price, Amount, Asset Value, Count)
Text — A column/field that contains alphanumeric values, possibly long text, and is not classified as an Identifier or Code (e.g., Name, Address, Long Description, Short Description)
Large Object (LOB)– A column/field that contains data traditional long text fields or binary data like graphics. The large objects can be broadly classified as Character Large Objects (CLOBs), Binary Large Objects (BLOBs), and Double-Byte Character Large Object (DBCLOB or NCLOB).
A Common Data Model (CDM) is a share data structure designed to provide well-formed and standardized data structures within an industry (e.g. medical, Insurance, etc.) or business channel (e.g. Human resource management, Asset Management, etc.), which can be applied to provide organizations a consistent unified view of business information. These common models can be leveraged as accelerators by organizations form the foundation for their information, including SOA interchanges, Mashup, data vitalization, Enterprise Data Model (EDM), business intelligence (BI), and/or to standardize their data models to improve meta data management and data integration practices.
Peer code review happens during the development phase and focuses on the overall quality and compliance to standards of code and configuration artifacts. However, the hard part of performing a Peer code review isn’t, performing the review, but rather to achieving consistency and thoroughness in the review. This is where a checklist can contribute significantly, providing a list of things to check and providing a relative weight for the findings. I hope this template assists with your DataStage job review process.
ETL (extract, transform, and load) error handling practices can vary, but three basic approaches can significantly assist in having effective ETL error handling practices. Effective error handling practices begin in the requirements and design phases. All too often, error handling practices are left to the build phase and the fall to the developer practices. This is an area where standard practices are not well defined or adopted by the ETL developer community. So, here are a few effective error handling practices which will contribute to process stability, information timeliness, information accuracy, and reduce the level of effort required to support the application once in operation.
Anticipating ETL Errors
In the requirements and design phases, with proper consideration, many errors can be avoided altogether in the ETL process. When discussing requirements and preparing designs consideration should be given to error handling, especially, treatment of common errors. As an effective practice, anticipated errors should be treated within the ETL process. Some examples, to consider are:
Replacement of special characters: do any special characters need to be removed if found? This is generally determined that the field level and should be considered in the source to target mapping (STTM) and business rules. Also, the passing between different systems and working with VARCHAR fields, should the ‘Unicode’ extended property be set.
Removal of leading and trailing spaces: removal of unnecessary leading and trailing spaces should be considered when changing fields from CHAR to VARCHAR and when working with keys used as primary keys, join keys, and/or lookup keys.
Deduplication of data: duplicate data prevention practices and business rules should always be considered. These can be of a couple of types:
First, is file processing conventions, such as assigning timestamps to files and removal or movement of process files to prevent reprocessing.
Second, is rules for the identification of duplicate rows, including the appropriate keys for determining duplicate rows.
Third, if duplicate rows are being produced as a result of more than one input source system, identification of the authoritative source should be considered to resolve conflicts.
Null Value Treatment: null value treatment can be extraordinarily important, especially, when working with keys and traditional data warehouse models. It is important to be mindful of the fact that to the database and the ETL nulls and spaces are not the same thing. They may or may not be the same thing in the mind of the consumer of the information. So, business rules should indicate the treatment of both spaces and nulls. In some circumstances, especially, when using surrogate keys in data warehousing business processes sometimes need to know the difference between a null in space or even a no and space and an unknown value. So these three scenarios should be considered when forming business rules and treating the ETL. Here are a couple questions that could be asked informing your solution:
do nulls and spaces mean the same thing to the business community?
Is space considered an unknown value?
Does a null need to be uniquely identified as different from space and/or an unknown lookup value?
If surrogate keys are in use for the field in question, which of these scenarios require a unique surrogate key, other than the unknown unique surrogate key?
Missing or Invalid Value Replacement or Defaults: having replacement values or defaults is especially important for any fields which are not nullable and/or require a surrogate key for data warehouse dimensions. Also, for reporting to be meaningful replacement or default value assignments can be important, as well (e.g. for cubes, and statistical calculations).
Rows should not be rejected unless there is a specific business requirement and/or need to do so. Rejecting rows causes data inaccuracies by omission and undermines the consumer’s confidence in the accuracy of the information being delivered. This can be, especially, problematic for accounting and other activities, which must balance across information sets.
If value lookups are in use:
Unknown and null values need to have a treatment rule to prevent errors.
Two surrogate key or transformation default values may be necessary if the ability to distinguish between an unknown/Invalid value and a null value is required.
Make sure the lookup ‘Key Type’ are aligned (e.g. equality, caseless equality) to the formatting of both inputs to the lookup
That the complete unique key is in use.
Information Consistency Practices
Information consistency practices allow the information to be transformed and enriched to make the information more consistent for ‘like to like’ comparisons, usability, and/or readability. As an effective practice consider these Standard formatting recommendations, which can be good requirements questions and should be included in the STTM:
Making descriptive and/or text fields consistent in their format (e.g. mixed case, Proper case, upper case).
Have use consistent date formatting, when converting dates to text fields.
When dealing with currency, convert the currency to consistent ISO currency codes (e.g. USD, CAD, EUR) and decimal (e.g. two decimal places).
Identification of financial records into categories (e.g. credit and debit) with a default group behavior included (e.g. N/A or Unknown).