Comment on page
System columns
This article explains the system columns that are automatically appended to your tables.
When you ingest your data into SQLake, your table is enriched with various system columns that are generated by SQLake based on your data.
The specific columns and what they describe are detailed below:
These columns are applicable to all staging tables.
Column | Data type | Description |
---|---|---|
$commit_time | timestamp | The time at which the record was committed to the current table. When reading historical data, this is the current time at which we're reading the data. When reading the latest data written to the source, this is the same as the source time. |
$event_date | date | The date at which the event was fired or recognized in the data source. This is typically used as a date partition column for your staging table. |
$event_timestamp | instant | The time at which the event was fired or recognized in the data source. |
$compression | string | The compression type, if applicable. |
These columns are applicable to staging tables with data ingested from storage connections (e.g. S3).
Column | Data type | Description |
---|---|---|
$file_last_modified | long | The time the file was last modified. |
$file_name | string | The name of the file that was read. |
$file_size | long | The size of the file that was read in bytes. |
$row_number | long | The index within the file corresponding to each record. |
$compressed_file_name | string | The name of the file that was read, if it was compressed. |
$column_count | long | The number of columns read for CSV files. |
These columns are applicable to staging tables with data ingested from streaming sources.
Column | Data type | Description |
---|---|---|
$partition | long | The partition this data was read from. |
These columns are applicable to staging tables with data ingested from Kafka.
Column | Data type | Description |
---|---|---|
$row_number | long | The index corresponding to each record. |
$kafka_timestamp | long | The time at which the event was fired or recognized in Kafka. |
$topic | string | The name of the topic the data was read from. |
These columns are applicable to staging tables with data ingested from Kinesis.
Column | Data type | Description |
---|---|---|
$shard | string | The ID of the shard the data was read from. |
$stream | string | The name of the stream the data was read from. |
$kinesis_timestamp | long | The time at which the event was fired or recognized in Kinesis. |
$offset | long | The offset of the stream that the data is read from. |
These columns apply to staging table with data ingested from database (CDC) sources.
Column | Data type | Description |
---|---|---|
$database_name | string | The name of the source database |
$table_name | string | The name of the source table |
$operation | string | Row operation. Values are: "create, "update", "delete", "snapshot". TRUNCATE and MESSAGE operations are not currently supported and will result in an error. |
$from_snapshot | boolean | Whether the row was created from a snapshot or a change event. Values are:
"true": snapshot
"false": from latest change event |
$primary_key | string | If a source table includes one or more primary keys, this field holds a concatenation, separated by ~ , of all primary key values. |
$old_row | string | Optional. Represents the row prior the captured change. |
$binlog_timestamp | timestamp | Timestamp for when the event was processed by the connector |
$is_delete | boolean | Boolean representation for $operation = 'delete'.
True: row has been deleted
False: row has not been deleted |
These columns apply to staging tables with data ingested from MySQL
Column | Data type | Description |
---|---|---|
$full_table_name | string | Concatenation of $database_name and $table_name separated by a period "." |
$binlog_file_name | string | Binlog name where the event was recorded |
$binlog_file_position | string | Position within the binlog |
$binlog_row | string | Row within the change event |
$query | string | |
$gtid | string |
These columns apply to staging tables with data ingested from PostgreSQL.
Column | Data type | Description |
---|---|---|
$schema_name | string | The name of the source schema |
$full_table_name | string | Concatenation of $database_name, $schema_name and $table_name separated by a period "." |
$is_hearbeat | boolean | |
$lsn | string | Log Sequence Number (LSN) represents location in the transaction log where the specific change event was recorded |
Last modified 3mo ago