Types of Models
You can create two types of Models in Hevo.
Non-Incremental (or Full) Models
Full Models re-create the table at every run. This allows you to change the query without worrying about the changed table schema. Full Models do not require a primary key to locate the starting point for the updates as the table is re-created at every run based on the fields in the query. These are also referred to as Truncate and Load Models in legacy Models.
Each time a table is re-created, you lose all the existing data and any constraints that you may have defined on it. Also, in case of legacy non-incremental Models created with the Amazon Redshift Destination, each time the existing table is dropped, you may lose permissions on it if you do not have the adequate privileges. In such a scenario, the table has to be created manually. Alternatively, you can configure permissions to objects created in the future using ALTER DEFAULT PRIVILEGES, so that the new table is created automatically. Refer to the ALTER DEFAULT PRIVILEGES document for steps to do this. By default, you can change only your own default access privileges. A superuser can specify default privileges for other users too.
Incremental Models
Incremental Models allow you to export only the changed data to the output table. This means that instead of recreating the table, you can choose to only update existing records and insert new records into the table.
Note: There is no way to configure the existing column mappings explicitly for both types of Models. You need to add appropriate column aliases in the query to make sure the output table schema does not change when the table is getting recreated.
You can also specify the following optional inputs for the incremental Model:
-
Primary Keys to update rows: If provided, the output table is deduplicated based on the primary key. If primary keys are not provided, the data fetched for each Model run is appended to the existing output table. This feature is available in all Model versions.
-
Templated methods: A templated method can be included in the SQL query to limit the data fetched by the query to records created or updated subsequent to the last run of the Model. Otherwise, the entire list of records is fetched and from those, the Model query must locate the new and changed records for upsertion. This feature is available only in Models version 2.0.
Note: You can use only one template method in a query. You must provide appropriate primary keys on the Destination table to make sure there are no duplicates.