Skip to main content

Adding and managing Tables in your Lightdash project

Tables are the things that you see in the Tables list when you open Lightdash. They're the foundation of your data exploration, the backbone to figuring out your unknowns, the art behind your charts...basically, they're pretty important.

So, we wanted to go through how to actually add these Tables to your Lightdash project, and once they're in there, how you can configure them to make the most of them.

How do I add tables to my Lightdash project?​

The beauty of Lightdash is that we're pretty well synced with your dbt project. So, in Lightdash, Tables actually come from dbt models that have been defined in your dbt project's .yml files.

If your dbt model has been defined in a .yml file, it will appear in Lightdash as a Table.

info

Not sure what a .yml file is? Make sure to check out dbt's docs to learn more about building .yml files for your dbt project.

For example, if we had a file called projects.yml in dbt that looked something like the one below, we'd see a Table called Projects in Lightdash.

version: 2

models:
  - name: projects
    columns: 
      - name: dashboards_created_num_total
      - name: days_since_project_created

We recommend structuring your dbt project with one .yml file per model (or .sql file).

We've found that this makes it easier to navigate through your .yml files and easier to manage your dbt models, especially as your project becomes bigger.

Here's an example of our dbt project at Lightdash too see what that looks like in practice:

  • We have one .sql file per model (these are the files where all of our models' business logic sits)
  • We have one .yml file per model (these are the files where all of your Tables' configuration sits)

But, in my dbt project, I have a single schema.yml file. Not one for each model. Will that still work?​

Yep! We realize that schema files come in all shapes and sizes.

Some people prefer to write the .yml files for all of their models in a single .yml file at the directory level, and that's totally fine - it will still work with Lightdash.

But, like we said just above, if you're trying to decide how to setup your dbt project, we'd recommend having one .yml file per model.

1. Creating Tables and their dimensions in Lightdash using lightdash dbt run​

info

These instructions assume that you've already installed the Lightdash CLI. If you haven't done that yet, then check out the docs on Installing the Lightdash CLI before moving on!

Dimensions are attributes of your data. They are directly linked to a column in a dbt model.

To explore data in Lightdash, you need to have dimensions defined in your Tables. Dimensions are created automatically when you define columns in your dbt model properties.

For example, if we go back to our projects.yml file from above, we'd have a Table called Projects and it would have the dimensions: Dashboards created num total and Days since project created.

version: 2

models:
  - name: projects
    columns: 
      - name: dashboards_created_num_total
      - name: days_since_project_created
info

Before you get started with the next steps, you might want to check out onto a new branch if you're working with a version controlled project!

We've made it really easy to add new Tables to our Lightdash project using our CLI tool and the command:

lightdash dbt run

This command runs our dbt project to create our analysis-ready tables in our data warehouse. Then, it auto-generates our Lightdash configuration.

You have a few options with this command though:

1. Generate Tables and dimensions for my entire dbt project​

You will only really need to do this when you're first setting up your Lightdash project.

If you're joining a project with lots of Tables in it already, you probably don't need to run this - we'd suggest checking out option 2 below.

To run + generate tables for all of the models with .yml files, you just need to run the following on your command line:

lightdash dbt run

This command will run + generate tables for all of the models with .yml files. It will also generate dimensions for all of the columns in your dbt project.

2. Generate Tables and dimensions for some of the models in my dbt project​

There may be a specific set of models that you want to generate Tables for in Lightdash. The lightdash dbt run command supports dbt model selection syntax to generate .yml files for a group of models. This means you can use tags, model names, or other model selection syntax to specify which models you want to generate dimensions for in your dbt project.

lightdash run # all models
lightdash run -s payments  # just payments
lightdash run -s payments+ # payments and all children
lightdash run -s +payments # payments and all parents
lightdash run -s +payments+ # payments and all children and parents
lightdash run -s tag:prod # all models with the prod tag
lightdash run -s payments customers # two specific models
lightdash run -s payments+ +customers tag:prod # mix and match

After running our dbt models, lightdash dbt run is going to auto-generate all the configuration that Lightdash needs to build our Tables. Here’s an example of an auto-generated .yml file for a table that you might see:

# customers.yml
version: 2
models:
  - name: customers
    columns:
      - name: customer_id
                meta:
                    dimension:
                        type: string
      - name: name
                meta:
                    dimension: 
                        type: string
      - name: created_at
        meta:
          dimension:
                        type: timestamp

The customers.yml file tells Lightdash about all the attributes of customers. So by now our Lightdash project usually looks a little like this:

lightdash-project
├── dbt_project.yml
└── models
    ├── customers.sql
    └── customers.yml

Now let’s push our Table to Lightdash so we can start exploring our data through the UI.

2. Make sure your changes are in production, then you're ready to go!​

If you're working with a version controlled project, you'll just want to make sure to merge your changes into production (e.g. main or master) before you move onto the next steps.

Once you've got the model .yml files where you want 'em, you're ready to move onto the next step of connecting your project.

Configuring which Tables appear in Lightdash​

Sometimes, there are models in our dbt project with .yml files that we might not want to appear in Lightdash (base tables, I'm looking at you 👀). So, we've made it possible for you to configure which Tables you want to appear in Lightdash.

To get to your Table Configuration settings, just:

  1. Click Settings in the navigation bar
  2. Click on project management in the sidebar
  3. Click on the Settings button for the project's tables you want to configure
  4. Once you're in your Project settings, click on the Tables configuration in the sidebar.

Now that you're in the right place, let's get to the juicy stuff. You have three options for configuring the Tables that pop up in Lightdash:

  1. Show entire project: I hope this one isn't too much of a surprise. If you select this option, it shows all of the models with .yml files in your dbt project in Lightdash.
  2. Show models with any of these tags: This option depends on dbt tags. If you're not familiar with dbt tags, we'd recommend checking them out (they're really handy for managing models!). If you already have a specific model tag (or tags) you want to limit Lightdash to using, this is where you can add them in. For example, all of our production models have the tag prod, so we've configured our Tables using that tag.
  3. Show models in this list: If you're not keen on using tags then you can manually select the models you want to include as Tables in your Lightdash project using this option.
info

🔥 Hot tip: if you want to manage the models you're using in Lightdash in your dbt project, then you can use dbt's tags to tag all of your models with lightdash then setup your Tables Configuration to limit tables with the tag lightdash (see option 2 above).

So, for the models you want to include in Lightdash, you'd just add the tag to your model's .yml file:

version: 2

models:
  - name: model_name
    config:
      tags: ['lightdash']
...

Or, to your model's .sql file in the config block:

{{ config(
    tags=["lightdash"]
) }}

select ...

Then, you'll set your Table Configuration:

Showing hidden tables in Lightdash​

You can still access + explore all of the Tables from your dbt project (with .yml files), even if you've filtered them from appearing in the sidebar. You can access these hidden table by just clicking on the Show hidden tables toggle in the Tables sidebar.

Changing your Table's labels, adding joins, and more!​

Once you're happy with which Tables are showing up in Lightdash, you can add configurations to your Tables like:

All of these configurations and more are outlined in the Tables reference doc here.

Next up: adding metrics + exploring your Tables​

Now that you have some of your Tables in Lightdash, it's time to enrich them with some metrics and learn how to explore them.

We'd recommend checking out these docs next: