Lead your team forward
OCT 24 / 9AM ET Register nowInternal Columns
This topic covers the columns not defined in the Model (see Model) and created or interpreted by the engine.
Database Columns
Database (DB) columns are part of each instance or master table maintained by the engine. The column names (both database columns and pseudocolumns) cannot be renamed or used for a different purpose than specified. Moreover, their values cannot be changed directly, but only by the engine processes or features.
The DB columns are added by the engine automatically. Some of the columns are also available in ONE plans serving as transitions under either database column name (DB) or audit pseudocolumn name (AP) - see Available in a plan as DB/AP column in the table below. There are also Audit Pseudocolumns (see Audit Pseudocolumns), which are (usually) not related to any database column, and they are available in ONE plans only.
There are no database types shown in the following table because they are dependent on the particular DB used.
Database column | Instance table | Master table | Description | Available in a plan as DB/AP | Data type |
---|---|---|---|---|---|
id |
x |
|
DB |
long_int*** |
|
id |
x |
|
DB |
long_int*** |
|
id |
|
long_int*** |
|||
source_id* |
x |
+ |
|
DB |
string |
master_id |
x |
+ |
|
DB |
long_int*** |
match_isolate |
x |
|
DB |
boolean |
|
eng_origin |
x |
|
AP |
string |
|
duplicate_id |
|||||
eng_key |
|||||
eng_active |
x |
x |
AP |
boolean |
|
eng_existing |
|
N/A |
boolean |
||
eng_existing |
x |
|
N/A |
boolean |
|
eng_existing |
|
||||
eng_modified_by |
x |
|
string |
||
eng_nature |
x |
|
boolean |
||
eng_source_timestamp |
x |
see below |
AP |
datetime |
|
eng_source_system |
x |
see below |
AP |
string |
|
eng_creation_tid |
x |
x |
see below |
AP** |
long_int*** |
eng_creation_date |
x |
x |
see below |
AP |
datetime |
eng_deletion_tid |
x |
x |
see below |
AP** |
long_int*** |
eng_deletion_date |
x |
x |
see below |
AP |
datetime |
eng_last_update_tid |
see below |
AP** |
long_int*** |
||
eng_last_update_date |
x |
x |
see below |
AP |
datetime |
eng_last_source_update_tid |
x |
see below |
AP** |
long_int*** |
|
eng_last_source_update_date |
x |
see below |
AP |
datetime |
|
eng_activation_tid |
x |
x |
see below |
AP** |
long_int*** |
eng_activation_date |
x |
x |
see below |
AP |
datetime |
eng_deactivation_tid |
x |
x |
see below |
AP** |
long_int*** |
eng_deactivation_date |
x |
x |
see below |
AP |
datetime |
xid** |
x |
x |
primary key of each table |
N/A |
long_int*** |
xctid** |
x |
x |
internal technical column |
N/A |
long_int*** |
-
Now defined as a string with size 1000, mapped to DB as varchar(2000), or varch2(2000) for Oracle (2000 because of UTF). This is hardcoded in the MDM source code now. The maximum allowed content is 1000 chars.
-
Technically, the column might be available in ONE plan. In general, various TID/DATE appearances in ONE plans depend on a particular MMD definition.
-
Currently used in MDM only. It refers to a data type longer than integer and shorter than long, with its size limited to 8 bytes due to performance reasons (max value is 264, roughly 18 exabytes).
-
Present only when VLDB Persistence is used, see Persistence Layer, section VLDB Persistence.
-
-
-
Audit Pseudocolumns
The following pseudocolumns are available in various places across engine configuration, for example, ONE plans and WebServices. DB columns are described above.
Batch Load Pseudocolumns
Appear only in batch load plans, and then they are remapped into an eng_ prefixed columns and persisted as described above.
origin (string)
Serves to map already defined origin, that is equal to textual representation eng_origin.
change_type (string)
Serves as input to the delta detection process. It’s mapped into eng_change_type.
source_timestamp (datetime)
Serves to provide external timestamp to the MDM. It’s mapped into eng_source_timestamp.
Transition Plans Pseudocolumns
The allowing pseudocolumns are available in DQC plans serving as transitions in MDM and in export plans.
Status Pseudocolumns
eng_active (boolean)
Instance records: determines if the record is reported from the source system as existing (when using sourceDeletionStaregy=DEACTIVATE
).
Master records: Determines if at least one instance record corresponding to this master has eng_active=true
.
eng_origin (string)
Instance records only. Contains the textual representation of the record origin. It is stored as origin_id column in a DB.
eng_source_system (string)
Instance records only. Contains the textual representation of the system corresponding to the record origin. DB column name is eng_system.
eng_source_timestamp (datetime)
Instance records only. The aim of this column is to prevent entering and overwriting the most recent record by the older ones based on the timestamp provided.
Modification Date Pseudocolumns
A set of pseudocolumn pairs (date + transaction id) describing when a particular record-level event took place. Date columns are of DATETIME type, transaction IDs are of LONG type. Only the date-related pseudocolumns are available in all transition and export plans, but all the columns ale persisted in a database as described.
The column values for new records are set by default during the Change Detection phase, however, for expanded records (for example CopyColumns, or aggregation) the values will be set during the Committing phase. This behavior means that expanded records always show previous values in the transition plans, and these values will be different from what you find in the MDM storage once the transaction completes. |
Transaction ID column | Date column | Event |
---|---|---|
eng_creation_tid |
eng_creation_date |
Record creation. Always non-null. |
eng_deletion_tid |
eng_deletion_date |
Record deletion. Null for existing records. Only makes sense in internal plans and delta exports. |
eng_last_update_tid |
eng_last_update_date |
Last update of the record. Will be changed every time record is changed, including record I/U/D from input or loaded during processing. Always non-null. |
eng_last_source_update_tid |
eng_last_source_update_date |
Only for instance records; last update of the record in delta detection phase (excludes internal calculations). Always non-null. That is, last time the record was sent by source system and change was detected in the delta detection phase. |
eng_activation_tid |
eng_activation_date |
(Last) record activation. Always non-null. |
eng_deactivation_tid |
eng_deactivation_date |
(Last) record deactivation. Null for active records. |
Inner Plan Pseudocolumns
The following pseudocolumns are not persisted in the database - they are only available in transition plans (computed for each loading operation):
eng_change_type (char)
Determines if the record was changed in the current load. The change type is evaluated against the eng_existing column.
-
N - No change (this normally appears only during retrofit operations).
-
I - Newly added record.
-
D - Deleted record.
-
U - An existing record that was modified (no insert/delete).
eng_activity_change_type (char)
Similar to eng_change_type, determines if the record was changed in the current load with respect to eng_active. See Status Pseudocolumns for reference.
-
N - No change (this normally appears only during retrofit operations).
-
I - Newly activated record.
-
D - Deactivated or inactive record.
-
U - An active record that was modified (no activation/deactivation).
eng_external_record (boolean)
True
if the record is not an actual record in the hub, for example, a record sent in by the Identify Service (see Services).
Delta Export Pseudocolumns
Only appear during the delta data exports and they are not persisted in the database (but the information to be able to determine the state is preserved).
eng_change_type (char)
Determines the change between the state in reference time and the current state.
-
I - The record has been newly inserted since the reference time (present at the moment).
-
D - The record has been deleted since the reference time (not present at the moment).
-
U - The record has been modified since the reference time. No insert or delete operation took place.
-
If the record was deleted and then inserted (or vice versa) since the reference time, I (resp. D) will be reported - depending on whether the record is (I) or is not (D) present currently.
eng_activity_change_type (char)
Determines the change of the eng_active pseudocolumn.
-
I - The record has been newly activated since the reference time (active at the moment).
-
D - The record has been deactivated since the reference time (not active at the moment).
-
X - The record was inactive at the reference time, hasn’t been activated in the period. Only appears when the scope is
EXISTING
. -
U - The record has been modified since the reference time. No change on
eng_active
flag took place. This also means thateng_active = true
, as otherwise anX
would be reported. -
If the record was deactivated and then activated (or vice versa) since the reference time, I (resp. D) will be reported - depending on whether the record is (I) or is not (D) active currently.
Web Services Pseudocolumns
Appear only in web services.
Request Pseudocolumns
sourceId (string)
Used to map instance primary key to the source_id
attribute for traversal/genericTraversal (see Services).
origin (string)
Serves as identification of requested record (together with sourceId
) for some of the MDM Native Services (see Services).
change_type (string)
Serves as input to the delta detection process for RW web service only.
It’s mapped into eng_change_type
.
source_timestamp (datetime)
Serves as input to the change detection process for RW web service only.
It’s mapped into eng_source_timestamp
.
Was this page useful?