System Tables for Change Data Capture and Privacy Compliance

Resolve Bottlenecks,
Find Innovative SolutionsGenerate Solutions

Solution Overview

Problem

Network-based database systems face limitations in analyzing changes to tables due to the limited functionality of virtual tables, which can also reveal sensitive information that needs to be deleted for privacy compliance, and struggle with efficient data retention and storage.

Innovation Solution

The use of system tables to store changelog information for primary tables, allowing streams to read from these system tables instead of or in addition to the primary tables, enabling efficient change data capture and compliance with privacy regulations by deleting sensitive information.

Engineering Contradictions & Design Principles

VSEngineering Contradiction Analysis

1Adaptability or versatility

If virtual tables are used to analyze changes to primary tables, then change data capture functionality is provided, but the virtual tables have limited functionality and can reveal sensitive information that must be deleted for privacy compliance

Engineering Contradiction:

Improvechange data capture functionalityVSAvoidprivacy compliance issues

Core Design Contradiction:

Adaptability or versatilityVSObject-affected harmful factors

Solution Approach 1:

The patent introduces system tables as an intermediary layer between primary tables and virtual tables. System tables store changelog information and serve as a mediator that enables change data capture while preventing direct exposure of sensitive primary table data to virtual tables, thus resolving the privacy compliance issue

Inventive Principle:

Principle #24Intermediary (Mediator)

Solution Approach 2:

The patent segments the data storage and access architecture into multiple layers: primary tables for source data, system tables for changelog information, and virtual tables for change data capture. This segmentation allows each layer to serve its specific function while isolating sensitive information from unauthorized access

Inventive Principle:

Principle #1Segmentation

2Duration of action of stationary object

If data is retained in primary tables for historical analysis, then data retention requirements are met, but storage overhead increases and privacy compliance becomes more difficult

Engineering Contradiction:

Improvedata retention periodVSAvoidstorage overhead

Core Design Contradiction:

Duration of action of stationary objectVSQuantity of substance

Solution Approach 1:

The patent extracts changelog information from primary tables and stores it separately in system tables. This extraction allows the primary tables to be optimized for current data storage while system tables handle historical change data, reducing storage overhead and improving privacy compliance without compromising data retention requirements

Inventive Principle:

Principle #2Taking out (Extraction)

3Adaptability or versatility

If virtual tables are tied to underlying primary tables, then change data capture is enabled, but functionality is limited and data integrity is compromised when primary table data is deleted

Engineering Contradiction:

Improvechange data capture capabilityVSAvoiddata integrity

Core Design Contradiction:

Adaptability or versatilityVSReliability

Solution Approach 1:

System tables act as an intermediary that decouples virtual tables from primary tables. Virtual tables reference system tables for changelog information rather than directly depending on primary table data, ensuring that change data capture functionality remains reliable even when primary table data is modified or deleted

Inventive Principle:

Principle #24Intermediary (Mediator)

Solution Approach 2:

The patent performs preliminary action by capturing and storing changelog information in system tables before primary table data may be deleted or modified. This ensures that change data is preserved independently, maintaining data integrity and reliability for historical analysis

Inventive Principle:

Principle #10Preliminary action

Data Source

PatentUS20250013624A1Streams using persistent tables

Publication Date: 2025.01.09 SNOWFLAKE INC

AI summary

A system or persistent table may be generated storing changelog information of a primary base table. The system table may then be used to create streams of relevant information. In some examples, the streams may read from the system table for information past a retention period of the primary table while reading from the primary table information in the retention period.