Materialized View Synchronization Using Watermark-Based Incremental Updates
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Current systems face inefficiencies in generating and synchronizing materialized views due to the need to regenerate entire representations when data changes, leading to delays and redundant processing, especially with large data sizes and long-running transactions.
Innovation Solution
A multi-tenant system with hardware processors that perform incremental updates, maintain watermarks, augment SQL to capture changes, and perform duplicate elimination using primary keys, enabling efficient synchronization of materialized views.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If entire materialized views are regenerated when data changes, then synchronization accuracy is improved, but processing time and resource consumption increase significantly
Solution Approach 1:
The patent segments the materialized view update process into incremental updates based on change detection. Instead of regenerating the entire materialized view, the system identifies and updates only the affected portions using watermark-based change detection, thereby reducing processing time while maintaining synchronization accuracy.
Solution Approach 2:
The patent applies partial action by performing only the necessary updates to synchronize materialized views rather than complete regeneration. The watermark mechanism enables the system to identify exactly which records have changed and update only those, avoiding redundant processing of unchanged data.
2Productivity
If incremental updates are performed on materialized views, then processing efficiency is improved, but synchronization accuracy may deteriorate due to missed changes
Solution Approach 1:
The patent implements a feedback mechanism through watermark tracking that continuously monitors constituent data for changes. The watermark values provide feedback about the state of data, enabling the system to detect changes and trigger appropriate synchronization actions, thereby maintaining accuracy while using efficient incremental updates.
Solution Approach 2:
The system performs preliminary action by pre-calculating and storing watermark values in the constituent data. These pre-computed watermarks enable rapid change detection without requiring full data scans, allowing the incremental update process to maintain both efficiency and accuracy by quickly identifying what needs to be synchronized.
3Difficulty of detecting and measuring
If watermark columns are added to track changes, then change detection capability is improved, but device complexity increases
Solution Approach 1:
The patent makes the watermark column universal by using it for multiple purposes: tracking changes for incremental updates, identifying records for synchronization, and enabling efficient query filtering. This multi-functionality justifies the added complexity as the single watermark column replaces what would otherwise require multiple change tracking mechanisms.
Solution Approach 2:
The system changes the parameter state of existing data by adding watermark columns that store timestamp or version information. This parameter change enables the database to efficiently detect and track modifications without fundamentally altering the data model or requiring complex external tracking systems.
4Reliability
If duplicate elimination is performed using primary keys, then data quality is improved, but processing overhead increases
Solution Approach 1:
The patent enables self-service duplicate elimination by leveraging the existing primary key infrastructure of the database. The system uses primary keys to automatically identify and eliminate duplicates during the incremental update process without requiring additional complex deduplication logic or external processing, thereby maintaining data quality with minimal overhead.
Data Source
AI summary
The multi-tenant system includes one or more hardware processors that obtain a query that includes statements defining one or more operations to be performed on a subset of constituent records stored in a source database. The hardware processors parse the query to decipher a type of the one or more operations, the subset of the constituent records referred to, and one or more other conditions of the operations. The hardware processors obtain the subset of the constituent records from the parsed query from the source database. The hardware processors execute the one or more operations, to generate a materialized view that denormalizes the data. The hardware processors detect any changes in the subset of the constituent records, synchronize the materialized view with the any changes, store the synchronized materialized view within a data platform, and transmit the synchronized materialized view to a target database.