System and method to maintain referential integrity while masking/migrating data in flat files
A computerized system maintains referential integrity during data migration and optionally masking. The system allows the user to identify one or more flat file(s) as the data source as well as identify the data destination file(s). The system automatically identifies the referential integrity columns where column names match in different flat files, automatically identifies referential integrity by analyzing related application source code for embedded business rules that indicate referential integrity, or can allow the user to describe the referential integrity pertaining to the data source to establish user-defined referential integrity, which referential integrity definition(s) are stored in a data store (e.g., an XML File) accessible via a configuration engine. The system, through a sequence engine and subsequently an execution engine, uses the stored referential integrity and other information to migrate and optionally masks the data from the data source file(s) into the data destination file(s).
1. A method comprising:
defining a data source comprising a flat file comprising a plurality of columns and a plurality of rows;
defining a data destination comprising at least a first table and a second table;
determining a referential integrity (RI) definition for and between the first and second tables based on at least a column name of the plurality of columns of the flat file and a referential integrity input, by scanning an application source code associated with the data source to identify one or more data rules associated with the at least column name wherein said one or more data rules comprises the RI definition; and
migrating the flat file to the at least first and second tables in accordance with and maintaining the RI definition.
2. The method of claim 1 wherein determining the RI definition comprises:
providing a user interface configured to receive the RI definition from a user.
3. The method of claim 1 wherein the flat file is a first flat file comprising a first plurality of columns with a first data header identifying first column names, and wherein defining the data source further comprises a second flat file comprising a second plurality of columns with a second data header identifying second column names, and wherein determining the RI definition comprises:
reading the first and second data headers to obtain respective sets of first column names and second column names;
matching at least one column name in the first set of column names with at least one column name in the second set of column names; and
defining the at least one matched column name as a primary key in the first table and a foreign key in the second table.
4. The method of claim 1 wherein said migrating further comprises masking columns.
5. The method of claim 4 wherein masking comprises masking values contained in at least one column of said plurality of columns of said flat file in accordance with a predetermined masking strategy.
6. The method of claim 5 wherein said predetermined masking strategy comprises irreversible masking.
7. The method of claim 6 wherein said irreversible masking comprises modifying values contained in the at least one column.
8. The method of claim 6 wherein said predetermined masking strategy comprises reversible masking.
9. The method of claim 8 wherein said reversible masking comprising encryption of values contained in the at least one column.
10. The method of claim 4 wherein migrating further comprises applying a filter to select one or more columns from the plurality of columns in the data source flat file for migration to the data destination.
11. The method of claim 10 wherein the selected one or more columns is less than a total number of the plurality of columns.
12. The method of claim 10 further comprising:
providing a user interface configured to receive a condition to define the filter.
13. The method of claim 1 wherein said data destination comprises a plurality of flat files.
14. The method of claim 1 , wherein the first and second tables of the data destination are included in a relational database management system (RDBMS).
15. The method of claim 1 further comprising, after said determining:
providing a user interface configured to receive from a user a mapping strategy that maps the plurality of columns of the flat file to respective columns of the data destination.
16. The method of claim 1 wherein said data store comprises an extensible markup language (XML) data file.