Step 1 - Query

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

Once you've entered your query, be sure to click the Preview Query button, which will execute your query against your warehouse and return the first row fetched from your targeted table. For queries against large datasets, the preview may take a minute or more to complete — this is expected behavior.

You'll use the previewed query and returned row further along in the configuration below.

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 NPS Responses

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 the $LAST_SYNC_TIMESTAMP with a timestamp that reflects when the data was synced to the data warehouse. This is particularly important when data is synced to the data warehouse in batches.

SELECT id, "userId", score, feedback, "respondedAt"
FROM "Nps_Session"
WHERE "respondedAt" > $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.

Step 2 - Configure

Once you input and preview your query, you'll be presented with a few additional configuration options to map the columns from your SELECT statement to the required Vitally fields.