Standardized Plumbing Fields for Data Ingestion

Resolve Bottlenecks,
Find Innovative SolutionsGenerate Solutions

Solution Overview

Problem

Traditional data modeling techniques require prior knowledge of important information and data source structures, leading to delays in implementation, lack of transparency, and potential audit issues, as well as unrecoverable processing errors due to transient staging areas.

Innovation Solution

The use of standardized plumbing fields in a relational database management system for data ingestion, including descriptive fields like stage table primary key, source primary key, and timestamp fields, allows for efficient data ingestion and tracing, enabling data modeling without altering source data and excluding the platform from audits.

Engineering Contradictions & Design Principles

VSEngineering Contradiction Analysis

1Manufacturing precision

If traditional data modeling techniques are used requiring prior knowledge of important information and data source structures, then data model accuracy can be ensured, but implementation time is significantly delayed

Engineering Contradiction:

Improvedata model accuracyVSAvoidimplementation time

Core Design Contradiction:

Manufacturing precisionVSLoss of time

Solution Approach 1:

The patent applies preliminary action by creating stage tables and ingesting source data before the data model is fully designed or business requirements are determined. The stage tables are pre-configured with standardized plumbing fields that will accommodate future modeling needs, allowing data to be loaded and stored without waiting for complete model specification. This enables parallel processing of data ingestion and model design activities.

Inventive Principle:

Principle #10Preliminary action

Solution Approach 2:

The patent segments the data modeling process into distinct layers: stage tables for raw data ingestion, intermediate tables for processing, and final data model tables. This segmentation allows each layer to be developed and populated independently, with the stage tables serving as a temporary holding area that doesn't require complete knowledge of the final model structure.

Inventive Principle:

Principle #1Segmentation

2Adaptability or versatility

If traditional data modeling techniques manipulate source data, then data can be transformed to meet business requirements, but transparency and auditability are reduced

Engineering Contradiction:

Improvedata transformation capabilityVSAvoidtraceability to source data

Core Design Contradiction:

Adaptability or versatilityVSLoss of information

Solution Approach 1:

The patent creates copies of source data in stage tables rather than modifying the original source data. Each stage table contains replicated data with added plumbing fields that track the source and transformation history. This copying approach allows extensive manipulation and transformation in the stage tables while preserving the original source data unchanged, maintaining complete traceability.

Inventive Principle:

Principle #26Copying

Solution Approach 2:

The stage tables serve as an intermediary layer between source data and the final data model. They include plumbing fields such as source system identifiers, source table names, and row identifiers that act as mediators to track the origin and transformation of each data element. This intermediary structure enables transparent auditing while allowing flexible data transformation.

Inventive Principle:

Principle #24Intermediary (Mediator)

3Productivity

If transient staging areas are used for data processing, then processing speed is improved, but processing errors become unrecoverable past the retention window

Engineering Contradiction:

Improvedata processing speedVSAvoiderror recoverability

Core Design Contradiction:

ProductivityVSReliability

Solution Approach 1:

The patent employs stage tables that function as temporary, disposable storage areas for data processing. These stage tables are designed to hold data temporarily during the modeling process, and once data is successfully transformed and loaded into the final model, the stage tables can be cleared or replaced. The plumbing fields embedded in stage tables capture sufficient metadata to enable error recovery and reprocessing if needed, combining the speed of transient storage with improved reliability.

Inventive Principle:

Principle #27Cheap short-living objects (Disposable)

Data Source

PatentUS11442953B2Methods and apparatuses for improved data ingestion using standardized plumbing fields

Publication Date: 2022.09.13 MCKESSON CORPORATION

AI summary

Embodiments are disclosed for improved data modeling in a relational database management system. In the context of a method, an example embodiment includes ingesting, by data ingestion circuitry and using a set of standardized plumbing fields comprising descriptive fields including at least a stage table primary key field, a source primary key field, and a source attribute field, one or more records into one or more stage tables in a staging layer. This example embodiment further includes generating, by data modeling circuitry and using the one or more stage tables in the staging layer, a data integration layer for modeling data represented by the data ingested into the one or more stage tables in the staging layer. Corresponding apparatuses and computer program products are also provided.