When application has been running in production or uat for some time there are some bugs that get reported because of bad data quality. This data is either generated by the application or is created by data migration scripts, data conversion scripts. The application suffers with non-deterministic errors especially is some specific environments while similar errors are not noticed in other environments.
How #
When application bugs or stack traces are reported, the data team should investigate along with the developers on the root cause of the bug. Some bugs are caused because the application is not expecting data in the given format, some bugs are caused by the application not expecting null values in the database and other times the data is not conforming to referential integrity rules as they are not defined in the database.
Data format #
Many times when data is loaded in the database using data conversion, data upload scripts there is mismatch with the type of formatting that is needed by the application and the type of formatting done by the data upload/conversion scripts. In collaboration with the developers the data team should introduce the data migration scripts that format the data in the correct way. Database refactoring pattern such as [http://databaserefactoring.com/IntroduceCommonFormat.html](Introduce Common Format) as a way to fix the formatting errors across all rows instead of fixing one occurrence of the error.
Unexpected null values #
In situations when the application is expecting the data attribute to not contain null values, the application code does not know how to deal with data where null value is found. These cases lead to null pointer exceptions, when there are these kinds of bugs found, the data team should try to isolate the reason why the data attribute is set to null, what all parts of the application or other scripts put data in this attribute and can this attribute be made non nullable.
In some situations we can in fact make the column non nullable by applying the [http://databaserefactoring.com/MakeColumnNonNullable.html](Make Column Non Nullable) database refactoring pattern. In this pattern, the data team finds all the rows where the column has null values and working with the business finds data for those rows, applies the data fixes, when the column is made non-nullable make sure the application testing is done to ensure it works with the non-nullable column and move these changes in to production.
Persistence frameworks driving database design #
Some persistence frameworks such as https://hibernate.org/orm/ https://blog.mybatis.org their usage my sometimes make the team to skip setting up foreign keys as the persistence frameworks may insert data out of order resulting in integrity constraint violations. The lack of foreign keys exposes the database for bad data to be entered either by the application or other data import programs.
Other situations where the primary key or unique key is being generated by some other means and the value may not be available burning the initial insert resulting in unique constraint violations, removing the primary key or unique key may not be the right option as this will lead to bad data getting into system.
The data team can pair with the developers and introduce DEFERRED constraints which instruct database to check for constraints at commit time instead of immediately after the insert, delete or update statement.
CREATE TABLE payment (
paymentid NUMBER NOT NULL,
paymentnumber VARCHAR2(128),
customerid NUMBER NOT NULL,
CONSTRAINT pk_payment
PRIMARY KEY (paymentid)
);
ALTER TABLE payment
ADD (CONSTRAINT fk_payment_customer
FOREIGN KEY (customerid)
REFERENCES customer
DEFERRABLE INITIALLY DEFERRED ) ;Setting up the constraints this way allows for the database integrity to be maintained and at the same time allows the application developers to function without re-writing or making major changes the applications persistence layer, thus improving productivity of the team.
Maintaining good data quality, allows for the developers to not code defensively, such as checking for null on columns that are not supposed to be null, parent rows existing when child records are found. Having better data quality also reduces null pointer exceptions errors and improves the quality perception of the application.
Others #
Similarly other data quality issues such as missing foreign keys or non standardized data can be fixed by applying [http://databaserefactoring.com/AddForeignKey.html](Add foreign key), [http://databaserefactoring.com/ApplyStandardCodes.html](Apply Standard Code) by the data team in collaboration with the developers by discovering these patterns easily and apply corrections that fix the root cause of the problem instead of fixing symptoms caused by bad data.