Skip to content

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.
© Copyright 2024 Matillion Ltd | Legal | Privacy Policy | Disclaimer | Modern Slavery Statement