Utilities for Snowflake development workflows

1. Stage Sync (stage_sync.py)

Download files from Snowflake workspace to local directory while preserving folder structure

2. Docker + Snowflake SPCS setup

This is for app hosting on Snowpark Container Services

Snowpark Container Services setup in Snowflake

Run below in Snowflake .sql, replace <ACCOUNTADMIN>, <TESTING_DB>, <COMPUTE_WH>, <SPCS>, <NEW_REPO> with your own one

USE ROLE <ACCOUNTADMIN>;
USE DATABASE <TESTING_DB>;
USE WAREHOUSE <COMPUTE_WH>;

-- create image repository in Snowflake
CREATE SCHEMA IF NOT EXISTS <SPCS>;

CREATE IMAGE REPOSITORY IF NOT EXISTS <SPCS>.<NEW_REPO>;

-- check Snowflake repository url
-- copy this <repository_url> for docker command below
-- example: abcdefg-ab88888.registry.snowflakecomputing.com/testing_db/spacs/new_repo
SHOW IMAGE REPOSITORIES IN SCHMEA <SPCS>;

Build and push docker image (run locally)

Run below in terminal, replace <image_name> with your image name, <repository_url> with image repository url in Snowflake, and <repository_url_base> with image repository url of base url

# build docker image locally
docker build -t <image_name> .

# check docker images
docker images

# tag for Snowflake registry, replace <image_name>
docker tag <image_name> <repository_url>/<image_name>

# login to Snowflake docker registry 
# example: docker login abcdefg-ab88888.registry.snowflakecomputing.com
docker login <repository_url_base>

# Username: your Snowflake username
# Password: your Snowflake password

# push image
docker push <repository_url>/<image_name>

Create compute pool in Snowflake

Replace <NEW_REPO> with your compute pool name

CREATE COMPUTE POOL IF NOT EXISTS <NEW_POOL>
  MIN_NODES = 1
  MAX_NODES = 2
  INSTANCE_FAMILY = CPU_X64_XS
  AUTO_RESUME = TRUE
  AUTO_SUSPEND_SECS = 300;

-- Check pool status
DESCRIBE COMPUTE POOL <NEW_REPO>;

Create service specification

Replace <SPCS> and <SERVICE_SPECS> with your schema and service name respectively

-- Create a stage to store the service spec
CREATE STAGE IF NOT EXISTS <SPCS>.<SERVICE_SPECS>
  ENCRYPTION = (TYPE = 'SNOWFLAKE_SSE');

Create service with Snowflake connection

Replace <NEW_SERVICE> with your service name

CREATE SERVICE IF NOT EXISTS <SPCS>.<NEW_SERVICE>
  IN COMPUTE POOL NEW_POOL
  FROM SPECIFICATION $$
spec:
  containers:
    - name: dashboard
      image: /<testing_db>/<spcs>/<new_repo>/<image_name>
      env:
        SNOWFLAKE_ACCOUNT: "<account_identifier>"
        SNOWFLAKE_DATABASE: "<TESTING_DB>"
        SNOWFLAKE_SCHEMA: "<CONFIG>"
        SNOWFLAKE_WAREHOUSE: "<COMPUTE_WH>"
      resources:
        requests:
          cpu: 0.5
          memory: 1G
        limits:
          cpu: 2
          memory: 2G
      secrets:
        - snowflakeSecret:
            objectName: spcs.dashboard_credentials
            objectType: password
          envVarName: SNOWFLAKE_PASSWORD
          secretKeyRef: password
        - snowflakeSecret:
            objectName: spcs.dashboard_credentials
            objectType: password
          envVarName: SNOWFLAKE_USER
          secretKeyRef: username
  endpoints:
    - name: dashboard
      port: 8080
      public: true
$$
  MIN_INSTANCES = 1
  MAX_INSTANCES = 2
  QUERY_WAREHOUSE = <COMPUTE_WH>;

Get url for app hosted on SPCS

-- Check service status
CALL SYSTEM$GET_SERVICE_STATUS('<SPCS>.<NEW_SERVICE>');

-- Get the public endpoint URL
SHOW ENDPOINTS IN SERVICE <SPCS>.<NEW_SERVICE>;