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 Position | Column Name |
---|---|
1 | pkcol |
2 | col1 |
3 | updated_at |
The corresponding destination table will have the following schema
Ordinal Position | Column Name |
---|---|
1 | __dc_timelabel |
2 | __dc_load_time |
3 | __dc_cdc_modified_at |
4 | __dc_cdc_deleted_at |
5 | pkcol |
6 | col1 |
7 | updated_at |