Creating and Modifying Models

Hevo has introduced important features in Models in Release 1.44, which help in improving the performance of the Model and making it easier for you to create them. All new Models use these features. Hevo continues to supports the legacy Models, which were created prior to Release 1.44, but to consume the new features, you must update your legacy Models.

When you reset your Model, it is automatically updated with the new features. See sections Models and Updating Legacy Models for more information.

Creating a Model

Click on the Models icon in the Asset Palette to view the Models previously created in your Hevo account. Search for a Model or create a new Model by performing the steps listed below.

Prerequisites

  • The tables on which the SQL queries must be run exist in the Destination.

  • Working knowledge of SQL.

Steps to create a Model

  1. Click on Models in the Asset Palette, and then click +CREATE MODEL in the List View. A Model is created in Draft state.

  2. In place of Untitled Model, specify a name for the Model and click Save.

  3. Select an existing Destination from the list or click on + ADD NEW DESTINATION to create one. Refer to Destinations for steps to add a Destination. The Query Builder is displayed, where you can create the queries for the Model.

  4. Write and test your query in the Query Builder. You can also browse your Destination’s schema from the left pane.

  5. Click on RUN QUERY to test the query and check the result.

  6. Click on EXPORT TO, and in the Export Query Output dialog box, select the table where the query results must be exported.

    • Create New Table

      • Specify a name for the table
    • Choose Existing Table

      • Select an existing table, which has the same structure as the query output, from the list of tables in the Destination. The system displays a warning that the table will be recreated.
        This option is not recommended if you do not want to lose the existing data in the table.

        An alert that the existing table will be recreated

  7. Optionally, select the Incremental Model toggle option to export only the new or changed data. Then, Choose Primary Keys for updating the rows in the table on each run of the Model.

  8. Click Continue. The system validates the query.

  9. Define the schedule and frequency at which you want to run the Model. See the section below.

Scheduling a Model

After successfully validating the queries, you are required to set a schedule as per which the Model must run.

There are three ways in which you can do this:

  • When events are loaded in a table: Schedule the Model to run when a particular table in a Destination is loaded with data. Since loads can be very frequent, Models runs at the most once per hour.

  • Runs Every: Schedule the Model to run at a fixed frequency. The highest frequency is 5 min and the lowest is 24 hours.

  • Custom: Choose Custom in Runs Every option to define a specific time for the Model run. The Runs On drop-down provides you three options in this:

    • Fixed Interval of: Provide a time interval in hours, at which the Model must run. Suppose you specify the interval as 4 hours, and as per that, the Model is scheduled to run at 12 noon, 4 p.m., 8 p.m. and so on. If you manually run the Model between these times, say at 5 p.m., then, the next run happens 4 hours after that at 9.00 pm and not 8.00 pm.

    • Daily: Schedule the Model to run daily at the specified time.

    • Weekly: Select the days of the week and the time when you want the Model to run.

Click SCHEDULE to set up the schedule. To change the schedule for an existing Model, click Change  on the top right corner of the Model page.

Updating a Model

Note: You cannot change the Destination table details while the Model is running.

You can update a Model by modifying the SQL query to select another Destination table or by changing the output table for the query results. To do this:

  1. In the list of Models, click on the Model that you want to update.

  2. Navigate to the Source Query tab of the Model, and make any or all of the following changes, as required:

    • Change the schedule

    • Change the table for exporting the results

    • Modify the SQL query and click on SAVE QUERY to commit the changes. For incremental Models, you can also click the drop-down option of Save Query and Reset Model to recreate the output table.

Modify the schedule, export table, or query

Last updated on 16 Oct 2020