Automation Testing of Oracle DB Data
Oracle DB for Application Development
Oracle Data Warehouse
Oracle DB as a source target during Data Migration
Oracle DB integrated to Cloud Based Applications
Oracle DB Integrated to on premise DBs
In order to ensure that the most accurate data is available for consumption, Data Accuracy checks need to be performed on the existing Database.
Validation of columns needs to be done as per the data model. Validation of rows needs to be done as per source records, business rules etc.
Example: Manual data entry at application level sometimes does not adhere to business rules and it might lead to data inconsistencies.
Running frequent sanity checks on the database is important so that the data hygiene is always maintained.
Example: Sometimes with manual data entry or data import from legacy systems, data rules are overridden to facilitate and maximise data push.
Metadata testing is needed to verify that the characteristics of the data in the tables is as expected.
Verify that the type and format of the data in the database tables matches the specified data type.
Example: Date, timestamp and time data types should have values in a specific format so that they can be parsed by the consuming process.
Length of string and number data values in the tables should match the minimum and maximum allowed length for those columns.
Example: If Data for the country column has more than 2 characters in the table’s field while the limit is only 2 characters, that is an anomaly.
Verify that all required data elements in the tables are populated.
Example: Date of Birth is a required data element but some of the records might be missing this value.
The purpose of Data Quality tests is to verify the accuracy of the data in the Oracle Tables.
Check for duplicate records in the tables with the same unique key column or a unique combination of columns as per business requirement.
Example: Business requirement says that a combination of First Name, Last Name, Middle Name and Data of Birth should be unique for the Customer record. Duplicates can be identified by using this criteria in SQL queries.
Business rules may dictate that the values in certain columns should adhere to a list of values. Verify that the values in the records conform to reference data standards.
Example: Values in the country_code column should have a valid country code from a Country Code domain.
Many data fields can contain a range of values that cannot be enumerated. However, using reasonable constraints or rules, these situations can be detected where the data is clearly wrong.
Example: Date of birth (DOB) field is defined as the DATE datatype and can assume any valid date. However, a DOB in the future, or more than 100 years in the past are probably invalid.
This check addresses “keyed” relationships of entities within a domain. The goal is to identify orphan records in the child entity with a foreign key to the parent entity.
Null value in foreign key column should not be allowed in order to maintain referential integrity. But, that rule might have been overridden during legacy data imports.
Example: Find Count of records with null foreign key values in the tables.
All invalid foreign key values in a table that do not have a corresponding primary key in the parent database table should be addressed.
Example: Find the gaps in referential integrity using –
- Count of null or unspecified dimension keys in a Fact table.
- Count of invalid foreign key values in the contact list