Append metadata

Overview

When using the Sync All Tables shared job component, metadata columns are appended to the target table. This article lists the metadata columns, and describes how the field values are derived.

By default, all metadata columns are appended to the target table. If you set the component's Append All Metadata property to N, only those metadata columns required by the job will be appended.

Note

With regard to MTLN_CDC_LAST_CHANGE_TYPE, the Sync All Tables shared job writes to your cloud data warehouse using the following codes as a reference for the job operation:

• c = create
• r = read (applies only to snapshots)
• u = update
• d = delete

---

Snowflake metadata

Full metadata coverage

Metadata column	Data type	Source
MTLN_CDC_SRC_DATABASE	VARCHAR	metadata, db
MTLN_CDC_SRC_SCHEMA	VARCHAR	metadata, schema
MTLN_CDC_SRC_TABLE	VARCHAR	metadata, table
MTLN_CDC_SRC_VERSION	NUMBER	source_version
MTLN_CDC_LAST_CHANGE_TYPE	VARCHAR	metadata, op
MTLN_CDC_LAST_COMMIT_TIMESTAMP	NUMBER	metadata, ts_ms
MTLN_CDC_PROCESSED_DATE_HOUR	TIMESTAMP	date_hour
MTLN_CDC_FILENAME	VARCHAR	filename
MTLN_CDC_FILEPATH	VARCHAR	filepath
MTLN_CDC_SEQUENCE_NUMBER	depends on source	See Sequence number calculation
MTLN_CDC_LOAD_BATCH_ID	NUMBER	run_history_id
MTLN_CDC_LOAD_TIMESTAMP	TIMESTAMP	CURRENT_TIMESTAMP()

Minimum - change log

Metadata column	Data type	Source
MTLN_CDC_SRC_VERSION	NUMBER	source_version
MTLN_CDC_LAST_CHANGE_TYPE	VARCHAR	metadata, op
MTLN_CDC_PROCESSED_DATE_HOUR	TIMESTAMP	date_hour
MTLN_CDC_FILENAME	VARCHAR	filename
MTLN_CDC_SEQUENCE_NUMBER	depends on source	See Sequence number calculation

Minimum - copy table

Metadata column	Data type	Source
MTLN_CDC_SRC_VERSION	NUMBER	source_version
MTLN_CDC_PROCESSED_DATE_HOUR	TIMESTAMP	date_hour
MTLN_CDC_FILENAME	VARCHAR	filename
MTLN_CDC_SEQUENCE_NUMBER	depends on source	See Sequence number calculation

Soft deletes

Metadata column	Data type	Source
MTLN_CDC_DELETED	BOOLEAN	Indicator

---

Redshift metadata

Full metadata coverage

Metadata column	Data type	Source
mtln_cdc_src_database	Text	metadata, db
mtln_cdc_src_schema	Text	metadata, schema
mtln_cdc_src_table	Text	metadata, table
mtln_cdc_src_version	Integer	source_version
mtln_cdc_last_change_type	Text	metadata, op
mtln_cdc_last_commit_timestamp	Integer	metadata, ts_ms
mtln_cdc_processed_date_hour	DateTime	date_hour
mtln_cdc_filename	Text	filename
mtln_cdc_sequence_number	depends on source	see Sequence number calculation
mtln_cdc_load_batch_id	Integer	run_history_id
mtln_cdc_load_timestamp	DateTime	CURRENT_TIMESTAMP()

Minimum - change log

Metadata column	Data type	Source
mtln_cdc_src_version	Integer	source_version
mtln_cdc_last_change_type	Text	metadata, op
mtln_cdc_last_commit_timestamp	Integer	metadata, ts_ms
mtln_cdc_processed_date_hour	DateTime	date_hour
mtln_cdc_filename	Text	filename
mtln_cdc_sequence_number	depends on source	see Sequence number calculation

Minimum - copy table

Metadata column	Data type	Source
mtln_cdc_src_version	Integer	source_version
mtln_cdc_last_commit_timestamp	Integer	metadata, ts_ms
mtln_cdc_processed_date_hour	DateTime	date_hour
mtln_cdc_sequence_number	depends on source	see Sequence number calculation

Soft deletes

Metadata column	Data type	Source
mtln_cdc_deleted	Boolean	Indicator

---

Delta Lake metadata

Full metadata coverage

Metadata column	Data type	Source
mtln_cdc_src_database	TEXT	metadata, db
mtln_cdc_src_schema	TEXT	metadata, schema
mtln_cdc_src_table	TEXT	metadata, table
mtln_cdc_src_version	INTEGER	source_version
mtln_cdc_last_change_type	TEXT	metadata, op
mtln_cdc_last_commit_timestamp	INTEGER	metadata, ts_ms
mtln_cdc_date_hour_filename	TEXT	date_hour_filename
mtln_cdc_filepath	TEXT	filepath
mtln_cdc_sequence_number	depends on source	see Sequence number calculation
mtln_cdc_load_batch_id	INTEGER	run_history_id
mtln_cdc_load_timestamp	TIMESTAMP	CURRENT_TIMESTAMP()

Minimum - change log

Metadata column	Data type	Source
mtln_cdc_src_version	INTEGER	source_version
mtln_cdc_last_change_type	TEXT	metadata, op
mtln_cdc_date_hour_filename	TEXT	date_hour_filename
mtln_cdc_sequence_number	depends on source	see Sequence number calculation

Minimum - copy table

Metadata column	Data type	Source
mtln_cdc_src_version	INTEGER	source_version
mtln_cdc_date_hour_filename	TEXT	date_hour_filename
mtln_cdc_sequence_number	depends on source	see Sequence number calculation

Soft deletes

Metadata column	Data type	Source
mtln_cdc_deleted	Boolean	Indicator

---

BigQuery metadata

Full metadata coverage

Metadata column	Data type	Source
mtln_cdc_src_database	STRING	metadata, db
mtln_cdc_src_schema	STRING	metadata, schema
mtln_cdc_src_table	STRING	metadata, table
mtln_cdc_src_version	INT64	source_version
mtln_cdc_last_change_type	STRING	metadata, op
mtln_cdc_last_commit_timestamp	INT64	metadata, ts_ms
mtln_cdc_date_hour_filename	STRING	date_hour_filename
mtln_cdc_filepath	STRING	filepath
mtln_cdc_sequence_number	depends on source	see Sequence number calculation
mtln_cdc_load_batch_id	INT64	run_history_id
mtln_cdc_load_timestamp	TIMESTAMP	CURRENT_TIMESTAMP()

Minimum - change log

Metadata column	Data type	Source
mtln_cdc_src_version	INT64	source_version
mtln_cdc_last_change_type	STRING	metadata, op
mtln_cdc_date_hour_filename	STRING	date_hour_filename
mtln_cdc_sequence_number	depends on source	see Sequence number calculation

Minimum - copy table

Metadata column	Data type	Source
mtln_cdc_src_version	INT64	source_version
mtln_cdc_date_hour_filename	STRING	date_hour_filename
mtln_cdc_sequence_number	depends on source	see Sequence number calculation

Soft deletes

Metadata column	Data type	Source
mtln_cdc_deleted	BOOL	Indicator

---

Sequence number calculation

• If the source is MySQL, the sequence number is constructed from the bin file, row and pos fields.
• If the source is Microsoft SQL Server, coalesce change_lsn with commit_lsn.
• If the source is Oracle, use the scn.
• Else use the commit_id_name from the metadata payload.