Skip to main content
Version: 9.5

Use Custom Queries in a Data Sync

Qrvey supports the use of custom queries in a data sync for retrieving recent records.

Overview​

When a dataset syncs using Append and Update, Qrvey normally takes your original query and wraps it with additional logic to retrieve only the most recently changed records. This works for simple queries, but the additional wrapping can slow down syncs for more complex queries (joins, unions, subqueries).

Use Case​

Consider adding a custom sync to a datasource to address the following needs:

  • Performance issues caused by wrapping the original complex query (for example, unions across multiple tables) in a timestamp filter.
  • You want full control over how “most recent records” are determined, rather than relying on Qrvey's automatic wrapping.

Custom sync queries are available for data sources that support MongoDB, DynanmoDB, and SQL/custom queries when the dataset has an ID column defined.

Each datasource in a dataset can have its own sync configuration. For example, DataSourceA might use Full Reload, while DataSourceB uses Append and Update, and DataSourceC uses Custom.

Add a Custom Sync Query​

  1. From the data source, set the sync type to Custom.

    Qrvey wraps your original query with start and end date filters, the same logic used by the default Append and Update sync.

  2. Edit this query as needed.

    Unlike a standard query, a custom sync query does not require timestamp columns. You define how the timeframe is applied, and you can cast or transform columns differently if needed.

  3. To insert a time boundary, type {{ in the query editor. Qrvey displays the available tokens for the start and end of the sync window (for example, the last successful sync time and the current time).

    When testing the query, you can supply sample values for these tokens. When a real sync runs, Qrvey substitutes the actual sync start and end times automatically.

  4. If needed, enable the long-running query option for the initial sync query. An initial load often takes longer than a day-to-day sync.

  5. Test the query before saving. Qrvey checks the columns returned against your original query and dataset and prompts you if it finds differences such as new or removed columns, changed data types, or different tables or views. You can continue if the difference is expected.

Edit a Custom Sync Query​

After creating a custom sync query, you can edit it in the following areas:

  • Data Sync tab
  • Data source pill (edit query option)

Changes to Original Query​

If you edit the original query for a data source that already has a custom sync query defined, Qrvey displays a banner prompting you about a possible mismatch. Review and update the custom sync query if needed.