Query Model

Introduction

When you have duplicate SQL logic in multiple reports, or a slow-running complex SQL query you want to speed up performance, one possible solution is to pre-transform the data and centralize the logic. Query Model is designed to help with that use case.

In Holistics, a Query Model (or SQL Model) is a data model created from a SQL SELECT statements that perform data transformations on other tables/models. Think of query model as a view (or materialized view) in your database.

Benefits:

• Query performance: Pre-aggregate large query that scan many rows into smaller table for performance improvement.
• Reusability of logic: Building reusable models to be used in multiple places, avoid repeating the same SQL query/logic.

tip

• Store query result in the database: By default the query result is calculated at run time and is not stored in your database. To store query result, you will need to use Model Persistence.
• Holistics's Query Model is best used for straight-forward, small scale transformation. For more advanced transformation features (for example, incremental transformation), we recommend using a dedicated transformation tool.

Create a Query Model

To create a new query model, go to the AML Studio, click on the + symbol next to a folder, and select Add Query Model. A new screen will appear, and from here you can write the query of the model.

After running the query and save the model, a new .model.aml file will be created.

Query Model Syntax

Below is a sample of how the Query Model syntax will look like. All of these codes will be generated when you create the model using the graphical UI, but you can also create the query model manually by writing from scratch:

Model model_name {
  type: 'query'
  label: "Model Label"
  description: ""
  owner: '[email protected]'
  data_source_name: 'data_source_name'

  models: [model_a, model_b]
  query: @sql SELECT {{ #model_a.field_name }} FROM {{ #model_a }};;

  dimension dimension_name_1 {
    // dimension 1 definition
    label: 'Dimension Label 1'
    type: 'text | number | date | datetime | truefalse'
    description: 'Field Description'
    hidden: true|false
    definition: @sql {{#SOURCE.column_name}};;
  }

  measure measure_name_1 {
    // measure 1 definition
    label: 'Measure Name'
    type: 'number | date | datetime'
    definition: @sql {{#SOURCE.column_name}};;
    aggregation_type: 'count | count_distinct | sum | avg'
  }
}

info

Please refer to AML Query Model Reference to learn more about all available parameters and example usage.

Model Persistence

With this feature, you can execute the model's query at scheduled intervals, and store the result in a database table.

To turn on Persistence for your AML Model, add persistence param to your query model:

  Model orders {
  type: 'query'
  label: "Orders"
  data_source_name: 'demodata'
  owner: '[email protected]'

  query: @sql SELECT * FROM ecommerce.orders ;;

  // Specify persistence mode here
  persistence: FullPersistence {
      schema: 'persisted'
      on_cascade: 'rebuild'
    }
  }

  dimension id {
    label: 'Id'
    type: 'number'
  }
  // dimension ...
}

You will also need a schedules.aml file in the root folder of your AML project with the following content:

const schedules = [
  // Schedule orders model to run every 10 minutes
  Schedule { models: [orders], cron: '0,10,20,30,40,50 * * * *' }
]

info

Please refer to Query Model Persistence document to learn more about all persistence modes and how they work.

Effects of Model Persistence

The following sections will describe the effects of Model Persistence on the queries generated from your models.

With Persistence

The Query Model will now be backed by a physical table in your data warehouse. The table will be updated with new data following a schedule of your choice. Think of this similar to "materialized view" concept in standard SQL databases.

When you refer to a persisted model in another transformation, you will be querying from the table instead of rerunning the whole transformation sequence. The final query will look like this:

select field_1, field_2, calculation_1 from persisted_model_a

Pros & Cons

• Pros: Reduce the amount of actual data scanned when you explore data from this model -> lighter load on your system, and shorter query time.
• Cons: Data can be stale or persistence schedule between models can be mismatch, which produces wrong results. (Flow-based persistence will be available in the future)

Without Persistence

The model will now resemble a "view" in your database. When you refer to a non-persistence model, the model's full SQL will be inserted in the final SQL and you will rerun the whole transformation sequence.

When querying a "non-persisted query model", the compiled SQL will usually contain a CTE (SQL WITH statement). Something like:

with model_a as (
  select
    field_1
    , field_2
    , some_calculation as calculation_1
  from source_table
)

select
  field_1
  , field_2
  , calculation_1
from model_a

Pros & Cons

• Pros: Have visibility of the whole transformation sequence
• Cons: Possible higher load to your database, and slower query.

When should I use Persistence?

Here is the general rule of thumb to help you decide whether to turn on Model Persistence for your models:

✔️ You should use Model Persistence when:

• It is an upstream model that runs slowly (due to complex query, or large amount data is scanned)
• It is a downstream model that your end users will explore frequently. This way they can have a faster exploration experience.

❌ You should not use Model Persistence when:

• The transformation makes little changes to the data (mostly renaming, concatenating...)
• You need to ensure a series of data transformation use and produce absolutely up-to-date data.

Model Dependencies

Holistics uses the modeling syntax to know the dependencies between SQL models and determine the sequence of transformation.

For example, when you run the following query, Holistics knows that model order_master, ecommerce_orders and ecommerce_users must be executed first.

with base as (
  select
    {{ #o.user_id }}
    , {{ #oi.orders_count }} as total_orders_count
    , {{ #oi.delivered_orders_count }}  as delivered_orders_count
    , {{ #oi.gmv }} as gmv
    , {{ #oi.nmv }} as nmv
    , min( {{ #o.order_created_date }} ) as first_order_date
    , max( {{ #o.order_created_date }}) as last_order_date
    , current_date - max( {{ #o.order_created_date }}) as days_from_last_order
  from {{ #orders_master oi }}
  left join {{ #ecommerce_orders o }} on {{ #o.id }} = {{ #oi.order_id }}
  group by 1
)

select
    {{ #u.id }} as user_id
    , total_orders_count
    , delivered_orders_count
    , gmv
    , nmv
    , first_order_date
    , last_order_date
    , days_from_last_order

from {{ #ecommerce_users u }}
left join base on {{ #u.id }} = base.user_id

If you save the query to a model called user_facts_aggr, Holistics can generate a dependency map for that model.

Changes you made in the parent model will be carried over subsequent models. If in model orders_master we changed the definition of gmv field and got different values, this change will reflect to user_facts_aggr.

Troubleshooting

Effects when changing model structure

When editing a query model, if you change the structure of the result set (add fields, remove fields...), there will be side effects:

• If your query model has persistence, the persistence will be reset
• Removing/renaming a field will:
  • Invalidate any custom dimensions or measures that are referring to it in their formulas
  • Invalidate any relationships that are pointing to that field
  • Break any query model or report that refers to that field
• Merely changing a field's data type will not reset the persistence setting. However, it can cause unforeseen issues in custom dimensions, measures and models using that field.

Broken downstream models

A query model can be broken when the structure of its upstream model is changed. For example:

• When the model's query refers to fields/measures that are no longer available, or invalid in the upstream model.
• When the upstream model is invalid.