Tables reference

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'
      primary_key: column_a
      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:

​

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:

1. 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.
2. 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 (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.

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.

​

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

​

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

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

version: 2

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