DEV Community: Mikoto Takigawa

Bringing Snowflake App Runtime Into Your Data Lineage Graph: A Horizon Context Experiment

Mikoto Takigawa — Thu, 15 Oct 2026 09:05:36 +0000

Introduction

Snowflake App Runtime lets you build and deploy a Next.js app directly on top of Snowflake, which is great for shipping internal tools fast. The catch is that once the app is live, it tends to disappear from your metadata. Snowsight's lineage graph stops at the table and view level, so if you want to know which app or API depends on a given column, you're stuck grepping through source code.

That bugged me enough to try fixing it. Snowflake's External Lineage API — which accepts lineage events in the open OpenLineage format and folds them straight into the lineage graph — has actually been generally available for a while now. What got me looking at it again is Horizon Context, a feature Snowflake recently put into preview that extends Horizon Catalog into a broader, pluggable metadata layer. If App Runtime's APIs and screens show up as regular nodes in that lineage graph, downstream of the tables they read from, a lot of the "what breaks if I change this column" guesswork goes away.

Heads up: Horizon Context itself was still in preview as of October 2026, so some of the framing around it may shift. The External Lineage mechanics this article relies on, though, are already GA. Check the official docs for anything that looks off by the time you read this.

Horizon Context release note

Here's the plan: take a small Next.js app deployed on App Runtime, automatically extract which tables its API routes query and which screens call those APIs, and push that as an OpenLineage event into Snowflake's lineage graph.

The Sample App

I skipped the app-building part, but here's what it looks like — a couple of screens on top of the dbt-labs jaffle_shop sample data, one for analytics and one for editing master data.

Charts built from mart-layer data

A tool for writing back to Snowflake's master data

The layout follows the standard Next.js App Router conventions: API routes with embedded SQL live under app/api/, and the screens live under app/ and components/.

jaffle-app/
├── app/
│   ├── page.tsx                 ← Dashboard screen (/)
│   ├── customers/page.tsx       ← Customers screen (/customers)
│   ├── orders/page.tsx          ← Orders screen (/orders)
│   └── api/
│       ├── dashboard/route.ts   ← GET: monthly revenue
│       ├── customers/route.ts   ← GET: customer list
│       ├── orders/route.ts      ← GET: order list
│       └── master-edit/route.ts ← PUT: update customer info
├── components/
│   ├── dashboard-view.tsx       ← calls fetch("/api/dashboard")
│   ├── customers-view.tsx       ← calls fetch("/api/customers"), fetch("/api/master-edit")
│   └── orders-view.tsx          ← calls fetch("/api/orders")
└── lib/
    └── snowflake.ts             ← Snowflake connection helper

To wire this into lineage, I pull the following out of the directory automatically:

Info	How it's extracted	Example
API endpoint name	File path under `app/api/`	`app/api/customers/route.ts` → `/app/api/customers`
HTTP method	export function GET/PUT in the file	`GET` or `PUT`
Tables referenced	SQL template literals, parsed with sqlglot	`JAFFLE_SHOP.MARTS.CUSTOMERS`
Screen node name	Page file path + app name	`app/customers/page.tsx` → `jaffle-app/customers`
Screen → API mapping	`fetch("/api/...")` calls inside components	`customers-view.tsx` → `/api/customers`, `/api/master-edit`

Once that's folded into the table and view lineage, you can see at a glance which API reads a given table and which screen calls that API.

The pipeline is three steps:

Extract — pull SQL template literals out of the TypeScript API route files with a regex
Analyze — parse the SQL with sqlglot: resolve table FQNs and trace column-level lineage
Send — build an OpenLineage COMPLETE event and push it to Snowflake's REST API

Step 1: Pulling SQL Out of TypeScript

Every App Runtime API route lives at app/api/<name>/route.ts, following the Next.js App Router convention, and the SQL inside each one is written as a template literal:

// app/api/customers/route.ts
const LIST_SQL = (db: string, schema: string) => `
  SELECT CUSTOMER_ID, CUSTOMER_NAME,
         COUNT_LIFETIME_ORDERS,
         LIFETIME_SPEND
  FROM ${db}.${schema}.CUSTOMERS
  WHERE CUSTOMER_NAME ILIKE ?
  ORDER BY LIFETIME_SPEND DESC
  LIMIT ? OFFSET ?
`

A regex pulls that out:

_TEMPLATE_LITERAL = re.compile(
    r'(?:const|let|var)\s+(\w+)\s*=\s*'
    r'(?:\([^)]*\)\s*(?::\s*\w+)?\s*=>\s*)?'  # the arrow function's argument list
    r'`(.*?)`',
    re.DOTALL,
)

It handles both the plain const NAME = \...form and the `const NAME = (db, schema) => \`...\ arrow-function form. The HTTP method (GET/PUT/POST/DELETE) is inferred from the exported function name and the variable name in the same file.

Step 2: Parsing SQL and Tracing Column Lineage with sqlglot

The extracted queries go through sqlglot. They still contain template placeholders like ${db} and Snowflake bind variables (:name, ?), so they need a bit of preprocessing before sqlglot can touch them:

def preprocess(sql: str) -> str:
    # ${VAR} -> actual value
    sql = re.sub(r'\$\{(\w+)\}', replacer, sql)
    # :name -> __BIND_name__ (skip the :: cast operator)
    sql = re.sub(r'(?<!:):(\w+)', r'__BIND_\1__', sql)
    return sql

Once preprocessed, sqlglot parses the SQL into an AST and pulls out every table reference from it. Its lineage() API goes further and traces, for each output column of a SELECT, exactly which table and column it was derived from.

Quick aside on what an AST actually is: it's a tree representation of the code — "this SELECT has a FROM clause, which has a table name" — and sqlglot builds one for every query it parses, then walks it to resolve table references and column dependencies.

from sqlglot.lineage import lineage

result = lineage("CUSTOMER_NAME", sql, schema=schema, dialect="snowflake")
# -> CUSTOMERS.CUSTOMER_NAME

Being able to pass dialect="snowflake" directly is a nice touch. It resolves correctly even through joins, CASE expressions, and aggregates — feed it something like this:

SELECT o.ORDER_ID,
       c.CUSTOMER_NAME,    -- comes from the right side of the LEFT JOIN
       CASE
         WHEN o.ORDER_TOTAL > 50 THEN 'HIGH'
         ELSE 'LOW'
       END AS VALUE_TIER   -- derived from the CASE condition column
FROM ORDERS o
JOIN CUSTOMERS c ON o.CUSTOMER_ID = c.CUSTOMER_ID

and it correctly attributes each output column back to its source, matching the comments above.

One catch: lineage() needs schema information (the tables' column definitions) to work. I fetched that ahead of time by connecting to Snowflake and running DESCRIBE TABLE.

Step 3: Building and Sending the OpenLineage Event

Two kinds of nodes get registered as lineage:

Layer	Event content	Purpose
Layer 1	table ↔ API endpoint	SQL input/output relationship, with column lineage
Layer 2	API endpoint → screen node	which screen calls which API

Screen nodes are named with the app name included, like jaffle-app/dashboard. The screen → API mapping is generated by regex-extracting fetch("/api/...") calls out of each frontend component and matching them back to the corresponding page.tsx file.

OpenLineage is an open format for data lineage: a job's inputs and outputs get recorded as a COMPLETE event. Snowflake accepts that format at the /api/v2/lineage/external-lineage endpoint and merges it straight into the lineage graph.

The read event for Layer 1 (table → API) carries OpenLineage's columnLineage facet. Facets are OpenLineage's mechanism for attaching extra information to a lineage event, but Snowflake currently only honors two of them: datasetType (controls how the node is labeled) and columnLineage (column-level lineage). Other facets like schema, documentation, or ownership won't error out, but they're silently ignored. I'd love to see documentation supported at some point — fingers crossed for a future update.

Here's what the assembled event looks like:

{
  "eventType": "COMPLETE",
  "job": { "namespace": "jaffle-app", "name": "api-app-api-orders" },
  "inputs": [
    { "namespace": "snowflake://<account-identifier>", "name": "JAFFLE_SHOP.MARTS.ORDERS" },
    { "namespace": "snowflake://<account-identifier>", "name": "JAFFLE_SHOP.MARTS.CUSTOMERS" }
  ],
  "outputs": [
    {
      "namespace": "app-runtime://jaffle-app",
      "name": "/app/api/orders",
      "facets": {
        "datasetType": { "datasetType": "API_ENDPOINT" },
        "columnLineage": {
          "fields": {
            "ORDER_ID": {
              "inputFields": [
                { "namespace": "snowflake://<account-identifier>",
                  "name": "JAFFLE_SHOP.MARTS.ORDERS", "field": "ORDER_ID" }
              ]
            },
            "CUSTOMER_NAME": {
              "inputFields": [
                { "namespace": "snowflake://<account-identifier>",
                  "name": "JAFFLE_SHOP.MARTS.CUSTOMERS", "field": "CUSTOMER_NAME" }
              ]
            },
            "VALUE_TIER": {
              "inputFields": [
                { "namespace": "snowflake://<account-identifier>",
                  "name": "JAFFLE_SHOP.MARTS.ORDERS", "field": "ORDER_TOTAL" }
              ]
            }
          }
        }
      }
    }
  ]
}

From there, it's a plain REST call to Snowflake, using the session token from the Python connector:

rest = connection._rest
token = rest.token

url = f"{rest._protocol}://{rest._host}:{rest._port}/api/v2/lineage/external-lineage"
headers = {"Authorization": f'Snowflake Token="{token}"'}

response = requests.post(url, json=event, headers=headers)

Results

Here's CUSTOMERS before any of this: Snowflake's native query-based lineage already traces it back through staging to the raw table, but that's where it stops. No app, no API, nothing downstream.

Before: lineage ends at the table level

Once the OpenLineage event above goes through, the app's APIs show up in the lineage of the tables and views they touch:

After: both the API and the screen that calls it now show up as connected nodes

Anything registered through OpenLineage shows up labeled as External — in this case, the first node is the API, and the second is the screen that calls it.

Write APIs (PUT/PATCH-style, like the master-data editor) show up as upstream of the table instead, since they write into it rather than read from it:

A write-path API shows up upstream of the table

Being able to see which screen calls which API, on top of the table relationship, is the part I like most here.

Putting the Lineage to Work

Say you want to change the definition of CUSTOMERS.CUSTOMER_NAME. Snowflake's built-in GET_LINEAGE function will tell you exactly what that touches:

SELECT SOURCE_OBJECT_NAME, SOURCE_COLUMN_NAME,
       TARGET_OBJECT_NAME, TARGET_COLUMN_NAME, DISTANCE
FROM TABLE(
  SNOWFLAKE.CORE.GET_LINEAGE(
    'JAFFLE_SHOP.MARTS.CUSTOMERS.CUSTOMER_NAME',
    'COLUMN', 'DOWNSTREAM', 3
  )
)

Source	Source column	Target	Target column	Distance
CUSTOMERS	CUSTOMER_NAME	/app/api/customers	CUSTOMER_NAME	1
CUSTOMERS	CUSTOMER_NAME	/app/api/orders	CUSTOMER_NAME	1

One query, and it's immediately obvious that changing CUSTOMER_NAME affects both the /api/customers and /api/orders endpoints.

The same path works from Snowflake's CoCo interface too — you can land on the same app-level nodes starting from a table.

Wrapping Up

Horizon Context's metadata connectors handle the easy cases — plugging in systems that already have a connector. OpenLineage is what you reach for when there isn't one: it let me register an app that would otherwise be invisible to Snowflake's lineage graph, API by API and screen by screen, with full column-level detail.

This is a small slice of what Horizon Context can do; there's a lot more in there worth exploring. But if you've ever had to go read source code just to figure out what a schema change would break, this is a solid place to start.

Separating Environments for an ML Platform on Snowflake

Mikoto Takigawa — Wed, 23 Sep 2026 08:18:54 +0000

Sharing ML Models Across Accounts Is Now Possible

Snowflake's Direct Share now supports ML models.

(Reference: Snowflake Model Registry - Sharing models)

This landed without me noticing. A good reminder that you really do have to keep up with the documentation.

This feature significantly widens the set of options available when you use Snowflake as an ML platform, so I want to lay them out.

Checking What Is Now Possible

First, let me go through what Direct Share actually lets you do.

I prepared a model using Snowflake's example helper. Model accuracy is irrelevant here, so the model itself is thrown together.

As I explain later, sharing behaves differently depending on whether you run inference in a warehouse or on SPCS, so the sample code below builds both.

Preparing a prediction model in the provider account

Environment:

Snowflake Notebook
Container Runtime v2.6

from snowflake.ml.feature_store.examples.example_helper import ExampleHelper
from snowflake.ml.feature_store import (
    FeatureStore,
    FeatureView,
    Entity,
    CreationMode,
    FeatureViewStatus,
)
from snowflake.ml.registry import Registry
from snowflake.ml.model.target_platform import TargetPlatform

import xgboost as xgb
from sklearn.model_selection import train_test_split
import pandas as pd

from snowflake.snowpark.context import get_active_session
session = get_active_session()

example_helper = ExampleHelper(session, session.get_current_database(), 'PUBLIC')


source_tables = example_helper.load_example('new_york_taxi_features')

fs = FeatureStore(
    session=session, 
    database=session.get_current_database(), 
    name='PUBLIC', 
    default_warehouse=session.get_current_warehouse(),
    creation_mode=CreationMode.CREATE_IF_NOT_EXIST,
)


for fv in example_helper.load_draft_feature_views():
    fs.register_feature_view(
        feature_view=fv,
        version='1.0'
    )

entity_key_names = ','.join(my_entity.join_keys)
spine_df = session.sql(f"SELECT {entity_key_names} FROM {source_tables[0]}").sample(n=1000)

training_fv = fs.get_feature_view(target_feature_view, '1.0')

training_data_df = fs.generate_training_set(
    spine_df=spine_df,
    features=[training_fv]
)


df = training_data_df.to_pandas()
feature_cols = ['PASSENGER_COUNT', 'TRIP_DISTANCE', 'TIP_AMOUNT', 'TOLLS_AMOUNT', 'PICKUP_LOCATION_ID', 'DROPOFF_LOCATION_ID']
target_col = 'FARE_AMOUNT'

X = df[feature_cols]
y = df[target_col]

X_train, X_test, y_train, y_test = train_test_split(X, y, test_size=0.2, random_state=42)

model = xgb.XGBRegressor(n_estimators=100, max_depth=4, learning_rate=0.1, random_state=42)
model.fit(X_train, y_train)

reg = Registry(session=session, database_name='ML_SHARE_TEST', schema_name='PUBLIC')
reg.log_model(
    model=model,
    model_name='taxi_fare_xgboost',
    version_name='v1',
    sample_input_data=X_train[:10],
    conda_dependencies=['xgboost'],
)

reg.log_model(
    model=model,
    model_name='taxi_fare_xgboost',
    version_name='v2_warehouse',
    sample_input_data=X_train[:10],
    conda_dependencies=['xgboost'],
    target_platforms=[TargetPlatform.WAREHOUSE],
)

Version v1 runs on SPCS, and v2_warehouse runs in a warehouse.

1. Provider Side: Create the Share Object

Create the share on the provider side.

USE ROLE ACCOUNTADMIN;
CREATE SHARE ML_MODEL_SHARE
    SECURE_OBJECT_ONLY = FALSE;

2. Grant Privileges to the Share

There are two ways to do this. One is to grant the model privileges directly to the share; the other is to grant them to a database role and then hand that role to the share.

The database role approach makes it easier for the consumer to reproduce the provider's role structure, so that is what I use here.

CREATE DATABASE ROLE DB_ROLE_SHARE;

-- Grant schema USAGE to the database role
GRANT USAGE ON SCHEMA ML_SHARE_TEST.PUBLIC TO DATABASE ROLE DB_ROLE_SHARE;

-- Grant the model privilege to the database role
GRANT USAGE ON MODEL ML_SHARE_TEST.PUBLIC.TAXI_FARE_XGBOOST TO DATABASE ROLE DB_ROLE_SHARE;

-- Put the database role into the share
GRANT USAGE ON DATABASE ML_SHARE_TEST TO SHARE ML_MODEL_SHARE;
GRANT DATABASE ROLE DB_ROLE_SHARE TO SHARE ML_MODEL_SHARE;

-- Add the consumer account to the share
ALTER SHARE ML_MODEL_SHARE ADD ACCOUNTS = <consumer_account_locator>;

You can view the share you created under External Sharing in the Data Sharing tab of Snowsight.

The database role is indeed included in the share.

3. Consumer Side: Create a Database from the Share

Now we move to the consumer account and create a shared database from the share.

CREATE DATABASE SHARED_ML_DB FROM SHARE <provider_account>.ML_MODEL_SHARE;
GRANT DATABASE ROLE SHARED_ML_DB.DB_ROLE_SHARE TO ROLE <custom_role>;

Here the shared database role is inherited by an appropriate custom role on the consumer side.

The model is now visible in the consumer's database explorer.

4. Consumer Side: Run Inference

You can run inference with the shared model.

from snowflake.ml.registry import Registry

reg = Registry(session, database_name='SHARED_ML_DB', schema_name='PUBLIC')
model = reg.get_model('TAXI_FARE_XGBOOST')
mv = model.version('V2_WAREHOUSE')

input_df = session.table('SHARED_ML_DB.PUBLIC."TAXI_TRIP_FEATURES$1.0"').select(
    'PASSENGER_COUNT', 'TRIP_DISTANCE', 'TIP_AMOUNT', 
    'TOLLS_AMOUNT', 'PICKUP_LOCATION_ID', 'DROPOFF_LOCATION_ID'
)

result_wh = mv.run(input_df, function_name='PREDICT')
result_wh.show(10)

The code is no different from working with a normal model. As long as the consumer provides the data and the compute, the same inference code that runs on the provider side runs here too.

Note 1: The Difference Between Privileges

There are two privileges you can hand to a share from a model: USAGE and READ. The easiest way to think about which one you need is in terms of the target_platform the model was created with.

target_platform	What you want to do	Required privilege
Warehouse	Inference via `mv.run`	USAGE
SPCS	Creating an inference service, inference on SPCS	READ

If you grant only USAGE on an SPCS model, the model's artifact files cannot be read and therefore cannot be loaded onto SPCS. That rules out both creating an inference service and running inference on SPCS with something like run_batch.

Also, with either privilege, you cannot copy a shared model into a model of your own.

-- This does not work
CREATE MODEL CONSUMER_DB.PUBLIC.SHARE_TEST_MODEL
FROM MODEL SHARED_ML_DB.PUBLIC.TAXI_FARE_XGBOOST VERSION V1;

Note 2: Replication

Model objects support not only sharing but also replication. Replicating one materializes the model on the consumer side. The materialized object is a replica of the source object, and its contents cannot be modified.

Designing Environments Around This Feature

From here I want to look at the possibilities that open up now that models can be shared.

Problems You Run Into with MLOps

One of the big advantages of practising MLOps or LLMOps on Snowflake is that you can build the platform directly on top of your data, with nothing in between. ML and AI only work when the underlying data is trustworthy, so being able to develop an ML platform as one more capability of the data platform is a significant benefit.

That said, building your environment around the data platform sometimes imposes constraints on the ML platform. Separate development and production accounts are a good example. When validation and production live in different Snowflake accounts, problems like these come up:

Even after confirming a model's accuracy in the validation environment, you have to retrain it in production, which means the accuracy of the model you actually operate is unknown
You end up repeating experiments in the production environment, risking an outage from human error
Even for identical processing, the features you can build differ between the validation and production environments, which makes validation meaningless

How much each of these matters depends on the business problem you are applying ML to and on the values of your team and company. So rather than simply chasing best practices, you need to think hard about which design actually fits what you want to do.

This article presents a few options that look reasonable, but each has its trade-offs and none of them is strictly superior. I hope it gives you a foothold for reaching the MLOps setup that is right for you.

Comparing the Concrete Options

It is easier to organise the patterns if you focus on three points:

Where training happens
Where accuracy validation happens
How a model is promoted to production

Pattern Overview

	A. Single environment	B. Training in production	C. Training in development
Training location	Production	Production	Development
Validation location	Production	Development	Development
Promotion method	Alias	Alias	Replication + alias

Let me go through each pattern with a diagram.

A. Single Environment

The simplest setup, where training and validation of the ML model are completed entirely within the production account.

A trunk-based branching strategy tends to keep development fast here, so I think this is the form to start with for a PoC or a small project.

By small project I mean the blast radius of the MLOps work, not the size of the data. For instance, when the goal is simply to build a model and produce predictions (when ML sits at the very end of the workflow), a few errors or some bad data may be tolerable. When most of the blast radius is under the control of the people doing MLOps, this approach has a lot going for it.

Pros: the model you validated is the model you operate. No data movement is involved, so you can start with nothing more than your regular processing pipeline.

Cons: ML model developers need access to the production environment, and as the scale grows, privilege management tends to become the bottleneck.

B. Training in Production

A setup where a model trained in the production account is exposed to the development account via Direct Share, and only validation work happens on the development side.

It is easier to get around the privilege constraints of production while still giving ML model developers a reasonable degree of freedom, but deploying a model to production involves more steps, so development slows down somewhat. On the other hand, it combines well with a wide range of branching strategies, and I think it adapts to almost any project.

Pros: because training happens in production, the validated model and the model running in production are the same object. Even when the training data contains sensitive information, you can train without exposing that data to the development environment. You can also validate safely in the development environment without running commands directly against production or triggering production pipelines.

Cons: since training runs in production, the branching strategy tends to get complicated, or you end up with several approval phases. You have to weigh how much developer velocity the data scientists need against how much clutter you are willing to accept in production, and find a development flow that fits.

C. Training in Development

An approach where a model trained in the development environment is replicated into production and operated there. Replication breaks the dependency between the environments and lets you operate the model as an immutable object.

Trial and error on the model happens in the development environment, which allows for flexible development. On the other hand, you have to guarantee the credibility of a model built in development and keep it consistent with objects outside the model itself, which makes the environment harder to build.

Some companies have operational policies that forbid putting objects created in a development environment into production, so it is important to sketch out a workable shape first. That demands a high level of engineering.

Pros: high freedom for trial and error and a fast iteration loop for developers. As with B, the model running in production and the model used for validation are the same.

Cons: if some data has to be masked in the development environment, you may not be able to develop the model properly. You also have to tighten the privilege design in the development environment as well, which tends to come back as operational and maintenance cost.

If your data platform is already running solidly and you are adding an ML environment on top of it, this may be an easy option to take.

Closing

In this article I organised the options for designing an MLOps environment around Snowflake's model sharing and replication features.

Each of the three patterns has its own pros and cons, and no single one is always the right answer. The right setup shifts with the scale and phase of the project and with your organisation's security policy.

Start with a simple setup, then revisit the architecture in stages as the project grows and the data platform changes. Snowflake gives you features with exactly that kind of flexibility.

Running Lightdash on Snowpark Container Services

Mikoto Takigawa — Wed, 23 Sep 2026 06:55:52 +0000

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

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');

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';

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.

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;

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

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>';

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;

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.

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

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

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

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/

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;

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'
);

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;

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.

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;

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

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

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';

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.

9-2. Connect the dbt project and configure Snowflake

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

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

I managed to get it up and running.

Here is a chart I put together by clicking around:

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!