Recipes - Lightdash

What are API recipes?

The Lightdash API is intentionally modular: most endpoints return small, focused pieces of information. While this keeps the API flexible, some real-world tasks require calling multiple endpoints together or assembling responses in a certain order. For example, Lightdash doesn’t provide a single endpoint that returns “SQL for every chart,” but you can accomplish this by combining the chart-listing endpoint with the metric-query endpoint. Follow the recipes below. API recipes are short guides that show you how to do exactly that. Each recipe walks through:

What you’re trying to accomplish (e.g., fetch SQL for all charts in a project)
Which endpoints to call and in what order
Example requests in curl
Optional scripts in JS/Python when useful

When should you use recipes?

Use a recipe when:

You want to automate a workflow that spans multiple endpoints
You need to query Lightdash programmatically but aren’t sure which calls to chain
You want real examples of authentication, pagination, or interpreting metric queries
You’re building internal tooling or dashboards powered by the Lightdash API

This page includes a few of the most common API recipes, but it’s not a complete list. For all available recipes, see the full collection here.

Before you run any recipe

You will need:

Python 3.9+ installed
A Personal Access Token (PAT)
Your Lightdash instance URL
The project UUID you want to modify

Extract SQL from a saved Lightdash chart using the API

This recipe shows how to fetch the underlying SQL for any saved chart. You can use this to debug queries, run them directly in your warehouse, or automate data pulls outside of Lightdash.

Step 1: Fetch the saved chart

(You must first create a Personal Access Token (PAT))

curl -H "Authorization: ApiKey ldpat_{{your_personal_access_token}}" \
  https://{{your.lightdash.instance}}/api/v1/saved/{{your_chart_uuid}}

Step 2: Extract the `metricQuery` JSON

curl -s \
  -H "Authorization: ApiKey ldpat_{{your_personal_access_token}}" \
  https://{{your.lightdash.instance}}/api/v1/saved/{{your_chart_uuid}} \
  | jq '.results.metricQuery' > metricQuery.json

This writes the chart’s semantic query definition to metricQuery.json.

Step 3: Compile the `metricQuery` into SQL

curl -s -X POST \
  -H "Authorization: ApiKey ldpat_{{your_personal_access_token}}" \
  -H "Content-Type: application/json" \
  -d @metricQuery.json \
  https://{{your.lightdash.instance}}/api/v1/projects/{{your_project_uuid}}/explores/{{your_explore_name}}/compileQuery \
  | jq '.results.query'

The request body needs a sorts array and a numeric limit. If sorts is empty, the compiled SQL still has an ORDER BY: Lightdash sorts by the first selected date or timestamp dimension (descending), otherwise the first metric (descending), otherwise the first dimension (ascending). A query with no dimensions returns one aggregated row and gets no default sort. The SQL uses the limit you send; compiling doesn’t cap it at the maximum query rows, which applies when Lightdash runs a query.

One-Step Solution

curl -s -H "Authorization: ApiKey ldpat_{{your_personal_access_token}}" \
  https://{{your.lightdash.instance}}/api/v1/savedc/{{your_chart_uuid}} \
  | jq '.results.metricQuery' \
  | curl -s -X POST \
      -H "Authorization: ApiKey ldpat_{{your_personal_access_token}}" \
      -H "Content-Type: application/json" \
      -d @- \
      https://{{your.lightdash.instance}}/api/v1/projects/{{your_project_uuid}}/explores/{{your_explore_name}}/compileQuery \
  | jq '.results.query'

List SQL charts in a project using the API

This recipe lists the SQL charts you can view in a project and fetches the SQL behind each one, for example to audit which tables your SQL charts query.

Step 1: List the project’s SQL charts

The content API returns every kind of chart. Each chart has a source field: sql for a chart saved from the SQL runner, dbt_explore for a chart built from an explore. Filter for sql:

curl -s -H "Authorization: ApiKey ldpat_{{your_personal_access_token}}" \
  "https://{{your.lightdash.instance}}/api/v2/content?projectUuids={{your_project_uuid}}&contentTypes=chart&pageSize=100&page=1" \
  | jq '.results.data[] | select(.source == "sql") | {uuid, name}'

Results are paginated. If .results.pagination.totalPageCount is greater than 1, repeat the request with page=2, page=3, and so on.

Step 2: Fetch the SQL for each chart

curl -s -H "Authorization: ApiKey ldpat_{{your_personal_access_token}}" \
  https://{{your.lightdash.instance}}/api/v1/projects/{{your_project_uuid}}/sqlRunner/saved/{{your_sql_chart_uuid}} \
  | jq -r '.results.sql'

Update the SQL of many charts

To rewrite SQL across many charts, for example after renaming a schema, the simplest route is content as code: lightdash download writes each SQL chart’s sql to a file, you edit and review the files, and lightdash upload pushes them back. To do it through the API instead, send a PATCH to the URL from step 2 with a versionedData object. It needs sql, limit and config, so copy limit and config from the step 2 response and change only sql:

curl -s -X PATCH -H "Authorization: ApiKey ldpat_{{your_personal_access_token}}" \
  -H "Content-Type: application/json" \
  -d '{"versionedData": {"sql": "select * from acme_analytics.orders", "limit": 500, "config": {{config_from_step_2}}}}' \
  https://{{your.lightdash.instance}}/api/v1/projects/{{your_project_uuid}}/sqlRunner/saved/{{your_sql_chart_uuid}}

Run a SQL query using the API

Running SQL through the API takes two calls: the first starts the query and returns a queryUuid, the second fetches its results. The v1 POST /api/v1/projects/{projectUuid}/sqlRunner/run endpoint is deprecated; use these instead.

Step 1: Start the query

curl -s -X POST -H "Authorization: ApiKey ldpat_{{your_personal_access_token}}" \
  -H "Content-Type: application/json" \
  -d '{"sql": "select * from acme_analytics.orders", "limit": 500}' \
  https://{{your.lightdash.instance}}/api/v2/projects/{{your_project_uuid}}/query/sql \
  | jq -r '.results.queryUuid'

Step 2: Fetch the results

curl -s -H "Authorization: ApiKey ldpat_{{your_personal_access_token}}" \
  "https://{{your.lightdash.instance}}/api/v2/projects/{{your_project_uuid}}/query/{{your_query_uuid}}?page=1&pageSize=500"

While the query runs, results.status is pending, queued or executing, so repeat the request until it’s ready. A ready response holds that page’s rows and columns, and nextPage is set while more pages remain. A failed query has the status error, with the message in results.error.

Dashboard cleanup & usage audit for a project

This Python script helps you clean up dashboards in a specific Lightdash project. It uses the v2 Content API and can export results to CSV, Excel, or JSON for deeper analysis. This script provides:

A complete list of all dashboards with metadata for that project
View counts and first viewed dates
Creation and last modification dates
Dashboard organization by spaces
Cleanup recommendations based on usage patterns (never viewed, low engagement, stale, no description, etc.)

Before you run this script

You will need:

A copy of find_dashboards.py

Step 1: Install dependencies

You can run this script with poetry or plain Python:

poetry install
# OR
pip install -r requirements.txt

(Dependencies include requests and pandas.)

Step 2: Update script configuration

Open the script and update these fields:

API_URL = 'https://{YOUR_INSTANCE_URL}.lightdash.cloud'  # Your Lightdash instance
API_KEY = ''                                             # Your Personal Access Token
PROJECT_UUID = ''                                        # Project you want to analyze (REQUIRED)
EXPORT_METHOD = 'csv'                                    # 'csv', 'excel', or 'json'

Step 3: Run the script

Using poetry:

poetry run python find_dashboards.py

Or directly:

python find_dashboards.py

Export all users in your organization

This Python script fetches all users in your Lightdash organization using the /api/v1/org/users endpoint. This is useful for audits, access reviews, and governance. It returns:

Full name
Email
Role
Group membership
Optional Excel or CSV export

Before you run this script

You will need:

A copy of get_all_organization_users.py

Step 1: Install dependencies

pip install requests pandas

(or add them to your environment of choice)

Step 2: Update script configuration

Open the script and replace:

API_URL = 'https://<yourinstance>.lightdash.cloud/api/v1/org/users'
API_KEY = '<yourkey>'
EXPORT_METHOD = 'excel'  # or 'csv'

Step 3: Run the script

python get_all_organization_users.py

Invite users to your organization using the API

To invite people who don’t have a Lightdash account yet, send one request per email address. Use a token from an organization admin or an admin service account. role is optional and defaults to member; it takes the organization roles member, viewer, interactive_viewer, editor, developer or admin.

curl -s -X POST -H "Authorization: ApiKey ldpat_{{your_personal_access_token}}" \
  -H "Content-Type: application/json" \
  -d '{"email": "jane@example.com", "role": "viewer"}' \
  https://{{your.lightdash.instance}}/api/v1/invite-links

Lightdash emails the invite and returns its inviteUrl. expiresAt is optional: a future date earlier than your organization’s invite link expiry is kept, and anything else gets the default expiry. Sending the request again for a pending invite creates a new link. If the email already belongs to a user in your organization or in another organization, the request fails with a 400 error.

Assign or update project access for a list of users

This Python script assigns Lightdash project roles to a list of users from a CSV file, and upgrades roles when needed. This is useful for large-scale onboarding, role synchronization, or managing customer/partner access. This script handles:

Bulk granting of project access
Automatic role upgrades (e.g., viewer → editor)
Skipping users who already have equal or higher access
Checking whether users exist in the organization
Merging org users, project access, and your CSV into one unified dataset

Before you run this script

You will need:

A CSV file of users with desired roles
A copy of assign_project_access_to_user_list.py

Step 1: Prepare your CSV file

Your CSV should contain two columns:

email,role
user1@example.com,viewer
user2@example.com,editor
user3@example.com,developer

Valid roles:

viewer, interactive_viewer, editor, developer, admin

Step 2: Install dependencies

pip install pandas lightdash-api-client

(or via poetry)

Step 3: Update script configuration

Open the script and update:

TARGET_URL = 'https://app.lightdash.cloud/api/v1/'
TARGET_API_KEY = ''
TARGET_PROJECT_ID = ''
USER_PERMS_FILEPATH = '~/Documents/user_permission_list.csv'

Step 4: Run the script

python assign_project_access_to_user_list.py

Grant project access to one user

To add a single user without the script, send their email, a project role and sendEmail:

curl -s -X POST -H "Authorization: ApiKey ldpat_{{your_personal_access_token}}" \
  -H "Content-Type: application/json" \
  -d '{"email": "jane@example.com", "role": "viewer", "sendEmail": true}' \
  https://{{your.lightdash.instance}}/api/v1/projects/{{your_project_uuid}}/access

sendEmail is required, and a request without it fails validation without adding the user. Set it to true to email the user a link to the project, or false to add them silently. The user must already belong to your organization; invite new people first. The role assignment endpoint POST /api/v2/projects/{projectUuid}/roles/assignments/user/{userUuid} also accepts sendEmail, but there it’s optional. It sends the email only when it’s true and the user had no role on the project before.

Assign Metrics Catalog categories using the API

Categories are the labels shown in the Metrics Catalog. Use this recipe to create a category and assign it to metrics from a script, for example to mark metrics as reviewed. The token’s user needs the editor role or above in the project. To manage categories in your dbt project instead, see Assigning categories to metrics.

Step 1: Create a category

Set color to one of the colors the catalog offers: gray, violet, red, orange, green, blue, indigo, pink, or yellow. The response returns the new category’s tagUuid. To find an existing category’s tagUuid, send a GET to the same URL.

curl -s -X POST -H "Authorization: ApiKey ldpat_{{your_personal_access_token}}" \
  -H "Content-Type: application/json" \
  -d '{"name": "Reviewed", "color": "green"}' \
  https://{{your.lightdash.instance}}/api/v1/projects/{{your_project_uuid}}/tags

Step 2: Assign the category to a metric

Find the metric’s catalogSearchUuid in the results of GET /api/v1/projects/{{your_project_uuid}}/dataCatalog/metrics, then assign the category:

curl -s -X POST -H "Authorization: ApiKey ldpat_{{your_personal_access_token}}" \
  -H "Content-Type: application/json" \
  -d '{"tagUuid": "{{your_tag_uuid}}"}' \
  https://{{your.lightdash.instance}}/api/v1/projects/{{your_project_uuid}}/dataCatalog/{{catalog_search_uuid}}/categories

To remove it, send a DELETE to /api/v1/projects/{{your_project_uuid}}/dataCatalog/{{catalog_search_uuid}}/categories/{{your_tag_uuid}}.

Filter metrics by tag using the data catalog API

The /api/v2/projects/{projectUuid}/dataCatalog/metrics-with-time-dimensions endpoint returns metrics from your project’s data catalog. You can filter the results by field-level tags using the tags query parameter, which makes it possible to retrieve only the metrics relevant to a specific workflow — such as a headless metrics store pipeline or a semantic layer integration. Field-level tags are set in your dbt YAML under individual metrics and dimensions. See the metrics reference and dimensions reference for syntax details.

Step 1: Tag your metrics in dbt

Add tags to specific metrics in your dbt YAML:

models:
  - name: orders
    columns:
      - name: amount
        config:
          meta:
            metrics:
              total_order_amount:
                type: sum
                tags: ["core", "finance"]
              average_order_amount:
                type: average
                tags: ["core"]

models:
  - name: orders
    columns:
      - name: amount
        meta:
          metrics:
            total_order_amount:
              type: sum
              tags: ["core", "finance"]
            average_order_amount:
              type: average
              tags: ["core"]

type: model
name: orders

metrics:
  total_order_amount:
    type: sum
    sql: ${TABLE}.amount
    tags: ["core", "finance"]
  average_order_amount:
    type: average
    sql: ${TABLE}.amount
    tags: ["core"]

Step 2: Fetch metrics filtered by tag

Use the tags query parameter to retrieve only metrics with matching tags. You can pass multiple tags parameters to filter by more than one tag (OR logic — metrics matching any of the provided tags are returned).

curl -H "Authorization: ApiKey ldpat_{{your_personal_access_token}}" \
  "https://{{your.lightdash.instance}}/api/v2/projects/{{your_project_uuid}}/dataCatalog/metrics-with-time-dimensions?page=1&pageSize=10&sort=label&order=asc&tags=core&tags=finance"

Example response

Each metric in the response includes a tags array showing all tags assigned to it:

{
  "status": "ok",
  "results": {
    "data": [
      {
        "name": "total_order_amount",
        "label": "Total order amount",
        "tags": ["core", "finance"],
        "categories": [],
        "tableName": "orders",
        "tableLabel": "Orders",
        ...
      },
      {
        "name": "average_order_amount",
        "label": "Average order amount",
        "tags": ["core"],
        "categories": [],
        "tableName": "orders",
        "tableLabel": "Orders",
        ...
      }
    ]
  }
}

Supported query parameters

Parameter	Type	Description
`page`	number	Page number for pagination (starts at 1)
`pageSize`	number	Number of results per page
`sort`	string	Field to sort by (e.g., `label`)
`order`	string	Sort order: `asc` or `desc`
`tags`	string	Filter by tag. Repeat the parameter to filter by multiple tags (OR logic).

Tags are a backend-only mechanism — they are not displayed as Spotlight categories in the Lightdash UI. This makes them suitable for controlling API-driven workflows without affecting the end-user experience.

What are API recipes?

When should you use recipes?

Before you run any recipe

Extract SQL from a saved Lightdash chart using the API

Step 1: Fetch the saved chart

Step 2: Extract the metricQuery JSON

Step 3: Compile the metricQuery into SQL

One-Step Solution

List SQL charts in a project using the API

Step 1: List the project’s SQL charts

Step 2: Fetch the SQL for each chart

Update the SQL of many charts

Run a SQL query using the API

Step 1: Start the query

Step 2: Fetch the results

Dashboard cleanup & usage audit for a project

Before you run this script

Step 1: Install dependencies

Step 2: Update script configuration

Step 3: Run the script

Export all users in your organization

Before you run this script

Step 1: Install dependencies

Step 2: Update script configuration

Step 3: Run the script

Invite users to your organization using the API

Assign or update project access for a list of users

Before you run this script

Step 1: Prepare your CSV file

Step 2: Install dependencies

Step 3: Update script configuration

Step 4: Run the script

Grant project access to one user

Assign Metrics Catalog categories using the API

Step 1: Create a category

Step 2: Assign the category to a metric

Filter metrics by tag using the data catalog API

Step 1: Tag your metrics in dbt

Step 2: Fetch metrics filtered by tag

Example response

Supported query parameters

Step 2: Extract the `metricQuery` JSON

Step 3: Compile the `metricQuery` into SQL