Query Modes for NetSuite SuiteAnalytics

Hevo queries your NetSuite SuiteAnalytics Source to ingest data. It uses the SuiteAnalytics Connect service to fetch data from your NetSuite analytics data source.

Hevo supports the following query modes:

• Advanced Full Load

• Delta-Timestamp

• Change Data Capture

• Full Load

In all query modes except Full Load, Hevo uses primary key columns, timestamp columns, or a combination of both to identify the incremental data to be ingested from the Source objects. Therefore, your primary keys or timestamp columns must not contain NULL values, as it may lead to mismatches between the Source and Destination data.

Refer to the sections below for details on these query modes.

Advanced Full Load

In the Advanced Full Load mode, Hevo retrieves data from your Source object using one or more primary keys. This mode is used when the Source object contains primary keys but does not include an incrementing timestamp column. Hevo tracks the offset for the next batch of records to be ingested based on these primary keys. However, as the Source object does not contain a timestamp column, Hevo can identify the next set of rows to be ingested only after resetting the offset.

Hevo runs one of the following queries to ingest data from the Source at the configured ingestion frequency:

• If the primary key comprises a single column:

  SELECT TOP (<no of records to poll>) *
  FROM <table_name>
    WHERE `id_column` > last_recorded_offset
  ORDER BY `id_column` ASC
  

• If the primary key comprises one or more columns:

  SELECT TOP (<no of records to poll>) *
  FROM <table_name>
    WHERE (`id_column1` > last_recorded_offset1 OR
          (`id_column1` = last_recorded_offset1 AND
          (`id_column2` > last_recorded_offset2 OR
          (`id_column2` = last_recorded_offset2 AND
            ...
          (`id_columnN` > last_recorded_offsetN)))
  ORDER BY `id_column1`, `id_column2`, ..., `id_columnN`
  

Delta - Timestamp

In the Delta - Timestamp mode, Hevo retrieves data from your Source object using an incrementing timestamp column.

Hevo uses this mode when the Source object does not contain primary keys and:

• New records are appended to the Source object, and a timestamp is maintained.

• Data in the Source object is updated, and the updated timestamp is maintained.

Hevo runs the following query to ingest data from the Source at the configured ingestion frequency:

SELECT * FROM <table_name>
  WHERE `timestamp_column` >= last_polled_time
ORDER BY `timestamp_column`;

As Hevo identifies the data to be ingested using the value of the timestamp column, the dataset may contain all rows with the same timestamp.

In such a scenario, Hevo runs the following query to fetch data from the Source object continuously until the last row is reached:

SELECT * FROM <table_name>
  WHERE timestamp_column = last_polled_time
ORDER BY timestamp_column;

Once the last row is reached, to fetch all rows with a different timestamp, Hevo runs the following query:

SELECT * FROM <table_name>
  WHERE timestamp_column > last_polled_time
ORDER BY timestamp_column;

Change Data Capture

In the Change Data Capture mode, Hevo retrieves data from your Source object using a list of primary keys that uniquely identify a record and an incrementing timestamp column. This mode combines the Advanced Full Load and Delta-Timestamp modes.

In this mode, Hevo tracks the ingestion offset using a combination of primary keys and the timestamp column. The primary keys track the ID of the last ingested record, and the timestamp value keeps track of when the record was last modified.

Hevo runs the following query to ingest data from the Source at the configured ingestion frequency:

SELECT TOP (<no of records to poll>) *
FROM <table_name>
  WHERE (`timestamp_column` = last_polled_time) AND
        (`id_column1` > last_recorded_offset1 OR
        (`id_column1` = last_recorded_offset1 AND
        (`id_column2` > last_recorded_offset2 OR
        (`id_column2` = last_recorded_offset2 AND
          ...
        (`id_columnN` > last_recorded_offsetN))) OR
        `timestamp_column` > last_polled_time
ORDER BY `timestamp_column`, `id_column1`, `id_column2`, ... `id_columnN`;

Note: In this mode, Hevo applies a 5-minute backward re-ingestion window during each polling cycle. As a result, data from 5 minutes before the last recorded offset is fetched again. While this approach helps maintain data consistency, it may result in increased consumption of your Events quota. If you need to adjust the backward re-ingestion window, contact Hevo Support.

Full Load

In Full Load, Hevo ingests all data from the Source object on each ingestion run and appends it to the end of the Destination table. Hevo ingests data from a NetSuite SuiteAnalytics object using this mode when the object does not have a primary key or an incrementing timestamp column.

Hevo runs the following query to ingest data from the Source at the configured ingestion frequency:

SELECT TOP (5000000) FROM <table_name>;

As seen above, Hevo fetches up to 5 Million rows in this query mode. You can contact Hevo Support to change this limit.

Note: By default, Hevo ingests data from the Full Load objects at the Pipeline frequency, which may lead to higher Events consumption. You can reduce your Events quota consumption by ingesting Full Load objects at a lower frequency without affecting other objects in the Pipeline. Read the section Custom frequency for Full Load objects for the steps to change the default frequency.