Staging Tables for Micro-Batched Data Ingestion Without Locking
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Modern data warehouses face performance issues when updating or deleting records in real-time due to the need for locking database tables, which can lead to delays and resource inefficiencies, especially when handling big data for analytics and reports.
Innovation Solution
A data ingestion system that uses a staging table with a merge instruction column to perform insert, update, and delete operations without locking the destination table, allowing for real-time or near real-time data persistence through micro-batch processing and scheduled jobs, enabling faster and more efficient data storage.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If database tables are locked to perform update and delete operations, then data integrity is maintained, but processing speed and productivity deteriorate due to delays of minutes or hours
Solution Approach 1:
The patent segments the database table into two separate tables: a locked destination table for data integrity and an unlocked staging table for rapid data ingestion. This segmentation allows concurrent access patterns where the staging table can be freely updated while the destination table remains locked for reading, eliminating the bottleneck of locking the entire table during data ingestion operations.
Solution Approach 2:
The staging table acts as an intermediary between the source data and the destination table. Data is first ingested into the staging table without locking the destination table, then merged into the destination table through a controlled process. This intermediary structure enables asynchronous data ingestion that maintains data integrity while improving processing speed.
2Stability of the object's composition
If database tables are locked for real-time updates, then data consistency is ensured, but resource usage increases and time delays occur
Solution Approach 1:
The patent performs preliminary data preparation and validation in the staging table before merging data into the destination table. This preliminary action allows data to be staged and prepared without locking the destination table, reducing the time the destination table needs to be locked and thereby minimizing time delays while maintaining data consistency.
Solution Approach 2:
The data merging from staging table to destination table is performed periodically or in batches rather than continuously locking the destination table. This periodic action allows the destination table to be unlocked between merge operations, reducing overall lock time and eliminating continuous time delays while maintaining data consistency through controlled merge points.
3Quantity of substance
If all records are stored in the destination table including old records, then data completeness is maintained, but processing efficiency deteriorates due to filtering requirements
Solution Approach 1:
The patent extracts only the necessary new or updated records from the staging table and merges them into the destination table, rather than moving all records. This extraction approach maintains data completeness by preserving all necessary records in the destination table while improving processing efficiency by only operating on the subset of records that need to be updated, avoiding unnecessary filtering of all old records.
Data Source
AI summary
A system is provided to provide near real-time updates to data tables for a data warehouse using a micro-batch manager. The system includes a processor and a computer readable medium operably coupled thereto, to perform table updating operations which include accessing a first staging data table comprising a plurality of records that was generated by the micro-batch manager for a first destination table of the data tables for the data warehouse, reading a merge instruction column of the first staging data table for the plurality of records, determining a merge operation for each of the plurality of records from reading the merge instruction column, merging the plurality of records from the first staging data table to the first destination table using the merge operation for each of the plurality of records, and deleting the plurality of records from the first staging data table after merging.