While forming your API request in case of Pipelines created with Salesforce and database Sources, you can use the mode parameter to specify the type and amount of data that Hevo must query from the Source. You can ingest the entire data each time there is a change, or just append or insert the changed data in the Destination table. This has a direct impact on your consumption of the Events quota.

The supported query modes include:

Full Load

The full load mode lets you ingest all the data that is present in the table. Both updates and inserts are captured in this mode. Once the entire table is loaded, Hevo waits for six hours to ingest the table again. This mode can be used only when there are less than 5 Million rows in the table.

Sample API request:

 curl --location --request PUT 'https://us.hevodata.com/api/public/v2.0/pipelines/<pipeline_id>/objects/<object_name>/query-mode' \
--header 'accept: application/json' \
--header 'authorization: Basic <auth_token>' \
--header 'content-type: application/json' \
--data-raw '{
  "config": {
    "mode": "full_load"
  }
}'

Incrementing Timestamp

In this mode, Hevo asks you for a Timestamp Column and time interval (in milliseconds) to query your Source table. The timestamp value for the Events is automatically updated by the database for any update in the records. Hevo stores the latest timestamp value available at the Source. The timestamp value of every record within the specified time interval is compared with the stored value and the record with the latest timestamp is fetched. The Timestamp column should not contain NULL values. Only updates are captured and inserts are not captured in this mode.

Incrementing timestamp mode is useful when you want to replicate data with a delay, which is useful when there are long-running transactions or it is a design requirement.

Sample API request:

 curl --location --request PUT 'https://us.hevodata.com/api/public/v2.0/pipelines/<pipeline_id>/objects/<object_name>/query-mode' \
--header 'accept: application/json' \
--header 'authorization: Basic <auth_token>' \
--header 'content-type: application/json' \
--data-raw '{
  "config": {
    "mode": "timestamp",
    "timestamp_column_name": "timestamp_column",
    "timestamp_delay_interval_ms": "10"
  }
}'

Read more about the incrementing timestamp mode.

Incrementing ID

In this mode, Hevo asks you for an Incrementing Column and queries your Source table based on it. The incrementing column contains a unique value for every new record added and must not be NULL. For example, auto-incrementing columns in MySQL. Row updates cannot be captured in this mode as updates do not generate a new incrementing column value.

Sample API request:

 curl --location --request PUT 'https://us.hevodata.com/api/public/v2.0/pipelines/<pipeline_id>/objects/<object_name>/query-mode' \
--header 'accept: application/json' \
--header 'authorization: Basic <auth_token>' \
--header 'content-type: application/json' \
--data-raw '{
  "config": {
    "mode": "incrementing",
    "incrementing_column_name": "column_name",
    "stop_on_gap": false
  }
}'

Read more about the incrementing id mode.

Change Data Capture

This mode is a mixture of both Incrementing ID and Incrementing Timestamp modes. Thus, in this mode, both updates and inserts are captured. The timestamp column can have NULL values, but the value of incrementing ID column must be updated every time a row is inserted.

Sample API request:

 curl --location --request PUT 'https://us.hevodata.com/api/public/v2.0/pipelines/<pipeline_id>/objects/<object_name>/query-mode' \
--header 'accept: application/json' \
--header 'authorization: Basic <auth_token>' \
--header 'content-type: application/json' \
--data-raw '{
  "config": {
    "mode": "timestamp+incrementing",
    "incrementing_column_name": "column_name",
    "timestamp_column_name": "timestamp_column"
  }
}'

Read more about the change data capture mode.

Unique Incrementing

In this mode, Hevo asks you for an Incrementing Column and queries your Source table based on it. Hevo uses this column to keep track of the last ingested ID that is recorded at the Destination and fetches the new data based on it. The incrementing column must not contain NULL values. For example, values of UUID data type.

Updates cannot be captured in unique incrementing mode. Only the new data gets appended in the Destination table.

Sample API request:

 curl --location --request PUT 'https://us.hevodata.com/api/public/v2.0/pipelines/<pipeline_id>/objects/<object_name>/query-mode' \
--header 'accept: application/json' \
--header 'authorization: Basic <auth_token>' \
--header 'content-type: application/json' \
--data-raw '{
  "config": {
    "mode": "unique_incrementing",
    "unique_column_name": "column_name"
  }
}'

Read more about the unique incrementing mode.

Change Tracking

In this mode, the Change Tracking feature provided by SQL Server is used to query your Source table. Change Tracking must be enabled for the database objects in order to use it. The change retention period should be more than the replication frequency of your Hevo Pipeline to avoid any data loss.

Note:

Use an SQL client tool to enable Change Tracking for the objects you want to track, and subsequently select Change
Tracking in the Query Mode drop-down.
If Load Historical Data is enabled, you need to provide a column containing unique values to replicate your data.

Sample API request:

 curl --location --request PUT 'https://us.hevodata.com/api/public/v2.0/pipelines/<pipeline_id>/objects/<object_name>/query-mode' \
--header 'accept: application/json' \
--header 'authorization: Basic <auth_token>' \
--header 'content-type: application/json' \
--data-raw '{
  "config": {
    "mode": "change_tracking",
    "historical_enabled": true,
    "unique_column_name": "column_name"
  }
}'

Read more about the change tracking mode.

Query Modes Supported for Different Sources

The following table lists the query modes supported for different Sources and Pipeline modes.

Source Type	Pipeline Mode	Supported Query Modes
Heroku PostgreSQL	Logical,\ Table	`full load` `timestamp` `timestamp+incrementing` `unique_incrementing`
Heroku PostgreSQL	Custom SQL	`full load` `timestamp` `timestamp+incrementing` `incrementing`
MS SQL	Logical,\ Table	`full_load` `timestamp` `timestamp+incrementing` `unique_incrementing` `change_tracking`
MS SQL	Custom SQL	`full_load` `timestamp` `timestamp+incrementing` `incrementing` `change_tracking`
MySQL Amazon RDS MySQL Google Cloud MySQL Azure MySQL	Logical,\ Table	`full_load` `timestamp` `timestamp+incrementing` `unique_incrementing`
MySQL Amazon RDS MySQL Google Cloud MySQL Azure MySQL	Custom SQL	`full_load` `timestamp` `timestamp+incrementing` `incrementing`
PostgreSQL Amazon RDS PostgreSQL Amazon Aurora PostgreSQL Google Cloud PostgreSQL Azure PostgreSQL Heroku PostgreSQL	Logical,\ Table	`full_load` `timestamp` `timestamp+incrementing` `unique_incrementing`
PostgreSQL Amazon RDS PostgreSQL Amazon Aurora PostgreSQL Google Cloud PostgreSQL Azure PostgreSQL	Custom SQL	`full_load` `timestamp` `timestamp+incrementing` `incrementing`

NOTE: New Pipelines use the Full Load query mode by default for ingesting Events. Therefore, if you want to specify a different mode, you must update the mode in the API request at the earliest.