CDC Data Pipeline (Core Layer)

How to design a Satellite to populate with CDC data?

To add CDC functionality to a satellite, simply add the column Status CDC and mark it with a stereotype. For…

To add CDC functionality to a satellite, simply add the column Status CDC and mark it with a stereotype.

For the Status CDC column the appropriate stereotype (CDC - Partially or CDC - Complete) must be selected, depending on the CDC data of the corresponding source table (of type SL - CDC Complete or SL - CDC Partially) in the Stage Layer.

Activating the CDC functionality is kept as simple as possible. The data modeler does not need to do more than add the column and set stereotypes for the column stereotype in the Data Vault Pattern.

The following pictures show an example of the configuration for a satellite with CDC functionality. One with Partially CDC and one with Complete CDC.

CDC Data Pipeline Core Raw Data Zone Table Sat Smart with CDC CompleteCDC Data Pipeline Core Raw Data Zone Table Sat Smart with CDC Complete

 

CDC Data Pipeline Core Raw Data Zone Table Sat Smart with CDC PartiallyCDC Data Pipeline Core Raw Data Zone Table Sat Smart with CDC Partially

The following table gives an overview of which Data Vault table stereotypes support CDC.

Data Vault Table  Table Stereotype CDC Complete CDC Partially
 Hub  CL - Hub  No  No
 Link  CL - Link  No  No
 Satellite (Hub)  CL -Satellite  Yes
 Yes
 CL - Satellite HashDiff  Yes (see hint)  Yes
 CL - Satellite Insert Only  No  No
 CL - Satellite bitemporal  No  No
 CL - Satellite bitemporal SI AlignsWith  No  No
 Satellite (Link)       CL - Link-Satellite  Yes  Yes
 CL - Link-Satellite EndDating  Yes  Yes
 CL - Link-Satellite HashDiff  Yes (see hint)  Yes
 CL - Link-Satellite Insert Only  No  No
 CL - Link-Satellite bitemporal  No  No
 CL - Link-Satellite bitemporal SI AlignsWith  No  No

To ensure data quality it is recommended to use a domain for the Status CDC column (see also the following topics).

Satellite with Hash-Diff - Only makes sense with Partially otherwise Hash-Diff is not needed.

Posted 1 year agoby Dirk.Lerner

How to design mappings for a CDC satellite (Hub)?

Mapping table of type SL - CDC Complete/Partially to CL - Satellite* The mapping of the columns from the table…

Mapping table of type SL - CDC Complete/Partially to CL - Satellite*

The mapping of the columns from the table of type SL - CDC Complete/Partially to type CL - Satellite* is kept simple. Connect the columns to be loaded from the table or view by drag & drop, give the mapping a suitable name and that's it.

The Business Key (Source, here column: Smart BK) has to be mapped to the Surrogate Key (Target, here column: Smart SK) in the Satellite.

The additional CDC column has to be mapped, too:

  • Status CDC

The exemplary additional Data Vault metadata columns must not be mapped. The Load Process Step takes care of the loading of the columns:

  • Load Timestamp
  • Load End Timestamp
  • Entity Id
  • Audit Trail Id (Name and Code as configured)

The following picture show exemplary mappings for a CDC Satellite. It doesn’t matter if the Satellite is of Partially or Complete CDC functionality.

CDC Stage to Core Mapping Editor Satellite HubCDC Stage to Core Mapping Editor Satellite Hub

Don’t forget the integrity load to the associated Hub!

Posted 1 year agoby Dirk.Lerner

How to design mappings for a CDC satellite (Link)?

Mapping table of type SL - CDC Complete/Partially to CL - Link Satellite* The mapping of the columns from the…

Mapping table of type SL - CDC Complete/Partially to CL - Link Satellite*

The mapping of the columns from the table of type SL - CDC Complete/Partially to type CL - Link Satellite* is kept simple. Connect the columns to be loaded from the table or view by drag & drop, give the mapping a suitable name and that's it.

The Business Key (Source, here columns: Registered Vehicle and License Plate) has to be mapped to the Surrogate Key (Target, here column: License Plate Belongs Vehicle SK) in the Link-Satellite (here and End-Dating Satellite).

The additional CDC column has to be mapped, too:

  • Status CDC

The exemplary additional Data Vault metadata columns must not be mapped. The Load Process Step takes care of the loading of the columns:

  • Load Timestamp
  • Load End Timestamp
  • Entity Id
  • Audit Trail Id (Name and Code as configured)

The following picture show exemplary mappings for a CDC Link-Satellite. It doesn’t matter if the Link-Satellite is of Partially or Complete CDC functionality.

CDC Stage to Core Mapping Editor edSatellite LinkCDC Stage to Core Mapping Editor edSatellite Link

Don’t forget the integrity load to the associated Link and Hubs!

Posted 1 year agoby Dirk.Lerner