"
}
}
```
* **Response:** validity, human-readable detail, and a status code. No code or configuration is pulled during validation.
The request contains the license key only. It does **not** contain warehouse credentials, dbt project data, query text, query results, chart or dashboard definitions, or any organization business data.
Keygen is the only Lightdash-operated external service required for Enterprise features. If you also enable the optional [organization roadmap](/self-host/customize-deployment/organization-roadmap), the backend additionally calls `https://roadmap.lightdash.com` — see the [egress matrix](#egress-matrix) below.
For full license setup steps and troubleshooting, see [Enterprise features and licensing](/self-host/enterprise-features).
## Related systems
These systems are commonly conflated with RudderStack telemetry. They are independent, and each continues to work when `RUDDERSTACK_ANALYTICS_DISABLED=true`.
| System | Where the data goes | Behaviour when `RUDDERSTACK_ANALYTICS_DISABLED=true` |
| ----------------------------------------------------------------------------------------------------------------------------------------------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | --------------------------------------------------------------------------------------------------- |
| **RudderStack product telemetry** | Outside the deployment — `https://analytics.lightdash.com` by default, or your own RudderStack endpoint if [redirected](#configuration-3-send-telemetry-to-your-own-rudderstack) | Disabled |
| **In-product [Usage Analytics dashboards](/workspace-admin/usage-analytics#usage-analytics-dashboards)** | Application Postgres inside the deployment | Continues to work |
| **[Warehouse query tags](/workspace-admin/usage-analytics#query-tags)** | Your warehouse's query history | Continues to work |
| **[Prometheus metrics](/self-host/customize-deployment/configure-prometheus-metrics-for-self-hosted-lightdash)** and OpenTelemetry HTTP metrics | Exposed on the Lightdash instance for your scraper | Continues to work |
| **Audit logs and application logs** | Wherever your log stack collects stdout | Continues to work |
| **Customer-configured [usage event stream](/self-host/customize-deployment/environment-variables#analytics--event-tracking)** | The S3 bucket you configure with `USAGE_EVENTS_*` variables | Continues to work — the event stream sink runs before the RudderStack path and is independent of it |
## Egress matrix
Every default and optional outbound destination from a self-hosted Lightdash deployment. "Required" means the deployment fails without the destination for the feature it enables; "Optional" means the destination is only reached when the feature is turned on.
| Destination | When it is used | Required or optional | Initiating component | Data sent | Control |
| ----------------------------------------------------------------------------------------------------- | ---------------------------------------------------------------------------------------------------------------------------- | ---------------------------------------------------------------------------- | ---------------------------------------------------------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------ |
| `analytics.lightdash.com` | Default RudderStack telemetry endpoint | Optional (on by default) | Backend, scheduler, workers, browser | Event stream — see [Telemetry payload](#telemetry-payload) | `RUDDERSTACK_ANALYTICS_DISABLED=true`, or redirect with `RUDDERSTACK_WRITE_KEY` + `RUDDERSTACK_DATA_PLANE_URL` |
| `api.keygen.sh` | Enterprise license validation on server start and every 24h | **Required** for Enterprise Edition. Not called at all in Community Edition. | Backend, scheduler, workers | License key inside a JSON `meta.key` field | Remove `LIGHTDASH_LICENSE_KEY` (Community only), or run the [validation proxy](https://roadmap.lightdash.com) in front |
| `roadmap.lightdash.com` | Optional [organization roadmap](/self-host/customize-deployment/organization-roadmap), and optional license-validation proxy | Optional | Backend | Roadmap requests: organization UUID as a path segment and license key in the `lightdash-license-key` header. Proxy requests: license key in a JSON `key` field. No query text or business data. | Do not enable the roadmap feature; leave `LIGHTDASH_LICENSE_VALIDATION_PROXY_ENABLED` unset so license validation goes directly to `api.keygen.sh` |
| Customer data warehouse | Every query, dbt compile, warehouse worker | **Required** | Backend, scheduler, warehouse NATS workers | SQL queries, receives query results in return | Warehouse connection config — kept internal to the deployment |
| Application Postgres | All persistent state (users, projects, encrypted secrets, dashboards, results cache metadata) | **Required** | Backend, scheduler, workers | Full application data | [`PGHOST`](/self-host/customize-deployment/configure-lightdash-to-use-an-external-database) and related variables |
| Object storage (S3 / S3-compatible) | Results cache, exports, images, headless browser artifacts, data-app bundles | **Required** | Backend, scheduler, headless browser | Encrypted results parquet, generated CSVs/PDFs/PNGs, data-app static bundles | [`S3_*`](/self-host/customize-deployment/configure-lightdash-to-use-external-object-storage) variables |
| SMTP provider | Transactional email (invites, password resets, scheduled deliveries) | Optional | Backend, scheduler | Email content: recipient address, invitation link, scheduled chart image or CSV attachment | [`SMTP_*`](/self-host/customize-deployment/environment-variables#smtp) variables |
| Identity provider (Google, Okta, Azure AD, OneLogin, generic OIDC, Snowflake OAuth, Databricks OAuth) | Sign-in and SSO | Optional | Backend, browser (redirect) | OIDC/OAuth handshake — user claims from the IdP; Lightdash sends only what the OAuth flow requires | [`AUTH_*` variables](/self-host/customize-deployment/environment-variables#sso) and [SSO setup](/self-host/customize-deployment/use-sso-login-for-self-hosted-lightdash) |
| AI/model provider (OpenAI, Anthropic, Azure OpenAI, OpenRouter, Bedrock) | AI agents, AI writeback, data apps, embeddings, chart metadata generation | Optional | Backend, scheduler, sandboxes (when the agent runs in a sandbox) | Prompts, tool calls, semantic-layer metadata for context, and — depending on the feature — query results the agent has already fetched from your warehouse. Provider API key. | `AI_COPILOT_ENABLED=false`, `AI_WRITEBACK_ENABLED=false`, or a private LLM gateway; see [AI providers](/self-host/enterprise-features/ai-agents) |
| GitHub (or other source-control provider) | dbt project write-back and AI writeback PRs | Optional | Backend, sandboxes (AI writeback) | Git commits and pull-request contents against the dbt repo you configured; GitHub App installation token or personal access token | Do not [connect GitHub](/self-host/customize-deployment/configure-github-for-lightdash); use a self-hosted GitLab / GHES instance you control |
| Slack | Scheduled deliveries, unfurls, AI agents in Slack | Optional | Backend, scheduler | Message content: chart images, CSVs, unfurl payloads; Slack bot token | Do not [install the Slack app](/self-host/customize-deployment/configure-a-slack-app-for-lightdash) |
| MCP servers | [Model Context Protocol](/self-host/enterprise-features/mcp) tool calls from Lightdash AI agents | Optional | Backend, scheduler | MCP tool call payloads to the servers you register | Do not enable MCP; register only internal MCP endpoints |
| E2B (`api.e2b.dev` and per-sandbox subdomains) | Default managed [sandbox provider](/self-host/customize-deployment/sandboxes) for AI writeback and data apps | Optional | Backend, scheduler | Files copied into the sandbox (dbt project, generated code), API key. Sandbox egress from inside E2B is separately allowlisted per sandbox. | `SANDBOX_PROVIDER=aws-lambda-microvms` or `SANDBOX_PROVIDER=azure-container-apps` to run sandboxes inside your own cloud |
| AWS Lambda MicroVMs / Azure Container Apps Sandboxes | Alternative [sandbox providers](/self-host/customize-deployment/sandboxes) | Optional | Backend, scheduler | Same payload as E2B, but into your own AWS or Azure account | Provider-specific configuration; stays inside your cloud |
| Sentry (or another DSN) | Error reporting (optional; **disabled unless a DSN is set**) | Optional | Backend, scheduler, browser | Error message, stack trace, breadcrumbs, `userId`, `organizationUuid`, `projectUuid`, `dashboardUuid` tags. Session replay is enabled on error. | Leave `SENTRY_BE_DSN` and `SENTRY_FE_DSN` unset (default). Point at your own Sentry to keep it internal. |
| Google Sheets API | Optional [Google Sheets sync](/self-host/customize-deployment/configure-google-sheets-integration) | Optional | Backend, scheduler | Query results written to the target sheet; Google service-account credentials | Do not configure Google Sheets integration |
| dbt Cloud API | Optional dbt Cloud integration for a project's dbt configuration | Optional | Backend | Job trigger requests, project artifact fetches; dbt Cloud API token | Do not configure a dbt Cloud connection for the project |
| Container image registry (Docker Hub / GHCR) | `docker pull` when starting or upgrading | **Required at deploy time only.** Not called at runtime. | Container orchestrator | Image pull | Mirror the image into a private registry |
Community Edition (no `LIGHTDASH_LICENSE_KEY`) does not call `api.keygen.sh` or `roadmap.lightdash.com`. Every other row is identical.
## Security-review checklist
Set `RUDDERSTACK_ANALYTICS_DISABLED=true` on every Lightdash container (backend, scheduler, NATS workers). See [Configuration 2](#configuration-2-disable-outbound-rudderstack-telemetry).
Set `RUDDERSTACK_WRITE_KEY` and `RUDDERSTACK_DATA_PLANE_URL` on every Lightdash container. Confirm the browser picks up the new endpoint by inspecting `GET /api/v1/health` — the `rudder` block should show your write key and data plane URL. See [Configuration 3](#configuration-3-send-telemetry-to-your-own-rudderstack).
* `api.keygen.sh` — Enterprise Edition only, required.
* `roadmap.lightdash.com` — only if you enable the organization roadmap.
* `analytics.lightdash.com` — only if you leave default telemetry on.
* Your warehouse endpoint, S3 endpoint, IdP, SMTP host, AI provider endpoints, Slack, GitHub, and sandbox provider endpoints — whichever features you use.
Everything else can be blocked. See the [production deployment checklist](/self-host/production-deployment-checklist#security-hardening) for a starting egress policy.
* **AI providers:** point `ANTHROPIC_BASE_URL`, `OPENAI_BASE_URL`, or `BEDROCK_BASE_URL` at an internal LLM gateway.
* **Sandboxes:** set `SANDBOX_PROVIDER=aws-lambda-microvms` or `azure-container-apps` to keep sandbox execution inside your cloud account.
* **Sentry:** leave `SENTRY_BE_DSN` and `SENTRY_FE_DSN` unset, or point them at your own Sentry instance.
* **GitHub:** connect a GHES or self-hosted GitLab instead of GitHub.com.
* **MCP:** register only internal MCP endpoints.
* **Google Sheets, Slack, dbt Cloud:** leave disabled.
* `GET /api/v1/health` returns the effective `rudder` and `sentry` config the frontend will use.
* Backend startup logs record whether Sentry initialized and whether license validation succeeded.
* Run a network capture or eBPF/Cilium egress log against the backend, scheduler, and worker pods to confirm the observed destinations match the allow-list.
* Query the `Instance health` page in Lightdash for the running configuration snapshot.
## Further reading
* [Environment variables — Analytics & Event Tracking](/self-host/customize-deployment/environment-variables#analytics--event-tracking)
* [Enterprise features and license validation](/self-host/enterprise-features)
* [Production deployment checklist](/self-host/production-deployment-checklist) — includes the default egress policy
* [Usage Analytics dashboards and query tags](/workspace-admin/usage-analytics)
* [Prometheus and OpenTelemetry metrics](/self-host/customize-deployment/configure-prometheus-metrics-for-self-hosted-lightdash)
* [OpenTelemetry tracing](/self-host/customize-deployment/configure-opentelemetry-tracing-for-self-hosted-lightdash)
* [AI agents and AI providers](/self-host/enterprise-features/ai-agents)
* [Data apps sandboxes](/self-host/customize-deployment/sandboxes)
* [MCP](/self-host/enterprise-features/mcp)
* [Slack](/self-host/customize-deployment/configure-a-slack-app-for-lightdash)
* [GitHub](/self-host/customize-deployment/configure-github-for-lightdash)
* [External object storage](/self-host/customize-deployment/configure-lightdash-to-use-external-object-storage)
* [Organization roadmap](/self-host/customize-deployment/organization-roadmap)
* [Instance health](/workspace-admin/instance-health)
# Enterprise features
Source: https://docs.lightdash.com/self-host/enterprise-features
Enable Enterprise Edition features on a self-hosted Lightdash instance
🛠 This page is for engineering teams self-hosting their own Lightdash instance. If you want to learn about enterprise features and pricing, go to [lightdash.com/pricing](https://lightdash.com/pricing).
An enterprise license key unlocks Lightdash Enterprise Edition features on a self-hosted instance. The key on its own enables nothing: each feature has its own environment flag, and you turn on only the ones you use. This page covers [getting](#get-a-license-key) and [applying](#apply-the-license-key) the key, then each feature — the ones that need a flag or two are enabled right here, and the larger ones ([AI agents](#ai-agents), [data apps](#data-apps), [AI writeback](#ai-writeback), [MCP](#mcp), and [pre-aggregates](#pre-aggregates)) each have a dedicated setup page linked from their section.
## Get a license key
1. **Contact the enterprise team** — [schedule a call](https://calendly.com/lightdash-cloud/enterprise?utm_source=docs\&utm_medium=referral\&utm_campaign=enterprise_licensing\&utm_content=license_key_page) to discuss your requirements.
2. **Receive your key** — the Lightdash team will share the key with you, typically via 1Password.
## Apply the license key
Set `LIGHTDASH_LICENSE_KEY` on **every** Lightdash container: backend, scheduler, and workers.
```bash theme={null}
LIGHTDASH_LICENSE_KEY=your-enterprise-license-key-here
```
```yaml theme={null}
secrets:
LIGHTDASH_LICENSE_KEY: your-enterprise-license-key-here
```
Then update your deployment:
```bash theme={null}
helm upgrade lightdash lightdash/lightdash -n lightdash -f values.yaml
```
```yaml theme={null}
services:
lightdash:
environment:
- LIGHTDASH_LICENSE_KEY=your-enterprise-license-key-here
```
Restart every container after adding the key.
## License validation and network requirements
On server start, Lightdash validates the license key by making an outbound HTTPS request to the [Keygen](https://keygen.sh) license validation API:
* **Endpoint:** `https://api.keygen.sh`
* **Purpose:** validates the key provided in `LIGHTDASH_LICENSE_KEY`
* **When:** on every Lightdash server start
Your Lightdash instance must be able to reach `https://api.keygen.sh` on server start. This is the only external network request **required** to run Lightdash with enterprise features. If your environment restricts outbound traffic, allow-list this endpoint in your firewall or proxy.
If you also enable the optional [organization roadmap](/self-host/customize-deployment/organization-roadmap), the backend makes outbound requests to `https://roadmap.lightdash.com` — allow-list that domain too.
No external code is pulled during validation — the request only checks the key and returns the result. For the exact request payload and how it fits into the broader self-hosted network picture, see [Data flows and telemetry — Enterprise license validation](/self-host/data-flows-and-telemetry#enterprise-license-validation).
## Verify the license
After applying the key and restarting:
1. Log in as an admin user.
2. Open **Settings → Organization**.
3. Confirm the **Enterprise Edition** badge is present.
## Features
### AI agents
[AI Analyst and AI agents](/agents) bring natural-language querying, AI-generated charts, and agents that answer questions in-app and in Slack. Set `AI_COPILOT_ENABLED=true` and configure a model provider — see [AI agents](/self-host/enterprise-features/ai-agents). Every other AI feature below assumes this one is enabled first.
### Data apps
[Data apps](/data-apps) are AI-generated React apps, built inside an isolated sandbox and served from an S3-compatible bucket. Set `APPS_RUNTIME_ENABLED=true` with a sandbox provider, a model provider, and a bucket — see [data apps](/self-host/enterprise-features/data-apps).
### AI writeback
[AI writeback](/agents/ai-writeback) lets an agent edit the dbt repository behind a project and open a pull request with the change. Configure the writeback sandbox and its Anthropic key — see [AI writeback](/self-host/enterprise-features/ai-writeback).
### MCP
Lightdash can expose a [Model Context Protocol server](/agents/lightdash-mcp) at `/api/v1/mcp` for AI assistants to query the semantic layer. Set `MCP_ENABLED=true` — see [MCP](/self-host/enterprise-features/mcp) for connecting assistants and network requirements.
### Multi-agent Slack channels
Beta [Multi-agent Slack channels](/agents/set-up-agents#slack-channels-single-agent-vs-multi-agent) let one Slack channel host every AI agent in the organization, with the [AI Router](/agents/enable-ai-router) picking the best-fit agent per question. Requires [AI agents](#ai-agents) and the [Slack integration](/self-host/customize-deployment/configure-a-slack-app-for-lightdash). Set:
```bash theme={null}
SLACK_MULTI_AGENT_CHANNEL_ENABLED=true
```
Restart the backend, then designate the channel from the **AI Agents** organization settings — see [setting up a multi-agent Slack channel](/agents/set-up-agents#setting-up-a-multi-agent-slack-channel) for the in-app steps.
### Embedding
[Embedding](/embed/set-up-embedding) renders Lightdash dashboards, charts, data apps, and AI agents inside your own product, authenticated with a JWT signed by an embed secret. Set:
```bash theme={null}
EMBEDDING_ENABLED=true
LIGHTDASH_IFRAME_EMBEDDING_DOMAINS=https://app.example.com,https://reports.example.com
```
`LIGHTDASH_IFRAME_EMBEDDING_DOMAINS` is the comma-separated allow-list of origins (protocol included, no trailing slash) permitted to iframe Lightdash — if an embed URL loads on its own but the iframe stays blank, the host origin is missing from this list. Restart the backend, then generate an embed secret from **Project settings → Embed configuration**. The [embedding variables reference](/self-host/customize-deployment/environment-variables#embedding) covers the optional settings, including the embed event system.
### Pre-aggregates
[Pre-aggregates](/semantic-layer/pre-aggregates) materialize aggregated tables to S3 and route matching queries to DuckDB instead of your warehouse. They need NATS, an S3-compatible bucket, and a dedicated worker deployment alongside the flag — see [pre-aggregates](/self-host/enterprise-features/pre-aggregates).
### Custom roles
[Custom roles](/workspace-admin/custom-roles) let organization admins define roles with a chosen set of permission scopes, beyond the built-in Admin, Developer, Editor, and Viewer roles. Set:
```bash theme={null}
CUSTOM_ROLES_ENABLED=true
```
Restart the backend. Admins can then build and assign roles from **Organization settings → Roles**.
### Service accounts
[Service accounts](/workspace-admin/service-accounts) are userless, scoped credentials for CLI and API access that outlive their creator. Set:
```bash theme={null}
SERVICE_ACCOUNT_ENABLED=true
```
Restart the backend. Admins can then create and rotate tokens from **Organization settings → Service accounts**.
### SCIM
[SCIM 2.0](/workspace-admin/sso/scim) automates user and group provisioning from an identity provider such as Okta, Azure AD, or OneLogin. Set:
```bash theme={null}
SCIM_ENABLED=true
```
When SCIM runs alongside SSO, also set `AUTH_ENABLE_OIDC_TO_EMAIL_LINKING=true` — without it, Lightdash tries to create a new user on SSO sign-in and fails because the email is already taken by the SCIM-provisioned user. Restart the backend, then follow [SCIM integration](/workspace-admin/sso/scim) for the provider-side setup.
### Enterprise SSO providers
SSO providers such as Google Workspace are available on all plans, but Okta, Azure AD, OneLogin, generic OIDC, Snowflake OAuth, and Databricks OAuth require the license key on self-hosted instances — see [Configure SSO for self-hosted Lightdash](/self-host/customize-deployment/use-sso-login-for-self-hosted-lightdash) for the full setup.
For the exhaustive list of environment variables that require an enterprise license key, [search "enterprise" on the environment variables reference](/self-host/customize-deployment/environment-variables).
## Troubleshooting
If enterprise features are unavailable after applying the key:
1. Confirm `LIGHTDASH_LICENSE_KEY` is set on **every** Lightdash container (backend, scheduler, workers).
2. Confirm every container was restarted after adding the key.
3. Confirm the per-feature environment flag is set (for example, `AI_COPILOT_ENABLED=true` for AI Analyst).
4. Confirm the server can reach `https://api.keygen.sh`.
5. Check the Lightdash logs for license-related errors.
6. Contact [enterprise support](https://calendly.com/lightdash-cloud/enterprise) if the issue persists.
## Related documentation
* [Enterprise on-prem setup](/self-host/enterprise-on-prem)
* [Production deployment checklist](/self-host/production-deployment-checklist)
* [Environment variables reference](/self-host/customize-deployment/environment-variables)
* [Configure SSO for self-hosted Lightdash](/self-host/customize-deployment/use-sso-login-for-self-hosted-lightdash)
* [Lightdash Cloud vs. self-hosted](/self-host/lightdash-cloud-vs-self-hosted)
# AI agents
Source: https://docs.lightdash.com/self-host/enterprise-features/ai-agents
Enable AI Analyst and AI agents on a self-hosted Lightdash instance
Enterprise AI Analyst and AI agents need a valid `LIGHTDASH_LICENSE_KEY` set on your instance before any of the configuration below takes effect. See [enterprise features](/self-host/enterprise-features) for applying the key.
[AI Analyst](/agents) powers natural-language querying, AI-generated charts, and [AI agents](/agents/set-up-agents) that answer questions in-app and in Slack. To enable it on a self-hosted instance you need a model provider and the AI Analyst feature flag. Every AI feature that runs on top — [Data apps](/self-host/enterprise-features/data-apps), [AI writeback](/self-host/enterprise-features/ai-writeback), [Autopilot](#autopilot), [MCP](/self-host/enterprise-features/mcp), and [multi-agent Slack channels](/self-host/enterprise-features#multi-agent-slack-channels) — assumes AI Analyst is enabled first.
## Prerequisites
* **Enterprise license** — `LIGHTDASH_LICENSE_KEY` must be set on your instance.
* **A model provider** — OpenAI, Anthropic, Azure AI, OpenRouter, or AWS Bedrock. OpenAI and Anthropic are the most tested.
* **Enough context budget on the chosen model** — AI Analyst sends the project's dbt catalog with every request.
## Enable AI Analyst
Set the main switch and provide credentials for one provider — the minimal setup uses OpenAI, the default provider:
```bash theme={null}
AI_COPILOT_ENABLED=true
OPENAI_API_KEY=
```
AI Analyst starts using the provider as soon as the backend restarts. To use a different provider, set `AI_DEFAULT_PROVIDER` (`openai`, `anthropic`, `azure`, `openrouter`, or `bedrock`) and the matching credentials — see [model providers](#model-providers) below. Optionally, set `ASK_AI_BUTTON_ENABLED=true` to add an "Ask AI" entry point in the app UI; without it, users reach agents from `/ai-agents`.
## Model providers
Each provider's exhaustive variable list lives in the [environment variables reference](/self-host/customize-deployment/environment-variables#ai-analyst); the notes below cover the choices and gotchas per provider.
### OpenAI (default)
Leave `AI_DEFAULT_PROVIDER` unset or set it to `openai`, then set `OPENAI_API_KEY`. `gpt-5.2` is the default model; override it with `OPENAI_MODEL_NAME`. All options: [OpenAI configuration](/self-host/customize-deployment/environment-variables#openai-configuration).
Behind an OpenAI-compatible LLM gateway (LiteLLM, an internal proxy), also set `OPENAI_BASE_URL` to the gateway URL and `OPENAI_MODEL_NAME` to a model your gateway exposes. If the gateway doesn't support streaming (SSE), set `OPENAI_SUPPORTS_STREAMING=false`. If it enforces Zero Data Retention, set `OPENAI_ZERO_DATA_RETENTION=true`.
### Anthropic
Set `AI_DEFAULT_PROVIDER=anthropic` and provide `ANTHROPIC_API_KEY`. `claude-sonnet-4-5` is the default model; override it with `ANTHROPIC_MODEL_NAME`. All options: [Anthropic configuration](/self-host/customize-deployment/environment-variables#anthropic-configuration).
To send Anthropic traffic through a corporate gateway, add `ANTHROPIC_BASE_URL`. See [Corporate LLM gateways](#corporate-llm-gateways) for the required URL, authentication, and API paths.
### Azure AI
Set `AI_DEFAULT_PROVIDER=azure` and point Lightdash at your deployment with `AZURE_AI_API_KEY`, `AZURE_AI_ENDPOINT`, `AZURE_AI_API_VERSION`, and `AZURE_AI_DEPLOYMENT_NAME`. For reasoning-capable deployments (e.g. `o3`), also set `AZURE_AI_DEPLOYMENT_SUPPORTS_REASONING=true`. All options: [Azure AI configuration](/self-host/customize-deployment/environment-variables#azure-ai-configuration).
### OpenRouter
Set `AI_DEFAULT_PROVIDER=openrouter` and provide `OPENROUTER_API_KEY`; override the default model with `OPENROUTER_MODEL_NAME`. All options: [OpenRouter configuration](/self-host/customize-deployment/environment-variables#openrouter-configuration).
### AWS Bedrock
Set `AI_DEFAULT_PROVIDER=bedrock` and `BEDROCK_REGION` (required — the AWS region where the target model is available), then authenticate with either `BEDROCK_API_KEY` or a `BEDROCK_ACCESS_KEY_ID`/`BEDROCK_SECRET_ACCESS_KEY` IAM pair — not both. Enable the corresponding models in the selected region before restarting Lightdash. All options: [AWS Bedrock configuration](/self-host/customize-deployment/environment-variables#aws-bedrock-configuration).
To send Bedrock traffic through a corporate gateway, add `BEDROCK_BASE_URL`. The gateway must support the Bedrock APIs used by every enabled Lightdash consumer. See [Corporate LLM gateways](#corporate-llm-gateways).
`AI_DEFAULT_PROVIDER` is instance-wide. Setting it to `bedrock` also routes the selected [Data apps](/self-host/enterprise-features/data-apps) coding agent through Bedrock. `APPS_CODING_AGENT` chooses Claude or Codex; it does not change the provider.
## Corporate LLM gateways
Lightdash can route AI Analyst, Autopilot, and Data app model traffic through an HTTP(S) corporate gateway. Use the base URL for the protocol your gateway exposes:
* `OPENAI_BASE_URL` for an OpenAI-compatible gateway.
* `ANTHROPIC_BASE_URL` for an Anthropic Messages gateway.
* `BEDROCK_BASE_URL` for a Bedrock-compatible gateway.
The gateway URL must include a scheme and must not contain credentials, query parameters, or a fragment. The Lightdash backend and any Data app sandbox must be able to resolve and reach the gateway hostname.
### Anthropic-compatible gateway
Configure `ANTHROPIC_BASE_URL` before the API's `/v1` segment. Lightdash accepts a trailing `/v1` and removes it, but using the unversioned base avoids ambiguity:
```bash theme={null}
AI_COPILOT_ENABLED=true
AI_DEFAULT_PROVIDER=anthropic
ANTHROPIC_API_KEY=
ANTHROPIC_BASE_URL=https://llm-gateway.example.com/anthropic
```
When `ANTHROPIC_BASE_URL` is set, Lightdash sends `ANTHROPIC_API_KEY` as an `Authorization: Bearer` token. Without a gateway, direct Anthropic requests continue to use the standard `x-api-key` header.
Lightdash queries `/v1/models` to determine which models the credential can access. If the gateway does not expose the Models API, set `ANTHROPIC_AVAILABLE_MODELS` to the exact comma-separated model names the gateway accepts.
An organization-level BYO Anthropic key cannot be combined with an instance-wide `ANTHROPIC_BASE_URL`. Remove the organization key to use the gateway, or remove the gateway URL to route that organization through its own Anthropic account.
### Bedrock-compatible gateway
Configure the gateway together with the existing Bedrock provider settings:
```bash theme={null}
AI_COPILOT_ENABLED=true
AI_DEFAULT_PROVIDER=bedrock
BEDROCK_REGION=us-east-1
BEDROCK_API_KEY=
BEDROCK_BASE_URL=https://llm-gateway.example.com/bedrock
```
The backend Bedrock provider still requires one supported credential mode: `BEDROCK_API_KEY`, static AWS credentials, or `BEDROCK_USE_DEFAULT_CREDENTIALS=true`. A custom Bedrock gateway used by Codex requires `BEDROCK_API_KEY`; Codex cannot combine an overridden endpoint with IAM/SigV4 credentials.
For Claude Data apps, set `CLAUDE_CODE_SKIP_BEDROCK_AUTH=true` only when the gateway accepts Claude Code requests without AWS authentication. This setting suppresses credentials inside Claude Code; it does not remove the backend provider's credential requirement.
### Required gateway APIs
One base URL can serve multiple consumers only if the gateway implements every wire protocol those consumers use:
| Consumer | Required path relative to the configured base |
| ------------------------------------------ | ------------------------------------------------------------------------------------- |
| AI Analyst and Autopilot through Anthropic | Anthropic Messages: `/v1/messages` |
| Anthropic model discovery | Anthropic Models: `/v1/models` |
| AI Analyst and Autopilot through Bedrock | Bedrock Converse: `/model/{model-id}/converse` or `/model/{model-id}/converse-stream` |
| Bedrock verified-answer embeddings | Bedrock Invoke: `/model/{model-id}/invoke` |
| Claude Data apps through Anthropic | Anthropic Messages: `/v1/messages` |
| Claude Data apps through Bedrock | Bedrock Invoke: `/model/{model-id}/invoke-with-response-stream` |
| Codex Data apps through a Bedrock gateway | OpenAI Responses: `/responses` |
A gateway that supports only Bedrock Converse, for example, can serve AI Analyst chat but cannot serve Claude Data app generation. For the coding-agent configuration and model naming rules, see [Data apps](/self-host/enterprise-features/data-apps).
These gateway settings do not reroute AI writeback or the onboarding agent. Autopilot uses the same provider configuration as AI Analyst. Its scheduler must also be able to reach the gateway.
### Validate before rollout
1. Confirm the Lightdash backend and any scheduler running Autopilot can resolve and connect to the gateway hostname.
2. Open AI Analyst and send a short prompt with the configured model. If model discovery fails, verify `/v1/models` or set `ANTHROPIC_AVAILABLE_MODELS`.
3. If Data apps are enabled, generate a small app with each coding agent you plan to support. This verifies the sandbox's separate network path and API wire.
4. If Autopilot is enabled, run it on a disposable project and check its recorded provider, model, and outcome. A successful configuration check does not prove the gateway accepts a model request.
5. Check gateway access logs for the expected paths in the table above. Redact authorization headers and tokens from logs.
6. Restrict direct provider egress only after all enabled consumers succeed through the gateway.
## Verified answers
Verified answers use vector embeddings to match new questions to previously validated ones. Enable embeddings with `AI_EMBEDDING_ENABLED=true` and pick an embedding provider with `AI_DEFAULT_EMBEDDING_PROVIDER` (`openai`, `bedrock`, or `azure`). The embedding provider can differ from the chat provider — see [verified answers](/agents/verified-answers) for how they're used at query time.
## Autopilot
[Autopilot](/agents/autopilot) runs scheduled maintenance for each enabled project using the AI SDK runtime. It needs AI Analyst enabled and a provider configured through the [instance model settings](#model-providers) or [organization AI providers and models](/agents/set-up-agents#bring-your-own-ai-providers-and-models). It uses the organization's visible, available default model, then the configured provider default if available, then another available model. Azure uses its configured deployment directly. There is no per-project model override.
Enable the Autopilot feature on every API and scheduler process:
```bash theme={null}
LIGHTDASH_ENABLE_FEATURE_FLAGS=ai-autopilot
```
Append `ai-autopilot` if the enable list already contains other flags, and remove it from `LIGHTDASH_DISABLE_FEATURE_FLAGS` if present. Restart the API and scheduler after changing environment variables. Keep their provider and Autopilot configuration consistent; the scheduler executes scheduled runs and needs access to the selected provider or gateway.
A user with project management permissions can then enable Autopilot from the project's Home page. Runs act as the user who enabled it and require that user to retain project management permissions. The AI SDK runtime needs no separate service-account token for Autopilot.
Setup and the activity page show the current provider, model, and key source. Enabling Autopilot checks that a model can be configured, without making a model request. Invalid credentials, unavailable deployments, and gateway errors can still fail the first run. Changing organization settings changes the next run's selection; previous runs retain their recorded model.
### Schedule and limits
Choose a schedule per project in the UI. `MANAGED_AGENT_SCHEDULE` supplies the fallback cron schedule, defaulting to `0 0 * * *`. `MANAGED_AGENT_SESSION_TIMEOUT_MS` defaults to 600000 (10 minutes), and `MANAGED_AGENT_MAX_STEPS` defaults to 120 model steps per run. The deadline requests cancellation and prevents queued actions from starting; a content write already in progress must finish its bookkeeping. These limits do not set a dollar spending cap. See [provider and usage](/agents/autopilot#ai-provider-and-usage).
### Model qualification and cleanup
Autopilot only flags or deletes content on models that are qualified for it. Every other model runs in observe mode. When `MANAGED_AGENT_VALIDATED_MODELS` is unset, these models are qualified for `cleanup`:
| Provider | Models |
| -------------- | ---------------------------------------------------------------- |
| Anthropic | Claude Opus 5, Claude Opus 4.8, Claude Opus 4.7, Claude Sonnet 5 |
| Amazon Bedrock | Claude Opus 5, Claude Opus 4.7, Claude Sonnet 5 |
| OpenAI | GPT-5.6 Sol, GPT-5.5, GPT-5.4 |
On Anthropic and Bedrock, Autopilot runs on the newest Claude Opus your organization may use, unless the organization default is already a Claude Opus. Other providers follow the organization default.
Azure deployments, Google, OpenRouter and any model not listed above start in observe mode. To qualify one, set `MANAGED_AGENT_VALIDATED_MODELS` to a JSON array of exact provider/model pairs with a mode of `observe`, `flag`, or `cleanup`. Setting the variable replaces the built-in list, so include the built-in models you still want; an empty value qualifies no model. Duplicate qualifications use the most restrictive mode; malformed configuration rejects startup. Qualify a model on disposable representative content before increasing its permissions; use the exact IDs shown in Autopilot, including Azure deployment names or Bedrock inference-profile prefixes.
Autopilot uses the more restrictive of the project's requested cleanup mode and the model's qualification. A downgrade appears in setup and the run summary. **Observe mode does not mean read-only:** chart creation and repair still follow the project's capability toggles. Disable those capabilities as well when evaluating without content changes.
### Upgrading from the hosted runtime
Lightdash 2.262.0 removed the hosted runtime. From that version Autopilot always runs on your configured AI provider.
* **Nothing to change to keep Autopilot running.** Provider credentials come from the shared AI configuration described above. Keep the schedule and timeout settings, and review the step limit and model qualifications.
* **Remove the old settings.** The hosted-runtime credential, the remote skill list and `MANAGED_AGENT_RUNTIME` are ignored. Lightdash logs a warning at startup for each one that is still set.
* **No configuration rollback.** Setting `MANAGED_AGENT_RUNTIME=anthropic-managed` cannot restore the hosted runner. To roll back, redeploy a release before 2.262.0 with the credentials it needs.
On releases 2.255.0 to 2.261.x both runtimes exist and the AI SDK runner is the default. `MANAGED_AGENT_RUNTIME` selects the runner there and must match on the API and scheduler processes.
## Costs
Self-hosting AI Analyst means you pay the selected model provider directly. Every user question sends the project's dbt catalog plus conversation history to the provider, so long conversations against large projects use noticeably more tokens than one-shot prompts. Query results also contribute to usage when [data access](/agents/data-access#data-access-modes) is enabled. Provider dashboards expose usage — set spend limits before rolling out.
To keep prompts smaller:
* [Limit each agent to the explores and fields it needs](/agents/data-access#limiting-access-to-specific-explores-and-fields).
* Start a new thread when you change subjects so unrelated conversation history is not sent again.
* Keep [always-included knowledge documents](/agents/effective-analytics-with-agents#always-include-in-context) short.
* Enable compact filter expressions as described below.
### Compact filter expressions
Compact filter expressions replace verbose structured filter schemas for AI agent and Model Context Protocol (MCP) query tools with a smaller expression syntax. This reduces the filter-related tool definitions and inputs sent to the model. Total savings vary by model, conversation history, caching, and tool usage; the setting does not reduce the catalog, existing conversation history, or query results.
Lightdash Cloud enables compact filter expressions automatically. On a self-hosted deployment, enable the feature on every backend/API replica and on any scheduler or worker process that executes AI agent jobs, then redeploy or recreate those containers:
```bash theme={null}
LIGHTDASH_ENABLE_FEATURE_FLAGS=ai-filter-expressions
```
If `LIGHTDASH_ENABLE_FEATURE_FLAGS` already contains other flags, append `ai-filter-expressions` to its comma-separated list. Remove it from `LIGHTDASH_DISABLE_FEATURE_FLAGS` if present. Lightdash reads both variables at process startup.
Users do not need to create new agents or threads: the next response uses compact filter expressions, including in an existing thread. After enabling the feature for MCP, reconnect or refresh each client so it reloads the query tool definitions.
## Permissions
AI Analyst follows the standard project role model: any user with query access to a project can use AI Analyst there. Restrict access at the project level via [roles and groups](/workspace-admin/roles). To limit AI Analyst to specific projects across the instance, set `AI_COPILOT_ALLOWED_PROJECT_UUID` to a comma-separated list of project UUIDs.
## Troubleshooting
**The "Ask AI" button doesn't appear.**
Confirm `AI_COPILOT_ENABLED=true`, `LIGHTDASH_LICENSE_KEY` is set, and `ASK_AI_BUTTON_ENABLED=true` if you want the top-bar entry point. Users without query access to any project also won't see it.
**AI Analyst returns provider authentication errors.**
Check the API key for the selected `AI_DEFAULT_PROVIDER`. For Azure, verify `AZURE_AI_ENDPOINT`, `AZURE_AI_API_VERSION`, and `AZURE_AI_DEPLOYMENT_NAME` all match a deployment your key can call. For Bedrock, confirm the region has the target model enabled for your account.
**Multi-step conversations fail against an OpenAI-compatible gateway.**
If the gateway enforces Zero Data Retention, set `OPENAI_ZERO_DATA_RETENTION=true`. If it doesn't support SSE, set `OPENAI_SUPPORTS_STREAMING=false`.
**Verified answers never match.**
Confirm `AI_EMBEDDING_ENABLED=true` and that the embedding provider credentials are set. Tune `AI_VERIFIED_ANSWER_SIMILARITY_THRESHOLD` if legitimate matches fall below the default 0.6 similarity cutoff.
# AI writeback
Source: https://docs.lightdash.com/self-host/enterprise-features/ai-writeback
Enable AI writeback on a self-hosted Lightdash instance
Enterprise AI writeback needs a valid `LIGHTDASH_LICENSE_KEY` set on your instance ([enterprise features](/self-host/enterprise-features) covers applying the key) before any of the configuration below takes effect.
AI writeback lets an AI agent edit the semantic layer repository that backs a project and open a pull request with the change. The edits run inside an isolated [sandbox](/self-host/customize-deployment/sandboxes). Validation depends on the project format; see [how AI writeback works](/agents/ai-writeback#how-it-works). To enable the feature on a self-hosted Lightdash instance you need a configured sandbox provider, an Anthropic API key, and repository access.
For an overview of what the feature does and how to use it in chat, see [AI writeback](/agents/ai-writeback).
## Prerequisites
* **Enterprise license** — `LIGHTDASH_LICENSE_KEY` must be set on your instance.
* **A configured sandbox provider** — see [sandboxes](/self-host/customize-deployment/sandboxes). The writeback agent edits files there.
* **An Anthropic API key** — sign up at [console.anthropic.com](https://console.anthropic.com/) and create an API key. Claude is the model that drives the writeback agent.
* **Repository access** — configure a supported provider and semantic layer format using the [AI writeback prerequisites](/agents/ai-writeback#prerequisites).
## Configuration
Add the following environment variables to your Lightdash deployment:
| Variable | Example | Purpose |
| -------------------------------- | ---------------------------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `E2B_AI_WRITEBACK_TEMPLATE_NAME` | `lightdash/lightdash-ai-writeback` | Lightdash's public E2B template for writeback — pulls our prebuilt sandbox image so you don't have to build one yourself. The `lightdash/` prefix points at our public team template; without it E2B looks in your own team and won't find the image. |
| `ANTHROPIC_API_KEY` | `` | Your [Anthropic](https://console.anthropic.com/) API key. Claude is the model that drives the writeback agent. |
Restart the backend. Users with AI Analyst access on a project connected to a supported git host will see the agent offer to open a pull request when they ask for a change that belongs in the semantic layer repo.
### Optional configuration
| Variable | Default | Purpose |
| ------------------------------- | ------------------------------------------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `E2B_AI_WRITEBACK_TEMPLATE_TAG` | Running Lightdash version (e.g. `0.2917.1`) | Pins the writeback sandbox image to a specific tag of the E2B template. Each Lightdash release publishes a matching tag so the backend and sandbox stay in sync. Override this to roll back to a previous build or set it to an empty string to use the template's `default` tag. Most operators don't need to touch this. |
## Supported dbt versions
This section applies to dbt projects. Native Lightdash YAML uses [native validation](/semantic-layer/yaml#ai-writeback-and-issues) and needs no dbt installation.
The writeback sandbox runs `lightdash compile` using the dbt version configured on the project, so validation behaves the same way it would locally. The sandbox ships with dbt **1.8, 1.9, 1.10, 1.11, and 1.12** preinstalled:
* Projects pinned to one of those versions compile against that exact version.
* Projects set to `latest` compile against the newest available version (currently 1.12).
* Projects pinned to a version below 1.8 (which is end-of-life upstream) are clamped up to 1.8 inside the sandbox.
You don't need to configure anything — the version is selected automatically from the project's dbt connection settings.
## Costs
Self-hosting AI writeback means you pay your sandbox provider and Anthropic directly:
* **Your sandbox provider** bills for sandbox runtime. A typical writeback runs for 1–5 minutes while the agent inspects files, edits them, and runs `lightdash compile`.
* **Anthropic** bills per token. Each writeback sends the project's dbt catalog and the self-contained instruction generated from the user's request to Claude, plus any files the agent reads while making the change.
Both providers expose usage dashboards. We recommend setting spend limits on both.
## Permissions
AI writeback is gated by the user's project role. Anyone asking the agent to open a pull request needs at least [project Developer](/workspace-admin/roles) permissions on the project the agent is connected to. Git authentication depends on the provider:
* **GitHub** commits are signed by the Lightdash GitHub App.
* **GitLab** commits are pushed over OAuth and are unsigned.
* **Bitbucket Cloud** uses the [project API token owner's identity](/get-started/quickstart/connect-project#bitbucket-cloud-api-token).
## Troubleshooting
**The agent never offers to open a pull request.**
Check that `LIGHTDASH_LICENSE_KEY` is set and the project meets the [repository and permission prerequisites](/agents/ai-writeback#prerequisites).
**Writebacks fail immediately with a sandbox creation error.**
Check your [sandbox provider's](/self-host/customize-deployment/sandboxes) credentials, and verify `E2B_AI_WRITEBACK_TEMPLATE_NAME` is set to `lightdash/lightdash-ai-writeback` (the `lightdash/` prefix is required to pull our public template — a bare name resolves to your own team). If you've overridden `E2B_AI_WRITEBACK_TEMPLATE_TAG`, double-check the tag exists.
**Writebacks fail mid-run with an Anthropic error.**
Check your Anthropic account usage limits and confirm `ANTHROPIC_API_KEY` is valid. Long-running writebacks can hit rate limits on lower-tier Anthropic plans.
**The agent surfaces a `GitHub App is not installed` or `GitLab App is not installed` error.**
Install the Lightdash GitHub App on the repository (from the project's dbt connection settings) or connect the Lightdash GitLab App for your organization, then ask the agent to try again.
# Data apps
Source: https://docs.lightdash.com/self-host/enterprise-features/data-apps
Configure a self-hosted Lightdash instance to run data apps
Enterprise Data apps need a valid `LIGHTDASH_LICENSE_KEY` set on your instance before any of the configuration below takes effect. See [enterprise features](/self-host/enterprise-features) for applying the key.
[Data apps](/data-apps) are AI-generated React code, built inside an isolated sandbox and stored in an S3-compatible bucket. To enable the feature on a self-hosted instance you need a sandbox provider, a model provider, and a bucket Lightdash can write to. Claude is the default coding agent, and you can run data apps with OpenAI Codex instead.
Sandbox providers are configured separately and cover several runtimes — E2B, AWS Lambda MicroVMs, Azure Container Apps, and Google Cloud Run. See [sandboxes](/self-host/customize-deployment/sandboxes) for the full provider setup; this page covers everything else data apps need.
## Prerequisites
* **Enterprise license** - `LIGHTDASH_LICENSE_KEY` must be set on your instance.
* **S3-compatible storage** - a bucket Lightdash can write to for app source and built artifacts. If your instance isn't already using S3, [set that up first](/self-host/customize-deployment/configure-lightdash-to-use-external-object-storage).
* **A configured sandbox provider** - see [sandboxes](/self-host/customize-deployment/sandboxes).
* **A coding-agent provider** - Anthropic, OpenAI, or an [AWS Bedrock](https://aws.amazon.com/bedrock/) account with access to the model used by your selected agent.
## Configuration
Add the following environment variables to your Lightdash deployment:
| Variable | Example | Purpose |
| ---------------------- | ---------------- | -------------------------------------------------------------------------------------------------------------------------------------------- |
| `APPS_RUNTIME_ENABLED` | `true` | Main switch for the feature. |
| `APPS_CODING_AGENT` | `claude` | Coding agent used to generate apps. Supports `claude` (default) or `codex`. |
| `APPS_S3_BUCKET` | `lightdash-apps` | Bucket Lightdash will write app source and built artifacts to. Falls back to `S3_BUCKET` if unset - set this if you want a dedicated bucket. |
Choose one of the coding-agent and provider combinations below.
### Claude through Anthropic (default)
Leave `APPS_CODING_AGENT` unset or set it to `claude`, then provide an Anthropic API key.
| Variable | Example | Purpose |
| -------------------- | ------------------------------------------- | ----------------------------------------------------------------------------------------------------------------------------------------- |
| `APPS_CODING_AGENT` | `claude` | Optional. Claude is used when this variable is unset. |
| `ANTHROPIC_API_KEY` | `` | Your [Anthropic](https://console.anthropic.com/) API key. Used for Data apps when `AI_DEFAULT_PROVIDER` is not `bedrock`. |
| `ANTHROPIC_BASE_URL` | `https://llm-gateway.example.com/anthropic` | Optional. Routes Claude and Anthropic-backed AI Analyst traffic through an Anthropic-compatible gateway. Configure the base before `/v1`. |
Users can choose Sonnet, Opus, or Haiku for each generation. Sonnet is the default.
With `ANTHROPIC_BASE_URL`, Lightdash uses `ANTHROPIC_API_KEY` as a bearer token and allows the gateway hostname through the sandbox firewall. The gateway must implement Anthropic Messages. See [Corporate LLM gateways](/self-host/enterprise-features/ai-agents#corporate-llm-gateways) for authentication and API-path requirements.
### Codex through OpenAI
Set `APPS_CODING_AGENT=codex` and provide an OpenAI API key. Codex uses OpenAI directly whenever `AI_DEFAULT_PROVIDER` is not `bedrock`. You do not need an Anthropic API key for Data app generation in this mode.
| Variable | Example | Purpose |
| ------------------- | --------------------------- | ---------------------------------------------------------------------------------------------------------------------------- |
| `APPS_CODING_AGENT` | `codex` | Runs Data app generation with OpenAI Codex. |
| `OPENAI_API_KEY` | `` | Your [OpenAI API key](https://platform.openai.com/api-keys). |
| `OPENAI_BASE_URL` | `https://api.openai.com/v1` | Optional. Override this for an OpenAI-compatible gateway that supports the Responses API and the configured Codex model IDs. |
Users can choose GPT-5.6 Sol, Terra, or Luna for each generation. Terra is the default.
### Claude or Codex through Bedrock
Set `AI_DEFAULT_PROVIDER=bedrock` to route the selected coding agent through AWS Bedrock or a Bedrock-compatible gateway. Set `APPS_CODING_AGENT` to `claude` or `codex`, then configure the region and credentials used by the selected route.
| Variable | Example | Purpose |
| ------------------------------- | ----------------------------------------- | --------------------------------------------------------------------------------------------------------------------------------------------- |
| `APPS_CODING_AGENT` | `claude` | Selects `claude` or `codex`. Defaults to `claude`. |
| `AI_DEFAULT_PROVIDER` | `bedrock` | Switches both AI Analyst and Data apps to Bedrock. |
| `BEDROCK_REGION` | `us-east-2` | **(Required)** AWS region where the selected model is available. |
| `BEDROCK_API_KEY` | `` | Bedrock API key (bearer token). Simplest option - use this *or* the IAM keys below, not both. |
| `BEDROCK_ACCESS_KEY_ID` | `` | AWS access key ID. Use with `BEDROCK_SECRET_ACCESS_KEY` as an alternative to `BEDROCK_API_KEY`. |
| `BEDROCK_SECRET_ACCESS_KEY` | `` | AWS secret access key paired with `BEDROCK_ACCESS_KEY_ID`. |
| `BEDROCK_SESSION_TOKEN` | `` | Optional. AWS session token for temporary IAM credentials. |
| `BEDROCK_BASE_URL` | `https://llm-gateway.example.com/bedrock` | Optional. Routes backend Bedrock calls and the selected Data app coding agent through a corporate gateway. |
| `CLAUDE_CODE_SKIP_BEDROCK_AUTH` | `true` | Optional. For Claude only, omit AWS authentication when the configured gateway performs upstream authentication. Requires `BEDROCK_BASE_URL`. |
For Claude, enable the Claude models you want to use in the selected region. For Codex, enable the corresponding OpenAI model IDs, such as `openai.gpt-5.6-terra`, through the Amazon Bedrock Mantle path. See [OpenAI's Amazon Bedrock guide](https://learn.chatgpt.com/docs/amazon-bedrock) for supported models and authentication requirements.
The Bedrock credentials are the same ones used by AI Analyst - see [AWS Bedrock configuration](/self-host/customize-deployment/environment-variables#aws-bedrock-configuration) for the full reference. The sandbox firewall automatically allows only the provider endpoints required for the selected agent and region.
When `BEDROCK_BASE_URL` is set, the gateway must support the protocol used by each selected consumer. Claude uses Bedrock's streaming Invoke API, while Codex uses the OpenAI Responses API through a custom gateway provider. Codex requires `BEDROCK_API_KEY` in gateway mode and sends model IDs with the `openai.` prefix, such as `openai.gpt-5.6-terra`; the gateway must register or translate those names. See the [required gateway APIs](/self-host/enterprise-features/ai-agents#required-gateway-apis) matrix.
`AI_DEFAULT_PROVIDER` is an instance-wide setting. Setting it to `bedrock` also routes AI Analyst through Bedrock. `APPS_CODING_AGENT` changes only the coding agent used by Data apps.
### Gateway networking
The Lightdash backend and the Data app sandbox make separate connections to the gateway. E2B and Azure Sandboxes receive the configured gateway hostname in their dynamic egress allowlist. AWS Lambda MicroVMs use a pre-provisioned egress connector instead, so `LAMBDA_MICROVM_EGRESS_CONNECTOR_ARN` must permit the gateway hostname. Docker and Cloud Run follow their existing runtime network policy. See [LLM gateway egress](/self-host/customize-deployment/sandboxes#llm-gateway-egress).
Restart the backend. The "Data apps" entry will appear in the **New** menu for users with the appropriate permission scope.
### Optional configuration
| Variable | Default | Purpose |
| -------- | ------- | ------- |
## Costs
Self-hosting data apps means you pay your sandbox provider and your selected model provider directly:
* **Your sandbox provider** bills for sandbox runtime. A typical build runs for 1–15 minutes; sandboxes are paused between iterations and resumed on follow-up prompts.
* **Anthropic, OpenAI, or AWS Bedrock** bills per token. Each generation sends the project's dbt catalog and the user's prompt to the selected coding agent, plus any attached charts, dashboards, or images.
Both your sandbox provider and your model provider expose usage dashboards. We recommend setting spend limits on both before rolling the feature out to your users.
## Permissions
Data apps follow the same space-based permission model as charts and dashboards. The relevant scopes (`view:DataApp`, `create:DataApp`, `manage:DataApp`) are bundled into the default system roles - but on enterprise instances using custom roles, you'll need to grant them explicitly. See [Custom roles](/workspace-admin/custom-roles) for details.
## Troubleshooting
**The "Data apps" entry doesn't appear in the New menu.**
Check that `APPS_RUNTIME_ENABLED=true`, `LIGHTDASH_LICENSE_KEY` is set, and the signed-in user has the `create:DataApp` scope.
**Builds fail immediately with a sandbox creation error.**
Check your sandbox provider's credentials and template configuration — see [sandboxes](/self-host/customize-deployment/sandboxes).
**Builds fail mid-generation with an Anthropic error.**
For direct Anthropic, check account usage limits and confirm `ANTHROPIC_API_KEY` is valid. With `ANTHROPIC_BASE_URL`, confirm the sandbox can reach the gateway and that it accepts bearer authentication at `/v1/messages`.
**Codex builds fail with an OpenAI authentication or model error.**
Confirm `APPS_CODING_AGENT=codex`. For OpenAI, verify `OPENAI_API_KEY`; if `OPENAI_BASE_URL` is set, the gateway must support the Responses API and the model IDs shown in the Data app model picker. For a Bedrock gateway, verify `BEDROCK_API_KEY`, `/responses` support, and the `openai.`-prefixed model ID.
**Builds fail mid-generation with a Bedrock error.**
Confirm `BEDROCK_REGION` is set to a region where the selected agent's model is available, and that either `BEDROCK_API_KEY` or the `BEDROCK_ACCESS_KEY_ID` / `BEDROCK_SECRET_ACCESS_KEY` pair is valid. If you use IAM credentials, the principal must have permission to invoke the selected model. With `BEDROCK_BASE_URL`, also confirm the gateway and sandbox allowlist support the selected agent's API wire. Codex requires an exact OpenAI Bedrock model ID such as `openai.gpt-5.6-terra`.
# MCP
Source: https://docs.lightdash.com/self-host/enterprise-features/mcp
Enable the Model Context Protocol server on a self-hosted Lightdash instance
Enterprise The MCP server needs a valid `LIGHTDASH_LICENSE_KEY` set on your instance — see [enterprise features](/self-host/enterprise-features) for applying the key. Without it, the MCP service is never registered and `/api/v1/mcp` returns an error.
Lightdash exposes a [Model Context Protocol](/agents/lightdash-mcp) server that AI assistants (Claude, ChatGPT, Codex, Cursor, Claude Code, and any other MCP client) connect to for querying the semantic layer. Enabling it on a self-hosted instance is a one-flag change.
## Prerequisites
* **Enterprise license** — `LIGHTDASH_LICENSE_KEY` must be set on your instance.
* **HTTPS** — MCP clients connect over HTTPS. See [secure Lightdash with HTTPS](/self-host/customize-deployment/secure-lightdash-with-https) if your instance is HTTP-only.
## Enable MCP
Set the following environment variable on your Lightdash deployment:
```bash theme={null}
MCP_ENABLED=true
```
The OAuth server is built into Lightdash, so no external OAuth configuration is needed — the authentication flow works the same way as it does on Lightdash Cloud.
## Optional configuration
| Variable | Description |
| :---------------------- | :----------------------------------------------------------------------------------------------------------------- |
| `MCP_ENABLED` | Enables the Lightdash Model Context Protocol (MCP) server. Requires `LIGHTDASH_LICENSE_KEY`. (default=false) |
| `MCP_RUN_SQL_MAX_LIMIT` | Maximum number of rows the MCP `run_sql` tool can return. Falls back to `AI_COPILOT_MAX_QUERY_LIMIT`, then `1000`. |
Set `MCP_RUN_SQL_MAX_LIMIT` to override the default row cap for the `run_sql` tool, tuned independently of AI Analyst. For example:
```bash theme={null}
MCP_RUN_SQL_MAX_LIMIT=5000
```
Requests that ask for more rows are clamped to this limit.
To reduce the filter-related query schema sent to the model, enable [compact filter expressions](/self-host/enterprise-features/ai-agents#compact-filter-expressions). That section covers deployment and client-refresh requirements.
## MCP URL format
Once enabled, the MCP endpoint is available at:
```text theme={null}
https:///api/v1/mcp
```
Replace `` with your instance's domain (e.g. `lightdash.yourcompany.com`).
## Connect an AI assistant
Follow the same steps as Lightdash Cloud, using your self-hosted URL instead of a `.lightdash.cloud` address. See the [Lightdash MCP installation guide](/agents/lightdash-mcp#installation) for detailed instructions per AI assistant.
For example, with Claude Code:
```bash theme={null}
claude mcp add lightdash https:///api/v1/mcp -t http
```
## Network requirements
If your Lightdash instance sits behind a firewall or VPN, ensure the MCP client can reach it over HTTPS. See the [MCP network requirements](/agents/lightdash-mcp#network-requirements) for additional domains that may need allow-listing depending on the AI assistant.
## Troubleshooting
| Symptom | Likely cause |
| ------------------------------------- | ------------------------------------------------------------------------------------- |
| `/api/v1/mcp` returns an error or 404 | `MCP_ENABLED` isn't set to `true`, or `LIGHTDASH_LICENSE_KEY` is missing or invalid |
| OAuth flow fails | The Lightdash instance isn't reachable from the browser performing the OAuth redirect |
| AI assistant can't connect | The MCP URL is wrong or the instance isn't accessible over HTTPS |
# Pre-aggregates
Source: https://docs.lightdash.com/self-host/enterprise-features/pre-aggregates
Enable pre-aggregate query routing on a self-hosted Lightdash instance
Enterprise Pre-aggregates need a valid `LIGHTDASH_LICENSE_KEY` set on your instance before any of the configuration below takes effect. See [enterprise features](/self-host/enterprise-features) for applying the key.
[Pre-aggregates](/semantic-layer/pre-aggregates) materialize aggregated tables to S3 and route matching queries to DuckDB instead of your warehouse, so common dashboard queries return in milliseconds without hitting the warehouse. To enable them on a self-hosted instance you need an S3-compatible bucket for the materialized data and at least one [pre-aggregate worker](/self-host/nats-workers/pre-aggregate-workers) to run the materializations.
## Prerequisites
* **Enterprise license** — `LIGHTDASH_LICENSE_KEY` must be set on your instance.
* **S3-compatible storage** — a bucket Lightdash can write Parquet files to. This can be the same bucket used for [external object storage](/self-host/customize-deployment/configure-lightdash-to-use-external-object-storage), or a dedicated one.
* **NATS enabled** — set `NATS_ENABLED=true` and `NATS_URL`. Materializations run through the NATS `pre-aggregate` stream.
* **At least one pre-aggregate worker** — see [pre-aggregate workers](/self-host/nats-workers/pre-aggregate-workers) for the worker deployment.
## Enable pre-aggregates
Set the main switch, keep Parquet materialization on, and point Lightdash at the bucket:
```bash theme={null}
PRE_AGGREGATES_ENABLED=true
PRE_AGGREGATES_PARQUET_ENABLED=true
PRE_AGGREGATE_RESULTS_S3_BUCKET=lightdash-pre-aggregates
PRE_AGGREGATE_RESULTS_S3_REGION=us-east-1
```
Authenticate to the bucket with `PRE_AGGREGATE_RESULTS_S3_ACCESS_KEY` and `PRE_AGGREGATE_RESULTS_S3_SECRET_KEY`, or omit them to use an IAM role or the default AWS credential chain. `PRE_AGGREGATES_PARQUET_ENABLED=false` keeps pre-aggregates in-memory only, lost on restart. To bound DuckDB memory per worker, set `PRE_AGGREGATE_DUCKDB_QUERY_MEMORY_LIMIT` (e.g. `4GB`). The full variable list is in the [pre-aggregates reference](/self-host/customize-deployment/environment-variables#pre-aggregates).
Deploy at least one [pre-aggregate worker](/self-host/nats-workers/pre-aggregate-workers) and restart the backend. Project developers can then define pre-aggregates in dbt and Lightdash will materialize and serve them.
## Verifying pre-aggregates
1. Add a pre-aggregate definition to a dbt model and deploy.
2. Trigger a materialization from the Lightdash UI or wait for the scheduled run.
3. Confirm a Parquet file lands in the configured bucket.
4. Run a query that matches the pre-aggregate — the query timing panel shows `pre-aggregate` as the source.
## Troubleshooting
**Materialization jobs stay queued forever.**
No pre-aggregate worker is consuming the NATS stream. Confirm at least one worker pod is running against the same NATS URL and is subscribed to the `pre-aggregate` stream.
**Materializations fail with an S3 permissions error.**
The credentials in `PRE_AGGREGATE_RESULTS_S3_ACCESS_KEY` / `_SECRET_KEY` (or the IAM role) need `s3:GetObject`, `s3:PutObject`, and `s3:ListBucket` on the bucket.
**Queries never route to a pre-aggregate.**
Check the pre-aggregate definition matches the query shape — see [pre-aggregate query matching](/semantic-layer/pre-aggregates#query-matching).
# Enterprise on-prem setup
Source: https://docs.lightdash.com/self-host/enterprise-on-prem
Deploy FedRAMP-ready Lightdash Enterprise Edition on premises on your own infrastructure
This guide covers the setup requirements and best practices for deploying Lightdash Enterprise Edition on-prem (on prem, on-premises, on premises). Lightdash Enterprise supports FedRAMP-ready deployments for government and regulated industries.
## Prerequisites
* Postgres database with the `pgvector` extension installed
## Accessing the Lightdash image
The Lightdash image is available on [DockerHub](https://hub.docker.com/repository/docker/lightdash/lightdash/tags). Pin a specific version in production rather than `latest`:
```bash theme={null}
docker pull lightdash/lightdash:0.2248.3
```
For the versioning policy, upgrade cadence, and how to rehearse upgrades in UAT, see [Upgrading Lightdash](/self-host/upgrading).
## License key
A license key is required for Enterprise Edition features. Set it via the `LIGHTDASH_LICENSE_KEY` environment variable — the Lightdash team shares your key via 1Password. See [enterprise features](/self-host/enterprise-features) for configuration, validation, and network requirements.
### After activating your license key
Once your license key is active, each Enterprise feature is enabled from its section on the [enterprise features](/self-host/enterprise-features) page — AI agents, data apps, embedding, MCP, custom roles, service accounts, SCIM, pre-aggregates, AI writeback, and multi-agent Slack channels.
If you need help enabling features or troubleshooting your deployment, contact the Lightdash team.
## Migration from OSS
If you're migrating from the open-source version to Enterprise Edition:
1. Migrate to a commercial version matching your current OSS version
2. Once a single container is running the commercial version, the connected database will be upgraded to include the commercial schemas
3. Any other OSS containers will fail on restart but can be run temporarily
4. Roll out the commercial version to all containers as soon as the first container successfully migrates
# Lightdash Cloud vs. Self-Hosted
Source: https://docs.lightdash.com/self-host/lightdash-cloud-vs-self-hosted
What you get and what you take on when you choose Lightdash Cloud or self-hosting
Self-hosting is great for hobbyist projects or if you want to go wild with a completely custom implementation (maybe you'd prefer the whole app was translated into pirate-speak). For most companies and teams, we recommend Lightdash Cloud.
To self-host Lightdash securely in production, you should have an excellent understanding of running services with Docker and kubernetes, as well as the security considerations.
If you're unsure whether to self-host or use Lightdash Cloud, here are some things to consider:
## When to use Lightdash Cloud
Unless you have a specific need to self-host, we recommend using Lightdash Cloud:
* Suitable for most teams
* Get started with Lightdash quickly
* Secure and reliable out-of-the-box.
* No need to maintain your own infrastructure.
* Always have the latest version of Lightdash.
* Incredible support from the core Lightdash team and maintainers.
## When to self-host
There are a few reasons why you might want to self-host Lightdash:
* You're a large enterprise and want to run a proof-of-concept on your own infrastructure. This can be a great way to get your hands on Lightdash, while your infosec team approves the use of Lightdash Cloud. For enterprise features on self-hosted instances, see our [enterprise features](/self-host/enterprise-features) documentation.
* You're a hobbyist and want to run Lightdash on your own infrastructure.
* You want to customise Lightdash to your specific needs.
# NATS workers
Source: https://docs.lightdash.com/self-host/nats-workers
Scale Lightdash query processing with dedicated NATS worker pods using the Helm chart
This page is for engineering teams self-hosting their own Lightdash instance.
By default, Lightdash processes all queries on the main API server. NATS workers move query execution onto dedicated pods, improving responsiveness under load and letting you scale query capacity independently.
Lightdash uses [NATS](https://nats.io/) — a lightweight, high-performance messaging system — with [JetStream](https://docs.nats.io/nats-concepts/jetstream), its built-in persistent streaming layer, to distribute work between the API server and worker pods.
NATS powers two opt-in features in Lightdash: [warehouse workers](/self-host/nats-workers/warehouse-workers), which process interactive and background warehouse queries on dedicated pods, and [pre-aggregate workers](/self-host/nats-workers/pre-aggregate-workers), which materialize pre-aggregates and serve queries from DuckDB.
## Requirements
* **Helm chart** version **2.7.2** or later
* **Lightdash** version [**0.2675.0**](https://hub.docker.com/r/lightdash/lightdash/tags) or later. Older images will fail with `MODULE_NOT_FOUND`.
Upgrading the Helm chart alone does not change how Lightdash works. NATS features are entirely opt-in — your existing deployment will behave exactly the same until you explicitly enable the new Helm values described below.
## Architecture
```mermaid theme={null}
flowchart LR
API[Lightdash API] -->|publish job| NATS[NATS JetStream]
NATS -->|deliver message| Worker[Worker pod
concurrency: 100]
Worker -->|return result| API
```
The Lightdash API publishes jobs to NATS JetStream. Worker pods consume messages from their stream and process them concurrently (default 100 concurrent jobs per pod).
## Enabling NATS
```yaml theme={null}
nats:
enabled: true
config:
cluster:
enabled: false
jetstream:
enabled: true
fileStore:
enabled: false
memoryStore:
enabled: true
maxSize: 1Gi
```
The JetStream configuration shown above reflects the defaults when `nats.enabled` is set to `true`. This deploys a NATS StatefulSet and sets `NATS_ENABLED=true` on the backend, which means the backend will start routing queries through NATS. You should always enable at least a [warehouse worker](/self-host/nats-workers/warehouse-workers) alongside NATS to process those queries — otherwise queries will be enqueued with no worker to pick them up.
Do not enable `nats.enabled: true` without also enabling `warehouseNatsWorker.enabled: true`. The backend routes queries to NATS when `NATS_ENABLED` is set, so queries will stall if no worker is running to process them.
## Auto-configured environment variables
The chart automatically sets these environment variables in the shared ConfigMap — you do not need to set them manually:
| Variable | Set when | Value |
| -------------- | -------------------- | ---------------------------- |
| `NATS_ENABLED` | `nats.enabled: true` | `"true"` |
| `NATS_URL` | `nats.enabled: true` | `nats://-nats:4222` |
Additional environment variables are auto-configured per worker deployment — see [Warehouse workers](/self-host/nats-workers/warehouse-workers) and [Pre-aggregate workers](/self-host/nats-workers/pre-aggregate-workers) for details.
## NATS JetStream configuration
JetStream supports two [storage backends](https://docs.nats.io/nats-concepts/jetstream/streams#storagetype) — we recommend memory store, but you can use file store depending on your needs.
### Memory store vs file store
| | Memory store (recommended) | File store |
| ------------------ | -------------------------------------------------------------------------------------------------- | -------------------------------------------------------------------------------------------------- |
| **How it works** | Messages are held in RAM | Messages are persisted to disk |
| **Performance** | Faster — no disk I/O overhead | Slower — writes go through disk |
| **Persistence** | Messages are lost if NATS restarts | Messages survive NATS restarts |
| **Infrastructure** | No PersistentVolumeClaim needed | Requires a PersistentVolumeClaim |
| **When to use** | Most deployments. Lightdash messages are small (just a query UUID) and are deleted once processed. | High message volume exceeding available RAM, or if you need messages to survive NATS pod restarts. |
For more details, see the NATS documentation on [JetStream](https://docs.nats.io/nats-concepts/jetstream) and [stream storage types](https://docs.nats.io/nats-concepts/jetstream/streams#storagetype).
### Configuration reference
```yaml theme={null}
nats:
enabled: true
config:
cluster:
enabled: false # single-node NATS, no clustering
jetstream:
enabled: true
fileStore:
enabled: false # no disk persistence
memoryStore:
enabled: true
maxSize: 1Gi # max memory for message storage
```
| Setting | Recommended | Description |
| ------------------------------------------- | ----------- | -------------------------------------------- |
| `nats.config.jetstream.memoryStore.enabled` | `true` | Enable memory-backed storage |
| `nats.config.jetstream.memoryStore.maxSize` | `1Gi` | Maximum memory for JetStream message storage |
| `nats.config.jetstream.fileStore.enabled` | `false` | Enable disk-backed storage |
| `nats.config.cluster.enabled` | `false` | Single-node NATS (no clustering) |
### Pod disruption
NATS is a stateful component — if the NATS pod restarts, in-flight messages are lost (queries will be retried by users). The chart protects against unplanned eviction with:
* `cluster-autoscaler.kubernetes.io/safe-to-evict: "false"` annotation
* `PodDisruptionBudget` with `maxUnavailable: 0`
# Pre-aggregate workers
Source: https://docs.lightdash.com/self-host/nats-workers/pre-aggregate-workers
Configure and scale pre-aggregate worker pods for materialization and DuckDB query serving
Enterprise
Pre-aggregate workers handle two jobs:
1. **Materializations** — Run a query against your warehouse, convert the results to a materialization format, and upload to S3
2. **DuckDB queries** — When a user query [matches a pre-aggregate](/semantic-layer/pre-aggregates#query-matching), read the materialized data from S3 and execute the query using DuckDB
Both job types are distributed via the NATS `pre-aggregate` stream.
### Pre-aggregate materializations
Scheduled jobs materialize warehouse query results and store them on S3:
```mermaid theme={null}
flowchart LR
API[Lightdash API] -->|publish materialization job| NATS[NATS JetStream]
NATS -->|deliver| Worker[Pre-aggregate worker]
Worker -->|execute SQL| WH[(Data warehouse)]
WH -->|rows| Worker
Worker -->|store data| S3[(S3 storage)]
```
### Pre-aggregate queries
When a user query matches a pre-aggregate, the worker serves it using DuckDB against materialized data on S3 — without hitting your data warehouse:
```mermaid theme={null}
flowchart LR
API[Lightdash API] -->|publish query job| NATS[NATS JetStream]
NATS -->|deliver| Worker[Pre-aggregate worker]
Worker -->|read data| S3[(S3 storage)]
S3 -->|data| Worker
Worker -->|DuckDB result| API
```
## Prerequisites
* A valid [Enterprise license key](/self-host/enterprise-features)
* An S3-compatible bucket for materialized data (AWS S3, GCS, MinIO)
* NATS and warehouse workers enabled (see [NATS workers overview](/self-host/nats-workers))
## Example configuration
Start from the NATS and warehouse worker configuration:
```yaml theme={null}
nats:
enabled: true
config:
cluster:
enabled: false
jetstream:
enabled: true
fileStore:
enabled: false
memoryStore:
enabled: true
maxSize: 1Gi
warehouseNatsWorker:
enabled: true
replicas: 1
concurrency: 100
resources:
requests:
cpu: 250m
memory: 1.5Gi
limits:
memory: 1.5Gi
```
Then add a pre-aggregate worker alongside it:
```yaml theme={null}
preAggregateNatsWorker:
enabled: true
replicas: 1
concurrency: 100
resources:
requests:
cpu: 650m
memory: 4Gi
ephemeral-storage: 9Gi
limits:
memory: 4Gi
ephemeral-storage: 9Gi
```
The Helm chart auto-configures these environment variables:
| Variable | Set from | Value |
| -------------------------------- | -------------------------------------- | ---------------------------- |
| `NATS_ENABLED` | `nats.enabled: true` | `"true"` |
| `NATS_URL` | `nats.enabled: true` | `nats://-nats:4222` |
| `NATS_WORKER_CONCURRENCY` | `preAggregateNatsWorker.concurrency` | `100` |
| `PRE_AGGREGATES_ENABLED` | `preAggregateNatsWorker.enabled: true` | `"true"` |
| `PRE_AGGREGATES_PARQUET_ENABLED` | `preAggregateNatsWorker.enabled: true` | `"true"` |
See the [overview](/self-host/nats-workers) for details on JetStream configuration options.
## S3 storage configuration
Pre-aggregates require a **dedicated S3 bucket** separate from your main Lightdash results cache bucket. This prevents query history cleanup from deleting active materialization files.
| Variable | Required | Description | Fallback |
| ------------------------------------- | -------- | -------------------------------------- | --------------- |
| `S3_ENDPOINT` | Yes | S3-compatible endpoint URL | — |
| `PRE_AGGREGATE_RESULTS_S3_BUCKET` | Yes | Dedicated bucket for materialized data | — |
| `PRE_AGGREGATE_RESULTS_S3_REGION` | Yes | S3 region for the bucket | — |
| `PRE_AGGREGATE_RESULTS_S3_ACCESS_KEY` | No | Access key for the bucket | `S3_ACCESS_KEY` |
| `PRE_AGGREGATE_RESULTS_S3_SECRET_KEY` | No | Secret key for the bucket | `S3_SECRET_KEY` |
`S3_ENDPOINT` and `S3_FORCE_PATH_STYLE` are inherited from your base [S3 configuration](/self-host/customize-deployment/configure-lightdash-to-use-external-object-storage). Access keys fall back to the base S3 credentials if not set separately.
The credentials used for the pre-aggregate bucket must have **`s3:PutObject`** permission (in addition to `s3:GetObject`, `s3:ListBucket`, and `s3:DeleteObject`). Without `PutObject`, materialization jobs will fail to upload results to the bucket. On other S3-compatible storage (GCS, MinIO), grant the equivalent write permission.
```yaml theme={null}
configMap:
S3_ENDPOINT: "https://s3.us-east-1.amazonaws.com"
PRE_AGGREGATE_RESULTS_S3_BUCKET: "my-lightdash-pre-aggs"
PRE_AGGREGATE_RESULTS_S3_REGION: "us-east-1"
secrets:
PRE_AGGREGATE_RESULTS_S3_ACCESS_KEY: "AKIA..."
PRE_AGGREGATE_RESULTS_S3_SECRET_KEY: "..."
```
GCS is S3-compatible via [HMAC keys](https://cloud.google.com/storage/docs/authentication/hmackeys). Generate an HMAC key pair in **Cloud Storage > Settings > Interoperability**.
```yaml theme={null}
configMap:
S3_ENDPOINT: "https://storage.googleapis.com"
PRE_AGGREGATE_RESULTS_S3_BUCKET: "my-lightdash-pre-aggs"
PRE_AGGREGATE_RESULTS_S3_REGION: "auto"
secrets:
PRE_AGGREGATE_RESULTS_S3_ACCESS_KEY: "GOOG..."
PRE_AGGREGATE_RESULTS_S3_SECRET_KEY: "..."
```
```yaml theme={null}
configMap:
S3_ENDPOINT: "https://minio.example.com"
S3_FORCE_PATH_STYLE: "true"
PRE_AGGREGATE_RESULTS_S3_BUCKET: "lightdash-pre-aggs"
PRE_AGGREGATE_RESULTS_S3_REGION: "us-east-1"
secrets:
PRE_AGGREGATE_RESULTS_S3_ACCESS_KEY: "minioadmin"
PRE_AGGREGATE_RESULTS_S3_SECRET_KEY: "..."
```
We recommend setting a **retention / lifecycle policy** on the pre-aggregate bucket to automatically clean up old files. Lightdash manages its own materializations, but a lifecycle policy prevents orphaned files from accumulating. Choose a retention period that makes sense for your deployment.
## Configuration reference
All configuration is set through your Helm `values.yaml` under `preAggregateNatsWorker`:
### Scaling
| Helm value | Default | Description |
| ------------------------------------ | ------- | --------------------------------------------------------------------------- |
| `preAggregateNatsWorker.replicas` | `1` | Number of worker pods. Scale horizontally for more parallel capacity. |
| `preAggregateNatsWorker.concurrency` | `100` | Maximum concurrent jobs per pod. Maps to `NATS_WORKER_CONCURRENCY` env var. |
### Resources
| Helm value | Recommended (request) | Recommended (limit) | Description |
| ------------------------------------------------------------- | --------------------- | ------------------- | ----------------------------------------------------- |
| `preAggregateNatsWorker.resources.requests.cpu` | `650m` | — | CPU request per pod |
| `preAggregateNatsWorker.resources.requests.memory` | `4Gi` | `4Gi` | Memory request and limit per pod |
| `preAggregateNatsWorker.resources.requests.ephemeral-storage` | `9Gi` | `9Gi` | Local disk for temporary files during materialization |
Pre-aggregate workers need significantly more resources than warehouse workers because they run **DuckDB in-process** for both materializing data and serving queries against materialized data.
**Ephemeral storage is critical.** During materialization, warehouse query results are written to a local temporary file before being converted and uploaded to S3. Large materializations can consume several gigabytes of local disk. If the pod runs out of ephemeral storage, it will be evicted.
### DuckDB memory tuning
DuckDB runs inside the pre-aggregate worker process. There are two types of DuckDB instances:
| Instance type | Used for | Memory limit | Concurrency |
| -------------------------------------- | -------------------------------- | ---------------------------- | ------------------------------------ |
| **Shared query instance** | Serving pre-aggregate queries | Configurable (see below) | Shared across all concurrent queries |
| **Isolated materialization instances** | Converting and uploading results | 256MB per instance, 1 thread | One per active materialization |
By default, the shared query instance has **no memory cap**. Under concurrent load, this can cause OOM kills. Set a limit:
```yaml theme={null}
configMap:
PRE_AGGREGATE_DUCKDB_QUERY_MEMORY_LIMIT: "3GB"
```
**Sizing guideline:** Start with 2–3GB and adjust based on observed memory usage. The limit should leave enough headroom for the Node.js process, active materializations, and OS overhead within the pod's total memory.
### Optional environment variables
These can be set via `extraEnv` or `configMap` if you need to override the defaults:
| Variable | Default | Description |
| ----------------------------------------- | ---------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `NATS_QUEUE_TIMEOUT_MS` | `180000` (3 min) | How long a message can wait in the queue before being discarded. |
| `PRE_AGGREGATE_DUCKDB_QUERY_MEMORY_LIMIT` | Unlimited | Memory cap for the shared DuckDB query instance (e.g., `2GB`, `3GB`). When unset, DuckDB will use all available pod memory and is likely to cause OOM kills under concurrent load. We strongly recommend setting this. |
| `PRE_AGGREGATES_MAX_ROWS` | Unlimited | Maximum rows per materialization. Results are truncated to this limit with a warning. Can also be set per pre-aggregate in dbt YAML via `max_rows`. |
## Troubleshooting
### Pre-aggregate queries hitting the warehouse instead of DuckDB
1. Verify that `PRE_AGGREGATES_ENABLED` is set to `"true"` on the pre-aggregate worker pod
2. Verify that `PRE_AGGREGATES_PARQUET_ENABLED` is set to `"true"` on the pre-aggregate worker pod
3. Confirm the pre-aggregate worker pod is running and healthy
4. Check that an active materialization exists in **Project Settings > Pre-aggregates**
5. Review query matching rules — see [monitoring and debugging](/semantic-layer/pre-aggregates/monitoring)
### Worker OOM kills
Pre-aggregate workers run DuckDB which can consume significant memory:
1. Set `PRE_AGGREGATE_DUCKDB_QUERY_MEMORY_LIMIT` (e.g., `3GB`) to cap DuckDB memory
2. Increase the worker's memory request and limit
3. Reduce `concurrency` to limit parallel DuckDB queries
### Materialization failures
Common causes:
* **S3 access denied** — Verify `PRE_AGGREGATE_RESULTS_S3_*` credentials and bucket permissions
* **Warehouse timeout** — Large materializations may exceed warehouse query timeout limits
* **Disk pressure** — Materialization writes temporary files to local disk. Increase ephemeral storage if you see evictions.
* **Too many rows** — Materializations should not contain very large datasets. We recommend keeping materializations under 100,000 rows for optimal performance. You can use `max_rows` in your [pre-aggregate definition](/semantic-layer/pre-aggregates#row-limits) or the `PRE_AGGREGATES_MAX_ROWS` environment variable to enforce a limit.
# Warehouse workers
Source: https://docs.lightdash.com/self-host/nats-workers/warehouse-workers
Configure and scale dedicated warehouse query worker pods
Warehouse workers process interactive and background SQL queries against your data warehouse. When a user runs a query in Lightdash, the API server publishes a job to the NATS `warehouse` stream, and a warehouse worker picks it up, executes the SQL, and returns the results.
```mermaid theme={null}
flowchart LR
API[Lightdash API] -->|publish query job| NATS[NATS JetStream]
NATS -->|deliver| Worker[Warehouse worker]
Worker -->|execute SQL| WH[(Data warehouse)]
WH -->|rows| Worker
Worker -->|result| API
```
## Example configuration
A complete Helm values configuration with NATS and a warehouse worker:
```yaml theme={null}
nats:
enabled: true
config:
cluster:
enabled: false
jetstream:
enabled: true
fileStore:
enabled: false
memoryStore:
enabled: true
maxSize: 1Gi
warehouseNatsWorker:
enabled: true
replicas: 1
concurrency: 100
resources:
requests:
cpu: 250m
memory: 1.5Gi
limits:
memory: 1.5Gi
```
The chart auto-configures `NATS_ENABLED=true` and `NATS_URL` for you. See the [overview](/self-host/nats-workers) for details on JetStream configuration options.
## Configuration reference
All configuration is set through your Helm `values.yaml` under `warehouseNatsWorker`:
### Scaling
| Helm value | Default | Description |
| --------------------------------- | ------- | --------------------------------------------------------------------------- |
| `warehouseNatsWorker.replicas` | `1` | Number of worker pods. Scale horizontally for more parallel query capacity. |
| `warehouseNatsWorker.concurrency` | `100` | Maximum concurrent jobs per pod. Maps to `NATS_WORKER_CONCURRENCY` env var. |
### Resources
| Helm value | Recommended (request) | Recommended (limit) | Description |
| ----------------------------------------------- | --------------------- | ------------------- | -------------------------------- |
| `warehouseNatsWorker.resources.requests.cpu` | `250m` | — | CPU request per pod |
| `warehouseNatsWorker.resources.requests.memory` | `1.5Gi` | `1.5Gi` | Memory request and limit per pod |
### Auto-configured environment variables
These are set by the Helm chart:
| Variable | Set from | Value |
| ------------------------- | --------------------------------- | ---------------------------- |
| `NATS_ENABLED` | `nats.enabled: true` | `"true"` |
| `NATS_URL` | `nats.enabled: true` | `nats://-nats:4222` |
| `NATS_WORKER_CONCURRENCY` | `warehouseNatsWorker.concurrency` | `100` |
### Optional environment variables
These can be set via `extraEnv` or `configMap` if you need to override the defaults:
| Variable | Default | Description |
| ----------------------- | ---------------- | ---------------------------------------------------------------- |
| `NATS_QUEUE_TIMEOUT_MS` | `180000` (3 min) | How long a message can wait in the queue before being discarded. |
## Troubleshooting
### Queries timing out
If workers are busy, messages may expire before being processed. Scale up replicas or concurrency.
### Worker OOM kills
Increase the memory request and limit. Large query result sets are held in memory during processing.
### NATS connection errors
1. Confirm the NATS pod is running and healthy
2. Verify that `NATS_URL` is set correctly on the warehouse worker pod (should be `nats://-nats:4222`)
3. Check that your network policies allow traffic between the worker pods and the NATS service on port `4222`
# Production deployment checklist
Source: https://docs.lightdash.com/self-host/production-deployment-checklist
What to configure for an enterprise-grade self-hosted deployment with the official Helm chart
This is the checklist for running a production-grade, self-hosted Lightdash deployment with the official Helm chart ([lightdash/helm-charts](https://github.com/lightdash/helm-charts)). The guide is intended for platform/devops engineers deploying their own Lightdash instance. The guide gives you 3-tiers of deployments with increasing configuration options.
| Tier | |
| ------ | ---------------------------------------------- |
| Tier 1 | Recommended for evaluating Lightdash or a PoC |
| Tier 2 | A best practice scalable production deployment |
| Tier 3 | Optional features and observability |
## Tier 1: Evaluation Deployment
Follow the [self-hosting guide](/self-host/self-host-lightdash) (or [docker compose](/self-host/self-host-lightdash-docker-compose) for a local spin-up) for a minimum production setup suitable for evaluating Lightdash.
**Prerequisites**
* [External PostgreSQL](/self-host/customize-deployment/configure-lightdash-to-use-an-external-database) — `postgresql.enabled: false` even for a PoC; a small managed instance is fine, no HA or backup requirements yet
* [S3-compatible object storage](/self-host/customize-deployment/configure-lightdash-to-use-external-object-storage) — required regardless; Lightdash fails to start without it
* [Enterprise license key](/self-host/enterprise-features) if you're evaluating Enterprise features — needs outbound access to `https://api.keygen.sh`
**Checklist**
* Deploy with the [self-hosting guide](/self-host/self-host-lightdash)
* [Pin an image version](#core-configuration) - pin `image.tag` to the [latest version](https://hub.docker.com/r/lightdash/lightdash/tags)
* [Set `SITE_URL`](#core-configuration) to your final `https://` URL before first boot
* [Generate a strong `LIGHTDASH_SECRET`](#core-configuration) and store it somewhere durable - losing it means losing access to encrypted data
* [`SECURE_COOKIES` and `TRUST_PROXY`](/self-host/customize-deployment/secure-lightdash-with-https) both `"true"` behind your TLS-terminating load balancer
* [Headless browser](#headless-browser) enabled (the chart default) - image previews, PDF exports, Slack unfurls
* [HTTPS end-to-end](/self-host/customize-deployment/secure-lightdash-with-https) via your ingress or load balancer
* [AI Analyst](#ai-analyst) — model provider key + `pgvector` in Postgres
## Tier 2: Scalable deployment
None of these steps are required but are recommended for running Lightdash beyond a PoC.
### Operations
* [Choose upgrade strategy](#upgrades-and-operations) — pinned image tag, at least monthly cadence
* [Monitor security advisories](/self-host/upgrading#monitor-security-advisories) — poll the GitHub advisory API and alert on anything that matches your deployed version
### Workers and scaling
* [Data apps](#data-apps) — sandboxes runtime, apps bucket, and a separate preview origin
* [Dedicated workers](#scheduler-worker) - `scheduler.enabled: true` to enable scalable scheduled deliveries
* [NATS + warehouse workers](/self-host/nats-workers) - `nats.enabled: true` **and** `warehouseNatsWorker.enabled: true` for handling large volumes of warehouse queries
* [≥ 2 backend replicas](#sizing-and-availability) with pod anti-affinity and a pod disruption budget
* [Size resource requests](#sizing-and-availability)
* [Configure health probes](#health-probes) - readiness on `/api/v1/readyz`, not `/api/v1/health`, so a migrating pod leaves the load balancer without being restarted
### Infrastructure dependencies
* [Postgres HA, backups and point-in-time recovery](#postgresql) on the external database from Tier 1, plus the `pgvector` extension if you use Enterprise AI features
* [Bucket strategy and lifecycle rules](#object-storage) - a dedicated bucket per purpose, with a 1-day lifecycle delete on the results bucket
* [SMTP email](/self-host/customize-deployment/environment-variables#smtp)
* [Email deliverability](#email-deliverability) - transactional provider, SPF/DKIM on the sender domain
* [Load balancer settings](#load-balancer-and-networking) - timeout ≥ 300s, health check on `/api/v1/health`, request body limit ≥ `LIGHTDASH_MAX_PAYLOAD`
### Security and authentication
* [SSO](/self-host/customize-deployment/use-sso-login-for-self-hosted-lightdash) (Okta / Azure AD / Google / generic OIDC) with password authentication disabled
* [Account linking and personal access token policy](#authentication-policy)
* [CSP enforcement and explicit CORS](#security-hardening)
## Tier 3: Optional features and observability
### Enterprise features
* [Pre-aggregations](/self-host/nats-workers/pre-aggregate-workers) — pre-aggregate NATS workers + a dedicated S3 bucket
* [SCIM provisioning](/workspace-admin/sso/scim) from your IdP
* [Results caching, embedding, service accounts, custom roles](#enterprise-feature-flags)
### Integrations (enable what you use)
* [Slack](/self-host/customize-deployment/configure-a-slack-app-for-lightdash) — scheduled deliveries, unfurls, AI agents in Slack
* [GitHub](/self-host/customize-deployment/configure-github-for-lightdash) or GitLab — dbt repo write-back
* [Google Sheets sync](/self-host/customize-deployment/configure-google-sheets-integration)
### Observability
* [Prometheus metrics](/self-host/customize-deployment/configure-prometheus-metrics-for-self-hosted-lightdash) enabled, and [scraped on every pod](#observability)
* [Structured JSON logging](/self-host/customize-deployment/environment-variables#logging) shipped to your log platform
* [Alerts](#observability) on HTTP error rate/latency, queue depth, and Postgres pool saturation
## Architecture: what you're deploying
| Component | Chart value | What it does |
| ------------------------- | ------------------------------------------ | ----------------------------------------------------------------- |
| Backend | (always on) | API + UI. Scale horizontally, 2+ replicas |
| Scheduler worker | `scheduler.enabled` | Scheduled deliveries, Slack/email sends, exports, syncs |
| NATS | `nats.enabled` | JetStream message bus for async query execution |
| Warehouse NATS worker | `warehouseNatsWorker.enabled` | Executes warehouse queries + streams results to S3 |
| Pre-aggregate NATS worker | `preAggregateNatsWorker.enabled` | Builds pre-aggregated materializations (Enterprise) |
| Headless browser | `browserless-chrome.enabled` | Chromium pool for screenshots/PDFs |
| Migration job | `migrationJob.enabled` | Pre-upgrade Helm hook that runs database migrations exactly once |
| PostgreSQL | **external** (`postgresql.enabled: false`) | Application state. Don't use the bundled subchart, even for a PoC |
| S3 bucket(s) | external | Query results, downloads, pre-agg materializations, data apps |
The chart wires up environment variables for you in three buckets:
* `configMap.*` — non-sensitive env vars, applied to backend **and** all workers
* `secrets.*` — sensitive env vars, rendered into a Kubernetes Secret (or bring your own via `existingSecret`)
* `extraEnv` / `schedulerExtraEnv` — raw env entries, including `valueFrom.secretKeyRef`
The full list of supported environment variables lives in the [environment variables reference](/self-host/customize-deployment/environment-variables).
## Core configuration
The chart's essential first-boot values — `SITE_URL` and `LIGHTDASH_SECRET` are the two to get right before you start:
```yaml theme={null}
image:
repository: lightdash/lightdash
tag: "0.xxxx.x" # always pin; upgrade deliberately
configMap:
SITE_URL: https://lightdash.yourcompany.com
SECURE_COOKIES: "true"
TRUST_PROXY: "true"
LIGHTDASH_MODE: default
LIGHTDASH_MAX_PAYLOAD: "40mb" # default 5mb is too small for large dbt manifests
existingSecret: lightdash-secrets # LIGHTDASH_SECRET, S3 keys, SMTP password, ...
```
* **`SITE_URL`** signs invite emails, OAuth redirect URIs, Slack unfurls, and delivery links — set the final `https://` URL before first boot.
* **`LIGHTDASH_SECRET`** signs session cookies and encrypts data at rest in Postgres. Set it and store it durably; losing it means losing access to encrypted data.
* `SECURE_COOKIES` and `TRUST_PROXY` must both be `"true"` behind a TLS-terminating proxy, and `COOKIES_MAX_AGE_HOURS` sets session length — see [Secure Lightdash with HTTPS](/self-host/customize-deployment/secure-lightdash-with-https).
* Every variable is documented in the [environment variables reference](/self-host/customize-deployment/environment-variables).
For secrets, prefer `existingSecret` populated by External Secrets Operator or a CSI driver so credentials stay out of Helm values and git.
## Headless browser
Enabled by default in the chart — keep it on, and tune the browserless timeouts and memory guards for large dashboards. The browser renders dashboards by calling `SITE_URL`, so it must reach that URL from inside the cluster (use `INTERNAL_LIGHTDASH_HOST` if it can't). See [Headless browser](/self-host/customize-deployment/enable-headless-browser-for-lightdash) for the container and backend variables, and [Resource recommendations](/self-host/production-deployment-checklist#recommended-resources) for sizing.
## Upgrades and operations
Pin `image.tag`, upgrade at least monthly, rehearse each upgrade in a UAT instance that mirrors production, take the database backup before you start, and enable the migration Job for multi-replica deployments. Before each upgrade, set `upgrade.mode` from the [upgrade-safety verdict](/self-host/upgrade-safety): `RollingUpdate` for `true`, or `Recreate` for `false` or `unknown`. Chart `2.16.284` and later runs the shutdown barrier before the migration Job when the mode is `Recreate`. Chart `2.16.283` and earlier needs the manual fallback. See the [upgrade runbook](/self-host/upgrade-runbook#configure-the-upgrade-mode) for both paths and custom deployments. Versioning policy, upgrade cadence, and advisory monitoring are in [Upgrading Lightdash](/self-host/upgrading).
## Scheduler worker
Run a **dedicated scheduler worker** so a heavy dashboard export can't starve the API — see [Scheduler worker](/self-host/customize-deployment/scheduler). For async warehouse queries, see the [NATS workers overview](/self-host/nats-workers) and [warehouse workers](/self-host/nats-workers/warehouse-workers), including the critical rule: **never enable `nats.enabled` without `warehouseNatsWorker.enabled`**.
## Sizing and availability
Size per-component resource requests and run 2+ backend replicas with pod anti-affinity and a pod disruption budget — the full component table and availability settings are in [Resource recommendations](/self-host/production-deployment-checklist#recommended-resources).
## PostgreSQL
Run external managed Postgres with high availability, backups, the `uuid-ossp` and `pgvector` extensions, and a connection budget — setup and production guidance are in [Configure Lightdash to use an external database](/self-host/customize-deployment/configure-lightdash-to-use-an-external-database).
## Object storage
Use a **dedicated bucket per purpose** with lifecycle rules, blocked public access, and scoped credentials — setup and the full bucket strategy are in [Configure Lightdash to use external object storage](/self-host/customize-deployment/configure-lightdash-to-use-external-object-storage).
## Email deliverability
Env vars are in the [SMTP reference](/self-host/customize-deployment/environment-variables#smtp). Best practice on top:
* Use a transactional provider (SES, Postmark, SendGrid) — Lightdash Cloud sends through Postmark.
* Set up SPF/DKIM for the sender domain so scheduled deliveries don't land in spam.
## Load balancer and networking
HTTPS end-to-end, a load-balancer timeout ≥ 300s, a health check on `GET /api/v1/health`, and a request body limit ≥ `LIGHTDASH_MAX_PAYLOAD` — covered in [Secure Lightdash with HTTPS](/self-host/customize-deployment/secure-lightdash-with-https).
## Health probes
Lightdash serves three health endpoints, and they are not interchangeable:
| Probe | Endpoint | Why |
| --------- | ---------------- | ------------------------------------------------------------------------------------------------------------------------------ |
| Liveness | `/api/v1/livez` | Answers without touching the database, so a database blip does not restart every pod at once |
| Readiness | `/api/v1/readyz` | TTL-cached, gates on schema migration state. Keeps a pod out of the load balancer while it is migrating, without restarting it |
| Startup | `/api/v1/livez` | Same endpoint as liveness, so a slow-starting pod is not killed before it is ready to serve |
Avoid `/api/v1/health` for any of the three. It queries the database on every request, which is fine for a manual `curl` after a deploy but means a brief database blip fails every pod's check at the same time.
The chart already points `startupProbe` and `livenessProbe` at `/api/v1/livez` by default. Set the readiness path explicitly:
```yaml theme={null}
lightdashBackend:
readinessProbe:
path: /api/v1/readyz
```
Recent chart versions resolve this path for you on Lightdash `1.169.1` and later. Setting it explicitly is always supported, and an explicit value always wins.
Only point a readiness probe at `/api/v1/readyz` on Lightdash `1.169.1` or later. Before that, a parked migration marked every working pod as not ready, so a readiness probe on `/api/v1/readyz` could pull the whole backend out of service over a single stuck migration. See [what shipped when](/self-host/upgrade-runbook#what-shipped-when) and the [upgrade runbook](/self-host/upgrade-runbook#kubernetes-and-helm) for the full reasoning and the `503` reasons.
Leave worker probes alone. Worker pods serve their own handler at `/api/v1/health` (in-memory state, no database query) and do not serve `/api/v1/readyz` at all.
## Authentication policy
Enterprise deployments should be SSO-only, with password authentication disabled and account linking enabled — per-provider setup is in [use SSO login for self-hosted Lightdash](/self-host/customize-deployment/use-sso-login-for-self-hosted-lightdash). Also set a personal access token policy (`PAT_ALLOWED_ORG_ROLES`, `PAT_MAX_EXPIRATION_TIME_IN_DAYS`, or `DISABLE_PAT`), and keep `ALLOW_MULTIPLE_ORGS: "false"` (default) for a single-company instance.
## Security hardening
* **CSP enforcement:** `LIGHTDASH_CSP_REPORT_ONLY: "false"` (default is report-only; enforce in production), plus `LIGHTDASH_CSP_ALLOWED_DOMAINS` for any extra origins you load from.
* **CORS:** leave disabled unless embedding; if embedding, `LIGHTDASH_CORS_ENABLED: "true"` with an explicit `LIGHTDASH_CORS_ALLOWED_DOMAINS` list — never `*`.
* **Egress policy:** Lightdash needs your warehouse, S3, SMTP, `api.keygen.sh` (license), your IdP, and any AI provider endpoints — plus `roadmap.lightdash.com` if you enable the [organization roadmap](/self-host/customize-deployment/organization-roadmap). RudderStack product telemetry to `analytics.lightdash.com` is on by default and can be disabled or redirected. See [Data flows and telemetry](/self-host/data-flows-and-telemetry) for the full egress matrix and controls.
* **NetworkPolicies:** the chart only ships one for NATS (keep `nats.networkPolicy.enabled: true`, the default); add your own default-deny + allow rules for backend ↔ postgres/browserless/S3 if your cluster uses them.
* **Pod security:** the chart sets no `podSecurityContext` / `securityContext` by default — add `runAsNonRoot` and drop capabilities per your Pod Security Standards baseline.
* **Soft delete** for content recovery: `SOFT_DELETE_ENABLED: "true"` (plus `SOFT_DELETE_RETENTION_DAYS`, default 30).
## Enterprise features
License key setup and validation is covered in [enterprise license keys](/self-host/enterprise-features) — the key is validated against `https://api.keygen.sh` on every server start, so allowlist that domain in your egress policy.
### Enterprise feature flags
Enable the Enterprise features you use through `configMap` — each is documented in the [environment variables reference](/self-host/customize-deployment/environment-variables):
* **Caching:** `RESULTS_CACHE_ENABLED`, `AUTOCOMPLETE_CACHE_ENABLED`, `CACHE_STALE_TIME_SECONDS`.
* **Governance:** `SERVICE_ACCOUNT_ENABLED`, `CUSTOM_ROLES_ENABLED`.
* **Embedding:** `EMBEDDING_ENABLED` with `LIGHTDASH_IFRAME_EMBEDDING_DOMAINS`.
### AI Analyst
Set `AI_COPILOT_ENABLED: "true"`, choose `AI_DEFAULT_PROVIDER` (openai, azure, anthropic, openrouter, or bedrock) with the matching API key, and `AI_EMBEDDING_ENABLED: "true"` for verified answers (requires `pgvector`). See [AI agents](/self-host/enterprise-features/ai-agents) for provider setup and LLM-gateway routing, and the [environment variables reference](/self-host/customize-deployment/environment-variables#ai-analyst) for guardrails such as `AI_COPILOT_MAX_QUERY_LIMIT` and `AI_COPILOT_ALLOWED_PROJECT_UUID`. For the MCP endpoint, see [MCP](/self-host/enterprise-features/mcp).
### Data apps
Serve app previews from a **separate domain** (`APP_RUNTIME_PREVIEW_ORIGIN`) so untrusted app content never shares an origin with your Lightdash session cookies, and use a persistent apps bucket with no delete lifecycle. Sandbox providers and their security model are in [sandboxes](/self-host/customize-deployment/sandboxes); configuration in [self-hosting data apps](/self-host/enterprise-features/data-apps).
## Observability
Enable Prometheus metrics and structured JSON logging on every pod, and scrape them from your monitoring stack:
* Metrics and alerting guidance: [Prometheus metrics](/self-host/customize-deployment/configure-prometheus-metrics-for-self-hosted-lightdash). Scrape port **9090** on all pods labelled `app.kubernetes.io/name=lightdash` (the chart ships no ServiceMonitor/PodMonitoring — create one; Lightdash Cloud scrapes at a 30s interval). If you run NATS, its Prometheus exporter is on port **7777** (`nats.promExporter.enabled: true`).
* Log configuration: [Configure logging](/self-host/customize-deployment/environment-variables#logging). Ship `LIGHTDASH_LOG_FORMAT: json` to your log platform; `LIGHTDASH_LOG_LEVEL: audit` adds an audit trail of user actions.
* Alert on HTTP p95/error rate (`http_server_request_duration_seconds`), queue depth / scheduler job failures, Postgres pool saturation, and event-loop lag.
* Distributed traces (optional): [OpenTelemetry tracing](/self-host/customize-deployment/configure-opentelemetry-tracing-for-self-hosted-lightdash). Set `LIGHTDASH_OTEL_TRACES_ENABLED: "true"` and point `OTEL_EXPORTER_OTLP_ENDPOINT` at your collector.
## Recommended resources
Baseline per-component resource requests for a standard self-hosted instance deployed with the official Helm chart:
| Component | CPU | Memory | Ephemeral | Replicas |
| ------------------------- | ------ | ----------- | --------- | -------- |
| Backend | 500m–1 | 1.5–4 Gi | 1–2 Gi | **2+** |
| Scheduler worker | 500m | 1425 Mi | 1 Gi | 1 |
| Warehouse NATS worker | 250m | 1.5 Gi | 9 Gi | 1 |
| Pre-aggregate NATS worker | 650m | 4 Gi | 9 Gi | 1 |
| Browserless | 2 | 4 Gi | 1 Gi | 1 |
| NATS | 100m | 256 Mi–1 Gi | — | 1 |
NATS workers buffer large result sets on ephemeral disk before uploading to S3 — the 9 Gi ephemeral-storage request is not a typo.
### Availability
Run at least two backend replicas with pod anti-affinity and a pod disruption budget:
```yaml theme={null}
replicaCount: 2
autoscaling:
enabled: true
minReplicas: 2
maxReplicas: 10
targetCPUUtilizationPercentage: 60 # backend only; workers scale via replicas
podAntiAffinity:
enabled: true # spreads each component across nodes (hard) and zones (soft)
podDisruptionBudget:
enabled: true
minAvailable: 1
```
# Self-Host Lightdash
Source: https://docs.lightdash.com/self-host/self-host-lightdash
Deploy Lightdash on Kubernetes with the official Helm chart, from minimum config to production
Lightdash is MIT licensed and open source. You can self-host Lightdash on your own infrastructure.
This guide is designed for DevOps Engineers that are familiar with Docker, Kubernetes, and are comfortable configuring environment variables, SMTP credentials, and database connections. If you're unsure whether to self-host please read our guide on [Lightdash Cloud vs. Self-Hosted](/self-host/lightdash-cloud-vs-self-hosted).
## Prerequisites for self-hosting
* Access to a kubernetes cluster and [kubectl](https://kubernetes.io/docs/tasks/tools) installed
* [Helm](https://helm.sh/docs/intro/quickstart)
* Docker
## Self-host Lightdash on Kubernetes
The following steps will create a Lightdash instance and a postgres database to store your metadata (note: this is separate to your data warehouse with your analytics data).
We recommend using kubernetes + helm but you can alternatively follow these guides for a minimum deployment without kubernetes:
* [Docker compose](/self-host/self-host-lightdash-docker-compose)
To deploy Lightdash on your Kubernetes cluster you can use our community maintained Helm chart: [https://github.com/lightdash/helm-charts](https://github.com/lightdash/helm-charts). This will get you started with the simplest configuration possible. At the end of this guide you can find a list of configuration options to customise your Lightdash instance and make it production ready.
### 1. Add the Lightdash Helm repository
```bash theme={null}
helm repo add lightdash https://lightdash.github.io/helm-charts
```
### 2. Create a namespace for Lightdash
```bash theme={null}
kubectl create namespace lightdash
```
### 3. Create a minimum configuration for Lightdash
At minimum you should configure:
* `secrets.LIGHTDASH_SECRET` - this variable is used by Lightdash to encrypt data at rest in the database. You must keep this secret. If this is lost, you will not be able to access your data in Lightdash.
* `configMap.S3_REGION`, `configMap.S3_BUCKET`, `configMap.S3_ENDPOINT`, `secrets.S3_ACCESS_KEY` and `secrets.S3_SECRET_KEY` - These variables are for configuring external object storage with S3. For detailed information, refer to the [external object storage documentation](/self-host/customize-deployment/configure-lightdash-to-use-external-object-storage).
* `service.type` - by default the Lightdash UI and API is exposed on a `ClusterIP` service. This means that it is only accessible from within the Kubernetes cluster. If you want to access Lightdash from outside the cluster, you can change this to `LoadBalancer` or `NodePort`. See the [Kubernetes documentation](https://kubernetes.io/docs/concepts/services-networking/service/#publishing-services-service-types) for more information.
* `configMap.SITE_URL` - if you know the URL that Lightdash will be accessible at, you can set this variable. This will ensure that all links in Lightdash are correct. If you don't know the URL yet, you can leave this blank and update it later.
When setting up and configuring your Lightdash instance, you'll likely need to configure many additional environment variables. The Helm chart supports three ways to set environment variables:
* `configMap` - for non-sensitive configuration values
* `secrets` - for sensitive values like passwords and API keys
* `extraEnv` - for additional environment variables not covered by the chart's built-in options
For a complete list of available environment variables, see the [environment variables documentation](/self-host/customize-deployment/environment-variables). For detailed Helm chart configuration options, refer to the [Lightdash Helm charts repository](https://github.com/lightdash/helm-charts).
Example `values.yaml` file containing our configuration:
```yaml theme={null}
# values.yaml
secrets:
LIGHTDASH_SECRET: notverysecret
S3_ACCESS_KEY: secret # omit on AWS when using an IAM role, or on GKE when using S3_AUTH_MODE=gcp_oauth
S3_SECRET_KEY: secret # omit on AWS when using an IAM role, or on GKE when using S3_AUTH_MODE=gcp_oauth
# LIGHTDASH_LICENSE_KEY: your-enterprise-license-key # Optional: for Enterprise Edition features
configMap:
SITE_URL: https://lightdash.mycompany.com
S3_REGION: us
S3_BUCKET: lightdash
S3_ENDPOINT: https://storage.provider.com
# Additional environment variables not covered by configMap or secrets
extraEnv:
- name: CUSTOM_ENV_VAR
value: "custom-value"
- name: ANOTHER_CONFIG
value: "another-value"
service:
type: NodePort
```
#### Enterprise License Key (Optional)
If you have a Lightdash Enterprise license, you can enable enterprise features by adding your license key to the configuration:
```yaml theme={null}
secrets:
LIGHTDASH_LICENSE_KEY: your-enterprise-license-key-here
```
To obtain an Enterprise License Key, [contact our team](https://calendly.com/lightdash-cloud/enterprise?utm_source=docs\&utm_medium=referral\&utm_campaign=enterprise_licensing\&utm_content=self_host_config). For more details, see our [enterprise features documentation](/self-host/enterprise-features).
### 4. Install Lightdash with helm
Create a new helm release called `lightdash` using the `lightdash/lightdash` helm chart. In this example we're also using the namespace `-n lightdash`. Finally we apply our minimum configuration from above using `-f values.yaml`.
```bash theme={null}
helm install lightdash lightdash/lightdash -n lightdash -f values.yaml
```
### 4 (alternative). Install Lightdash with kubectl
If you prefer not to manage your deployment with helm, you can generate the kubernetes manifests and apply them using `kubectl`.
```bash theme={null}
helm template lightdash lightdash/lightdash -n lightdash -f values.yaml > lightdash.yaml
kubectl apply -f lightdash.yaml
```
Visit your `SITE_URL` to access Lightdash!
## Configure Lightdash for production
Now you have a working Lightdash instance, you can customise it to your needs. The following docs cover the most common configuration options, including those we recommend before going to production:
**Required configuration**
* [Configure Lightdash to use external object storage](/self-host/customize-deployment/configure-lightdash-to-use-external-object-storage)
**Recommended for production usage**
* [Secure Lightdash with HTTPS](/self-host/customize-deployment/secure-lightdash-with-https)
* [Configure Lightdash to use an external database](/self-host/customize-deployment/configure-lightdash-to-use-an-external-database)
* [Configure SMTP for email notifications](/self-host/customize-deployment/environment-variables#smtp)
* [Resource recommendations](/self-host/production-deployment-checklist#recommended-resources)
**Optional configuration**
* [Enterprise features](/self-host/enterprise-features)
* [Use SSO login for self-hosted Lightdash](/self-host/customize-deployment/use-sso-login-for-self-hosted-lightdash)
* [Enable scheduler in self-hosted Lightdash](/self-host/customize-deployment/scheduler)
* [Configure a Slack App for Lightdash](/self-host/customize-deployment/configure-a-slack-app-for-lightdash)
* [Configure environment variables for Lightdash](/self-host/customize-deployment/environment-variables)
* [Enable headless browser for Lightdash](/self-host/customize-deployment/enable-headless-browser-for-lightdash)
* [Configure Logging for Lightdash](/self-host/customize-deployment/environment-variables#logging)
* [Configure Prometheus metrics for Lightdash](/self-host/customize-deployment/configure-prometheus-metrics-for-self-hosted-lightdash)
# Self-host Lightdash using docker compose
Source: https://docs.lightdash.com/self-host/self-host-lightdash-docker-compose
Run a minimal Lightdash instance on your own machine with Docker Compose
This guide will give you a minimal Lightdash instance running on your local machine. It will not be accessible from the internet, but it will be accessible from your local machine. This is a great way to get started with Lightdash for a proof-of-concept without needing access to kubernetes.
## Prerequisites
* [Docker](https://docs.docker.com/get-docker)
* [Docker Compose](https://docs.docker.com/compose/install)
## 1. Clone the Lightdash repository
Clone the Lightdash code to your local machine. This will create a new directory called `./lightdash` (the Lightdash directory).
```bash theme={null}
# Clone the Lightdash repo
git clone https://github.com/lightdash/lightdash
cd lightdash
```
## 2. Update your ENV config
Edit all the ENV variables in `.env` to match your setup, eg:
```bash theme={null}
PGHOST=db
PGPORT=5432
PGUSER=pg_user *OR* machine username if no prior postgres set up
PGPASSWORD=pg_password *OR* blank if no prior postgres set up
PGDATABASE=postgres
DBT_DEMO_DIR=/*path*/*to*/lightdash/project/examples/full-jaffle-shop-demo
```
## 3. Create containers
You must set the following two environment variables:
* `PGPASSWORD` is the password used for the internal postgres database
* `LIGHTDASH_SECRET` is the secret used to encrypt data at rest in the database. If you lose this secret, you will not be able to access your data in Lightdash.
```bash theme={null}
export LIGHTDASH_SECRET="not very secret"
export PGPASSWORD="password"
docker compose -f docker-compose.yml --env-file .env up --detach --remove-orphans
```
If you have a Windows machine and get the error **Error response from daemon: i/o timeout**. Go to **Docker > Settings > General** and enable the option **Expose daemon on tcp\://localhost:2375 without TLS**
# Upgrade runbook
Source: https://docs.lightdash.com/self-host/upgrade-runbook
Upgrade sequences for Kubernetes, docker compose, and automated deployments
🛠 This page is for engineering teams self-hosting their own Lightdash instance. If you're on Lightdash Cloud, upgrades are handled for you automatically.
Safety-gated upgrades are in [Beta](/support/feature-maturity-levels). Every command on this page ships in a released image and the recovery paths are supported, but we're still refining the workflow on our own instances, so some sequences and outputs will change. [Open an issue](https://github.com/lightdash/lightdash/issues) if a step doesn't fit your deployment.
[Upgrade safety](/self-host/upgrade-safety) tells you **whether** an upgrade is safe to roll and whether there are required stops on the way. This page tells you **how to run it**: the exact sequence for each deployment shape, the commands that inspect and drive migrations, and what to do when something gets stuck.
Read them in that order. Decide first, then execute.
## No Lightdash account is needed to upgrade safely
Nothing in this runbook requires a Lightdash login, a personal access token, or an authenticated instance:
* `lightdash upgrade-check` reads the public release-safety index over plain HTTPS. It never contacts your instance and never asks who you are.
* The `migrate` commands run **inside your own Lightdash container** and authenticate with the same database environment variables the server already uses (`PGHOST`, `PGUSER`, `PGPASSWORD`, and friends). There is no second credential to provision.
That matters for air-gapped and locked-down deployments: the decision layer runs in CI with no secrets, and the execution layer runs in your cluster with credentials that already exist.
## What shipped when
Every command on this page is in a released image. Version-fence your runbook accordingly:
| Capability | Available from |
| --------------------------------------------------------------------------- | ----------------------- |
| Migration lease runtime, `migrate status`, `migrate wait`, `migrate unlock` | Lightdash `1.123.0` |
| Migration run ledger, `parked` state | Lightdash `1.124.0` |
| `migrate preflight` | Lightdash `1.125.0` |
| `lightdash upgrade-check` | Lightdash CLI `1.126.0` |
| `/api/v1/livez` and `/api/v1/readyz` probes | Lightdash `1.129.0` |
| `/api/v1/readyz` keeps working pods in service during a parked migration | Lightdash `1.169.1` |
On Lightdash `1.129.0` to `1.168.x`, do not point a readiness probe at `/api/v1/readyz`. A parked migration marked every working pod as not ready, so a single stuck migration removed the whole backend from the load balancer. Fixed in `1.169.1`. See [configuring health probes](/self-host/production-deployment-checklist#health-probes) for the current setup.
If you are upgrading *from* something older, that is fine. These are properties of the image you are upgrading *to*, and of the CLI you run the check with. The one place the old world still shows up is [rolling back across the `1.123.0` boundary](#rolling-back).
## The command surface
### `lightdash upgrade-check`
Answers the span question from the public index, with no login and no instance access. Full detail, including the JSON shape and the exit-code contract, is on [upgrade safety](/self-host/upgrade-safety#checking-an-upgrade-span).
```bash theme={null}
lightdash upgrade-check --from 1.130.0 --to 1.138.0
lightdash upgrade-check --from 1.130.0 --to 1.138.0 --json
```
Exit `0` means the whole span is proven safe to roll. Anything else, including a version the index cannot see, exits non-zero. Both `--from` and `--to` are required and must be `X.Y.Z` release versions.
`upgrade-check` only answers **forward** spans. Asking it about a rollback (a `--to` older than `--from`) is an error, not a verdict. Rollback guidance is [further down this page](#rolling-back).
### The `migrate` commands
These ship inside the Lightdash image and are the runtime execution layer. Invoke them the same way the image's own entrypoint does:
```bash theme={null}
pnpm -F backend migrate-production [flags]
```
You do not need to change directory first. The image's working directory is `/usr/app/packages/backend`, which is where the entrypoint runs this command, and both `kubectl exec` and `docker compose exec` inherit it. The chart's migration Job runs the same command from `/usr/app` instead. Either directory works.
| Command | What it does |
| ----------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------ |
| `up` | Runs pending Knex and Graphile Worker migrations. This is the default when no command is given, and it is what the image entrypoint and the Helm migration Job run |
| `preflight` | Checks migration safety **without changing the database**. Also runs automatically at the start of every `up` |
| `status` | Prints the migration lease, the Knex ledger, and recent migration run history |
| `wait` | Waits for migrations to finish. Follows a live migrator without racing it, and claims the lease itself if work is pending and no live holder exists |
| `unlock` | Clears migration locks for recovery, with attribution |
| Flag | Valid on | Meaning |
| -------------------- | --------------------------- | -------------------------------------------------------------------------------------------------------------------------- |
| `--timeout-ms ` | `up`, `wait` | How long to wait for another process to finish before giving up. Defaults to 30 minutes, or to `MIGRATION_WAIT_TIMEOUT_MS` |
| `--json` | `status`, `preflight` | Emit the payload as a single JSON object instead of human-readable lines |
| `--strict` | `up`, `preflight` | Promote preflight warnings to blockers |
| `--force` | `up`, `preflight`, `unlock` | Override blocking preflight checks, an actively held lease, or a legacy Knex lock |
| `--actor ` | `unlock` | Required on `unlock`. Records who released the lock |
| `-h`, `--help` | all | Print usage |
#### Running them in context
The commands need the deployment's database environment, so run them where that environment already exists.
Against a running pod:
```bash theme={null}
kubectl exec deploy/lightdash-backend -- \
pnpm -F backend migrate-production status
```
`exec` bypasses the image entrypoint, so this inspects without triggering a migration.
The examples on this page assume a release called `lightdash`, matching the `helm upgrade` command above. The chart names the backend Deployment `-backend` and the migration Job `-migrate`, where `` is your release name when it already contains `lightdash`, and `-lightdash` when it does not. `fullnameOverride` replaces it outright. If your release is named differently, list the real names:
```bash theme={null}
kubectl get deploy,job -l app.kubernetes.io/instance=
```
To run the **new** image's preflight before you upgrade, run a Job on the new tag that reuses your backend's environment sources:
```yaml theme={null}
apiVersion: batch/v1
kind: Job
metadata:
name: lightdash-preflight
spec:
backoffLimit: 0
template:
spec:
restartPolicy: Never
containers:
- name: preflight
image: lightdash/lightdash:
workingDir: /usr/app
command: ["pnpm", "-F", "backend", "migrate-production", "preflight"]
env:
- name: PGPASSWORD
valueFrom:
secretKeyRef:
name:
key:
envFrom:
# the same ConfigMap and Secret your backend deployment uses,
# both named by the chart
- configMapRef:
name: lightdash
- secretRef:
name: lightdash
```
Read the result with `kubectl logs job/lightdash-preflight`.
Against the running container:
```bash theme={null}
docker compose exec lightdash \
pnpm -F backend migrate-production status
```
To run the new image's preflight after pulling but before switching, override the entrypoint so the container does not migrate on the way in:
```bash theme={null}
docker compose run --rm --entrypoint pnpm lightdash \
-F backend migrate-production preflight
```
#### Reading `preflight`
Preflight probes the live database and reports one line per check, then a decision:
```text theme={null}
[RED PASS] version-path: The migration ledger structurally matches the target artifact direct-predecessor or up-to-date path
[RED PASS] postgres-version: ...
[RED PASS] migration-privileges: ...
[YELLOW WARN] long-transactions: ...
[INFO INFO] pending-migrations: ...
Preflight decision: proceed-with-warnings (0 red, 1 yellow)
```
| Check | Severity | What it catches |
| ---------------------- | ------------------------------------------------- | ---------------------------------------------------------------------------------------------------------------------------- |
| `version-path` | red (yellow when the image has no baked artifact) | A ledger that diverges from the image's migration files, an unreadable release-safety artifact, or unresolved required stops |
| `postgres-version` | red | A PostgreSQL server older than major version 12 |
| `migration-privileges` | red | The migration role cannot create in its schema, or does not own tables the pending migrations touch |
| `held-locks` | yellow | Existing locks on tables the pending migrations will touch |
| `long-transactions` | yellow | Transactions running longer than 5 minutes against those tables |
| `disk-headroom` | yellow | Less than 5 GiB free, when you tell it how much there is via `MIGRATION_PREFLIGHT_DISK_HEADROOM_BYTES` |
| `pending-migrations` | info | The inventory of what is about to run, including whether each migration runs in a transaction |
The decision is one of `proceed`, `proceed-with-warnings`, `abort`, or `force-proceed`. Any red failure aborts; `--strict` makes yellow warnings abort too; `--force` turns an abort into `force-proceed` and prints a loud override banner. A standalone `preflight` that aborts exits non-zero, which makes it a usable CI gate.
#### Reading `status`
`status` reports one of four states:
| State | Meaning |
| ----------- | ---------------------------------------------------------------------------------------------------------------------- |
| `idle` | Nobody holds the lease and nothing is parked |
| `migrating` | A process holds the lease and is heartbeating |
| `stale` | A process holds the lease but its heartbeat has expired, 75 seconds after the last one. Another process will take over |
| `parked` | Migration failed its retries and stopped. It will not retry on this app version until a human intervenes |
Alongside the state it prints the lease holder (hostname, pod, app version, current migration, last heartbeat), the parked details if any, the completed and pending Knex migration counts, the ledger classification, and recent migration runs with their outcomes. Any prior `unlock` is recorded against the run that followed it, so the audit trail survives.
`--json` gives you the same payload for automation.
#### When you need `wait`
`wait` blocks until the database is migrated. While another process holds a live lease, it follows that process and never migrates alongside it. If migrations are still pending and the lease is unheld or has expired, `wait` claims the lease and runs them itself. It gives up after 30 minutes by default; change that with `--timeout-ms` or `MIGRATION_WAIT_TIMEOUT_MS`.
So `wait` is not a read-only command. It will not race a live migrator, but it will become the migrator when there is work to do and nobody is doing it. Use `status` when you only want to look.
You rarely need to run `wait` by hand. On the non-Job path, `up` already falls back to this exact behaviour when it loses the race for the lease, which is how the pod that does not migrate ends up waiting for the pod that does.
## Kubernetes and Helm
This is step one, not advice. Production recovery is forward-only: there are no down-migrations to unwind a bad upgrade, so a current backup is what makes the worst case survivable.
Take a fresh backup, and confirm it restores. If you run point-in-time recovery, confirm the window covers the whole upgrade.
Read the release notes for every release you are crossing, then check the span:
```bash theme={null}
lightdash upgrade-check --from 1.130.0 --to 1.138.0
```
Green means `upgrade.mode: RollingUpdate` is advised. Anything else means `upgrade.mode: Recreate` and a maintenance window. The upgrade check supplies this decision. The chart does not fetch or calculate the verdict. If the check reports required stops, upgrade to the first stop and let it finish before continuing. See [upgrade safety](/self-host/upgrade-safety) for how verdicts compose across a span.
Set the release-wide mode from the upgrade check. This example is for a `false` or `unknown` verdict:
```yaml theme={null}
upgrade:
mode: Recreate
migrationJob:
enabled: true
```
Set `upgrade.mode: RollingUpdate` for a `true` verdict. Leave `upgrade.mode` empty to preserve the legacy per-component Deployment strategies. When the mode is empty and a legacy backend or enabled worker strategy is `Recreate`, the chart still uses the shutdown barrier with `migrationJob.enabled`.
Chart `2.16.284` and later supports `upgrade.mode` and the automatic shutdown barrier. Chart `2.16.283` and earlier needs the manual fallback in the next step.
With chart `2.16.284` or later, `migrationJob.enabled: true`, and `upgrade.mode: Recreate`, the chart automatically removes the release-managed HPA, scales all database-capable Lightdash workloads to zero, waits for their pods to terminate, and then runs the pre-upgrade migration Job. Do not scale workloads manually for this path.
Helm restores the configured replicas and release-managed HPA after a successful upgrade. If the shutdown, migration, or upgrade fails, the release can remain stopped. Follow [recovery](#recovery) or [manual rollback](#rolling-back) before restoring workloads.
Use this fallback with chart `2.16.283` and earlier, and for custom or manual deployments. For a `false` or `unknown` verdict with `migrationJob.enabled: true`, scale every Lightdash application workload to zero before you run `helm upgrade`.
First, inventory the release-managed HorizontalPodAutoscalers (HPAs). Remove them, or suspend them if your platform supports that, before you scale workloads. An active HPA can immediately scale a deployment back up:
```bash theme={null}
kubectl get hpa -l app.kubernetes.io/instance=lightdash
kubectl delete hpa -l app.kubernetes.io/instance=lightdash
```
Then list the workloads for your release and scale the backend and every enabled worker deployment to zero:
```bash theme={null}
kubectl get deployments -l app.kubernetes.io/instance=lightdash
kubectl scale deployments -l app.kubernetes.io/instance=lightdash --replicas=0
```
Verify that all Lightdash application pods have terminated before continuing. Do not run `helm upgrade` while any of those pods are `Running` or `Terminating`:
```bash theme={null}
kubectl get pods -l app.kubernetes.io/instance=lightdash
```
If old application code remains running while the migration Job changes the database schema, it can run against an incompatible schema.
Keep the migration Job enabled. Helm recreates the release-managed HPAs and restores the configured replica counts only after a successful upgrade. If the migration Job or upgrade fails, the workloads stay stopped. Follow [recovery](#recovery) or [manual rollback](#rolling-back) before you restore the HPAs or workloads.
`up` runs preflight automatically, so this step buys you the answer *before* you commit to the deploy rather than during it. Run the [one-off Job](#running-them-in-context) on the new tag and read its report.
Worth doing when the span ships heavy migrations, when the database is large, or when you want a green light before opening a maintenance window.
Bump `image.tag` in your values and upgrade:
```bash theme={null}
helm repo update lightdash
helm upgrade -f values.yml lightdash lightdash/lightdash
```
With `migrationJob.enabled: true`, the chart runs migrations in a `pre-install,pre-upgrade` hook Job and the backend pods then start without migrating, so replicas never race for the lock. This is the recommended setup for any multi-replica deployment. For a `false` or `unknown` verdict, chart `2.16.284` and later runs the [automatic shutdown barrier](#run-the-automatic-shutdown-barrier); chart `2.16.283` and earlier uses the [manual fallback](#scale-application-workloads-manually-when-required). Without the Job, the pods migrate at startup and the lease runtime arbitrates between them: one pod wins and migrates, the rest wait.
Follow the migration:
```bash theme={null}
kubectl logs -f job/lightdash-migrate # when migrationJob is enabled
kubectl exec deploy/lightdash-backend -- \
pnpm -F backend migrate-production status
```
Then confirm the instance is actually ready. On `1.129.0` and later, `/api/v1/readyz` returns `200` only when the schema gate has passed and the migration run ledger is clean:
```bash theme={null}
curl -sS -o /dev/null -w '%{http_code}\n' https://lightdash.example.com/api/v1/readyz
```
A `503` carries a `reason`: `schema_pending` (migrations still outstanding), `migration_parked` (a migration failed and stopped), `migration_ledger_unavailable`, or `db_unavailable`. `/api/v1/livez` answers without touching the database, which is why it is the right liveness probe and the wrong readiness signal.
`/api/v1/health` also answers, but it queries the database on every call. That makes it a fine manual check to run after a deploy, and a poor probe, since a brief database blip fails it on every pod at once. Worker pods serve their own handler at this same path, backed by in-memory worker state rather than the database, and workers do not serve `/api/v1/readyz` at all. For which endpoint belongs on which probe, see the [production deployment checklist](/self-host/production-deployment-checklist#health-probes).
Confirm the version too, then upgrade the [Lightdash CLI](/workflow/cli/install#updating-the-lightdash-cli) to match.
Redeploy the previous image tag. Code rollback is the supported mitigation: it takes the new code out of service while leaving the migrated schema in place, which is the safe direction. Read [rolling back](#rolling-back) before you reach for a database rollback, which is a different and much heavier operation.
## Docker compose
A single-container compose deployment has **no zero-downtime upgrade path**. Recreating the container stops the old version, boots the new one, and runs migrations before the server accepts traffic. A green `rollingUpdateSafe` verdict does not change that: it certifies that old and new code *may* overlap, and compose never overlaps them. Plan for a few minutes of downtime, more if the release ships heavy migrations.
Step one here too, for the same reason. If your Postgres runs in the compose stack, back up the volume as well as the database.
```bash theme={null}
lightdash upgrade-check --from 1.130.0 --to 1.138.0
```
Respect required stops: upgrade to the stop, let it come up cleanly, then continue.
Pull the new image first, then run preflight against it without letting the entrypoint migrate:
```bash theme={null}
docker compose pull lightdash
docker compose run --rm --entrypoint pnpm lightdash \
-F backend migrate-production preflight
```
Pin the new tag (or pull it, if you track a floating tag), then recreate:
```bash theme={null}
docker compose pull lightdash
docker compose up --detach --remove-orphans
```
The new container runs migrations on the way up, so the server is unavailable until they finish. Follow along with `docker compose logs -f lightdash`.
```bash theme={null}
docker compose exec lightdash \
pnpm -F backend migrate-production status
curl -sS -o /dev/null -w '%{http_code}\n' http://localhost:8080/api/v1/readyz
```
Expect `idle` with no pending migrations, and `200`.
## Automated upgrades
The Lightdash repository ships a generic reference automation at [`examples/upgrade-automation`](https://github.com/lightdash/lightdash/tree/main/examples/upgrade-automation): a GitHub Actions workflow plus two composite actions that keep a deployment on the newest release the public safety gate can reach. It is deliberately generic, sends no telemetry, and keeps all of its evidence in your own repository. Copy it and point it at the file that pins your image tag.
The loop it implements is the sequence to copy even if you build your own:
Schedule, manual dispatch, or a `repository_dispatch` event when a release lands. These are detection mechanisms only. There is no upgrade window and no veto delay: a release is considered as soon as a trigger notices it.
Read the currently pinned version, then run `lightdash upgrade-check` against the public index to pick the **newest green-reachable target**. Required stops resolve hop by hop, so the automation steps *to* a stop rather than over it, and never crosses a red break silently. Unknown or incomplete safety data fails closed and retries on the next run.
The bump lands as a pull request carrying the full verdict JSON, so the evidence for the decision is attached to the change that acts on it.
* **Green verdict:** auto-merge, zero-touch. Nobody is asked to approve a machine-verified safe hop.
* **Not green:** hold the pull request and notify a channel with a plain explanation of what stopped it. Yellow and unknown both count as not green.
Merging the pin triggers your existing deployment workflow. The automation does not deploy; it drives the thing that does.
Poll `/api/v1/readyz` until it returns `200` and the served version matches the version you pinned. Require **three consecutive** green polls, inside a configurable budget that defaults to about 20 minutes. One green poll can catch an old pod that has not been replaced yet.
If verification fails, freeze: open a freeze issue, escalate to the channel, and stop planning further upgrades until a human closes it. There is **no auto-rollback**. Recovery is forward-only, and an automation that rolls back unattended is an automation that can undo a migration nobody watched.
### Pass a checked mode to Helm
The reference automation only promotes green-reachable targets. Use the route below when your deployment pipeline intentionally upgrades to a checked target that can require `Recreate`. The chart does not fetch the verdict. The pipeline passes the checked mode to Helm.
```bash theme={null}
set -euo pipefail
: "${CURRENT_VERSION:?Set CURRENT_VERSION}"
: "${TARGET_VERSION:?Set TARGET_VERSION}"
: "${TARGET_IMAGE_TAG:=$TARGET_VERSION}"
: "${VALUES_FILE:=values.yml}"
version_gte() {
local current_major current_minor current_patch minimum_major minimum_minor minimum_patch
IFS=. read -r current_major current_minor current_patch <<<"$1"
IFS=. read -r minimum_major minimum_minor minimum_patch <<<"$2"
if (( 10#$current_major != 10#$minimum_major )); then
(( 10#$current_major > 10#$minimum_major ))
return
fi
if (( 10#$current_minor != 10#$minimum_minor )); then
(( 10#$current_minor > 10#$minimum_minor ))
return
fi
(( 10#$current_patch >= 10#$minimum_patch ))
}
set +e
CHECK_JSON="$(lightdash upgrade-check --from "$CURRENT_VERSION" --to "$TARGET_VERSION" --json)"
CHECK_EXIT=$?
set -e
if ! jq -e \
--arg from "$CURRENT_VERSION" \
--arg to "$TARGET_VERSION" \
'
(.fromVersion == $from) and
(.toVersion == $to) and
(.safe | type == "boolean") and
(.verdict == true or .verdict == false or .verdict == "unknown") and
(.requiredStops | type == "array" and all(.[]; type == "string")) and
(.minPreviousVersion == null or (.minPreviousVersion | type == "string")) and
(.missingRanges | type == "array" and all(.[]; type == "object" and (.afterVersion | type == "string") and (.beforeVersion | type == "string")))
' >/dev/null <<<"$CHECK_JSON"; then
printf '%s\n' 'upgrade-check returned no usable verdict; aborting.' >&2
exit 1
fi
MINIMUM_VERSION="$(jq -r '.minPreviousVersion // empty' <<<"$CHECK_JSON")"
if [[ -n "$MINIMUM_VERSION" ]] && ! version_gte "$CURRENT_VERSION" "$MINIMUM_VERSION"; then
printf 'Current version %s is below the minimum direct-upgrade version %s.\n' "$CURRENT_VERSION" "$MINIMUM_VERSION" >&2
exit 1
fi
EARLIEST_INTERMEDIATE_STOP="$(jq -r --arg target "$TARGET_VERSION" '
[.requiredStops[] | select(. != $target) | {version: ., parts: (split(".") | map(tonumber))}]
| sort_by(.parts)
| .[0].version // empty
' <<<"$CHECK_JSON")"
if [[ -n "$EARLIEST_INTERMEDIATE_STOP" ]]; then
printf 'Upgrade first to required stop %s, then check the next hop.\n' "$EARLIEST_INTERMEDIATE_STOP" >&2
exit 1
fi
REQUIRED_STOP_COUNT="$(jq -r '.requiredStops | length' <<<"$CHECK_JSON")"
if [[ "$REQUIRED_STOP_COUNT" != "0" ]]; then
STOP_TARGET_VERDICT="$(jq -r --arg target "$TARGET_VERSION" '
if .requiredStops | length == 1 and .[0] == $target then .verdict else "invalid" end
' <<<"$CHECK_JSON")"
case "$STOP_TARGET_VERDICT:$CHECK_EXIT" in
true:1) UPGRADE_MODE=RollingUpdate ;;
false:1|unknown:1) UPGRADE_MODE=Recreate ;;
*)
printf '%s\n' 'The required-stop target has an unusable verdict; aborting.' >&2
exit 1
;;
esac
else
CHECK_STATE="$(jq -r '[.safe, .verdict] | @tsv' <<<"$CHECK_JSON")"
case "$CHECK_STATE:$CHECK_EXIT" in
$'true\ttrue:0') UPGRADE_MODE=RollingUpdate ;;
$'false\tfalse:1'|$'false\tunknown:1') UPGRADE_MODE=Recreate ;;
*)
printf '%s\n' 'upgrade-check returned an unusable result; aborting.' >&2
exit 1
;;
esac
fi
helm upgrade -f "$VALUES_FILE" \
--set-string upgrade.mode="$UPGRADE_MODE" \
--set-string image.tag="$TARGET_IMAGE_TAG" \
lightdash lightdash/lightdash
```
`--set-string` overrides any static `upgrade.mode` in the values file. Derive the mode for every upgrade. An earlier required stop is a separate target. When `requiredStops` is exactly `[TARGET_VERSION]` and the minimum version is satisfied, `true` selects `RollingUpdate`; `false` or `unknown` selects `Recreate`. A current version below `minPreviousVersion` aborts the direct hop. Malformed output, an execution failure, or a fetch failure has no usable verdict and aborts the pipeline.
### Recommended default policy
**Auto-apply when green, hold when not.** A proven-safe hop is exactly the case where human review adds latency and no information; everything else is exactly the case where it adds both. Keep the freeze switch manual and obvious, so disarming upgrades during an incident is one action rather than a code change.
## Recovery
### The migration Job failed
On the `migrationJob.enabled` path, a failed upgrade shows up as a failed Job. Read its logs before you reach for anything else. The migration's own error says what broke; the commands below only tell you the state it left behind.
```bash theme={null}
kubectl logs job/lightdash-migrate
kubectl describe job/lightdash-migrate
```
The Job retries before it gives up. `migrationJob.backoffLimit` defaults to `10`, so Kubernetes marks the Job failed only after 11 attempts. Each attempt runs in its own pod, so `kubectl logs job/...` shows you one of them and `kubectl get pods` shows the rest.
**Those logs delete themselves.** `migrationJob.ttlSecondsAfterFinished` defaults to `100`, so Kubernetes removes the Job about 100 seconds after it finishes — failed or succeeded — and the pod logs go with it. If you are debugging upgrades, raise that value in your values file, or ship the Job's logs off the cluster before the window closes.
If you missed the window, the database still has the durable record. `migrate status` reports the parked details and recent migration runs with their outcomes, and that record outlives the Job object.
To retry after you have fixed the cause, re-run `helm upgrade` to recreate the hook Job. See [what resumes on its own](#after-an-unlock-what-resumes-on-its-own).
### A migration is stuck
Start by looking, not by fixing:
```bash theme={null}
kubectl exec deploy/lightdash-backend -- \
pnpm -F backend migrate-production status
```
* **`migrating`** with a recent heartbeat: it is working. Migrations on large tables can take a long time. Leave it alone.
* **`stale`**: the holder died. The lease expires 75 seconds after its last heartbeat, and another process takes it over automatically. No action needed in most cases.
* **`parked`**: the migration failed its retries (three attempts with backoff) and stopped deliberately. The same app version will refuse to retry, which is what stops a crash-looping pod from hammering a half-applied migration. Fix the cause, then deploy a fixed version, or unlock with attribution and retry.
Do **not** edit the `knex_migrations_lock` table by hand on `1.123.0` and later. The lease runtime holds locks that live migrations legitimately own, and clearing them manually can let a second migrator start on top of the first. Use `migrate status` to inspect and `migrate unlock` to release.
### Releasing a lock
```bash theme={null}
kubectl exec deploy/lightdash-backend -- \
pnpm -F backend migrate-production unlock --actor "alex@example.com"
```
`--actor` is mandatory and is recorded against the next migration run, so an unlock is always attributable afterwards.
`unlock` refuses, by design, when the lease is actively held by a live process, or when a pre-lease Knex lock is still held. Both refusals mean "something may still be running". Terminate the holder first. Only then reach for `--force`, which overrides the refusal.
### After an unlock: what resumes on its own
| Deployment | Behaviour |
| -------------------------------------- | -------------------------------------------------------------------------------------------------------------------------------------------------------- |
| Kubernetes, pods migrating at startup | Self-resumes. The waiting followers re-race for the freed lease and one of them takes over |
| Kubernetes with `migrationJob.enabled` | May need re-triggering. If the hook Job exhausted its `backoffLimit`, nothing is left to claim the lease. Re-run `helm upgrade` to recreate the hook Job |
| docker compose | Needs a container restart: `docker compose restart lightdash` |
### Rebuilding an instance from scratch
Three things are needed to stand an instance back up, so keep all three recoverable:
* the Postgres backup,
* the `LIGHTDASH_SECRET` (it decrypts data at rest — losing it means losing access to encrypted data), and
* your Helm values.
## Rolling back
Rolling back means redeploying an older image. It does **not** unwind the database, and Lightdash does not run down-migrations in production.
* **Back up first.** Always, and before the upgrade rather than after you need it. A backup restore is the only path that undoes a schema change, and it costs you everything written since the snapshot.
* **Prefer small spans.** One release back is a decision. Ten releases back is an archaeology project. Frequent, small upgrades keep the rollback target close.
* **Roll back promptly.** Schema compatibility is not data compatibility. The new version may have written values the old code mishandles or cannot read, and that risk grows every hour the new version serves traffic. A rollback ten minutes in is a very different proposition from one ten days in.
* **`upgrade-check` will not help here.** It answers forward spans only; reverse spans are an error, not a verdict. Use the guidance on this page instead.
### The `1.123.0` fence
The image you roll back **to** determines what happens when it meets a database that is ahead of it:
* **`1.123.0` and later**: the migrate command classifies the ledger itself. A database carrying migrations the image does not have is recognised as database-ahead and the image starts normally. `ALLOW_MISSING_MIGRATIONS` is a deprecated no-op on this path and logs a warning saying so.
* **Before `1.123.0`**: the image validates the migration directory at boot and treats any database-only migration as a corrupt migration directory. It will refuse to start. Set `ALLOW_MISSING_MIGRATIONS=true` on that deployment so it can start against the newer database.
So a rollback from `1.130.0` to `1.124.0` needs nothing extra, while a rollback from `1.130.0` to `1.122.0` needs `ALLOW_MISSING_MIGRATIONS=true`.
### Migration batch granularity
The lease runtime applies each migration as its **own** Knex batch, rather than grouping a whole deploy into one batch as stock Knex does. That changes the granularity of the development-tooling rollback: `knex migrate:rollback` unwinds **one migration per invocation**, not one deploy per invocation.
This matters mid-incident, when someone reaches for a rollback expecting a whole deploy to come off in one command. It will not. Production recovery remains forward-only regardless.
## For contributors
If you write migrations, the safety verdict this runbook depends on is generated from **declarations in the migration files themselves**. A migration containing a detected breaking operation must declare it in the same file:
```typescript theme={null}
export const breaking = {
reason: 'old pods read legacy_column',
requiredStop: true,
};
```
Raw SQL that the static lint cannot classify needs an explicit `export const classification = { kind: 'safe' | 'breaking', reason: '...' }`. Declaring a break is not a way to make CI pass: it flips the release to not rolling-safe and advises every self-hosted deployment to use `Recreate`. Try an expand-only redesign first.
The full rules, including the idempotency contract for `transaction: false` migrations and the `down()` requirements, live in `packages/backend/src/database/migrations/CLAUDE.md` in the Lightdash repository.
# Upgrade safety
Source: https://docs.lightdash.com/self-host/upgrade-safety
Read the release-safety artifact, check an upgrade span, and pick the right deployment strategy
🛠 This page is for engineering teams self-hosting their own Lightdash instance. If you're on Lightdash Cloud, upgrades are handled for you automatically.
Release-safety artifacts are in [Beta](/support/feature-maturity-levels). They are published for every release and are safe to read today, but we're still refining how risk is classified and what the artifact carries, so fields and verdicts may change. Build automation against it and keep that automation easy to change, and [tell us](https://github.com/lightdash/lightdash/issues) if the artifact doesn't answer a question you need it to.
Every Lightdash release publishes a machine-readable answer to the question: **"is upgrading from version X to version Y safe to roll, and are there required stops on the way?"**
Two documents carry that answer:
| Document | Scope | Where to get it |
| --------------------------- | --------------------------------------------------------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `release-safety.json` | One release: the step from `previousVersion` to `version` | Attached to every [GitHub release](https://github.com/lightdash/lightdash/releases) as an asset, and committed at the repository root of the release tag (so it ships inside the Docker image) |
| `release-safety-index.json` | Every release back to the index floor (\~12 months) | Committed at the repository root: [`raw.githubusercontent.com/lightdash/lightdash/main/release-safety-index.json`](https://raw.githubusercontent.com/lightdash/lightdash/main/release-safety-index.json) |
Both are public — no account or license required — and both are designed to be consumed by your own automation (CI checks, GitOps hooks) as well as by a human before an upgrade.
## The one rule that matters
**Only an explicit `rollingUpdateSafe: true` means a rolling update is advised. `unknown` means NOT safe.**
`unknown` is what the pipeline publishes when it could not *prove* safety — a degraded check, an unclassifiable change, an old release nobody vouched for. Treat every `unknown` exactly like `false`: use the `Recreate` strategy. Never write automation that treats "not false" as safe.
The check supplies this verdict. The Helm chart does not fetch or calculate it. Set `upgrade.mode: RollingUpdate` for `true`, and `upgrade.mode: Recreate` for `false` or `unknown`. With `migrationJob.enabled`, chart `2.16.284` and later stops application workloads before its pre-upgrade migration hook when the mode is `Recreate`. Chart `2.16.283` and earlier needs the manual fallback in the [upgrade runbook](/self-host/upgrade-runbook#configure-the-upgrade-mode).
Everything else on this page is detail on top of that rule.
This page is the decision layer: whether an upgrade is safe. Once you have your verdict, the [upgrade runbook](/self-host/upgrade-runbook) is the execution layer: the sequence for Kubernetes, docker compose, and automation, plus the `migrate` command reference, recovery, and rollback.
## How to read the signal
1. **Verdicts compose by AND across a span.** Upgrading across several releases (say `1.111.0` → `1.115.0`) is rolling-safe only if *every* release in the span is `rollingUpdateSafe: true`. One `false` or `unknown` anywhere in the span means the whole upgrade needs `Recreate`. The cumulative index exists so you can evaluate a whole span in one fetch.
2. **Required stops are hard requirements.** If any release in your span lists a version in `requiredStops`, you must upgrade *to that version first*, let it run its migrations, and then continue. Skipping a stop runs later migrations against a schema that's missing the prerequisites they assume — which is how databases end up in states that need manual repair.
3. **`minPreviousVersion` floors direct jumps.** Each release states the oldest version you may upgrade from directly. If you're on something older, upgrade to an intermediate version first.
4. **Versions older than the index floor route through the floor.** The index reaches back to `0.1893.0`. If you're running something older, first upgrade to `0.1893.0` (the floor entry is marked `syntheticRequiredStop: true` for exactly this reason), then evaluate the rest of your span normally. In practice: upgrading from `0.1050.0` to `1.121.0` means `0.1050.0` → `0.1893.0` → whatever the span check from there tells you.
5. **Backfilled entries are conservative.** Entries marked `backfilled: true` were generated retroactively — nobody vouched for those releases at the time they shipped. Expect `unknown` verdicts there, and plan a `Recreate` upgrade for any span that crosses them.
## Checking an upgrade span
The answer depends on exactly three inputs: the version you're running, the version you're targeting, and every release in between. The [Lightdash CLI](/workflow/cli/install) (version `1.126.0` or later) answers it in one command:
```bash theme={null}
lightdash upgrade-check --from 1.111.0 --to 1.115.0
```
```text theme={null}
Release safety check: 1.111.0 -> 1.115.0
Direction: upgrade
Verdict: UNSAFE
Unsafe rolling-update safety: 1.112.0
Unknown rolling-update safety: 1.113.0, 1.113.1, 1.114.0, 1.115.0
Minimum previous version: 1.111.0
Covered releases: 1.112.0, 1.113.0, 1.113.1, 1.114.0, 1.115.0
```
The command fetches the public index and applies the span rules from this page — AND-composition, required stops, minimum versions — locally. It needs **no Lightdash login, token, or instance access**, so it runs anywhere, including CI against an air-gapped production instance. Here the verdict is `UNSAFE` because the span crosses one release known not to be rolling-safe and several unproven ones — so **deploy with `Recreate`**: stop the old version, then start the new one.
### Using it as a CI gate
The exit code carries the verdict: **exit `0` only when the whole span is proven safe to roll**. Any other outcome — an `unknown` or `false` release in the span, a required stop, a version the index doesn't cover, a fetch failure — exits non-zero. That fail-closed contract means you can use the bare command as a pipeline gate:
```bash theme={null}
lightdash upgrade-check --from "$CURRENT_VERSION" --to "$TARGET_VERSION"
```
For automation that needs the detail, `--json` prints a stable machine-readable object (`fromVersion`, `toVersion`, `direction`, `safe`, `verdict`, `requiredStops`, `minPreviousVersion`, `coveredVersions`, `missingRanges`):
```bash theme={null}
lightdash upgrade-check --from 1.111.0 --to 1.115.0 --json | jq .safe
```
## What the verdict means for your deployment
| Verdict for the span | Kubernetes / Helm | docker compose |
| --------------------------------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------- | ------------------------------------------------------------- |
| Every release `rollingUpdateSafe: true` | Set `upgrade.mode: RollingUpdate` | `docker compose up -d` with the new tag |
| Anything `false` or `unknown` | Set `upgrade.mode: Recreate`. With `migrationJob.enabled`, use the [runbook's automatic path or fallback](/self-host/upgrade-runbook#configure-the-upgrade-mode) | `docker compose down`, update the tag, `docker compose up -d` |
`Recreate` means a short window of downtime. With `migrationJob.enabled` and chart `2.16.284` or later, the chart stops database-capable application workloads before it migrates. Chart `2.16.283` and earlier needs the [manual fallback](/self-host/upgrade-runbook#scale-application-workloads-manually-when-required). Without a migration Job, `Recreate` still applies the Deployment strategies during Helm's normal apply phase. A rolling update is only appropriate when the verdict certifies that behaviour as safe.
## Worked example: reading a release artifact
This is the real artifact attached to release `1.121.0`:
```json theme={null}
{
"schemaVersion": "2",
"version": "1.121.0",
"previousVersion": "1.120.1",
"releaseDate": "2026-08-11T11:08:39.052Z",
"migrations": {
"present": false,
"count": 0,
"coreCount": 0,
"eeCount": 0,
"files": []
},
"compatibility": {
"rollingUpdateSafe": true,
"recommendedStrategy": "RollingUpdate"
},
"api": {
"rest": {
"checked": true,
"breaking": false,
"changes": [],
"breakingCount": 0,
"advisories": [],
"advisoryCount": 0
},
"mcp": {
"checked": true,
"breaking": false,
"changes": [],
"breakingCount": 0,
"advisories": [],
"advisoryCount": 0
}
},
"config": {
"checked": true,
"breaking": false,
"changes": []
},
"upgrade": {
"minPreviousVersion": "1.111.0",
"requiredStops": []
},
"declaredBreaks": []
}
```
Reading it top to bottom:
* **No migrations ship in this release** (`migrations.present: false`), so there is no schema change to coordinate.
* **`rollingUpdateSafe: true`** — this is the explicit green verdict. Upgrading from `1.120.1` (the `previousVersion`) to `1.121.0` can be a rolling update.
* **All three change surfaces were checked and came back clean**: no breaking REST API changes, no breaking MCP tool changes (not even non-breaking advisories on either), and no environment variable removals, renames, or default changes.
* **You may jump here directly from `1.111.0` or newer** (`upgrade.minPreviousVersion`), with no required stops on the way — but remember the span rule: the jump is only *rolling*-safe if every release in between is, which you check against the index, not this single artifact.
* **No engineer declared a breaking change** (`declaredBreaks` is empty).
## Field reference: `release-safety.json`
Fields that can't prove safety report the string `"unknown"` instead of a boolean — and per the rule above, you consume `"unknown"` as unsafe.
### Top level
| Field | Type | Meaning |
| ----------------- | -------------- | ----------------------------------------------------------------- |
| `schemaVersion` | string | Always `"2"` for this format |
| `version` | string | The release this artifact describes |
| `previousVersion` | string \| null | The release immediately before it — the step this artifact covers |
| `releaseDate` | ISO date-time | When the release was published |
### `migrations`
| Field | Type | Meaning |
| ------------------------------- | ---------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `present` | boolean \| `"unknown"` | Whether this release ships database migrations |
| `count`, `coreCount`, `eeCount` | integer | How many, split between core and Enterprise migrations |
| `files[].name` | string | Migration file name |
| `files[].edition` | `core` \| `ee` | Which edition ships it |
| `files[].tables` | string\[] | Tables the migration touches |
| `files[].heaviness` | object | Three verdicts — `locksTable`, `rewritesTable`, `scansTable` — each boolean or `"unknown"`, flagging migrations that may lock, rewrite, or scan whole tables (and therefore take time or block writes on large databases) |
### `compatibility`
| Field | Type | Meaning |
| --------------------- | ----------------------------- | ------------------------------------------------------------------------------------------------------ |
| `rollingUpdateSafe` | boolean \| `"unknown"` | **The verdict.** Only `true` means a rolling update is advised |
| `recommendedStrategy` | `RollingUpdate` \| `Recreate` | A convenience rendering of the verdict. Gate your automation on `rollingUpdateSafe`, not on this field |
### `api` and `config`
| Field | Type | Meaning |
| --------------------- | ---------------------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `api.rest`, `api.mcp` | object | One entry per API surface — REST endpoints and MCP tools — with the fields below |
| `….checked` | boolean | Whether the diff for that surface actually ran |
| `….breaking` | boolean \| `"unknown"` | Whether the surface has consumer-breaking changes |
| `….changes[]` | string\[] | The breaking changes, one human-readable line each (e.g. `GET /api/v1/… — response property removed`). The list is capped with an explicit overflow line; `breakingCount` always carries the full total |
| `….breakingCount` | integer | Uncapped total number of breaking changes |
| `….advisories[]` | string\[] | Non-breaking advisory notes (for example, a response enum gaining a new value) — worth a scan if you consume the API strictly, but they never affect the `breaking` verdict. Capped like `changes` |
| `….advisoryCount` | integer | Uncapped total number of advisory notes |
| `config.checked` | boolean | Whether the environment variable surface was diffed |
| `config.breaking` | boolean \| `"unknown"` | Whether any config change is breaking |
| `config.changes[]` | object | Each change is `removed` (with `previousDefault`), `renamed` (with `previousName` and `defaultValue`), or `defaultChanged` (with `previousDefault` and `defaultValue`) — scan this list for any environment variable you set explicitly |
### `upgrade` and `declaredBreaks`
| Field | Type | Meaning |
| ---------------------------- | -------------- | ------------------------------------------------------------------------------------------------------------------------------------------ |
| `upgrade.minPreviousVersion` | string \| null | Oldest version you may upgrade from directly |
| `upgrade.requiredStops` | string\[] | Versions you must pass through on the way to this release |
| `declaredBreaks[]` | object | Breaking changes declared by engineers in the source (`file`, `line`, `reason`, and whether the break makes this release a `requiredStop`) |
## Field reference: `release-safety-index.json`
The index is a derived summary — one entry per release, oldest first. Per-release artifacts remain the source of truth.
| Field | Type | Meaning |
| ----------------------------------------------------- | ---------------------- | -------------------------------------------------------------------------------------------- |
| `backfillFloorVersion` | string | The oldest release the index covers (`0.1893.0`) |
| `entries[].version`, `previousVersion`, `releaseDate` | | Same meaning as the per-release artifact |
| `entries[].rollingUpdateSafe` | boolean \| `"unknown"` | The verdict for that single release step |
| `entries[].requiredStops`, `minPreviousVersion` | | Same meaning as the per-release artifact |
| `entries[].backfilled` | boolean | `true` when the entry was generated retroactively — expect conservative (`unknown`) verdicts |
| `entries[].syntheticRequiredStop` | boolean | `true` only on the floor entry: versions older than the floor must upgrade to it first |
## Before you upgrade: checklist
* Read the [release notes](https://github.com/lightdash/lightdash/releases) for every release in your span.
* Run the [span check](#checking-an-upgrade-span) — note the verdict, required stops, and `minPreviousVersion`.
* If the span verdict is anything other than `true`, plan a maintenance window and set `upgrade.mode: Recreate`. With `migrationJob.enabled`, follow the [upgrade runbook](/self-host/upgrade-runbook#configure-the-upgrade-mode).
* If migrations are present, check their `heaviness` flags in the per-release artifacts — table rewrites and scans on large tables take time.
* Scan `config.changes` for environment variables you set explicitly.
* Confirm your database backup (and [point-in-time recovery](/self-host/production-deployment-checklist), if configured) is current.
* Follow the [upgrade runbook](/self-host/upgrade-runbook) for the sequence itself, and read its [rollback section](/self-host/upgrade-runbook#rolling-back) before you need it.
* After upgrading, [update the Lightdash CLI](/workflow/cli/install#updating-the-lightdash-cli) to match.
## Appendix: checking a span without the CLI
In environments where you can't run the CLI (no Node.js, or a fully air-gapped pipeline that mirrors the index), you can evaluate a span directly from the index with `curl` and `jq`. Check every release between your current version (exclusive) and your target (inclusive):
```bash theme={null}
FROM=1.111.0 # the version you are running
TO=1.115.0 # the version you want
curl -fsSL https://raw.githubusercontent.com/lightdash/lightdash/main/release-safety-index.json |
jq --arg from "$FROM" --arg to "$TO" '
.entries
| (map(.version == $from) | index(true)) as $i
| (map(.version == $to) | index(true)) as $j
| if $i == null or $j == null then
error("version not found in index — treat the span as unsafe")
else
.[$i+1:$j+1]
| {
releasesInSpan: length,
rollingUpdateSafe: (map(.rollingUpdateSafe == true) | all),
requiredStops: (map(.requiredStops[]) | unique),
minPreviousVersionForTarget: (last.minPreviousVersion)
}
end'
```
Against the live index, that span answers:
```json theme={null}
{
"releasesInSpan": 5,
"rollingUpdateSafe": false,
"requiredStops": [],
"minPreviousVersionForTarget": "1.111.0"
}
```
Read it in order:
1. `requiredStops` is empty — no mandatory intermediate version, so a direct jump is allowed…
2. …provided your current version is at or above `minPreviousVersionForTarget`. Here `1.111.0` ≥ `1.111.0`, so the jump is permitted.
3. `rollingUpdateSafe` is `false` — the span crosses at least one release that is not proven rolling-safe, so deploy with `Recreate`.
If your current version isn't in the index at all, the command errors on purpose. That's the fail-safe default: a version the index can't see is a span it can't vouch for — the same rule `upgrade-check` applies.
# Upgrading Lightdash
Source: https://docs.lightdash.com/self-host/upgrading
How Lightdash versioning works and when to upgrade a self-hosted deployment
🛠 This page is for engineering teams self-hosting their own Lightdash instance. If you're on Lightdash Cloud, upgrades are handled for you automatically.
Lightdash uses semantic versioning in the form `major.minor.patch`. Each segment signals how significant the changes are and how much care to take when upgrading.
## How Lightdash versioning works
* **Patch versions** (`x.x.PATCH`) are small fixes. Safe to pick up as part of routine upgrades.
* **Minor versions** (`x.MINOR.x`) introduce new features or larger changes that can be backwards incompatible. Review the release notes before upgrading.
* **Major versions** (`MAJOR.x.x`) introduce breaking changes to the API or the self-hosted architecture. Expect to make configuration, infrastructure, or integration changes, and read the release notes before upgrading.
Lightdash ships continuously and does **not** publish an LTS or "stable" tag. Pin `image.tag` to a specific [version](https://hub.docker.com/r/lightdash/lightdash/tags) in production and upgrade deliberately — pinning makes deployments repeatable and avoids unexpected changes. Using `latest` is fine for a local laptop instance.
## Upgrade cadence
Upgrade at least **monthly**. Falling many minor versions behind makes the eventual migration jump riskier. Treat every upgrade as a controlled rollout: upgrade UAT / pre-production first, run your validation (SSO, warehouse connections, dbt refreshes, scheduled jobs, embeds, critical dashboards), then roll out to production.
Your UAT environment should mirror production closely enough that upgrade testing is meaningful:
* **The same configuration and environment variables as production** — feature flags, SSO, SMTP, object storage, license key, scheduler and headless browser config. Differences here are the most common reason an upgrade passes pre-production but fails in production.
* **A replica of the production Lightdash database** (including `pgvector`), not an empty or hand-seeded one. Real data volume, migrations, and project/role/embed state are what surface upgrade regressions. Refresh the replica from production before each round of testing.
## Monitor security advisories
[GitHub Security Advisories](https://github.com/lightdash/lightdash/security/advisories) are the canonical source for published Lightdash vulnerabilities. Docker Hub and Docker Scout can provide supplemental findings, but pulling an image does not subscribe your team to security notifications and existing containers do not update automatically.
Opt in to automated monitoring by polling GitHub's public repository-advisories API every six hours:
```text theme={null}
GET https://api.github.com/repos/lightdash/lightdash/security-advisories?state=published&sort=updated&direction=desc&per_page=100
Accept: application/vnd.github+json
X-GitHub-Api-Version: 2026-03-10
```
The endpoint does not require authentication. Follow the `Link` response header when it is present and use `ETag` with `If-None-Match` to avoid downloading unchanged results. Persist each advisory's `ghsa_id`, `updated_at`, and a hash of its normalized content, then alert when an advisory is new or either value changes. The content hash is necessary because GitHub can update affected-product metadata without advancing `updated_at`. A non-null `withdrawn_at` means the advisory was withdrawn.
Compare your deployed version against each applicable `vulnerabilities[].vulnerable_version_range` with a SemVer library. Treat an advisory without `patched_versions` as affected until the advisory says otherwise. The advisory's `html_url` is the canonical remediation guide.
Read the running version from the unauthenticated health endpoint, or from the version your deployment manifest pins:
```bash theme={null}
curl --fail --silent https://lightdash.example.com/api/v1/health |
jq --raw-output '.results.version'
```
See the [Lightdash security policy](https://github.com/lightdash/lightdash/security/policy) for a cache-aware polling example and the complete operator contract.
## Running an upgrade
Start with the [release-safety signal](/self-host/upgrade-safety) for your upgrade span: it tells you whether a rolling update is advised and whether there are required stops on the way. Then follow the [upgrade runbook](/self-host/upgrade-runbook) for the sequence itself — Kubernetes, docker compose, or automation — plus the `migrate` command reference, the recovery paths, and [rollback](/self-host/upgrade-runbook#rolling-back).
If you're running Lightdash on your own laptop with Docker, none of that applies: pull the latest image and restart.
```bash theme={null}
docker pull lightdash/lightdash
```
For a security update, pull the advisory's fixed version explicitly and verify the container was replaced.
If the upgrade goes wrong, [recovery](/self-host/upgrade-runbook#recovery) covers reading a failed migration Job and running the `migrate` commands inside a pod to see what state the schema is actually in.
## Update the Lightdash CLI
Your CLI version should match the version your instance runs, so after upgrading a deployment, everyone using the CLI should [update it to match](/workflow/cli/install#updating-the-lightdash-cli).
# Lightdash semantic layer
Source: https://docs.lightdash.com/semantic-layer
Define your metrics, dimensions, and tables once in YAML and use them consistently across Lightdash
The Lightdash semantic layer sits between your data warehouse and the people who query it. You define your business concepts once — in YAML — and everyone gets the same metric definitions, dimension logic, and table relationships everywhere they work. It's the difference between seeing a field called `cust_id` in a table called `tbl_ord_dtl` and seeing **Customer** on an **Order**.
## Core components
* [**Metrics**](/semantic-layer/metrics) — the numbers your business cares about, like revenue, customer count, or order volume, each defined once with clear business logic.
* [**Dimensions**](/semantic-layer/dimensions) — the attributes you slice metrics by, such as time periods, regions, or product categories.
* [**Tables**](/semantic-layer/tables) — the business objects (customers, orders, products) that hold your dimensions and support your metrics.
## How it's defined
You define the semantic layer in version-controlled YAML, either inside a [dbt project](/integrations/dbt) or with [Lightdash YAML](/semantic-layer/yaml) if you don't use dbt. Lightdash connects to your warehouse, reads these definitions, and generates optimized SQL at query time — handling joins, aggregations, and filters for you. Adding business-friendly labels, descriptions, and formatting turns technical fields into concepts the whole organization can use.
## Ways to use it
* **In the app** — explore metrics in the [Metrics Catalog](/explore/metrics-catalog) or [query directly from tables](/explore/explore-view).
* **With AI** — [Lightdash AI agents](/agents) answer natural-language questions using the same definitions and guardrails as your business users, and can propose writeback changes to your semantic layer.
* **Programmatically** — the [API](/api-reference/v1/introduction) and [Python SDK](https://github.com/lightdash/python-sdk) give you the semantic layer from code, for automation, scheduled reporting, or embedding.
# Caching
Source: https://docs.lightdash.com/semantic-layer/caching
Cut the number of queries Lightdash runs against your warehouse with results and warehouse caching
**Availability:** Caching features are only available to:
* **Lightdash Cloud customers** (all plans)
* **On-premise customers with a valid License key**
Lightdash supports two types of caching:
1. **[Cached Filter values](#filter-value-caching)** - enabled for every cloud user without requiring any configuration.
2. **[Cached results for Charts and Dashboards](#chart-and-dashboard-results-caching)** - only available for Cloud Pro or above and must be enabled by the Lightdash team.
Results caching is **not enabled by default**, even on paid plans. To check if caching is active on your instance, look for a cache timestamp in the dashboard header. If no time is displayed, caching is not enabled. Contact the Lightdash team to enable it.
## Filter value caching
This type of caching works without any configuration for cloud users. You'll see a message in your filter values that tells you when the cached filter values were loaded from (usually within the last day). If you want to refresh the filter values, you can click on that message and the values will be refreshed.
If you search filter values by starting to type, Lightdash will also cache those values so the next time you need the same search the values will be shown much faster.
## Chart and dashboard results caching
Popular charts and dashboards will load faster when caching is enabled. The first user to visit a chart or dashboard each day will load fresh results from the warehouse, which get cached. After that, all following visits to the same chart or dashboard will load from the cached results.
Any changes to the chart query or dashboard queries (user attributes, filters, limit, date zoom) will trigger new queries to the warehouse and create a separate cached results entry.
Caching popular charts and dashboards will reduce warehouse costs to the organization by reducing the number of queries; it also improves server performance and makes the user experience much faster in Lightdash.
### Scope of the cache
Caching is turned on per instance, but each project can set its own **cache duration** under **Project settings → Results caching** — see [Cache duration per project](#cache-duration-per-project). There is no way to enable or disable caching for an individual dashboard.
These Lightdash features use caching (if it's enabled on your instance):
* **Saved Charts** are cached based on the last refresh in any context (edit mode, view mode, dashboard refresh, etc.), but queries made while editing are NOT cached or pulled from cache.
* **Dashboard tiles** (internal and embedded) will use the cache from saved charts they reference. If charts only exist on a single dashboard, they will refresh whenever you click the Refresh button on the dashboard.
* **Scheduled Deliveries** generate and use cached results for the saved chart or dashboard they belong to.
* **Google Sheets syncs** also use cached results. If you need syncs to always return fresh data, be aware that enabling caching will cause syncs to deliver cached results until the cache expires.
* **SQL runner queries** including saved SQL charts and dashboard SQL chart tiles are cached through the same execution path as metric-based charts. Note that the SQL runner does not have a UI button to force-refresh or invalidate the cache — results remain cached until the cache expires.
* **Metrics Catalog** queries go through the same async query service and are cached like any other query.
* **[Data apps](/data-apps)** run every metric query through the same async query service and are cached like any other query, including live-linked charts. The **Refresh** button in the app preview invalidates the cache for that app and re-queries the warehouse fresh for the rest of the session, the same way the dashboard refresh button does. Because the cache key is project ID plus generated SQL, apps with lots of interactive filter controls produce more distinct cache entries than an equivalent dashboard — the first hit on each filter combination still goes to the warehouse.
These Lightdash features DO NOT use caching:
* **Editing a saved chart** — actions in edit mode (such as changing columns, filters, or other query parameters) explicitly bypass the cache to ensure you always see fresh results while building a query.
* **[External connections](/data-apps/external-connections) in data apps** — requests to third-party HTTP APIs are runtime fetches through the Lightdash proxy and are not covered by results caching.
### Cache Mechanism
The cache is stored in S3 and the cache identifier is based on the project ID and the generated SQL. This means that any change to the selected columns, filters, joins, user attributes, etc. will trigger a new query to the warehouse and add a new cache entry for that query.
### User-level caching
When a project is configured to [require user credentials](/personal-settings/personal-warehouse-connections), cached results are scoped to each individual user. This means:
* Each user's queries are cached separately based on their personal warehouse credentials
* Users cannot access cached results from other users' queries
* This ensures data access controls are maintained at the individual user level
### Filtering with time values and caching
When using filters with datetime values, the specificity of the time component affects caching behavior:
* **Dynamic datetime values with seconds** (e.g., `12:11:25`) will generally not benefit from caching because each query generates a unique timestamp, creating a new cache entry every time.
* **Definite times** (e.g., `12:00:00`) or **dates without time components** will cache effectively because they produce consistent SQL queries that can be reused.
**Best practices for cacheable time filters:**
* Use date-only filters when possible (e.g., `2024-01-15` instead of `2024-01-15 12:11:25`)
* Round times to the nearest hour or fixed interval (e.g., `12:00:00` instead of `12:11:25`)
* Avoid filters that use dynamic "current time" functions with second precision
This ensures your queries can leverage cached results and reduce warehouse load.
### Cache expiry and invalidation
Cached results automatically expire after 24 hours by default. The instance default is set by `CACHE_STALE_TIME_SECONDS` and applies to every project that hasn't set its own duration.
Cache expiry is **rolling, not scheduled**. It's based on the age of each cached result, not a fixed time of day. For example, if a dashboard is first loaded at 2:00 PM, its cache expires at 2:00 PM the next day. This means different dashboards and charts may have different cache ages depending on when they were last refreshed.
#### Cache duration per project
Project admins set the cache duration for a single project under **Project settings → Results caching**. Turn off **Use the default cache duration** and enter a whole number of minutes between `1` and `43200` (30 days). Leaving the toggle on falls back to the instance default.
If your dashboards report on "yesterday's data" and you want users to always see fresh results in the morning, set a shorter cache duration (for example, 8 hours) so that results cached during the workday expire overnight.
The dashboard header displays the date and time of the chart with the oldest cache. If no time is displayed, then no charts are cached. You can invalidate and refresh cached dashboard results by pressing the dashboard refresh button.
There is currently no way to invalidate cached results for individual Saved Charts.
## Results caching vs pre-aggregates
Lightdash has two independent systems for speeding up queries: **results caching** (documented above) and **pre-aggregates**. They work differently and are designed to be used together, not as replacements for each other.
### Results caching
Results caching stores the exact result of any query that runs through Lightdash, keyed by a hash of the generated SQL, and serves subsequent identical queries from S3 until the entry expires (24 hours by default, [configurable per project](#cache-duration-per-project)). It covers every query shape — including custom metrics, table calculations, and SQL runner queries — but the first run of each unique query still hits your warehouse, and any change to the query (a different filter, column, limit, or user attribute) produces a new entry and another warehouse query.
### Pre-aggregates
[Pre-aggregates](/semantic-layer/pre-aggregates) are summary tables you define in your dbt YAML. Lightdash materializes them on a schedule (or on compile, or manually) and stores the results in S3. When a user query matches the pre-aggregate's dimensions, metrics, filters, and granularity, Lightdash serves the query from the materialized data using in-memory DuckDB workers. The warehouse is not touched at query time, even on the first query.
A single pre-aggregate can serve many different queries. A daily pre-aggregate with five dimensions can answer day, week, month, quarter, and year queries across any subset of those dimensions and with any narrower filter. Results caching, in contrast, needs one cache entry per unique SQL.
### Key differences
| | Results caching | Pre-aggregates |
| --------------------------------- | ----------------------------------------------- | -------------------------------------------------------------------- |
| **Configuration** | Automatic once enabled for your instance | Defined in dbt YAML |
| **Trigger** | First query runs against warehouse, then cached | Materialized on compile, cron, or manual refresh |
| **Storage** | Query result (row set) | Pre-computed summary table |
| **Query execution** | Exact cached result is returned | DuckDB workers re-aggregate at query time |
| **Warehouse hit on first query?** | Yes | No — only materialization hits the warehouse, not query-time serving |
| **Coverage** | All metric types, all query shapes | Only re-aggregatable metrics (sum, count, min, max, average) |
| **Scope** | One cache entry per unique SQL | One pre-aggregate can serve many query shapes |
| **Availability** | Cloud Pro+ or self-hosted with license | Enterprise (Beta) |
### When to use which
**Use pre-aggregates when:**
* You have high-traffic dashboards with predictable query patterns
* You want to reduce warehouse cost or improve latency on the first query, not just repeat visits
* The metrics are re-aggregatable (sum, count, min, max, average)
* You're willing to design and schedule the materializations
**Use results caching when:**
* Query patterns are ad-hoc or unpredictable
* You need features that pre-aggregates don't support, such as `count_distinct` outside an [exact match](/semantic-layer/pre-aggregates#exact-match-queries), [Parameters](/semantic-layer/parameters), or raw SQL table calculations
* You're using the SQL runner
* You don't want upfront configuration work
In most cases, both should be enabled. Pre-aggregates handle your heaviest, most predictable workloads. Results caching is the safety net for everything else.
### Using both together
When both systems are enabled, they act as two layers of caching. A query that matches a pre-aggregate is served from the materialized data by DuckDB workers. The result of that DuckDB query can then be stored in the results cache, so subsequent identical requests skip even the DuckDB step and return the cached result directly. This means pre-aggregates eliminate the warehouse hit, and results caching eliminates repeated computation on top of that.
# Dimensions reference
Source: https://docs.lightdash.com/semantic-layer/dimensions
The columns of your tables, and every option for defining and configuring them in YAML
Dimensions usually match 1:1 with columns in your dbt models (see [additional dimensions](#additional-dimensions) for counterexamples).
## Adding dimensions to your project
For a dimension to appear in Lightdash, you just need to declare it in your dbt model's YAML file.
```yaml theme={null}
models:
- name: my_model
columns:
- name: user_id # will be "User id" in LightDash
description: "Unique identifier for a user."
```
Write these entries by hand, or generate one for every column in a model with [`lightdash generate`](/workflow/cli/generate).
Dimensions cannot use window functions in their `sql`. A dimension's SQL is inserted into the same `SELECT` as the main `GROUP BY` in the generated query, and SQL does not allow window functions in that position. If you need window-function logic, either model it directly in your dbt model (so the value is precomputed before Lightdash queries it), or add a [table calculation](/explore/table-calculations) on the query results. Lightdash also supports a small set of built-in window operations through [post calculation metrics](/semantic-layer/metrics#post-calculation-metrics) (`percent_of_previous`, `percent_of_total`, `running_total`).
## Dimension configuration
To customize the dimension, you can do it in your dbt model's YAML file under the `meta` tag. The syntax depends on your dbt version.
If you want to declare multiple dimensions based on the same column, check [additional dimensions](#additional-dimensions) section.
```yaml dbt v1.10+ theme={null}
models:
- name: sales_stats
config:
meta:
group_details:
finance:
label: Finance
description: Finance-related fields.
joins:
- join: web_sessions
sql_on: ${web_sessions.date} = ${sales_stats.date}
columns:
- name: revenue_gbp_total_est
description: 'Total estimated revenue in GBP based on forecasting done by the finance team.'
config:
meta:
dimension:
type: number
label: 'Total revenue' # this is the label you'll see in Lightdash
description: 'My custom description' # you can override the description you'll see in Lightdash here
sql: 'IF(${TABLE}.revenue_gbp_total_est = NULL, 0, ${registered_user_email})' # custom SQL applied to the column from dbt used to define the dimension
hidden: false
format: '[$£]#,##0.00' # GBP rounded to two decimal points
groups: ['finance']
- name: forecast_date
description: 'Date of the forecasting.'
config:
meta:
dimension:
type: date
time_intervals: ['DAY', 'WEEK', 'MONTH', 'QUARTER'] # not required: the default time intervals for dates are `['DAY', 'WEEK', 'MONTH', 'YEAR']`
urls:
- label: 'Open in forecasting tool'
url: 'https://finance.com/forceasts/weeks/${ value.raw }'
- label: Open in Google Calendar
url: 'https://calendar.google.com/calendar/u/0/r/day/${ value.formatted |split: "-" |join: "/"}'
required_attributes:
is_admin: 'true'
```
```yaml dbt v1.9 and earlier theme={null}
models:
- name: sales_stats
meta:
group_details:
finance:
label: Finance
description: Finance-related fields.
joins:
- join: web_sessions
sql_on: ${web_sessions.date} = ${sales_stats.date}
columns:
- name: revenue_gbp_total_est
description: 'Total estimated revenue in GBP based on forecasting done by the finance team.'
meta:
dimension:
type: number
label: 'Total revenue' # this is the label you'll see in Lightdash
description: 'My custom description' # you can override the description you'll see in Lightdash here
sql: 'IF(${TABLE}.revenue_gbp_total_est = NULL, 0, ${registered_user_email})' # custom SQL applied to the column from dbt used to define the dimension
hidden: false
format: '[$£]#,##0.00' # GBP rounded to two decimal points
groups: ['finance']
- name: forecast_date
description: 'Date of the forecasting.'
meta:
dimension:
type: date
time_intervals: ['DAY', 'WEEK', 'MONTH', 'QUARTER'] # not required: the default time intervals for dates are `['DAY', 'WEEK', 'MONTH', 'YEAR']`
urls:
- label: 'Open in forecasting tool'
url: 'https://finance.com/forceasts/weeks/${ value.raw }'
- label: Open in Google Calendar
url: 'https://calendar.google.com/calendar/u/0/r/day/${ value.formatted |split: "-" |join: "/"}'
required_attributes:
is_admin: 'true'
```
```yaml Lightdash YAML theme={null}
type: model
name: sales_stats
group_details:
finance:
label: Finance
description: Finance-related fields.
joins:
- join: web_sessions
sql_on: ${web_sessions.date} = ${sales_stats.date}
dimensions:
- name: revenue_gbp_total_est
description: 'Total estimated revenue in GBP based on forecasting done by the finance team.'
type: number
label: 'Total revenue' # this is the label you'll see in Lightdash
sql: 'IF(${TABLE}.revenue_gbp_total_est = NULL, 0, ${registered_user_email})' # custom SQL applied to the column from dbt used to define the dimension
hidden: false
format: '[$£]#,##0.00' # GBP rounded to two decimal points
groups: ['finance']
- name: forecast_date
description: 'Date of the forecasting.'
type: date
time_intervals: ['DAY', 'WEEK', 'MONTH', 'QUARTER'] # not required: the default time intervals for dates are `['DAY', 'WEEK', 'MONTH', 'YEAR']`
urls:
- label: 'Open in forecasting tool'
url: 'https://finance.com/forceasts/weeks/${ value.raw }'
- label: Open in Google Calendar
url: 'https://calendar.google.com/calendar/u/0/r/day/${ value.formatted |split: "-" |join: "/"}'
required_attributes:
is_admin: 'true'
```
The table below shows all the dimension properties you can customize:
| Property | Required | Value | Description |
| :------------------------------------------- | :------- | :------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | :------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| label | No | string | Custom label. If you set this property, this is what you'll see in Lightdash instead of the dimension name. |
| [type](#type) | No | Dimension type | The dimension type is automatically pulled from your table schemas in Lightdash but you can override the type using this property. |
| [description](#description) | No | string | Description of the dimension in Lightdash. You can use this to override the description you have for the dimension in dbt. |
| sql | No | string | Custom SQL applied to the column used to define the dimension. |
| [time\_intervals](#time-intervals) | No | 'default' or OFF or an array\[] containing elements of [date](#date-options), [numeric](#numeric-options), [string](#string-options) options, or [custom granularity](#using-custom-granularities) names | 'default' (or not setting the time\_intervals property) will be converted into \['DAY', 'WEEK', 'MONTH', 'QUARTER', 'YEAR'] for dates and \['RAW', 'DAY', 'WEEK', 'MONTH', 'QUARTER', 'YEAR'] for timestamps; if you want no time intervals set 'OFF'. You can also include custom granularity names defined in `lightdash.config.yml`. |
| hidden | No | boolean | If set to true, the dimension is hidden from Lightdash. By default, this is set to false if you don't include this property. Hidden dimensions are also excluded from drilldowns (View underlying data). |
| [compact](#compact) | No | string | This option will compact the number value (e.g. 1,500 to 1.50K). Currently supports one of the following: \['auto', 'thousands', 'millions', 'billions', 'trillions', 'kilobytes', 'megabytes', 'gigabytes', 'terabytes', 'petabytes', 'kibibytes', 'mebibytes', 'gibibytes', 'tebibytes', 'pebibytes']. Use `auto` to dynamically pick K/M/B/T based on each value's magnitude. |
| [format](#format) | No | string | This option will format the output value on the results table and CSV export. Supports spreadsheet-style formatting (e.g. #,##0.00). Use [this website](https://customformats.com) to help build your custom format. |
| [separator](#separator) | No | string | This option controls the grouping and decimal characters used when rendering numbers (e.g. `1.234.567,50` for European locales). One of: `default`, `commaPeriod`, `spacePeriod`, `periodComma`, `noSeparatorPeriod`, `apostrophePeriod`. |
| [groups](#groups) | No | string or string\[] | If you set this property, the dimension will be grouped in the sidebar with other dimensions with the same group label. |
| [urls](#urls) | No | Array of `url`, `label` | Adding urls to a dimension allows your users to click dimension values in the UI and take actions, like opening an external tool with a url, or open at a website. You can use liquid templates to customise the link based on the value of the dimension. |
| [richText](#rich-text) | No | string | Rich text template for displaying formatted content in table cells. Supports Markdown, HTML, and LiquidJS templating. |
| [required\_attributes](#required-attributes) | No | Object with `user_attribute`, `value` | Limits access to users with those attributes (AND logic - all must match) |
| [any\_attributes](#any-attributes) | No | Object with `user_attribute`, `value` | Limits access to users with those attributes (OR logic - at least one must match) |
| [colors](#color) | No | Object with `value`, `color` | Color for the values in the chart |
| [image](#image-display) | No | Object with `url` | **\[WIP]** Display images in table cells using URL templates. Supports LiquidJS templating for dynamic URLs. |
| [case\_sensitive](#case-sensitive) | No | boolean | If set to `false`, string filters on this dimension will be case insensitive. Defaults to `true`. Overrides explore-level setting. |
| [filter\_autocomplete](#filter-autocomplete) | No | Object | Configure the filter autocomplete suggestions for this dimension. Provide a static list of `values` (with optional display `label`s), point at a `label_dimension` in the same table to label warehouse-fetched values, source values from a dimension in another model with `options_from_dimension`, and/or disable warehouse-based autocomplete by setting `fetch_from_warehouse: false`. |
| [tags](#tags) | No | string\[] | An array of string tags for categorizing and filtering dimensions programmatically. Tags can be used by AI agents, API filters, and other backend workflows. |
| [convert\_timezone](#convert-timezone) | No | boolean | If set to `false`, the dimension opts out of the project query timezone for display, grouping, and extracts - the raw warehouse value is rendered instead. Defaults to `true`. Has no effect unless a project query timezone is set. |
| [timestamp\_domain](#timestamp-domain) | No | `aware` or `naive` | Declares whether a `timestamp` column stores an instant (`aware`) or a bare wall clock with no zone (`naive`). Overrides what Lightdash detects from the warehouse catalog. |
## Type
The types of your dimensions are pulled from your data warehouse, automatically. You can override these types using the `type` meta tag in your .yml file. If you run `lightdash generate` to generate your .yml files, then Lightdash will add the `type` from your data warehouse to your .yml files automatically.
```yaml dbt v1.10+ theme={null}
- name: user_created_date
config:
meta:
dimension:
type: date
```
```yaml dbt v1.9 and earlier theme={null}
- name: user_created_date
meta:
dimension:
type: date
```
```yaml Lightdash YAML theme={null}
dimensions:
- name: user_created_date
type: date
```
We currently support these dimension types:
| Dimension Types |
| :-------------- |
| string |
| number |
| timestamp |
| date |
| boolean |
## Description
Column descriptions in your YAML file are automatically pulled into Lightdash and you can spot them if you hover over the dimension name.
Descriptions support any formatting that works with YAML, but the three characters used most often are:
### Quotes for escaping
When you surround text with double or single quotes it will escape the text between so that any special characters recognized by YAML will still pass through to the Lightdash UI.
```yaml theme={null}
description: 'The contents of this column include this & that.'
```
### Greater than symbol for folded text blocks
When you use `>-` it allows you to type descriptions that are multiple lines long in the YAML file, but the text will be combined into a single line when parsed. The `lightdash generate` command will automatically add this to keep YAML files easy to read.
This description in YAML:
```yaml theme={null}
- name: product_tier
description: >-
This is a longer description...
...that requires multiple lines
and it will be combined in the Lightdash UI
```
Will appear like this in the Lightdash UI:
```yaml theme={null}
This is a longer description......that requires multiple lines and it will be combined in the Lightdash UI
```
### Vertical bar for preserving line breaks
If you need line breaks to stay in place when they show up in the Lightdash UI, you can use a `|` character like this:
```yaml theme={null}
- name: product_tier
description: |
This is a longer description...
...that requires multiple lines
and it will stay on multiple lines
```
And in Lightdash UI it will appear like this:
```yaml theme={null}
This is a longer description...
...that requires multiple lines
and it will stay on multiple lines
```
### Using dbt doc blocks
You can also use dbt docs blocks in descriptions, [more on that here](/workflow/cli/generate#using-doc-blocks-to-build-better-yml-files).
## Format
You can use the `format` parameter to have your fields show in a particular format in Lightdash. Lightdash supports spreadsheet-style format expressions for all field types.
To help you build your format expression, we recommend using [https://customformats.com/](https://customformats.com/).
```yaml theme={null}
models:
- name: sales
columns:
- name: revenue
meta:
metrics:
total_us_revenue:
type: sum
description: 'Total revenue in USD, with two decimal places, compacted to thousands'
format: '$#,##0.00," K"' # 505,430 will appear as '$505.43 K'
percent_of_total_global_revenue:
type: number
description: 'Percent of total global revenue coming from US revenue.'
sql: ${total_us_revenue} / ${total_global_revenue}
format: '0.00%' # 0.67895243 will appear as '67.89%'
```
### Example format expressions
| Description | Format Expression | Raw Value | Formatted Output |
| :--------------------------------------------- | :-------------------- | :------------------- | :-------------------- |
| **Adds "km" suffix to the value** | `#,##0.00" km"` | 100000.00 | 100,000.00 km |
| | | 15000.25 | 15,000.25 km |
| | | 500 | 500.00 km |
| **Format date with 12-hour clock** | `m/d/yyyy h:mm AM/PM` | 2023-09-05T15:45:00Z | 9/5/2023 3:45 PM |
| | | 2024-01-20T08:30:00Z | 1/20/2024 8:30 AM |
| **Display the full name of the day** | `dddd` | 2023-09-05T15:45:00Z | Tuesday |
| | | 2024-01-20T08:30:00Z | Saturday |
| **Format positive, negative, and zero values** | `"⬆️ "0;"⬇️ "0;0` | -500 | ⬇️ 500 |
| | | 200 | ⬆️ 200 |
| | | 0 | 0 |
| **Text formatting** | `"Delivered in "@` | 2 weeks | Delivered in 2 weeks |
| | | 18 hours | Delivered in 18 hours |
| **Percentage formatting** | `#,##0.00%` | 0.6758 | 67.58% |
| | | 0.1 | 10.00% |
| | | 0.002 | 0.20% |
| **No formatting** | `0` | 12345232 | 12345232 |
| | | 56.7856 | 57 |
| **Currency formatting (USD)** | `[$$]#,##0.00` | 15430.75436 | \$15,430.75 |
| | | 1234.50 | \$1,234.50 |
| **Currency formatting (GBP)** | `[$£]#,##0.00` | 15430.75436 | £15,430.75 |
| | | 1234.50 | £1,234.50 |
| **Compact currency in thousands** | `[$$]#,##0,"K"` | 15430.75436 | \$15K |
| | | 15430.75436 | \$15.43K |
| **Compact currency in millions** | `[$$]#,##0.00,,"M"` | 13334567 | \$13.33M |
| | | 120000000 | \$120.00M |
Spreadsheet-style format expressions are the recommended way of adding formatting to your fields in Lightdash. There are legacy formatting options, listed below, which are less flexible than the spreadsheet-style formatting.
If you use both legacy and spreadsheet-style formatting options for a single field, Lightdash will ignore the legacy `format` and `round` options and only apply the spreadsheet-style formatting expression.
#### Format (legacy)
```yaml theme={null}
models:
- name: sales_stats
columns:
- name: revenue
description: 'Total estimated revenue in GBP based on forecasting done by the finance team.'
meta:
metrics:
total_revenue:
label: 'Total revenue GBP'
type: SUM
format: 'gbp'
```
These are the options:
| Option | Equivalent format expression | Raw value | Displayed value |
| :------ | :--------------------------- | :-------- | :-------------- |
| km | `'#,##0.00" km"'` | 10 | 10 km |
| mi | `'#,##0.00" mi"'` | 10 | 10 mi |
| usd | `'[\$\$]#,##0.00'` | 10 | \$10.00 |
| gbp | `'[\$£]#,##0.00'` | 10 | £10.00 |
| eur | `'[\$€]#,##0.00'` | 10 | €10.00 |
| jpy | `'[\$¥]#,##0.00'` | 10 | ¥10 |
| percent | `'#,##0.00%'` | 0.1 | 10.00% |
| id | `'0'` | 12389572 | 12389572 |
`percent` multiplies the raw value by 100, and `id` strips commas and spaces from number and string values so they read as identifiers.
#### Round (legacy)
You can round values to appear with a certain number of decimal points.
```yaml theme={null}
models:
- name: sales
columns:
- name: revenue
meta:
metrics:
total_revenue:
type: sum
round: 0 # equivalent format expression: '#,##0.0'
```
## Compact
You can compact values in your YAML. For example, if I wanted all of my revenue values to be shown in thousands (e.g. `1,500` appears as `1.50K`), then I would write something like this in my .yml:
```yaml theme={null}
models:
- name: sales
columns:
- name: revenue
meta:
dimension:
compact: thousands # You can also use 'K'
```
| Value | Alias | Equivalent format expression | Example output |
| :-------- | :------------------- | :---------------------------------------- | :------------- |
| auto | | dynamic — picks K/M/B/T per value | 1.50K / 2.30M |
| thousands | "K" and "thousand" | `'#,##0," K"'` or `'#,##0.00," K"'` | 1K |
| millions | "M" and "million" | `'#,##0,," M"'` or `'#,##0.00,," M"'` | 1M |
| billions | "B" and "billion" | `'#,##0,,," B"'` or `'#,##0.00,,," B"'` | 1B |
| trillions | "T" and "trillion" | `'#,##0,,,," T"'` or `'#,##0.00,,,," T"'` | 1T |
| kilobytes | "KB" and "kilobyte" | | 1KB |
| megabytes | "MB" and "megabyte" | | 1MB |
| gigabytes | "GB" and "gigabyte" | | 1GB |
| terabytes | "TB" and "terabyte" | | 1TB |
| petabytes | "PB" and "petabyte" | | 1PB |
| kibibytes | "KiB" and "kibibyte" | | 1KiB |
| mebibytes | "MiB" and "mebibyte" | | 1MiB |
| gibibytes | "GiB" and "gibibyte" | | 1GiB |
| tebibytes | "TiB" and "tebibyte" | | 1TiB |
| pebibytes | "PiB" and "pebibyte" | | 1PiB |
## Separator
You can use the `separator` property to control the grouping and decimal characters used when rendering numbers, so values display correctly for non-US locales (e.g. `1.234.567,50` in Europe or `1'234'567.50` in Switzerland).
`separator` only changes the separator characters. It composes with `format` (including spreadsheet-style `format` expressions), `compact`, `round`, and `currency`, which still control the overall shape of the number.
```yaml theme={null}
models:
- name: sales
columns:
- name: revenue
meta:
metrics:
total_revenue:
type: sum
format: '[$€]#,##0.00'
separator: periodComma # 1.234.567,50 €
```
These are the supported values:
| Value | Example output | Description |
| :------------------ | :------------- | :------------------------------------------------------------------------------------------ |
| `default` | `1,234,567.50` | Uses the Lightdash default (comma thousands, period decimal). Same as omitting `separator`. |
| `commaPeriod` | `1,234,567.50` | Comma thousands separator, period decimal separator. |
| `spacePeriod` | `1 234 567.50` | Space thousands separator, period decimal separator. |
| `periodComma` | `1.234.567,50` | Period thousands separator, comma decimal separator. |
| `noSeparatorPeriod` | `1234567.50` | No thousands separator, period decimal separator. |
| `apostrophePeriod` | `1'234'567.50` | Apostrophe thousands separator, period decimal separator (Swiss). |
## Time intervals
Lightdash automatically adds intervals for dimensions that are timestamps or dates, so you don't have to!
For example, here we have the timestamp dimension `created` defined in our dbt project:
```yaml theme={null}
- name: created
description: 'Timestamp when the user was created.'
```
Lightdash breaks this out into the default intervals automatically. So, this is how `created` appears in our Lightdash project:
[**Formatting added to a date or timestamp dimension will be applied to all of the time intervals for that dimension.**](#format)
If you want to apply different formats for different time intervals, we recommend creating [additional dimensions](#additional-dimensions) for time intervals where you want to customize the format.
### Default time intervals
The default time intervals that Lightdash adds are...
**For `date` type**:
```yaml theme={null}
['DAY', 'WEEK', 'MONTH', 'QUARTER', 'YEAR']
```
**For `timestamp` type**:
```yaml theme={null}
['RAW', 'DAY', 'WEEK', 'MONTH', 'QUARTER', 'YEAR']
```
### Disable time intervals
If you want to turn off time intervals for a dimension, you set the `time_intervals` property to `OFF`.
In this example, `created` would now appear as a single, timestamp dimension without a drop-down list of time intervals in Lightdash:
```yaml dbt v1.10+ theme={null}
- name: created
description: 'Timestamp when the user was created.'
config:
meta:
dimension:
type: timestamp
time_intervals: OFF
```
```yaml dbt v1.9 and earlier theme={null}
- name: created
description: 'Timestamp when the user was created.'
meta:
dimension:
type: timestamp
time_intervals: OFF
```
```yaml Lightdash YAML theme={null}
dimensions:
- name: created
description: 'Timestamp when the user was created.'
type: timestamp
time_intervals: OFF
```
### To customize the time intervals for a dimension, you can use the `time_intervals` parameter.
If you specify time intervals manually, then this overrides the default time intervals used by Lightdash.
```yaml dbt v1.10+ theme={null}
- name: created
description: 'Timestamp when the user was created.'
config:
meta:
dimension:
time_intervals: ['DAY', 'DAY_OF_MONTH_NUM', 'MONTH', 'QUARTER_NAME', 'YEAR']
```
```yaml dbt v1.9 and earlier theme={null}
- name: created
description: 'Timestamp when the user was created.'
meta:
dimension:
time_intervals: ['DAY', 'DAY_OF_MONTH_NUM', 'MONTH', 'QUARTER_NAME', 'YEAR']
```
```yaml Lightdash YAML theme={null}
dimensions:
- name: created
description: 'Timestamp when the user was created.'
time_intervals: ['DAY', 'DAY_OF_MONTH_NUM', 'MONTH', 'QUARTER_NAME', 'YEAR']
```
You can see all of the standard interval options for date and timestamp fields below. You can also add custom intervals using *additional dimensions*, there's an example below the following tables.
### Date options
| Option | Description | Type | Displayed value | Notes |
| :---------- | :-------------------------------------------- | :-------------- | :---------------------------------------- | :------------------------------------------------------------ |
| RAW | Original value | Date / DateTime | 2019-01-01 / 2019-01-01, 09:30:30:300 UTC | |
| YEAR | Date truncated to the nearest year | Date | 2019 | |
| QUARTER | Date truncated to the nearest quarter | Date | 2019-Q1 | |
| MONTH | Date truncated to the nearest month | Date | 2019-01-01 | |
| WEEK | Date truncated to the nearest week | Date | 2019-01-01 | The start of the week depends on your warehouse configuration |
| DAY | Date truncated to the nearest day | Date | 2019-01-01 | |
| HOUR | Datetime truncated to the nearest hour | DateTime | 2019-01-01, 09 UTC | |
| MINUTE | Datetime truncated to the nearest minute | DateTime | 2019-01-01, 09:30 UTC | |
| SECOND | Datetime truncated to the nearest second | DateTime | 2019-01-01, 09:30:30 UTC | |
| MILLISECOND | Datetime truncated to the nearest millisecond | DateTime | 2019-01-01, 09:30:30:300 UTC | |
### Numeric options
| Option | Description | Type | Displayed value | Notes |
| :-------------------- | :--------------------------- | :----- | :-------------- | :---------------------------------------------------------------------------- |
| DAY\_OF\_WEEK\_INDEX | Index of the day of the week | Number | 0 | The value range and start of the week depends on your warehouse configuration |
| DAY\_OF\_MONTH\_NUM | Day of the month | Number | 21 | |
| DAY\_OF\_YEAR\_NUM | Day of the year | Number | 127 | |
| WEEK\_NUM | Week number | Number | 37 | |
| MONTH\_NUM | Month number | Number | 7 | |
| QUARTER\_NUM | Quarter number | Number | 3 | |
| YEAR\_NUM | Year number | Number | 2019 | |
| MINUTE\_OF\_HOUR\_NUM | Minute number | Number | 50 | |
| HOUR\_OF\_DAY\_NUM | Hour number | Number | 22 | |
### String options
| Option | Description | Type | Displayed value |
| :------------------ | :-------------- | :----- | :-------------- |
| DAY\_OF\_WEEK\_NAME | Day of the week | String | Monday |
| MONTH\_NAME | Month name | String | March |
| QUARTER\_NAME | Quarter name | String | Q3 |
### Using custom granularities
Beta Custom granularities are available on all plans. [What Beta means](/support/feature-maturity-levels).
You can define reusable custom time granularities in your `lightdash.config.yml` file and reference them in the `time_intervals` array. Unlike [custom time intervals using additional dimensions](#custom-time-intervals-with-additional-dimensions), custom granularities **appear in the date zoom dropdown** alongside standard options (Day, Week, Month, etc.).
**Step 1**: Define custom granularities in `lightdash.config.yml`:
```yaml theme={null}
# lightdash.config.yml
custom_granularities:
fiscal_quarter:
label: "Fiscal Quarter"
sql: "DATE_TRUNC('quarter', ${COLUMN} + INTERVAL '1 month')"
week_monday:
label: "Week (Mon-Sun)"
sql: "DATE_TRUNC('week', ${COLUMN})"
```
**Step 2**: Reference them in your dimension's `time_intervals`:
```yaml dbt v1.10+ theme={null}
- name: order_date
description: 'Date the order was placed'
config:
meta:
dimension:
type: date
time_intervals: ['DAY', 'WEEK', 'fiscal_quarter', 'YEAR']
```
```yaml dbt v1.9 and earlier theme={null}
- name: order_date
description: 'Date the order was placed'
meta:
dimension:
type: date
time_intervals: ['DAY', 'WEEK', 'fiscal_quarter', 'YEAR']
```
```yaml Lightdash YAML theme={null}
dimensions:
- name: order_date
description: 'Date the order was placed'
type: date
time_intervals: ['DAY', 'WEEK', 'fiscal_quarter', 'YEAR']
```
The `${COLUMN}` placeholder in the SQL expression is automatically replaced with the dimension's column SQL at runtime. Custom granularities also inherit `requiredAttributes` and `anyAttributes` from the parent dimension.
See [lightdash.config.yml reference](/semantic-layer/lightdash-config-yml#custom-granularities-configuration) for the full configuration options and the [Date zoom guide](/explore/dashboards/date-zoom) for information on configuring the date zoom dropdown.
### Custom time intervals with additional dimensions
You can also create custom time-based dimensions by using additional dimensions and groups. This approach groups custom dimensions with their parent date dimension in the sidebar, but **these dimensions do not appear in the date zoom dropdown** - they are only available as separate fields in the dimension list.
If you need custom time intervals to appear in the **date zoom dropdown**, use [custom granularities](#using-custom-granularities) defined in `lightdash.config.yml` instead.
Here's an example of how that might look if you wanted to add `year_of_week_iso` grouped with the `delivery_date` dimension. Note that by defining the `groups:` option, we ensure that the new "Year of week" option is displayed grouped with the parent dimension in the sidebar.
```yaml dbt v1.10+ theme={null}
- name: delivery_date
config:
meta:
dimension:
label: "Delivery Date"
type: date
time_intervals: [ ... ]
additional_dimensions:
year_of_week_num:
type: number
label: "Year of week"
sql: "yearofweekiso(${delivery_date})"
groups: ["Delivery Date"]
```
```yaml dbt v1.9 and earlier theme={null}
- name: delivery_date
meta:
dimension:
label: "Delivery Date"
type: date
time_intervals: [ ... ]
additional_dimensions:
year_of_week_num:
type: number
label: "Year of week"
sql: "yearofweekiso(${delivery_date})"
groups: ["Delivery Date"]
```
```yaml Lightdash YAML theme={null}
dimensions:
- name: delivery_date
label: "Delivery Date"
type: date
time_intervals: [ ... ]
- name: year_of_week_num
type: number
label: "Year of week"
sql: "yearofweekiso(${delivery_date})"
groups: ["Delivery Date"]
```
### Reference time intervals in other dimensions
You can reference specific time intervals of a dimension in other dimensions. When you define time intervals for a dimension (like `session_start`), Lightdash creates separate dimensions for each interval (e.g., `session_start_day`, `session_start_month`). You can reference these in custom SQL for other dimensions.
For example, if you have a `user_created_at` dimension with time intervals defined, you can calculate the duration between two dates using the `DAY` interval:
```yaml theme={null}
- name: user_created_at
meta:
dimension:
type: timestamp
time_intervals:
- DAY_OF_WEEK_NAME
- WEEK
- MONTH
- RAW
- DAY
- HOUR_OF_DAY_NUM
- QUARTER
- name: first_purchase_at
meta:
dimension:
type: timestamp
time_intervals:
- DAY
- MONTH
- QUARTER
- name: duration
meta:
dimension:
type: number
sql: EXTRACT(DAY FROM ${first_purchase_at_day} - ${user_created_at_day})
```
In this example, `${user_created_at_day}` and `${first_purchase_at_day}` reference the `DAY` time interval versions of the `user_created_at` and `first_purchase_at` dimensions.
## Groups
You can group your dimensions and metrics in the sidebar using the `groups` parameter.
To do this, you need to set up `group_details` in the model's configuration. Then, you can use these groups to organize metrics and dimensions. You can create nested groups up to 3 levels.
```yaml dbt v1.10+ theme={null}
models:
- name: baskets
config:
meta:
group_details:
product_details:
label: Product Details
description: 'Fields that have information about the products in the basket.'
item_details:
label: Item Details
description: 'Fields that have information about the items in the basket.'
columns:
- name: basket_item_id
description: 'ID for the product item within the basket.'
config:
meta:
dimension:
groups: ['product_details', 'item_details'] # this would add the dimension to a nested group: `product details` --> `item details`
- name: product_name
description: 'Full name of the product.'
config:
meta:
dimension:
label: 'Product name'
groups: ['product_details'] # this would add the dimension under the group label: `product_details`
```
```yaml dbt v1.9 and earlier theme={null}
models:
- name: baskets
meta:
group_details:
product_details:
label: Product Details
description: 'Fields that have information about the products in the basket.'
item_details:
label: Item Details
description: 'Fields that have information about the items in the basket.'
columns:
- name: basket_item_id
description: 'ID for the product item within the basket.'
meta:
dimension:
groups: ['product_details', 'item_details'] # this would add the dimension to a nested group: `product details` --> `item details`
- name: product_name
description: 'Full name of the product.'
meta:
dimension:
label: 'Product name'
groups: ['product_details'] # this would add the dimension under the group label: `product_details`
```
```yaml Lightdash YAML theme={null}
type: model
name: baskets
group_details:
product_details:
label: Product Details
description: 'Fields that have information about the products in the basket.'
item_details:
label: Item Details
description: 'Fields that have information about the items in the basket.'
dimensions:
- name: basket_item_id
description: 'ID for the product item within the basket.'
groups: ['product_details', 'item_details'] # this would add the dimension to a nested group: `product details` --> `item details`
- name: product_name
description: 'Full name of the product.'
label: 'Product name'
groups: ['product_details'] # this would add the dimension under the group label: `product_details`
```
This example would look like this in the sidebar:
## URLs
Lightdash users can interact with dimension values by clicking on them. If you're already storing URLs in your models, you can create hyperlinks to those URLs in Lightdash, like so:
```yaml dbt v1.10+ theme={null}
columns:
- name: candidate_profile_url
label: URL of the candidate profile
config:
meta:
dimension:
urls:
- label: Open in CRM
url: ${ value.raw }
```
```yaml dbt v1.9 and earlier theme={null}
columns:
- name: candidate_profile_url
label: URL of the candidate profile
meta:
dimension:
urls:
- label: Open in CRM
url: ${ value.raw }
```
```yaml Lightdash YAML theme={null}
dimensions:
- name: candidate_profile_url
label: URL of the candidate profile
urls:
- label: Open in CRM
url: ${ value.raw }
```
### How to add custom URLs
By adding custom urls you can configure the actions available to your users. Like linking to external tools, or taking actions in other tools.
In the example below, users can click on a company name and open a corresponding record in their CRM or search for the company in google or open that company's Slack channel.
```yaml dbt v1.10+ theme={null}
columns:
- name: company_name
label: Registered trading name of the company
config:
meta:
dimension:
urls:
- label: Search for company in Google
url: 'https://google.com/search?${ value.formatted | url_encode }'
- label: Open in CRM
url: 'https://mycrm.com/companies/${ row.company.company_id.raw | url_encode }'
```
```yaml dbt v1.9 and earlier theme={null}
columns:
- name: company_name
label: Registered trading name of the company
meta:
dimension:
urls:
- label: Search for company in Google
url: 'https://google.com/search?${ value.formatted | url_encode }'
- label: Open in CRM
url: 'https://mycrm.com/companies/${ row.company.company_id.raw | url_encode }'
```
```yaml Lightdash YAML theme={null}
dimensions:
- name: company_name
label: Registered trading name of the company
urls:
- label: Search for company in Google
url: 'https://google.com/search?${ value.formatted | url_encode }'
- label: Open in CRM
url: 'https://mycrm.com/companies/${ row.company.company_id.raw | url_encode }'
```
The `${ value.formatted }` will be replaced with the value of the company name in the Lightdash UI at query run time. The `${ row.company.company_id.raw }` will be replaced with the value of the company id in the Lightdash UI at query run time. The action will be disabled if the column "company\_id" from table "company" is not part of the query.
### You can reference values from other columns in your URLs
You can reference another dimension from your table in your URL. For these URLs to work, the other column you've referenced needs to be included in your results table. For example, say I've added a URL to `company_name` and it uses the field `customer_id`:
```yaml dbt v1.10+ theme={null}
columns:
- name: company_name
label: Registered trading name of the company
config:
meta:
dimension:
urls:
- label: "Open company"
url: "https://example.com/company/${row.customers.customer_id.raw | url_encode }"
```
```yaml dbt v1.9 and earlier theme={null}
columns:
- name: company_name
label: Registered trading name of the company
meta:
dimension:
urls:
- label: "Open company"
url: "https://example.com/company/${row.customers.customer_id.raw | url_encode }"
```
```yaml Lightdash YAML theme={null}
dimensions:
- name: company_name
label: Registered trading name of the company
urls:
- label: "Open company"
url: "https://example.com/company/${row.customers.customer_id.raw | url_encode }"
```
This URL will only work if I have `customer_id` included in my results table.
### Liquid templating
Use templates to configure values dynamically at runtime based on query results.
**Available liquid tags**
| Tag | Description |
| :------------------------------------------ | :------------------------------------------------------------------------------------------ |
| `${ value.formatted }` | The exact value of the dimension as seen in the Lightdash UI. For example `$1,427.20` |
| `${ value.raw }` | The raw value of the dimension returned from the underlying SQL query. For example `1427.2` |
| `${ row.table_name.column_name.formatted }` | The exact value of the column as seen in the Lightdash UI. For example `$1,427.20` |
| `${ row.table_name.column_name.raw }` | The raw value of the dimension returned from the underlying SQL query. For example `1427.2` |
**Available liquid filters**
Filters can be used to make small transformations of your values:
* `url_encode`: Encode a string as URL safe, for example it replaces spaces with `%20`.
```liquid theme={null}
${ value.formatted | url_encode }
```
* `downcase`: Convert a string to lowercase.
```liquid theme={null}
${ value.formatted | downcase }
```
* `append`: Append one string to another.
```liquid theme={null}
${ value.formatted | append: ".html" }
```
There are [many more filters available in the Liquid documentation](https://liquidjs.com/filters/overview.html).
## Rich text
The `richText` property allows you to define custom HTML/Markdown templates for displaying dimension and metric values in table cells. This enables sophisticated data presentation with formatting, styling, conditional logic, and external integrations.
Rich text only renders in the **Table chart visualization**. It does not affect the Results panel below the explore, CSV/Excel exports, or the underlying data values. To see your `richText` take effect, switch the chart type to **Table**.
`richText` is supported on both **dimensions** and **metrics**. It accepts Markdown, inline HTML, and [LiquidJS](https://liquidjs.com/) templating. HTML is sanitized with a GitHub-safe allowlist that permits inline `style` attributes but strips `