# Dimensions

(Applies to dbt v1.12 and later)

Dimensions represent the non-aggregatable columns in your data set, which are the attributes, features, or characteristics that describe or categorize data. In the context of the Semantic Layer, dimensions are part of a larger structure called a semantic model. They are created along with other elements like [entities](./entities.md) and [simple metrics](./simple.md) and used to add more details to your data. In SQL, dimensions are typically included in the `group by` clause of your SQL query.

All dimensions require a `name`, `type`, and can optionally include an `expr` parameter. The `name` for your Dimension must be unique within the same semantic model.

(Applies to dbt v1.12 and later)

| Parameter                                                            | Description                                                                                                                                                                                                                                                                                                                                                                | Required | Type       |
| -------------------------------------------------------------------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | -------- | ---------- |
| `name`                                                               | The name of the dimension that will be visible to the user in downstream tools. It can also serve as an alias for derived dimensions<br /><br />Dimension names should be unique within a semantic model, but they can be non-unique across different models as MetricFlow uses [joins](./join-logic.md) to identify the right dimension. | Required | String     |
| `type`                                                               | Specifies the type of group created in the semantic model. There are two types:<br /><br />- **Categorical**: Describe attributes or features like geography or sales region.<br />- **Time**: Time-based dimensions like timestamps or dates.                                                                                                                             | Required | String     |
| `description`                                                        | A clear description of the dimension.                                                                                                                                                                                                                                                                                                                                      | Optional | String     |
| `expr`                                                               | Defines the underlying column or SQL query for a dimension. If no `expr` is specified, MetricFlow will use the column with the same name as the group. You can use the column name itself to input a SQL expression.                                                                                                                                                       | Optional | String     |
| `label`                                                              | Defines the display value in downstream tools. Accepts plain text, spaces, and quotes (such as `orders_total` or `"orders_total"`).                                                                                                                                                                                                                                        | Optional | String     |
| [`meta`](../../reference/resource-configs/meta.md) | Set metadata for a resource and organize resources. Accepts plain text, spaces, and quotes.                                                                                                                                                                                                                                                                                | Optional | Dictionary |

Refer to the following for the complete specification for dimensions:

```yaml
models:
  - name: Model name # Required
    semantic_model:
      enabled: true # bool. Required

    columns: # Any column can have either an entity or a dimension, but not both
      - name: my_dimension_column # Required
        description: Column description # Optional
        dimension:
          name: my_dimension # Optional, defaults to column name
          type: categorical # Required. Accepted values: categorical | time
          label: Recommended adding a string that defines the display value in downstream tools # Optional
          description: Same as always # Optional, defaults to the column description if not otherwise specified
```

Refer to the following example to see how dimensions are used in a semantic model:

(Applies to dbt v1.12 and later)

```yaml
models:
  - name: fact_transactions
    semantic_model:
      enabled: true
      name: transactions

    agg_time_dimension: order_date


    columns:
      # --- entities ---
      - name: transaction_column
        entity:
          type: primary
          name: transaction

      # --- dimensions tied 1:1 to columns ---
      - name: another_transaction_column
        granularity: day
        dimension:
          type: time
          name: order_date
          label: "Date of transaction"
          description: "A record for every transaction that takes place. Carts are considered multiple transactions for each SKU."

      - name: type
        dimension:
          type: categorical
          name: type
```

## `derived_semantics` in `dimensions`

Use the `derived_semantics` key in the model YAML entry when you need to derive a dimension definition that is not a direct 1:1 mapping to a single physical column. The `expr` field is required when using `derived_semantics`.

```yaml
models: 
  - name: my_model
    semantic_model:
      enabled: true
    ...
    derived_semantics: 
      dimensions:
        - name: is_bulk
          type: categorical
          expr: "case when quantity > 10 then true else false end" # Required
```

(Applies to dbt v1.12 and later)

Dimensions are bound to the primary entity of the semantic model they are defined in.

MetricFlow requires that all semantic models have a primary entity. This is to guarantee unique dimension names. If your data source doesn't have a primary entity, you need to assign the entity a name using the `entity` key. It doesn't necessarily have to map to a column in that table and assigning the name doesn't affect query generation. We recommend making these "virtual primary entities" unique across your semantic model. See the following example on how to define a primary entity:

```yaml
models:
  - name: bookings_monthly_source
    semantic_model:
      enabled: true

    agg_time_dimension: ds

    columns:
      # Primary entity
      - name: booking_id
        entity:
          type: primary
          name: booking_id

      - name: order_date
        granularity: day
        dimension:
          type: time
          label: "Date"

    metrics:
      - name: bookings_monthly
        type: simple
        agg: sum
```

If your table doesn't have a physical primary key column, you can still declare a primary entity. Set the model’s grain by declaring a `primary_entity`. The new YAML spec supports this and treats the model as being at that entity’s grain.

```yaml
models:
  - name: model_without_pk
    semantic_model:
      enabled: true
    primary_entity: order  # "virtual primary entity"
    columns:
      - name: customer_id
        entity: foreign
```

## Dimensions types

This section further explains the dimension definitions, along with examples. Dimensions have the following types:

* [`derived_semantics` in `dimensions`](#derived_semantics-in-dimensions)

* [Dimensions types](#dimensions-types)

* [Categorical](#categorical)

* [Time](#time)

  * [SCD Type II](#scd-type-ii)

    * [Basic structure](#basic-structure)
    * [Semantic model parameters and keys](#semantic-model-parameters-and-keys)
    * [Implementation](#implementation)
    * [SCD examples](#scd-examples)

## Categorical

Categorical dimensions are used to group metrics by different attributes, features, or characteristics such as product type. They can refer to existing columns in your dbt model or be calculated using a SQL expression with the `expr` parameter. An example of a categorical dimension is `is_bulk_transaction`, which is a group created by applying a case statement to the underlying column `quantity`. This allows users to group or filter the data based on bulk transactions.

(Applies to dbt v1.9 and later)

```yaml
dimensions: 
  - name: is_bulk_transaction
    type: categorical
    expr: case when quantity > 10 then true else false end
    config:
      meta:
        usage: "Filter to identify bulk transactions, like where quantity > 10."
```

## Time

(Applies to dbt v1.12 and later)

Time dimensions no longer use `type_params`.

* For dimensions defined on a column entry, add the column’s `granularity` at the column level.
* For derived dimensions, add `granularity` in the dimension configuration.

A semantic model’s default aggregation time dimension is set with the `agg_time_dimension` property at the model's top level. A metric can override this with its own `agg_time_dimension`. For more information, see [Migrate to the latest YAML spec](./latest-metrics-spec.md).

You can use multiple time groups in separate metrics. For example, the `users_created` metric uses `created_at`, and the `users_deleted` metric uses `deleted_at`:

```bash
# dbt users
dbt sl query --metrics users_created,users_deleted --group-by metric_time__year --order-by metric_time__year

# dbt Core users
mf query --metrics users_created,users_deleted --group-by metric_time__year --order-by metric_time__year
```

You can set `is_partition` for time to define specific time spans.

### is\_partition

Use `is_partition: True` to show that a dimension exists over a specific time window. For example, a date-partitioned dimensional table. When you query metrics from different tables, the Semantic Layer uses this parameter to ensure that the correct dimensional values are joined to measures.

(Applies to dbt v1.12 and later)

```yaml
models:
  - name: orders
    semantic_model:
      enabled: true

    agg_time_dimension: created_at

    columns:
      - name: ts_created
        granularity: day
        dimension:
          type: time
          name: created_at
          label: "Date of creation"
          config:
            meta:
              notes: "Only valid for orders from 2022 onward"
          is_partition: true

      - name: ts_deleted
        granularity: day
        dimension:
          type: time
          name: deleted_at
          label: "Date of deletion"
          is_partition: true

    metrics:
      - name: users_deleted
        type: simple
        agg: sum
        expr: 1
        agg_time_dimension: deleted_at

      - name: users_created
        type: simple
        agg: sum
        expr: 1
```

### time\_granularity

(Applies to dbt v1.12 and later)

`granularity` specifies the grain of a time dimension. MetricFlow will transform the underlying column to the specified granularity. For example, if you add hourly granularity to a time dimension column, MetricFlow will run a `date_trunc` function to convert the timestamp to hourly. You can easily change the time grain at query time and aggregate it to a coarser grain, for example, from hourly to monthly. However, you can't go from a coarser grain to a finer grain (monthly to hourly).

Our supported granularities are:

* nanosecond (Snowflake only)
* microsecond
* millisecond
* second
* minute
* hour
* day
* week
* month
* quarter
* year

Aggregation between metrics with different granularities is possible, with the Semantic Layer returning results at the coarsest granularity by default. For example, when querying two metrics with daily and monthly granularity, the resulting aggregation will be at the monthly level.

```yaml
models:
  - name: your_model_name
    semantic_model:
      enabled: true

    agg_time_dimension: created_at

    columns:
      - name: ts_created
        granularity: hour
        dimension:
          type: time
          name: created_at
          label: "Date of creation"
          is_partition: true

      - name: ts_deleted
        granularity: day
        dimension:
          type: time
          name: deleted_at
          label: "Date of deletion"
          is_partition: true

    metrics:
      - name: users_deleted
        type: simple
        agg: sum
        expr: 1
        agg_time_dimension: deleted_at

      - name: users_created
        type: simple
        agg: sum
        expr: 1
```

### SCD Type II

(Applies to dbt v1.12 and later)

caution

Currently, semantic models with SCD Type II dimensions cannot contain simple metrics.

MetricFlow supports joins against dimensions values in a semantic model built on top of a slowly changing dimension (SCD) Type II table. This is useful when you need a particular metric sliced by a group that changes over time, such as the historical trends of sales by a customer's country.

#### Basic structure

SCD Type II are groups that change values at a coarser time granularity. SCD Type II tables typically have two time columns that indicate the validity period of a dimension: `valid_from` (or `tier_start`) and `valid_to` (or `tier_end`). This creates a range of valid rows with different dimension values for a metric.

MetricFlow associates the metric with the earliest available dimension value within a coarser time window, such as a month. By default, it uses the group valid at the start of this time granularity.

MetricFlow supports the following basic structure of an SCD Type II data platform table:

| entity\_key | dimensions\_1 | dimensions\_2 | ... | dimensions\_x | valid\_from | valid\_to  |
| ----------- | ------------- | ------------- | --- | ------------- | ----------- | ---------- |
| 123         | value\_a      | value\_x      | ... | value\_n      | 2024-01-01  | 2024-06-30 |
| 123         | value\_b      | value\_y      | ... | value\_m      | 2024-07-01  | 2024-12-31 |

* `entity_key` (required): A unique identifier for each row in the table, such as a primary key or another unique identifier specific to the entity.
* `valid_from` (required): Start date timestamp for when the dimension is valid. Use `validity_params: is_start: True` in the semantic model to specify this.
* `valid_to` (required): End date timestamp for when the dimension is valid. Use `validity_params: is_end: True` in the semantic model to specify this.

#### Semantic model parameters and keys

When configuring an SCD Type II table in a semantic model, use `validity_params` to specify the start (`valid_from`) and end (`valid_to`) of the validity window for each dimension.

* `validity_params`: Parameters that define the validity window.

  * `is_start: True`: Indicates the start of the validity period. Displayed as `valid_from` in the SCD table.
  * `is_end: True`: Indicates the end of the validity period. Displayed as `valid_to` in the SCD table.

Here’s an example configuration:

(Applies to dbt v1.12 and later)

```yaml
models:
  - name: tiers
    semantic_model:
      enabled: true
    
    agg_time_dimension: tier_start

    columns:
      - name: start_date
        granularity: day
        dimension:
          type: time # The type of dimension
          name: tier_start #  The name of the dimension.
          label: "Start date of tier" # A readable label for the dimension
          validity_params: # Defines the validity window
            is_start: true # Indicates the start of the validity period

      - name: end_date
        granularity: day
        dimension:
          type: time
          name: tier_end
          label: "End date of tier"
          validity_params:
            is_end: true # Indicates the end of the validity period
```

SCD Type II tables have a specific dimension with a start and end date. To join tables:

* Set the additional [entity `type`](./entities.md#entity-types) parameter to the `natural` key.
* Use a `natural` key as an [entity `type`](./entities.md#entity-types), which means you don't need a `primary` key.
* In most instances, SCD tables don't have a logically usable `primary` key because `natural` keys map to multiple rows.

#### Implementation

Here are some guidelines to follow when implementing SCD Type II tables:

* The SCD table must have `valid_to` and `valid_from` time dimensions, which are logical constructs.
* The `valid_from` and `valid_to` properties must be specified exactly once per SCD table configuration.
* The `valid_from` and `valid_to` properties shouldn't be used or specified on the same time dimension.
* The `valid_from` and `valid_to` time dimensions must cover a non-overlapping period where one row matches each natural key value (meaning they must not overlap and should be distinct).
* We recommend defining the underlying dbt model with [dbt snapshots](./snapshots.md). This supports the SCD Type II table layout and ensures that the table is updated with the latest data.

This is an example of SQL code that shows how a sample metric called `num_events` is joined with versioned dimensions data (stored in a table called `scd_dimensions`) using a primary key made up of the `entity_key` and `timestamp` columns.

```sql
select metric_time, dimensions_1, sum(1) as num_events
from events a
left outer join scd_dimensions b
on 
  a.entity_key = b.entity_key 
  and a.metric_time >= b.valid_from 
  and (a.metric_time < b. valid_to or b.valid_to is null)
group by 1, 2
```

#### SCD examples

The following are examples of how to use SCD Type II tables in a semantic model:

 SCD dimensions for sales tiers and the time length of that tier.

This example shows how to create slowly changing dimensions (SCD) using a semantic model. The SCD table contains information about salespersons' tier and the time length of that tier. Suppose you have the underlying SCD table:

| sales\_person\_id | tier | start\_date | end\_date  |
| ----------------- | ---- | ----------- | ---------- |
| 111               | 1    | 2019-02-03  | 2020-01-05 |
| 111               | 2    | 2020-01-05  | 2048-01-01 |
| 222               | 2    | 2020-03-05  | 2048-01-01 |
| 333               | 2    | 2020-08-19  | 2021-10-22 |
| 333               | 3    | 2021-10-22  | 2048-01-01 |

As mentioned earlier, the `validity_params` include two important arguments that specify the columns in the SCD table that mark the start and end dates (or timestamps) for each tier or dimension:

* `is_start`
* `is_end`

Additionally, the entity is tagged as `natural` to differentiate it from a `primary` entity. In a `primary` entity, each entity value has one row. In contrast, a `natural` entity has one row for each combination of entity value and its validity period.

(Applies to dbt v1.12 and later)

```yaml
models:
  - name: sales_person_tiers
    semantic_model:
      enabled: true

    agg_time_dimension: tier_start

    # You can use a virtual primary entity name
    primary_entity: sales_person

    columns:
      - name: start_date
        granularity: day
        dimension:
          type: time
          name: tier_start
          label: "Start date of tier"
          validity_params:
            is_start: true

      - name: end_date
        granularity: day
        dimension:
          type: time
          name: tier_end
          label: "End date of tier"
          validity_params:
            is_end: true

      - name: tier
        dimension:
          type: categorical
          name: tier

      - name: sales_person_id
        entity:
          type: natural
          name: sales_person
```

The following code represents a separate semantic model that holds a fact table for `transactions`:

(Applies to dbt v1.12 and later)

```yaml
models:
  - name: fact_transactions
    semantic_model:
      enabled: true

    agg_time_dimension: metric_time

    columns:
      - name: transaction_id
        entity:
          type: primary
          name: transaction_id

      - name: customer_id
        entity:
          type: foreign
          name: customer

      - name: product_id
        entity:
          type: foreign
          name: product

      - name: sales_person_id
        entity:
          type: foreign
          name: sales_person

      - name: metric_time
        granularity: day
        dimension:
          type: time
          name: metric_time
          label: "Date of transaction"
          is_partition: true

      - name: sales_geo
        dimension:
          type: categorical
          name: sales_geo

    metrics:
      - name: transactions
        type: simple
        agg: sum
        expr: 1

      - name: gross_sales
        type: simple
        agg: sum
        expr: sales_price

      - name: sales_persons_with_a_sale
        type: simple
        agg: count_distinct
        expr: sales_person_id
```

You can now access the metrics in the `transactions` semantic model organized by the slowly changing dimension of `tier`.

In the sales tier example, For instance, if a salesperson was Tier 1 from 2022-03-01 to 2022-03-12, and gets promoted to Tier 2 from 2022-03-12 onwards, all transactions from March would be categorized under Tier 1 since the dimensions value of Tier 1 comes earlier (and is the default starting point), even though the salesperson was promoted to Tier 2 on 2022-03-12.

 SCD dimensions with sales tiers and group transactions by month when tiers are missing

This example shows how to create slowly changing dimensions (SCD) using a semantic model. The SCD table contains information about salespersons' tier and the time length of that tier. Suppose you have the underlying SCD table:

| sales\_person\_id | tier | start\_date | end\_date  |
| ----------------- | ---- | ----------- | ---------- |
| 111               | 1    | 2019-02-03  | 2020-01-05 |
| 111               | 2    | 2020-01-05  | 2048-01-01 |
| 222               | 2    | 2020-03-05  | 2048-01-01 |
| 333               | 2    | 2020-08-19  | 2021-10-22 |
| 333               | 3    | 2021-10-22  | 2048-01-01 |

In the sales tier example, if sales\_person\_id 456 is Tier 2 from 2022-03-08 onwards, but there is no associated tier level dimension for this person from 2022-03-01 to 2022-03-08, then all transactions associated with sales\_person\_id 456 for the month of March will be grouped under 'NA' since no tier is present prior to Tier 2.

The following command or code represents how to return the count of transactions generated by each sales tier per month:

```bash
# dbt platform users
dbt sl query --metrics transactions --group-by metric_time__month,sales_person__tier --order-by metric_time__month,sales_person__tier

# dbt Core users
mf query --metrics transactions --group-by metric_time__month,sales_person__tier --order-by metric_time__month,sales_person__tier
```

## Was this page helpful?

YesNo

[Privacy policy](https://www.getdbt.com/cloud/privacy-policy)[Create a GitHub issue](https://github.com/dbt-labs/docs.getdbt.com/issues)

This site is protected by reCAPTCHA and the Google [Privacy Policy](https://policies.google.com/privacy) and [Terms of Service](https://policies.google.com/terms) apply.