Methods and apparatuses for improved data ingestion using standardized plumbing fields

Inventors

Hoyle, Keith • Salgado, Manuel • Brown, Marion A. • Yalamanchili, Rajesh

Assignees

McKesson Corp

Interested in licensing this patent?

MTEC can help explore whether this patent might be available for licensing for your application.

Publication Number

US-11442953-B2

Patent

Publication Date

2022-09-13

Expiration Date


Abstract

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.

Core Innovation

The invention provides improved data ingestion in a relational database management system by ingesting one or more records from a plurality of data sources into one or more stage tables in a persistent staging layer. Each stage table corresponds to one of the plurality of data sources, and the ingestion uses a set of standardized plumbing fields comprising descriptive fields including a stage table primary key field, a source primary key field, and a source attribute field. The approach persistently stages incoming and preexisting records to support traceability and recovery from processing errors, while also allowing ingestion without predefining the eventual data model.

For each particular record, the invention determines whether the particular record corresponds to any preexisting record in the one or more stage tables. The determination is based on a hash of at least one descriptive field and a date/time associated with the particular record, where the hash of the at least one descriptive field and the date/time uniquely identifies the particular record. The plurality of hashes of the at least one descriptive field and the date/time associated with each preexisting record uniquely identifies one preexisting record, enabling correspondence and matching between incoming and preexisting staged records.

After determining correspondence, the invention generates, using the one or more stage tables in the persistent staging layer, a data integration layer for modeling data represented by the data ingested into the persistent staging layer. The documented approach includes handling deletes via a delete flag and detecting record changes via comparisons that include source attribute comparisons and stage table primary key comparison, including reversion to prior versions as described. The data integration layer is updated with additionally ingested records and can be used to support query processing via an information-retrieval query using a subset of stage tables.

Claims Coverage

The document contains three independent claim types—one method claim, one apparatus claim, and one computer program product claim—each sharing the same core ingestion-to-modeling workflow. Across the independent claims, the inventive features center on persistent per-source stage tables with standardized plumbing fields, correspondence detection using uniquely identifying hashes tied to descriptive fields and an associated date/time, and generation of a data integration layer for modeling the ingested data.

Persistent stage-table ingestion using standardized plumbing fields

Ingesting, using a set of standardized plumbing fields comprising descriptive fields including a stage table primary key field, a source primary key field, and a source attribute field, one or more records from a plurality of data sources into one or more stage tables in a persistent staging layer, wherein each of the one or more stage tables corresponds to one of the plurality of data sources.

Record correspondence determination using hashes of descriptive fields and associated date/time

Determining whether each particular record of the one or more records corresponds to any preexisting record of a plurality of preexisting records in the one or more stage tables based on: a hash of at least one descriptive field and a date/time associated with the particular record, wherein the hash of the at least one descriptive field and the date/time associated with the particular record uniquely identifies the particular record, and wherein the date/time associated with the particular record comprises at least one of a timestamp or a date that the particular record was ingested into the persistent staging layer, and a plurality of hashes of the at least one descriptive field and a date/time associated with each preexisting record of the plurality of preexisting records, wherein each hash of the plurality of hashes uniquely identifies one preexisting record of the plurality of preexisting records.

Generating a data integration layer for modeling ingested data

Generating, using the one or more stage tables in the persistent staging layer, a data integration layer for modeling data represented by the data ingested into the one or more stage tables in the persistent staging layer.

Using subset of stage tables for information retrieval queries

Processing an information-retrieval query, identifying data using the data integration layer and a subset of the one or more stage tables, and outputting the identified data.

Overall, the claim coverage centers on persistent staging per data source with standardized plumbing fields, unique correspondence determination using hashes of descriptive fields plus associated timestamp/date/time, and generation of a data integration layer to model the ingested data. Some dependent claim refinements also cover retrieval-time use of an integration layer with a subset of stage tables, and additional deletion/change handling and field comparison logic, as stated in the provided claim set.

Stated Advantages

Enables persistent staging that supports traceability/audit support and recovery from long-retention processing errors, as described.

Supports ingestion without predefining the eventual data model, as described.

Documented Applications

Modeling data represented by data ingested into the persistent staging layer via a generated data integration layer, for downstream query processing.

Information-retrieval query processing using a data integration layer and a subset of stage tables to output identified data.

JOIN OUR MAILING LIST

Stay Connected with MTEC

Keep up with active and upcoming solicitations, MTEC news and other valuable information.