🖇️ Managing the data model

Each data model describes its:

• Source of data: a database table or a SQL query
• Fields: the columns present on this data model
• Relations: relationships to other data models

Additionally, for certain kinds of data models that almost every B2B SaaS company has, such as accounts, users and analytics events, you can describe additional semantic properties about the data models.

These later make it even easier for anyone on your team to do complex things like cohort retention analysis, without having to worry about database tables or configuring anything.

models:
  account:
    name: Account

    semantics:
      kind: Account
      properties:
        created_at: created_at

    properties:
      account_id:
        name: Account ID
        type: String
      name:
        name: Name
        type: String
      payment_plan:
        name: Payment Plan
        type: Enum
        enum_options:
          load: static
          options:
            - value: enterprise
              label: Enterprise
            - value: pro
              label: Pro
            - value: basic
              label: Basic
            - value: free
              label: Free
      created_at:
        name: Created At
        type: Date

    table: raw_account
    primary_key:
      - account_id
    relations:
      users:
        name: Users
        type: hasMany
        model_id: user
        join_strategy:
          join_key: account_id
      onboarding_response:
        name: Onboarding Response
        type: hasOne
        model_id: onboarding_response
        join_strategy:
          join_key: account_id

Data source

The data source can be defined in three ways. Only one may be used for a given model.

SQL

models:
  somemodel:
    name: My model
    sql: select id, amount / 100 as amount_usd from x

Table

models:
  somemodel:
    name: My model
    table: schemaname.tablename # use the whole table as-is

Properties

Sets the properties (aka Fields) that a model has. Each field must be listed explicitly in order to be shown on the platform. Fields that are present in the data source but not in the properties list are not visible to users.

Properties can have the following type:

• String
• Enum
• Boolean
• Number
• Integer
• Float
• Date

Relations

Relations describe how different data models are linked together. Relations:

• Encapsulate the semantic meaning of the relationships – two data models might have several relationships between each other, with different meanings (e.g. a Person might have multiple relations to other Persons: friends and enemies)
• Centrally define the SQL join logic

Relations are, by default, unidirectional. They are defined from the "base model" – the data model from which you can use them. For example, for an User's Car, User would be the base model, and Car would be the related model.

hasMany

Each row in the base model has zero or more (up to infinity) matches in the related model (e.g. Company->Employee)

models:
  ...
  company:
    ...
    relations:
      employees: 
        name: Employees
        type: hasMany
        model_id: employee
        join_strategy:
          join_key_on_base: company_id
          join_key_on_related: company_id

hasOne

Each row in the base model has exactly one match in the related model, or it has none at all (e.g. Employee->Employer)

models:
  ...
  employee:
    ...
    relations:
      employees: 
        name: Employer
        type: hasOne
        model_id: company
        join_strategy:
          join_key_on_base: company_id
          join_key_on_related: company_id

manyToMany

Functions just like hasMany; each row in the base model has zero or more matches in the related model (e.g. User->Team where every user can be in multiple teams and every team can have multiple users)

models:
  ...
  team:
    ...
    relations:
      users: 
        name: Users
        type: manyToMany
        model_id: user
        join_strategy:
          through:
            model_id: supersimple_user_in_team
            join_key_to_base: team_id
            join_key_to_related: user_id  
          join_key_on_base: team_id
          join_key_on_related: user_id

hasOneThrough

Functions just like hasOne; the underlying database has an intermediary table (e.g. a Person's Grandfather is defined through Person->Parent->Parent)

models:
  ...
  person:
    ...
    relations:
      father: # This relation is used in the definition of the next relation
        name: Father
        type: hasOne
        model_id: person
        join_strategy: 
          join_key_on_base: father_id
          join_key_on_related: person_id
      grandfather:
        name: Grandfather
        type: hasOneThrough
        model_id: person
        join_strategy: 
          steps:
            - relation_key: father # This uses the relation defined above
            - relation_key: father

TIP

Note that you only need to define one level of relations between data models. It's always possible to later dynamically traverse through your entire data graph, e.g. going from accounts to their users, to the users' analytics events.

Metrics

Metrics allow you to reuse calculation logic in a flexible way. Metrics correspond to a single base model, and can be used from anywhere that has access to that data model. A metric can also be broken down (grouped) by any of that data model's Fields.

In your models YAML file, you can define metrics as follows:

metrics:
  transaction_gmv:
    name: GMV
    model_id: transaction
    aggregation:
      type: sum
      key: amount

The Metric can then be used as described under summarization options.