Extract, transform, and load application complexity management framework

• US 9,922,072 B1
• Filed: 04/21/2016
• Issued: 03/20/2018
• 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 P., Steen, Mark
• Primary Examiner(s): Vy, Hung T
• Application Number:
  US15/134,627
• Time in Patent Office: 698 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;

transforming 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 transforming the source data asset is performed using information from a dependency resource that identifies an extract transform and load job and at least one of the source data asset and the target data asset; and

transmitting the target data asset to a data consuming system;

subsequent to transmitting the target data asset to the data consuming system, performing a quality check with respect to the source data asset or the target data asset; and

modifying the target data asset on the data consuming system based on the quality check.

2. The method of claim 1, wherein the dependency resource identifies a relation between the extract, transform and load job and the source data asset and/or a relation between the extract, transform and load job and the target data asset.

3. The method of claim 1, wherein the source system stores two or more versions of the source data asset, and

wherein the dependency resource identifies the version of the source data asset transformed by the data processing system.

4. The method of claim 1, wherein the dependency resource comprises a name of the source data asset and a name of the target data asset.

5. The method of claim 1, wherein the dependency resource identifies the source data asset, the target data asset, and a relationship to one or more other source data assets stored on the source system.

6. The method of claim 1, wherein the dependency resource identifies the source data asset, the target data asset, and a relationship to one or more other target data assets stored on the data consuming system.

7. The method of claim 1, wherein performing the quality check comprises:

determining whether a newer version of the source data asset is available on the source system.

8. The method of claim 1, wherein performing the quality check comprises:

detecting an error with respect to the target data asset or the source data asset.

9. The method of claim 1, wherein modifying the target data asset on the data consuming system comprises replacing at least a portion of data of the target data asset on the data consuming system.

10. The method of claim 1, wherein modifying the target data asset on the data consuming system comprises deleting at least a portion of data of the target data asset on the data consuming system.

11. The method of claim 1, wherein the information from the dependency resource identifies a dependency between the source data asset and the target data asset.

12. 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;

transforming 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 transforming the source data asset is performed using information from a dependency resource that identifies an extract transform and load job and at least one of the source data asset and the target data asset; and

transmitting the target data asset to a data consuming system;

subsequent to transmitting the target data asset to the data consuming system, performing a quality check with respect to the source data asset or the target data asset; and

modifying the target data asset on the data consuming system based on the quality check.

13. The non-transitory computer-readable medium of claim 12, wherein the dependency resource identifies a relation between the extract, transform and load job and the source data asset and/or a relation between the extract, transform and load job and the target data asset.

14. The non-transitory computer-readable medium of claim 12, wherein the source system stores two or more versions of the source data asset, and

wherein the dependency resource identifies the version of the source data asset transformed by the data processing system.

15. The non-transitory computer-readable medium of claim 12, wherein the dependency resource comprises a name of the source data asset and a name of the target data asset.

16. The non-transitory computer-readable medium of claim 12, wherein the dependency resource identifies the source data asset, the target data asset, and a relationship to one or more other source data assets stored on the source system.

17. The non-transitory computer-readable medium of claim 12, wherein the dependency resource identifies the source data asset, the target data asset, and a relationship to one or more other target data assets stored on the data consuming system.

18. The non-transitory computer-readable medium of claim 12, wherein performing the quality check comprises:

determining whether a newer version of the source data asset is available on the source system.

19. The non-transitory computer-readable medium of claim 12, wherein performing the quality check comprises:

detecting an error with respect to the target data asset or the source data asset.

20. The non-transitory computer-readable medium of claim 12, wherein modifying the target data asset on the data consuming system comprises replacing at least a portion of data of the target data asset on the data consuming system.

21. The non-transitory computer-readable medium of claim 12, wherein modifying the target data asset on the data consuming system comprises deleting at least a portion of data of the target data asset on the data consuming system.

22. The non-transitory computer-readable medium of claim 12, wherein the information from the dependency resource identifies a dependency between the source data asset and the target data asset.

23. 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;

transforming 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 transforming the source data asset is performed using information from a dependency resource that identifies an extract transform and load job and at least one of the source data asset and the target data asset; and

transmitting the target data asset to a data consuming system;

subsequent to transmitting the target data asset to the data consuming system, performing a quality check with respect to the source data asset or the target data asset; and

modifying the target data asset on the data consuming system based on the quality check.

24. The system of claim 23, wherein the dependency resource identifies a relation between the extract, transform and load job and the source data asset and/or a relation between the extract, transform and load job and the target data asset.

25. The system of claim 23, wherein the source system stores two or more versions of the source data asset, and

wherein the dependency resource identifies the version of the source data asset transformed by the data processing system.

26. The system of claim 23, wherein the dependency resource comprises a name of the source data asset and a name of the target data asset.

27. The system of claim 23, wherein the dependency resource identifies the source data asset, the target data asset, and a relationship to one or more other source data assets stored on the source system.

28. The system of claim 23, wherein the dependency resource identifies the source data asset, the target data asset, and a relationship to one or more other target data assets stored on the data consuming system.

29. The system of claim 23, wherein performing the quality check comprises:

determining whether a newer version of the source data asset is available on the source system.

30. The system of claim 23, wherein performing the quality check comprises:

detecting an error with respect to the target data asset or the source data asset.

31. The system of claim 23, wherein modifying the target data asset on the data consuming system comprises replacing at least a portion of data of the target data asset on the data consuming system.

32. The system of claim 23, wherein modifying the target data asset on the data consuming system comprises deleting at least a portion of data of the target data asset on the data consuming system.

33. The system of claim 23, wherein the information from the dependency resource identifies a dependency between the source data asset and the target data asset.