Adding and managing Tables

In Lightdash, everything you need for BI is written as code in your dbt project. You use dbt to transform all of the data from your data warehouse, then you use Lightdash to explore it.

So, to add and manage Tables in Lightdash, we use dbt.

We’ll walk you through the steps of installing + using the Lightdash CLI and generating the YAML you need to add a new table to your dbt project.

What are Tables?

Tables are the starting point to any data exploration in Lightdash - they’re the data in Lightdash that you can query. 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 YAML files.

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

Not sure what a YAML file is? Check out dbt’s docs about model properties to learn more about adding YAML files for your dbt models.

---

Adding Tables to your Lightdash project using the CLI

Watch this video, or keep reading for a step-by-step guide on how to add Tables to your Lightdash project using the Lightdash CLI.

To get a model in dbt Lightdash-ready, we need to define all of the columns that we want to explore in Lightdash. We’ve made this really easy to do using our CLI tool and the command:

lightdash dbt run -s my_model

This will generate the Table and dimensions for the model you’ve selected with dbt run. It will also document all of the columns in your new model in a schema.yml file.

For example, if you just created a new orders.sql model and you run lightdash dbt run -s orders, an orders.yml file will be generated in your dbt project with all columns documented, like this:

models: 
  - name: orders
    columns:
      - name: basket_total
      - name: browser

Next, preview your changes

You’ll be using the lightdash preview command for this step.

Once you’ve generated your Tables and YAML files in dbt, you can test them out in a Lightdash preview environment.

Developer previews are temporary Lightdash projects where you can safely experiment with your metrics, dimensions and charts without affecting your production project.

So, let’s spin up a developer preview and check out our changes. In your terminal, run the commands:

lightdash preview

Following our example from Step 2, in Lightdash, you’ll see a Table called Orders and each column will appear as a dimension:

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 schema.yml details for all of their models in a single YAML 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 YAML file per model.

Limiting the Tables in Lightdash using dbt tags

There may be a specific set of models that you want include as Tables in Lightdash. If this is the case, we recommend using dbt tags to tag models. You can use sets of existing tags, or you can create a new Lightdash-specific tag.

You can add tags to your YAML file like this:

dbt v1.9 and earlier

models:
  - name: model_name
    tags: ['prod']

dbt v1.10+ and Fusion

models:
  - name: model_name
    config:
      tags: ['prod']

Or, to your model’s SQL file in the config block:

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

select ...

Then, you’ll set your Table Configuration:

Select the models you want to run using dbt selection syntax

The lightdash dbt run command supports dbt model selection syntax to generate YAML files for a group of models. This means you can use tags or other model selection syntax to specify which models you want to generate dimensions for in your dbt project.

lightdash dbt run -s tag:lightdash # all models with the lightdash tag
lightdash dbt run -s payments  # just payments
lightdash dbt run -s payments+ # payments and all children
lightdash dbt run -s +payments # payments and all parents

Generate Tables and dimensions for your entire dbt project in one command

To do this, 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 YAML files. It will also generate dimensions for all of the columns in your dbt project.