Skip to main content

Update your project connection

To setup your Lightdash connection you'll need to:

  1. Connect to your data warehouse (bigquery, postgres, redshift, snowflake, databricks)
  2. Connect to your dbt project

Open up your Lightdash instance to get started.​

To update an existing connection, head to:

  1. Settings
  2. Project management
  3. Settings for your project

(see steps 1-3 in the image below)

To create a new project connection, head to:

  1. Settings
  2. Project management
  3. Create New

(see steps 1, 2 & 4 in the image below)

1. Connect to a warehouse​

We currently support:

  1. Bigquery
  2. Postgres
  3. Redshift
  4. Snowflake
  5. Databricks
  6. Trino

We always recommend giving read-only permissions to Lightdash, that way you ensure than no data can be manipulated. See each section below for warehouse specific details.

info

If you need to add our IP address to your database's allow-list, check what IP addresses we use here

Bigquery​

Project​

This is project ID from Google Cloud Platform for the data that you want to connect Lightdash to.

To find your project ID, head to the BigQuery Cloud console.

Once you're in there, click on the project name in the top bar.

Then, you should see all of the projects and their project IDs in your organization (that you have access to).

For the project you want to connect Lightdash to, just copy its id and pop it into the project field in the Warehouse Connection form in Lightdash.

Data set​

This is the default dataset used by dbt to compile and run your dbt project. You can find this in the dbt cloud IDE or your local profiles.yml file.

If you're a dbt cloud user you can find this under your profile in the dbt cloud IDE:

If you work with dbt locally, then check your profiles.yml file at ~/.dbt/profiles.yml and look for a field named dataset:

my-profile:
  target: dev
  outputs:
    dev:
      type: bigquery
      method: oauth
      project: abc-123
      dataset: my_dataset # look for this one!
      ...

Location​

The data location of the dataset in BigQuery where the output of your dbt models is written to.

location may be either a multi-regional location (e.g. EU, US), or a regional location (e.g. us-west2 ). Check out the BigQuery documentation for more information on dataset locations.

You can find the location of the dataset you're using for your dbt project in your dbt profiles.yml file, or in your BigQuery console.

Key File​

To connect to BigQuery, you'll need to have a service account that you can use with Lightdash. You can read more about creating and managing service accounts with Google BigQuery in their docs.

info

If you're creating a new service account, make sure that you save your JSON key file somewhere safe! You'll need it for connecting to Lightdash.

The service account you use with Lightdash will need to have the following roles in your GCP project:

  • roles/bigquery.dataViewer (to see data in your project)
  • roles/bigquery.jobUser (to run queries in your project)

If you need to provide access to data across multiple BigQuery projects, the service account will need to be granted roles/bigquery.dataViewer on each additional BigQuery project.

Once you have a service account all ready to go, you'll need to add its JSON key file to Lightdash in the key file section of the Warehouse Connection page.

Timeout in seconds​

BigQuery supports query timeouts. By default, the timeout is set to 300 seconds. If a query run by Lightdash takes longer than this timeout to complete, then BigQuery may cancel the query and issue the following error:

Operation did not complete within the designated timeout.

To change this timeout, use the Timeout in seconds configuration.

Priority​

The priority for the BigQuery jobs that Lightdash executes can be configured with the priority configuration in your Warehouse Connection settings. The priority field can be set to one of batch or interactive.

For more information on query priority, check out the BigQuery documentation.

Retries​

The retries configuration specifies the number of times Lightdash should retry queries that result in unhandled server errors.

For example, setting retries to 5 means that Lightdash will retry BigQuery queries 5 times with a delay. If the query does not succeed after the fifth attempt, then Lightdash will raise an error.

By default, the number of retries is set to 3.

Maximum bytes billed​

If a value for the Maximum bytes billed is set, then queries executed by Lightdash will fail if they exceed the configured maximum bytes threshhold. This configuration should be supplied as an integer number of bytes.

For example, setting this to 1000000000 means if a query would bill more than a gigabyte of data (e.g. 2Gb), then BigQuery will reject the query and you'd get an error like this:

  Query exceeded limit for bytes billed: 1000000000. 2000000000 or higher required.

Start of week​

This controls what day is the start of the week in Lightdash. Auto sets it to whatever the default is for your data warehouse. Or, you can customize it and select the day of the week from the drop-down menu. This will be taken into account when using 'WEEK' time interval in Lightdash.

Postgres​

You can see more details in dbt documentation.

Host​

This is the host where the database is running.

User​

This is the database user name.

Password​

This is the database user password.

DB name​

This is the database name.

Schema​

This is the default schema used by dbt to compile and run your dbt project. You can find this in the dbt cloud IDE or your local profiles.yml file.

If you're a dbt cloud user you can find this under your profile in the dbt cloud IDE:

If you work with dbt locally, then check your profiles.yml file at ~/.dbt/profiles.yml and look for a field named schema:

company-name:
  target: dev
  outputs:
    dev:
      type: postgres
      host: [hostname]
      user: [username]
      password: [password]
      port: [port]
      dbname: [database name]
      schema: [dbt schema] # look for this one!

Port​

This is the port where the database is running.

Keep alive idle (seconds)​

This specifies the amount of seconds with no network activity after which the operating system should send a TCP keepalive message to the client. You can see more details in postgresqlco documentation.

Search path​

This controls the Postgres "search path". You can see more details in dbt documentation.

SSL mode​

This controls how dbt connects to Postgres databases using SSL. You can see more details in dbt documentation.

Start of week​

This controls what day is the start of the week in Lightdash. Auto sets it to whatever the default is for your data warehouse. Or, you can customize it and select the day of the week from the drop-down menu. This will be taken into account when using 'WEEK' time interval in Lightdash.

Redshift​

You can see more details in dbt documentation.

Host​

This is the host where the database is running.

User​

This is the database user name.

Password​

This is the database user password.

DB name​

This is the database name.

Schema​

This is the default schema used by dbt to compile and run your dbt project. You can find this in the dbt cloud IDE or your local profiles.yml file.

If you're a dbt cloud user you can find this under your profile in the dbt cloud IDE:

If you work with dbt locally, then check your profiles.yml file at ~/.dbt/profiles.yml and look for a field named schema:

company-name:
  target: dev
  outputs:
    dev:
      type: redshift
      host: hostname.region.redshift.amazonaws.com
      user: username
      password: password1
      port: 5439
      dbname: analytics
      schema: analytics # look for this one!

Port​

This is the port where the database is running.

Keep alive idle (seconds)​

This specifies the amount of seconds with no network activity after which the operating system should send a TCP keepalive message to the client.

If the database closes its connection while Lightdash is waiting for data, you may see the error SSL SYSCALL error: EOF detected. Lowering the keepalives_idle value may prevent this, because the server will send a ping to keep the connection active more frequently.

By default, this value is set to 240 seconds, but can be configured lower (perhaps 120 or 60), at the cost of a chattier network connection.

SSL mode​

This controls how dbt connects to Postgres databases using SSL.

RA3 Node​

Allow dbt to use cross-database-resources

Start of week​

This controls what day is the start of the week in Lightdash. Auto sets it to whatever the default is for your data warehouse. Or, you can customize it and select the day of the week from the drop-down menu. This will be taken into account when using 'WEEK' time interval in Lightdash.

Snowflake​

You can see more details in dbt documentation.

Account​

This is your Snowflake account identifer.

The easiest way to find this is to login to snowflake in your browser and your account identifier is whatever comes before snowflakecomputing.com.

For example in the image below, the user logs in via https://aaa99827.snowflakecomputing.com/console/login#/ so the account identifier is aaa99827.

If you don't have access via the browser, you can use the following format <organization_name>-<account_name> where organization_name and account_name can be found by following any of the methods listed in Managing accounts in your organization.

User​

This is the login name for your Snowflake user. This is usually the same username you use to login to Snowflake.

Alternatively, you can create a new user through the snowflake console with a username and password specifically for Lightdash to use.

If you're a snowflake admin you can list all users available in the snowflake console:

Password​

This is the password your Snowflake user. This is usually the same password you use to login to Snowflake:

Alternatively, you can create a new user through the snowflake console with a username and password specifically for Lightdash to use.

If you're a Snowflake admin you can list all users available in the snowflake console:

Role​

This is the security role that you would like to use when running queries as the specified user. The role must have access to any warehouses, databases, schemas, and tables you want to use.

If you're a Snowflake admin you can list all roles available in the snowflake console:

You can configure your role to allow read access to all warehouses, databases, schemas, and tables by following the guide for Creating custom read-only roles.

Database​

This is the name of your database. The specified user must be granted access to this database. You can see a list of databases available in the snowflake console:

Warehouse​

This is the name of the warehouse you would like to use for running queries. The specified user must be grantend access to use this warehouse. You can see al list of warehouses available in the snowflake console:

Schema​

This is the default schema used by dbt to compile and run your dbt project. You can find this in the dbt cloud IDE or your local profiles.yml file.

If you're a dbt cloud user you can find this under your profile in the dbt cloud IDE:

If you work with dbt locally, then check your profiles.yml file at ~/.dbt/profiles.yml and look for a field named schema:

my-snowflake-db:
  target: dev
  outputs:
    dev:
      type: snowflake
      account: [account id]
      user: [username]
      password: [password]
      role: [user role]
      database: [database name]
      warehouse: [warehouse name]
      schema: [dbt schema]    # Look for this one!

Keep client session alive​

This is intended to keep Snowflake sessions alive beyond the typical 4 hour timeout limit. You can see more details in dbt documentation.

Query tag​

A value with which to tag all queries, for later searching in QUERY_HISTORY view)

Start of week​

This controls what day is the start of the week in Lightdash. Auto sets it to whatever the default is for your data warehouse. Or, you can customize it and select the day of the week from the drop-down menu. This will be taken into account when using 'WEEK' time interval in Lightdash.

Databricks​

The credentials needed to connect to your cluster can be found in the ODBC options in your databricks account:

  1. Go to the Compute tab in the sidebar.
  2. Click the configuration tab for the cluster that you're connecting to Lightdash.
  3. Expand the Advanced options tab
  4. Open the JDBC/ODBC tab

Server hostname​

Follow the instructions above to find your ODBC connection instructions.

HTTP Path​

Follow the instructions above to find your ODBC connection instructions.

Port​

Follow the instructions above to find your ODBC connection instructions.

Personal Access Token​

Your personal access token can be found in your user settings in databricks:

  1. Open Settings by clicking the cog ⚙️ in the sidebar and select User settings
  2. Click Generate token. You'll be asked to enter a name and expiry.
  3. Copy the token

Database​

The default database name used by dbt for this connection.

Start of week​

This controls what day is the start of the week in Lightdash. Auto sets it to whatever the default is for your data warehouse. Or, you can customize it and select the day of the week from the drop-down menu. This will be taken into account when using 'WEEK' time interval in Lightdash.

Trino​

You can see more details in dbt's documentation.

Host​

This is the hostname to connect to.

User​

This is the username for authentication.

Password​

This is the password for authentication.

DB name​

Specify the name of the database that your dbt models are built into.

Port​

The port to connect to the host on.

SSL mode​

This controls how dbt connects to Trino database using SSL.

Start of week​

This controls what day is the start of the week in Lightdash. Auto sets it to whatever the default is for your data warehouse. Or, you can customize it and select the day of the week from the drop-down menu. This will be taken into account when using 'WEEK' time interval in Lightdash.

2. Import a dbt project​

Connecting Lightdash to a hosted dbt project means that you'll be able to keep your Lightdash instance in sync with the changes in your dbt project.

To connect your dbt project, just head to your project connection settings in Lightdash:

Then scroll down to your dbt project connection:

Pick your repository type and follow the guide below:

We don't support connecting Lightdash to dbt Cloud managed repositories. If you're using dbt Cloud and you'd like to use Lightdash, you'll need to have a remote repository for your dbt project in one of the services listed above.

GitHub​

Personal access token​

You can create a personal access token (classic), or a fine-grained access token (beta).

Personal access token (classic)​

This is used to access your repo. See the instructions for creating a personal access token here.

Select repo scope when you're creating the token.

Fine-grained access token (beta)​

Fine-grained access tokens are new special tokens that can only give access to individual repositories on your github account. You can read more about it on the Github docs.

  1. Go to settings > Developer access > Personal access tokens > fine-grained token
  2. Add the name, expiration, description and owner (we'll need their username later)
  3. Add repository access, you might want to give access only to your dbt repository
  1. On repository permissions, select Contents
  1. Click Generate token and copy the token.

You could also replace your old tokens with new fine-grained tokens on project settings.

Repository​

This should be in the format my-org/my-repo. e.g. lightdash/lightdash-analytics

Branch​

This is the branch in your GitHub repo that Lightdash should sync to. e.g. main, master or dev

By default, we've set this to main but you can change it to whatever you'd like.

Project directory path​

This is the folder where your dbt_project.yml file is found in the GitHub repository you entered above.

  • Put / if your dbt_project.yml file is in the main folder of your repo (e.g. lightdash/lightdash-analytics/dbt_project.yml)
  • Include the path to the sub-folder where your dbt project is if your dbt project is in a sub-folder in your repo. For example, if my project was in lightdash/lightdash-analytics/dbt/dbt_project.yml, I'd write /dbt in this field.

Host domain​

If you've customized the domain for your GitHub pages, you can add the custom domain for your project in here.

By default, this is github.com

Target name​

target contains information about your dbt connection to your warehouse.

It's the dataset/schema in your data warehouse that Lightdash will look for your dbt models. By default, we set this to be the same value as you have as the default in your profiles.yml file.

If you want to change this to be something other than the default target defined in dbt, you can enter the target of your choice here (for example dbt_khindson.)

To read more about dbt targets, check out the dbt docs here.

Environment variables​

If you've used environment variables in your dbt profiles.yml file, you can add these to Lightdash here.

For each environment variable, you'll need to add the key + value pair for the item.

You'll normally find these values in a file called .env in your dbt project directory.

For example, I might have something like:

profile:
  target: prod
  outputs:
    prod:
      type: postgres
      host: 127.0.0.1
      user: "{{ env_var('DBT_USER') }}"
      ....

Then a .env file like:

export DBT_USER="myspecialuserkey123"

So, in Lightdash, I'd add a new environment variable and put key as DBT_USER and value as myspecialuserkey123.

GitLab​

Personal access token​

This is used to access your repo. See the instructions for creating a personal access token here.

Select read_repository scope when you're creating the token.

Repository​

You can find this in the GitLab URL when you're in your repo. This should be in the format my-org/my-repo. e.g. if my browser had https://gitlab.com/lightdash/lightdash-analytics.gitlab.io, I'd put in: lightdash/lightdash-analytics as my repository in Lightdash.

Branch​

This is the branch in your GitLab repo that Lightdash should sync to. e.g. main, master or dev

By default, we've set this to main but you can change it to whatever you'd like.

Project directory path​

This is the folder where your dbt_project.yml file is found in the GitLab repository you entered above.

If your dbt_project.yml file is in the main folder of your repo (e.g. lightdash/lightdash-analytics/dbt_project.yml), then you don't need to change anything in here. You can just leave the default value we've put in.

If your dbt project is in a sub-folder in your repo (e.g. lightdash/lightdash-analytics/dbt/dbt_project.yml), then you'll need to include the path to the sub-folder where your dbt project is (e.g. /dbt).

Host domain​

If you've customized the domain for your GitLab pages, you can add the custom domain for your project in here.

By default, this is gitlab.io.

Target name​

target contains information about your dbt connection to your warehouse.

It's the dataset/schema in your data warehouse that Lightdash will look for your dbt models. By default, we set this to be the same value as you have as the default in your profiles.yml file.

If you want to change this to be something other than the default target defined in dbt, you can enter the target of your choice here (for example dbt_khindson.)

To read more about dbt targets, check out the dbt docs here.

Environment variables​

If you've used environment variables in your dbt profiles.yml file, you can add these to Lightdash here.

For each environment variable, you'll need to add the key + value pair for the item.

You'll normally find these values in a file called .env in your dbt project directory.

For example, I might have something like:

profile:
  target: prod
  outputs:
    prod:
      type: postgres
      host: 127.0.0.1
      user: "{{ env_var('DBT_USER') }}"
      ....

Then a .env file like:

export DBT_USER="myspecialuserkey123"

So, in Lightdash, I'd add a new environment variable and put key as DBT_USER and value as myspecialuserkey123.

Azure DevOps​

Personal access token​

This is your secret token used to access Azure Devops. See the instructions to create a personal access token You must specify at least the Repo:Read scope.

Organization​

This is the name of the organization that owns your repository

Project​

This is the name of the project that owns your repository

Repository​

This is the name of the repository. For many projects, this is the same as your project name above.

Branch​

This is the branch in your repository that Lightdash should sync to. e.g. main, master or dev

By default, we've set this to main but you can change it to whatever you'd like.

Project directory path​

This is the folder where your dbt_project.yml file is found in the repository you entered above.

If your dbt_project.yml file is in the main folder of your repo (e.g. lightdash/lightdash-analytics/dbt_project.yml), then you don't need to change anything in here. You can just leave the default value we've put in.

If your dbt project is in a sub-folder in your repo (e.g. lightdash/lightdash-analytics/dbt/dbt_project.yml), then you'll need to include the path to the sub-folder where your dbt project is (e.g. /dbt).

Target name​

target contains information about your dbt connection to your warehouse.

It's the dataset/schema in your data warehouse that Lightdash will look for your dbt models. By default, we set this to be the same value as you have as the default in your profiles.yml file.

If you want to change this to be something other than the default target defined in dbt, you can enter the target of your choice here (for example dbt_khindson.)

To read more about dbt targets, check out the dbt docs here.

Environment variables​

If you've used environment variables in your dbt profiles.yml file, you can add these to Lightdash here.

For each environment variable, you'll need to add the key + value pair for the item.

You'll normally find these values in a file called .env in your dbt project directory.

For example, I might have something like:

profile:
  target: prod
  outputs:
    prod:
      type: postgres
      host: 127.0.0.1
      user: "{{ env_var('DBT_USER') }}"
      ....

Then a .env file like:

export DBT_USER="myspecialuserkey123"

So, in Lightdash, I'd add a new environment variable and put key as DBT_USER and value as myspecialuserkey123.

Local dbt project​

Prerequisite

Unsuitable for production and only available for Lightdash instances installed on your local machine

To start Lightdash with the option to connect to a local dbt project, you must specify the directory of the dbt project when you start docker compose:

# Specify the absolute path to your dbt project
# e.g. export DBT_PROJECT_DIR=/Users/elonmusk/mydbtproject
export DBT_PROJECT_DIR= # Enter your path here!
docker compose start

Bitbucket​

Username​

This is the login name for your Bitbucket user. This is usually the same username you use to login to Bitbucket. You can find your username in Bitbucket by:

  1. Going to your avatar in the bottom left and click Personal settings.
  2. From the Account settings page, check the value next to the Username

Alternatively, you can create a new user through the Bitbucket console with a username and password specifically for Lightdash to use.

Http access token​

Getting a token depends on whether you use Bitbucket Cloud or Bitbucket server:

Select Project read and Repository read scope when you're creating the token.

Repository​

This should be in the format my-org/my-repo. e.g. lightdash/lightdash-analytics

Branch​

This is the branch in your Bitbucket repo that Lightdash should sync to. e.g. main, master or dev

By default, we've set this to main but you can change it to whatever you'd like.

Project directory path​

This is the folder where your dbt_project.yml file is found in the Bitbucket repository you entered above.

  • Put / if your dbt_project.yml file is in the main folder of your repo (e.g. lightdash/lightdash-analytics/dbt_project.yml)
  • Include the path to the sub-folder where your dbt project is if your dbt project is in a sub-folder in your repo. For example, if my project was in lightdash/lightdash-analytics/dbt/dbt_project.yml, I'd write /dbt in this field.

Host domain​

If you've customized the domain for your Bitbucket server, you can add the custom domain for your project in here.

Target name​

target contains information about your dbt connection to your warehouse.

It's the dataset/schema in your data warehouse that Lightdash will look for your dbt models. By default, we set this to be the same value as you have as the default in your profiles.yml file.

If you want to change this to be something other than the default target defined in dbt, you can enter the target of your choice here (for example dbt_khindson.)

To read more about dbt targets, check out the dbt docs here.

Environment variables​

If you've used environment variables in your dbt profiles.yml file, you can add these to Lightdash here.

For each environment variable, you'll need to add the key + value pair for the item.

You'll normally find these values in a file called .env in your dbt project directory.

For example, I might have something like:

profile:
  target: prod
  outputs:
    prod:
      type: postgres
      host: 127.0.0.1
      user: "{{ env_var('DBT_USER') }}"
      ....

Then a .env file like:

export DBT_USER="myspecialuserkey123"

So, in Lightdash, I'd add a new environment variable and put key as DBT_USER and value as myspecialuserkey123.