> ## Documentation Index
> Fetch the complete documentation index at: https://docs.cube.dev/llms.txt
> Use this file to discover all available pages before exploring further.

# 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][cpn].
* Cube team (for demo projects, documentation, and customer engagements).

## Syntax

* Default to [YAML syntax][ref-syntax-model] for data modeling.
* Use [snake case][ref-syntax-naming] when using either YAML or JavaScript
  syntax.
* Follow the recommendations on [YAML syntax][self-yaml] and [SQL
  syntax][self-sql] below.

## Folder structure

* Put [cube and view files][ref-syntax-folder-structure] in `model/cubes` and
  `model/views` folders.
* Within these folders, create subfolders to reflect your business units
  structure.

```tree theme={"dark"}
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`][ref-public] 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`][ref-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][ref-join-rel] instead of `belongs_to`, `has_many`, `has_one`.
* Applicable [cube parameters][ref-cube-params] 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][ref-dimension-params] and [measure
  parameters][ref-measure-params] 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

```yaml theme={"dark"}
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][ref-view-params] should be ordered as follows:
  * `name`
  * `description`
  * `public`
  * `cubes`
  * `folders`
  * `access_policy`

### Example view

```yaml theme={"dark"}
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)][wiki-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

```yaml theme={"dark"}
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][ref-yaml-folded-and-literal] (`|`) for multi-line strings
  over folded style (`>`).
* If quotes are needed around a string, use double quotes.

### Example YAML

```yaml theme={"dark"}
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][wiki-backtick].

### Example JavaScript

```javascript theme={"dark"}
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](https://github.com/brooklyn-data/co/blob/main/sql_style_guide.md)
* [LAMS Style Guide](https://looker-open-source.github.io/look-at-me-sideways/rules.html)

[cpn]: https://cube.dev/consulting/cube-partner-network

[ref-syntax-model]: /docs/data-modeling/syntax#model-syntax

[ref-syntax-naming]: /docs/data-modeling/syntax#naming

[ref-syntax-folder-structure]: /docs/data-modeling/syntax#folder-structure

[ref-public]: /reference/data-modeling/cube#public

[ref-sql-table]: /reference/data-modeling/cube#sql_table

[ref-join-rel]: /reference/data-modeling/joins#relationship

[ref-cube-params]: /reference/data-modeling/cube#parameters

[ref-measure-params]: /reference/data-modeling/measures#parameters

[ref-dimension-params]: /reference/data-modeling/dimensions#parameters

[ref-view-params]: /reference/data-modeling/view#parameters

[self-yaml]: #yaml-style-guide

[self-sql]: #sql-style-guide

[wiki-cte]: https://en.wikipedia.org/wiki/Hierarchical_and_recursive_queries_in_SQL#Common_table_expression

[wiki-backtick]: https://en.wikipedia.org/wiki/Backtick

[ref-yaml-folded-and-literal]: /docs/data-modeling/dynamic/jinja#folded-and-literal-strings