Semantic Modeling in Trilogy

The Semantic Model and Query Engine

Trilogy has an "inline semantic model" that supports iteration just like SQL and enables automatic query resolution. But what does that actually mean?

It adds three semantic statement types to the SQL you already know. These work like any other Trilogy statement: they run inline, so you can iteratively create, define, and extend a model.

There is no distinction between the semantic language and the query language—think of it as similar to DDL and SELECT in SQL.

Theory

There are many detailed technical analyses of SQL through the lenses of category theory and relational algebra. Trilogy is inspired by many of them, but takes a practical, pragmatic approach to modeling—you don't need to understand the theoretical underpinnings to follow along.

The core idea is a virtual database. In this virtual database, every field can be addressed by a unique combination of other fields.

For example, a customer table might have a customer ID, name, address, and other fields. The customer ID—if it is a primary key—is the unique identifier for the other fields. When you get a new customer, you get a new ID.

The name and address are "properties": they represent attributes associated with the unique identifier. If you get a new customer, you may not get a new address or name—they might share either with an existing customer.

At query time, we resolve all selected fields and produce a grain. The grain represents the unique combination of identifying keys. If a property and all its key fields are selected, the property can be dropped from the grain—it provides no additional information. If the property keys are not in the grain, then the property is implicitly promoted to a key for query resolution.

Tips

select customer.name; has a grain of customer.name; select customer.id, customer.name, customer.address; has a grain of just customer.id.

Aggregates can also have grains. For example, you can use select customer.id, count(customer.id) by customer.name -> same_name_customers; in a query. The principle is identical. Aggregations without an explicit grain take the grain of the query as a whole.

This logical database can then be mapped to a physical database; it doesn't matter whether all those fields are in one table. Names and addresses could live in other tables. As long as those tables have a customer.id key, they can be combined as needed to resolve a query.

When we run a query, we recursively resolve each concept in the SELECT output by physically instantiating the required portion of the logical model. After optimizing for performance, we combine those pieces into the final query.

Syntax

Now let's explore how to define these concepts.

Concept Declaration

Concepts are the key unit. They have a name, a type, and, optionally, a dependency relationship with other concepts.

Keys

The core declaration is a key.

key order_id int;

Properties

We can imagine that an order ID uniquely identifies an order, but there are other things about an order we might want to know.

Those are called properties.

Property declarations include a set of dependent keys before the property name: <key1,key2...>.<name>.

Tips

Angle brackets are optional when you have a single key: <key1>.val and key1.val are treated the same.

property order_id.product string;
property <order_id>.placed datetime;
property order_id.revenue float;

A property defines a dependency relationship used for aggregation and simplification. If we say every order has a revenue value, then retrieving order revenue requires a set of rows with one order_id and one revenue value per row before we aggregate.

We can also drop revenue from inferred joins if order_id is present.

Types and the Standard Library

Trilogy includes a standard library with helpful functions and types for common use cases. IDEs and display tools can integrate with these types to provide richer experiences.

import std.geography; # import latitude/longitude

key store_lat float::latitude;

Here is an example of an editor automatically formatting a display based on the percent type:

Derived Concepts (Transformations)

Transformations represent a derivation of a new concept from other concepts. They should use the keyword auto, and the compiler will determine their type.

auto product_revenue_rank <- rank product by sum(revenue) by product desc;

Constants

Constants are a special subset of transformations in which the right-hand side is a static expression. They can undergo additional optimization.

const meaning_of_life <- 42;

Datasources

Datasources define where data comes from. They are the inputs to the resolution graph. Typically, they are tables in a database, though they can also be queries.

A datasource has a name; a mapping from asset columns (physical names) to concepts; a grain (the table's primary key, expressed as concepts); and a physical database address.

datasource order_data
(
    ord_id:order_id,
    ord_rev:revenue,
)
grain (order_id)
address orders;

To use a query—which is useful for testing—replace address with query.

datasource
(ord_id:order_id,
ord_rev:revenue,)
grain (order_id)
query '''
SELECT
    1 as ord_id, 1.23 as ord_rev
UNION ALL
SELECT
    2 as ord_id, 4.0 as ord_rev
'''
;

Merge Statements

Merge statements let you merge one concept into another. You can use them either to bridge two models or to express synonym relationships.

For example:

key number int;
key number_size string;

key _number_size <- len(number::string);

merge _number_size into number_size;

Functions

Functions let you create reusable code factories.

For example, the standard library defines a function that calculates percentages:

def calc_percent(a, b, digits=-1) -> 
    case 
        when digits =-1 then 
            case 
                when b = 0 then 0.0::numeric
                else (a/b)::numeric
            end
        else round((
            case 
                when b = 0 then 0.0::float
                else (a/b)::float
            end
            )::numeric, digits) 
    end::numeric::percent;

Functions are parsed and called with the @ prefix.

SELECT
    @calc_percent(40.56, 100, 1);