Gen-T: Table Reclamation in Data Lakes

Discovering a set of candidate tables requires discovering tables that share some of the same values as the Source Table in an efficient manner. In the context of data lakes, where metadata is inconsistent or missing, searching using schema names is unreliable [16, 17, 18, 74]. However, we can use any existing data-driven table discovery approach that is scalable in a data lake setting.

With a set of top-$k$ tables returned as relevant to the Source Table, we need to verify the set similarities of their values with the Source Table. To do so, we retrieve candidate tables among the previously discovered tables using a set similarity algorithm. This could be done efficiently with a system like JOSIE [32] that computes exact set containment or MATE [36] that supports multi-attribute joins. In addition to finding candidate tables containing columns that have high set similarity with a Source Table, we also diversify the set of candidate tables such that each candidate table has minimal overlap with other candidates. This is especially important in public data lakes, which tend to have multiple versions of the same tables [62, 75] and a large percentage of duplicate column sets [32]. By diversifying candidates, each candidate may overlap with different values in $S$, as illustrated in the following example.

With a diverse set of candidates found for each column in a Source Table $S$, we ensure that each candidate table still has high set overlap with the Source Table across related columns. To do so, we find all tuples in a candidate table that share column values with $S$. We verify that for each column that has high set overlap with a column in $S$, it still has high set overlap within these tuples. For columns from the candidate table that have high value overlap with columns in $S$, we rename them with names of corresponding columns in $S$. This way, we implicitly perform schema matching between each candidate table and $S$. Finally, we check for and remove any candidate table whose columns and column values are subsumed by other candidate tables.

With a set of diversified candidate tables, we could potentially enter the table integration phase (Section V-B). However, it may be computationally expensive to directly integrate all candidate tables. Thus, we need to refine the set of candidate tables to a set of originating tables containing a maximum set of aligned tuples with respect to the Source Table. To do so, we emulate the table integration process and see what candidate tables are necessary to reclaim our Source Table. By simulating tuple alignment, we can uncover erroneous aligned tuples with respect to the Source Table, and discard tables that could decrease the EIS Score.

First, we need to align tuples in candidate tables to tuples in a Source Table, based on shared values with the key attribute from the Source Table. To do so, we need to ensure that each candidate table contains a key column of the Source Table. If it does not, we greedily find a best way to join it with candidates that include the source key. We use standard join cardinality estimation to find a path that covers the most source key values and is as close to functional as possible (this procedure is denoted as Expand()).³³3The Expand algorithm is included in the technical report [73]. This way, all tables can align its tuples with the Source Table using key values.

We represent aligned tuples and shared columns from a candidate table in the form of a matrix. To encode aligned tuples, we initialize the matrices to have the same dimensions as the Source Table $S$, such that the matrix indices represent the Source Table’s indices. For each key value and its associated column values in the Source Table, check if the value appears in the candidate table at the corresponding column and key value. If so, then the matrix has 1 at the same index as the value’s index in Source Table, and 0 otherwise.

Next, we simulate table integration by applying the logical OR on the matrices, which takes the maximum value at each position. Suppose we have tuples $t_{1},t_{2}$ from matrices $m_{1},m_{2}$, respectively, at the same row index $i$. We want to combine values from $t_{1},t_{2}$ at column $j$. Assuming that $S[i,j]\neq\bot$, the produced tuple $m_{r}$ contains the following value at position ($i,j$): $m_{r}[j]=max(t_{1}[j],t_{2}[j])$. If $t_{1}[j]$ is 1 and $t_{2}[j]$ is 0, for example, $max(t_{1}[j],t_{2}[j])$ is 1, which is equivalent to the logical OR. This is comparable to applying the Outer Union ($\uplus$) of two tables, and Subsumption ($\beta$) and Complementation ($\kappa$) on the resulting table (refer to Theorem 1). In this table integration, for tuples $t_{1},t_{2}$ that share a non-null value at the same column, the resulting tuple $t_{r}$ is formed such that for every column $j$, $t_{r}[j]=\not\perp\text{if}\,t_{1}[j]\neq\bot\text{ or }t_{2}[j]\neq\bot\text{ and }t_{r}[j]=\bot\text{ otherwise}$. Thus, both table and matrix integrations maximally combine tuples such that non-null values replace a null value at the same index.

We demonstrate matrix initialization and traversal in Algorithm 1. Given a set of candidate tables $\mathcal{T}$ and Source Table $S$, we first ensure that each candidate table contains a key column from $S$ by joining candidate tables that do not share a key column with $S$ with those that do (Expand() on line 1). This way, we align tuples in each candidate table with respect to $S$ and initialize each candidate table’s matrix representation (Line 1). Then, we traverse over the matrices and perform the logical OR operator to combine a pair of matrices in $\mathsf{Combine()}$ (Line 1). To evaluate the resulting matrix, we check the fraction of 1’s in the matrix, which represent the number of values in the resulting table integration found in the Source Table, thus evaluating the EIS score. At each step of the matrix traversal (Line 1), including the start (Lines 1-1), we choose the matrix that results in a matrix containing the most 1’s in $\mathsf{evaluateSimilarity()}$. This traversal ends when either all matrices have been traversed (Line 1), or the percentage of 1’s in the resulting matrix converges (Lines 1-1). We return the set of tables used in the final traversal as the set of originating tables to perform table integration. If a candidate table that was joined with other candidates to contain a key column from $S$ (from Expand() on line 1) becomes an originating table, we include its expanded form in the returned set.

Previously, we use matrices populated with binary values to represent aligned tuples with respect to the Source Table. However, this representation cannot distinguish between nullified and erroneous aligned tuples with respect to the Source Table. Specifically, it does not account for cases in which a tuple in the Source Table and an aligned tuple in a candidate table have different non-null values in the same column, and if a tuple in the Source Table has a null value while the aligned tuple has a non-null value at the same column. Rather, it represents both types of values as 0 in the matrices. In actuality, when we apply Outer Union on two tables with aligned tuples containing different non-null values in the same column, we keep the tuples separate. Thus, we need to distinguish between nullified and erroneous aligned tuples in the matrix representation (Line 1 in Algorithm 1). To do so, we use three-valued matrices, in which we encode a 1 if a candidate table shares the same value with the Source Table at the same index in an aligned tuple, 0 if a candidate table contains a null where the Source Table has a non-null value at the same index, and -1 if a candidate table contains a non-null value that contradicts with the Source Table’s value at the same index (shown in Figure 5). Formally, given Source Table $S$ and candidate table $T$, we populate position $(i,j)$ for each aligned tuple $t_{\text{Align}}\in T$ in matrix $M$ as:

Combining the amended three-valued matrix representations with the logical OR takes the maximum of two truth-values at each index. Specifically, if we have two tuples from two matrices that contain a 1 and -1 at the same position, applying logical OR would choose the 1 [76]. However, in practice when applying Outer Union on two tuples with contradicting non-null values at the same index, the resulting integration would contain both tuples. Thus, we need to keep both tuples from the matrices if they contain different non-0 values at the same index. We re-define $\mathsf{Combine}$() (Line 1) between two matrices, given tuples $t_{1},t_{2}$ at the same row index accordingly.

This way, we keep tuples separated if they contain contradicting, non-0 values at the same position. Else we apply logical OR and take the maximum of truth values element-wise. The new Combine() function is illustrated in Example V-A3.

The new $\mathsf{Combine}$() could result in matrices with more rows than in the Source Table. Thus, we encode each matrix as a dictionary, with each key value in the Source Table as a dictionary key, and the list of aligned tuples in the resulting matrix with respect to a tuple in the Source Table as values.

For $\mathsf{evaluateSimilarity()}$ of the start (Lines 1-1) and resulting matrices (Line 1), we evaluate the EIS Score by taking the aligned tuple with the largest number of aligned values to its corresponding tuple in the Source Table, or the largest number of 1’s. To find the EIS score (Equation 3) between a tuple $t$ in a resulting matrix that shares a key value with a Source tuple $s$, we set $\alpha(s,t)$ to be the number of non-key attributes for which tuple $t$ has (1)’s, representing shared values between $t$ and $s$. For $\delta(s,t)$, we take the number of non-key attributes for which $t$ has (-1)’s, representing different, non-null values. Thus, we treat correct, nullified, and erroneous aligned tuples with respect to the Source Table in different manners, and combine their matrix representations depending on the behavior of applying Outer Union and unary operators.

First, we project out columns not in $S$ ($\pi$), and select tuples whose values are in the source key column ($\sigma$). Hence, we only keep columns and tuples that overlap with $S$ (ProjectSelect(), Line 2). We then union all originating tables that share the same schema (InnerUnion(), Line 2) to reduce the space of tables we need to explore. To prevent over-combining tuples that share nulls with tuples in $S$, for each table $T\in\mathcal{T}$, we find tuples in $S$ that share key values and contain nulls in the same columns, and replace these nulls in $T$ with unique labeled non-null values (LabelSourceNulls(), Line 2). Finally, we remove duplicate tuples, subsumed tuples ($\beta$), and take the resulting tuples of complementation ($\kappa$) (TakeMinimalForm() on Line 2).

We integrate all resulting tables $\mathcal{T}_{\cup}$, all of which contain the source key column. At each iteration of $T_{i}\in\mathcal{T}_{\cup}$, we outer union $T_{i}$ with the integrated result so far, $T_{\uplus}$. Next, we check if applying Complementation($\kappa$) and Subsumption($\beta$) on $T_{\uplus}$ results in a table that has a higher EIS score in the evaluateSimilarity() function. This lets us check if these operators are over-combining tuples (e.g., removing a subsumed tuple that is identical to a tuple in $S$) and decreasing the number of values shared with $S$. After iterating through all tables from the input set, we revert the previous labeling of shared nulls with $S$ (RemoveLabeledNulls() on Line 2). To ensure that the resulting integrated table ($T_{\mathsf{result}}$) has the same schema as $S$, we add null columns in $T_{\mathsf{result}}$ for every column it does not share with $S$ (Line 2). Finally, we return the resulting integration as a possible reclaimed table.

We compare Gen-T to the state-of-the-art for by-target synthesis, Auto-Pipeline [9], the state-of-the-art for Query-by-example, Ver [59], and the state-of-the-art for table integration, ALITE [24] which were modified for the reclamation problem.

Auto-Pipeline has a similar framework to our problem in discovering the integration (query or pipeline) that reclaims a Source Table. However, Gen-T does not assume to have the perfect set of input tables from which we can synthesize the query that reproduces the Source Table. Auto-Pipeline has both query-search and deep reinforcement learning approaches, but since we propose an unsupervised approach, we use the query-search variation as our baseline. Auto-Pipeline’s code implementation is not openly available, so we adopted an open re-implementation of their search approach [62], which adapts the framework in Foofah [81], and, for a fair comparison, revised their set of table operators to only contain table operators that Gen-T considers ({$\sigma,\pi,\cup,\bowtie,\mathbin{\rule[-0.08612pt]{2.5pt}{0.4pt}\hbox to 0.0pt{\hss\rule[7.0pt]{2.5pt}{0.4pt}}\mkern-5.8mu\bowtie},\mathbin{\rule[-0.08612pt]{2.5pt}{0.4pt}\hbox to 0.0pt{\hss\rule[7.0pt]{2.5pt}{0.4pt}}\mkern-5.8mu\bowtie\mkern-5.8mu\rule[-0.08612pt]{2.5pt}{0.4pt}\hbox to 0.0pt{\hss\rule[7.0pt]{2.5pt}{0.4pt}}}$}). We call this re-implemented, adapted baseline Auto-Pipeline*. Since Auto-Pipeline’s benchmarks contain small tables, and most of their operators are string-transformation operators, we do not consider their benchmarks for our experiments.

Ver has a similar objective of discovering and integrating tables to produce a table that contains the source table and other similar tuples. In contrast, Gen-T aims to reproduce only the tuples from the source table. Ver takes small source tables as input (e.g., source tables with 2 columns and 3 rows [59]), so we query Ver with two columns from the Source Tables. We evaluate the output table for each run, and aggregate the results to evaluate the entire source table.

We validate the need for our Matrix Traversal rather than directly integrating the set of candidate tables returned from Set Similarity (Section V-A1), by comparing against ALITE provided with the set of candidate tables from Set Similarity as input. We also compare with a variation of ALITE, which we call ALITE-PS, that, similar to Gen-T, first performs projection and selection to match the Source Table before performing table integration. ALITE without project and select is much slower as it creates a larger integration result.

For all baselines on the TP-TR benchmarks, we create another variant in which we give each method a specific integrating set (int. set) of tables as input, rather than the full set of candidate tables returned from Set Similarity. We know what subset of tables from the 8 original tables were used to create the 26 Source Tables, so, we know that a perfect reclamation contains variants of these tables. Thus, for all original tables used to create each Source Table, the integrating set includes all variations (2 nullified and 2 erroneous versions of each original table) of these tables.

For effectiveness, we evaluate how many values in a Source Table have been reclaimed, or how similar the values in the reclaimed table are to those of the Source Table. Thus, the Source Tables are essentially our ground truth in that we see how many of its values we can reproduce. In an aligned tuple in a reclaimed table with respect to a Source Table, it contains an erroneous value if there is a non-null, different value at a given column compared to a value in the same column in the Source Table. Similarly, it contains a nullified value if it contains a null value in a column where the Source tuple contains a non-null value (see Section IV-A).

In addition to metrics that measure the similarity between the tuples of a reclaimed table and a Source Table, we also include finer-grain metrics that measure the number of values that do not match within aligned tuples (tuples with the same key value). If there are multiple aligned tuples with respect to one tuple in the Source Table (multiple tuples in the reclaimed table with the same key value), then we consider the tuple that contains the largest number of column values shared with the corresponding tuple in the Source Table. This way, there is at most 1 aligned tuple in the reclaimed table for each tuple in the Source Table. In these measures, which we denote as divergence measures, the ideal score is 0 (the reclaimed table is identical to the Source Table). Specifically, we introduce Instance Divergence and Conditional KL-Divergence, enabling us to measure the nullified and erroneous values in the reclaimed table’s aligned tuples, with respect to the Source Table (see Section IV-A).