Rollback-free referential integrity update processing

1. A method, comprising:

receiving an Extract-Transform-Load (ETL) job to update data to data tables of a data store;

obtaining schemas for the data tables from the data store, wherein the ETL job comprises a link to the update data and identifiers for the data tables;

deriving referential integrity issues between the update data and the data tables from patterns defined in the schemas by processing a key resolution algorithm, deducing foreign keys from the schemas based on the patterns, splitting the ETL job into multiple sub jobs, one associated with processing first references that are guaranteed not to have any referential integrity problem, another job associated with second references guaranteed to have referential integrity issues, and third job associated with third references for which it is presently unknown as to whether there are any referential integrity issues;

modifying the ETL job to update a first portion of the update data to the corresponding data tables, wherein the first portion lacks any of the referential integrity issues with the data tables; and

submitting a modified ETL job to the data store based on the modifying and dynamically modifying the modified ETL job as the modified ETL job processes to identify and add certain portions of the referential integrity violations originally determined to be unknowable when the certain portions are resolved and known as the modified ETL job dynamically processes, wherein a size of a given portion of the ETL job that is unresolved as to whether it is associated with the update data that may or may not have any of the referential integrity issues substantially decreases from a first larger size because resolution is known.

2. The method of claim 1 , wherein deriving further includes deducing from the schemas the foreign keys that are incorrectly referenced in a second portion of the update data.

3. The method of claim 2 , wherein deriving further includes identifying not-yet-resolved results needed by the ETL job that may have second referential integrity issues in a third portion of the update data.

4. The method of claim 3 , wherein modifying further includes further modifying the ETL job to insert a first custom message in a diagnostic message field of the data tables associated with the second portion of the update data.

5. The method of claim 4 , wherein modifying further includes further inserting a reference to a custom script or a custom set of instructions into the ETL job to perform insertion of the first custom message into the diagnostic message field.

6. The method of claim 5 , wherein modifying further includes further modifying the ETL job to insert a second custom message in the diagnostic message field of the data tables associated with the third portion of the update data.

7. The method of claim 6 , wherein modifying further includes further inserting a second reference to a second custom script or a second custom set of instructions into the ETL job to perform insertion of the second custom message into the diagnostic message field.

8. The method of claim 7 , wherein modifying further includes further modifying the ETL job to generate diagnostic table information for the data tables corresponding to the second and third portions during the update to the data tables associated with the first portion, wherein the diagnostic table information comprises the first custom message and the second custom message and the diagnostic table information is housed in the corresponding diagnostic message field of the data tables.

9. The method of claim 8 further comprising:

receiving a new ETL job associated with updating the third portion of the update data to the corresponding data tables; and

submitting the new ETL job with the third portion of the update data.

10. A method, comprising:

intercepting an Extract-Transform-Load (ETL) job sent to a data store for updating data tables of the data store with update data resolved or defined by the ETL job, wherein the ETL job comprises a link to the update data and identifiers for the data tables;

classifying the update data into three categories comprising:

a first portion that is guaranteed not to have a referential integrity problem, a second portion that is known to have the referential integrity problem, and a third portion for which the referential integrity problem cannot be determined, wherein classifying further includes processing a key resolution algorithm, deducing foreign keys from patterns defined in the schemas of the data tables, and detecting, based on the patterns, referential integrity violations that are guaranteed to happen when the ETL job is processed as the second portion, guaranteed not to happen when the ETL job is processed as the first portion, and determined to be unknowable when the ETL job is processed as the third portion;

creating a modified ETL job that updates the data tables of the data store with the first portion of the update data and that inserts a customized diagnostic message into a diagnostic field of the data tables that corresponding to the second portion and the third portion; and

submitting the modified ETL job to the data store for processing in parallel with and during the processing of classifying and the creating to the ETL job to form the modified ETL job and dynamically modifying the modified ETL job as the modified ETL job processes to split the third portion into multiple sub jobs, one associated with processing first references that are guaranteed not to have any referential integrity problem, another job associated with second references guaranteed to have referential integrity issues, and a third job associated with third references it is presently unknown as to whether there any of the referential integrity issues wherein referents are always processed before corresponding references are.

11. The method of claim 10 , wherein classifying further includes identifying attempted data value changes from the update data to attributes or fields of records in the data tables where the attributes do not exist and classifying the update data that corresponds to the attempted data value changes as the second portion.

12. The method of claim 11 , wherein classifying further includes identifying processing results that will be returned when the ETL job is processed and that are currently undetermined and classifying the update data that corresponds to the processing results as the third portion.

13. The method of claim 12 , wherein classifying further includes identifying specific data value changes from the update data to second attributes or second fields of second records in the data tables where the attributes or fields exists and classifying the update data that corresponds to the specific data value changes as the first portion.

14. The method of claim 10 , wherein classifying further includes deducing the referential integrity problem based on the schemas associated with the data tables.

15. The method of claim 14 , wherein deducing further includes identifying the first portion based on the first portion attempting to change a particular foreign key associated with one or more of the data tables, wherein the particular foreign key identified from the schemas.

16. The method of claim 10 , wherein creating further includes inserting a reference to a first customized diagnostic script or application into the modified ETL job that performs insertion of the customized diagnostic message for the second portion.

17. The method of claim 16 , wherein inserting further includes inserting a reference to a second customized diagnostic script of second application into the modified ETL job that performs insertion of a second customized diagnostic message for the third portion.

18. A system, comprising:

a client comprising a client processor and a client non-transitory computer-readable storage medium comprising executable instructions representing an Extract-Transform-Load (ETL) interface;

a server comprising a server processor and a server non-transitory computer-readable storage medium comprising executable instructions representing an ETL preprocessor and a database Application Programming Interface (API);

a database comprising database tables;

the ETL interface when executed by the client processor from the client non-transitory computer-readable storage medium causes the client processor to perform processing comprising:

submitting an ETL job to the ETL preprocessor, the ETL job to update the database tables with update data defined within the ETL job, wherein the ETL job comprises a link to the update data and identifiers for the data tables;

the ETL preprocessor and the API when executed by the server processor from the server non-transitory computer-readable storage medium causes the server processor to perform processing comprising:

inspecting the update data defined within the ETL job for referential integrity problems with the database tables based on patterns defined in schemas associated with the database tables by processing a key resolution algorithm, deducing foreign keys from the schemas based on the patterns, and detecting the referential integrity problems that are guaranteed to happen when the ETL job is processed, guaranteed not to happen when the ETL job is processed, and determined to be unknowable when the ETL job is processed;

creating a modified ETL job by modifying the ETL job to update a first portion of the update data to the database tables, wherein the first portion is identified as guaranteed to not have any of the referential integrity problems when the ETL job is processed and dynamically modifying the modified ETL job as the modified ETL job processes to split the referential integrity problems originally determined to be unknowable when the ETL job is processed into multiple sub jobs, one associated with processing first references that are guaranteed to not have any referential integrity problem, another job associated with second references guaranteed to have referential integrity issues, and a third job associated with third references for which it is presently unknown as to whether there are any of the referential integrity issues, wherein referents are always processed before corresponding references are; and

processing the API to submit the modified ETL to the database for updating the corresponding database tables with valued defined in the first portion of the update data.

19. The system of claim 18 , wherein ETL preprocessor and the API when executed by the server processor from the server non-transitory computer-readable storage medium further causes the server processor to perform additional processing comprising:

identifying a second portion defined within the update data for the ETL job that is guaranteed to have the referential integrity problems; and

adding instructions to the modified ETL job before the submitting of the modified ETL job that adds a first customized message to a first diagnostic field in the database tables that corresponds to the second portion.

20. The system of claim 19 , wherein ETL preprocessor and the API when executed by the server processor from the server non-transitory computer-readable storage medium further causes the server processor to perform additional processing comprising:

identifying a third portion defined within the update data for the ETL job that was determined to be unknowable when the ETL job is processed;

processing classification for the first portion, the second portion, and the third portion in parallel with execution of a gradually identified first portion for the modified ETL job; and

adding second instructions to the modified ETL job that adds a second customized message to a second diagnostic field in the database tables that correspond to a reduced in size third portion.