Working with 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 support the legacy Models, which were created prior to Release 1.44. However, to use the new features, you must update your legacy Models.

When you reset your Model, it is automatically updated with the new features. Read Models and Updating Legacy Models.


Prerequisites

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

  • Working knowledge of SQL.


Creating a Model

  1. In the Asset Palette, click TRANSFORM.

  2. If you are creating your first Model, click CREATE MODEL in the Transform home page.

    First Model Creation

    Else, click + CREATE MODEL in the Models List View.

    List View Create Model

  3. In the Select Destination page, do one of the following:

    • Select an existing Destination.

      Select Destination Model

    • Click + ADD DESTINATION to configure a new Destination.

    A Model is created in the Draft state.

  4. In place of Untitled Model, specify a name for the Model, and click SAVE.

  5. In the Query Builder, create the queries for the Model. Write the query in the Query Builder. You can also browse your Destination’s schema from the left pane.

    Examples:

    Scenario 1: Creating Models using simple SQL queries

    Let us suppose you want to extract the ID, first name, and last name of employees born before Jan 01, 2000. The sample query would be:

    
    select
      emp.id as emp_id,
      emp.first_name,
      emp.last_name
    from
      emp
    where
      emp.dob < '2000-01-01';
    
    

    Scenario 2: Creating Models using SQL queries with Join clause

    Let us suppose you want to extract the count of employees born before Jan 01, 2000, grouped by department. The sample query would be:

    
    select
      dept.name as department_name,
      count(emp.id) as emp_count
    from
      emp
      join
        dept
        on emp.dept_id = dept.id
    where
      emp.dob < '2000-01-01'
    group by
      dept.name;
    
    
  6. Click TEST QUERY, and check the result.

  7. Click EXPORT TO, and in the Export Query Output dialog box, select one of the following options to export the query results:

    • Create New Table: Specify a unique name for the table. This table is created in the Destination.

    • Choose Existing Table: Select an existing Destination table, which has the same structure as the query output. The system displays a warning that the table will be recreated.

      Note: If you select this option, you lose the existing data in the table.


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 Run On drop-down provides you three options in this:

    • Fixed Interval of: Provide a time interval in hours, at which the Model must run. Let us suppose you specify the interval as 4 hours, and as per that, the Model is scheduled to run at noon, 4 PM (UTC), 8 PM (UTC), and so on. If you manually run the Model between these times, say at 5 PM (UTC), then the next run happens 4 hours after that, at 9 PM (UTC) and not 8 PM (UTC).

    • 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. In the Summary Bar, click Change to change the schedule for an existing Model.


Modifying 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



Revision History

Refer to the following table for the list of key updates made to this page:

Date Release Description of Change
Mar-11-2022 NA - Updated section, Creating a Model.
- Renamed section, Updating a Model to Modifying a Model.
Dec-10-2021 NA Updated the screenshots to reflect the latest UI.
Oct-04-2021 NA Updated the screenshots to reflect the latest UI.
Last updated on 11 Mar 2022