Skip to main content

Dimensions reference doc

Dimensions are the columns in your table. They are the "attributes" of your data. For example, user_id in your users table is a dimension.

Dimensions usually match 1:1 with columns in your dbt models (see additional dimensions for counterexamples).


Adding dimensions to your projectโ€‹

Read more about adding dimensions to your project in our docs here.

For a dimension to appear in Lightdash, you just need to declare it in your dbt model's YAML file.

version: 2

models:
- name: my_model
columns:
- name: user_id # will be "User id" in LightDash
description: 'Unique identifier for a user."

Column descriptions in your YAML file are automatically pulled into Lightdash and you can spot them if you hover over the dimension name ๐Ÿ‘€

screenshot-column-descriptions

Dimension configurationโ€‹

To customize the dimension, you can do it in your dbt model's YAML file.

If you want to declare multiple dimensions based on the same column, check additional dimensions section.

version: 2

models:
- name: sales_stats
meta:
group_details:
finance:
label: Finance
description: Finance-related fields.
joins:
- join: web_sessions
sql_on: ${web_sessions.date} = ${sales_stats.date}
columns:
- name: revenue_gbp_total_est
description: 'Total estimated revenue in GBP based on forecasting done by the finance team.'
meta:
dimension:
type: string
label: 'Total revenue' # this is the label you'll see in Lightdash
description: 'My custom description' # you can override the description you'll see in Lightdash here
sql: 'IF(${TABLE}.revenue_gbp_total_est = NULL, 0, ${registered_user_email})' # custom SQL applied to the column from dbt used to define the dimension
hidden: false
round: 2
format: 'gbp'
groups: ['finance']
- name: forecast_date
description: 'Date of the forecasting.'
meta:
dimension:
type: date
time_intervals: ['DAY', 'WEEK', 'MONTH', 'QUARTER'] # not required: the default time intervals for dates are `['DAY', 'WEEK', 'MONTH', 'YEAR']`
urls:
- label: 'Open in forecasting tool'
url: 'https://finance.com/forceasts/weeks/${ value.raw }'
- label: Open in Google Calendar
url: 'https://calendar.google.com/calendar/u/0/r/day/${ value.formatted |split: "-" |join: "/"}'
required_attributes:
is_admin: 'true'
- name: date
meta:
dimension:
type: date
additional_dimensions:
dimension_name_one:
type: number
label: 'Day of Week'
sql: 'day_of_week(${date})'
dimension_name_two:
type: boolean
label: 'Weekday or Weekend'
sql: "case when day_of_week(${date}) < 5 then 'weekday' else 'weekend' end"

All the properties you can customize:

PropertyRequiredValueDescription
labelNostringCustom label. If you set this property, this is what you'll see in Lightdash instead of the dimension name.
typeNoDimension typeThe dimension type is automatically pulled from your table schemas in Lightdash but you can override the type using this property.
descriptionNostringDescription of the dimension in Lightdash. You can use this to override the description you have for the dimension in dbt.
sqlNostringCustom SQL applied to the column used to define the dimension.
time_intervalsNo'default' or OFF or an array[] containing elements of date, numeric or string options'default' (or not setting the time_intervals property) will be converted into ['DAY', 'WEEK', 'MONTH', 'YEAR'] for dates and ['RAW', 'DAY', 'WEEK', 'MONTH', 'YEAR'] for timestamps; if you want no time intervals set 'OFF'.
hiddenNobooleanIf set to true, the dimension is hidden from Lightdash. By default, this is set to false if you don't include this property.
roundNonumberRounds a number to a specified number of digits
formatNostringThis option will format the output value on the result table and CSV export. Currently supports one of the following: ['km', 'mi', 'usd', 'gbp', 'eur', 'percent', 'id']
compactNostringThis option will compact the number value (e.g. 1,500 to 1.50K). Currently supports one of the following: ['thousands', 'millions', 'billions', 'trillions']
groupsNostring or string[]If you set this property, the dimension will be grouped in the sidebar with other dimensions with the same group label.
urlsNoArray of { url, label }Adding urls to a dimension allows your users to click dimension values in the UI and take actions, like opening an external tool with a url, or open at a website. You can use liquid templates to customise the link based on the value of the dimension.
required_attributesNoObject with { user_attribute, value }Limits access to users with those attributes
colorsNoObject with { value, color }Color for the values in the chart

Typeโ€‹

The types of your dimensions are pulled from your data warehouse, automatically. You can override these types using the type meta tag in your .yml file. If you run lightdash generate to generate your .yml files, then Lightdash will add the type from your data warehouse to your .yml files automatically.

- name: user_created_date
meta:
dimension:
type: date

We currently support these dimension types:

Dimension Types
string
number
timestamp
date
boolean

Compact valuesโ€‹

You can compact values in your YAML. For example, if I wanted all of my revenue values to be shown in thousands (e.g. 1,500 appears as 1.50K), then I would write something like this in my .yml:

    version: 2
models:
- name: sales
columns:
- name: revenue
meta:
dimension:
compact: thousands # You can also use 'K'
ValueAliasExample output
thousands"K" and "thousand"1K
millions"M" and "million"1M
billions"B" and "billion"1B
trillions"T" and "trillion"1T

Time intervalsโ€‹

Lightdash automatically adds intervals for dimensions that are timestamps or dates, so you don't have to!

For example, here we have the timestamp dimension created defined in our dbt project:

- name: created
description: 'Timestamp when the user was created.'

Lightdash breaks this out into the default intervals automatically. So, this is how created appears in our Lightdash project:

screenshot-default-intervals

By default, the time intervals we use are:โ€‹

Date: ['DAY', 'WEEK', 'MONTH', 'YEAR']

Timestamp: ['RAW', 'DAY', 'WEEK', 'MONTH', 'YEAR']

To turn off time intervals for a dimension, set time_intervals: OFFโ€‹

If you want to turn off time intervals for a dimension, you can simply set the time_intervals property to OFF.

In this example, created would now appear as a single, timestamp dimension without a drop-down list of time intervals in Lightdash:

- name: created
description: 'Timestamp when the user was created.'
meta:
dimension:
time_intervals: OFF

screenshot-intervals-off

To customize the time intervals for a dimension, you can use the time_intervals parameter.โ€‹

If you specify time intervals manually, then this overrides the default time intervals used by Lightdash.

- name: created
description: 'Timestamp when the user was created.'
meta:
dimension:
time_intervals: ['DAY', 'WEEK', 'MONTH', 'YEAR', 'QUARTER']

You can see all of the interval options for date and timestamp fields below.

Date optionsโ€‹

OptionDescriptionTypeDisplayed valueNotes
RAWOriginal valueDate / DateTime2019-01-01 / 2019-01-01, 09:30:30:300 UTC
YEARDate truncated to the nearest yearDate2019
QUARTERDate truncated to the nearest quarterDate2019-Q1
MONTHDate truncated to the nearest monthDate2019-01-01
WEEKDate truncated to the nearest weekDate2019-01-01The start of the week depends on your warehouse configuration
DAYDate truncated to the nearest dayDate2019-01-01
HOURDatetime truncated to the nearest hourDateTime2019-01-01, 09 UTC
MINUTEDatetime truncated to the nearest minuteDateTime2019-01-01, 09:30 UTC
SECONDDatetime truncated to the nearest secondDateTime2019-01-01, 09:30:30 UTC
MILLISECONDDatetime truncated to the nearest millisecondDateTime2019-01-01, 09:30:30:300 UTC

Numeric optionsโ€‹

OptionDescriptionTypeDisplayed valueNotes
DAY_OF_WEEK_INDEXIndex of the day of the weekNumber0The value range and start of the week depends on your warehouse configuration
DAY_OF_MONTH_NUMDay of the monthNumber21
DAY_OF_YEAR_NUMDay of the yearNumber127
WEEK_NUMWeek numberNumber37
MONTH_NUMMonth numberNumber7
QUARTER_NUMQuarter numberNumber3
YEAR_NUMYear numberNumber2019
MINUTE_OF_HOUR_NUMMinute numberNumber50
HOUR_OF_DAY_NUMHour numberNumber22

String optionsโ€‹

OptionDescriptionTypeDisplayed value
DAY_OF_WEEK_NAMEDay of the weekStringMonday
MONTH_NAMEMonth nameStringMarch
QUARTER_NAMEQuarter nameStringQ3

Formatโ€‹

You can use the format parameter to have your dimensions show in a particular format in Lightdash.

- name: revenue
description: 'Timestamp when the user was created.'
meta:
dimension:
format: 'usd'
metrics:
total_revenue:
type: sum
format: 'usd'

These are the options:

OptionDescriptionRaw valueDisplayed value
kmAdds the suffix km to your value1010 km
miAdds the suffix mile to your value1010 mi
usdAdds the $ symbol to your number value10$10.00
gbpAdds the ยฃ symbol to your number value10ยฃ10.00
eurAdds the โ‚ฌ symbol to your number value10โ‚ฌ10.00
percentAdds the % symbol and multiplies your value by 1000.1%10
idRemoves commas and spaces from number or string types so that they appear like IDs.12,389,57212389572

Groupsโ€‹

You can group your dimensions and metrics in the sidebar using the groups parameter.

To do this, you need to set up group_details in the model's configuration. Then, you can use these groups to organize metrics and dimensions. You can create nested groups up to 2 levels.

version: 2
models:
- name: baskets
meta:
group_details:
product_details:
label: Product Details
description: 'Fields that have information about the products in the basket.'
item_details:
label: Item Details
description: 'Fields that have information about the items in the basket.'

columns:
- name: basket_item_id
description: 'ID for the product item within the basket.'
meta:
dimension:
groups: ['product_details', 'item_details'] # this would add the dimension to a nested group: `product details` --> `item details`
- name: product_name
description: 'Full name of the product.'
meta:
dimension:
label: 'Product name'
groups: ['product_details'] # this would add the dimension under the group label: `product_details`

This example would look like this in the sidebar:

URLsโ€‹

Lightdash users can interact with dimension values by clicking on them. By adding custom urls you can configure the actions available to your users. Like linking to external tools, or taking actions in other tools.

In the example below, users can click on a company name and open a corresponding record in their CRM or search for the company in google or open that company's Slack channel.

columns:
- name: company_name
label: Registered trading name of the company
meta:
dimension:
urls:
- label: Search for company in Google
url: 'https://google.com/search?${ value.formatted | url_encode }'
- label: Open in CRM
url: 'https://mycrm.com/companies/${ row.company.company_id.raw | url_encode }'

The ${ value.formatted } will be replaced with the value of the company name in the Lightdash UI at query run time. The ${ row.company.company_id.raw } will be replaced with the value of the company id in the Lightdash UI at query run time. The action will be disabled if the column "company_id" from table "company" is not part of the query.

You can reference values from other columns in your URLsโ€‹

You can reference another dimension from your table in your URL. For these URLs to work, the other column you've referenced needs to be included in your results table. For example, say Iโ€™ve added a URL to company_name and it uses the field customer_id:

columns:
- name: company_name
label: Registered trading name of the company
meta:
dimension:
urls:
- label: "Open company"
url: "https://example.com/company/${row.customers.customer_id.raw | url_encode }"

This URL will only work if I have customer_id included in my results table.

Liquid Templatingโ€‹

Use templates to configure the url values depending on the query, this allows your urls to depend on the results of queries.

Available liquid tags

TagDescription
${ value.formatted }The exact value of the dimension as seen in the Lightdash UI. For example "$1,427.20"
${ value.raw }The raw value of the dimension returned from the underlying SQL query. For example "1427.2 "
${ row.table_name.column_name.formatted }The exact value of the column as seen in the Lightdash UI. For example "$1,427.20"
${ row.table_name.column_name.raw }The raw value of the dimension returned from the underlying SQL query. For example "1427.2 "

Available liquid filters

Filters can be used to make small transformations of your values:

FilterDescriptionExample usage
url_encodeEncode a string as url safe, for example it replaces spaces with %20`${value.formatted \ url_encode }`
downcaseConvert string to all lowercase`${value.formatted \ downcase }`
appendAppend a string to another`${value.formatted \ append: ".html"}`

There are many more filters available in the Liquid documentation.

Required attributesโ€‹

Lightdash can use user attributes to limit some dimensions to some users.

In the example below, only users with is_admin attribute true can use the salary dimension on user table. Users without access to this dimension will not see it on the explore page.

columns:
- name:
description: User name
- salary:
description: User salary
meta:
dimension:
required_attributes:
is_admin: "true"

If a user without access to this dimension runs a query that contains this dimension, they will get a Forbidden error.

Current limitationsโ€‹

Lightdash dimensions and custom metrics are protected by this feature, however, it is possible to write custom SQL to bypass this filter, for example:

  • Developers and admins running SQL queries on SQL runner.
  • Custom SQL or subqueries on table calculations
info

Scheduler deliveries will run against the user who created the scheduled delivery, be careful when sharing required attributes with other users.

Colorsโ€‹

You can predefine colors for your string type dimensions, these colors will be used instead of your default organization colors for the right value when you use a grouped bar chart or a pie chart.

  - name: status
description: "{{ doc(\"orders_status\") }}"
meta:
dimension:
colors:
"placed": "#e6fa0f"
"completed": "#558B2F"
"shipped": "#29B6F6"
"return_pending": "#FF6F00"
"returned": "#E91E63"
info

We recommend using #HEX colors, other color types like rgba,rgba or color name (eg: orange) are also supported on charts, but they are not yet supported on the chart config.

info

You can manually override these dimension colors by going into the chart config and manually picking a color for that serie.

These colors will also take precedence over the organization color palette.

Using special characters or capital letters in your column namesโ€‹

If you use special characters on your column names, you might get errors when using those columns on explore. For example, having a column named Status with capital S on a table named orders in postgres throws the following error:

column orders.status does not exist

To fix this, we can add the quoted column to our sql meta tag on dimensions

- name: status
meta:
dimension:
type: string
sql: '"orders"."Status"' # you can also use '${TABLE}."Status"'

This will quote the Status columns on the SQL query

SELECT
"orders".order_id AS "orders_order_id",
"orders"."Status" AS "orders_status"
FROM "postgres"."jaffle"."orders" AS "orders"

Additional dimensionsโ€‹

Additional dimensions let you define multiple dimensions off of a single column from your dbt model. This is useful when adding different formatting to a column, comparing or combining columns, parsing JSON columns, or creating persisted groups/buckets based off of a column.

A "normal" dimension is a column created in your .sql file in dbt that is written to your data warehouse. An additional dimension is not included in your dbt .sql file, so it's not written to your data warehouse. When used in Lightdash, it just adds the dimension definition to your SQL query (so it's "created" at runtime).

All the dimension configuration, beside time_intervals, are available for additional dimensions. You can also use additional dimensions when defining metrics.

info

Additional dimensions names need to be unique in the model.

Adding different formattingโ€‹

columns:
- name: revenue
meta:
dimension:
type: number
additional_dimensions:
revenue_in_thousands:
type: number
compact: thousands
revenue_in_millions:
type: number
compact: million

Comparing or combining columnsโ€‹

When defining additional dimensions, you can reference other dimensions, even from joined tables (organizations is a joined table in the example below).

columns:
- name: created_date
meta:
dimension:
type: date
additional_dimensions:
days_to_first_query_run:
type: number
description: 'Number of days between a user being created and their first query run.'
sql: ${first_query_date} - ${created_date}
days_to_organization_first_payment:
type: number
description: 'Number of days between a user being created and their organization making its first payment. This will be negative for users who joined after the first payment.'
sql: ${created_date} - ${organizations.first_payment_date}

Parsing JSON columnsโ€‹

Usually you'll want to add hidden:true for the main JSON dimension since raw JSON is not useful in charts.

columns:
- name: metadata # this is a jsonb column with metadata
meta:
dimension:
hidden: true
additional_dimensions:
version:
type: number
sql: JSON_VALUE(${metadata}, '$.version') # custom SQL applied to get the "version" value inside metadata

Using additional dimensions in metricsโ€‹

To define metrics based on additional dimensions, you need to add them to the model's meta metrics, or use custom SQL in defining them under the column's meta.

version: 2
models:
- name: users
meta:
metrics:
highest_version_model_metric_example:
type: max
sql: ${version}
columns:
- name: metadata
meta:
dimension:
hidden: true
additional_dimensions:
version:
type: number
sql: JSON_VALUE(${metadata}, '$.version')
metrics:
highest_version:
type: max
sql: ${version}