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.

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.

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:

• Amazon Redshift
• Databricks
• Google BigQuery
• Greenplum Database
• PostgreSQL
• Snowflake

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.

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.

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.

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.

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

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.

Sync runs table

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

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.