One To One Data Copy Pipeline (General)

Which Stereotypes and Columns for One To One Data Copy are available?

Table type: One To One Data Copy - Stores all loaded data without any changes or filter. Table stereotype: O -…

Table type: One To One Data Copy - Stores all loaded data without any changes or filter.

  • Table stereotype: O - One To One Data Copy

    This table stores (insert) all data which were available all time. There is no versioning of the data.

  • Mandatory column: Audit Trail Id (Name and Code as configured)

    This column stores an Id which ties the loaded data to the ExMeX Logging Framework.

Optional Columns

  • Optional column: Entity Id (Name and Code as configured)

    This column stores an Id which ties the target table to the ExMeX Logging Framework.

  • Optional column: Load Timestamp (LDTS) (Name and Code as configured)

    This column stores the AssertionTimeFrom timestamp inherited from the executing batch (timestamp at which the batch was started). See also database-object MetadataZone.MetadataForExecuteDataLogisticBatch.

  • Optional column: Load End Timestamp (LDTS) (Name and Code as configured)

    This column stores the AssertionTimeBefore timestamp inherited from the executing batch (timestamp at which the batch was started) for end-dating previous rows and the High End Timestamp for new inserted rows.
    Applicable only in Temporal- or CDC-Mode and if column Load Timestamp (LDTS) is available.

  • Optional column: Hash-Diff (Code: HashDiff)

    This column stores a hash, generated with all non CDC Compare Key columns. It increases speed to identify changes.
    Applicable only in Temporal- or Delta-Mode.

  • Optional column: Status CDC (Code: StatusCDC)

    This column stores a flag, generated by CDC: I,U,D
    To ensure data quality it is recommended to use a domain for the Status CDC column.
    Applicable only in Delta-Mode (No column stereotype on column Audit Trail Id (Name and Code as configured)) and only flag-values I,U are available.

"Optional" column stereotypes drives the mode

The "optional" stereotypes control the mode of how the target table is loaded. Therefore one of the column stereotypes must be used.

  • Column stereotype: Stack (Optional) -> Stack-Mode

    This stereotype identifies if an incoming full or delta load should be stacked into the target table.
    Use cases may be a Persitent Staging Area (PSA) or a History Zone (HiZ).
    Applicable only on column Audit Trail Id (Name and Code as configured).

  • Column stereotype: Truncate (Optional) -> Truncate-Mode

    This stereotype identifies if an incoming full (or maybe delta) load should be loaded into a previously truncated target table.
    Use cases may be a Sandbox or any Full Load with previous truncate (Truncate/Insert data - equates CDC I,U,D).
    Applicable only on column Audit Trail Id (Name and Code as configured).

  • Column stereotype: CDC - Compare Key (Optional) -> Delta Mode

    This stereotype identifies one or many columns which drives the compare to identify changes and capture them.
    Use cases may be a Sandbox or any Full/Delta Load (Update/Insert data - equates CDC I,U).
    Applicable on any (target) column except Audit Trail Id (Name and Code as configured).

  • Column stereotype: Temporal (Optional) -> Temporal-Mode

    This stereotype identifies if incoming data (full or delta) should be loaded historized into a target table.
    Use cases may be a Persitent Staging Area (PSA) or a History Zone (HiZ)
    Applicable only on column Load Timestamp (LDTS) (Name and Code as configured).

  • Column stereotype: CDC - Complete (Optional) -> CDC-Mode

    This stereotype identifies if incoming data (full or delta) should be loaded historized into a target table by using CDC information. This CDC information must be provided by a mandatory upfront CDC pipeline or external (e.g. from a source system) CDC data.
    Use cases may be a Persitent Staging Area (PSA) or a History Zone (HiZ)
    Applicable only on column Status CDC (Code: StatusCDC).
Posted 1 year agoby Dirk.Lerner

Any background information?

DAPI The DAPI MetadataZoneDapi.MetadataForGenerateOneToOneDataCopy is the basis for generating the LPS with the LoadPatternId 19. Generator The script for generating…

DAPI

The DAPI MetadataZoneDapi.MetadataForGenerateOneToOneDataCopy is the basis for generating the LPS with the LoadPatternId 19.

Generator

The script for generating the LPS with LoadPatternId 19 is One To One Data Copy.sql

Posted 1 year agoby Dirk.Lerner