DataWashroom - Database Clean up Engine

Project Description
Without washrooms today, imagine what would human society be like? In the era of the proliferation of information, data is also need to be localized excreta, control defecation.
The DataWashroom provides a filter core of the data-purification system, includes the common functions of data isolation and waste separation by a simplicity instruction set (7 instructions). The instruction engine handles pre-compiling, building, publishing (deploying), version control and documentation-generation. (It’s compiling, not interpreting) The input of the compiler is some metadata in wash-instruction tables, the output is a stored procedure.

For more intentions about this project, please see also the [Data Quality] section on the related project http://view.codeplex.com wiki page.

Instruction Set
There are 7 build-in wash instructions shown as following:

• The basic manipulation instructions mainly perform data isolation from source systems;
• The relationship clean-up instructions perform the waste separation.

Clean-up is not just about deleting those no longer needed historical data or garbage data. The clean-up here emphasizes particularly on treatment for bad data. In order to simplify the generalized common solution from untold symptomatic treatments for bad data, the design thinking of above build-in wash instructions is based on following conventions:

• Face duplicate keys, none of them should be deleted physically. Instead, additional tags will be taken part in the discriminator to uniquely identify a business key. For example,
  
  

The duplicate key treatment is subdivided into two alternative methods:
 - Check Unique Key (CHK_UK)
   Only adopt qualified rows, exclude all duplicate rows.
 - Sort Duplicate Key (RNK_DK)
   Assign a sequential number of every row within each partition of supposed unique key by a specified ordering rule. So that the first (top 1) row of each supposed unique key can be picked out directly.

• Face missing keys, all of supposed foreign keys should be made up in the primary table or bridge table if they were missing before, to avoid missing relationship. So that all subsequent users can consume the data straightforwardly and much more efficiently. For example,
  
  

Wash Cycle
All 7 wash-instructions inherit from the base WASH_ISTR. They are organized under the wash cycle in sequence.

Wash Instructions

7 derived wash instruction tables:

• The main practices of following 3 instructions of basic manipulation is to refresh data from source systems to the isolation system, the refresh may include some basic corrections, tagging, null-reference transformation, etc. those rules base on the columns of the the same row or constants, and pivot transformation ... by the view on source data. The refresh can be full refresh, partial refresh or incremental refresh.

• Following 2 instructions of duplicate keys treatment are aimed mostly at the situation of source system does not define proper primary keys or unique constraints, some biased designs don't even allow business key constraints to sit in the table.

• The direct purpose of following 2 instructions of missing keys treatment is to avoid using LEFT/RIGHT OUTER JOIN in most cases. (A common cause of this situation could be the source table does not define properly foreign key constraint, non-null constraint, etc.)

Above is all metadata may need to be filled in instructions tables. The triggers behind these tables will check the validity of input metadata.

Build and Deploy

• Development Phase

In many instances, developer may need to use individual steps for trial/debug convenience. Following diagram shows internal procedures of the whole build and deployment management.
(Labels in gray indicate the corresponding name of table, view, stored procedure or function)

VIEW_WASH_CYCLE_CODE can be used to preview the complete code of stored procedure which will be generated for the wash cycle after precompilation.

• Deployment Phase

As shown in above diagram, there are two ways of production deployment:

- Method 1 (recommended)
Distribute a manifest of metadata to production if the production database has the same setup of the DataWashroom environment as a deployment manager.

- Method 2 (left side)
Distribute the final stored procedure to production if the production database is not allow to have the DataWashroom for any reason, or you don't want people to think that you are too lazy to write those boring code. No one else would care your code was written in your seat or was generated in washroom.

Utilities by Product

All database packages of this project are organized in the package dependency graph below.

The bottom 3 blue packages PROGRESS_TRACK, DEPLOY_UTILITY and CACHE_UTILITY were designed to be used independently.

• PROGRESS_TRACK
  This is a generalized redesign (from the parent project http://view.codeplex.com) of Progress Status Tracker which provides 4 public interfaces:

    ...
    tProgress_ID    PLS_INTEGER;
BEGIN
    ...
    tProgress_ID := VPI.PROGRESS_TRACK.REGISTER(tTotal_Steps);
    ...
    VPI.PROGRESS_TRACK.GO_STEP(tProgress_ID, NULL, 'Status information 1 ...');
    ...
    VPI.PROGRESS_TRACK.GO_STEP(tProgress_ID, NULL, 'Status information 2 ...');
    ...
    VPI.PROGRESS_TRACK.GO_STEP(tProgress_ID, NULL, 'Status information 3 ...');
    ...
    VPI.PROGRESS_TRACK.GO_STEP(tProgress_ID, NULL, 'Status information n ...');
    ...
    VPI.PROGRESS_TRACK.GO_STEP(tProgress_ID, NULL, 'Done.');
EXCEPTION
WHEN OTHERS THEN
    VPI.PROGRESS_TRACK.ON_ERROR(tProgress_ID, SQLERRM);
    ...
END;

• DEPLOY_UTILITY

• CACHE_UTILITY

CACHE_CONTROL is the central control table which defines a unique identifier and expiration for each cache.

 
The CACHE_UTILITY package provides a global cache control similar to Reader-Writer Locks (refer to MSDN).
Refresh Lock is different from write lock:
- Only work in critical period while cache expiring, as shown above.
- Only one refresher can obtain the token to exclusively access for refreshing, other blocked refreshers will not refresh again when they enter the lock, seen in the circuit diagram below.
 

• CACHE_UTILITY.ENTER_REFRESH_LOCK
• CACHE_UTILITY.EXIT_REFRESH_LOCK
• CACHE_UTILITY.ENTER_READ_LOCK
• CACHE_UTILITY.EXIT_READ_LOCK

It is not recommended to use these APIs in application level directly.
Next section [Singleton] will explain the risk of deadlocks and corresponding precautions in a higher level encapsulation.

Extensibility and Applicability

Refer to the source code of 7 build-in wash instructions, extened instructions can also be customized when necessary.

The original intention of DataWashroom is designed for data pretreatment, not for entire processings. To simplify subsequent processing system designs by ensuring the data quality in the central pretreatment stage.
So the generated stored procedure has following characteristics:

• Parameterless
  It's sufficient for most centralized pretreatment applications.
  If you really need to pass some parameters to the generated stored procedure, the way of implementing a parameterize view can be used for reference (Oracle solution):
  1. Define all parameters as member variables in a database package,
     Encapsulate each of them by a get accessor (public function) and a set accessor (Optional), just like a property of a class;
  2. Create a view to expose these member variables;
  3. Set values to these member variables before execute the generated stored procedure.
     These package-based variables act as globals within a single session (connection) scope.
• Singleton
  A centralized data pretreatment system is normally hosted as a singleton instance to avoid unnecessary repetition of warm-up time. Two common scenarios:
  1. Act as a scheduled job without considering any concurrency.
     Scheduled job calls the generated stored procedure directly - This use pattern should always be considered as the top priority for most backoffice applications. If daily (nighttime) refreshing can not satisfy the requirement of timeliness, then consider following alternatives. 
  2. Be triggered on-demand by a central controller which handles critical section and expiry window.
     WASH_GLOBAL_CACHE Package:
     Always enclose those queries which will select from global cache in a pair of WASH_GLOBAL_CACHE.ENTER_LOCK(inCycle_ID) and WASH_GLOBAL_CACHE.EXIT_LOCK(inCycle_ID)
     (Note: Don't forget to EXIT_LOCK in exception block to make sure the read lock will always be released).
     The CACHE_EXPIRY (minutes) is defined in CACHE_CONTROL table, a new entry will be there accompanied by a new wash CYCLE_ID be created, default as 240 minutes (4 hours).

  Following diagram shown the internal implementation of cache lock mechanism.

  

  In practice, a corresponding schema would be allocated for a cache to organize its target tables. This would help consumers to be clear which cache is using. Also combined with naming convention (E.g. suffix of schema name, prefix of table name), whatever a schema contains a single cache or multiple caches ... all roads lead to a clarity, and reduce unnecessary configurations.

  Risks of dead lock
  • The WASH_GLOBAL_CACHE.EXIT_LOCK might be forgotten to call, then the lock would never be released until a session terminates.
    A similar way introduced by MSDN to avoid forgetting:
    1. Create a stored procedure that encapsulates together your DML operations which will query from a cache (a cache is a set of tables). Put this stored procedure in your package body only as a private member, don't expose it as a public member in package. For example:

       PROCEDURE MY_DM_FROM_CACHEA(...)
       AS
       BEGIN
           ...
           INSERT INTO XYZ.LOCAL_TABLE1 (...)
           SELECT ... FROM ABC.GLOBAL_CACHE_TABLE1, ... WHERE ...;
           ...
           UPDATE XYZ.LOCAL_TABLE2 L
           SET COL1 = (SELECT G.VAL1 FROM ABC.GLOBAL_CACHE_TABLE2 G WHERE ...);
           ...
       END MY_DM_FROM_CACHEA;

    2. Create another wrapper stored procedure that handles the lock and call the private stored procedure. For example:

       PROCEDURE PUB_DM_FROM_CACHEA(...)
       AS
           tCycle_ID CONSTANT VARCHAR2(32) := 'DPT.WASH_A';
           -- The Cycle ID is also the Cache ID.
       BEGIN
           VPI.WASH_GLOBAL_CACHE.ENTER_LOCK(tCycle_ID);
       
           MY_DM_FROM_CACHEA(...);
       
           VPI.WASH_GLOBAL_CACHE.EXIT_LOCK(tCycle_ID);
       EXCEPTION
           WHEN OTHERS THEN
               VPI.WASH_GLOBAL_CACHE.EXIT_LOCK(tCycle_ID);
               RAISE;
       END PUB_DM_FROM_CACHEA;

  • Multiple caches' data might be used in the same query, and multiple concurrent sessions interlace them in unpredicted order.
    Following aspects should be taken into comprehensive precautions:
    1. Cache Organizing
       In order to minimise this circumstances, arrange necessary tables in the same cache if they are usually coupling together in most use cases. So investigating and analysising are crucial to cache planning.
    2. Multi-cache Lock
       WASH_GLOBAL_CACHE will reject nested locks and cross locks.
       

       OK	Throw Exception	Throw Exception
       ENTER_LOCK('CACHEA'); DM_CACHEA(...); EXIT_LOCK('CACHEA'); ENTER_LOCK('CACHEB'); DM_CACHEB(...); EXIT_LOCK('CACHEB');	ENTER_LOCK('CACHEA'); ENTER_LOCK('CACHEB'); DM_CACHEAB(...); EXIT_LOCK('CACHEB'); EXIT_LOCK('CACHEA');	ENTER_LOCK('CACHEA'); DM_CACHEA(...); ENTER_LOCK('CACHEB'); DM_CACHEAB(...); EXIT_LOCK('CACHEA'); DM_CACHEB(...); EXIT_LOCK('CACHEB');

       In order to lock multiple caches as a combination of critical section, please use overloads of ENTER_LOCK and EXIT_LOCK to an array of cache IDs, as example below:

       PROCEDURE PUB_DM_FROM_CACHEAB(...)
       AS
           tCycle_IDs CONSTANT VCH_ID_ARY := VCH_ID_ARY('DPT.WASH_A', 'MKT.WASH_B' ...);   -- An array of Cache IDs.
       BEGIN
           VPI.WASH_GLOBAL_CACHE.ENTER_LOCK(tCycle_IDs);
       
           MY_DM_FROM_CACHEAB(...);
       
           VPI.WASH_GLOBAL_CACHE.EXIT_LOCK(tCycle_IDs);
       EXCEPTION
           WHEN OTHERS THEN
               VPI.WASH_GLOBAL_CACHE.EXIT_LOCK(tCycle_IDs);
               RAISE;
       END PUB_DM_FROM_CACHEAB;

       The internal implementation of overloads guarantee all caches (parameterized by an array) will always be locked in the same order by different sessions.
    3. Cache-lock Monitor
       It is impossible to avoid dead locks completely. The VPI.VIEW_CACHE_LOCK lists the locks currently held by the global cache control, it can be polled from your own alarm system.
       Structure application design as succinctly as possible, that's the ultimate approach. Everybody get sick sometime as long as we live, this should not be as an excuse not to keep clean.
  • Effective scope of cache lock
    Cache locks are automatically released by database engine when a session terminates, so client side applications need special attention: Don't split up a database processing into many small pieces and separate them through a lot of database connections(sessions) -- open a connection, do a small piece of manipulation, close the connection, open another connection, do another small piece, close ... Cache lock does not work for thus client application. This almost like a man rushes into washroom, does a piece of No.1 job, backs to office, runs again to washroom right away, does a piece of No.2 job, backs to office again ... so on and so on, how to reserve a spot in public washroom like a reserved parking spot?
  • Cache Safe Period
    As an additional option, if some end applications are not time sensitive that much, it could be fine to query caches without any explicit lock during the safe period. For example a man-generated report which is just for in-house informal research purposes, you can check all referenced caches at the beginning,
    - if the rest of safe period is not enough time to run related queries (estimation), then declines the human with message: "My cache system is busy in refreshing at the moment, please try again later. Sorry for the inconvenience, my dear!";
    - else go ahead as you love at your own risk of data incompleteness in case your querying time runs too long (than estimated) to fall into crises... You should have a good sense of the result data is integral or not, so that you can retry later.
    The view VPI.VIEW_CACHE_SAFE_PERIOD can be used to determine the safety, column names show as upper case in the figure below:
    
     
    In order to make this option feasible, a higher level system plan may consider to preassign some consumers as female applications/reports with lock(period), other clients as male applications/reports without lock(period), ensure the cache will not be extinct.
     
• Sequentiality
  The adoption of executing in sequence was based on the design target, the similar consideration as most washing machines.
  This is one reason why although the instruction infrastructure allow custome instructions to be extended, but it is not encouraged to over extend too beyond the target of data pretreatment. (-- A cardiovascular medicine is not intended to treat haemorrhoid, or vice versa.)
  
  Another project for data mid-processing aided designing system based on the foundation of http://dbparallel.codeplex.com/ and http://view.codeplex.com/ may be in the plan later, which will be good at parallelism.

Prevention over Treatment

Such note often seen in public washrooms:

(See: the Before and After Triggers)

An example for data, [START_DATE, END_DATE] is one of common modes for storing slow change data in back office systems, the date range is error prone (overlapped or fragmentary) for manual maintenance without validation. However, introducing the checksum could avoid mistake as easy as washing hands. E.g.

CREATE TABLE ABC.SEC_HOLDINGS
(
    SEC_ID      VARCHAR2(16)                                     NOT NULL,
    HOLDINGS    NUMBER(19)                                       NOT NULL,
    START_DATE  DATE DEFAULT TO_DATE('1990-01-01', 'yyyy-mm-dd') NOT NULL,
    END_DATE    DATE DEFAULT TO_DATE('9999-12-31', 'yyyy-mm-dd') NOT NULL,

    CONSTRAINT PK_SEC_HOLDINGS PRIMARY KEY (SEC_ID, START_DATE),
    CONSTRAINT UK_SEC_HOLDINGS UNIQUE (END_DATE, SEC_ID),
    CONSTRAINT CK_SEC_HOLDINGS_START_END CHECK (START_DATE <= END_DATE)
);

CREATE OR REPLACE TRIGGER ABC.TRG_SEC_HOLDINGS
AFTER INSERT OR DELETE OR UPDATE OF START_DATE, END_DATE ON ABC.SEC_HOLDINGS
DECLARE
    tSec_ID VARCHAR2(16);
BEGIN
    SELECT
        MIN(SEC_ID) INTO tSec_ID
    FROM
    (
        SELECT
            SEC_ID,
            SUM(END_DATE - START_DATE + 1) - 
               (MAX(END_DATE) - MIN(START_DATE) + 1) AS CHECKSUM
        FROM
            ABC.SEC_HOLDINGS
        GROUP BY
            SEC_ID
    )
    WHERE   ROWNUM    = 1
        AND CHECKSUM <> 0;

    IF tSec_ID IS NOT NULL THEN
        RAISE_APPLICATION_ERROR(-20021,
            'Some [START_DATE, END_DATE]s are overlapped or fragmentary!
             First issue SEC_ID: ' || tSec_ID);
    END IF;
END TRG_SEC_HOLDINGS;

Data Pollution Report

Distributing a complete list of bad data (base on known rules) everyday to every data producers may help people to look for accurate pathogeny. Sooner or later each source of problem application(design) will be laid bare. No matter how arrogant or humble, strong or weak ... a application is, every data sits equal on the toilet.

A utility for generating these queries(views) of data pollution report will be introduced in later release.

Requirement on requirement
In order to have a compact and well structured requirement for a clean system, some basic principles are recommended to keep in mind during the requirements analysis.

1. Disentangle functional dependencies between attributes in source systems base on true business meanings.
   As a isolation of basis data, the target system needs to carry out 3NF or BCNF. Some normalized relationships might have been safeguarded in source systems already, the requirement specifications is required to list the rest of them, and some redundant attributes can be cancelled.
2. Unify different units of measurement into a standardized system of units.
   For example, converting all units of measurement into internationally standard measurement units (miles/h => km/h, yards => metres) if source systems use multiple different systems of units; converting all native dollars into the settlement currency; converting mixed ISO code systems(two-letter code and three-letter code) into a pure ISO code system (either one of them)... etc.
3. Act as a global cache of basis data from source systems, to provide downstream back-office systems a clean data source with optimization for query performance is a key service purpose of the DataWashroom. It's requirement specification's responsibility to investigate:
   • Which attributes are often used as keys to look up others?
   • Which attributes are usually coupling together in consumers' queries?
   • What amount of data and growthrate of size?
   • What probability distributions of different partitions of data being used in most queries?
   Etc., these investigations will be substantial arguments for the physical design of target schema.
    
4. Summarize data quality requirements into 3 basic parts:
   • Regular rules within the same row.
     Such as: non-null, reasonable values range and comparisons, etc.
   • Business keys uniqueness, relationships conflict.
   • Relation-chains directivity and connectivity.

The sufficient analyses is intended to simplify solutions through inductiveness trimness and summary, not to complicate matters.

Supported Databases

• Oracle
  - Supported.
   
• SQL Server
  - To be implemented later...

Contribute

• Welcome all feedback through the CodePlex project (through comments, patches, or items in the Issue Tracker);
• Earnestly request adding implementation for other databases;