System Columns and Tables
We value transparency and want you to have complete access about what Fivetran does with your data, so we created system columns and tables, which is something like metadata about your data.
System columns
Wherever applicable, Fivetran adds extra columns to every table that is added to your destination, to give you insight into the progress and changes of your integration:
_fivetran_synced(INSTANT) shows when Fivetran last synced the row. We update_fivetran_syncedfor individual (soft) deletes. However, we do not update this column for the following:- Bulk operations such as
TRUNCATE - Mass deletes that occur during a re-sync
- Records deleted from tables with history mode enabled
- Bulk operations such as
_fivetran_deleted(BOOLEAN) to mark rows that were deleted or the past versions of the rows changed in the source system._fivetran_index(INT) enables the user to make sense of the order of updates for tables without a primary key._fivetran_id(STRING) is a Fivetran-generated internal unique surrogate primary key. Its value is generated based on the row's set of values and helps us avoid duplicate rows in tables without a primary key.
History mode columns
When you activate history mode for a table, Fivetran adds the following three extra columns:
_fivetran_active(BOOLEAN) to identify an active record._fivetran_start(INSTANT) to record the time that record was first created or modified in the source, based on a timestamp value in the source table that monotonically increases over time with data change or update._fivetran_end(INSTANT) to record the time when a record became inactive in the source system.
When you activate history mode for a table, we drop its _fivetran_deleted column.
Source operation metadata columns Private Preview
You can add source operation metadata columns to your destination tables to capture information about operations such as inserts, updates, and deletes performed in the source database:
_fivetran_src_operation_type(STRING) to indicate the source operation type:Ifor an insert,Ufor an update, orDfor a delete. In soft delete mode, we populate this column with the type of the most recent operation (insert, update, or delete) performed in the source. In history mode, we populate this column withIfor an insert orUfor an update performed in the source. The value remains unchanged when the row is deleted in the source; it doesn't change toD._fivetran_src_operation_timestamp(INSTANT) to indicate the commit timestamp of the source operation. In soft delete mode, we populate this column with the commit timestamp of the most recent operation (insert, update, or delete) performed in the source. In history mode, we populate this column with the commit timestamp of the insert or update performed in the source. The value remains unchanged when the row is deleted in the source; it doesn't change to the delete's commit timestamp._fivetran_src_operation_user(STRING) to identify the source database user who executed the operation. For changes captured through CDC, if the username is unavailable, we populate this column withNULLfor inserts and updates, andUNKNOWNfor deletes. In soft delete mode, we populate this column with the username of the user who performed the most recent operation (insert, update, or delete). In history mode, we populate this column with the username of the user who performed the insert, update, or delete in the source.
We populate the source operation metadata columns with NULL values for rows imported during historical syncs and re-syncs, and for existing rows when you enable these columns for an existing connection. When we capture subsequent source changes from redo logs during incremental syncs, we populate these columns with the corresponding source operation metadata based on the table’s sync mode.
We don't add these columns by default. You can enable each column independently in the connection setup form.
The source operation metadata columns are supported only for the Oracle connector. For more information, see the Source operation metadata section in our Oracle connector documentation.
Other system tables
Certain integrations have unique Fivetran-created system tables that give specific information about that integration. See each individual integration page for more information.
MAR impact
Fivetran's system tables count towards free Monthly Active Rows (MAR):
FIVETRAN_API_CALLFIVETRAN_DEPENDENT_PICKLIST_RELATIONFIVETRAN_FORMULAFIVETRAN_FORMULA_FAILURE_REASONFIVETRAN_FORMULA_MODELFIVETRAN_PICKLIST_FIELDFIVETRAN_PICKLIST_FIELD_VALUEFIVETRAN_QUERYFIVETRAN_ROLLUP_SUMMARYFIVETRAN_ROLLUP_SUMMARY_FILTER
The MAR that the Fivetran Platform connections generate is free.