Tables reference

You can run queries against the tables in your Lightdash project.

Tables 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 .yml file, 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.

version: 2

models:
  - name: users

You can read more about adding Tables to Lightdash here.

Table configuration

You can customize your Tables in your dbt model's YAML file. Here's an example of the properties used in defining a Table:

version: 2

models:
  - name: my_table
    meta:
      label: 'My Custom Table Name'
      order_fields_by: 'label'
      group_label: 'Revenue'
      sql_filter: ${date_dimension} > '2020-01-01'
      joins:
        - join: my_other_table
          sql_on: ${my_table.column_a} = ${my_other_table.column_a}
      required_attributes:
        is_admin: 'true'

Here are all of the properties you can customize:

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 more about the order rules in here
joins	array	Join logic to join other data models to the Table. Read more about joins in here.
metrics	object	Model metrics. Read more about model metrics in here
group_label	string	Group tables in the sidebar. Read more about the group label in here
sql_filter	string	A permanent filter that will always be applied when querying this table directly. Read more about `sql_filter` below
sql_where	string	Alias for `sql_filter`
required_attributes	Object with { user_attribute, value }	Limits access to users with those attributes. Read more about user attributes in here
group_details	Object with { label, description }	Describes the groups for dimensions and metrics
required_filters	Array of {filter field: value}	Dimension filters that will be applied when no other filter on those dimension exists. Read more about `required_filters` below

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/changes to the table (using dbt run). You basically 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 dbt refresh in Lightdash. This will re-sync your dbt project in Lightdash so that any changes you made to your dbt models is shown in Lightdash (e.g. adding a new table).

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 model's dbt .yml file. You can achieve this by setting the order_fields_by parameter in your table's meta tag to index, like this:

version: 2

models:
  - name: my_table
    meta:
      order_fields_by: 'index'
    columns:
      - name: user_id
      - name: product_id
      - name: account_id

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

user_id
product_id
account_id

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 .yml (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 filter

Use sql_filter to permanently filter the table for all queries made against it. sql_filter accepts a sql string that can reference

${TABLE} the current table
${dimension_a} a dimension from the current table
${table_b.dimension_b} a dimension from a joined table

info

sql_filter will only be applied when querying tables directly. For example: If Table A includes another table (Table B) with sql_filter applied to it, then only the sql_filter from Table A will be applied when querying Table A directly. The sql_filter from Table B will not be applied.

info

If you reference a dimension from a joined table in your sql_filter, the referenced table will be joined automatically. For example: If you reference ${table_b.dimension_b} in Table A's sql_filter, then Table B will be joined to Table A automatically.

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.

version: 2
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.

Required Filters

Use required_filters to define filters on Dimensions that will be applied when no other user-defined filter on those Dimensions exists. This allows to enforce a default filter on a Dimension when querying a table. This defined required_filters will be replaced by any user-defined filter on the same Dimension making sure at least one filter is applied to the Dimension.

This is particularly useful when you want to enforce a default filter to save on query time and potential warehouse costs.

info

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.

For example, the defined required_filters on the date Dimension will be in place unless there is another user-defined filter on the date Dimension when querying the orders table, like this:

version: 2

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

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

For example:

version: 2
  - name: orders
    meta:
      required_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.

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"`

Tables reference

Adding Tables to your project​

Table configuration​

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​

Order fields by​

Group label​

SQL filter​

Required attributes​

Required Filters​

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

Available filter types​

Adding Tables to your project

Table configuration

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

Order fields by

Group label

SQL filter

Required attributes

Required Filters

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

Available filter types