Warehouse Sync Logs

Warehouse Sync Logs are only available on Business tier plans.

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 enabled on a supported source.

When to use Warehouse Sync Logs

The live debugger 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 and surface unexpected ones.
Filter previously failed rows out of a model 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 for concrete SQL.

Set up Warehouse Sync Logs

Warehouse Sync Logs build on the Lightning sync engine, 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:

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

Go to Integrations > Sources and select your source.
Click the Sync logs tab.
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.
Save.

Enabling sync log tables at the source level

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.

Open the sync and click the Sync logs tab.
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

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:

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 for every column.

For how Hightouch handles PII and retention in these tables, see 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 has more.

Get the most common sync error

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

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

Track when users entered and exited a model

Track when users enter and exit a model — useful with Customer Studio audiences and a BI tool:

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

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 audiences:

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

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 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.
`split_group`	(Optional) The experiment group 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 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, 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.

Get started

Marketing

Data platform

Manage

Resources

API reference

Before you begin

When to use Warehouse Sync Logs

Set up Warehouse Sync Logs

Required permissions

Enable logs for a source

Enable logs for a single sync

Confirm it worked

Schema

Example queries

Get the most common sync error

Track when users entered and exited a model

Get the current rows in all models

Detailed schema

Changelog table

Snapshot table

Sync runs table

FAQ

What’s the performance impact of enabling Warehouse Sync Logs?

What are the limitations in Redshift?

​Before you begin

​When to use Warehouse Sync Logs

​Set up Warehouse Sync Logs

​Required permissions

​Enable logs for a source

​Enable logs for a single sync

​Confirm it worked

​Schema

​Example queries

​Get the most common sync error

​Track when users entered and exited a model

​Get the current rows in all models

​Detailed schema

​Changelog table

​Snapshot table

​Sync runs table

​FAQ

​What’s the performance impact of enabling Warehouse Sync Logs?

​What are the limitations in Redshift?

Before you begin

When to use Warehouse Sync Logs

Set up Warehouse Sync Logs

Required permissions

Enable logs for a source

Enable logs for a single sync

Confirm it worked

Schema

Example queries

Get the most common sync error

Track when users entered and exited a model

Get the current rows in all models

Detailed schema

Changelog table

Snapshot table

Sync runs table

FAQ

What’s the performance impact of enabling Warehouse Sync Logs?

What are the limitations in Redshift?