Query Modes

Query modes define how Hevo must query your Source database for ingesting data. Hevo uses the following modes for querying data:

• Full Load

• Unique Incrementing Append Only

• Delta - Timestamp

• Change Data Capture

• Change Tracking

• XMIN

Query modes are applicable only for log-based and table-based Pipelines created with JDBC Sources, such as MySQL, PostgreSQL, Oracle, and MS SQL. Read Query Modes for Ingesting Data from Relational Databases.

---

Query Modes and Events Quota Consumption

For log-based Pipelines, query modes are used for ingesting only the historical data, which does not count towards your Events quota consumption. Incremental data, which does count towards your Events quota, is ingested using logs specific to the Source such as OpLog for MongoDB, and BinLog for MySQL, leading to exactly one-to-one replication. Read Ingestion Modes. Thus, query modes do not have an effect on quota consumption for log-based Pipelines.

For table-based Pipelines, query modes are used for both historical and incremental data ingestion. As incremental Events are counted towards your Events quota, choosing a suitable query mode can reduce your Events quota usage.

Example:

Let us assume you have a Source table with:

• Number of rows = 100

• Ingestion frequency = 6 Hours

and

• Number of rows modified = 4

• Number of rows appended = 5

Scenario 1:

Query mode = Full Load

The entire table is ingested in each run of the Pipeline.

Therefore:

• Number of Events ingested on subsequent Pipeline runs = 105 (number of rows in the table)

• Total number of Events ingested and loaded in 12 hours = 105 + 105 = 210

Scenario 2:

Query mode = XMIN

Both updates and appends to the table are ingested in each run of the Pipeline.

Therefore:

• Number of Events ingested on the next Pipeline run = 9

• Number of Events ingested on the subsequent Pipeline run = 0

• Total number of Events ingested and loaded in 12 hours = 9 + 0 = 9

Note: XMIN query mode is only available for PostgreSQL Sources.

Scenario 3:

Query mode = Unique Incrementing Append Only

Only new rows added to the table are ingested. Hevo recommends this query mode only if the rows in your table are immutable.

Therefore:

• Number of Events ingested on the next Pipeline run = 5

• Number of Events ingested on the subsequent Pipeline run = 0

• Total number of Events ingested and loaded in 12 hours = 5 + 0 = 5

Scenario 4:

Query mode = Delta-Timestamp

Delta-Timestamp can be used in the two configurations which can only be set from the Source.

• Updated timestamp maintained only for updates to a table.

  This configuration only tracks the updates made to the table.

  Therefore:

  • Number of Events ingested on the next Pipeline run = 5

  • Number of Events ingested on the subsequent Pipeline run = 0

  • Total number of Events ingested and loaded in 12 hours = 5 + 0 = 5

• Updated timestamp maintained for updates and appends to a table.

  This configuration can track both update and appends to the table.

  Therefore:

  • Number of Events ingested on the next Pipeline run = 9

  • Number of Events ingested on the subsequent Pipeline run = 0

  • Total number of Events ingested and loaded in 12 hours = 9 + 0 = 9

Scenario 5:

Query mode = Change Data Capture or Change Tracking

Both updates and appends to the table are ingested.

So, total number of Events ingested in 12 hours = 9 + 0 = 9

Note: Change Tracking mode is applicable for only MS SQL Source types.

To conclude, using Full Load query mode can drastically increase your Events quota consumption as the entire table is ingested on every Pipeline run. Hence, this mode should only be used when no indexed or timestamp columns are available.

Choosing between Delta-Timestamp, Unique Incrementing Append Only, CDC, or Change Tracking (for MS SQL Source types) query modes does not change your Events quota usage, as these query modes only capture modifications (updates or appends) to the table.

---

See Also

• Pipeline Frequency

---

Revision History