Using previous high watermark value in source query and high watermark query

The high watermark value for the previous run is always stored in the metadata storage. In can be used in the source query and query to calculate the high watermark value (hwm query).

Referencing HWM value in the query

Use the following notation to reference the HWM value in the query:

{TRANSFORMATION_NAME_HIGH_WATERMARK}

Read how to set the transformation name.

Working with NULL HWM value

To protect the query against the NULL HWM value use the following trick:

{token} is null or field > {token}

If the value of the HWM field represented by {token} is indeed NULL, the query will be automatically transformed to

NULL is null or field > NULL

Using previous HWM value is the Source query

Anywhere in the source query you can add the following string:

{TRANSFORMATION_NAME_HIGH_WATERMARK} is null OR 
hwm_field > {TRANSFORMATION_NAME_HIGH_WATERMARK}

For example, when

• The source query is select * from audit
• The transformation name is AUDIT
• The hwm field is audit_id

then, the source query will be:

select * from audit 
WHERE {AUDIT_HIGH_WATERMARK} is null OR audit_id > {AUDIT_HIGH_WATERMARK}

Using previous HWM value in the HWM query

Anywhere in the hwm query you can add the following string:

{TRANSFORMATION_NAME_HIGH_WATERMARK} is null OR 
hwm_field > {TRANSFORMATION_NAME_HIGH_WATERMARK}

For example, when

• The hwm query is select max(audit_id) from audit_updates 
• The transformation name is AUDIT
• The hwm field is audit_id

then, the hwm query will be:

select max(audit_id) from audit_updates 
WHERE {AUDIT_HIGH_WATERMARK} is null OR audit_id > {AUDIT_HIGH_WATERMARK}