# 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]({{< ref "sources/reverse-etl/" >}}) documentation.

| Warehouse | Schema name |
| :--- | :--- |
| Snowflake | `_RUDDERSTACK` |
| BigQuery | `rudderstack_` |
| Other warehouses | `_rudderstack` |

{{< warning >}}
Do not rename this schema.
{{< /warning >}}

What gets written depends on the [sync mode]({{< ref "data-pipelines/reverse-etl/developer-guides/sync-modes.md" >}}) and whether you enable a [cursor column]({{< ref "data-pipelines/reverse-etl/developer-guides/cursor-column-support.md" >}}). Configure how long tables and logs are retained in [Sync Observability Settings]({{< ref "data-pipelines/reverse-etl/developer-guides/sync-observability.md" >}}).

## Table families

| Table naming pattern | Created when | Contents |
| :--- | :--- | :--- |
| `SNAPSHOT_<connectionId>_<syncRunId>` | Mirror, or Upsert without a cursor column | Full set of in-scope rows for that sync in source schema, plus `RUDDER_OPERATION_TYPE` (`insert`, `update`, `delete`, or `unchanged`) |
| `FAILED_KEYS_<connectionId>_<syncRunId>` | Mirror, Upsert | Primary-key columns of failed records (for example `CONTEXT_TRAITS_EMAIL` and `_RUDDER_ID`), plus `RUDDER_OPERATION` and `RUDDER_INVALID` |
| `AGGR_FAIL_KEYS_<connectionId>_<syncRunId>` | Mirror, Upsert | `_RUDDER_ID` and numeric `CODE` (HTTP status). Join to `FAILED_KEYS_*` on `_RUDDER_ID` |
| `CURSOR_<connectionId>_<syncRunId>` | Upsert with a cursor column | One row: `CHECKPOINT` = high-watermark of the cursor column |
| `SYNC_DATA_<connectionId>_<syncRunId>` | Upsert with a cursor column | Delta rows since the last checkpoint (first sync = full load). Includes `RUDDER_OPERATION_TYPE`, `RUDDER_SNAPSHOT_ID`, and `RUDDER_SNAPSHOT_RUN_AT` |
| `SYNC_LOG` | Any mode, when sync logs are enabled | Per-record, per-sync log across connections (see [Sync log table](#sync-log-table)) |

{{< info >}}
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>`.
{{< /info >}}

## Table name format

Example: `SNAPSHOT_2WRS3TRTNKIGHHLDKHXIJTWLU7C_D0F3FOFMJ6M0GOGO5HHG`

| Segment | Meaning | Where to find it |
| :--- | :--- | :--- |
| `SNAPSHOT` | Table type | — |
| `2WRS3TRTNKIGHHLDKHXIJTWLU7C` | Connection ID | **Settings** tab of the Reverse ETL [connection]({{< ref "data-pipelines/reverse-etl/developer-guides/retl-connection-setup.md#update-mapping-configuration" >}}) |
| `D0F3FOFMJ6M0GOGO5HHG` | Sync run ID | Specific sync in the **Syncs** view |

{{< image src="images/retl-sources/sync-run-id.webp" alt="Reverse ETL connection sync run ID" >}}

## Behavior by sync mode

| Mode | Warehouse tables | Point-in-time history |
| :--- | :--- | :--- |
| [Mirror]({{< ref "data-pipelines/reverse-etl/developer-guides/sync-modes.md#mirror-mode" >}}) | `SNAPSHOT_*`, `FAILED_KEYS_*`, `AGGR_FAIL_KEYS_*` | Full in-scope population per retained sync |
| [Upsert]({{< ref "data-pipelines/reverse-etl/developer-guides/sync-modes.md#upsert-mode" >}}) (no cursor column) | Same as Mirror | Full in-scope population per retained sync |
| Upsert with [cursor column]({{< ref "data-pipelines/reverse-etl/developer-guides/cursor-column-support.md" >}}) | `CURSOR_*`, `SYNC_DATA_*` (no `SNAPSHOT_*`) | Delta rows only — not a full population snapshot |

{{< warning >}}
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](#retention) as needed.
{{< /warning >}}

## Retention

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

| Setting | Unit | Default (typical) | Effect |
| :--- | :--- | :--- | :--- |
| Snapshot table retention | Number of syncs | 5 | Keeps the N most recent successful snapshots per connection; older `SNAPSHOT_*` / `FAILED_KEYS_*` tables are dropped |
| Sync log retention | Days | 30 | Deletes `SYNC_LOG` rows older than N days |

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

| Table | What you get | What you don't get |
| :--- | :--- | :--- |
| `FAILED_KEYS_*` | Keys of failed records | Error code or message |
| `AGGR_FAIL_KEYS_*` | Numeric HTTP status (for example `400`) per `_RUDDER_ID` | Human-readable destination error |
| `SYNC_LOG.error_reason` | Same class of status detail when logs are enabled | Full per-record destination message |

Detailed destination errors (for example, "invalid email") appear as sampled examples in the RudderStack dashboard — one sample per error type per sync — not as full per-record messages in the warehouse.

## Sync log table

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

| Column | Description |
| :--- | :--- |
| `connection_id` | Connection ID for the sync run |
| `sync_run_id` | Unique identifier for the sync run |
| `primary_key` | Value of the primary key column selected for the sync |
| `operation` | Operation on the row: `insert`, `update`, or `delete` |
| `status` | `succeeded` or `failed` |
| `error_reason` | Failure detail when `status` is `failed` (typically a numeric HTTP status code, not a full destination message) |
| `sync_started_at` | Sync start time in UTC |
| `sync_finished_at` | Sync finish time in UTC |

{{< warning >}}
Storing sync logs in your warehouse incurs additional costs.
{{< /warning >}}

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

| Column | Description |
| :--- | :--- |
| `rudder_operation_type` | `insert`, `update`, `delete`, or `unchanged` relative to the last synced data |
| Mapped source columns | Columns selected in the sync mappings |

{{< warning >}}
Storing snapshot tables in your warehouse incurs additional costs.
{{< /warning >}}

## 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

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

### Join failed sync log rows to a snapshot

```sql
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.

```sql
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>
  );
```

## 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](#retention) setting.