Inferring Implicit Relationships Between Table Columns

Recent years have seen an increase in the use of computing devices (e.g., mobile devices, personal computers, server devices) to create, store, edit, and share data. For example, tools and applications for creating data tables and other structured data are increasingly common. As spreadsheet applications, charting applications, business intelligence (BI) products, and other applications that make use of tables (and other structured datasets) become more prevalent, so too does the frequency with which data is ported between different types of structured data sets.

For example, a relational table may be created using functions and transformations to generate derived columns—producing rich inter-column relationships within the same table. However, these rich relationships are typically lost once the table is exported and saved into a standard format such as CSV. As such, the inter-column relationships captured in the original table become unusable to leverage in downstream use cases such as for table understanding, data cleaning, and data provenance.

The subject matter in the background section is intended to provide an overview of the overall context for the subject matter disclosed herein. The subject matter discussed in the background section should not be assumed to be prior art merely as a result of its mention in the background section. Similarly, a problem mentioned in the background section or associated with the subject matter of the background section should not be assumed to have been previously recognized in the prior art.

The present disclosure relates to systems and methods for automatically inferring implicit relationships between columns of a single data table. As discussed above, existing systems fail to maintain inter-column relationships when data is transferred from one structured data format to another. In an effort to rebuild these relationships, such existing systems are limited to inferring single types of potential relationships between columns (e.g., arithmetic relationships), and relying on human experts to make final determinations.

Some automated inference systems apply known arithmetic functions, string transformations, and other functional dependencies to input tables to identify relationships between data table columns. For example, these automated inference systems can enumerate all possible column combinations as well as all possible relationships between those columns and test whether a candidate relation holds on a column combination from an input table. This approach, however, leads to large numbers of “false-positive” or spuriously identified column relationships. These spurious relationships appear to hold on a particular table instance, but do not make semantic sense-nor do they hold in general. The likelihood of such spurious relationships increases with the complexity of the relationships identified among the columns of an input table. Additionally, the problems with spurious relationships are further exacerbated when the data in the input table is not “clean,” but rather includes errors, typos, and so forth.

To solve these problems, a functional relationship system discussed herein provides a unified framework for inferring true-positive or semantically-accurate functional relationships between columns of an input table. For example, the functional relationship system develops principled statistical tests based on a hypothetical analysis that slightly “perturbs” values in an input table in order to determine the strength of a functional relationship candidate.

As will be described in greater detail below, the functional relationship system operates under an assumption that a functional relationship candidate will hold on an input table only “by chance.” The functional relationship system then performs a slight “perturbation” of the input table. For example, the functional relationship system may modify select cells or fields of the table or specific column(s) in a variety of ways. The functional relationship system determines that a functional relationship candidate is genuine on the input table when the probability of the functional relationship candidate holding on the slightly perturbed input table is exceedingly small (e.g., below a threshold probability or score). Thus, the functional relationship system can determine that an underlying semantic relationship likely exists when the corresponding functional relationship candidate fails to hold following the perturbation. As such, the functional relationship system can reliably infer diverse types of functional relationships between columns of an input table.

As such, the functional relationship system discussed herein provides improved flexibility and accuracy over existing systems. For example, where existing systems often identify inaccurate, false-positive column relationships, the functional relationship system can infer rich and diverse inter-column relationships with a high degree of accuracy. By leveraging perturbation and statistical measurement, the functional relationship system infers true-positive column relationships that may have been lost through table combinations, data transfers, and so forth.

In one or more embodiments, the increased accuracy generated by the functional relationship system leads to additional improvements. For example, the accurate inter-column functional relationships inferred by the functional relationship system can serve as rich data-quality constraints. For example, these inter-column functional relationships can serve as data-quality constraints by data cleaning algorithms to detect and repair table data.

Furthermore, the accurate inter-column functional relationships inferred by the functional relationship system can help ensure better data consistency. For example, in spreadsheet tables, it is common for users to edit cell values, which can lead to cascading changes in dependent data cells. When such dependencies are not auto-populated and enforced, users often have to manually edit all dependent cells to maintain consistency, which is error-prone. By automatically inferring and enforcing inter-column relationships, the functional relationship system can increase the data consistency and data quality reflected by the table.

In one or more implementations, the methods and steps performed by the unsupervised error detection system reference multiple terms. For example, as used herein, an “input table” refers to a collection of data items that are organized into rows and columns.

As used herein, a “functional relationship” refers to a connection between two or more sets of data. For example, a functional relationship can exist between data items in a first column of an input table and data items in a second column of the input table. Functional relationships can indicate a connection between multiple columns of the same table and can have different types. Some non-limiting examples of functional relationships will be discussed below.

For example, functional relationships can be arithmetic relationships where the connection between the two or more columns is mathematical. Additionally, functional relationships can be string transformation relationships where the connection between the two or more columns is some type of string transformation (e.g., a concatenation, an extraction, a joining, a splitting, case conversions, etc.). Furthermore, functional relationships can be functional dependencies where the connection between the two or more columns is some type of function (e.g., a part to a whole, a zip code to a state, an occupation to a professional license). Additionally, as used herein a “functional relationship candidate” refers to a potential functional relationship identified within an input table. Functional relationship candidates may be true-positive or false-positive, but most likely functional relationship candidates are those that have probability scores indicating that they are true-positive.

1 FIG. 2 FIG. 3 3 FIGS.A-C 4 4 FIGS.A-C 5 FIG. 6 FIG. 7 FIG. 102 Additional details regarding example implementations of the unsupervised error detection system will now be discussed in connection with the following figures. To illustrate,provides an example overview of a networked environment where the functional relationship system operates to determine functional relationships between columns of an input table.illustrates steps taken by the functional relationship system to generate a set of most likely functional relationships between columns of an input table.illustrate examples of different types of functional relationships that can be inferred by the functional relationship system.illustrate additional detail in connection with the statistical measurements performed by the functional relationship system while inferring inter-column relationships within an input table.illustrates additional detail in connection with the functional relationship system. Finally,illustrates a series of acts for automatically inferring inter-column functional relationships, whileillustrates certain components that may be included within a computer system.

1 FIG. 1 FIG. 100 102 104 106 102 112 114 116 118 104 116 106 118 108 120 110 122 As just mentioned,illustrates an example overview environmentincluding a functional relationship systemoperating within a memoryon a server(s). As further shown in, the functional relationship systemworks in combination with a functional relationship system pluginoperating as part of a data table applicationwithin a memoryon a client device. In addition to the memories,, the server(s)and the client devicecan also include additional items,, and processor(s),, respectively.

1 FIG. 102 112 124 124 As further shown in, the functional relationship systemand the functional relationship system pluginmay be communicatively coupled through the network. In one or more implementations, the networkmay represent any type or form of communication network, such as the Internet, and may include one or more physical connections, such as a LAN, and/or wireless connections, such as a WAN.

1 FIG. 100 102 114 118 102 112 118 102 112 112 112 Althoughillustrates components of the environmentin one arrangement, other arrangements are possible. For example, in one embodiment, the functional relationship systemmay operate as part of the data table applicationon the client device. Additionally or alternatively, some of the functionality of the functional relationship systemmay be performed by the functional relationship system pluginon the client device. For example, in some embodiments, the functional relationship systemmay generate a list of inferred functional relationships associated with an input table received from the functional relationship system pluginand provide that list back to the functional relationship system plugin. At that point, the functional relationship system pluginmay generate a display illustrating the functional relationships indicated by the list and update an existing display of the input table to include this illustration of the functional relationships.

2 FIG. 2 FIG. 200 102 102 202 102 illustrates an overview of a sequenceof acts performed by the functional relationship systemin automatically determining relationships between columns of a data table. For example, as shown in, the functional relationship systemcan receive an input table in an act. In one or more embodiments, the functional relationship systemcan receive the input table in any format that indicates columns and rows of data items. The data items in the input table can include alpha-numeric characters and/or strings of alpha-numeric characters.

204 102 102 102 102 102 In an act, the functional relationship systemcan generate functional relationship candidates based on the input table. For example, the functional relationship systemcan generate functional relationship candidates by identifying potential relationships among the columns of the input table. To illustrate, the functional relationship systemcan identify potential arithmetic relationships, potential string transformation relationships, and potential functional dependency relationships among the columns of the input table. In at least one embodiment, the functional relationship systemidentifies these functional relationship candidates by applying known arithmetic functions, string transformations, and functional dependencies to the columns of the input table. The functional relationship systemthen determines that a functional relationship candidate is a function, transformation, and/or dependency that holds on two or more columns of the input table across a threshold number of rows of the input table (e.g., holds on at least 85% of the input table rows).

102 102 206 102 102 In one or more embodiments, the functional relationship systemoperates under the assumption that functional relationship candidates hold on the input table only by chance. As such, the functional relationship systemperforms statistical probability tests on the functional relationship candidates in an actto identify the functional relationships that most likely exist among the columns of the input table. For example, and as will be discussed in greater detail below, the functional relationship systemidentifies spurious relationships represented among the functional relationship candidates by performing tests including perturbation and minimality tests. In at least one embodiment, the functional relationship systemfurther narrows the functional relationship candidates down to a final group of candidates by identifying functional relationship candidates with probability scores that are better than a threshold score.

102 208 102 114 102 102 102 Finally, the functional relationship systemperforms an actof generating a display of the most likely functional relationship candidates in connection with the input table. For example, the functional relationship systemcan generate a display including an overlay or side listing the most likely functional relationship candidates within the data table application. In another embodiment, the functional relationship systemupdates or modifies the input table to indicate the most likely functional relationship candidates. For example, the functional relationship systemcan color-code columns of the input table that correspond to a most likely functional relationship candidate. In another example, the functional relationship systemcan add a row to the input table that indicate the most likely functional relationship candidates.

3 FIG.A 300 300 302 302 302 302 302 302 302 302 302 302 302 302 3021 302 302 302 302 302 304 304 304 304 a b c d e f g h h i j k m n o a o a b c a To further illustrate how functional relationships can exist between columns of a data table,shows a data tableincluding data items in rows across a number of columns. For example, the data tablehas columns,,,,,,,,,,,,,,, and. Each of the columns-may be associated with one or more data types (e.g., numbers, characters, strings, etc.) such that data items falling under each column generally have the same data type. To further explain, data items within each of the rows,,, and so forth are associated with nursing and rehabilitation facilities. For example, the rowindicates types of care and fees for the various types of care for a single facility.

300 302 304 302 304 302 304 302 302 302 302 302 302 302 302 302 302 302 302 f a g a h a f g h f f h i j k l m n. Further inspection reveals that certain functional relationships exist between columns within the data table. For example, a number of days indicated under the columnfor the row(e.g., 283 days) multiplied by a rate per day under the columnfor the same row(e.g., $282 per day) results in the revenue data item under the columnfor the row(e.g., $7,992,400). As such, it can be said that the columns,, andshare an arithmetic relationships-denoted as columnmultiplied by the columnresults in the column. The same relationship may be found among the columns,, and, as well as among the columns,, and

300 300 302 302 302 302 302 302 3 FIG. b e b c d e. While these correct or true-positive arithmetic relationships may be intuitively apparent to a human analyst, automatic recognition of arithmetic relationships in the data tablemay also lead to false-positive results. For example, as shown in, the data tablefurther includes binary (e.g., yes/no) indications of types of specialty care offered by various facilities across the columns-. Because many of the facilities represented fail to offer any type of specialty care, automatic functional relationship recognition may result in the identification of false-positive arithmetic relationships such as the columnplus the columnplus the columnequals the column

300 306 308 308 308 308 308 308 308 308 308 308 308 308 308 308 308 308 308 308 308 310 310 310 310 308 308 308 308 3 FIG.B a b c d e f g h i j k l m n o e b c d a a b c e b c d Similar to the true-positive and false-positive arithmetic relationships shown in connection with the data table, other data tables can include different types of functional relationships. For example, as shown in, a data tableincludes data items that strings and other alpha-numeric characters. In one or more embodiments, string transformation relationships can exist between a subset of columns,,,,,,,,,,,,,, and. To illustrate, data items under the columnmay include a concatenation of data items from the columns,, and. For instance, the data item in the row(of rows,, and) under the column(e.g., “ElmIL_60126”) is a concatenation of the data items from the same row under the column(e.g., “Elmhurst”), the column(e.g., “IL”), and the column(e.g., “60126-1128”).

300 306 308 308 308 308 308 3 FIG.A 3 FIG.B i j j i j As with the tableshown in, some of the string transformation relationships that potentially exist in the data tableare true-positive relationships, while others are false-positive relationships. For example, as further shown in, the data items under the columnindicate a distribution type of “National.” Most of the data items under the columnindicate “N,” which may automatically be recognized as a truncation of the word “National.” The string transformation relationship of columnbeing a truncation of the column, however, would be a false-positive relationship as the columnis actually a binary value (e.g., Yes/No) associated with whether or not the entity associated with the corresponding row imports goods.

3 FIG.C 312 314 314 312 314 314 316 316 316 316 314 314 314 314 a o f g a a b c f g f g. Furthermore, data tables may include functional dependencies between columns. To illustrate,shows a data tableincluding data items across multiple columns-. In one or more embodiments, the data tableincludes true-positive functional dependencies such as between columnsand. For example, as shown in row(of rows,, and), a functional relationship exists between the data item in the column(e.g., “Australia”) and the column(e.g., “South Pacific”) because the country represented in the columnexists within the region represented in the column

312 312 314 314 314 314 314 314 m n m n m n In addition to the true-positive functional dependencies that exist within the data table, the data tablealso includes false-positive functional dependencies. For example, the data items under the columnall include a value of “0.” Additionally, the data items under the columnall include a value of “stable.” In some embodiments, a system may determine that a functional dependency of “0=stable” exists between the columnand the column. A human operator, however, might intuitively understand that the columnincludes a binary, yes/no indication of whether an airline has experienced a downgrade while the columnindicates whether the same airline's business outlook is stable. As such, these columns actually do not share a functional dependency.

102 102 As mentioned above, the functional relationship systemautomatically determines most likely true-positive functional relationships between columns of an input table. For example, where existing systems are limited to identifying all potential relationships between columns of an input table with little to no accuracy as to whether those potential relationships are true-positive relationships or false-positive relationships, the functional relationship systemcan identify true-positive column relationships in a manner that is highly accurate and explainable.

4 4 FIGS.A-C 4 FIG.A 2 FIG. 102 102 102 102 102 illustrate additional detail with regard to how the functional relationship systemautomatically determines these true-positive functional relationships. For example,illustrates initial steps taken by the functional relationship systemin generating functional relationship candidates that represent potential functional relationships between input table columns. As mentioned above in connection with, the functional relationship systemfirst generates a set of functional relationship candidates for an input table by applying series of potential arithmetic formulas, string transformations, and functional relationships to the columns of the input table. If, for example, the functional relationship systemdetermines that a particular arithmetic formula holds at a threshold rate between two or more columns of the input table (e.g., holds on more than eighty percent of the data items between the two or more columns), the functional relationship systemcan use that arithmetic formula and the two or more columns as a functional relationship candidate.

102 102 102 As such, the resulting set of functional relationship candidates across all possible arithmetic formulas, string transformations, and functional dependencies may be very large. Accordingly, the functional relationship systemcan first identify a minimal subset of the total number of functional relationship candidates by utilizing a minimality test. For example, the functional relationship systemcan identify functional relationship candidates that represent the simplest possible form. Put another way, the functional relationship systemidentifies functional relationship candidates that involve the smallest number of input table columns while still holding true.

102 102 102 402 4 FIG.A Once the functional relationship systemhas identified the functional relationship candidates that satisfy the minimality test, the functional relationship systemnext performs a perturbation test to identify functional relationship candidates that lead to true-positive results and those that lead to false-positive results. For example, as shown in, the functional relationship systemperforms the perturbation test in connection with a functional relationship candidate by first performing a stepof selecting two rows of the input table (or a subset of one or more columns within an input table) to serve as perturbation rows.

102 404 406 102 408 102 402 406 Next, the functional relationship systemcontinues by performing a stepof selecting two or more columns that participate in the functional relationship candidate to serve as perturbation columns. Following this, in a step, the functional relationship systemcan swap the data items from the perturbed rows across the perturbed columns and then verify whether the functional relationship candidate still holds on the rows including the now-swapped data items. In a step, the functional relationship systemrepeats the steps-and uses the rate at which the functional relationship candidate holds as the probability score for the functional relationship candidate indicating how likely it is that the functional relationship candidate is true-positive.

300 302 302 302 102 304 304 300 302 302 102 302 302 304 304 102 304 302 304 302 304 302 102 304 302 304 302 304 302 102 300 3 FIG.A b c e a b b c b c a b b b b c a e a b a c b e To further illustrate the perturbation test, a functional relationship candidate associated with the data tableshown inis the columnmultiplied by the columnequals the column. During the perturbation test on this functional relationship candidate, the functional relationship systemselects the rowsandfrom the data tableas perturbation rows and uses the columnsandas perturbation columns. The functional relationship systemnext swaps the data items from the columns,across the rows,. Thus, the functional relationship systemnow determines whether “0” (e.g., the data item from the rowand column) times “1” (e.g., the data item from the rowand column) equals “0” (e.g., the data item from the rowand column). Similarly, the functional relationship systemdetermines whether “0” (e.g., the data item from the rowand column) times “0” (e.g., the data item from the rowand column) equals “0” (e.g., the data item from the rowand column). The functional relationship systemcan then repeat these steps for additional rows in the data tableusing the same functional relationship candidate.

102 300 102 302 302 302 102 102 b c e 5 FIG. The functional relationship systemcan determine that this functional relationship candidate holds following these perturbation steps for a threshold number of rows in the data table. In one or more embodiments, the functional relationship systemoperates under an assumption that true-positive functional relationship candidates will largely fail the perturbation test. As such, upon determining that the functional relationship candidate including the columnmultiplied by the columnequals the columnlargely passes the perturbation test, the functional relationship systemcan determine that this functional relationship candidate is a false-positive functional relationship candidate and should not be considered further. As will be discussed further below in connection with, the functional relationship systemcan perform these same perturbation test steps in connection with arithmetic functional relationship candidates, string transformation functional relationship candidates, and functional dependency functional relationship candidates.

102 410 412 412 412 412 4 FIG.B c d a b In one or more embodiments, the functional relationship systemcan identify true-positive functional relationship candidates in connection with “clean” data sets (e.g., error-free data sets) as well as in connection with “dirty” or “noisy” data sets (e.g., data sets with mistakes, typos, etc.). In real-world data sets, noise can be introduced through data collection processes, human input errors, and inconsistencies during data integration. To illustrate, in, the tableincludes noisy data such as the data itemsand(e.g., may include duplicates), the data item(e.g., may include a typo), and the data item(e.g., may include an outlier compared to other data items in the same column).

102 102 To handle “dirty” data, the functional relationship systemcan apply an independence test to filter non-independent functional relationship candidates in the presence of noise. In one or more embodiments, the functional relationship systemoperates under the assumption that a non-independent functional relationship candidate is also unreliable (i.e., a false-positive functional relationship candidate).

4 FIG.C 102 414 102 414 102 a b illustrates steps taken by the functional relationship systemin performing the independence test in connection with a functional relationship candidate. For example, in a step, the functional relationship systemcan construct a list representing the validity of the functional relationship candidate for each row in the input table (e.g., using 0 for invalid and 1 for valid). Following this, in a step, the functional relationship systemcan traverse every column in the input table that is not part of the functional relationship candidate.

414 102 414 102 102 102 c d 5 FIG. In a step, the functional relationship systemcan create a contingency table between the traversed columns and the list of validity data previously constructed. Finally, in a step, the functional relationship systemcan apply the Chi-Square test on the contingency table to assess the independence between the traversed columns and the list of validity data. In one or more embodiments, the functional relationship systemutilizes the results of the Chi-Square test to filter out the functional relationship candidate if any traversed column (e.g., a column not included in the functional relationship candidate) is dependent on the list of validity data. Conversely, if no traversed column is dependent on the list of validity data, the functional relationship systemcan determine that the functional relationship candidate passes the independence test and can move forward through the perturbation test. Additional detail with regard to the independence test is given below in connection with.

5 FIG. 5 FIG. 5 FIG. 5 FIG. 102 102 500 102 104 106 102 102 502 504 506 508 As mentioned above, and as shown in, the functional relationship systemaccurately infers inter-column relationships for an input table. For example, the inter-column relationships inferred by the functional relationship systemextend to arithmetic relationships, string transformation relationships, and functional dependency relationships.is a block diagramof the functional relationship systemoperating within the memoryof the server(s). As such,provides additional detail with regard to the functions performed by the functional relationship system. For example, as shown in, the functional relationship systemcan include a communication manager, a functional relationship candidate creation manager, a functional relationship candidate testing manager, and a display manager.

102 502 504 506 508 106 502 504 506 508 5 FIG. In certain implementations, the functional relationship systemmay represent one or more software applications, modules, or programs that, when executed by a computing device, may cause the computing device to perform one or more tasks. For example, and as will be described in greater detail below, one or more of the communication manager, the functional relationship candidate creation manager, the functional relationship candidate testing manager, and the display managermay represent software stored and configured to run on one or more computing devices, such as the server(s). Any of the communication manager, the functional relationship candidate creation manager, the functional relationship candidate testing manager, and the display managershown inmay also represent all or portions of one or more special purpose computers to perform one or more operations.

5 FIG. 1 FIG. 102 502 502 102 100 502 502 114 118 502 114 118 124 502 114 118 As mentioned above, and as shown in, the functional relationship systemincludes the communication manager. In one or more embodiments, the communication managerhandles communication tasks between the functional relationship systemand the other devices illustrated in the overview environment(e.g., as shown in). For example, the communication managercan detect or receive indications of a selected input table. To illustrate, the communication managercan detect a user selection of an input table within the data table applicationon the client device. Similarly, the communication managercan receive an input table selected via the data table applicationon the client deviceand transmitted via the network. Additionally, in some embodiments, the communication managercan look up a input table within a cloud-based repository in response to detecting a user indication of the input table via the data table applicationon the client device.

502 114 118 102 502 114 114 Additionally, the communication managercan further transmit display instructions to the data table applicationon the client device. For example, additional components of the functional relationship systemcan inferred one or more functional relationships between table columns of the input table and generate display instructions based on these inferred functional relationships. In one or more embodiments, the communication managercan transmit such display instructions to the data table applicationthat cause the data table applicationto generate or update a display associated with the input table to indicate the inferred functional relationships.

5 FIG. 102 504 504 502 504 As mentioned above, and as shown in, the functional relationship systemincludes the functional relationship candidate creation manager. In one or more embodiments, the functional relationship candidate creation managergenerates a set of functional relationship candidates for an input table received by the communication manager. For example, the functional relationship candidate creation managercan discover functional relationship candidates by applying known arithmetic functions, string-transformations, and functional dependencies to the columns of the input table to identify which functions, transformations, and dependencies hold on the columns of the input table.

y r r Given a table T with a set of columns C, column subsets X, Y⊆C. A Functional Relationship Ψ=(X, ƒ) is composed of input columns set X, output columns set Y and a mapping function ƒ. In more detail, a functional relationship candidate can refer to a mapping between different columns in tabular data that illustrates how input values (one or more columns in the data) are transformed into output values (other columns). A formal definition of a functional relationship candidate can include the following:

504 504 In one or more embodiments, the functional relationship candidate creation managerconsiders V(X), V(Y) as the domains of X, Y, respectively. For all x∈V(X) and y∈V(Y), the functional relationship candidate creation managerdetermines that a functional relationship candidate holds if;

y r Ψ 1 2 Ψ 1 Ψ 2 1 2 504 504 A functional relationship candidate Ψ=(X, ƒ) is said to be non-trivial if YX. A functional relationship candidate is said to be normalized if the output columns set has a single column. The functional relationship candidate creation managerconsiders C=X∪Y as the participative columns set of the functional relationship candidate Ψ. For two functional relationship candidates Ψand Ψ, if C=C, then Ψand Ψare said to be isomerous. In one or more embodiments, the functional relationship candidate creation managerconsiders only the set of non-trivial, normalized functional relationship candidates as that set can be used to infer all other functional relationship candidates that hold on the input table (T).

504 504 504 When input and output columns for a functional relationship candidate are numeric data, the functional relationship candidate creation managercan determine that the functional relationship candidate is an arithmetic relationship (e.g., addition, subtraction, multiplication, division). When input and output columns for a functional relationship candidate are textual data, the functional relationship candidate creation manageran determine that the functional relationship candidate is a string transformation (e.g., split, substring, concatenate). In one or more embodiments, the functional relationship candidate creation managercan utilize functional dependencies to describe functional relationship candidates that involve categorical data. For example, a functional relationship candidate that includes a functional dependency can involve both numeric and textual data.

5 FIG. 102 506 506 504 506 As mentioned above, and as shown in, the functional relationship systemincludes the functional relationship candidate testing manager. In one or more embodiments, the functional relationship candidate testing managermeasures each of the functional relationship candidates generated by the functional relationship candidate creation managerfor the input table. For example, the functional relationship candidate testing managercan utilize these measurements to identify a subset of the functional relationship candidates representing most-likely inter-column relationships for the input table.

506 506 506 In at least one embodiment, the functional relationship candidate testing managercan determine measurements on clean data (e.g., input table data with no noise or errors). Under this scenario, the functional relationship candidate testing managercan assume that each functional relationship candidate in the set of functional relationship candidates (Ry) holds on all rows of the input table. Under the clean data assumption, the functional relationship candidate testing managercan identify most-likely functional relationship candidates for the input table by performing a minimality test followed by a perturbation test on each functional relationship candidate identified on the input table.

506 506 506 506 Ψ Ψ′ Ψ′ Ψ In one or more embodiments, the functional relationship candidate testing managerperforms the minimality test to identify a set of functional relationship candidates that represent the minimal subset of functional relationship candidates for the input table. For example, the functional relationship candidate testing managerconsiders a functional relationship candidate Ψ with a participative column set C. If there exists a functional relationship candidate Ψ′ with participative column set C, and C⊆C, the functional relationship candidate testing managerutilizes the minimality test to filter out Ψ. If no such Ψ′ exists, the functional relationship candidate testing managercan determine that Ψ passes the minimality test.

3 FIG.A 300 302 302 302 506 302 506 506 H H f g b b To illustrate, as shown in, a functional relationship candidate for the data table(e.g., H) can include Ψ=({F, G, B}, F*G+B) (e.g., for the columns F=column, G=column, and B=column). In one or more embodiments, the functional relationship candidate testing managercan decompose this functional relationship candidate into Ψ=({F, G}, F*G) because the columnconsists entirely of zeros. As such, the functional relationship candidate testing managercan set minimality test scores (p) to 1 to filter out the redundant functional relationship candidates. The functional relationship candidate testing managercan determine that functional relationship candidates with p<1 have passed the minimality test.

506 506 506 Next, the functional relationship candidate testing managerperforms the perturbation test on functional relationship candidates that have passed the minimality test to determine most-likely inter-column relationships within an input table. In one or more embodiments, the functional relationship candidate testing managerassumes that the absence of violations in a functional relationship candidate is due to the data distribution. Therefore, the functional relationship candidate testing managerperturbs the data and observes the probability of violations in the functional relationship candidate as the p-value (i.e., p).

506 506 506 506 c s c s In one or more embodiments, the functional relationship candidate testing managerperforms the perturbation test by selecting two rows from the input table (T), denoted as rand rto serve as perturbation rows. Next, the functional relationship candidate testing managerselects a subset from the columns that participate in the functional relationship candidate, denoted as C′, to serve as the perturbation columns. Following this, the functional relationship candidate testing managerswaps the perturb columns of the perturb rows to get r′and r′, and verifies whether the functional relationship candidate still holds on the two swapped rows. Finally, the functional relationship candidate testing managerrepeats these steps with the remaining rows of the input table (T) and uses the probability that the functional relationship candidate holds on the remaining rows as the probability score (p).

506 506 In one or more embodiments, the functional relationship candidate testing managercan perform the perturbation test under a naïve version or a rapid version. For example, the functional relationship candidate testing managercan naively perform the perturbation test by perturbing all rows in the input table to determine probability score reflecting how well the functional relationship candidate holds on the input table. This, however, may lead to a running time that grows exponentially with the number of columns involved in the functional relationship candidate.

506 506 Thus, to save time and computing resources, the functional relationship candidate testing managercan perform the perturbation test under a rapid version. In one or more embodiments, the functional relationship candidate testing managerdetermines the probability score for the perturbation rows and perturbation columns until the upper or lower bound of p reaches a significance level α.

506 506 506 300 302 302 302 3 FIG.A H f g h Regardless of whether the functional relationship candidate testing managerperforms the perturbation test under the naïve version or the rapid version, the functional relationship candidate testing managercan generate probability scores for different types of functional relationship candidates. For example, the functional relationship candidate testing managerperforms the perturbation test to determine probability scores for arithmetic functional relationship candidates. To illustrate, an arithmetic functional relationship candidate shown in connection with the data tableinincludes Ψ=({F, G}, F*G) consists of three columns F, G, and H (e.g., the columns,, and), each of which has multiple elements in the domain.

H H H 506 302 302 506 f h When applying perturbation test to this three-column relationship, perturbing one column and perturbing two columns have the same effect. As such, the arithmetic functional relationship candidate Ψdoes not violate only if the functional relationship candidate testing managerselects the same value for swapping or if both columnsandare equal to zero. In one or more embodiments, the functional relationship candidate testing managercan determine that the arithmetic functional relationship candidate Ψ=({F, G}, F*G) is reliable if the p of Ψ=({F, G}, F*G) is within the significance level α (e.g., set to a default level of 0.5).

506 306 506 506 3 FIG.B E E Additionally, the functional relationship candidate testing managercan perform the perturbation test in connection with string transformation functional relationship candidates. For example, a string transformation functional relationship candidate shown above in connection with the data tableincan include Ψ=({B, C, D}, concatenate (B, C, D)). For each column affected by this string transformation functional relationship candidate, the functional relationship candidate only holds if the perturbation results in the same value. In one or more embodiments, the functional relationship candidate testing managercan determine that the p of Ψ({B, C, D}, concatenate (B, C, D)) is 0.06, suggesting that this functional relationship candidate is likely reliable. The functional relationship candidate testing managercan determine that a functional relationship candidate is not reliable when p=1—indicating that when columns are perturbed the functional relationship candidate always holds.

506 312 506 314 314 506 3 FIG.C B B B a b Moreover, the functional relationship candidate testing managercan perform the perturbation test in connection with functional dependency functional relationship candidates. For example, a functional dependency functional relationship candidate discussed above in connection with the data tableinincludes Ψ=({A}, A→B). In one or more embodiments, the functional relationship candidate testing managercan determine that the p of Ψ=({A}, A→B) is 0.02 (e.g., where A is the columnand B is the column). As such, the functional relationship candidate testing managercan determine that Ψ=({A}, A→B) is reliable because p=0.02 is within the significance level α.

506 506 4 FIG.B As mentioned above, the functional relationship candidate testing managercan perform the minimality and perturbation tests on clean data within input tables. To account for errors in the data of an input table (e.g., due to human input errors, inconsistencies during data integration, etc.), the functional relationship candidate testing managercan also determine likelihood probabilities for inter-column relationships found among “dirty” or noisy data. As discussed above in connection with, it is unbefitting to require a functional relationship candidate to hold for every row if the input table includes dirty or noisy data.

506 506 Given a Table T with a set of columns C, column subsets X, Y ⊆C, and a threshold α y ∈[0,1]. An a functional relationship (α FR) is an approximate FR such that the proportion of rows in T that violate Ψ= r y (X, ƒ) is less than α. In other words, the α FR Ψ= r (X, ƒ) holds for at least (1−α)×100% of the rows in T. Instead, the functional relationship candidate testing managercan utilize approximate functional relationship candidates. In one or more embodiments, the functional relationship candidate testing managerdefines an approximate functional relationship candidate as follows:

506 506 506 In one or more embodiments, the functional relationship candidate testing managerfurther accounts for the inaccuracies produced by noisy data by adding an additional preprocessing step of performing an independence test. For example, the functional relationship candidate testing managercan perform the independence test before the perturbation test on “dirty” data to filter out non-independent functional relationship candidates in the presence of noise. The functional relationship candidate testing managercan consider that non-independent functional relationship candidates are also unreliable functional relationship candidates.

506 1) Construct a list I representing the validity of the functional relationship candidate Ψ for each row, using 0 for invalid and 1 for valid. Ψ 2) Traverse every column c′ not in C. 3) For each column c′, create a contingency table between c′ and I. 506 4) Apply the Chi-Square test on the contingency table to assess the independence between c′ and I.In one or more embodiments, the functional relationship candidate testing manageruses the independence test to filter out the functional relationship candidate Ψ if any column c′ is dependent on I. Conversely, if no columns are dependent on I, the functional relationship candidate Ψ passes the independence test. In at least one embodiment, the functional relationship candidate testing managerperforms the independence test by performing four steps:

2 506 506 0 1 The Chi-Square test, also know as the Xis a statistical method used to determine if there is a significant association between categorical variables. For example, the Chi-Square test of independence assesses whether two categorical variables are independent or related. To determine the association between categorical variables using the p-value from a Chi-Square test, the functional relationship candidate testing managercan first set the hypotheses with the null hypothesis (H) stating that the variables are independent and the alternative hypothesis (H) stating that they are not. The functional relationship candidate testing managercan then calculate the Chi-Square statistic using the formula:

ij ij 506 Where Ois the observed frequency and Eis the expected frequency. The functional relationship candidate testing managercan determine the degrees of freedom as (r−1)×(c−1), where r and c are the number of rows and columns, respectively.

506 Additionally, the functional relationship candidate testing managercan find the p-value using the Chi-Square distribution:

506 506 506 The functional relationship candidate testing managercompares the p-value to the significance level α. If the p-value is less than or equal to α, the functional relationship candidate testing managercan reject the null hypothesis—indicating a significant association. If the p-value is greater than α, the functional relationship candidate testing managermay not reject the null hypothesis—indicating no significant association.

506 506 506 506 As mentioned above, the functional relationship candidate testing managercan perform the perturbation test under a naïve version or a rapid version. For example, under the naïve version, the functional relationship candidate testing managerenumerates all combinations of rows and columns. Under the rapid version, the functional relationship candidate testing managerrandomly selects perturbation columns and perturbation rows each time. As such, under the rapid version, time costs are determined by the number of random selections, which the functional relationship candidate testing managercan control even with large datasets.

506 506 506 506 In one or more embodiments, the functional relationship candidate testing managercan further accelerate the rapid version of the perturbation test. For example, in one embodiment, the functional relationship candidate testing managercan perform a closed form speed-up. To illustrate, the functional relationship candidate testing managercan store arithmetic functional relationship candidates as expression trees. In at least one embodiment, if the perturbed columns are precisely the left and right children of a binary tree, the result of this binary tree is referred to as the perturbation core. By observing whether the perturbation core changes, the functional relationship candidate testing managercan decide if the arithmetic functional relationship candidate remains valid after perturbation.

506 506 506 506 506 Additionally or alternatively, the functional relationship candidate testing managercan leverage a partition bound. To illustrate, during a perturbation test, if the perturbed columns within the perturbed rows remain identical, the post-swap functional relationship candidate will inevitably hold. By leveraging this characteristic, the functional relationship candidate testing managercan set a partition bound to estimate the lower bound of p for the functional relationship candidate. Specifically, when there is only one column in the perturbation columns, the functional relationship candidate testing managercounts the occurrence of each value in this column and calculates the probability of each value being selected for itself during a swap. When there are multiple columns in the perturbation columns, the functional relationship candidate testing managercomputes the occurrence of their “joint values” (e.g., concatenation of the values across these columns) to determine the probability of selecting the same joint value during a swap. In at least one embodiment, the partition bound serves as only the lower bound for the p for the functional relationship candidate because the functional relationship candidate may still hold during a perturbation test even if the swapped values are not identical. By assessing this lower bound against a threshold before conducting a perturbation test, the functional relationship candidate testing managercan bypass those functional relationship candidates that clearly do not meet the criteria.

506 506 506 s s Additionally or alternatively, the functional relationship candidate testing managercan leverage a binomial bound to determine when a perturbation test under the rapid version should be terminated. During the perturbation test under the rapid version (e.g., random sampling), the functional relationship candidate testing managerrepeatedly samples perturbation rows and verifies whether the functional relationship candidate holds after swapping the perturbation columns. Each perturbation yields only two possible outcomes: the functional relationship candidate holds (denoted by 1) or does not hold (denoted by 0). Moreover, each “sampling and verifying” procedure is independent of the others. If the likelihood score for the functional relationship candidate from the perturbation test under the naïve version (e.g., enumeration) is defined as p, then the likelihood score for the functional relationship candidate from the perturbation test under the rapid version (e.g., random sampling) can be seen as an estimation of p, denoted as {tilde over (p)}. After conducting n “sampling and verifying” procedures, the random variable nrepresents the number of times the functional relationship candidate holds, with n's probability distribution being a binomial distribution, denoted as B(n, p). Utilizing the Wilson confidence interval, the functional relationship candidate testing managercan estimate the upper and lower bounds of p as follows:

506 Here, z is the critical value from the standard normal distribution, correlating to the required confidence level. For instance, at a 95% confidence level, z is approximately 1.96. Assuming that 1000 “sampling and verifying” processes were conducted, with the functional relationship holding in 900 instances, the upper bound would be 0.92 and the lower bound 0.88. Therefore, the functional relationship candidate testing managercan be 95% certain that p falls between 0.88 and 0.92.

5 FIG. 102 508 508 118 506 508 As mentioned above, and as shown in, the functional relationship systemincludes the display manager. In one or more embodiments, the display managergenerates displays or instructions that cause the client deviceto generate displays in connection with most likely functional relationship candidates for inter-column relationships within an input table. For example, once the functional relationship candidate testing managerdetermines a set of most likely functional relationship candidates for columns of an input table, the display managercan generate displays that indicate those most likely functional relationship candidates.

508 508 114 112 114 In one embodiment, the display managercan generate a display that includes an overlay, pop-up window, or side panel display that includes a listing of the most likely functional relationship candidates. The display managercan cause the data table application(e.g., via the functional relationship system plugin) to display the overlay, pop-up window, or side panel display adjacent to the input table displayed by the data table application.

508 508 508 In an additional embodiment, the display managercan update a display of the input table or the input table itself to indicate the most likely functional relationship candidates. For example, the display managercan update the input table by adding a new row to the input table that illustrates the most likely functional relationship candidates. To illustrate, if a most likely functional relationship candidate for an input table includes A+B=C, the display managercan update the input table with an additional row at the top of the input table and can further insert “A+B=C” into the additional row under column “C.” Thus, a user can see quickly within the updated input table that column “C” is the sum of columns “A” and “B.”

508 508 508 In additional embodiments, the display managercan update the input table in other ways to indicate most likely functional relationship candidates. For example, the display managercan add a color to columns within the input table that are part of the same functional relationship candidate. Similarly, the display managercan add borders, animations, or other annotations to columns within the input table that are part of the same functional relationship candidate.

5 FIG. 106 108 108 510 102 510 102 510 As further shown in, the server(s)can include additional items. In one or more embodiments, the additional itemscan include functional relationship candidates. For example, the functional relationship systemcan store functional relationship candidatesthat are commonly found across multiple input tables (e.g., “First Name” concatenated with “Last Name” becomes “Full Name”). As such, in some embodiments, the functional relationship systemcan utilize the functional relationship candidatesto further increase the speed of the functional relationship candidate generation and measurement process.

106 104 110 104 116 118 104 116 102 1 FIG. In one or more embodiments, the server(s)includes the memoryand the processors. For example, the memory(and the memoryof the client deviceshown in) can generally represent any type or form of volatile or non-volatile storage device or medium capable of storing data and/or computer-readable instructions. In one example, the memory(and the memory) may store, load, and/or maintain one or more components of the functional relationship system. Examples of the memory can include, without limitation, Random Access Memory (RAM), Read Only Memory (ROM), flash memory, Hard Disk Drives(HDDs), Solid-State Drives (SSDs), optical disk drives, caches, variations or combinations of one or more of the same, and/or any other suitable storage memory.

110 122 118 110 122 102 110 122 1 FIG. Additionally, the processors(and the processorof the client deviceas shown in) can generally represent any type or form of hardware-implemented processing units capable of interpreting and/or executing computer-readable instructions. In one implementation, the processors(and the processors) may access and/or modify one or more components of the functional relationship system. Examples of the processors,include, without limitation, microprocessors, microcontrollers, Central Processing Units (CPUs), Field-Programmable Gate Arrays(FPGAs) that implement softcore processors, Application-Specific Integrated Circuits(ASICs), portions of one or more of the same, variations or combinations of one or more of the same, and/or any other suitable physical processor.

6 FIG. 6 FIG. 6 FIG. 6 FIG. 6 FIG. 6 FIG. 600 As mentioned above,illustrates an example series of actsrelated to automatically inferring functional relationships between table columns. Whileillustrates acts according to one or more embodiments, alternative embodiments may omit, add to, reorder, and/or modify any of the acts shown in. The acts ofcan be performed as part of a method. Alternatively, a non-transitory computer-readable medium can include instructions that, when executed by one or more processors, cause a computing device to perform the acts of. In still further embodiments, a system can perform the acts of.

6 FIG. 600 610 As illustrated in, the series of actsincludes an actof receiving an input table comprising a plurality of columns. For example, receiving the input table can include receiving a copy of the input table, receiving a memory address of the input table stored in a secondary location, or receiving a link to the input table stored in cloud storage.

6 FIG. 600 620 As further shown in, the series of actsincludes an actof determining a plurality of functional relationship candidates representing potential relationships among the plurality of columns in the input table. For example, determining the plurality of functional relationship candidates representing potential relationships among the plurality of columns in the input table can include identifying sets of two or more columns wherein a threshold number of values within each of the two or more columns satisfy a functional relationship. In one or more embodiments, the functional relationship can include at least one of an arithmetic relationship, a string transformation relationship, or a functional dependency relationship.

6 FIG. 600 630 As further shown in, the series of actsincludes an actof determining probability scores for the plurality of functional relationship candidates by perturbing the input table and ascertaining whether each of the plurality of functional relationship candidates holds. For example, determining probability scores for the plurality of functional relationship candidates can include, for each of the plurality of functional relationship candidates, repeatedly perturbing the input table, applying the functional relationship candidate to the input table following each perturbation, and determining a probability score for the functional relationship candidate following each perturbation based on whether the functional relationship candidate holds on the input table following the perturbation.

600 In one or more embodiments, the series of actsfurther includes, prior to determining the probability scores for the plurality of functional relationship candidates, removing functional relationship candidates from the plurality of functional relationship candidates that fail to pass a minimality test. Additionally, in some embodiments, ascertaining whether each of the plurality of functional relationship candidates holds is in connection with a threshold number of rows within the input table, wherein the threshold number of rows is less than a total number of rows within the input table.

6 FIG. 600 640 As further shown in, the series of actsincludes an actof identifying a subset of the plurality of functional relationship candidates with probability scores above predetermined threshold. For example, identifying the subset of the plurality of functional relationship candidates can further include identifying functional relationship candidates with probability scores within an upper bound and a lower bound.

6 FIG. 600 650 As further shown in, the series of actsincludes an actof generating a display associated with the input table that illustrates the subset of the plurality of functional relationship candidates. For example, generating the display associated with the input table that illustrates the subset of the plurality of functional relationship candidates can include generating a new row within the input table that illustrates the subset of the plurality of functional relationship candidates in association with input table columns involved in each of the subset of the plurality of functional relationship candidates.

7 FIG. 700 700 illustrates certain components that may be included within a computer system. One or more computer systemsmay be used to implement the various devices, components, and systems described herein.

700 701 701 701 701 700 7 FIG. The computer systemincludes a processor. The processormay be a general-purpose single- or multi-chip microprocessor (e.g., an Advanced RISC (Reduced Instruction Set Computer) Machine (ARM)), a special purpose microprocessor (e.g., a digital signal processor (DSP)), a microcontroller, a programmable gate array, etc. The processormay be referred to as a central processing unit (CPU). Although just a single processoris shown in the computer systemof, in an alternative configuration, a combination of processors (e.g., an ARM and DSP) could be used.

700 703 701 703 703 The computer systemalso includes memoryin electronic communication with the processor. The memorymay be any electronic component capable of storing electronic information. For example, the memorymay be embodied as random-access memory (RAM), read-only memory (ROM), magnetic disk storage media, optical storage media, flash memory devices in RAM, on-board memory included with the processor, erasable programmable read-only memory (EPROM), electrically erasable programmable read-only memory (EEPROM) memory, registers, and so forth, including combinations thereof.

705 707 703 705 701 705 707 703 705 703 701 707 703 705 701 Instructionsand datamay be stored in the memory. The instructionsmay be executable by the processorto implement some or all of the functionality disclosed herein. Executing the instructionsmay involve the use of the datathat is stored in the memory. Any of the various examples of modules and components described herein may be implemented, partially or wholly, as instructionsstored in memoryand executed by the processor. Any of the various examples of data described herein may be among the datathat is stored in memoryand used during execution of the instructionsby the processor.

700 709 709 709 A computer systemmay also include one or more communication interfacesfor communicating with other electronic devices. The communication interface(s)may be based on wired communication technology, wireless communication technology, or both. Some examples of communication interfacesinclude a Universal Serial Bus (USB), an Ethernet adapter, a wireless adapter that operates in accordance with an Institute of Electrical and Electronics Engineers (IEEE) 802.11 wireless communication protocol, a Bluetooth® wireless communication adapter, and an infrared (IR) communication port.

700 711 713 711 713 700 715 715 717 707 703 715 A computer systemmay also include one or more input devicesand one or more output devices. Some examples of input devicesinclude a keyboard, mouse, microphone, remote control device, button, joystick, trackball, touchpad, and lightpen. Some examples of output devicesinclude a speaker and a printer. One specific type of output device that is typically included in a computer systemis a display device. Display devicesused with embodiments disclosed herein may utilize any suitable image projection technology, such as liquid crystal display (LCD), light-emitting diode (LED), gas plasma, electroluminescence, or the like. A display controllermay also be provided, for converting datastored in the memoryinto text, graphics, and/or moving images (as appropriate) shown on the display device.

700 719 7 FIG. The various components of the computer systemmay be coupled together by one or more buses, which may include a power bus, a control signal bus, a status signal bus, a data bus, etc. For the sake of clarity, the various buses are illustrated inas a bus system.

The techniques described herein may be implemented in hardware, software, firmware, or any combination thereof, unless specifically described as being implemented in a specific manner. Any features described as modules, components, or the like may also be implemented together in an integrated logic device or separately as discrete but interoperable logic devices. If implemented in software, the techniques may be realized at least in part by a non-transitory processor-readable storage medium comprising instructions that, when executed by at least one processor, perform one or more of the methods described herein. The instructions may be organized into routines, programs, objects, components, data structures, etc., which may perform particular tasks and/or implement particular data types, and which may be combined or distributed as desired in various embodiments.

The steps and/or actions of the methods described herein may be interchanged with one another without departing from the scope of the claims. In other words, unless a specific order of steps or actions is required for proper operation of the method that is being described, the order and/or use of specific steps and/or actions may be modified without departing from the scope of the claims.

The term “determining” encompasses a wide variety of actions and, therefore, “determining” can include calculating, computing, processing, deriving, investigating, looking up (e.g., looking up in a table, a database, or another data structure), ascertaining and the like. Also, “determining” can include receiving (e.g., receiving information), accessing (e.g., accessing data in a memory) and the like. Also, “determining” can include resolving, selecting, choosing, establishing and the like.

The terms “comprising,” “including,” and “having” are intended to be inclusive and mean that there may be additional elements other than the listed elements. Additionally, it should be understood that references to “one embodiment” or “an embodiment” of the present disclosure are not intended to be interpreted as excluding the existence of additional embodiments that also incorporate the recited features. For example, any element or feature described in relation to an embodiment herein may be combinable with any element or feature of any other embodiment described herein, where compatible.

The present disclosure may be embodied in other specific forms without departing from its spirit or characteristics. The described embodiments are to be considered as illustrative and not restrictive. The scope of the disclosure is, therefore, indicated by the appended claims rather than by the foregoing description. Changes that come within the meaning and range of equivalency of the claims are to be embraced within their scope.