Templatized Queries

You can use templated methods in the Model’s SQL query to make the fetch operation more efficient. Hevo adds the field __hevo_record_sequence_ts in the Destination tables when you create a Model using a templatized query. The GTE_RECORD_SEQUENCE_TS method uses this field as a timestamp to incrementally pull data from the tables. Consequently, it only fetches the records that were updated after the Model was last run.

Sample usage of templated method in simple queries:

select
   id,
   name
from
   employees
where
   ${GTE_RECORD_SEQUENCE_TS(employees, __hevo__record_sequence_ts)}

Sample usage of templated method for queries with Join clause:

select
   e.emp_no,
   e.first_name,
   e.last_name,
   d.dept_name
from
   test_demo_employees e
   join
      test_demo_departments d
      on (e.dept_no = d.dept_no)
where
   ${GTE_RECORD_SEQUENCE_TS(test_demo_employees, e.__hevo__record_sequence_ts)}

Templatized queries are normally used to reduce the execution time as well as the cost incurred in querying the data. This feature is available only on tables that are created in Hevo.

Note: If you are using Legacy Models, you can switch over to the new Models to consume the latest features. Read Updating Legacy Models.