Query Model

Definition

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.

Does the results of the query store back to my database? By default it doesn't store the query results into your database. However, you can turn on Persistence mode and it will be written into your database table.

Note: Holistics's Query Model is best used for straight-forward, small scale transformation. For more advanced transformation capabilities, we recommend you leverage dedicated transformation tools.

Creating Query Model

info

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

Go to Modeling tab to create a Query Model:

1. Create a new model my_query_model.model.aml in your AML project.
2. Create the model definition using Query Model syntax.
3. (Optional) Click Preview to enter Preview mode. This is useful to verify that your Query Model behaves correctly.
4. Click Save.
5. Click Deploy to production to make your new model live.

Query Model Syntax

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'
  }
}

Storage Settings (or Model Persistence)

When defining your Query Model, you have the option to write (persist) the data back to a table in your SQL database. That means at scheduled intervals, the models' SQL query will be executed, and write the results into a database table.

info

Please refer to AML Persistence Query Model Reference to learn more about all persistence modes and how they work in Holistics 4.0

To turn on Storage Settings (or Persistence) for your AML Model, simply 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 ;;

  persistence: // ... your selected Persistence mode

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

This is not an option that you should enable by default, but you should base the decision on the nature of your model.

Refer to the sections below for the differences if you choose to turn on/off this setting.

If you choose to turn on Storage Settings

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

info

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

For more details on how the persistence works, please check our docs on Storage Settings.

If you choose to turn off Storage Settings

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

info

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

When should I enable Storage Settings for my query models?

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

✔️ You should turn it on 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 turn it on 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.

Reset Storage Settings

Reset Storage Settings lets you delete the Storage Settings of a given query model.

To use this feature:

• Step 1: Navigate to your Query Model > Storage Settings.
• Step 2: Click Manage.
• Step 3: Click More > Reset.
• Step 4: Click Confirm.

Delete persisted model's underlying table

To delete the underlying table of a persisted model, go to Storage Settings Management > More > Reset Storage Settings. Then, choose Delete underlying table in your database.

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.