Extract, transform, and load application complexity management framework

• US 9,323,816 B1
• Filed: 04/01/2014
• Issued: 04/26/2016
• 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:
  US14/242,476
• Time in Patent Office: 756 Days
• Field of Search: 707/602, 707/682, 707/204, 707/661, 717/155
• US Class Current: 1/1
• 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 a source data asset at a data processing system, the source data set having a first data format;

transforming the source data asset to a target data asset using the data processing system, the target data set having a second data format different than the first data format;

transmitting the target data set to a data consuming system;

receiving incident information regarding the source data asset, the incident information identifying errant data associated with the source data asset;

producing a roll back plan to remove an effect of the errant data from the data consuming system;

executing the roll back plan; and

replacing at least a portion of data of the target data asset with corrected data.

2. The method of claim 1, the roll back plan is executed responsive to receiving an indication that a user has reviewed the roll back plan.

3. The method of claim 1, wherein replacing at least a portion of data of the target data asset with corrected data corrects the errant data identified by the data asset incident information.

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

receiving information about

a data asset incident relating to a data asset error,

a data asset consumer associated with a particular data asset, and

a data asset status associated with the particular data asset to determine a data asset dependency;

producing a roll back plan, based on the data asset dependency, to remove the effect of the data asset error from one or more target data asset consumers;

executing the roll back plan; and

replacing one or more subsets of target data assets, each containing at least one data item, with corrected data to remove the effects of the data asset error,

wherein replacing the one or more subsets of target data assets with corrected data to remove the effects of the data asset error comprises recalculating totals and counts within one or more data sets.

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

receiving information about

a data asset incident relating to a data asset error,

a data asset consumer associated with a particular data asset, and

a data asset status associated with the particular data asset to determine a data asset dependency;

producing a roll back plan, based on the data asset dependency, to remove the effect of the data asset error from one or more target data asset consumers;

executing the roll back plan; and

replacing one or more subsets of target data assets, each containing at least one data item, with corrected data to remove the effects of the data asset error,

wherein replacing the one or more subsets of target data assets with corrected data to remove the effects of the data asset error comprises removing a subset of one or more target data assets.

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

receiving information about

a data asset incident relating to a data asset error,

a data asset consumer associated with a particular data asset, and

a data asset status associated with the particular data asset to determine a data asset dependency;

producing a roll back plan, based on the data asset dependency, to remove the effect of the data asset error from one or more target data asset consumers;

executing the roll back plan; and

replacing one or more subsets of target data assets, each containing at least one data item, with corrected data to remove the effects of the data asset error,

wherein replacing the one or more subsets of target data assets with corrected data comprises providing corrected data to the target data asset to replace a subset of data in one or more target data asset consumers.

7. The method of claim 6, wherein providing corrected data to the target data asset to replace the subset of data in the one or more target data asset consumers comprises recalculating the totals and counts within one or more of the subsets of data.

8. The method of claim 6, wherein providing corrected data to the target data asset to replace the subset of data in the one or more target data asset consumers comprises inserting the corrected data into one or more consuming target data assets.

9. The method of claim 8, wherein the target data asset consumers are consumed by one or more subsequent target data asset consumers.

10. The method of claim 9, wherein inserting the corrected data into the one or more target data asset consumers corrects all subsequent target data asset consumers.