Skip to main content

Defining a Model

Defining a Model

Recently, we were designing a Trilogy model to support analytics against a stock portfolio tracking application. This provides an opportunity to walk through building a data driven app using Trilogy in practice.

This example is simplified a bit to make it easier to follow.

Tips

For an existing database, you don't have to define a model from scratch as we will do here - you can use the trilogy ingest CLI command to create models off your existing database schema.

The Context

We have access to a collection of information around the holdings of a portfolio, dividends paid, and orders placed. We also have some generic information about stocks.

We can imagine the various kind of questions we want to be able to answer:

  • What's my return for stocks?
  • Which stocks have paid the most dividends?
  • What's my dividend return by sector?
  • etc

We're just shooting for descriptive analytics/reporting here - there's a rich, rich ecosystem of quantitive finance libraries that can help you with more advanced questions, but let's get a basic handle on what we have.

The Tech Stack

We're going to use Trilogy to define our model, and DuckDB to run our queries. We'll load the relevant data into a local DuckDB instance as it's relatively small in the grand scheme of things - thousands of stocks, dividends, orders, not billions. We're going to skip over where we got our information from and get right to the data engineering; imagine that we're pulling from a Robinhood account or similar.

The Model - Entrypoints

Let's assume we have 4 core domains we care about.

  • Stocks
  • Orders
  • Dividends
  • Portfolio

The Model - Stocks

So let's get down to creation. We know we need to know things about stocks. Stocks can be identified in many ways - ISIN, ticker, etc. We'll assume that ticker is the unique key, but that we can load a integer surrogate key for it. (we like integers for anything we might use in a join).

We'll start a model with concepts. We want our surrogate id, and then we can associate that with the ticker, ISIN, name, sector, and industry.


key id int; # surrogate identifier for a stock
property id.ticker string; # the ticker
property id.security_identification_number string; # also international. But that was too long to type.
property id.name string; # the name of the stock
property id.sector string; # the sector of the stock
property id.industry string; # the industry of the stock

We'll need some way to get that data. Let's assume we ran this DDL in duckdb.

DROP TABLE IF EXISTS symbols CASCADE;
CREATE TABLE symbols (
    id INTEGER PRIMARY KEY,
    ticker VARCHAR,
    isin VARCHAR,
    name VARCHAR,
    sector VARCHAR,
    industry VARCHAR
);

We'd bind that in our trilogy like this. For columns that match the concept name, we can just include them; if we want to map a column name to a concept, we separate them with a colon.

Note we know the primary key is id, so we can set the grain that way.


root datasource stock_info (
    id,
    ticker,
    isin: security_identification_number, # column and field have different name
    name,
    sector,
    industry
)
grain (id)
address symbols;

Let's save this file as stocks.preql.

The Model - Orders

Let's pivot over to orders. We already defined a stock dataset, so let's pull that in first.

import stocks as stock;

We know an order will be placed for a stock with a certain quantity and price. We'll also want to know the date of the order. Let's assume a surrogate PK too.


key id int; # surrogate identifier for an order
property id.quantity float;
property id.price numeric(18, 4);
property id.date_placed date;

We'll need some way to get that data. Let's assume we ran this DDL in duckdb.

DROP TABLE IF EXISTS orders CASCADE;
CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    stock_id INTEGER,
    quantity FLOAT,
    price DECIMAL(18, 4),
    date_placed DATE
);

We'd bind that in our trilogy like this. Note that we bind the import stock.id concept to the stock_id column directly.


root datasource order_info (
    id,
    stock_id: stock.id,
    quantity,
    price,
    date_placed
)
grain (id)
address orders;

Let's save this file as orders.preql.

Let's Take A Query Break

We've set up two components of our model - stocks and orders. Let's take a break to see what we can do now.

Let's answer - how many orders have we placed by industry?

There's actually two ways to slice this; let's start with the easiest one.

import orders as orders;

SELECT
    orders.stock.industry,
    coalesce(count(orders.id), 0) as order_count
order by
    order_count desc;

Since orders references the stock model, we can directly access the industry property. We're counting the number of orders, and ordering by that count.

But what if you do industry level reporting, and you want to know all the industries, even if no orders were placed?

Good news - we already did that. An imported dimension exposes its complete domain through the nested path, so orders.stock.industry includes industries without orders. coalesce turns the unmatched count into a friendly zero. We don't need to merge anything manually.

Back to Model - Dividends

Let's pivot over to dividends.

We already defined a stock dataset, so let's pull that in first.

import stocks as stock;

We know a dividend will be paid for a stock with a certain amount and date. We'll also want to know the date of the dividend. Let's assume a surrogate PK as well.

key id int;
property id.amount numeric(18, 4);
property id.date_paid date;

We'll need some way to get that data. Let's assume we ran this DDL in duckdb.

DROP TABLE IF EXISTS dividends CASCADE;
CREATE TABLE dividends (
    id INTEGER PRIMARY KEY,
    stock_id INTEGER,
    amount DECIMAL(18, 4),
    date_paid DATE
);

Create the datasource - note that we bind the import stock.id concept to the stock_id column directly, again.

root datasource dividend_info (
    id, 
    stock_id: stock.id,
    amount,
    date_paid
)
grain (id)
address dividends;

Save it as dividends.preql;

Query Break 2

Let's check out how many dividends we got. Orders and dividends are two separate fact domains, so we don't want to combine their raw rows and accidentally repeat values. We'll summarize each one at the shared stock grain first.

import stocks as stocks;
import orders as orders;
import dividends as dividends;

rowset orders_by_stock <- select
    orders.stock.id as stock_id,
    count(orders.id) as order_count;

rowset dividends_by_stock <- select
    dividends.stock.id as stock_id,
    sum(dividends.amount) as dividend_return;

# this will return all industries, even if no orders were placed
SELECT
    stocks.industry,
    coalesce(sum(orders_by_stock.order_count), 0) as order_count,
    coalesce(sum(dividends_by_stock.dividend_return), 0) as dividend_return
subset join orders_by_stock.stock_id = stocks.id
subset join dividends_by_stock.stock_id = stocks.id
order by
    order_count desc;

Holdings

Last but not least, let's define our holdings.

Surprise surprise, we'll start with our stock info.

import stocks as stock;

We know we'll have a certain quantity held. Since this is 1-1 with stocks, we can just use the stock ID as our PK.


property stock.id.qty_held float;
property stock.id.value numeric(18, 4);
property stock.id.cost_basis numeric(18, 4);

We'll need some way to get that data. Let's assume we ran this DDL in duckdb.

DROP TABLE IF EXISTS holdings CASCADE;
CREATE TABLE holdings (
    stock_id INTEGER PRIMARY KEY,
    value DECIMAL(18, 4),
    cost_basis DECIMAL(18, 4),
    qty_held FLOAT
);

Create a datasource

root datasource holding_info (
    stock_id: stock.id,
    value,
    cost_basis,
    qty_held
)
grain (stock.id)
address holdings;

Query Break 3

Now we can calculate which of our holdings give us the most dividends.

Just like the last example, we'll aggregate each fact before combining them. This keeps a holding's value from being repeated once for every dividend payment.

import stocks as stocks;
import dividends as dividends;
import holdings as holdings;

rowset dividends_by_stock <- select
    dividends.stock.id as stock_id,
    sum(dividends.amount) as total_dividend;

rowset holdings_by_stock <- select
    holdings.stock.id as stock_id,
    sum(holdings.qty_held) as total_holding_qty,
    sum(holdings.value) as total_holding_value;

SELECT
    stocks.sector,
    stocks.industry,
    sum(holdings_by_stock.total_holding_qty) as total_holding_qty,
    sum(holdings_by_stock.total_holding_value) as total_holding_value,
    coalesce(sum(dividends_by_stock.total_dividend), 0) as total_dividend,
    100 * coalesce(sum(dividends_by_stock.total_dividend), 0)
        / nullif(sum(holdings_by_stock.total_holding_value), 0) as dividend_yield
subset join holdings_by_stock.stock_id = stocks.id
subset join dividends_by_stock.stock_id = stocks.id
order by
    total_holding_qty desc
;

subset join a = b tells Trilogy that the keys in a are contained in the domain of b. It isn't an inner join and it won't silently drop unmatched rows. If neither side contains the other's complete key domain, use union join instead. And if the shared grain has more than one key, join on every key - otherwise you'll have a bad time with duplicated results.

Validate the Model

Before we wrap up, let's make sure the model actually hangs together.

# Parse, type-check, and test with mocked datasources
trilogy unit .

# Sample the real DuckDB datasources
trilogy integration . duckdb

trilogy explore orders.preql is also handy for checking the concepts, imports, and grain available to a query. And trilogy fmt <file> will format an individual Trilogy script.

Wrap up

If we were going to use this model a lot, we'd probably define an entrypoint.preql file that imports the models we commonly query together.

For each analysis/view you need, you can create a custom entrypoint.preql to replicate the intended environment. Most queries should import a fact and use dot paths to reach its dimensions; scoped joins are there when we genuinely need to combine separate fact domains.