# Snowflake Setup Requirements (GCP) source: https://docs.chalk.ai/docs/snowflake-gcp-deployment ## Setting up Snowflake as your offline store with Google Cloud Storage. This guide covers setting up Snowflake as your offline store for GCP deployments using GCS storage integration. The steps below will guide you through the necessary Snowflake database setup and configuring the offline store connection. For architecture overview, component hierarchy, and multi-environment planning, see Snowflake Offline Store Overview. ### Database Setup To use Snowflake as your offline store, you need a warehouse, database, schema, and a user with a role whose access is scoped to the offline-store schema. Chalk does not need ownership of the database or schema. See Required Snowflake Permissions for the complete privilege model, including shared-user and existing-store setups. ### Step 0: Generate Key Pair Generate a private key in PKCS#8 format without encryption. Other key formats are not currently supported. ``` openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out rsa_key.p8 -nocrypt ``` Then generate the corresponding public key: ``` openssl rsa -in rsa_key.p8 -pubout -out rsa_key.pub ``` For more details, see the Snowflake key-pair authentication guide. ### Step 1: Create Database Objects Run the following commands in SnowSQL, replacing the variables at the top. For multi-environment deployments, see the annotations and the Additional Environments section below. ``` -- ═══════════════════════════════════════════════════════════════ -- CLUSTER-LEVEL RESOURCES (can be shared or separated per env) -- These CAN be shared across environments, or you can create -- separate databases/warehouses per environment if desired -- ═══════════════════════════════════════════════════════════════ SET WAREHOUSE_NAME='CHALK_WAREHOUSE'; -- Can share across envs OR use CHALK_WAREHOUSE_DEV, etc. SET WAREHOUSE_SIZE='XSMALL'; SET DB_NAME='CHALK'; -- Can share across envs OR use CHALK_DEV, etc. -- ═══════════════════════════════════════════════════════════════ -- ENVIRONMENT-LEVEL RESOURCES (unique per environment) -- For multi-env deployments, use suffixes that fit your use case -- Examples: _DEV, _STAGE, _PROD, _UAT, _SANDBOX, _TEAM1, etc. -- ═══════════════════════════════════════════════════════════════ SET SCHEMA_NAME='OFFLINE_STORE'; -- ⚠️ MUST be unique per env SET ROLE_NAME='CHALK_ROLE'; -- Recommended: unique per env SET USER_NAME='CHALK_USER'; -- Recommended: unique per env -- Create the database + schema + warehouse CREATE DATABASE IF NOT EXISTS IDENTIFIER($DB_NAME); USE DATABASE IDENTIFIER($DB_NAME); CREATE SCHEMA IF NOT EXISTS IDENTIFIER($SCHEMA_NAME); CREATE WAREHOUSE IF NOT EXISTS IDENTIFIER($WAREHOUSE_NAME) WITH WAREHOUSE_SIZE=$WAREHOUSE_SIZE; -- Create a role for Chalk and grant the minimum offline-store privileges CREATE ROLE IF NOT EXISTS IDENTIFIER($ROLE_NAME); SET QUALIFIED_SCHEMA_NAME=concat($DB_NAME, '.', $SCHEMA_NAME); GRANT USAGE ON WAREHOUSE IDENTIFIER($WAREHOUSE_NAME) TO ROLE IDENTIFIER($ROLE_NAME); GRANT USAGE ON DATABASE IDENTIFIER($DB_NAME) TO ROLE IDENTIFIER($ROLE_NAME); GRANT USAGE ON SCHEMA IDENTIFIER($QUALIFIED_SCHEMA_NAME) TO ROLE IDENTIFIER($ROLE_NAME); GRANT CREATE TABLE, CREATE FUNCTION ON SCHEMA IDENTIFIER($QUALIFIED_SCHEMA_NAME) TO ROLE IDENTIFIER($ROLE_NAME); -- Create a user for Chalk CREATE USER IF NOT EXISTS IDENTIFIER($USER_NAME) RSA_PUBLIC_KEY=`` DEFAULT_ROLE=IDENTIFIER($ROLE_NAME) DEFAULT_WAREHOUSE=IDENTIFIER($WAREHOUSE_NAME) DEFAULT_NAMESPACE=IDENTIFIER(concat($DB_NAME, '.', $SCHEMA_NAME)); -- Grant the Chalk role to the Chalk user GRANT ROLE IDENTIFIER($ROLE_NAME) TO USER IDENTIFIER($USER_NAME); ``` ### What to Share with Chalk Share the following securely via GPG encryption: - The USER_NAME and RSA private key - The WAREHOUSE_NAME, DB_NAME, SCHEMA_NAME, and ROLE_NAME - Your Snowflake account name (SELECT CURRENT_ACCOUNT_NAME();) - Your Snowflake organization name (SELECT CURRENT_ORGANIZATION_NAME();) ### Storage Integration Setup The storage integration creates a GCS service account with allowed storage locations, enabling Chalk to securely load and unload data from the offline store. ### Required Information Before setting up the storage integration, gather the following: - GCP Project ID: The GCP project where your Chalk environment is deployed - GCS Bucket Name: This is subject to custom naming, but may resemble chalk-{organization}-offline-store-bulk-insert (confirm with Chalk if different) - Snowflake Role Name: The role name created in Database Setup (e.g., CHALK_ROLE) Permissions required: - GCP: Permissions to create IAM custom roles and assign roles to service accounts - Snowflake: ACCOUNTADMIN role or equivalent permissions for CREATE INTEGRATION ### Step 1: Create Snowflake Storage Integration This creates the storage integration object in Snowflake that links to your GCS bucket. Follow the Snowflake GCS Storage Integration documentation and execute the following SQL commands (replace placeholders with actual values): ``` -- Create the storage integration pointing to your GCS bucket CREATE STORAGE INTEGRATION "gcs-integration-chalk-{organization}-offline-store-bulk-insert" TYPE = EXTERNAL_STAGE STORAGE_PROVIDER = 'GCS' ENABLED = true STORAGE_ALLOWED_LOCATIONS = ('gcs://chalk-{organization}-offline-store-bulk-insert/'); -- Grant usage permissions to the Chalk role GRANT USAGE ON INTEGRATION "gcs-integration-chalk-{organization}-offline-store-bulk-insert" TO ROLE "CHALK_ROLE"; -- Verify the integration was created successfully and retrieve the service account DESCRIBE INTEGRATION "gcs-integration-chalk-{organization}-offline-store-bulk-insert"; ``` From the DESCRIBE INTEGRATION output, record the STORAGE_GCP_SERVICE_ACCOUNT value. This is the service account that Snowflake will use to access your GCS bucket. Note: Snowflake creates a single service account that is referenced by all GCS storage integrations in your Snowflake account. The service account identifier will look like: service-account@.iam.gserviceaccount.com ### Step 2: Create Custom IAM Role in GCP This custom role will grant Snowflake the minimum permissions needed to read and write objects to your GCS bucket. Follow the Snowflake GCS IAM configuration documentation to create a custom IAM role with the following permissions: For data loading only: - storage.buckets.get - storage.objects.get - storage.objects.list For data loading and unloading (recommended): - storage.buckets.get - storage.objects.get - storage.objects.list - storage.objects.create - storage.objects.delete If using Cloud KMS encryption: Additionally grant the service account the "Cloud KMS CryptoKey Encryptor/Decryptor" role on your key ring. ### Create the Custom Role In your GCP Console or using gcloud: ``` # Create a custom role definition file: chalk-snowflake-gcs-role.yaml cat > chalk-snowflake-gcs-role.yaml < \ --file=chalk-snowflake-gcs-role.yaml ``` ### Step 3: Grant IAM Role to Service Account Assign the custom IAM role to the Snowflake service account at the bucket level. Follow the Snowflake IAM role assignment documentation to grant the role. ### Using GCP Console: - Navigate to Cloud Storage > Buckets - Select your bucket: chalk-{organization}-offline-store-bulk-insert - Go to the "Permissions" tab - Click "Grant Access" - Add the service account from Step 1: service-account@.iam.gserviceaccount.com - Select the custom role: ChalkSnowflakeGCSAccess - Save the changes ### Using gcloud: ``` # Grant the custom role to the Snowflake service account gsutil iam ch serviceAccount:@.iam.gserviceaccount.com:projects//roles/ChalkSnowflakeGCSAccess \ gs://chalk-{organization}-offline-store-bulk-insert ``` Important: The service account must have access to the bucket before Snowflake can load or unload data. ### Step 4: Test Storage Integration Before proceeding, validate that your storage integration is properly configured using Snowflake's built-in validation function. Run the following SQL command to test all operations (read, write, list, delete): ``` -- Test the storage integration SELECT SYSTEM$VALIDATE_STORAGE_INTEGRATION( 'gcs-integration-chalk-{organization}-offline-store-bulk-insert', 'gcs://chalk-{organization}-offline-store-bulk-insert/', 'validation_test.txt', 'all' ); ``` A successful result will return a JSON object with "status": "success" for each action. For more details on this validation function, see the Snowflake documentation. ### Step 5: Configure Domain Restrictions (if applicable) For organizations created after May 3, 2024, Snowflake enforces domain restrictions that may require additional configuration. If you encounter access issues related to domain restrictions: - Review your organization's domain restriction policy in GCP - Ensure the Snowflake service account is authorized - Contact Chalk support if you need assistance with domain configuration ### Step 6: Create External Stage (Optional) You can optionally create an external stage in Snowflake that references your storage integration for easier data access: ``` -- Create an external stage pointing to your GCS bucket CREATE STAGE IF NOT EXISTS CHALK_GCS_STAGE URL = 'gcs://chalk-{organization}-offline-store-bulk-insert/' STORAGE_INTEGRATION = "gcs-integration-chalk-{organization}-offline-store-bulk-insert"; -- Grant usage permissions to the Chalk role GRANT USAGE ON STAGE CHALK_GCS_STAGE TO ROLE "CHALK_ROLE"; -- List files in the stage to verify it works LIST @CHALK_GCS_STAGE; ``` ### Additional Environments If you have multiple Chalk environments (e.g., dev, staging, production) in the same cluster, follow these steps for each additional environment. ### What to Reuse (Cluster-Level) These resources are already created and must be shared across all environments: - GCS Bucket These resources can be shared or separated per environment (your choice): - Storage Integration — reuse the one created above, or create a separate integration per environment for stricter isolation - GCP IAM custom role and service account binding — a shared integration reuses the existing binding; a per-environment integration needs its own - Database — share one database, or create CHALK_DEV, CHALK_STAGE, CHALK_PROD - Warehouse — share one warehouse, or create separate warehouses per environment for independent scaling/billing ### What to Create New (Per Environment) For each additional environment, create: - New Schema (required—must be unique per environment) - New Role (recommended) - New User with new key pair (recommended) ### SQL for Additional Environment ``` -- ═══════════════════════════════════════════════════════════════ -- ADDITIONAL ENVIRONMENT SETUP -- Replace _STAGE with your environment suffix -- Use suffixes that fit your use case: _DEV, _PROD, _UAT, _SANDBOX, etc. -- ═══════════════════════════════════════════════════════════════ -- Database and Warehouse: share existing OR create new per environment -- Option A: Share (simpler) SET DB_NAME='CHALK'; -- Same database (shared) SET WAREHOUSE_NAME='CHALK_WAREHOUSE'; -- Same warehouse (shared) -- Option B: Separate (for independent scaling/billing) -- SET DB_NAME='CHALK_STAGE'; -- Separate database per env -- SET WAREHOUSE_NAME='CHALK_WH_STAGE'; -- Separate warehouse per env -- New environment-specific resources SET SCHEMA_NAME='OFFLINE_STORE_STAGE'; -- ⚠️ MUST be unique SET ROLE_NAME='CHALK_ROLE_STAGE'; SET USER_NAME='CHALK_USER_STAGE'; -- Create the new schema USE DATABASE IDENTIFIER($DB_NAME); CREATE SCHEMA IF NOT EXISTS IDENTIFIER($SCHEMA_NAME); -- Create a new role for this environment CREATE ROLE IF NOT EXISTS IDENTIFIER($ROLE_NAME); SET QUALIFIED_SCHEMA_NAME=concat($DB_NAME, '.', $SCHEMA_NAME); GRANT USAGE ON WAREHOUSE IDENTIFIER($WAREHOUSE_NAME) TO ROLE IDENTIFIER($ROLE_NAME); GRANT USAGE ON DATABASE IDENTIFIER($DB_NAME) TO ROLE IDENTIFIER($ROLE_NAME); GRANT USAGE ON SCHEMA IDENTIFIER($QUALIFIED_SCHEMA_NAME) TO ROLE IDENTIFIER($ROLE_NAME); GRANT CREATE TABLE, CREATE FUNCTION ON SCHEMA IDENTIFIER($QUALIFIED_SCHEMA_NAME) TO ROLE IDENTIFIER($ROLE_NAME); -- Create a new user for this environment CREATE USER IF NOT EXISTS IDENTIFIER($USER_NAME) RSA_PUBLIC_KEY=`` DEFAULT_ROLE=IDENTIFIER($ROLE_NAME) DEFAULT_WAREHOUSE=IDENTIFIER($WAREHOUSE_NAME) DEFAULT_NAMESPACE=IDENTIFIER(concat($DB_NAME, '.', $SCHEMA_NAME)); GRANT ROLE IDENTIFIER($ROLE_NAME) TO USER IDENTIFIER($USER_NAME); -- Grant access to a storage integration: reuse the shared one (shown), or create a separate -- integration for this environment following the Storage Integration Setup steps above GRANT USAGE ON INTEGRATION "gcs-integration-chalk-{organization}-offline-store-bulk-insert" TO ROLE IDENTIFIER($ROLE_NAME); ``` ### Example Multi-Environment Naming Must be unique per environment: | Resource | Dev | Stage | Prod | | -------- | ------------------- | --------------------- | -------------------- | | Schema | `OFFLINE_STORE_DEV` | `OFFLINE_STORE_STAGE` | `OFFLINE_STORE_PROD` | | Role | `CHALK_ROLE_DEV` | `CHALK_ROLE_STAGE` | `CHALK_ROLE_PROD` | | User | `CHALK_USER_DEV` | `CHALK_USER_STAGE` | `CHALK_USER_PROD` | Can be shared OR separated (your choice): | Resource | Shared Option | Separated Option | | -------------------- | ----------------------------------------------------------------------------- | -------------------------------------------------- | | Storage Integration | `gcs-integration-chalk-{organization}-offline-store-bulk-insert` for all envs | One integration per environment | | Database | `CHALK` for all envs | `CHALK_DEV`, `CHALK_STAGE`, `CHALK_PROD` | | Warehouse | `CHALK_WAREHOUSE` for all envs | `CHALK_WH_DEV`, `CHALK_WH_STAGE`, `CHALK_WH_PROD` | | GCP IAM Role/Binding | One binding backing a shared integration | A separate binding per per-environment integration | Must be shared (cluster-level): | Resource | All Environments | | ---------- | ------------------------------------------------ | | GCS Bucket | `chalk-{organization}-offline-store-bulk-insert` | ### Offline Store Connection Setup You can configure your offline store connection either through the dashboard under Integrations > Offline Store or programmatically via the Chalk CLI tool. To configure the connection programmatically via the Chalk CLI tool, there are the following prerequisites: - Chalk Platform version must be v3.31.22 or higher - Chalk CLI version must be v1.39.8 or higher - The Database Setup steps above must be completed. - There must be offline writers deployed (you can do so in the dashboard under Settings > Shared Resources). To verify that your Chalk deployment and Chalk CLI tool meet the version requirements, having run chalk login in your Chalk repository, you can run the following command with the CLI: ``` > chalk offline-store connection list ✓ Fetched offline store connections No offline store connections found. ``` If you run that command and see errors, it is likely that one of your platform or CLI version is below the requisite version. ### Configuration Steps ### 1. Create the offline store connection There are two modes for the create command below--guided prompts and YAML. If you run the command below without any flags, this will guide you through guided prompts. ``` > chalk offline-store connection create ``` To pass in a YAML file with the configuration, you can instead use the -f flag. ``` > chalk offline-store connection create -f path/to/your/config.yaml ``` For authentication, you can choose to pass in either password or private_key. Private key is required for all Snowflake accounts with MFA. ``` name: A_NAME config: snowflake: credentials: account: gukdghb-chalk_aws_us_east_1 username: A_USER database: A_DB schema: A_SCHEMA warehouse: A_WH role: A_ROLE private_key: | -----BEGIN PRIVATE KEY----- ... -----END PRIVATE KEY----- ``` This command will store the configuration details. ### 2. List connections to get the connection ID Run the following command, and copy the connection ID for the connection you just created. You will need this for the next step. ``` > chalk offline-store connection list ``` ### 3. Activate the offline store connection Using the ID from the previous step, run the following command to run schema migrations against the offline store. ``` chalk offline-store connection activate ``` This command will kick off the schema migrations asynchronously. These migrations can also be triggered manually using the migrate command. Migrations may take up to a few minutes to run. This step will also set a Cloud Secret ID that will get picked up by engines and writers. If feature_store_secret was previously set in the environment, it must be unset for the connection to be activated successfully. ### 4. Verify the connection is active After a few minutes, you can verify that the feature_store_secret has been set by running the following command: ``` chalk environment config | grep -o 'feature_store_secret:"[^"]*"' | cut -d'"' -f2 ``` ### 5. Redeploy environment After the feature_store_secret has been set, you can redeploy your environment (in the Chalk Dashboard, in the Deployments tab, click Redeploy on the latest active deployment). Once pods roll, the offline store connection will be active and your environment will be able to read/write from the offline store. ### Troubleshooting and Notes - There’s a deactivate command if you need to unset the active connection. - Note that the storage integration settings for Snowflake in the CLI have no effect today. Use the offline writer configs set in the dashboard under Settings > Shared Resources. ### Additional Considerations ### Lifecycle Management Configure GCS lifecycle rules to automatically clean up incomplete multipart uploads: ``` # Create a lifecycle rule to delete incomplete uploads after 7 days gsutil lifecycle set lifecycle-config.json gs://chalk-{organization}-offline-store-bulk-insert ``` Example lifecycle-config.json: ``` { "lifecycle": { "rule": [ { "action": {"type": "Delete"}, "condition": { "age": 7, "matchesPrefix": [""] } } ] } } ``` ### Monitoring and Auditing Enable Cloud Audit Logs for your GCS bucket to monitor Snowflake's access: - Go to IAM & Admin > Audit Logs - Enable "Admin Read", "Data Read", and "Data Write" logs for Cloud Storage - Review logs in Cloud Logging to track access patterns ### Performance Optimization - Use appropriate warehouse sizes based on your data volume - Consider using clustering keys for frequently queried tables - Monitor warehouse usage and adjust AUTO_SUSPEND and AUTO_RESUME settings - Use folder structures in GCS with trailing slashes in stage URLs for better organization