Creating tailored tables from a single dbt model

You can use the explores configuration in your Lightdash Semantic Layer to curate multiple ways to query from the same table for different audiences.

What this guide covers

You’ll learn how to use the explores config in Lightdash to define multiple curated table experiences from a single dbt model.

Each explore appears as its own table in the Query from tables list in Lightdash.

---

When to use explores

Use the explores config when you want to create tailored versions of the same table for different teams or use cases. For example:

• Show different columns or joins depending on the audience (e.g. Users + CRM for Sales, Users + product usage for PMs)
• Customize each version of the table to match a specific workflow or department
• Restrict access to certain versions or fields using user attributes (e.g. exec-only views, region-based filters, or hiding PII)

---

Quickstart

1. Start with your base model

   This is your regular dbt model, for example, deals.

   dbt v1.9 and earlier

   models:
     - name: deals
       meta:
         primary_key: deal_id
   

   dbt v1.10+

   models:
     - name: deals
       config:
         meta:
           primary_key: deal_id
   

   Lightdash YAML

   type: model
   name: deals
   
   primary_key: deal_id
   
2. Add an explores section under meta

   Use the explores config to define multiple versions of the table. Each explore has its own label, joins, joined fields, and filters.

   dbt v1.9 and earlier

   models:
     - name: deals
       meta:
         primary_key: deal_id
         label: Deals (Basic)
         description: Basic deals table with no joins
         explores:
           deals_accounts:
             label: Deals w/Accounts
             description: Deals table with accounts joined in, limited account fields included
             joins:
               - join: accounts
                 relationship: many-to-one
                 sql_on: ${deals.account_id} = ${accounts.account_id}
                 fields: [industry, segment, count_accounts]
           deals_closed_won:
             label: Deals (Closed Won)
             description: Deals table filtered to closed-won deals, with full account info
             sql_filter: ${deals.status} = 'closed_won'
             joins:
               - join: accounts
                 relationship: many-to-one
                 sql_on: ${deals.account_id} = ${accounts.account_id}
   

   dbt v1.10+

   models:
     - name: deals
       config:
         meta:
           primary_key: deal_id
           label: Deals (Basic)
           description: Basic deals table with no joins
           explores:
             deals_accounts:
               label: Deals w/Accounts
               description: Deals table with accounts joined in, limited account fields included
               joins:
                 - join: accounts
                   relationship: many-to-one
                   sql_on: ${deals.account_id} = ${accounts.account_id}
                   fields: [industry, segment, count_accounts]
             deals_closed_won:
               label: Deals (Closed Won)
               description: Deals table filtered to closed-won deals, with full account info
               sql_filter: ${deals.status} = 'closed_won'
               joins:
                 - join: accounts
                   relationship: many-to-one
                   sql_on: ${deals.account_id} = ${accounts.account_id}
   

   Lightdash YAML

   type: model
   name: deals
   
   primary_key: deal_id
   label: Deals (Basic)
   description: Basic deals table with no joins
   
   explores:
     deals_accounts:
       label: Deals w/Accounts
       description: Deals table with accounts joined in, limited account fields included
       joins:
         - join: accounts
           relationship: many-to-one
           sql_on: ${deals.account_id} = ${accounts.account_id}
           fields: [industry, segment, count_accounts]
     deals_closed_won:
       label: Deals (Closed Won)
       description: Deals table filtered to closed-won deals, with full account info
       sql_filter: ${deals.status} = 'closed_won'
       joins:
         - join: accounts
           relationship: many-to-one
           sql_on: ${deals.account_id} = ${accounts.account_id}
   
3. Preview the result in Lightdash

   Once you commit and deploy your dbt changes:

   • Go to Query from tables in Lightdash
   • You’ll now see:
     • Deals (Basic)
     • Deals w/Accounts
     • Deals (Closed Won)

   Each shows up as its own table in the UI, but all use the same deals model.

Table config options you can use

Inside each explore definition, you can use the following config options:

Property	Description
label	Display name for the explore in the Lightdash UI. Defaults to a friendly version of the explore name.
description	Description shown for the explore.
group_label	Group the explore under a label in the Query from tables list. Inherits from the base model if not set.
joins	List of joined tables for this explore. If omitted, joins are inherited from the base model. See joins reference.
sql_filter	SQL expression applied as a WHERE clause to every query against this explore.
sql_where	Alias for sql_filter.
case_sensitive	When false, all string filters in this explore are case insensitive. Defaults to true.
default_filters	Default filters applied when users open the explore. See default filters.
additional_dimensions	Explore-scoped custom dimensions that can reference fields from joined tables. See below.

Other model-level options such as required_attributes, metrics, sql_from, default_time_dimension, spotlight, primary_key, and pre_aggregates are configured on the base model and are inherited by all explores. They cannot be overridden per explore.

📚 Read the Tables reference docs for all configuration options

---

Adding custom dimensions to an explore

Use additional_dimensions to define dimensions that are scoped only to a specific explore. This is useful when you need custom dimensions that reference joined tables, which wouldn’t make sense at the model level.

When to use explore-scoped dimensions

• You have multiple explores from the same model with different joins
• You need dimensions that combine fields from the base model and joined tables
• You want to keep explore-specific logic out of the base model

Example

dbt v1.9 and earlier

models:
  - name: orders
    meta:
      primary_key: order_id
      explores:
        orders_with_custom_dims:
          label: Orders with Custom Dimensions
          joins:
            - join: customers
              sql_on: ${orders.customer_id} = ${customers.customer_id}
          additional_dimensions:
            full_name:
              type: string
              sql: "CONCAT(${customers.first_name}, ' ', ${customers.last_name})"
              label: Customer Full Name

dbt v1.10+

models:
  - name: orders
    config:
      meta:
        primary_key: order_id
        explores:
          orders_with_custom_dims:
            label: Orders with Custom Dimensions
            joins:
              - join: customers
                sql_on: ${orders.customer_id} = ${customers.customer_id}
            additional_dimensions:
              full_name:
                type: string
                sql: "CONCAT(${customers.first_name}, ' ', ${customers.last_name})"
                label: Customer Full Name

Lightdash YAML

type: model
name: orders

primary_key: order_id

explores:
  orders_with_custom_dims:
    label: Orders with Custom Dimensions
    joins:
      - join: customers
        sql_on: ${orders.customer_id} = ${customers.customer_id}
    additional_dimensions:
      full_name:
        type: string
        sql: "CONCAT(${customers.first_name}, ' ', ${customers.last_name})"
        label: Customer Full Name

Available properties

Explore-scoped additional_dimensions support the same properties as column-level additional dimensions:

Property	Required	Description
type	Yes	Dimension type: string, number, date, timestamp, or boolean
sql	Yes	SQL expression for the dimension. Can reference fields from the base model and joined tables using ${table.field} syntax
label	No	Display name in Lightdash
description	No	Description shown on hover
hidden	No	Set to true to hide from the UI
format	No	Spreadsheet-style format expression
time_intervals	No	For date/timestamp types, specify which intervals to generate
groups	No	Group the dimension in the sidebar
required_attributes	No	Limit access based on user attributes