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:
​General​
​Storage​
​Streaming​
​Kafka​
​Kinesis​
​Databases (CDC)​
​MySQL​
​PostgreSQL​
General
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.
Storage
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.
Streaming
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.
Kafka
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.
Kinesis
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.
Databases (CDC)
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
MySQL
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
Optional. Original SQL query that produced the change event. Instructions to enable query logging.
$gtid
string
If query logging is enabled, represents Global Transaction ID. Learn more.
PostgreSQL
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
 True if event originates from a heartbeat table. False if it originates from a tracked table.
$lsn
string
Log Sequence Number (LSN) represents location in the transaction log where the specific change event was recorded
​

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.

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.

Column	Data type	Description
$partition	long	The partition this data was read from.

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.

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.

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

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	Optional. Original SQL query that produced the change event. Instructions to enable query logging.
$gtid	string	If query logging is enabled, represents Global Transaction ID. Learn more.

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	True if event originates from a heartbeat table. False if it originates from a tracked table.
$lsn	string	Log Sequence Number (LSN) represents location in the transaction log where the specific change event was recorded

PostgreSQL

Transformation jobs

Last modified 27d ago