Building a Data Stack Live with AI AgentsLivestream August 18

Skip to main content

Lightdash

Lightdash is an open-source BI platform that turns your dbt project into a governed metrics and dashboarding layer. It connects to MotherDuck as a DuckDB warehouse.

How it works with MotherDuck​

Lightdash builds its semantic layer from your dbt project and queries the warehouse directly. When you connect a project, select DuckDB as the warehouse type and choose the MotherDuck option so Lightdash runs queries against your MotherDuck database.

Prerequisites​

  • A MotherDuck database with the tables or views your dbt project models.
  • A dbt project (dbt v1.8 or later) whose profile targets MotherDuck.
  • A MotherDuck access token for Lightdash to use.

Setup​

  1. In MotherDuck, create an access token for Lightdash.
  2. In Lightdash, create a project and select DuckDB as the warehouse type, then choose the MotherDuck option at the top of the connection form.
  3. Fill in the connection fields:
    • Database: the MotherDuck database name. If your dbt profile uses path: "md:analytics", enter analytics.
    • Schema: the schema Lightdash should use, for example main.
    • Access token: the MotherDuck token you created.
    • Threads: start with 1 and increase as needed.
  4. Save the connection and let Lightdash compile your dbt project.

Authentication and configuration​

  • Use a dedicated token for Lightdash, scoped to only the database(s) you want it to query.
  • Enter the database name without the md: prefix.
  • Keep the schema in the connection form aligned with the schema your dbt models write to.

Your dbt profiles.yml should target MotherDuck through the DuckDB adapter with the motherduck extension:

my-motherduck-db:
  target: prod
  outputs:
    prod:
      type: duckdb
      path: "md:analytics"
      schema: main
      threads: 4
      extensions:
        - motherduck
      settings:
        motherduck_token: "{{ env_var('MOTHERDUCK_TOKEN') }}"

Important notes​

  • Lightdash reads its metrics and dimensions from your dbt project, so keep the dbt models and Lightdash connection pointed at the same MotherDuck database and schema.
  • The "Start of week" setting controls which day begins the week in charts. "Auto" uses the warehouse default.

Use cases​

  • Expose a governed metrics layer over MotherDuck data built from your dbt models.
  • Build dashboards and explores for business users on top of curated MotherDuck schemas.
  • Reuse an existing dbt-on-MotherDuck project as the semantic layer for self-serve BI.

Related content​