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 columnslink

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 (UTC TIMESTAMP) indicates the start time of the job that synced this row.
• fivetran_deleted (BOOLEAN) to mark rows that were deleted in the source database.
• fivetran_index (INTEGER) enables the user to make sense of the order of updates for primary keyless tables.
• fivetran_id (TEXT) is a Fivetran internal unique id that helps us avoid duplicate rows in primary keyless tables.

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 (UTC DATETIME) to keep track of the time when a record is first created or modified in the source database.
• fivetran_end (UTC DATETIME) to keep track of the time till when a record is active in the source database.

Note: When you activate history mode for a table, you can opt to remove the fivetran_deleted column for that table.

Audit tableslink

Important: We have deprecated the two audit tables fivetran_audit and fivetran_audit_warning. We will stop populating the tables with the sync logs after March 3, 2021. Use the Fivetran Log Connector to track your sync logs. The Fivetran Log Connector syncs the logs to a table.

For each of your schemas, we publish a table called fivetran_audit that contains an event log of every table’s sync.

fivetran_auditlink

The audit table lets you see the last time each table updated, as well as any current issues you may be experiencing. You then have the option of creating a view of this information. As a result, any visualization is contextualized by a fresh timestamp.

The audit table updates immediately after you load tables or rows into your destination. For example, if you load 100 tables to your destination, your audit table will get updated with 100 rows. If there is no update to a table, we do not yet publish 0 rows updated to your table.

The statuses for the fivetran_audit table are:

1. “Ok” - Data was successfully delivered to your destination.

2. “Rescheduled” - If there are API limitations on a particular integration, such as Marketo, and Fivetran either hits this API limit (or if Fivetran sets a maximum number of calls it will use) then the integration will automatically pause. It will continue syncing data again the following day, once its daily API limit has been restored.

3. “Failed” - The sync encountered an error during the update. Generally, when an overall integration fails, then all of the tables fail. Because the audit_table is broken down by table you will be able to detect failure down to the table level. Even if an integration has failed, we will never load bad data into your destination. We can, however, load partial data (that will be completed on the next update). If our system encounters bad data, it will fail before loading it.

fivetran_audit_warninglink

Fivetran also generates a table called fivetran_audit_warning when there are multiple warnings on a single table. This table will not appear in your schema until an issue of sufficient magnitude has surfaced.

Other system tableslink

Certain integrations have unique Fivetran-created system tables that give specific information about that integration. See each individual integration page for more information.

Example queries for audit_tablelink

The following query will tell you when the last successful update started for each table. The data in that table will be fresh at least up to that point in time.

select "schema", "table", max(update_started)
from {name of your destination schema}.fivetran_audit
where status = 'Ok'
group by 1, 2
order by 1, 2;

Follow these instructions to find out the number of rows that have been updated per day in the last 30 days for a specific table.

Replace my_table_name with your table name and my_schema_name with your schema name in the query below and run it:

WITH volume_data AS (
    SELECT
      date_trunc('DAY', done) AS date,
      rows_updated_or_inserted AS row_volume
    FROM _my_schema_name_._fivetran_audit
    WHERE datediff(DAY, done, current_date) < 30
    AND "TABLE" = '_my_table_name_'
)
SELECT
 date,
 sum(row_volume) AS row_volume
FROM volume_data
GROUP BY date
ORDER BY date;

Note that the query above is in Snowflake SQL dialect.

Important: The rows_updated_or_inserted count returned by the query above is the total number of upserts, updates, and deletes that Fivetran observed before deduplication. In the logs, however, the count of REPLACED_OR_INSERTED rows is the number of records that Fivetran flushes to the destination after deduplication.