ETL Database Change Aggregation via Audit Log Consolidation
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Large batch data processing events in corporations lead to high volumes of database updates, resulting in intermediate record changes that complicate synchronization between source and target databases, causing applications to be temporarily offline and leading to significant time lags in data accuracy.
Innovation Solution
A database change aggregation method and system in an ETL environment that aggregates transactions from audit logs, reducing the number of transactions needed to be propagated across networks and written into federated databases, by processing only the last update to each record during batch processing.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If complete data store refresh is performed to propagate database changes, then data synchronization between source and target databases is achieved, but synchronization complexity increases and applications must be temporarily offline
Solution Approach 1:
The patent extracts only the essential change information (audit log entries) from the source database and applies them directly to the target database, rather than performing a complete data store refresh. This extraction approach maintains data synchronization while avoiding the complexity of full refresh operations and eliminating the need to take applications offline.
Solution Approach 2:
The patent segments the database changes into individual audit log entries that can be processed and applied separately to the target database. This segmentation allows for incremental updates rather than monolithic refresh operations, reducing synchronization complexity and allowing applications to remain online during the process.
2Reliability
If complete data store refresh is performed to ensure data accuracy, then data synchronization is achieved, but time lags occur between source and target databases
Solution Approach 1:
The patent performs preliminary actions by capturing and logging database changes as they occur in the source database through audit logs. These pre-captured changes are then efficiently applied to the target database, eliminating the time lag associated with periodic complete refresh operations and maintaining near-real-time data accuracy.
3Reliability
If all intermediate record changes are propagated to target database, then complete data updates are achieved, but network bandwidth and processing overhead increase significantly
Solution Approach 1:
The patent merges multiple intermediate record changes into consolidated update operations that are applied to the target database. By combining redundant or related changes, the system maintains complete data update integrity while significantly reducing the volume of data transmitted over the network and the processing overhead required.
Data Source
AI summary
Systems and methods for implementing a federated database system are presented herein. One or more source databases may store changed data to a target database. Each update of a record in a source database can result in an audit log entry written with the before and after image, a timestamp, and an identifier for the log entry. Using the audit log, the database implementation herein can consolidate updates to any record for a batching event to be processed in an Extract, Transform, and Load (ETL) process for export of updates to the target database.