> ## Documentation Index
> Fetch the complete documentation index at: https://docs.hightouch.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Warehouse Sync Logs

> Warehouse Sync Logs write a record of every row a sync processes back into your data warehouse, so you can analyze sync history with SQL instead of inspecting runs one at a time.

> [!NOTE]
>
> Warehouse Sync Logs are only available on [Business tier plans](https://hightouch.com/pricing).

When you enable Warehouse Sync Logs, Hightouch writes a row into your warehouse for every row a sync processes, including its status and any error from processing it. You then query those logs with SQL or the BI tools already on top of your warehouse.

## Before you begin

* The [Lightning sync engine](https://docs.hightouch.com/syncs/lightning-sync-engine.md) enabled on a supported source.

## When to use Warehouse Sync Logs

The [live debugger](https://docs.hightouch.com/syncs/debugger.md) is the right tool for inspecting one run's rows, and it keeps row-level data for seven days. Reach for Warehouse Sync Logs when you need durable history you can query across many runs — for example, to:

* **Categorize** errors across syncs with [regular expressions](https://docs.snowflake.com/en/sql-reference/functions-regexp.html) and surface unexpected ones.
* **Filter** previously failed rows out of a [model](https://docs.hightouch.com/getting-started/concepts.md#models) with a `JOIN`.
* **Aggregate** history to find rows that change the most — flapping rows can signal data-integrity issues.
* **Track** how a model's or audience's membership changed over time, such as how an ad campaign's targeted users shifted over its duration.

See [Example queries](#example-queries) for concrete SQL.

## Set up Warehouse Sync Logs

Warehouse Sync Logs build on the [Lightning sync engine](https://docs.hightouch.com/syncs/lightning-sync-engine.md), so enable that on your source first. Logs are off for every sync until you turn them on, either for a whole source or for a single sync.

Hightouch supports Warehouse Sync Logs on these sources:

* [Amazon Redshift](https://docs.hightouch.com/sources/amazon-redshift.md)
* [Databricks](https://docs.hightouch.com/sources/databricks.md)
* [Google BigQuery](https://docs.hightouch.com/sources/google-bigquery.md)
* [Greenplum Database](https://docs.hightouch.com/sources/greenplum.md)
* [PostgreSQL](https://docs.hightouch.com/sources/postgresql.md)
* [Snowflake](https://docs.hightouch.com/sources/snowflake.md)

### Required permissions

The user that connects your source to Hightouch must be able to write to the `hightouch_audit` schema. Setting up the [Lightning sync engine](https://docs.hightouch.com/syncs/lightning-sync-engine.md#warehouse-schemas) grants this, so you shouldn't need any additional permissions.

### Enable logs for a source

Enable logs on a source to capture every sync that uses it. This keeps logs available whenever you need to debug, which saves time when a sync fails unexpectedly.

1. Go to **Integrations > Sources** and select your source.
2. Click the **Sync logs** tab.
3. Select the tables to write: **Sync snapshots**, **Changelog**, **Sync runs**, and optionally **Audience snapshots** and **Audience holdout group logs**. The first three each have an **Audience syncs only** sub-option that limits logging to audience syncs.
4. Save.

![Enabling sync log tables at the source level](https://mintcdn.com/hightouch-3c037897/L_vQfA0JQEpeGUUo/images/sources/warehouse-sync-logs-source.png?fit=max\&auto=format\&n=L_vQfA0JQEpeGUUo\&q=85\&s=6747fcb511b07459b47d9dfa66c137aa)

### Enable logs for a single sync

Enable logs on one sync when you only need history for a specific workflow and want to limit storage.

1. Open the sync and click the **Sync logs** tab.
2. Select the tables to write: **Snapshot**, **Changelog**, and **Sync runs**.

![The Sync logs tab on a sync, with the Snapshot, Changelog, and Sync runs tables](https://mintcdn.com/hightouch-3c037897/L_vQfA0JQEpeGUUo/images/syncs/sync-logs-tab.png?fit=max\&auto=format\&n=L_vQfA0JQEpeGUUo\&q=85\&s=2b797c2965b107db2d047b70efbe108b)

> [!TIP]
>
> Sync logs can generate a lot of data depending on how many tables you enable
> and how often syncs run. Enable logs per source when you want history always
> available for debugging; enable them per sync when you only need a few
> workflows and want to keep storage down.

## Confirm it worked

After the sync's next run, query the sync runs table to confirm Hightouch is writing logs:

```sql theme={null}
select sync_id, model_name, status, started_at
from hightouch_audit.sync_runs
order by started_at desc
limit 5;
```

Recent runs in the result mean logging is working. If the table is empty, confirm the run finished after you enabled logging and that the Lightning sync engine is enabled on the source.

## Schema

Hightouch writes sync logs into three tables in the `hightouch_audit` schema:

* **Changelog** — one row for every operation Hightouch performs, with the result and any error message.
* **Snapshot** — each row's latest status in your model. Similar to the changelog, but easier to query when you only need current state.
* **Sync runs** — a log of every sync run. `JOIN` it to the changelog and snapshot tables for details on when a run occurred and how it was configured.

All syncs write to these same three tables; use the `sync_id` column to tell which rows belong to which sync. See [Detailed schema](#detailed-schema) for every column.

> [!NOTE]
>
> For how Hightouch handles PII and retention in these tables, see [table
> removal](https://docs.hightouch.com/syncs/lightning-sync-engine.md#table-removal) on the Lightning sync
> engine page.

## Example queries

These examples are written for Snowflake; adapt them for other sources. Hightouch's [dbt package](https://github.com/hightouchio/dbt-hightouch) has more.

### Get the most common sync error

Group and count rows by `failure_reason` to find the most common error:

```sql theme={null}
select
  failure_reason,
  count(*) as c
from hightouch_audit.sync_snapshot
where failure_reason is not null
group by failure_reason
order by c desc
```

![Result of the most common errors query](https://mintcdn.com/hightouch-3c037897/L_vQfA0JQEpeGUUo/images/syncs/history-example-common-errors.png?fit=max\&auto=format\&n=L_vQfA0JQEpeGUUo\&q=85\&s=64f1e2a58aeed88240be33523279b115)

### Track when users entered and exited a model

Track when users enter and exit a model — useful with [Customer Studio audiences](https://docs.hightouch.com/customer-studio/overview.md) and a BI tool:

```sql theme={null}
with details as (
  select
    model_name,
    row_id,
    op_type as type,
    started_at as timestamp,
    lag(op_type) over(partition by model_name, row_id order by started_at) as lag_type
  from hightouch_audit.sync_changelog c
  join hightouch_audit.sync_runs r on c.sync_id = r.sync_id and c.sync_run_id = r.sync_run_id
  where op_type != 'changed'
  order by model_name, row_id
)

select
  row_id as user_id,
  model_name as audience,
  type,
  timestamp
from details
where (lag_type != type or lag_type is null)
order by model_name, row_id, timestamp
```

![Result of the audience changes query](https://mintcdn.com/hightouch-3c037897/L_vQfA0JQEpeGUUo/images/syncs/history-example-audience-changes.png?fit=max\&auto=format\&n=L_vQfA0JQEpeGUUo\&q=85\&s=128d43ccab3d0dcb5a3d4dfa48b8be62)

### Get the current rows in all models

Find the most recently synced rows that didn't fail, across all models — useful for listing current members of [Customer Studio](https://hightouch.com/platform/customer-studio) audiences:

```sql theme={null}
with model_names as (
  select distinct
    sync_id,
    model_name
  from hightouch_audit.sync_runs
)

select
  model_name,
  row_id as user_id
from hightouch_audit.sync_snapshot s
join model_names r on s.sync_id = r.sync_id
where s.status != 'failed'
qualify row_number() over (partition by user_id, model_name order by null) = 1
order by user_id
```

![Result of the current audiences query](https://mintcdn.com/hightouch-3c037897/L_vQfA0JQEpeGUUo/images/syncs/history-example-current-audiences.png?fit=max\&auto=format\&n=L_vQfA0JQEpeGUUo\&q=85\&s=b50179753417ab8674f771111f35ba3a)

## Detailed schema

Hightouch writes to the `sync_changelog`, `sync_snapshot`, and `sync_runs` tables after each sync. If you enabled **audience snapshots**, you'll also find a `hightouch_planner.audience_membership` table — see the [audience snapshot docs](https://docs.hightouch.com/customer-studio/audience-snapshots.md) for its schema.

### Changelog table

The `hightouch_audit.sync_changelog` table is a log of all changes across all sync runs. A row synced in multiple runs has multiple entries.

| COLUMN | DESCRIPTION |
| - | - |
| `sync_id` | The ID of the sync. |
| `sync_run_id` | The ID of the sync run. |
| `op_type` | Whether the row was added, changed, or removed relative to the last run. Hightouch computes this when planning the run. |
| `row_id` | The value of the row's primary key, as defined on the model. |
| `status` | Whether the row synced to the destination: `succeeded` (the row synced), `failed` (Hightouch tried but the row failed), or `aborted` (Hightouch planned to sync the row but didn't try, such as when a run is canceled or hits a fatal error that ends it early). |
| `failure_reason` | If the status is `failed`, a string describing why the row failed. |
| `fields` | A JSON object of the raw model data synced to the destination. This is the raw warehouse data, not the payload Hightouch sent. This column has limitations in Redshift — see [the FAQ](#what-are-the-limitations-in-redshift). |
| `split_group` | (Optional) The [experiment group](https://docs.hightouch.com/customer-studio/experiments.md) name. Not created if no syncs use experiments. |

### Snapshot table

The `hightouch_audit.sync_snapshot` table stores each row's current status as of the most recent run, even if the row wasn't synced in that run. After each run, the old statuses are dropped and replaced.

| COLUMN | DESCRIPTION |
| - | - |
| `sync_id` | The ID of the sync. |
| `op_type` | Whether the row was added, changed, or unchanged relative to the last run. |
| `row_id` | The value of the row's primary key, as defined on the model. |
| `status` | The status of the row. See the `sync_changelog.status` description for possible values. |
| `failure_reason` | If the status is `failed`, a string describing why the row failed. |
| `fields` | The model fields for this row. See the `sync_changelog.fields` description. |
| `split_group` | (Optional) The [experiment group](https://docs.hightouch.com/customer-studio/experiments.md) name. Not created if no syncs use experiments. |

### Sync runs table

The `hightouch_audit.sync_runs` table stores metadata about each run. `JOIN` it to the `sync_changelog` table on `sync_run_id`, and to the `sync_snapshot` table on `sync_id`.

| COLUMN | DESCRIPTION |
| - | - |
| `sync_id` | The ID of the sync. |
| `sync_run_id` | The ID of the sync run. |
| `primary_key` | The primary key column of the sync, as defined on the model. |
| `destination` | The destination type, such as Salesforce or Braze. |
| `model_name` | The name of the model attached to the sync. |
| `model_id` | The ID of the model attached to the sync. |
| `status` | The status of the run, either `succeeded` or `failed`. The per-row results are usually a better signal. |
| `error` | The sync-level error if the run ended early. |
| `started_at` | When the run started. |
| `finished_at` | When the run finished. |
| `num_planned_add` | The number of planned adds. |
| `num_planned_change` | The number of planned changes. |
| `num_planned_remove` | The number of planned removes. |
| `num_attempted_add` | The number of planned adds that were attempted. |
| `num_attempted_change` | The number of planned changes that were attempted. |
| `num_attempted_remove` | The number of planned removes that were attempted. |
| `num_succeeded_add` | The number of planned adds that synced successfully. |
| `num_succeeded_change` | The number of planned changes that synced successfully. |
| `num_succeeded_remove` | The number of planned removes that synced successfully. |
| `num_failed_add` | The number of attempted adds that failed to sync. |
| `num_failed_change` | The number of attempted changes that failed to sync. |
| `num_failed_remove` | The number of attempted removes that failed to sync. |

## FAQ

### What's the performance impact of enabling Warehouse Sync Logs?

Low. Logs reuse data the Lightning sync engine already produces, and Hightouch writes the rows only after syncing to the destination, so there's no effect on destination throughput. Pruning entries from the log tables is safe and doesn't affect future syncs, though pruned rows aren't rewritten.

### What are the limitations in Redshift?

Because [Redshift doesn't support strings longer than 65,535 bytes](https://docs.aws.amazon.com/redshift/latest/dg/r_Character_types.html#r_Character_types-storage-and-ranges), Hightouch can't store JSON of arbitrary length in the logs. It attempts to store model data in the `fields` column of `sync_changelog` and `sync_snapshot`, but if the model has long strings or many fields, some values may be truncated. Truncated values in those columns don't mean the data was truncated when Hightouch synced to the destination — it's purely a limitation of the warehouse logs.