Step 1 - Set the Query

After connecting to your warehouse, you can start syncing Events by configuring the query that Vitally will run against your warehouse on each incremental sync.

To sync Events, the query must select or alias all three of the following column names:

event: the name or type of event
timestamp: the time the event was generated in ISO 8601 format (1970-01-01T00:00:00.000Z)
message_id: a unique ID for the event

And at least one of:

user_id: the ID of the user that 'owns' the event
account_id: the ID of the account that 'owns' the event
organization_id: the ID of the organization that 'owns' the event (only available if you have organization hierarchy enabled)

Incremental Sync

Incremental sync is a process of syncing warehouse data that has only been added or modified since the last sync cycle instead of processing the entire dataset each time. This approach is more efficient as it minimizes the amount of data queried and processed during each sync, making the update process quicker and resource-efficient.

Incremental sync is required for syncing Events

In order to incrementally query your data each sync, you'll need to add a clause to your query with the variable $LAST_SYNC_TIMESTAMP. We'll fill in that variable with the timestamp of the last sync each time we query the warehouse.

When previewing a query that uses $LAST_SYNC_TIMESTAMP, the preview substitutes epoch time (1970-01-01) rather than your actual last sync timestamp. This means the preview may return more rows than a live sync would. During actual syncs, the variable is replaced with the true last sync time.

Incremental syncs should compare $LAST_SYNC_TIMESTAMP with a timestamp that reflects when the data was loaded into the data warehouse, not when the event occurred. This is particularly important when data is synced to the data warehouse in batches — events that happened before the last sync but were loaded after it can be missed if you use an event-occurrence timestamp.

SELECT
  'pageView' AS event,
  user_id AS "user_id",
  created_at AS timestamp,
  id AS message_id,
  page,
  application
FROM page_views
WHERE created_at > $LAST_SYNC_TIMESTAMP

Sync Check

The Sync check appears under the query box and tells you whether your $LAST_SYNC_TIMESTAMP clause limits which rows each sync pulls.

The check shows one of three states. Green means each sync pulls only the rows that are new or changed since the last sync. Yellow means every sync pulls the whole table. Blue means every sync pulls the whole table and that may be intended, for example when the table is rebuilt on each load.

Some filters include the variable but are always true. For example, NOW() > $LAST_SYNC_TIMESTAMP and CURRENT_TIMESTAMP() > $LAST_SYNC_TIMESTAMP only check whether the current time is later than the last sync, so every sync pulls the whole table. The Sync check flags these conditions as you type and explains how to fix them. It’s a warning only, so you can still save the query.

To filter correctly, compare $LAST_SYNC_TIMESTAMP against a column in your table, such as updated_at > $LAST_SYNC_TIMESTAMP.

Click How many rows will sync? to see how many rows the first sync would pull and how many a sync a day later would pull. If the two counts match, your filter may not be limiting the sync. Row counts are an estimate. A small table where every row changes daily, or a table that’s rebuilt each day, can return matching counts even when the filter works. Row counts are available for Snowflake, BigQuery, PostgreSQL, MySQL, and Redshift.

Click Check your query with Quin to have Quin explain what your filter does. Quin can also suggest a fixed query. Click Use this query to use it.

Adding Another Event Source

Events can be configured to sync from multiple queries, such as when events are in different tables. In order to sync an additional event query, select the "Add another event source" button at the bottom of the query list:

It is recommended that each event type (i.e., Login, File Downloaded, Page Clicked) has its own event source.

Step 2 - Configure Properties

In order to pull in additional metadata associated with the event, simply include those columns in your SELECT statement, and they will be added as event properties.

For the example query above, the following event properties will be synced:

{ "page": "...", "application": "..." }

What if my properties are all in one column?

If your event properties are contained in a single nested or JSON column, and you've selected it:

SELECT
  event_name AS event,
  user_id AS "user_id",
  created_at AS timestamp,
  id AS message_id,
  meta_data
FROM all_events
WHERE created_at > $LAST_SYNC_TIMESTAMP

And the responses looks like:

{
  "event": "pageView",
  "user_id": "2dc30ead-2e64-4d87-8395-aad4be299698",
  "timestamp": "2019-11-15T16:08:59.915Z",
  "message_id": "7ca60dbd-0ad0-4ef6-b049-aac3fb5e0116",
  "meta_data": {
    "page": "home-page",
    "duration_ms": 3253
  },
}

Then you will want to set the optional "property field" to meta_data in order to track those values.

Step 3 - Continue Configuration

If you have other features to sync continue configuring those.

Configuring Sync

OR Step 3 - Enable Sync

Enabling Sync

FAQ

Q: Why are some events missing in Vitally but correctly appear in my data warehouse?

A: This usually happens because the column used in the WHERE clause reflects when the event occurred rather than when it was loaded into the warehouse. If your warehouse loads data in batches, events that happened before the last sync but were loaded after it can be missed. To avoid this, use a warehouse load timestamp (sometimes called inserted_at, loaded_at, or similar) in your WHERE clause instead of the event-occurrence timestamp.

Q: Why can't I see events for churned accounts?

A: Once an account is marked as churned, Vitally automatically stops tracking product events for that account. If you're sending events for a churned account, they won't appear in Vitally.

Syncing Events