Data Lakehouse Schema Discovery Using Key Embeddings
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Identifying primary and foreign keys in a data lakehouse is challenging due to the large volume of tabular data, making schema discovery difficult and undermining data accessibility and governance.
Innovation Solution
A method involving column embeddings and similarity measures to identify primary keys, followed by identifying top n-similar columns and joinable tables using inclusion dependency constraints to discover foreign keys, thereby building a relational schema.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Ease of operation
If manual schema discovery is used in a data lakehouse, then data accessibility and governance are improved, but the process is time-consuming and labor-intensive due to the large volume of tabular data
Solution Approach 1:
The system performs automatic schema discovery by analyzing tabular data to identify primary keys, foreign keys, and relationships without human intervention. The machine learning model autonomously processes data to generate schemas, eliminating the need for manual analysis while maintaining data accessibility and governance.
Solution Approach 2:
The patent replaces manual mechanical processes of schema discovery with automated machine learning algorithms. The system uses computational models to analyze data patterns, identify relationships, and construct schemas automatically, substituting human analytical work with automated mechanical processes.
2Productivity
If automated schema discovery is implemented, then schema discovery time is reduced, but the complexity of the system increases due to the need for machine learning models and data processing mechanisms
Solution Approach 1:
The system introduces machine learning models as intermediary components that bridge raw tabular data and desired schema outputs. These models act as mediators that automatically transform unstructured data into structured schemas, managing system complexity through specialized intermediate layers rather than direct complex processing.
Solution Approach 2:
The patent employs parameter changes in machine learning models to adapt to different data types and relationships. By adjusting model parameters and configuration settings, the system can handle various schema discovery scenarios without requiring complete system redesign, thus managing complexity through configurable parameters rather than structural complexity.
3Reliability
If manual key identification is used, then data governance and accuracy are improved, but the process becomes inefficient when dealing with large volumes of tabular data
Solution Approach 1:
The system incorporates feedback mechanisms where machine learning models continuously analyze data patterns and refine their schema discoveries. The model receives feedback from data analysis results and adjusts its identifications of primary and foreign keys, ensuring accuracy while maintaining high processing efficiency through iterative improvement.
Solution Approach 2:
The patent replaces manual mechanical verification of data keys with automated machine learning algorithms that systematically identify primary and foreign keys. The system uses computational pattern recognition and statistical analysis to ensure accurate key identification at scale, substituting human verification with automated mechanical processes that maintain reliability while improving efficiency.
Data Source
AI summary
Described are techniques for identifying primary and foreign keys to build a schema. A primary key of a query table of a data lakehouse, which may consist of multiple columns, is identified. The top n-similar columns of the tables of the data lakehouse are then identified to identify the joinable tables using the primary key as a query using column embeddings. A list of candidate joinable tables is then identified based on the identified top n-similar columns of tables of the data lakehouse. Joinable tables from the list of candidate joinable tables are selected that satisfy an inclusion dependency constraint. The foreign keys having full containment with the primary key of the query table are then identified from the selected joinable tables. Such identified primary and foreign keys are utilized to build/discover the schema, such as a relational schema, of the data lakehouse.