# US 2017/0286504 A1 · Methods and apparatuses for improved data ingestion using standardized plumbing fields

> Agents: this data is available as JSON from the patentgrep API and MCP server — see https://patentgrep.com/llms.txt

- Number: US 2017/0286504 A1
- Document ID: US20170286504A1
- Status: Published application
- Kind: A1
- Application number: 15/086,534
- Filed: 2016-03-31
- Published: 2017-10-05
- Earliest claimed: 2016-03-31
- Assignees: McKesson Corporation
- Applicants: Mckesson Corporation
- Inventors: Keith Hoyle; Manuel Salgado; Marion A. Brown; Rajesh Yalamanchili
- Web page: https://patentgrep.com/patent/US20170286504A1

## CPC

- G06F 16/254: {Extract, transform and load [ETL] procedures, e.g. ETL data flows in data warehouses}
- G06F 16/217: {Database tuning (G06F16/2282 takes precedence; database performance monitoring G06F11/3409)}
- G06F 16/2282: {Tablespace storage structures; Management thereof}
- G06F 16/25: Integrating or interfacing systems involving database management systems
- G06F: ELECTRIC DIGITAL DATA PROCESSING (computer systems based on specific computational models G06N)

## 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.

## Claims

### Claim 1

A method for improved data ingestion in a relational database management system, the method comprising:
ingesting, by data ingestion circuitry and 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 staging layer, wherein each of the one or more stage tables corresponds to one of the plurality of data sources; and
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.

### Claim 2 (depends on 1)

The method of claim 1, further comprising:
processing a query requesting information for retrieval;
identifying, by the data modeling circuitry, using the data integration layer and a subset of the one or more stage tables, data corresponding to the information for retrieval; and
outputting the identified data.

### Claim 3 (depends on 1)

The method of claim 1, wherein ingesting a target record from a particular data source includes:
storing a copy of the target record in a particular stage table in the staging layer that corresponds to the particular data source;
inserting the set of standardized plumbing fields into the copy of the target record, the set of standardized plumbing fields including a timestamp field in addition to the set of descriptive fields;
populating descriptive fields of the set of standardized plumbing fields based on content of the target record; and
populating the timestamp field with a current date and time.

### Claim 4 (depends on 3)

The method of claim 3, wherein the set of descriptive fields further includes:
a data source name field, a data source data set name field, and a source delete flag.

### Claim 5 (depends on 4)

The method of claim 4, wherein the set of descriptive fields further includes at least one of an expiry timestamp field and a natural key field.

### Claim 6 (depends on 1)

The method of claim 1, wherein ingesting the one or more records into the one or more stage tables in the staging layer includes, for each particular record of the one or more records:
determining whether the particular record corresponds to a preexisting record in one of the stage tables in the staging layer;
in an instance in which the particular record corresponds to a preexisting record,
determining whether the particular record has not been deleted in its corresponding data source,
in an instance in which the particular record has not been deleted,
determining whether the particular record is different than the preexisting record, and
in an instance in which the particular record is different than the preexisting record, storing the particular record; and
in an instance in which the particular record does not correspond to a preexisting record, storing the particular record.

### Claim 7 (depends on 6)

The method of claim 6, wherein determining whether the particular record is different than the preexisting record includes:
comparing a source attribute field of the particular record to a source attribute field of the preexisting record;
in an instance in which the source attribute field of the particular record matches the source attribute field of the preexisting record,
comparing a stage table primary key field of the particular record to a stage table primary key field of the preexisting record,
in an instance in which the stage table primary key field of the particular record matches the stage table primary key field of the preexisting record, concluding that the particular record is not different than the preexisting record, and
in an instance in which the stage table primary key field of the particular record does not match the stage table primary key field of the preexisting record, concluding that the particular record is different than the preexisting record; and
in an instance in which the source attribute field of the particular record does not match the source attribute field of the preexisting record, concluding that the particular record is different than the preexisting record.

### Claim 8 (depends on 1)

The method of claim 1, further comprising:
ingesting, by the data ingestion circuitry and using the standardized plumbing fields, one or more additional records from the plurality of data sources into the one or more stage tables in the staging layer; and
updating, by the data modeling circuitry, the data integration layer based on ingested one or more additional records.

### Claim 9

An apparatus for improved data ingestion in a relational database management system, the apparatus comprising at least one processor and at least one memory storing computer-executable instructions, that, when executed by the at least one processor, cause the apparatus to:
ingest, 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 staging layer, wherein each of the one or more stage tables corresponds to one of the plurality of data sources; and
generate, 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.

### Claim 10 (depends on 9)

The apparatus of claim 9, wherein the computer-executable instructions, when executed by the apparatus, further cause the apparatus to:
process a query requesting information for retrieval;
identify, using the data integration layer and a subset of the one or more stage tables, data corresponding to the information for retrieval; and
output the identified data.

### Claim 11 (depends on 9)

The apparatus of claim 9, wherein the computer-executable instructions, when executed by the apparatus, further cause the apparatus to ingest a target record from a particular data source by causing the apparatus to:
store a copy of the target record in a particular stage table in the staging layer that corresponds to the particular data source;
insert the set of standardized plumbing fields into the copy of the target record, the set of standardized plumbing fields including a timestamp field in addition to the set of descriptive fields;
populate descriptive fields of the set of standardized plumbing fields based on content of the target record; and
populate the timestamp field with a current date and time.

### Claim 12 (depends on 11)

The apparatus of claim 11, wherein the set of descriptive fields further includes:
a data source name field, a data source data set name field, and a source delete flag.

### Claim 13 (depends on 12)

The apparatus of claim 12, wherein the set of descriptive fields further includes at least one of an expiry timestamp field and a natural key field.

### Claim 14 (depends on 9)

The apparatus of claim 9, wherein the computer-executable instructions, when executed by the apparatus, cause the apparatus to ingest the one or more records into the one or more stage tables in the staging layer by causing the apparatus to, for each particular record of the one or more records:
determine whether the particular record corresponds to a preexisting record in one of the stage tables in the staging layer;
in an instance in which the particular record corresponds to a preexisting record,
determine whether the particular record has not been deleted in its corresponding data source,
in an instance in which the particular record has not been deleted,
determine whether the particular record is different than the preexisting record, and
in an instance in which the particular record is different than the preexisting record, store the particular record; and
in an instance in which the particular record does not correspond to a preexisting record, store the particular record.

### Claim 15 (depends on 14)

The apparatus of claim 14, wherein the computer-executable instructions, when executed by the apparatus, cause the apparatus to determine whether the particular record is different than the preexisting record by causing the apparatus to:
compare a source attribute field of the particular record to a source attribute field of the preexisting record;
in an instance in which the source attribute field of the particular record matches the source attribute field of the preexisting record,
compare a stage table primary key field of the particular record to a stage table primary key field of the preexisting record,
in an instance in which the stage table primary key field of the particular record matches the stage table primary key field of the preexisting record, conclude that the particular record is not different than the preexisting record, and
in an instance in which the stage table primary key field of the particular record does not match the stage table primary key field of the preexisting record, conclude that the particular record is different than the preexisting record; and
in an instance in which the source attribute field of the particular record does not match the source attribute field of the preexisting record, conclude that the particular record is different than the preexisting record.

### Claim 16 (depends on 9)

The apparatus of claim 9, wherein the computer-executable instructions, when executed by the apparatus, further cause the apparatus to:
ingest, using the standardized plumbing fields, one or more additional records from the plurality of data sources into the one or more stage tables in the staging layer; and
update the data integration layer based on ingested one or more additional records.

### Claim 17

A computer program product comprising at least one non-transitory computer-readable storage medium for improved data ingestion in a relational database management system, the at least one non-transitory computer-readable storage medium storing computer-executable instructions that, when executed, cause an apparatus to:
ingest, 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 staging layer, wherein each of the one or more stage tables corresponds to one of the plurality of data sources; and
generate, 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.

### Claim 18 (depends on 17)

The computer program product of claim 17, wherein the computer-executable instructions, when executed, further cause the apparatus to:
process a query requesting information for retrieval;
identify, using the data integration layer and a subset of the one or more stage tables, data corresponding to the information for retrieval; and
output the identified data.

### Claim 19 (depends on 17)

The computer program product of claim 17, wherein the computer-executable instructions, when executed, further cause the apparatus to ingest a target record from a particular data source by causing the apparatus to:
store a copy of the target record in a particular stage table in the staging layer that corresponds to the particular data source;
insert the set of standardized plumbing fields into the copy of the target record, the set of standardized plumbing fields including a timestamp field in addition to the set of descriptive fields;
populate descriptive fields of the set of standardized plumbing fields based on content of the target record; and
populate the timestamp field with a current date and time.

### Claim 20 (depends on 17)

The computer program product of claim 17, wherein the computer-executable instructions, when executed, further cause the apparatus to ingest the one or more records into the one or more stage tables in the staging layer by causing the apparatus to, for each particular record of the one or more records:
determine whether the particular record corresponds to a preexisting record in one of the stage tables in the staging layer;
in an instance in which the particular record corresponds to a preexisting record,
determine whether the particular record has not been deleted in its corresponding data source,
in an instance in which the particular record has not been deleted,
determine whether the particular record is different than the preexisting record, and
in an instance in which the particular record is different than the preexisting record, store the particular record; and
in an instance in which the particular record does not correspond to a preexisting record, store the particular record.
