DEV Community

Mikoto Takigawa
Mikoto Takigawa

Posted on Originally published at zenn.dev

Running Lightdash on Snowpark Container Services

#cloud #database #docker #infrastructure

Introduction

I recently learned that there is a handy BI tool out there called Lightdash. It comes in both a SaaS edition and an OSS edition.

Getting the SaaS edition approved would have taken a while at my organization, so I decided to try the OSS edition instead.
Since I also wanted an excuse to learn Snowflake Postgres, I ended up trying to host Lightdash on SPCS (Snowpark Container Services).

A note before we start: this article describes what I did on April 9, 2026. Snowflake ships changes quickly, so please refer to the official documentation for the current details of the features covered here.

Architecture Overview

archi

Main Components

Component Role
Compute Pool The VM nodes that run SPCS containers. I used CPU_X64_S.
Lightdash Service Runs the web UI and the scheduler in a single container, exposed through a public endpoint.
Snowflake Postgres The metadata DB for Lightdash. PG 17.
External Access Integration (EAI) Controls egress from the container. Two of them: one for Postgres, one for Azure DevOps.
Image Repository Holds the Docker image inside Snowflake.
Azure DevOps Where the dbt project source code lives. Lightdash fetches it through the API.

Prerequisites

Item Requirement
Snowflake Edition Enterprise or above (required for SPCS)
Role ACCOUNTADMIN, or a custom role with the necessary privileges
Docker Docker CLI in your local environment (for pushing the image)
Snowflake Postgres Enabled on your account
Azure DevOps A repository for the dbt project, plus a PAT with the Code: Read scope

Build Steps

Step 1: Create the Role, Database, Warehouse, and Compute Pool

This step is pure setup work, so there is not much to explain.

Resource creation queries 
-- 1-1. Custom role
USE ROLE ACCOUNTADMIN;
CREATE ROLE IF NOT EXISTS LIGHTDASH_ADMIN_ROLE;

GRANT CREATE DATABASE ON ACCOUNT TO ROLE LIGHTDASH_ADMIN_ROLE;
GRANT CREATE WAREHOUSE ON ACCOUNT TO ROLE LIGHTDASH_ADMIN_ROLE;
GRANT CREATE COMPUTE POOL ON ACCOUNT TO ROLE LIGHTDASH_ADMIN_ROLE;
GRANT CREATE INTEGRATION ON ACCOUNT TO ROLE LIGHTDASH_ADMIN_ROLE;
GRANT CREATE POSTGRES INSTANCE ON ACCOUNT TO ROLE LIGHTDASH_ADMIN_ROLE;
GRANT BIND SERVICE ENDPOINT ON ACCOUNT TO ROLE LIGHTDASH_ADMIN_ROLE;
GRANT MONITOR USAGE ON ACCOUNT TO ROLE LIGHTDASH_ADMIN_ROLE;
GRANT ROLE LIGHTDASH_ADMIN_ROLE TO ROLE SYSADMIN;

-- 1-2. Database and schema
USE ROLE LIGHTDASH_ADMIN_ROLE;
CREATE DATABASE IF NOT EXISTS LIGHTDASH_DB;
CREATE SCHEMA IF NOT EXISTS LIGHTDASH_DB.LIGHTDASH_SCHEMA;

-- 1-3. Warehouse
CREATE WAREHOUSE IF NOT EXISTS LIGHTDASH_WH
  WAREHOUSE_SIZE = 'XSMALL'
  AUTO_SUSPEND = 60
  AUTO_RESUME = TRUE;

-- 1-4. Compute Pool
CREATE COMPUTE POOL IF NOT EXISTS LIGHTDASH_COMPUTE_POOL
  MIN_NODES = 1
  MAX_NODES = 1
  INSTANCE_FAMILY = CPU_X64_S
  AUTO_SUSPEND_SECS = 3600
  AUTO_RESUME = TRUE;

-- 1-5. Image Repository
CREATE IMAGE REPOSITORY IF NOT EXISTS LIGHTDASH_DB.LIGHTDASH_SCHEMA.LIGHTDASH_REPO;

-- Check the repository URL (you will need it for docker push)
SHOW IMAGE REPOSITORIES IN SCHEMA LIGHTDASH_DB.LIGHTDASH_SCHEMA;

-- 1-6. Stage for the spec file
CREATE STAGE IF NOT EXISTS LIGHTDASH_DB.LIGHTDASH_SCHEMA.LIGHTDASH_SPECS
  ENCRYPTION = (TYPE = 'SNOWFLAKE_SSE');
Enter fullscreen mode Exit fullscreen mode

Step 2: Create the Snowflake Postgres Instance 

USE ROLE ACCOUNTADMIN;

-- 2-1. Network rule and policy
CREATE OR REPLACE NETWORK RULE LIGHTDASH_DB.LIGHTDASH_SCHEMA.PG_INGRESS_RULE
  TYPE = IPV4
  MODE = POSTGRES_INGRESS
  VALUE_LIST = ('0.0.0.0/0')   -- TODO: restrict in production
  COMMENT = 'Allow ingress to Postgres instance';

CREATE OR REPLACE NETWORK POLICY LIGHTDASH_PG_NETWORK_POLICY
  ALLOWED_NETWORK_RULE_LIST = (LIGHTDASH_DB.LIGHTDASH_SCHEMA.PG_INGRESS_RULE);

GRANT USAGE ON NETWORK POLICY LIGHTDASH_PG_NETWORK_POLICY TO ROLE LIGHTDASH_ADMIN_ROLE;

-- 2-2. Create the Postgres instance
USE ROLE LIGHTDASH_ADMIN_ROLE;

CREATE POSTGRES INSTANCE LIGHTDASH_PG
  COMPUTE_FAMILY = 'BURST_S'
  STORAGE_SIZE_GB = 50
  AUTHENTICATION_AUTHORITY = POSTGRES
  POSTGRES_VERSION = 17
  HIGH_AVAILABILITY = FALSE
  NETWORK_POLICY = 'LIGHTDASH_PG_NETWORK_POLICY';
Enter fullscreen mode Exit fullscreen mode

A note on that network rule: I set VALUE_LIST = ('0.0.0.0/0') for the SPCS-to-Postgres connection, and as far as I can tell there is currently no way around it. I vaguely recall hearing a rumor that static IP support is in preview. Either way, keep this in mind if you are thinking about production use.

"You can create a Postgres instance with plain SQL? Snowflake never disappoints!" — that was my first reaction. But then I noticed that the admin username and password, which you can only obtain at instance creation time, were never displayed.

Wait... did I miss them?

So I reset the password from Snowsight instead. Clicking "Regenerate credentials" does the trick.

pg

Once the instance is up, connect with psql (or a similar client) and create the database for Lightdash. It took about five minutes for the Postgres instance to become available.

psql -h <pg_host> -U <pg_user> -d postgres
# CREATE DATABASE lightdash;
Enter fullscreen mode Exit fullscreen mode

Mapping that back to the diagram at the top, here is how far we have come. Still a long way to go.

step2

Step 3: Create Snowflake Secrets

Next, store the Postgres password. While we are at it, we also create the encryption key that Lightdash uses.

USE ROLE LIGHTDASH_ADMIN_ROLE;
USE SCHEMA LIGHTDASH_DB.LIGHTDASH_SCHEMA;

-- For the PostgreSQL connection (PASSWORD type)
CREATE OR REPLACE SECRET LIGHTDASH_PG_SECRET
  TYPE = PASSWORD
  USERNAME = '<pg_username>'
  PASSWORD = '<pg_password>';

-- Lightdash encryption key (GENERIC_STRING type)
-- A random string of 32 characters or more. Cannot be changed once set.
CREATE OR REPLACE SECRET LIGHTDASH_APP_SECRET
  TYPE = GENERIC_STRING
  SECRET_STRING = '<random string>';
Enter fullscreen mode Exit fullscreen mode

LIGHTDASH_SECRET is the key Lightdash uses to encrypt stored data. Changing it apparently makes your data inaccessible. As long as it exists, things work, so I did not dig any deeper.

Step 4: Create the External Access Integrations (EAI)

SPCS containers have outbound traffic blocked by default, so we create two EAIs.

USE ROLE ACCOUNTADMIN;

-- 4-1. Network rule for Postgres egress
CREATE OR REPLACE NETWORK RULE LIGHTDASH_DB.LIGHTDASH_SCHEMA.PG_EGRESS_RULE
  TYPE = HOST_PORT
  MODE = EGRESS
  VALUE_LIST = ('<pg_host>:5432');

-- 4-2. EAI for Postgres
CREATE OR REPLACE EXTERNAL ACCESS INTEGRATION LIGHTDASH_PG_EAI
  ALLOWED_NETWORK_RULES = (LIGHTDASH_DB.LIGHTDASH_SCHEMA.PG_EGRESS_RULE)
  ALLOWED_AUTHENTICATION_SECRETS = (LIGHTDASH_DB.LIGHTDASH_SCHEMA.LIGHTDASH_PG_SECRET)
  ENABLED = TRUE;

GRANT USAGE ON INTEGRATION LIGHTDASH_PG_EAI TO ROLE LIGHTDASH_ADMIN_ROLE;

-- 4-3. For HTTPS egress to Azure DevOps and friends
CREATE OR REPLACE NETWORK RULE LIGHTDASH_DB.LIGHTDASH_SCHEMA.AZURE_DEVOPS_RULE
  TYPE = HOST_PORT
  MODE = EGRESS
  VALUE_LIST = ('dev.azure.com:443', 'login.microsoftonline.com:443',
                'app.vssps.visualstudio.com:443', 'aex.dev.azure.com:443');

CREATE OR REPLACE EXTERNAL ACCESS INTEGRATION ADO_EAI
  ALLOWED_NETWORK_RULES = (LIGHTDASH_DB.LIGHTDASH_SCHEMA.AZURE_DEVOPS_RULE)
  ENABLED = TRUE;

GRANT USAGE ON INTEGRATION ADO_EAI TO ROLE LIGHTDASH_ADMIN_ROLE;
Enter fullscreen mode Exit fullscreen mode

Having to define an EAI even for SPCS-to-Snowflake-Postgres traffic feels like a chore. It would be nice if this got a bit smoother someday.

With that, the EAIs are in place.

step4

Step 5: Prepare and Push the Docker Image

Lightdash publishes an official Docker image, so we use that.

# Pull the official Lightdash image
docker pull lightdash/lightdash:latest

# Tag it for the Snowflake Image Repository
docker tag lightdash/lightdash:latest \
  <repository_url>/lightdash:latest

# Log in to Snowflake and push
snow sql -c <connection>
docker push <repository_url>/lightdash:latest
Enter fullscreen mode Exit fullscreen mode

The flow is: log in with the Snowflake CLI, then run docker push. Once it finishes, you can see the image in Snowsight.

image

Step 6: Write the Service Spec YAML

Create lightdash_service_spec.yaml and fill in the Postgres connection details.

lightdash_service_spec.yaml 
spec:
  containers:
  - name: lightdash
    image: /lightdash_db/lightdash_schema/lightdash_repo/lightdash:latest
    env:
      # -- PostgreSQL connection --
      PGHOST: "<pg_host>"
      PGPORT: "5432"
      PGUSER: "<pg_username>"
      PGDATABASE: "lightdash"

      # -- SSL settings (for Snowflake Postgres) --
      PGSSLMODE: "no-verify"
      NODE_TLS_REJECT_UNAUTHORIZED: "0"

      # -- Lightdash core --
      PORT: "8080"
      LIGHTDASH_INSTALL_TYPE: "spcs"
      LIGHTDASH_LOG_LEVEL: "info"
      LIGHTDASH_QUERY_MAX_LIMIT: "5000"
      LIGHTDASH_MAX_PAYLOAD: "5mb"
      SECURE_COOKIES: "true"
      TRUST_PROXY: "true"

      # -- Scheduler --
      SCHEDULER_ENABLED: "true"
      SCHEDULER_CONCURRENCY: "3"

      # -- SITE_URL is set in Step 8 --
      # SITE_URL: "https://<endpoint_url>"

    secrets:
    - snowflakeSecret: LIGHTDASH_DB.LIGHTDASH_SCHEMA.LIGHTDASH_PG_SECRET
      secretKeyRef: password
      envVarName: PGPASSWORD
    - snowflakeSecret: LIGHTDASH_DB.LIGHTDASH_SCHEMA.LIGHTDASH_APP_SECRET
      secretKeyRef: secret_string
      envVarName: LIGHTDASH_SECRET

    readinessProbe:
      port: 8080
      path: /api/v1/health

    resources:
      requests:
        memory: 2G
        cpu: 1
      limits:
        memory: 4G
        cpu: 2

  endpoints:
  - name: ui
    port: 8080
    public: true
Enter fullscreen mode Exit fullscreen mode

Snowflake Postgres uses a self-signed certificate, which is why we set PGSSLMODE: "no-verify" and NODE_TLS_REJECT_UNAUTHORIZED: "0". Without them, the connection fails with a SELF_SIGNED_CERT_IN_CHAIN error.

Upload the YAML to the stage.

snow stage copy lightdash_service_spec.yaml \
  @LIGHTDASH_DB.LIGHTDASH_SCHEMA.LIGHTDASH_SPECS/
Enter fullscreen mode Exit fullscreen mode

Step 7: Create the Service

Now create the service from the image we pushed and grab the endpoint.

USE ROLE LIGHTDASH_ADMIN_ROLE;

CREATE SERVICE LIGHTDASH_DB.LIGHTDASH_SCHEMA.LIGHTDASH_SERVICE
  IN COMPUTE POOL LIGHTDASH_COMPUTE_POOL
  FROM @LIGHTDASH_DB.LIGHTDASH_SCHEMA.LIGHTDASH_SPECS
  SPECIFICATION_FILE = 'lightdash_service_spec.yaml'
  MIN_INSTANCES = 1
  MAX_INSTANCES = 1
  EXTERNAL_ACCESS_INTEGRATIONS = (LIGHTDASH_PG_EAI, ADO_EAI)
  QUERY_WAREHOUSE = LIGHTDASH_WH
  AUTO_RESUME = TRUE;
Enter fullscreen mode Exit fullscreen mode

It takes a little while for the engine to warm up.

-- Check the status
SELECT SYSTEM$GET_SERVICE_STATUS(
  'LIGHTDASH_DB.LIGHTDASH_SCHEMA.LIGHTDASH_SERVICE'
);
Enter fullscreen mode Exit fullscreen mode

Once the status reads Ready, you are good to go.

-- Get the public endpoint URL
SHOW ENDPOINTS IN SERVICE LIGHTDASH_DB.LIGHTDASH_SCHEMA.LIGHTDASH_SERVICE;
Enter fullscreen mode Exit fullscreen mode

You should get an address that looks like https://xxxxxxx-<org>-<account>.snowflakecomputing.app.

The service is finally up, and things are starting to take shape.

step7

Step 8: Set Up the dbt Project (jaffle_shop)

To verify that Lightdash works, we use the official sample project, jaffle_shop. My environment uses Azure DevOps, but GitHub works just as well.

8-1. Create the schema on the Snowflake side 

USE ROLE LIGHTDASH_ADMIN_ROLE;
CREATE SCHEMA IF NOT EXISTS LIGHTDASH_DB.JAFFLE_SHOP;

GRANT ALL PRIVILEGES ON SCHEMA LIGHTDASH_DB.JAFFLE_SHOP TO ROLE LIGHTDASH_ADMIN_ROLE;
GRANT ALL PRIVILEGES ON FUTURE TABLES IN SCHEMA LIGHTDASH_DB.JAFFLE_SHOP TO ROLE LIGHTDASH_ADMIN_ROLE;
Enter fullscreen mode Exit fullscreen mode

8-2. Prepare the repository and mirror it to Azure DevOps 

git clone https://github.com/lightdash/jaffle_shop.git
cd jaffle_shop

# Push to Azure DevOps
git remote add azure https://dev.azure.com/<org>/<project>/_git/jaffle_shop
git push azure --all
git push azure --tags
Enter fullscreen mode Exit fullscreen mode

Cloning through the Azure DevOps GUI is fine too. Do not forget to prepare profile.yml.

8-3. Load the data with the dbt project

If you already have an environment where you can run dbt, feel free to run it there. I did not have dbt Core available in my environment, so I used Snowflake's dbt project feature.

snow dbt deploy JAFFLE_SHOP_PROJECT \
  --source "<path_to_jaffle_shop>" \
  --database LIGHTDASH_DB \
  --schema JAFFLE_SHOP
Enter fullscreen mode Exit fullscreen mode 
USE ROLE LIGHTDASH_ADMIN_ROLE;
USE DATABASE LIGHTDASH_DB;
USE SCHEMA JAFFLE_SHOP;
USE WAREHOUSE LIGHTDASH_WH;

EXECUTE DBT PROJECT JAFFLE_SHOP_PROJECT ARGS = 'seed';
EXECUTE DBT PROJECT JAFFLE_SHOP_PROJECT ARGS = 'run';
Enter fullscreen mode Exit fullscreen mode

dbt

That wraps up the resource setup.

Step 9: Initial Setup in the Lightdash UI

Open the SPCS public endpoint URL in your browser and walk through the initial setup.

9-1. Create the admin account

On the screen that appears the first time you visit, enter your email address, password, and name.

sign up

9-2. Connect the dbt project and configure Snowflake

Fill in the fields as prompted. For Snowflake I chose key-pair authentication.

sf
dbt

The dbt configuration asks for the project's repository name, and it turns out the Azure DevOps project name and repository name have to be identical.

It Works

welcome

I managed to get it up and running.

Here is a chart I put together by clicking around:

graph

I am looking forward to playing with Lightdash. That is it for today.

Conclusion

Using SPCS, I was able to host Lightdash on Snowflake. Until fairly recently you had to run PostgreSQL on SPCS as well, but now Snowflake Postgres is available.

Beyond Lightdash, this approach feels like it could work for the applications we use at work, which leaves me satisfied.

Thanks for reading!

Top comments (0)