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 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.
|
_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
 |
_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 |