RudderStack Schema Reference

On every Reverse ETL sync, RudderStack writes tables into a dedicated schema in your warehouse so you can audit what was extracted, what changed, and what failed. Create this schema before you set up a Reverse ETL source — see the permissions section of your warehouse source documentation.

Do not rename this schema.

What gets written depends on the sync mode and whether you enable a cursor column. Configure how long tables and logs are retained in Sync Observability Settings.

Table families

Older workspaces may also show legacy names such as SNAPSHOT_<hash>_<epoch>, SYNC_DATA_<hash>, or FAILED_RECORDS_<hash>. The current convention is <TABLE_TYPE>_<connectionId>_<syncRunId>.

Table name format

Example: SNAPSHOT_2WRS3TRTNKIGHHLDKHXIJTWLU7C_D0F3FOFMJ6M0GOGO5HHG

Behavior by sync mode

Cursor/incremental mode and snapshot tables are mutually exclusive. If you need to reconstruct the full population at a past sync, use Mirror or Upsert without a cursor column, and raise snapshot retention as needed.

Retention

Two independent settings on each connection (Settings > sync observability):

Point-in-time lookback is bounded by these settings. Raising retention extends your audit window and increases warehouse storage and table count in the schema.

Source data vs destination payload

SNAPSHOT_* and SYNC_DATA_* capture rows as extracted from the source warehouse — before transformation or mapping applied on the way to the destination. They show what RudderStack decided to send, in the source schema. They do not show the exact post-transformation payload delivered to the destination.

Failure tables

By default, detailed destination errors (for example, “invalid email”) appear as sampled examples in the RudderStack dashboard — one sample per error type per sync. Turn on Retain and show failures to store the full error message for every failed record in REJ_* and view it in the dashboard.

Sync log table

When you enable sync logs, RudderStack writes a single SYNC_LOG table. Column names may appear uppercased depending on your warehouse.

Storing sync logs in your warehouse incurs additional costs.

Snapshot table

For Mirror and Upsert (no cursor), each sync writes a SNAPSHOT_<connection_id>_<sync_run_id> table. It contains in-scope source rows (excluding invalid rows such as duplicate or null primary keys) and the change type for each row.

Storing snapshot tables in your warehouse incurs additional costs.

Failed records table

When Retain and show failures is enabled, RudderStack writes a REJ_<connectionId>_<syncRunId> table for each sync that runs after you enabled it. It contains one row per failed record. Column names may appear uppercased depending on your warehouse.

RudderStack drops each REJ_* table once its sync is older than the failure retention window.

Example queries

Replace placeholders with your connection ID, sync run IDs, primary key column, and column name. Schema name may be _rudderstack, _RUDDERSTACK, or rudderstack_ depending on your warehouse.

Trace a primary key across syncs

SELECT *
FROM _rudderstack.sync_log
WHERE primary_key = '<primary_key>'
  AND connection_id = '<connection_id>';

Join failed sync log rows to a snapshot

SELECT *
FROM _rudderstack.sync_log sl
LEFT JOIN _rudderstack.snapshot_<connection_id>_<sync_run_id> sn
  ON sl.primary_key = sn.<primary_key>
WHERE sl.sync_run_id = '<sync_run_id>'
  AND sl.status = 'failed';

Find rows whose value changed between two snapshots

Use this when you need contacts (or other records) that had a given value in an earlier sync but not in a later one — for example, to remediate downstream data after a bad source query.

SELECT earlier.<primary_key>, earlier.<column_name>
FROM _rudderstack.snapshot_<connection_id>_<earlier_sync_run_id> earlier
WHERE earlier.<column_name> = '<value>'
  AND NOT EXISTS (
    SELECT 1
    FROM _rudderstack.snapshot_<connection_id>_<later_sync_run_id> later
    WHERE later.<primary_key> = earlier.<primary_key>
      AND later.<column_name> = earlier.<column_name>
  );

List the failed records of a sync with their errors

SELECT primary_key, operation, error_code, error_message, failed_at
FROM _rudderstack.rej_<connection_id>_<sync_run_id>
ORDER BY error_code, primary_key;

Join failed records to their source rows

SELECT r.primary_key, r.error_code, r.error_message, s.*
FROM _rudderstack.rej_<connection_id>_<sync_run_id> r
JOIN _rudderstack.sync_data_<connection_id>_<sync_run_id> s
  ON s._rudder_id = r._rudder_id;

Empty failure tables

RudderStack creates FAILED_KEYS_* and AGGR_FAIL_KEYS_* for each applicable sync even when no records fail. Empty tables are expected. Retention cleanup removes older ones according to your snapshot table retention setting.