Extract, transform, and load application complexity management framework

• US 10,635,656 B1
• Filed: 03/19/2018
• Issued: 04/28/2020
• Est. Priority Date: 12/31/2010
• Status: Active Grant

• Current Assignee: United Services Automobile Association
• Original Assignee: United Services Automobile Association
• Inventors: Clark, Larry W., Hendry, Jason Paul, Steen, Mark
• Primary Examiner(s): Vy, Hung T
• Application Number:
  US15/924,715
• Time in Patent Office: 771 Days
• Field of Search: 707602, 707607
• CPC Class Codes: G06F 11/1446 Point-in-time backing up or restoration of persistent data
• CPC Class Codes: G06F 16/2365 Ensuring data consistency and integrity
• CPC Class Codes: G06F 16/2474 Sequence data queries, e.g. querying versioned data
• CPC Class Codes: G06F 16/254 Extract, transform and load [ETL] procedures, e.g. ETL data flows in data warehouses
• CPC Class Codes: G06Q 10/00 Administration; Management

Abstract

Extract, transform, and load application (ETL) complexity management framework systems and methods are described herein. The present disclosure describes systems and methods that reduce the complexity in managing ETL flow and correcting errant data that is subsequently identified. One or more methods include defining an ETL job definition, defining a data asset definition, defining a data asset dependency definition, receiving an ETL flow to provide execution of one or more ETL flow steps, providing retrieval of data from a source data asset, applying a data control to the source asset data, and producing an ETL job registration, a data asset status, a latest asset available date, a data asset consumer identifier, and a target data asset based on at least one of the ETL job definition, the data asset definition, the data dependency definition, and the source asset data.

Claims

1. A computer-implemented method of providing extract, transform, and load (ETL) application complexity management, the method comprising:

receiving, from a source system, a source data asset at a data processing system, the source data asset having a first data format;

initiating a transformation of the source data asset to a target data asset using the data processing system, the target data asset having a second data format different than the first data format, wherein initiating the transformation comprises executing an ETL job;

determining that an error occurred with respect to the execution of the ETL job during execution of the ETL job;

responsive to determining that the error occurred with respect to the execution of the ETL job during execution of the ETL job;

terminating the execution of the ETL job,

identifying one or more modifications to the target data asset due to the execution of the ETL job,

reversing the one or more modifications to the target data asset, and

re-executing the ETL job.

2. The method of claim 1, wherein reversing the one or more modifications to the target data asset comprises removing data inserted into the target data asset during execution of the ETL job.

3. The method of claim 2, wherein removing data inserted into the target data asset comprises removing one or more rows of data inserted into the target data asset during execution of the ETL job.

4. The method of claim 1, wherein reversing the one or more modifications to the target data asset comprises reversing a modification to an end date associated with the target data asset.

5. The method of claim 1, wherein reversing the one or more modifications to the target data asset comprises reversing a modification to a flag associated with the target data asset.

6. The method of claim 1, wherein initiating the transformation of the source data asset to a target data asset comprises registering the ETL job by generating a data asset dependency table, wherein the data asset dependency table comprises data indicating a relation between a job definition associated with the ETL job, the source data asset, and the target data asset.

7. The method of claim 1, wherein initiating the transformation comprises performing one or more quality checks with respect to the source data asset and/or the target asset.

8. The method of claim 1, wherein performing one or more quality checks comprises:

determining a version of the source data asset, and

determining whether the version of the source data asset corresponds to a most recent version of the source data asset.

9. The method of claim 1, wherein performing one or more quality checks comprises:

determining a number of data records written to the target data asset, a number of data records discarded from the target data asset, and a number of data records in the source data asset, and

comparing the number of data records written to the target data asset and the number of data records discarded from the target data asset to the number of data records in the source data asset.

10. A non-transitory computer-readable medium including one or more sequences of instructions which, when executed by one or more processors, causes:

receiving, from a source system, a source data asset at a data processing system, the source data asset having a first data format;

initiating a transformation of the source data asset to a target data asset using the data processing system, the target data asset having a second data format different than the first data format, wherein initiating the transformation comprises executing an ETL job;

determining that an error occurred with respect to the execution of the ETL job during execution of the ETL job;

responsive to determining that the error occurred with respect to the execution of the ETL job during execution of the ETL job;

terminating the execution of the ETL job,

identifying one or more modifications to the target data asset due to the execution of the ETL job,

reversing the one or more modifications to the target data asset, and

re-executing the ETL job.

11. The non-transitory computer-readable medium of claim 10, wherein reversing the one or more modifications to the target data asset comprises removing data inserted into the target data asset during execution of the ETL job.

12. The non-transitory computer-readable medium of claim 11, wherein removing data inserted into the target data asset comprises removing one or more rows of data inserted into the target data asset during execution of the ETL job.

13. The non-transitory computer-readable medium of claim 10, wherein reversing the one or more modifications to the target data asset comprises reversing a modification to an end date associated with the target data asset.

14. The non-transitory computer-readable medium of claim 10, wherein reversing the one or more modifications to the target data asset comprises reversing a modification to a flag associated with the target data asset.

15. The non-transitory computer-readable medium of claim 10, wherein initiating the transformation of the source data asset to a target data asset comprises registering the ETL job by generating a data asset dependency table, wherein the data asset dependency table comprises data indicating a relation between a job definition associated with the ETL job, the source data asset, and the target data asset.

16. The non-transitory computer-readable medium of claim 10, wherein initiating the transformation comprises performing one or more quality checks with respect to the source data asset and/or the target asset.

17. The non-transitory computer-readable medium of claim 10, wherein performing one or more quality checks comprises:

determining a version of the source data asset, and

determining whether the version of the source data asset corresponds to a most recent version of the source data asset.

18. The non-transitory computer-readable medium of claim 10, wherein performing one or more quality checks comprises:

determining a number of data records written to the target data asset, a number of data records discarded from the target data asset, and a number of data records in the source data asset, and

comparing the number of data records written to the target data asset and the number of data records discarded from the target data asset to the number of data records in the source data asset.

19. A system comprising:

one or more processors;

a non-transitory computer-readable medium including one or more sequences of instructions which, when executed by the one or more processors, causes;

receiving, from a source system, a source data asset at a data processing system, the source data asset having a first data format;

initiating a transformation of the source data asset to a target data asset using the data processing system, the target data asset having a second data format different than the first data format, wherein initiating the transformation comprises executing an ETL job;

determining that an error occurred with respect to the execution of the ETL job during execution of the ETL job;

responsive to determining that the error occurred with respect to the execution of the ETL job during execution of the ETL job;

terminating the execution of the ETL job,

identifying one or more modifications to the target data asset due to the execution of the ETL job,

reversing the one or more modifications to the target data asset, and

re-executing the ETL job.

20. The system of claim 19, wherein reversing the one or more modifications to the target data asset comprises removing data inserted into the target data asset during execution of the ETL job.

21. The system of claim 20, wherein removing data inserted into the target data asset comprises removing one or more rows of data inserted into the target data asset during execution of the ETL job.

22. The system of claim 19, wherein reversing the one or more modifications to the target data asset comprises reversing a modification to an end date associated with the target data asset.

23. The system of claim 19, wherein reversing the one or more modifications to the target data asset comprises reversing a modification to a flag associated with the target data asset.

24. The system of claim 19, wherein initiating the transformation of the source data asset to a target data asset comprises registering the ETL job by generating a data asset dependency table, wherein the data asset dependency table comprises data indicating a relation between a job definition associated with the ETL job, the source data asset, and the target data asset.

25. The system of claim 19, wherein initiating the transformation comprises performing one or more quality checks with respect to the source data asset and/or the target asset.

26. The system of claim 19, wherein performing one or more quality checks comprises:

determining a version of the source data asset, and

determining whether the version of the source data asset corresponds to a most recent version of the source data asset.

27. The system of claim 19, wherein performing one or more quality checks comprises:

determining a number of data records written to the target data asset, a number of data records discarded from the target data asset, and a number of data records in the source data asset, and

comparing the number of data records written to the target data asset and the number of data records discarded from the target data asset to the number of data records in the source data asset.