SageData System table and column reference

 

When using SageData integrations to load data into your destination, additional columns and tables will be created. They will provide the information needed for better transparency about your data when moving through SageData.

 

System columns

There are two types of columns that will be created, when data is loaded into your destination table: the columns you want to import and replicate, and the SageData system columns. The system columns consist of metadata about the records, such as extraction date from the source. SageData uses this information to correctly replicate and load your data.

The system columns can be recognized by the prefix _sdc. Depending on the integration type, the system columns added fall into different categories.

  • Integration tables
  • Subtables
  • Database integrations
  • SaaS integrations

 Note: Do not remove or block the system columns, as this will cause issues with replication.

 

All integration tables

Every integration table created by SageData will apply those columns to all tables and integration types unless stated otherwise.

Note: Not applicable to system tables.

 

Column name Data type Description
_sdc_extracted_at TIMESTAMP Applicable only to certain (Singer-?)backed integrations.  Timestamp of record extracted from the source

Example data: 2022-03-25 11:32:45.138+00

 

_sdc_received_at TIMESTAMP Timestamp of record received for loading by SageData

Example data: 2022-03-25 11:34:23.249+00

 

_sdc_batched_at TIMESTAMP Timestamp of SageData loading the batch with the record into the destination

Example data: 2022-03-25 11:35:49.524+00

 

_sdc_sequence INT A Unix epoch (in nanoseconds) showing the order in which data was received for loading

 

Example data: 1648208175000000064

_sdc_table_version INT Indicator for the version of the table. Used for determination of when to trigger TRUNCATE commands on loading

Example data: 0

 

__sdc_primary_key STRING If no primary key is detected by SageData in the source table, it will be added automatically.

 

Example data: 4h3ja82-27jj-3h392z3bs03

 

Subtables

The columns below are applicable to integration subtables. They are needed when SageData tries to load nested data into a destination that does not support nested data structures.

 

Column name Data type Description
_sdc_source_key_[name]

 

VARIES

 

Part of a composite Primary Key for the table

Contains the primary key of the top-level table, as well as inherits the data type

Example: Top-level table (inventory) with a primary key inventory_id type integer. In the subtable created from the top-level table, the column would be named _sdc_source_key_inventory_id, type int, and contain the same values as the primary key.

Example data: 827348

 

 

_sdc_level_#_id

 

INT Part of a composite Primary Key for the table.

Can be used as an identification of further nested records.

Uses auto-increment for the unique records, starting from 0.

Based on the level of nesting there might be more than one column _sdc_level_id  in the subtable

 

Example data: 0

 

 

Database integrations

This is a list of columns available only to database integrations.

 

Column name Data type Description
_sdc_replication_id

 

string

 

The primary key is added automatically by SageData if no other is detected.

Example data: ?????

 

 

_sdc_source_file

 

string Contains the file path to the table source file on the SFTP server

Example data: inventory/orders.csv

! Note: Applicable only to Intacct and Responsys integrations

 

_sdc_source_lineno

 

int Gives information on the row number of the record in the source file.

Example data: 3

! Note: Applicable only to Intacct and Responsys integrations

_sdc_deleted_at timestamp If SageData detects a delete event for a record in the database’s binary log, a timestamp will be set. Otherwise, it will be null.

This column will be filled in only after the initial replication has been completed.

Example data: 2022-03-25 09:58:43.173+00

!Note: Applicable only to integrations supporting Log-based Replication

_sdc_extra string Array containing of key-value pair of duplicated columns and values, not associated with any column:

Example data: [{“age”:”30″},{“no_headers”:[“12.18.1991”]}]

 

!Note: Applicable only to Amazon S3 CSV integrations

 

SaaS integrations

This is a list of columns available only to SaaS integrations.

 

Column name Data type Description
_sdc_customer_id

 

string

 

Contains the Ads Account ID

Example data: 2374394894

!Note: Applicable only to Google Ads integrations

 

_sdc_record_hash

 

string Generated by SageData – SHA 256 hash that is used as a primary key.

For Google Ads, the hash is encoded JSON (UTF-8) containing the segments and attributes in the report.

For Google Analytics, the hash is encoded JSON (UTF-8) containing

·       account ID, web property ID, and profile ID of the report

·       pairs of ga:dimension_name and dimension_value

·       start_date and end_date for the record (YYYY-mm-dd)

For NetSuite Suite Analytics, the hash uses the values of the  Primary Keys created by NetSuite for the table

 Example data: ????

 

_sdc_report_datetime

 

DATE-TIME

 

Starting time of data extraction by Sage Data

Example data: 2022-03-25 12:23:56.193+00

!Note: Applicable only to Google Ads and Campaign Manager integrations

 

System tables

Following system tables will be created additionally:

 

Column name Description
_sdc_primary_keys Will be added only to Google BigQuery (v2) and Microsoft Azure Synapse Analytics destinations. The table consists of a list of all tables in an integration schema and which columns are used as primary keys by those tables
_sdc_rejected

 

History log for rejected integration records