Tables reference

Tables in Lightdash are built from dbt models (either one, or many joined together).

Adding Tables to your project

Tables come from dbt models that have been defined in your dbt project’s schema.yml files. If your dbt model has been defined in a YAML file, and has at least one column documented, it will appear in Lightdash as a table. For example, if we had this in our schema.yml files in dbt, we’d see a Table called Users in Lightdash.

models:
  - name: users
    columns:
      - name: user_id
        description: "The unique identified for each user"

You can read more about adding Tables to Lightdash here.

Table configuration

You can customize how Tables look in Lightdash by adding configuration to your YAML file. Here’s an example of most the properties you can use when defining a Table:

models:
  - name: users
    meta:
      label: 'App Users'
      order_fields_by: 'label'
      group_label: 'Mobile App'
      sql_filter: ${date_dimension} >= '2025-01-01'
      primary_key: user_id
      sets:
  	    event_fields: 
    	  fields:
            - user_id
            - event_type
      joins:
        - join: events
          sql_on: ${users.user_id} = ${events.user_id}
          fields: [event_fields*]
          relationship: one-to-many
      required_attributes:
        product_team: 'Mobile'
      explores:
        users_pii:
          required_attributes:
            has_pii_access: true 
          joins: 
            - join: users_pii
              sql_on: ${users.user_id} = ${users_pii.user_id}
              relationship: one-to-one

Table properties

Property	Value	Note
label	string	Custom label. This is what you’ll see in Lightdash instead of the Table name.
order_fields_by	`index` or `label`	How the fields will be sorted in the sidebar. Read about the order rules.
joins	array	Join logic to join other data models to the Table. Read about joins.
metrics	object	Model metrics. Read about model metrics
group_label	string	Group tables in the sidebar. Read about the group label.
sql_from	string	Overrides dbt model relation_name
sql_filter	string	A permanent filter that will always be applied when querying this table directly. `Read about sql_filter.`
sql_where	string	Alias for `sql_filter`
required_attributes	object	Limits access to users with those attributes. Read about user attributes
group_details	object	Describes the groups for dimensions and metrics
default_filters	array	Dimension filters that will be applied when no other filter on those dimension exists. `Read about default_filters`
explores	object	Allows you to define multiple table explores in Lightdash from a single dbt model.
parameters	object	Model-level parameters that can be referenced in SQL properties. Read about parameters
sets	object	Allows you to define a reference to a collection of fields. This reference can be re-used throughout the model.

Adding a new dbt model

If you’ve added a new dbt model to your project, you need to do dbt run + dbt refresh before it will appear in Lightdash. Lightdash gets information about your data models from dbt. But it gets information about the data generated by those data models from your data warehouse. This means that if you add a new dbt model to your project or update a model so that you’re making changes to the table it generates, then you need to do two things before your changes will appear in Lightdash:

Materialize the new table or changes using dbt run. You want the data in your data warehouse to be the new table you’re expecting. So you need to do dbt run to update the table from the data model you just changed.
Click Refresh dbt in Lightdash or run lightdash refresh in the CLI. This will re-sync your dbt project in Lightdash so that changes you made to your dbt models are shown in Lightdash (e.g. adding a new table or column).

Order fields by

By default, the fields in your sidebar for any table will appear alphabetically (order_fields_by: "label"). Sometimes, you might not want your fields to appear alphabetically, but instead, in the same order as they are in your YAML file. You can achieve this by setting the order_fields_by parameter in your table’s meta tag to index, like this:

models:
  - name: users
    meta:
      order_fields_by: 'index'
    columns:
      - name: user_id
      - name: user_name
      - name: user_email

So, in the example above, the fields in the sidebar for “My Table” would appear in the order:

user_id
user_name
user_email

Instead of being listed alphabetically. Here are some other things worth mentioning about the order_fields_by parameter:

By default, order_fields_by is set to label, which means that your fields will appear in the table listed alphabetically.
Since metrics can be declared in multiple places within your YAML (as a dbt metric, in the model meta tag, under a dimension’s meta), we force the following order on metrics if you set order_fields_by to index:
- dbt metrics appear first
- then, metrics defined in the model’s meta
- then, metrics defined in the dimensions’ meta
Group labels inherit the index of the first dimension that use them.

Group label

If you set this property, the table will be grouped in the sidebar with other tables with the same group label. The tables in your sidebar will appear in the following order:

Group labels appear first, alphabetically
Ungrouped tables appear after the grouped tables in the sidebar, alphabetically
Tables within the groups are also ordered alphabetically

SQL from

sql_from is a configuration option that overrides the default dbt model relation name when generating SQL queries in Lightdash. For example, you might use this if you want your Lightdash explore to query from a specific materialized view, a different schema, or include additional SQL logic in the FROM clause while still maintaining the dbt model structure for dimensions and metrics.

models:
  - name: sales
    meta:
      sql_from: my_schema.my_sales_view

SQL filter (row-level security)

sql_filter adds a filter to the table that cannot be removed in Lightdash. It is automatically added to the compiled SQL when running queries. For example:

models:
  - name: sales
    meta:
      sql_filter: ${TABLE}.sales_region = 'EMEA'

Any queries that I run using the Sales table in Lightdash will always have a filter for sales_region = 'EMEA' in their compiled SQL

select [...]
from lightdash.prod.sales
where sales_region = 'EMEA'

Row-level security using user attributes

Using sql_filter with user attributes allows you to set up row-level security in your tables. You can reference user attributes in your sql_filter using ${lightdash.attributes.my_attribute_name} For example:

models:
  - name: sales
    meta:
      sql_filter: ${TABLE}.sales_region IN (${lightdash.attributes.sales_region})

`sql_filter` will only be applied when querying tables directly.

For example:

Table A is joined to Table B
Table B has a sql_filter applied to it
A user queries Table A and adds a field from the joined table (Table B) to their query
the sql_filter from Table B will not be applied to the query (you would need to add this as a sql_filter to Table A directly for it to apply)

If you reference a dimension from a joined table in your `sql_filter`, the referenced table will always be joined in your queries.

For example:

You have Table A which is joined to Table B
In Table A, you’ve added a sql_filter: ${TABLE}.sales_region = 'EMEA' OR ${table_b}.sales_region IS NULL
Table B will always be joined to Table A in your queries (even if there are no fields from Table B selected in your results table)

Required attributes

Lightdash can use user attributes to limit some tables to some users. In the example below, only users with is_admin attribute true can use the payments table. Users without access to this table will not see it on the tables page or the explore page when joined to other tables.

models:
  - name: payments
    meta:
      required_attributes:
        is_admin: "true"

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

Default filters

Use default_filters to define filters on Dimensions that will be applied when no other user-defined filter on those Dimensions exists. Default filters will show apply to tables on load and can be populated with a pre-determined value. User them to suggest to users the kind of filters they might want to consider, or provide a default filtered view of a table that can be changed if needed. An optional required flag can be added - in which case the filter cannot be removed. This can be particulalry useful if you have a large table and want to force users to filter on a partitioned date. Below you can see there is a default filter with the optional required flag, that will have show the last 14 days of data by default.

models:
  - name: orders
    meta:
      default_filters:
        - date: 'inThePast 14 days'
		  required: true
    columns:
      - name: date
        description: 'Order date'
        meta:
          dimension:
            type: date

A required filter’s field reference can’t be changed, but its operator (is, is not, etc.) and value can be changed when querying the table from the UI.

If you have many filters in your list, they will be joined using `AND`

  - name: orders
    meta:
      default_filters:
        - date: 'inThePast 14 days'
        - status: "completed"
    columns:
      - name: date
        description: 'Order date'
        meta:
          dimension:
            type: date
      - name: status
        description: 'Order status - completed, pending, cancelled'
        meta:
          dimension:
            type: string

In the example above, the orders table will have a default filter of date in the past 14 days and status completed. Both can be removed by the user, as the required flag is not present. Note that we do also support a legacy structure for defining required filters, see below:

models:
  - name: orders
    meta:
      required_filters:
        - date: 'inThePast 14 days'
    columns:
      - name: date
        description: 'Order date'
        meta:
          dimension:
            type: date

Defining primary keys

You can specify a primary key for your model to uniquely identify each row. This is important for tables as it helps Lightdash understand the relationships between tables and prevent data duplication, especially when dealing with SQL fanouts in joins. The primary key can be defined in two ways:

Single column primary key

If your table has a single column that uniquely identifies each row, you can define it as a string:

models:
  - name: users
    meta:
      primary_key: user_id

Complex primary key

If your table requires multiple columns to uniquely identify each row, you can define the primary key as an array of strings:

models:
  - name: order_items
    meta:
      primary_key: [order_id, item_id]

Using a properly defined primary key helps Lightdash optimize queries and provide accurate results when working with joined tables. It’s especially important for preventing metric inflation in SQL joins where duplicate rows can lead to incorrect aggregations.

Available filter types

Type	Example (in English)	Example (as code)
is	User name is equal to katie	user_name: “katie”
is not	User name is not equal to katie	user_name: “!katie”
contains	User name contains katie	user_name: “%katie%“
does not contain	User name does not contain katie	user_name: ”!%katie%“
starts with	User name starts with katie	user_name: “katie%“
ends with	User name ends with katie	user_name: “%katie”
is greater than (number)	Number of orders is greater than 4	num_orders: ”> 4”
in the past (date) (interval)	Date is before x (days / months / years)	date: “inThePast 14 months”
in the next (date) (interval)	Date is after x (days / months / years)	date: “inTheNext 14 days”
is greater than or equal to	Number of orders is greater than or equal to 4	num_orders: ”>= 4”
is less than	Number of orders is less than 4	num_orders: ”< 4”
is less than or equal to	Number of orders is less than or equal to 4	num_orders: ”<= 4”
is null	Status is NULL	status: “null”
is not null	Status is not NULL	status: “!null”
is [boolean]	Is complete is true	is_complete: “true”
is not [boolean]	Is complete is false or null	is_complete: “!true”

Parameters configuration

The parameters section allows you to define model-level parameters that can be referenced in various parts of your model’s SQL properties. These parameters are scoped to the specific model where they’re defined.

models:
  - name: orders
    meta:
      parameters:
        region:
          label: "Region"
          description: "Filter data by region"
          options:
            - "EMEA"
            - "AMER"
            - "APAC"
          default: ["EMEA", "AMER"]
          multiple: true
        min_order_value:
          label: "Minimum Order Value"
          description: "Filter for minimum order value"
          type: "number"
          options:
            - 100
            - 500
            - 1000
          default: 500
        department:
          label: "Department"
          description: "Filter data by department"
          options_from_dimension:
            model: "employees"
            dimension: "department"

Each parameter is defined as a key-value pair where the key is the parameter name (must be alphanumeric with underscores or hyphens) and the value is an object with the following properties:

Property	Required	Value	Description
`label`	Yes	string	A user-friendly label for the parameter as it will be displayed in the UI.
`description`	No	string	A description of the parameter.
`type`	No	”string” or “number”	The type of the parameter. Defaults to “string” if not specified.
`options`	No	Array of strings or numbers	A list of possible values for the parameter.
`default`	No	string, number, or Array of strings/numbers	The default value(s) for the parameter.
`multiple`	No	boolean	Whether the parameter input will be a multi-select.
`allow_custom_values`	No	boolean	Whether users can input custom values beyond predefined options.
`options_from_dimension`	No	Object	Get parameter options from a dimension in a model. Requires `model` and `dimension` arguments (see below).

If using options_from_dimension, the object requires the following properties:

Property	Required	Value	Description
`model`	Yes	string	The model containing the dimension.
`dimension`	Yes	string	The dimension to get options from.

Using model-level parameters

Model-level parameters are referenced with the model name included in the syntax: ${lightdash.parameters.model_name.parameter_name} or the shorter alias ${ld.parameters.model_name.parameter_name}. For example, to reference a parameter named region from the current model:

${lightdash.parameters.orders.region}

Or using the shorter alias:

${ld.parameters.orders.region}

Using parameters from joined tables

You can also reference model-level parameters from joined tables. This is particularly useful when you want to use parameters defined in one model while working in another:

models:
  - name: orders
    meta:
      joins:
        - join: customers
          sql_on: |
            ${orders.customer_id} = ${customers.customer_id}
            AND ${customers.status} = ${ld.parameters.customers.customer_status}

In this example, the join condition references a model-level parameter customer_status that is defined in the customers model, even though we’re configuring the orders model. See the Parameters guide for more examples and information on how to use parameters.

Explores

You can define multiple table explores from a single table using the explores config. This will allow you to list the same dbt model multiple times in the list of Tables in Lightdash. You can use it to show different versions of a table, join different tables to the base table, customize table visibility, etc. Below is an advanced example of using Explores. This will result in three total tables using the deals model at the base.

Deals will not have any joins or limitations
Deals w/Accounts will join to the accounts table and show all Accounts fields, but only people with the is_exec user attribute can see it
Deals w/Accounts (no Names) will join to the accounts table and only show Industry and Segment dimensions, it has no access restrictions

models:
- name: deals
  meta:
    primary_key: deal_id
    explores:
      deals_accounts:
        required_attributes:
          is_exec: "true"
        label: 'Deals w/Accounts'
        description: The deals table with the Accounts table details included
        joins:
        - join: accounts
          relationship: many-to-one
          sql_on: ${deals.account_id} = ${accounts.account_id}
      deals_accounts_no_names:
        label: 'Deals w/Accounts (no Names)'
        description: The deals table with the Accounts table details included
        joins:
        - join: accounts
          relationship: many-to-one
          sql_on: ${deals.account_id} = ${accounts.account_id}
          fields: [industry, segment, unique_accounts, unique_smb_accounts, unique_midmarket_accounts, unique_enterprise_accounts]

All the table configuration options can be used under the explores tag. Read this guide to learn more about explores

Sets

Sometimes you may find that you’re redeclaring the same set of fields for things like joins and show_underlying_values. In this case, you can define a set. A set allows you to associate those fields to a single value. That reference can then be used any place you would normally define fields.

# Define a set
sets: 
  my_set:
    fields: 
      - user_id
      - user_name
      - created_at

# Referencing the set
fields: [my_set*]

# Lightdash resolves to
fields: [user_id, user_name, created_at]

Expand

The expand operator (ex. my_set*) tells Lightdash to look up the set being referenced and resolve it to the associated collection of fields. When Lightdash compiles your model, it will replace set reference names with the actual fields.

models:
  - name: orders_model
    meta:
      sets:
        my_user_fields:
          fields:
            - user_id
            - user_name
    columns:
      - name: revenue
        meta:
          metrics:
            sum_revenue:
              type: sum
              show_underlying_values:
                - my_user_fields* # Reference to the set we defined

Exclusions

In the instance where you want to leverage some, but not all fields in a set, you can use the excludes operator (ex. -field_name). The exclusion needs to be used in conjunction with an expand operator. This tells Lightdash to expand a given set while omitting any field name using the exclusion operator.

models:
  - name: orders_model
    meta:
      sets:
        my_user_fields:
          fields:
            - user_id
            - user_name
    columns:
      - name: revenue
        meta:
          metrics:
            sum_revenue:
              type: sum
              show_underlying_values:
                - my_user_fields* 
                - -user_id # Expand `my_user_fields` and exclude `user_id`

Joins

Within a model, you may join with other model tables. The sets you define can reference those joined fields as well! Use dot notation to reference a joined table:

models:
  - name: purchases
    columns: <...>
    meta:
      sets:
  	    revenue_fields: 
    	  fields:
            - purchase_date
            - purchase_amount
            - user.user_name # Referencing the joined table
      joins:
        - join: user
          sql_on: ${users.user_id} = ${purchases.user_id}
          relationship: one-to-many
      metrics: 
        revenue:
          type: sum
          show_underlying_values:
            - revenue_fields*

Remember, to use a fields from another table in your set, you need ensure it is joined with the model defining the set.

Introduction

Explore and analyze

Build your semantic layer

Workspace and user management

Integrations

Embedding

Self-hosting and deployment

Contact

Adding Tables to your project

Table configuration

Table properties

Adding a new dbt model

Order fields by

Group label

SQL from

SQL filter (row-level security)

Row-level security using user attributes

`sql_filter` will only be applied when querying tables directly.

If you reference a dimension from a joined table in your `sql_filter`, the referenced table will always be joined in your queries.

Required attributes

Default filters

If you have many filters in your list, they will be joined using `AND`

Defining primary keys

Single column primary key

Complex primary key

Available filter types

Parameters configuration

Using model-level parameters

Using parameters from joined tables

Explores

Sets

Expand

Exclusions

Joins

Introduction

Explore and analyze

Build your semantic layer

Workspace and user management

Integrations

Embedding

Self-hosting and deployment

Contact

​Adding Tables to your project

​Table configuration

​Table properties

​Adding a new dbt model

​Order fields by

​Group label

​SQL from

​SQL filter (row-level security)

Row-level security using user attributes

sql_filter will only be applied when querying tables directly.

If you reference a dimension from a joined table in your sql_filter, the referenced table will always be joined in your queries.

​Required attributes

​Default filters

​If you have many filters in your list, they will be joined using AND

​Defining primary keys

​Single column primary key

​Complex primary key

​Available filter types

​Parameters configuration

​Using model-level parameters

​Using parameters from joined tables

​Explores

​Sets

​Expand

​Exclusions

​Joins

Adding Tables to your project

Table configuration

Table properties

Adding a new dbt model

Order fields by

Group label

SQL from

SQL filter (row-level security)

`sql_filter` will only be applied when querying tables directly.

If you reference a dimension from a joined table in your `sql_filter`, the referenced table will always be joined in your queries.

Required attributes

Default filters

If you have many filters in your list, they will be joined using `AND`

Defining primary keys

Single column primary key

Complex primary key

Available filter types

Parameters configuration

Using model-level parameters

Using parameters from joined tables

Explores

Sets

Expand

Exclusions

Joins