System Timestamp Columns in Warehouse Tables

Datacoral adds a few standard timestamp columns to most of the warehouse tables that it actively updates.

  • __dc_timelabel: This marks the label of batch of data that the particular row was updated. It has the format YYYYMMDDHHmmSS, e.g., 20210410000000
  • __dc_load_time: This is a timestamp column that is available in the source tables populated by Datacoral connectors.

For CDC connectors, the following columns are added to each destination warehouse table.

  • __dc_cdc_modified_at: This is a timestamp column in nanoseconds which indicates the read timestamp of the WAL row. This timestamp is set by Datacoral as the system time in nanoseconds if the WAL row itself does not have a timestamp associated with it. PosgreSQL CDC connectors have a Datacoral-generated value. MySQL CDC connectors have a value that comes directly from the WAL.
  • __dc_cdc_deleted_at: This is a timestamp column in nanoseconds which indicates the read timestamp of the WAL row that indicates a delete operation. This value is the same as the __dc_cdc_modified_at of the delete WAL row.

When a historical sync is performed on a table of a CDC connector, __dc_cdc_modified_at and __dc_cdc_deleted_at will be NULL in the destination, only __dc_timelabel and __dc_load_time are populated. For rows that were updated using WAL rows, __dc_cdc_modified_at and __dc_cdc_deleted_at are populated.

So, a table in the source with the following schema

Ordinal PositionColumn Name
1pkcol
2col1
3updated_at

The corresponding destination table will have the following schema

Ordinal PositionColumn Name
1__dc_timelabel
2__dc_load_time
3__dc_cdc_modified_at
4__dc_cdc_deleted_at
5pkcol
6col1
7updated_at