Syntax for Querying Models

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.

ActionSyntax

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.

When Defining Fields

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

Using model syntax for better query performance

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