Warehouse Sync Logs

Overview

Warehouse Sync Logs writes information on the results of your syncs back into your warehouse. It makes the per-row information from the live debugger available in your warehouse so that you can perform more complex analysis.

When Warehouse Sync Logs is enabled, Hightouch creates a corresponding row for every row processed during the sync. This includes the status of the row for the sync, as well as any errors from processing the row. These tables can then be explored using the full flexibility of SQL. For example, you can:

Categorize all the errors in your sync using regular expressions, and find unexpected errors.
Filter out previously failed rows from your model using a JOIN.
Aggregate the sync history to see what rows are changing the most (flapping rows can be a sign of data problems).
Visualize how rows in your models (e.g. targeted users in ad campaigns) are changing over time.

See the Example Queries section for some concrete examples.

Schema

Hightouch writes the history into three tables within the hightouch_audit schema:

sync_changelog: This table contains a row for every operation performed by Hightouch. It includes the result of the operation, as well as any error messages from syncing.
sync_snapshot: This table contains the latest status of each row in your model. The information is very similar to the sync_changelog table, but is easier to query for some use cases.
sync_runs: This table contains a log of all the sync runs. The changelog and snapshot tables can be JOINed to this table for more information on when the sync occurred, and how it was configured.

Information across all syncs are written into the same table (they're differentiated with the sync_id column).

See the Detailed Schema section for a detailed description of the available columns .

Setup

Warehouse Sync Logs requires enabling Warehouse Planning. It currently supports the following sources:

Snowflake
BigQuery
Redshift
Databricks
Postgres

Required permissions

The user for your source must be able to write into the hightouch_audit schema. You should not require any additional permissions if you have set up Warehouse Planning.

Enabling Warehouse Sync Logs for a sync

Warehouse Sync Logs is off by default. To enable it:

Make sure Warehouse Planning is enabled for the source.

Go to the "Sync Logs" tab in your sync.
Enable your desired history tables.

Example queries

All examples are written for Snowflake.

See our dbt package for even more use cases!

Get the most common sync 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

This SQL tracks when users enter and exit a model. It's particularly useful when used with Hightouch Audiences and visualized in 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

This is particularly useful when used with Hightouch 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_snapshot, sync_changelog, and sync_runs tables after each sync.

hightouch_audit.sync_changelog

This table is an append-only log of all changes across all sync runs. If the same row is synced in multiple sync runs, it will have multiple entries in this table.

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. This is computed by Hightouch when planning the sync run
row_id	The value of the row's primary key (as defined from the model)
status	Whether the row was successfully synced into destination. They value may be: succeeded (the row was successfully synced), failed (Hightouch attempted to sync the row, but it failed to sync), and aborted (Hightouch planned to sync the row, but did not attempt to sync. This may happen if the sync may have been cancelled, or the sync encountered a fatal error that terminated the run early).
failed_reason	If the `status` is `failed`, this will contain a string describing why the row failed to sync.
fields	A JSON object of the raw data from the model that is being synced into destination. Note that this is the raw data from the warehouse, not the payload that Hightouch sent to the destination. This column is not supported on Redshift.

hightouch_audit.sync_snapshot

This table stores the current status of each row in the most recent sync run (even if the row wasn't synced in the most recent run).

After each run, the old statuses for the sync are dropped and replaced with updated statuses.

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 from the model)
status	The status of the row. See the `sync_changelog.status` description for a list of possible statuses
failed_reason	If the `status` is `failed`, this will contain a string describing why the row failed to sync
fields	The fields from the model for this row. See the `sync_changelog.fields` description for more information

hightouch_audit.sync_runs

This table stores general metadata information about each sync run. The sync_changelog and sync_snapshot tables may be joined with this table using the sync_id column.

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 your sync (as defined on the model attached to the sync)
destination	The destination type (e.g. salesforce or braze)
model_name	The name of the model attached to the sync
status	The status of the sync run. This will be either succeeded or failed. In general, the per-row results of the sync are a better indication of status.
error	The sync-level error if the sync terminated early
started_at	When the sync run started
finished_at	When the sync 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 actually attempted.
num_attempted_change	The number of planned changes that were actually attempted.
num_attempted_remove	The number of planned removes that were actually attempted.
num_succeeded_add	The number of planned adds that were successfully synced to the destination
num_succeeded_change	The number of planned changes that were successfully synced to the destination
num_succeeded_remove	The number of planned removes that were successfully synced to the destination
num_failed_add	The number of planned adds that were attempted, but failed to get synced into destination
num_failed_change	The number of planned changes that were attempted, but failed to get synced into destination
num_failed_remove	The number of planned removes that were attempted, but failed to get synced into destination

Frequently asked questions

What's the performance impact?

The performance impact of enabling Warehouse Sync Logs is low. The rows reuse data already present from Warehouse Planning, and the rows are only written after Hightouch syncs to the destination (i.e. in the Reporting phase), so throughput destinations is not affected.

Pruning entries in the history tables is safe. Doing so will not affect the correctness of future sync runs (although the deleted rows will not be rewritten into the history tables).

Need help?

Our team is relentlessly focused on your success. We're ready to jump on a call to help unblock you.

Connection issues with your data warehouse?
Confusing API responses from destination systems?
Unsupported destination objects or modes?
Help with complex SQL queries?

Feature Requests?

If you see something that's missing from our app, let us know and we'll work with you to build it!

We want to hear your suggestions for new sources, destinations, and other features that would help you activate your data.