Working with SQL Models

Last updated on May 27, 2024

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


Creating a Model

  1. If this is the first Model you are creating in Hevo, perform the actions listed in this step, else, skip to Step 3.

    1. Click + CREATE MODEL.

      Create Model

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

      • Select an existing Destination.

        Select an existing Destination

      • Click + ADD DESTINATION to configure a new Destination.

    3. In the Select your Model variant page, select SQL Models, and click CONTINUE.

      Select a Model variant

      A Model is created in the Draft state.

    4. Skip to Step 3.

  2. To create your SQL Model:

    1. Click the SQL tab in the Models List View, and click CREATE SQL MODEL.

      Create SQL Model

    2. 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.

  3. Replace Untitled Model 1 with the name of the Model, and click SAVE.

  4. In the SQL Query dialog, create the queries for the Model. 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;
    
    
  5. Click TEST QUERY, and check the result.

  6. 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 must create a schedule for your Model to run.

To do this, select from one of the following scheduling options:

Create Schedule for the Model

  • When relevant Pipelines load Events to the table: Schedule the Model to run whenever a Pipeline loads data to a particular table in the Destination. As loads can be very frequent, Hevo runs the Models at the most once per hour, which might not provide you the data in real-time.

  • Runs Every: Schedule the Model to run at a fixed frequency. The highest frequency is 5 Mins and the lowest is 24 Hrs. To transform and load data in near real-time, you can schedule the Models to run at a higher frequency.

    Note: A higher frequency may lead to increased Events quota consumption.

  • Custom: Select Custom in the Runs Every option to define a specific time for the Model run. The Run On drop-down provides the following options:

    • 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.

Change Schedule

The Change option is visible only if the Model is not a part of any Workflow, with the Run Outside Workflow option disabled. Else, the Summary Bar shows the Workflow(s) that the Model is included in. Read Creating a Workflow.

Model runs in a Workflow


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
May-27-2024 NA Updated section, Scheduling a Model to reflect the supported functionality.
Jan-09-2024 NA Merged FAQ, Can I use Models to transform and load data in real-time? into this document.
May-23-2023 NA Updated the section, Scheduling a Model to reflect the latest Hevo UI.
Nov-25-2022 NA - Updated section, Creating a Model to include UI changes.
Sep-07-2022 1.97 Updated sections, Creating a Model and Scheduling a Model to add information regarding the latest changes in Workflows.
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.

Tell us what went wrong