Time-variant Data Schema Management in Data Warehouses
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Current data warehouse systems cannot preserve historical data schema structures as they are often overridden by schema changes, making it impossible to analyze data according to its original structure.
Innovation Solution
A method and system that update metadata tables with timestamps for schema modifications, allowing new information to be annotated with creation times and old information to be marked as inactive, thereby preserving historical data integrity.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Adaptability or versatility
If schema changes are performed in data warehouses, then the data structure can be updated to reflect current business needs, but the historical data structure is lost and cannot be queried
Solution Approach 1:
The system performs preliminary actions by capturing schema state information before modifications occur. A schema state manager records the complete schema structure (tables, columns, data types, constraints) at specific time points, enabling historical queries to access the structure as it existed at any past moment.
Solution Approach 2:
The invention creates copies of schema state information at different time points. Instead of maintaining a single live schema, the system generates and stores multiple versions of schema metadata, allowing queries to retrieve the schema structure that existed at any historical time point without affecting the current schema.
2Adaptability or versatility
If destructive schema changes are applied, then the data warehouse can adapt to new requirements, but previous schema versions cannot be examined
Solution Approach 1:
Before any destructive schema change is applied, the system captures the current schema state and stores it as a historical version. This preliminary action ensures that the previous schema structure is preserved and can be examined later, while still allowing the new schema to be applied for current operations.
Solution Approach 2:
The system implements dynamic schema management where the schema structure can change over time while maintaining historical versions. The schema state manager dynamically tracks schema evolution, allowing the data warehouse to adapt to changing requirements while preserving reliability for historical queries.
3Loss of information
If metadata tables are updated with schema modification entries, then historical data can be preserved and queried, but the system complexity increases
Solution Approach 1:
The schema state manager performs multiple functions within a unified system component: it captures schema changes, stores historical versions, manages metadata table updates, and enables temporal queries. This multi-functionality reduces overall system complexity by consolidating what could be separate systems into a single coordinated manager.
Solution Approach 2:
The schema state manager acts as an intermediary between schema modification operations and historical data retrieval. It mediates between the need for flexible schema changes and the need to preserve historical information, translating schema modification requests into structured metadata entries that can be queried temporally.
Data Source
AI summary
A system, method, and computer readable medium for preserving information in time variant data schemas are disclosed. The method includes determining if at least one modification request associated with a database schema has been received. In response to the modification request being received, a metadata table associated with the database schema is updated to include at least one entry associated with the modification request. The entry identifies an instance in time when an action associated with the modification request was performed.