How to Handle Counter-Based Watermarks in integrator.io Exports

Most exports in integrator.io use date or datetime fields to track what data has been processed. This works great in most cases. But what if you’re working with a counter-based watermark (like an ID or sequence number)? In those cases, you might run into issues when determining the last page of data.

This scenario isn’t very common, but if you’re using a counter instead of a date, here’s a straightforward way to ensure your export handles the last page correctly and updates the watermark properly.

How to Set It Up:

  1. PreSavePage Script: Add the script below to the preSavePage function of your export.
  2. Page Size: Set the page size on your export to match the pageSize variable in the script (e.g., 19).
  3. Connection: Create a connection to integrator.io using the integrator.io connector, note the connection ID, and update the script with it.
  4. Export: Edit your export's first API call to reference the settings variable via handlebar expressions, such as {{{settings.export.watermark}}}. This ensures your export starts from the correct watermark value.

What the Script Does:

This script identifies the last page of data being exported from the source application. When it determines the last page (a page with fewer records than the page size), it updates the export settings with the watermark value from the last record of that page.

However, if the last page of data happens to perfectly match the page size, the script won’t identify it as the last page, and the setting won’t be updated. Due to this limitation, I recommend designing your flow to handle potential duplicates between runs. The chances of the same issue occurring in the next run are low, especially if new data is being generated by the source application.



import {connections,integrations,exports,imports,flows,request} from 'integrator-api';

//set this page size parameter to whatever you have set on the export
//the default page size on exports is 20
//I would put a page size of 19 to have less of a chance where the last page has exactly 19 records
//a full page is what is being used to determine the LAST page of data so that the setting can be updated with the last watermark
var pageSize = 19;

//this is the connection id for your integrator.io connection
var ioConnectionId = "637527a77f409d08acc91d00";

function preSavePage (options) {
  
  if (options.data.length !== pageSize && options.testMode === false) {
    let exportObject = getExport(options._exportId);
    exportObject.settings.watermark = options.data[options.data.length - 1].watermark;
    updateExport(exportObject)
  }
  
  return {
    data: options.data,
    errors: options.errors,
    abort: false,
    newErrorsAndRetryData: []
  }
}

function updateExport(exportObject) {
  return imports.runVirtual({
    "import": {
      "_connectionId": ioConnectionId,
      "http": {
        "relativeURI": [
          `/v1/exports/${exportObject._id}`
        ],
        "method": [
          "PUT"
        ],
        "body": [
          JSON.stringify(exportObject)
        ]
      }
    },
    "data": [{}]
  });
}

function getExport(exportId) {
  return exports.runVirtual({
    "export": {
      "_connectionId": ioConnectionId,
      "http": {
        "relativeURI": `/v1/exports/${exportId}`,
        "method": "GET"
      }
    }
  }).data[0];
}
0

Comments

0 comments

Please sign in to leave a comment.

 

Didn't find what you were looking for?

New post