Derivation Graph Querying for Reusable Deferred SQL Joins

Resolve Bottlenecks,
Find Innovative SolutionsGenerate Solutions

Solution Overview

Problem

Existing software solutions for processing complex and voluminous data are sub-optimal for workloads that include both batch and streaming data, require unnecessarily complex data expressions, and lack efficient query re-use, often necessitating sophisticated query languages like SQL that demand high compute resources and manual join relationship specification.

Innovation Solution

A data platform that generates queries using derived data relationships between quads from simplified query expressions, employing deferred join processing to avoid ambiguous join relationships and optimize query execution, utilizing a query generation component that parses data expressions into a tree of quads and builds a derivation graph to generate queries in SQL.

Engineering Contradictions & Design Principles

VSEngineering Contradiction Analysis

1Productivity

If sophisticated query languages like SQL are used to process complex and voluminous data, then data processing capability is improved, but compute resources and complexity requirements increase

Engineering Contradiction:

Improvedata processing capabilityVSAvoidcompute resources and complexity requirements

Core Design Contradiction:

ProductivityVSDevice complexity

Solution Approach 1:

The patent introduces a derivation graph as an intermediary data structure that mediates between the simplified query language and SQL. The derivation graph automatically infers join relationships and data flow, serving as a bridge that translates user-friendly queries into executable SQL without requiring users to manually specify complex join conditions, thus reducing the computational and complexity burden on the user while maintaining powerful data processing capabilities

Inventive Principle:

Principle #24Intermediary (Mediator)

Solution Approach 2:

The patent replaces the mechanical requirement for manual join relationship specification in SQL with an automated derivation process. Instead of requiring users to mechanically write out complex join conditions and table relationships, the system automatically derives the necessary join relationships by analyzing the derivation graph and inferring connections between datasets, thereby reducing the complexity burden while maintaining processing capability

Inventive Principle:

Principle #28Mechanics substitution (Replace mechanical system)

2Measurement precision

If manual join relationship specification is required in SQL queries, then query precision is improved, but ease of operation deteriorates

Engineering Contradiction:

Improvequery precisionVSAvoidease of query writing

Core Design Contradiction:

Measurement precisionVSEase of operation

Solution Approach 1:

The derivation graph system performs self-service by automatically inferring join relationships and data flow without requiring user intervention. The system analyzes the query context, identifies relevant datasets and columns, and automatically constructs the necessary join relationships, thereby maintaining query precision while dramatically improving ease of operation. Users simply write high-level queries without needing to manually specify join conditions

Inventive Principle:

Principle #25Self-service

Solution Approach 2:

The system performs preliminary action by pre-computing and storing derivation relationships in the derivation graph before query execution. This pre-computed information about data relationships and join paths is ready to be instantly retrieved and applied when users write queries, eliminating the need for manual join specification while ensuring precise query results

Inventive Principle:

Principle #10Preliminary action

3Measurement precision

If complex data expressions are used to specify join relationships, then query accuracy is improved, but device complexity and computational overhead increase

Engineering Contradiction:

Improvequery accuracyVSAvoidcomputational overhead

Core Design Contradiction:

Measurement precisionVSDevice complexity

Solution Approach 1:

The patent extracts the complex join relationship logic from the user query and relocates it to the derivation graph infrastructure. The derivation graph contains pre-computed and stored relationship information that is extracted and applied automatically during query execution, thereby maintaining query accuracy while reducing the computational overhead in the query processing path

Inventive Principle:

Principle #2Taking out (Extraction)

Data Source

PatentUS12393583B2Derivation graph querying using deferred join processing

Publication Date: 2025.08.19 OPTIMIZELY NORTH AMERICA INC

AI summary

A derivation graph including nodes representing quads identified within a data expression in a simplified query language is queried using deferred join processing. A derivation graph is generated based on a first data expression that includes a join between a second data expression and a third data expression, in which the derivation graph includes at least one node representative of the second data expression and at least one node representative of the third data expression. A root node is identified within the derivation graph by determining that the nodes representative of the second data expression and the third data expression are derivable from the root node using the derivation graph. Query language instructions representing the join between the second data expression and the third data expression written in a second query language are then generated using the root node.