Model Field Expression

Knowledge Checkpoint

A grasp of these concepts will help you understand this documentation better:

• Holistics Expression

Syntaxes

While Holistics uses SQL as our underlying, final query language, we extend SQL to add our own little language on top. This page talks about the different syntax we put on top of SQL, and how they will be translated back to SQL.

Querying Models & Fields

In SQL queries (mostly Transform Models), instead of querying physical tables, you can query the model instead using this syntax.

Action	Syntax
Refer to a model	{{ #model_name }}
Refer to a model (with alias)	{{ #model_name alias }} (place alias inside the curly brackets)
Refer to a field (dimension or measure)	{{ #model_name.field_name }} {{ #alias.field_name }}
Get all fields	{{ #model_name.* }}

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

Behind the scenes, our query engine will parse the syntaxes into a full, valid SQL query to run against your database. Referred models will be turned into CTEs or a persisted table (if persistence is turned on). Referred custom fields/measures will be turned into the full formula.

It is recommended that you use models all the time for consistency and ensure dependency between SQL models.

{{ #THIS.field }} syntax usage

info

This syntax is only available in Holistics 3.0.

When defining custom dimensions or measures, to refer to a dimension or measure in the same model, the syntax is: {{ #THIS.field_name }} or {{ #THIS.measure_name }}.

THIS keyword is an alias to refer to the current model.

{{ #SOURCE.column_name }} syntax usage

info

This syntax is mostly used to create 4.0 Field definitions.

{{ #SOURCE.column_name }} references a column in the table that is connected to the table model you’re working on.

SOURCE keyword is an alias to refer to the current table underlying the model.

Using model syntax for better query performance

When you are selecting from a Table 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 normal SQL syntax is used, the engine will need to insert all the fields in the base table into the CTE.

For example, selecting from a Table 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 resulting 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).