System for performing data transformations using a set of independent software components

• US 10,552,440 B1
• Filed: 08/22/2018
• Issued: 02/04/2020
• Est. Priority Date: 08/22/2018
• Status: Active Grant

• Current Assignee: Capital One Services LLC (Capital One Financial Corporation)
• Original Assignee: Capital One Services LLC (Capital One Financial Corporation)
• Inventors: Janyavula, Meher Ram, Rajappan, Arunkumar, Venkatasubramaniam, Muralikumar
• Primary Examiner(s): Somers, Marc S
• Application Number:
  US16/109,106
• Publication Number:
  US 20200065414A1
• Time in Patent Office: 531 Days
• CPC Class Codes: G06F 16/1748 De-duplication implemented within the file system, e.g. based on file segments de-duplication techniques in storage systems for the management of data blocks G06F3/0641
• CPC Class Codes: G06F 16/2379 Updates performed during online database operations; commit processing
• CPC Class Codes: G06F 16/2456 Join operations
• CPC Class Codes: G06F 16/254 Extract, transform and load [ETL] procedures, e.g. ETL data flows in data warehouses
• CPC Class Codes: G06F 16/258 Data format conversion from or to a database

Abstract

Described is a system (and method) that provides a framework for performing data transformations, which may be part of an Extract, Transform, and Load (ETL) process. The system may perform a data transformation by creating a pipeline that executes a set of independent software components (or component, plugins, add-ons, etc.). The components may be executed as individual services (e.g., microservices) that may be provided within containers to allow the components to be deployed as self-contained units on various types of host systems including cloud-based infrastructures. In addition, to provide further flexibility for the framework, the components may be implemented using preexisting software libraries.

Claims

1. A method for transforming data from a source data store for loading onto a target data store, the method comprising:

obtaining a transformation graph of a set of components, each component executable as an independent service and performing a predefined operation, the transformation graph representing one or more sequences of operations to transform data from one or more source data stores to a format for updating the target data store, wherein the data from the one or more source data stores corresponds to one or more accounts during a current day and a previous day;

initiating an execution of the set of components based on the transformation graph, each component executing as a separate process, wherein the execution of the set of components includes executing;

a first source component that performs operations including;

reading the data associated with the current day from a first of the one or more source data stores; and

converting the data associated with the current day to a first table data structure;

a second source component that performs operations including;

reading data from a second of the one or more source data stores; and

converting the data to a second table data structure; and

a third source component that performs operations including;

reading the data associated with the previous day from a third of the one or more source data stores; and

converting the data associated with the previous day to a third table data structure;

one or more transformation components that perform operations including;

transforming the data of the first table data structure as part of a first sequence of transformation operations;

transforming the data of the second table data structure as part of a second sequence of transformation operations; and

transforming the data of the third data table structure as part of a third sequence of transformation operations, wherein the third sequence of transformation operations comprises;

identifying updated data based upon a comparison of the data associated with the current day and the data associated with the previous day;

combining the updated data with data corresponding to one or more new accounts to produce combined data; and

joining the combined data with a copy of the data of the third table data structure to produce joined data; and

one or more target components that perform operations including;

writing one or more output files for updating the target data store, wherein the one or more output files comprise;

a first output file, based at least partially upon the updated data, that includes an updated list of the one or more accounts that is synchronized with changes to the one or more accounts within or between the previous day and the current day; and

a second output file, based at least partially upon the joined data, that includes the changes that occur within or between the previous day and the current day to the one or more accounts; and

initiating a loading of the one or more output files to the target data store.

2. The method of claim 1, wherein the execution of the set of components is performed as a data pipeline.

3. The method of claim 2, wherein each component is implemented using a python data analysis library, and the table data structure includes a DataFrame object of the python data analysis library.

4. The method of claim 3, wherein each component is implemented as an independent microservice or service.

5. The method of claim 4, wherein each microservice or service is deployed within a container on a host that is remote from the set of components, and the execution of the set of components is initiated by an instruction from the system using a communication protocol.

6. The method of claim 1, wherein the second sequence of transformation operations comprises:

deduplicating, by a deduplication component, the joined data to remove duplicate records of the joined data associated with the current day and the previous day.

7. The method of claim 1, wherein the first sequence of transformation operations comprises:

filtering the data of the first table data structure using a filter component to produce filtered data and rejected data; and

writing the rejected data to a reject file.

8. The method of claim 7, wherein the second sequence of transformation operations comprises:

reformatting the data of the second table data structure using a first reformat component to produce first reformatted data;

retrieving data from one or more columns of an input file using the filtered data and the first reformatted data; and

extracting a set of fields from the data in the input file.

9. The method of claim 8, wherein the third sequence of transformation operations further comprises:

reformatting the data of the third data table structure using a second reformat component to produce second reformatted data;

replicating the second reformatted data using a replicate component to produce first and second copies of the second reformatted data; and

joining the first reformatted data and the first copy of the second reformatted data using a first join component to produce the first joined data, wherein the updated data is based upon a comparison of the data associated with the current day in the first reformatted data and the data associated with the previous day in the first copy of the second reformatted data.

10. The method of claim 9, wherein joining the combined data with a copy of the data of the first table data structure to produce joined data comprises joining the combined data and the second copy of the second reformatted data using a second join component to produce second joined data, wherein the second output file is based at least partially upon the second joined data.

11. A system for transforming data, comprising:

one or more processors; and

a memory coupled to the one or more processors, the memory storing instructions, which when executed by the one or more processors, cause the one or more processors to perform operations comprising;

initiating an execution of a data pipeline for a set of components to perform one or more sequences of operations to transform data from one or more source data stores to a format for updating a target data store, wherein the data from the one or more source data stores corresponds to one or more accounts during a current day and a previous day, wherein each component is executable as an independent service and performs a predefined operation of the sequence, and wherein the execution of the data pipeline for the set of components includes executing;

a first source component that performs operations including;

reading the data associated with the current day from a first of the one or more source data stores; and

converting the data associated with the current day to a first table data structure;

a second source component that performs operations including;

reading data from a second of the one or more source data stores; and

converting the data to a second table data structure; and

a third source component that performs operations including;

reading the data associated with the previous day from a third of the one or more source data stores; and

converting the data associated with the previous day to a second third table data structure;

one or more transformation components that perform operations including;

transforming the data of the first table data structure as part of a first sequence of transformation operations;

transforming the data of the second table data structure as part of a second sequence of transformation operations; and

transforming the data of the third data table structure as part of a third sequence of transformation operations, wherein the third sequence of transformation operations comprises;

identifying updated data based upon a comparison of the data associated with the current day and the data associated with the previous day;

combining the updated data with data corresponding to one or more new accounts to produce combined data; and

joining the combined data with a copy of the data of the third table data structure to produce joined data;

and

one or more target components that perform operations including;

writing one or more output files for updating target data store, wherein the one or more output files comprise;

a first output file, based at least partially upon the updated data, that includes an updated list of the one or more accounts that is synchronized with changes to the one or more accounts within or between the previous day and the current day; and

a second output file, based at least partially upon the joined data, that includes the changes that occur within or between the previous day and the current day to the one or more accounts; and

initiating a loading of the one or more output files to the target data store.

12. The system of claim 11, wherein each component is implemented using a python data analysis library, and the table data structure includes a DataFrame object of the python data analysis library.

13. The system of claim 11, wherein each component is implemented as an independent microservice or service.

14. The system of claim 13, wherein each microservice or service is deployed within a container on a host that is remote from the system, and wherein the execution of the set of components is initiated by an instruction from the system using a communication protocol.

15. A non-transitory computer-readable medium storing instructions which, when executed by one or more processors of a system, cause the system to perform operations comprising:

identifying one or more source data stores that provide data to be extracted by the system and loaded onto a target data store, wherein the data from the one or more source data stores corresponds to one or more accounts during a current day and a previous day;

creating a transformation graph of a set of components, each component executable as an independent service and performing a predefined operation, the transformation graph representing one or more sequences of operations to transform data from the one or more source data stores to a format for updating the target data store;

initiating an execution of the set of components based on the transformation graph, each component executing as a separate process, wherein the execution of the set of components includes executing;

a first source component that performs operations including;

reading the data associated with the current day from a first of the one or more source data stores; and

converting the data associated with the current day to a first table data structure;

a second source component that performs operations including;

reading data from a second of the one or more source data stores; and

converting the data to a second table data structure; and

a third source component that performs operations including;

reading the data associated with the previous day from a third of the one or more source data stores; and

converting the data associated with the previous day to a third table data structure;

one or more transformation components that perform operations including;

transforming the data of the first table data structure as part of a first sequence of transformation operations;

transforming the data of the second table data structure as part of a second sequence of transformation operations; and

transforming the data of the third data table structure as part of a third sequence of transformation operations, wherein the third sequence of transformation operations comprises;

identifying updated data based upon a comparison of the data associated with the current day and the data associated with the previous day;

combining the updated data with data corresponding to one or more new accounts to produce combined data; and

joining the combined data with a copy of the data of the third table data structure to produce joined data;

and

one or more target components that perform operations including;

writing one or more output files for updating target data store, wherein the one or more output files comprise;

a first output file that includes an updated list of the one or more accounts that is synchronized with changes to the one or more accounts within or between the previous day and the current day; and

a second output file, based at least partially upon the joined data, that includes the changes that occur within or between the previous day and the current day to the one or more accounts; and

initiating a loading of the one or more output files to the target data store.

16. The medium of claim 15, wherein each component is implemented using a python data analysis library, and the table data structure includes a DataFrame object of the python data analysis library.

17. The medium of claim 16, wherein each component is implemented as an independent microservice or service that is deployed within a container on a host that is remote from the system, and wherein the execution of the set of components is initiated by an instruction from the system using a communication protocol.