You can run queries against the tables in your Lightdash project.
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.
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_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. |
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:
dbt run
to update the table from the data model you just changed.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: "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:
order_fields_by
parameter:
order_fields_by
is set to label
, which means that your fields will appear in the table listed alphabetically.meta
tag, under a dimension’s meta
), we force the following order on metrics if you set order_fields_by
to index
:
meta
meta
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:
Sales
table in Lightdash will always have a filter for sales_region = 'EMEA'
in their compiled SQL
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:
sql_filter
will only be applied when querying tables directly.sql_filter
applied to itsql_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)sql_filter
, the referenced table will always be joined in your queries.sql_filter: ${TABLE}.sales_region = 'EMEA' OR ${table_b}.sales_region IS NULL
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.
Forbidden
error.
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.
AND
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:
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” |
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.
accounts
table and show all Accounts fields, but only people with the is_exec
user attribute can see itaccounts
table and only show Industry and Segment dimensions, it has no access restrictionsexplores
tag.
Read this guide to learn more about explores