Patent Publication Number: US-11657050-B1

Title: Data reconciliation for big data environments

Description:
FIELD OF TECHNOLOGY 
     Aspects of the disclosure relate to big data environments. 
     BACKGROUND OF THE DISCLOSURE 
     Data reconciliation may be understood to be the process of verification of data during a data transfer. As data is transferred from one location to another location, it is important to track the data movement so that there is a clear image of the transfer. The image of the transfer may also represent the data lineage. Maintaining accurate and clear data lineage may identify each step of the data transfer and therefore ensure that there is no undesired transfer, transformation or problem in the data transition. 
     Data reconciliation involves mathematical algorithms. Conventional data reconciliation algorithms and associated systems have been used for systems with conventionally-sized data silos. However, as enterprises utilize more and more data, and data silos have grown to multiple petabytes, also referred to as big data, conventional data reconciliation algorithms and associated systems have become sub-optimal. Therefore, it would be desirable to create updated data reconciliation algorithms that are more advantageous and specialized for a big data environment. 
     SUMMARY OF THE DISCLOSURE 
     There exist challenges when implementing data reconciliation in a big data environment. A first challenge may involve determining, within a predetermined time period, whether there are differences or discrepancies between two large-sized data sets. 
     Once the determination has been made that there are differences or discrepancies between the two data sets, a second challenge may involve identifying the location, or locations, of the differences or discrepancies. 
     The size of the files makes it difficult to perform, in a relatively short time period, conventional data reconciliation to identify the differences or discrepancies between the files. 
     An illustration of the data size may be understood by the following example: A financial institution may have various departments. These departments may include a mortgage department, a home equity loan department, a checking account department, a savings account department, a certificate of deposit (CD) department and a credit card department. Each of the departments may execute various reporting algorithms and thereby produce various reporting logs each week, each month, each quarter and each year. 
     In one example, the credit card department may execute a credit loss forecast log for credit cards each quarter. Such a credit loss forecast log may include twenty billion records, which may account for approximately six terabytes of memory. It should be appreciated that the credit loss forecast log is one of many reporting logs included in the credit card department, and the credit card department is one of many departments included in the financial institution. 
     The credit loss forecast log may be transferred from a first location in memory to a second location in memory. In order to confirm the completeness of the data transfer, a comparison may be executed between the credit loss forecast log stored in the first location and the credit loss forecast log stored in the second location. Such a data reconciliation that compares approximately six terabytes of memory stored in the first location to six terabytes of memory stored in the second location may involve non-conventional data reconciliation systems and methods. Such non-conventional data reconciliation systems and methods may execute the data reconciliation within a relatively short time period, such as less than, or equal to one minute. 
     Apparatus and methods for reconciling data in a big data environment is provided. Methods may include receiving a first data set and a second data set for reconciliation. Methods may include identifying a first set of metadata associated with the first data set. Methods may include identifying a second set of metadata associated with the second data set. 
     Methods may include comparing the first set of metadata to the second set of metadata to obtain a joinable link between the two data sets. Methods may include identifying a subset of data found within the first data set and a subset of data found within the second data set. The subset of data found within the first data set and the subset of data found within the second data set may be joinable. 
     Methods may include dynamically constructing one or more structured query language (SQL) queries to identify any discrepancies between the first data set and the second data set. The SQL query may utilize the subset of data found within the first data set and the subset of data found within the second data set to join the first data set to the second data set. Methods may include executing the one or more SQL queries to identify any discrepancies between the first data set and the second data set. Any identified discrepancies may be displayed to a user. 
     It should be noted that the physical data memory location of the first data set and the physical data memory location of the second data set may be different data locations. 
    
    
     
       BRIEF DESCRIPTION OF THE DRAWINGS 
       The objects and advantages of the invention will be apparent upon consideration of the following detailed description, taken in conjunction with the accompanying drawings, in which like reference characters refer to like parts throughout, and in which: 
         FIG.  1    shows an illustrative diagram in accordance with principles of the disclosure; 
         FIG.  2    shows another illustrative diagram in accordance with principles of the disclosure; 
         FIG.  3    shows yet another illustrative diagram in accordance with principles of the disclosure; 
         FIG.  4    shows still another illustrative diagram in accordance with principles of the disclosure; 
         FIG.  5    shows yet another illustrative diagram in accordance with principles of the disclosure; 
         FIG.  6    shows still another illustrative diagram in accordance with principles of the disclosure; 
         FIG.  7    shows yet another illustrative diagram in accordance with principles of the disclosure; 
         FIG.  8    shows still another illustrative diagram in accordance with principles of the disclosure; 
         FIG.  9    shows yet another illustrative diagram in accordance with principles of the disclosure; 
         FIGS.  10 A,  10 B,  10 C and  10 D  show another illustrative diagram in accordance with principles of the disclosure; 
         FIGS.  11 A,  11 B,  11 C,  11 D and  11 E  show yet another illustrative diagram in accordance with principles of the disclosure; 
         FIGS.  12 A and  12 B  show still another illustrative diagram in accordance with principles of the disclosure; 
         FIGS.  13 A,  13 B and  13 C  show yet another illustrative diagram in accordance with principles of the disclosure; 
         FIG.  14    shows still another illustrative diagram in accordance with principles of the disclosure; and 
         FIG.  15    shows yet still another illustrative diagram in accordance with principles of the disclosure. 
     
    
    
     DETAILED DESCRIPTION OF THE DISCLOSURE 
     Apparatus and methods for data reconciliation in a big data environment is provided. Apparatus and methods may include and/or utilize a hardware processor, a server-based receiver, a display and any other suitable components. 
     Methods may include receiving identification of a first big data set and a second big data set. The size of the first big data set and the second big data set may be larger than 5 terabytes. The server-based receiver may be used to receive identification of the first big data set and the second big data set. 
     Methods may include receiving a dimension, also referred to as dims. The server-based receiver may be used to receive the dimension. The dimension may include a first set of fields from the first big data set and a second set of fields from the second big data set. The dimension may be used to join the first big data set to the second big data set. The dimension may also be referred to as SQL join fields. 
     The first set of fields may correspond to the second set of fields. The first set of fields from the first big data set may be a primary key of the first big data set. The second set of fields from the second big data set may be a primary key of the second big data set. 
     In some embodiments, the dimension may be system generated. As such, methods may include identifying a first set of metadata associated with the first big data set. The first set of metadata may include a set of schema information and a set of data type information. Methods may also include identifying a second set of metadata associated with the second big data set. The second set of metadata may include a second set of schema information and a second set of data type information. Methods may also include identifying a first subset of metadata from the first set of metadata and a second subset of metadata from the second set of metadata. The first subset may correspond to the second subset. Methods may include generating a dimension based on the first subset of metadata that corresponds to the second subset of metadata. The dimension may include a first set of fields from the first big data set and a second set of fields from the second big data set. The first set of fields may correspond to the second set of fields. 
     Methods may include dynamically constructing a SQL query to compare the first big data set to the second big data set. The constructing may use the dimension as a join set of data fields within the SQL query. The SQL query may further include a where clause. The where clause may include comparing of a set of column names in the first big data set that correspond to a second set of column names in the second big data set. As such, the where clause may determine any differences, between data records included in the first data set and data records included in the second data set, that are found within the specified column names. In the event that there are differences between two records and the differences are located in a column name that is not specified within the where clause, the SQL query may not identify those differences. 
     Methods may include executing the query on the first big data set and the second big data set. Methods may include generating a result set from the SQL query. 
     An exemplary SQL query may include selecting a first data set and a second data set, joining the first data set with the second data set based on a link between a first column from the first data set and a second column from the second data set, where a cell in a third column of the first data set is not equal to a corresponding cell in a fourth column of the second data set or a cell in a fifth column of the first data set is not equal to a corresponding cell in a sixth column of the second data set or a cell in a seventh column in the first data set is not equal to a corresponding cell in an eighth column of the second data set, ordering a set of results by the first column, then by the third column, then by the fifth column and then by the eighth column. 
     In some embodiments, methods may include identifying that the result set is empty. Based on the empty result set, methods may include identifying that there are no differences between the first big data set and the second big data set. Methods may also include presenting to a user that a data reconciliation between the first big data set and the second big data set obtained a no differences result. 
     In other embodiments, methods may include identifying that the result set includes one or more differences. The result set may include one or more columns that are included in the first big data set that correspond to one or more columns that are included in the second big data set. 
     Methods may include ordering the result set by the differences included in the first of the one or more columns. The differences may be included in a top portion of the first column. 
     Methods may include selecting a first predetermined number of rows in the ordered result set. It should be appreciated that the selected rows may be associated with the one or more columns. The first predetermined number of rows may be 200, 2000 or any other suitable number. Methods may include transposing the first predetermined number of rows where the columns become rows and the rows become columns. 
     Methods may include identifying a subset of the differences along a first row that corresponds to the first column. Methods may include selecting the subset of the differences from the first row. 
     Methods may include dynamically reconstructing the SQL query by removing a column name of the first column from the where clause included in the SQL query. Methods may include executing the reconstructed SQL query. It should be noted that the SQL query and the reconstructed SQL query may be executed in parallel. 
     Methods may include identifying that the result set includes a second subset of one or more differences. The result set may include one or more columns that are included in the second big data set. Methods may include ordering the result set by the second subset of the differences included in a subsequent column of the one or more columns. The second subset of the differences may be included in the top portion of the subsequent column. 
     Methods may include selecting a subsequent predetermined number of rows in the ordered result set. The predetermined number of rows may be 200, 2000 or any other suitable number. The selected rows may be associated with the one or more columns. It should be appreciated that the selection may limit the cells within the rows to the cells that correspond to the subsequent column. Methods may include transposing the subsequent predetermined number of rows in the ordered result set. The columns may become rows and the rows may become columns. 
     Methods may include identifying the second subset of the differences along a subsequent row that corresponds to the subsequent column. Methods may include selecting the second subset of the differences from the subsequent row. 
     Methods may include repeating the dynamic reconstruction of the SQL query by removing the subsequent column from the SQL query, executing the reconstructed query, ordering the result set, selecting the subsequent rows, transposing the rows and identifying the differences. The method may be repeated until all the differences between the first data set and the second data set have been identified. 
     Methods may include upon completion of identifying differences between the first big data set and the second big data set, displaying the differences identified from each of the rows. The set differences displayed may include a set of differences identified from the query, a set of differences identified from the subsequent query and any other differences identified from any other further query. The differences may be displayed on a mobile device, computer display or any other suitable display. 
     Apparatus and methods described herein are illustrative. Apparatus and methods in accordance with this disclosure will now be described in connection with the figures, which form a part hereof. The figures show illustrative features of apparatus and method steps in accordance with the principles of this disclosure. It is to be understood that other embodiments may be utilized and that structural, functional and procedural modifications may be made without departing from the scope and spirit of the present disclosure. 
     The steps of methods may be performed in an order other than the order shown or described herein. Embodiments may omit steps shown or described in connection with illustrative methods. Embodiments may include steps that are neither shown nor described in connection with illustrative methods. 
     Illustrative method steps may be combined. For example, an illustrative method may include steps shown in connection with another illustrative method. 
     Apparatus may omit features shown or described in connection with illustrative apparatus. Embodiments may include features that are neither shown nor described in connection with the illustrative apparatus. Features of illustrative apparatus may be combined. For example, an illustrative embodiment may include features shown in connection with another illustrative embodiment. 
       FIG.  1    shows an illustrative diagram. Data reconciliation algorithm  108  may receive various inputs, such as process models  104  and data measurements  102 . Data measurements  102  may determine the selection of the appropriate data reconciliation algorithm. Data reconciliation algorithm  108  may be used to output reconciled data  108 . 
       FIGS.  2 - 8    show exemplary diagrams that illustrate the backend processes of a data reconciliation in big data environments. 
       FIG.  2    shows illustrative diagram  200 . Illustrative diagram  200  shows exemplary SQL queries. Section  202  may correspond to line numbers in the query. Section  204  may correspond to the language of the query. 
     At line numbers  1974  and  1975 , a SQL query may select the top thirty records from a data file named CLMSReporting.dbo.vwOverLayMainData. 
     At line numbers  1977 ,  1978  and  1980 , a SQL query may select all records. Selection of all records may be indicated by the (*) indicator. The records may be selected from a data file named CLMSUserSpace.OMR.CSTMRunSetting. The data records may be selected when the variable fcst_cycle, included in the data records, is equivalent to 202110. Line number  1979  may indicate a commented-out portion of the query which states, ‘where portfolio_id like’%202003%′. 
     At line numbers  1983 ,  1984  and  1985 , a SQL query may update CLMSUserSpace.OMR.CSGMRunSetting by setting the portfolio_id to ‘202003_BT_CCBR_BackBook_P5_ME’ in records where in the job identifier field, also referred to herein as job_id field, is equivalent to 1231. 
     At line numbers  1988 ,  1989  and  1990 , a SQL query may select all records from CLMSUserSpace.OMR.CSTMRunSetting and placed into the variable a. Line number  1990  shows a join between variable a and temporary variable (#tmp) b on the job_id variable included in both a and b. 
     At line numbers  1993  and  1994 , a SQL query shows selecting all records from the temporary variable. 
     At line numbers  1997 ,  1998  and  1999 , a SQL query shows updating the temporary variable. The SQL query includes setting the output_month variable of the records to 100 where the job_id variable is 550 or 1208. 
       FIG.  3    shows illustrative diagram  300 . Illustrative diagram  300  shows exemplary SQL queries. Section  302  may correspond to line numbers in a query. Section  304  may correspond to the language of a query. Section  306  may correspond to the results of the execution of a query. 
     At line numbers  1974  and  1975 , a SQL query may select the top thirty records from a data file named CLMSReporting.dbo.vwOverLayMainData. 
     At line numbers  1977 - 1980 , a SQL query may select all records. Selection of all records may be indicated by the (*) indicator. The records may be selected from a data file named CLMSUserSpace.OMR.CSTMRunSetting. The records may be selected when the variable fcst_cycle, included in the records, is equivalent to 202110. Line number  1979  may indicate a commented-out portion of the query which states, ‘where portfolio_id like ‘%202003%’. 
     At line numbers  1981 - 1983 , a SQL query may update CLMSUserSpace.OMR.CSGMRunSetting by setting the portfolio_id to ‘202003_BT_CCBR_BackBook_P5_ME’ in records where the job_id field is equivalent to 1231. 
     Line numbers  1985 - 1989  show a query. The query includes selecting all records from the data set a that have common set of columns within a. Line numbers  1985 - 1986  show selecting all records from a memory location. The memory location may be CLMSUserSpace.OMR.CSTMRunSetting. The memory location may be identified as variable a. Line  1987  shows a join. A join may indicate one or more sets of data fields that may be similar in a first data set and a second data set. The one or more sets of fields may enable the query to compare the first data set to the second data set. The join may include joining a temporary file b with data set a. The set of fields with which to join data set a and data set b may be a.job_id and b.job_id. As such, the comparison between data set a and data set b may be based on the equivalence of the job_id field in data set a and data set b. Line number  1988  shows a where clause that may limit the SQL query. A where clause may enable the query to select data records that fulfill the limitation of the where clause. The where clause may identify which records to retrieve from the data sets. The where clause shown may enable the query to retrieve records for which the following is true: the output_month field identified in data set a is not equivalent to the output_month field identified in data set b, or the scenario_cycle field identified in data set a is not equivalent to the scenario_cycle field identified in data set b or the sandbox field identified in data set a is not equivalent to the sandbox field identified in data set b. 
     Line numbers  1993  and  1994  may show a tie query, also referred to as a tie function. The tie function may compare two data sets. The tie function may accept parameters. Each of the parameters may be recited in greater detail in connection with  FIG.  9   . 
     Line numbers  1985  through  1989  may have been executed. As such, results section  306  may include the results of the execution of line numbers  1985  through  1989 . Results section  306  may display the data records associated with job identifiers  550  and  1200  that have been retrieved from data set a. 
     As such, the system may identify that data records associated with job identifiers  550  and  1200  may include differences between data set a and data set b. Although a user may be able to locate the differences by examining the two data records, the user may not be informed where the differences are located. It should be noted that, it may be difficult to identify the differences especially when there are a large number of corresponding data records that differ from one another. 
       FIG.  4    shows illustrative diagram  400 . Illustrative diagram  400  shows exemplary SQL queries. Section  402  may correspond to line numbers in the query. Section  404  may correspond to the language of the query. Section  406  may correspond to the section of the SQL query currently executed. Section  408  may correspond to the results of the execution of a query. 
     The queries shown in line numbers  1974 - 1983  and  1993 - 1994  may be discussed in connection with  FIG.  3   . 
     Section  406  shows a SQL query. The SQL query may compare a data set identified as a to a data set identified as b. The job_id field may be used to join data set a to data set b. The SQL query shows identifying data records that include differences in the output_month, scenario_cycle or sandbox columns. The query may order the results by the job_id. 
     Section  408  shows the results of the executed SQL query. Data records that may be identified by job ids  550  and  1208  may include differences between the data record included in the data set identified as a and the data identified as b. 
       FIG.  5    shows illustrative diagram  500 . Illustrative diagram  500  shows exemplary SQL queries. Section  502  may correspond to line numbers in the query. Section  504  may correspond to the language of the query. Section  506  may correspond to the section of the SQL query currently executed. Sections  508  and  510  may correspond to the results of the execution of the SQL query currently executed. 
     There may be two sections to the SQL query shown at  506 . A first section of the SQL query may be executed on a data set identified as a. The result set of the first section of the SQL query may be shown at  508 . A second section of the SQL query may be executed on a data set identified as b. The result set of the second section of the SQL query may be shown at  510 . 
     The first section of the SQL query may include query components. A first component may include selecting the job_id field, the output_month field and the scenario_cycle field from a data set identified as a. A second component may include joining the data set identified as a with the data set identified as b using the job_id field. A third component may include limiting the query to data records where the output_month field or the scenario_cycle field or the sandbox field are not the same in the a data set and the b data set. A fourth component may include ordering the result set first by the job_id field, then by the output_month field and then by the scenario_cycle field. 
     The second section of the SQL query include query components. A first component may include selecting the job_id field, the output_month field and the scenario_cycle field from a data set identified as b. A second component may include joining the data set identified as a with the data set identified as b using the job_id field. A third component may include limiting the query to data records where the output_month field or the scenario_cycle field or the sandbox field are not the same in the a data set and the b data set. A fourth component may include ordering the result set first by the job_id field, then by the output_month field and then by the scenario_cycle field. 
     As viewable at result sets shown in sections  508  and  510 , the data records identified by job identifiers  550  and  1208  may have been assigned 72 in the output_month field of the data set identified as a, and assigned 100 in the output_month field of the data set identified as b. These differences may be viewable to a reader. However, in its current state the system may not be able to present the exact differences to the user. As such, further data manipulation, as shown at  FIG.  7    may be utilized in order for the system to present the differences to the user. 
       FIG.  6    shows illustrative diagram  600 . Illustrative diagram  600  shows exemplary SQL queries. Section  602  may correspond to line numbers in the query. Section  604  may correspond to the language of the query. Section  606  may correspond to the section of the SQL query currently executed. Sections  608  and  610  may correspond to the results of the execution of the SQL query currently executed. 
     There may be two sections to the SQL query shown at  606 . A first section of the SQL query may be executed on a data set identified as a. The result set of the first section of the SQL query may be shown at  608 . The second section of the SQL query may be executed on a data set identified as b. The result set of the second section of the SQL query may be shown at  610 . 
     The first section of the SQL query includes query components. A first component may include selecting the job_id field, the output_month field, the scenario_cycle field and the sandbox field from a data set identified as a. A second component may include joining the data set identified as a with a data set identified as b using the job_id field. A third component may include limiting the query to data records where the output_month field or the scenario_cycle field or the sandbox field are not the same in the a data set and the b data set. A fourth component may include ordering the result set first by the job_id field, then by the output_month field and then by the scenario_cycle field. 
     The second section of the SQL query includes query components. A first component includes selecting the job_id field, the output_month field, the scenario_cycle field and the sandbox field from a data set identified as b. A second component includes joining the data set identified as a with a data set identified as b using the job_id field. A third component includes limiting the query to data records where the output_month field or the scenario_cycle field or the sandbox field are not the same in the a data set and the b data set. A fourth component includes ordering the result set first by the job_id field, then by the output_month field and then by the scenario_cycle field. 
     As viewable at result sets shown in sections  608  and  610 , the data records identified by job identifiers  550  and  1208  may have been assigned 72 in the output_month field of the data set identified as a, and assigned 100 in the output_month field of the data set identified as b. These differences may be viewable to a reader. However, in its current state the system may not be able to present the exact differences to the user. As such, further data manipulation, as shown at  FIG.  7    may be utilized in order for the system to present the differences to the user. 
     It should be noted that result sets  608  and  610  may be combined to enable the system to identify the differences.  FIGS.  7  and  8    show the combination and manipulation of such result sets. 
       FIG.  7    shows an illustrative diagram  700 . Illustrative diagram  700  may include section  702 , section  704 , section  706  and section  708 . Section  702  may be a copy of the result set shown in section  608  in  FIG.  6   . Section  704  may be copy of the result set  610  shown at  FIG.  6   . 
     After obtaining results sets shows in sections  702  and  704 , a next step in identifying differences may include transposing each of the result sets in memory. The transposition of the result set, shown in section  702 , may be shown at  706 . The transposition of the result set, shown in section  704 , may be shown at  708 . 
       FIG.  8    shows an illustrative diagram. Section  802  may correspond to section  702 . Section  804  may correspond to section  704 . Section  806  may correspond to section  706  and section  808  may correspond to section  708 . 
     Section  810  shows a line-by-line comparison between record 1, included in data set a and record 1, included in data set b. Section  810  also shows a line-by-line comparison between record 2, included in data set a and record 2 included in data set b. Such a line-by-line comparison may be easily reviewed and worked on by the system. As such, the system may present to a user the differences between a first big data set and a second big data set. 
       FIG.  9    shows an illustrative diagram. The diagram shows functions that can be used by a user to interface with a data reconciliation system for big data. Title  902  indicates that  FIG.  9    includes the data reconciliation system functions. The functions may be included in a query or in a method that is executed by the system to provide data reconciliation for big data. 
     Code segment  904  shows a sample method that receives parameters. The method may tie out (or determine discrepancies between) two Spark® or Pandas® data frames, as shown at  906 . Spark®, Pandas®, Python® and Hadoop® are infrastructure and resources upon which programs and application are built. Spark® data frames may be used to compare big data sets. Pandas® data frames may be used to compare two small or medium sized data sets. The parameters may be entered by a user. The user-entered parameters may correspond to the data types shown in code segment  904 . 
     The names of the parameters receivable at  904  may include the following: data1, data2, spark, dims, metrics_exclude, metrics_substr_exclude, mumeric_threshold, intersection, fields_mapping, verbose and show_stats). Index  908  describes each of the parameters and associated definitions. 
     Parameter data1 may relate to a Spark® or Pandas® data frame. The Spark® or Pandas® data frame may include a first data set. As such, data1 may refer to a first data set to be compared. The information entered by a user may include an address or memory location of the Spark® or Pandas® data frame that includes the first data set. 
     It should be noted that memory may refer to physical data storage locations. Such physical data storage locations may store data for continuous time periods. 
     Memory may also refer to one or more memory locations into which data sets are read in order to be manipulated by a data reconciliation algorithm. Data loaded into memory locations from which data sets are read may not be held in such memory constantly. Rather, once the data has been manipulated, the data may be removed from such memory. Also, big data sets, such as those that involve a Spark® data frame, may not fit into such memory at one time. Therefore, lazy evaluation may be used to read the data into such memory. Lazy evaluation may be an evaluation strategy which delays the loading of the data until the data is demanded by the algorithm. Lazy evaluation may enable sections of the big data sets to be read into, and manipulated by, the algorithm. Additionally, sections of the big data sets may be read into one or more such memory locations in order to enable parallel execution of the data sets. 
     Parameter data2 may relate to a Spark® or Pandas® data frame. The Spark® or Pandas® data frame may include a second data set. As such, data2 may refer to a second data set to be compared to the first data set. The information entered by a user may include an address or memory location of the Spark® or Pandas® data frame that includes the second data set. 
     Parameter spark may relate to a Spark® instance. The Spark® instance may be identified or located when a Spark® instance is related to data1 or data2. When the spark parameter is set to none, a pandas data frame is being used. 
     Parameter dims may relate to a set of fields, such as a set of rows, a set of columns and/or set of cells with which to join data1 to data2. As such, the data indicated in the dimensions&#39; (“dims”) parameter may be the similarity between data1 to data2. At times, the user may assign a null value to the dims parameter. As such, the system may attempt to locate the set of fields in both data1 and data2 that can be joined. 
     Parameter metrics_exclude may relate to a set of fields that can be excluded from reconciliation. Parameter metrics_exclude may accept a specific field listing, such as row and column line data. Parameter metrics_substr_exclude may also relate to a set of fields that can be excluded from the reconciliation. Parameter metrics_substr_exclude may accept a substring. The substring may indicate a pattern. Fields that match the indicated pattern may be excluded from the reconciliation. 
     Parameter numeric_threshold may relate to a numerical threshold of differences that are referred to herein, for the purpose of this application, as differences. The default value may be 0.01. Such a parameter may be useful when considering whole integer values as opposed to decimal values. In such an embodiment—i.e., an embodiment that compares whole integer values to decimal values—the parameter may be set to 1.0. 
     Parameter intersection_only may, in certain embodiments, identify a set of fields to be used in the comparison. Such a parameter may be used when data1 has 100 values and data2 has 200 values. After the one hundred value comparison between data1 and data2, the system may preferably stop identifying differences. When parameter intersection_only is set to true, the comparison may be restricted to involve the common dataset between data1 and data2. 
     Parameter fields_mapping may be used to identify a dictionary of mapping between two data sets. Parameter field_mapping may be used to compare two data sets that are named differently. At times, a user may attempt compare data1 to data2, however the fields in data1 may be named differently from the fields in data2. Parameter fields_mapping describes a bridge between the two data sets—i.e., which fields in data1 correspond to which fields in data2. 
     Parameter verbose may be used to customize the log message displayed to the user. The verbose parameter may determine how detailed the information is included in the log message. When verbose is set to true, the log message is more detailed. When verbose is set to false, the log message may be less detailed. 
     Parameter show_stats may identify whether or not the identified differences are displayed to the user. The default of show_stats may be set to false. As such, if the user does not indicate whether the differences should be displayed, differences are not displayed. The reason that the default is set to false is that initially a user may determine if there are differences. If there are no differences, there is no need for the parameter show_stats to be set to true. Setting show_stats to false may shorten query time. However, if differences are determined, the query can be executed again with show_stats being set to true. 
     The return indicator indicates a list of three Pandas® data frames. The three Pandas® data frames may include differences samples, differences stats and differences breakdown. 
     It should be appreciated that parameters data1 and data2 may not be optional. 
     Sample methods and parameter identification are shown at  910 . A first sample method may be shown at  912 . Data1 may be selected from a location within a Spark® instance. As such, data1 may be set to spark.sql(select*from wra_gcp_cns_output.cstm2_portfolio_output_alll where job_id=281475′). Data2 may be selected from another location within a Spark® instance. As such, data2 may be set to spark.sql(‘select*from md1_cstm_1.alll_out where job_id=1003’). Data1 and data2 may be used to identify the diff variable. The diff variable may be a variable name that identifies the following method tieout_dfs (data1, data2, spark, dims=[‘accountid’, ‘actdate’], metrics_exclude=[‘insert_ts’, ‘portfolio_id’, ‘portfolio_desc’, ‘job_id’], fields_mapping={‘misc_fees’: ‘miscfee’, ‘billedannualfee’: ‘annualfee’, ‘billedlatefee’: ‘latefee’}). The diff method may compare data1 to data2. The diff method may utilize the account ID and account date field as the join fields. The diff method may exclude the following fields from the comparison: the insert_ts fields, the portfolio ID field, the portfolio description field and the job ID field. Fields mapping parameter shows that misc_fees in data1 corresponds to miscfee is data2, billedannualfee in data1 corresponds to annualfee in data2 and billedlatefee in data1 corresponds to latefee in data2. 
     A second sample method may be shown at  914 . Data1 may be retrieved from pd.read_csv (‘/project/usrwrk/md1/gra/app/model_runner/model_runner/cstm/assumptions/201901_bacb 1.csv’). Data1 may be retrieved from pd.read.csv (‘/project/usrwrk/md1/gra/app/model_runner/model_runner/cstm/assumptions/201901_bacb 1_v3.csv’). The diff variable may identify a tieout_dfs method that accepts the following parameters: data1 and data2. As such, method may compare data1 to data2 using the default values described above. 
       FIGS.  10 - 13    show use cases that utilize the big data reconciliation system. 
       FIGS.  10 A,  10 B,  10 C and  10 D  show a first use case for reconciling data between different systems. A subset of the first use case may include reconciling finance rates data between Vertica® and Hive®, as shown at  1002  in  FIG.  10 A . Vertica® is an analytic database management software. Hive® is a distributed, fault-tolerant data warehouse system that enables data analytics in a big data environment. 
     Code segment  1004  in  FIG.  10 A  shows importing libraries that may be used in the data reconciliation. Code segment  1006  in  FIG.  10 A  shows setting the variables data1 and data2 to specific memory locations. Code segment  1006  in  FIG.  10 A  also shows executing the data reconciliation algorithm between data1 and data2. The instance used to identify the differences may be a Spark® data frame instance. 
     Code segment  1008  in  FIG.  10 A  shows an exemplary stage  14  of a log file produced by executing the executable included in code segment  1006 . Stage  14  shows a match between data1 and data2 indicated by a record count. Stage  14  also shows that a column named ‘insert_ts” is included in data2, however, such a column is not found in data1. 
     Code segment  1010  in  FIG.  10 A  shows an exemplary stage  22  of the log file produced by executing the executable included in code segment  1006 . Stage  22  shows that the record join count matches, and that the records have been reconciled with differences within 0.01 in metric fields and exact match in non-metric fields. 
     Code segment  1012  in  FIG.  10 B  shows data types included in data1. Code segment  1014  in  FIG.  10 B  shows data types included in data2. The system may review the data types included in data1 and data2 to identify a set of dimension fields. Code segment  1016  in  FIG.  10 B  shows dimension fields, included in both data1 and data2, which may be used to join data1 and data2. 
     A subset of the first use case may include reconciling finance rates data between Teradata® and Hive®, as shown at  1018  in  FIG.  10 C . Teradata® is an enterprise analytic database system. Hive® is a distributed, fault-tolerant data warehouse system that enables data analytics in a big data environment. 
     Code segment  1020  in  FIG.  10 C  shows setting the variables data1 and data2 to specific memory locations. Code segment  1020  also shows executing a data reconciliation algorithm between data1 and data2. A Spark® data frame instance may be used to reconcile data1 with data2. The dimension fields may be specified by the user. The dimension field may be accountid. 
     Stage  26 , shown at  1022 , shows that the record counts match. As shown at  1024 , there may be schema differences between data1 and data1. These schema differences may include additional columns in data2. These schema differences may also include mismatched data types which may be excluded from the comparison. The mismatched datatypes may be displayed in the log file shown. Stage  34 , as shown at  1026 , shows that the record join count matches and the joined records have been reconciled within differences within 0.01 in metric fields and exact match in non-metric fields. 
     Code segment  1028  shows a similar code example to code segment  1020 . However, in  1028 , the columns included in data1, that were not able to be reconciled in code segment  1020  because of the mismatched data type, are converted to a string. Accordingly, code segments  1020  and  1032  show that the converted files are able to be reconciled. 
       FIGS.  11 A,  11 B,  11 C,  11 D and  11 E  show a second use case. The second use case shows the impact analysis of the data reconciliation system in model development. 
     A subset of the second use case may include reconciling CSTM V3 201812 Backbook Model Results, as shown at  1102  in  FIG.  11 A . 
     Code segment  1104  in  FIG.  11 A  shows setting the variables data1 and data2 to specific locations in memory. Code segment  1104  also shows converting a subset of fields included data2 to a string data type. Code segment  1104  also shows executing a data reconciliation query. The data reconciliation system may receive parameters. The parameters may include data1 and data2. The dimension parameters, which may be used to join data1 with data2, may be assigned account id and account date. The excluded fields may be insert_ts, job_id and portfolio_id. The query may display the results, as indicated that the show_stats parameter is set to True. 
     At stage  328 , shown at  1106  in  FIG.  11 A , the log file may indicate that the record counts match. At stage  386 , shown at  1108  in  FIG.  11 A , the log file may indicate that there are 94.3% of records with differences. At stage  432 , shown in  1110  in  FIG.  11 A , the log file may indicate that differences exist in 16 out of 22 fields. At stage  482 , shown at  1114  in  FIG.  11 B , the log file indicates the records with the differences. Numeric values indicated by  1116  in  FIG.  11 B and  1118    in  FIG.  11 B  show significant differences. 
     A second subset of the second use case may include Reconciling CSTM v3 201812 Backbook Portfolio Data, as shown at  1119  in  FIG.  11 C . 
     Code segment  1120  shows setting each of the data1 and data2 to specific memory locations or retrieving each of the data1 and data2 to specific memory locations. Code segment  1120  may also include executing a data reconciliation query. The data reconciliation query may utilize data1 and data2. The query may utilize an account identifier as the dimension field. The query may map the field prod_acct_no_tu_ccba to the account id field. The query may display the results to the user, as shown at show_stats is set to true. 
     Code segment  1122  shows that record count does not match. However, the record may be able to be joined. Code segment  112  shows that 100% of the records may have differences. 
     Stage  76 , shown at  1124  in  FIG.  11 C , shows the fields in which the differences are located. Stage  117 , shown at  1126  in  FIG.  11 D  shows a listing of the differences. Stage  168 , shown at  1128  in  FIG.  11 E  shows the breakdown of differences in 10 non-numeric fields. The breakdown may involve a count of the differences between data1 and data2. 
       FIGS.  12 A and  12 B  show a third use case. The third use case may involve reconciling macroeconomic data from EVER downloads. The third use case may involve a sub-use case. The sub-use case may include reconciling different versions of macroeconomic data from EVER download, as shown at  1202  in  FIG.  12 A . 
     Code segment  1204  in  FIG.  12 A  may show selecting and displaying data from a spark data frame in order for a user to view the data. 
     Code segment  1208  in  FIG.  12 A  may involve assigning the data1 and data2 variables data from the selected Spark® data frame. Code segment  1208  may also involve identifying variables within the data reconciliation query. 
     Stage  166  shown at  1210  in  FIG.  12 A  shows that the record counts do not match between the two data sets. However, stage  174  shown at  1212  shows that the records can be joined and that the joined records have been reconciled. 
     A second sub-use case of the third use case may involve reconciling macro data in a Pandas® data frame, as shown at  1214  in  FIG.  12 B . Code segment  1216  in  FIG.  12 B  involves setting each of data1 and data2 to specific variables. Code segment  1216  in  FIG.  12 B  may also involve a data reconciliation query that compares data1 to data2. The show_stats parameter may be set to true. Therefore, the results of the query are displayed to a user as shown at  1218 . 
       FIGS.  13 A,  13 B and  13 C  show a fourth use case for the data reconciliation query system. Such a use case may be integrated in Pytest® for Test-Driven Development. Pytest® may be a testing framework based on Python®.  FIG.  13 A  shows code segment  1302 . Code segment  1302  involves assigning memory locations to data1 and data2 and executing a data reconciliation query. 
       FIG.  13 B  shows code segments  1304  and  1306 . Code segments  1304  and  1306  involves assigning memory locations to data1 and data2 and executing a data reconciliation query. 
       FIG.  13 C  shows code segment  1308  and  1310 . Code segment  1308  involves assigning memory locations to data1 and data2 and executing a data reconciliation query. 
       FIG.  14    shows an illustrative block diagram of system  1400  that includes computer  1401 . Computer  1401  may alternatively be referred to herein as an “engine,” “server” or a “computing device.” Computer  1401  may be a workstation, desktop, laptop, tablet, smartphone, or any other suitable computing device. Elements of system  1400 , including computer  1401 , may be used to implement various aspects of the systems and methods disclosed herein. Each of the data reconciliation systems, methods and algorithms illustrated in  FIGS.  1 - 13    may include some or all of the elements and apparatus of system  1400 . 
     Computer  1401  may have a processor  1403  for controlling the operation of the device and its associated components, and may include RAM  1405 , ROM  1407 , input/output circuit  1409 , and a non-transitory or non-volatile memory  1415 . Machine-readable memory may be configured to store information in machine-readable data structures. The processor  1403  may also execute all software running on the computer. Other components commonly used for computers, such as EEPROM or Flash memory or any other suitable components, may also be part of the computer  1401 . 
     The memory  1415  may be comprised of any suitable permanent storage technology—e.g., a hard drive. The memory  1415  may store software including the operating system  1417  and application(s)  1419  along with any data  1411  needed for the operation of the system  1400 . Memory  1415  may also store videos, text, and/or audio assistance files. The data stored in memory  1415  may also be stored in cache memory, or any other suitable memory. 
     Input/output (“I/O”) module  1409  may include connectivity to a microphone, keyboard, touch screen, mouse, and/or stylus through which input may be provided into computer  1401 . The input may include input relating to cursor movement. The input/output module may also include one or more speakers for providing audio output and a video display device for providing textual, audio, audiovisual, and/or graphical output. The input and output may be related to computer application functionality. 
     System  1400  may be connected to other systems via a local area network (LAN) interface  1413 . System  1400  may operate in a networked environment supporting connections to one or more remote computers, such as terminals  1421  and  1423 . Terminals  1421  and  1423  may be personal computers or servers that include many or all of the elements described above relative to system  1400 . The network connections depicted in  FIG.  14    include a local area network (LAN)  1425  and a wide area network (WAN)  1429  but may also include other networks. When used in a LAN networking environment, computer  1401  is connected to LAN  1425  through a LAN interface  1413  or an adapter. When used in a WAN networking environment, computer  1401  may include a modem  1427  or other means for establishing communications over WAN  1429 , such as Internet  1431 . 
     It will be appreciated that the network connections shown are illustrative and other means of establishing a communications link between computers may be used. The existence of various well-known protocols such as TCP/IP, Ethernet, FTP, HTTP and the like is presumed, and the system can be operated in a client-server configuration to permit retrieval of data from a web-based server or application programming interface (API). Web-based, for the purposes of this application, is to be understood to include a cloud-based system. The web-based server may transmit data to any other suitable computer system. The web-based server may also send computer-readable instructions, together with the data, to any suitable computer system. The computer-readable instructions may be to store the data in cache memory, the hard drive, secondary memory, or any other suitable memory. 
     Additionally, application program(s)  1419 , which may be used by computer  1401 , may include computer executable instructions for invoking functionality related to communication, such as e-mail, Short Message Service (SMS), and voice input and speech recognition applications. Application program(s)  1419  (which may be alternatively referred to herein as “plugins,” “applications,” or “apps”) may include computer executable instructions for invoking functionality related to performing various tasks. Application program(s)  1419  may utilize one or more algorithms that process received executable instructions, perform power management routines or other suitable tasks. Application program(s)  1419  may utilize one or more decisioning processes for the processing of calls received from calling sources as detailed herein. 
     Application program(s)  1419  may include computer executable instructions (alternatively referred to as “programs”). The computer executable instructions may be embodied in hardware or firmware (not shown). The computer  1401  may execute the instructions embodied by the application program(s)  220  to perform various functions. 
     Application program(s)  1419  may utilize the computer-executable instructions executed by a processor. Generally, programs include routines, programs, objects, components, data structures, etc. that perform particular tasks or implement particular abstract data types. A computing system may be operational with distributed computing environments where tasks are performed by remote processing devices that are linked through a communications network. In a distributed computing environment, a program may be located in both local and remote computer storage media including memory storage devices. Computing systems may rely on a network of remote servers hosted on the Internet to store, manage, and process data (e.g., “cloud computing” and/or “fog computing”). 
     Any information described above in connection with data  1411 , and any other suitable information, may be stored in memory  1415 . 
     The invention may be described in the context of computer-executable instructions, such as application(s)  1419 , being executed by a computer. Generally, programs include routines, programs, objects, components, data structures, etc., that perform particular tasks or implement particular data types. The invention may also be practiced in distributed computing environments where tasks are performed by remote processing devices that are linked through a communications network. In a distributed computing environment, programs may be located in both local and remote computer storage media including memory storage devices. It should be noted that such programs may be considered, for the purposes of this application, as engines with respect to the performance of the particular tasks to which the programs are assigned. 
     Computer  1401  and/or terminals  1421  and  1423  may also include various other components, such as a battery, speaker, and/or antennas (not shown). Components of computer system  1401  may be linked by a system bus, wirelessly or by other suitable interconnections. Components of computer system  1401  may be present on one or more circuit boards. In some embodiments, the components may be integrated into a single chip. The chip may be silicon-based. 
     Terminal  1421  and/or terminal  1423  may be portable devices such as a laptop, cell phone, tablet, smartphone, or any other computing system for receiving, storing, transmitting and/or displaying relevant information. Terminal  1421  and/or terminal  1423  may be one or more user devices. Terminals  1421  and  1423  may be identical to system  1400  or different. The differences may be related to hardware components and/or software components. 
     The invention may be operational with numerous other general purpose or special purpose computing system environments or configurations. Examples of well-known computing systems, environments, and/or configurations that may be suitable for use with the invention include, but are not limited to, personal computers, server computers, hand-held or laptop devices, tablets, mobile phones, smart phones and/or other personal digital assistants (“PDAs”), multiprocessor systems, microprocessor-based systems, cloud-based systems, programmable consumer electronics, network PCs, minicomputers, mainframe computers, distributed computing environments that include any of the above systems or devices, and the like. 
       FIG.  15    shows illustrative apparatus  1500  that may be configured in accordance with the principles of the disclosure. Apparatus  1500  may be a computing device. Apparatus  1500  may include one or more features of the apparatus shown in  FIG.  14   . Apparatus  1500  may include chip module  1502 , which may include one or more integrated circuits, and which may include logic configured to perform any other suitable logical operations. 
     Apparatus  1500  may include one or more of the following components: I/O circuitry  1504 , which may include a transmitter device and a receiver device and may interface with fiber optic cable, coaxial cable, telephone lines, wireless devices, PHY layer hardware, a keypad/display control device or any other suitable media or devices; peripheral devices  1506 , which may include counter timers, real-time timers, power-on reset generators or any other suitable peripheral devices; logical processing device  1508 , which may compute data structural information and structural parameters of the data; and machine-readable memory  1510 . 
     Machine-readable memory  1510  may be configured to store in machine-readable data structures: machine executable instructions, (which may be alternatively referred to herein as “computer instructions” or “computer code”), applications such as applications  1419 , signals, and/or any other suitable information or data structures. 
     Components  1502 ,  1504 ,  1506 ,  1508  and  1510  may be coupled together by a system bus or other interconnections  1512  and may be present on one or more circuit boards such as circuit board  1514 . In some embodiments, the components may be integrated into a single chip. The chip may be silicon-based. 
     Thus, a big data reconciliation system is provided. Persons skilled in the art will appreciate that the present invention can be practiced by other than the described embodiments, which are presented for purposes of illustration rather than of limitation. The present invention is limited only by the claims that follow.