Cube style guide

This style guide includes best practices on data modeling in Cube.

While Cube allows for certain flexibility with regards to data modeling, following this fairly opinionated style guide helps create maintainable semantic layers and reduce effort to support them in the long run.

This style guide is intended to be used by:

• All users of Cube Cloud and self-hosted Cube Core deployments.
• Solution integrators from the Cube Partner Network .
• Cube team (for demo projects, documentation, and customer engagements).

Syntax

• Default to YAML syntax for data modeling.
• Use snake case when using either YAML or JavaScript syntax.
• Follow the recommendations on YAML syntax and SQL syntax below.

Folder structure

• Put cube and view files in model/cubes and model/views folders.
• Within these folders, create subfolders to reflect your business units structure.

cube_project
└── model
    ├── cubes
    │   ├── finance
    │   │   ├── stripe_invoices.yml
    │   │   └── stripe_payments.yml
    │   └── sales
    │       └── base_opportunities.yml
    └── views
        ├── product
        │   └── cloud_tenants.yml
        └── sales
            └── opportunities.yml

Cubes

• Cubes should remain private; set public: false for all cubes. Only views can be exposed to visualization tools.
• A cube’s name should represent a business entity and be plural. If a cube’s name may clash with a view’s name, add the base_ prefix to the cube’s name, e.g., base_opportunities.yml.
• If possible, use sql_table instead of sql, i.e., use sql_table: schema.table instead of sql: SELECT * FROM schema.table.
• Use many_to_one, one_to_many, one_to_one join relationship types instead of belongs_to, has_many, has_one.
• Applicable cube parameters should be ordered as follows:
  • name
  • sql_alias
  • extends
  • data_source
  • sql
  • sql_table
  • title
  • description
  • public
  • refresh_key
  • meta
  • pre_aggregations
  • joins
  • dimensions
  • hierarchies
  • segments
  • measures
  • access_policy

Dimensions & measures

• Primary key of the cube should be the first dimension listed.
• Applicable dimension and measure parameters should be ordered as follows:
  • name
  • title
  • description
  • sql
  • type
  • primary_key
  • sub_query
  • public
  • format
  • filters
  • drill_members
• Use title and description if the name is not intuitive.

Example cube

cubes:
  - name: line_items
    sql_table: public.line_items
    public: false
 
    joins:
      - name: products
        sql: "{CUBE}.product_id = {products.id}"
        relationship: many_to_one
 
      - name: orders
        sql: "{CUBE}.order_id = {orders.id}"
        relationship: many_to_one
 
    dimensions:
      - name: id
        sql: id
        type: number
        primary_key: true
 
      - name: created_date
        sql: created_at
        type: time
 
    measures:
      - name: count
        type: count
 
      - name: total_amount
        sql: price
        type: sum

Views

• Views should be designed for data consumers and optimized for consumption in visualization tools.
• Applicable view parameters should be ordered as follows:
  • name
  • description
  • public
  • cubes
  • folders
  • access_policy

Example view

views:
  - name: orders
 
    cubes:
      - join_path: base_orders
        includes:
          # dimensions
          - status
          - created_date
 
          # measures
          - total_amount
          - total_amlunt_shipped
          - count
          - average_order_value
 
      - join_path: base_orders.line_items.products
        includes:
          - name: name
            alias: product
 
      - join_path: base_orders.line_items.products.product_categories
        includes:
          - name: name
            alias: product_category
 
      - join_path: base_orders.users
        prefix: true
        includes:
          - city

SQL style guide

• Indent with 2 spaces.
• Use trailing commas.
• Use upper case for SQL keywords and function names.
• Use != instead of <>.
• Always use the AS keyword when aliasing columns, expressions, and tables.
• Unless SQL query is a trivial one-liner, start SQL query from the new line.
• Use new lines, optimize for readability and maintainability.
• Use common table expressions (CTE)  rather than subqueries.
• When joining multiple tables, always prefix the column names with the table name/alias.
• Use single quotes for strings.
• Avoid initialisms and unnecessary table aliases.
• If there’s only one thing in the list (e.g., projection expressions in SELECT), put it on the same line as the opening keyword.
• If there are multiple things in the list, put each one on its own line (including the first one), indented one level more than the opening keyword.

Example SQL

cubes:
  - name: california_users
    sql: |
      SELECT
        id,
        first_name,
        last_name
      FROM public.users WHERE state = 'CA'
 
    dimensions:
      - name: id
        sql: id
        type: number
        primary_key: true
 
      - name: first_name
        sql: first_name
        type: string
 
      - name: last_name
        sql: last_name
        type: string
 
    measures:
      - name: count
        type: count

YAML style guide

• Use .yml extension instead of .yaml.
• Indent with 2 spaces.
• Indent list items.
• Use a new line to separate list items that are dictionaries, where appropriate.
• Make sure lines are no longer than 80 characters.
• Prefer literal style (|) for multi-line strings over folded style (>).
• If quotes are needed around a string, use double quotes.

Example YAML

cubes:
  - name: users
    description: |
      All users in the system.
      Note that this dataset contains PII data.
    sql_table: public.users
 
    dimensions:
      - name: id
        sql: id
        type: number
        primary_key: true
 
      - name: city
        sql: city
        type: string
 
      - name: lifetime_value
        sql: "{line_items.total_amount}"
        type: number
        sub_query: true
 
    measures:
      - name: count
        type: count
 
      - name: total_orders_amount
        sql: "{lifetime_value}"
        type: sum

JavaScript style guide

• Indent with 2 spaces.
• Don’t use trailing semicolons.
• Don’t use trailing commas after last elements of arrays and objects.
• Use a new line to separate list items that are objects, where appropriate.
• Make sure lines are no longer than 80 characters.
• If quotes are needed around a string, use backticks .

Example JavaScript

cube(`users`, {
  sql_table: `public.users`,
 
  dimensions: {
    id: {
      sql: `id`,
      type: `number`,
      primary_key: true
    },
 
    city: {
      sql: `city`,
      type: `string`
    },
 
    lifetime_value: {
      sql: `${line_items.total_amount}`,
      type: `number`,
      sub_query: true
    }
  },
 
  measures: {
    count: {
      type: `count`
    },
 
    total_orders_amount: {
      sql: `${lifetime_value}`,
      type: `sum`
    }
  }
})

Credits

This style guide was inspired in part by:

• Brooklyn Data Co. SQL style guide 
• LAMS Style Guide