Holistics Docs - End-to-End Business Intelligence Platform

Holistics Documentation

Welcome to the Holistics Documentation page. You'll find comprehensive guides and documentation to help you start working with Holistics as quickly as possible, as well as support if you get stuck. Let's jump right in!

Get Started

Data Modeling Syntax

Where the syntax is used

Data Modeling Syntax is used in SQL interfaces, including:

  • SQL editors: SQL Editor, Transform Model's creation interface
  • Custom Dimensions and Measures creation in Data Modeling

In SQL editors

Model reference

In queries, you can use models just like your normal database tables. This means you can combine models and raw database tables in a query. However, it is recommended that you use models all the time for consistency and ensure dependency between SQL models.

ActionSyntax
Referring a model{ { #model_name }}
Aliasing a model{ { #model_name alias }} (place alias inside the curly brackets)

For example, we want to combine models ecommerce_orders, ecommerce_products and ecommerce_order_items:

with val as (
  select
    { { #oi.order_id }}
    , { { #oi.quantity }} * { { #p.price }} as total_value
  from { {#ecommerce_order_items oi}}
  left join { {#ecommerce_products p}} on { { #oi.product_id }} = { { #p.id }}
)
    
select
  { { #o.* }}
  , val.total_value
from { {#ecommerce_orders o}}
left join val on { { #o.id }} = val.order_id

If you do not want to use alias on models, you can use the model name, just like when you query normal tables:

with val as (
  select
    { { #oi.order_id }}
    , { { #oi.quantity }} * { { #p.price }} as total_value
  from { {#ecommerce_order_items oi}}
  left join { {#ecommerce_products p}} on { { #oi.product_id }} = { { #p.id }}
)

select
  { { #ecommerce_orders.* }} -- Use model name
  , val.total_value
from { {#ecommerce_orders}} -- No alias
left join val on { { #ecommerce_orders.id }} = val.order_id -- Use model name

Field reference

ActionSyntax
Referring a base dimension or custom dimension{ {model_name.field_name}}
Referring a measure{ {#model_name.measure_name}}
Getting all fields{ {#model_name.*}}

Example of a query using field reference:

-- Calculate statistics of buyers
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

Custom Dimensions and Measures are just SQL snippets, so you must specify a field name when using them in a query. If not, the resulted column will be named automatically by your database's SQL.

In Custom Dimensions & Measures

To refer to a Base Dimensions, custom Dimensions or Measure in the same model, the syntax is

{ { #THIS.field_name }} or { { #THIS.measure_name }}

How it works

When you write a SQL using the syntax, Holistics's engine will parse it into a full query to be run against your database. In the final query:

  • Referred models will be turned into CTEs
  • Referred custom fields/measures will be turned into the full formula

This way the dependency between models will be enforced. For example, you have model users_count created from this aggregation:

select
  { {#u.age_group}} as age_group
  , { {#u.count_users}} as count_users
from { {#ecommerce_users u}}
group by 1

In the final query:

  • Model ecommerce_users is turned into a CTE
  • age_group's formula is used
  • count_users formula is used

Query optimization

When you are selecting from a Base Model, or a persisted Transform Model, if you use Holistics's field reference syntax, Holistics will be able to select only the necessary fields to be inserted into the CTE.
If the normal SQL syntax is used, the engine will need to insert all the fields in the base table to the CTE.

For example, selecting from a Base Model created from a table with more than 20 fields, using normal SQL syntax:

select 
  id
  , name
  , property_type
  , room_type
from { {#homestay_listings}}

The resulted query will include all the fields:

If you use Holistics's syntax:

select 
  { {#l.id}}
  , { {#l.name}}
  , { {#l.property_type}}
  , { {#l.room_type}}
from { {#homestay_listings l}}

This is particularly important when you query from "fat tables" with large number of columns (like Snowplow event tables).

Updated 2 months ago


What's Next

Troubleshooting

Data Modeling Syntax


Suggested Edits are limited on API Reference Pages

You can only suggest edits to Markdown body content, but not to the API spec.