Automated Schema Data Fixes With Parallel SQL Batching
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Conventional data fixes in database systems are cumbersome and inefficient, particularly when dealing with multiple customer schemas across various regions and data centers, lacking automated and secure mechanisms for modifying tenant data.
Innovation Solution
A system and method for automating data fixes by dividing schemas into batches and applying pre-configured SQL commands in parallel, using a schema manager to manage credentials securely and integrating with a batch executor for robust retry logic, ensuring successful data fixes before allowing application deployments.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If manual data fixes are performed by system administrators, then data security is maintained through privileged access control, but the process becomes cumbersome and time-consuming especially when dealing with thousands of customer schemas
Solution Approach 1:
The system enables automated self-service data fixes by allowing the data fix service to automatically execute pre-configured SQL commands across multiple schemas without requiring manual administrator intervention for each schema, thus reducing deployment time while maintaining security through controlled automated access
Solution Approach 2:
The system segments the large task of fixing thousands of schemas into smaller manageable batches, processing them in parallel groups rather than sequentially, which significantly reduces the total time required while maintaining secure controlled access to each schema segment
2Productivity
If automated parallel processing is implemented across multiple schemas, then productivity and deployment speed are improved, but system complexity and the risk of errors increase
Solution Approach 1:
The system performs preliminary actions by pre-configuring SQL commands and organizing schemas into batches before execution, which simplifies the actual runtime processing and reduces system complexity during the data fix operation while maintaining high productivity through parallel execution
Solution Approach 2:
The system implements feedback mechanisms through retry logic that monitors execution results and automatically retries failed operations, which manages system complexity by providing automated error handling and ensures productivity is maintained through resilient parallel processing
3Stability of the object's composition
If pre-configured SQL commands are applied to all schemas, then consistency across customer schemas is improved, but the ability to handle schema-specific variations and edge cases is reduced
Solution Approach 1:
The system applies local quality by allowing schema-specific configurations and variations within the batch structure, enabling each schema to receive appropriate handling for its specific characteristics while still benefiting from the overall consistency provided by pre-configured commands, thus balancing both consistency and flexibility
4Measurement precision
If manual intervention is required for each schema, then error detection and correction are improved, but the operational overhead and cost increase significantly
Solution Approach 1:
The system implements self-service through automated error detection and retry mechanisms that monitor and correct errors without requiring manual intervention, thereby maintaining error detection accuracy while dramatically reducing operational overhead and making the operation easier to execute
Data Source
AI summary
Embodiments of the present disclosure include techniques for performing data fixes. In one embodiment, a list of schemas and access information for the schemas is received from a schema manager. The schemas are batched for processing. During processing, schemas in a batch are processed in parallel. Processing includes applying pre-configured SQL commands. If the data fix is successful, applications may be deployed. If the data fix is not successful, application deployments may be blocked.