Snowflake

Chalk supports Snowflake as a SQL source.

​

Prerequisites

If Chalk has helped you configure a Snowflake Offline Store already, then some of these commands may overlap with work that is already done. Please review your Snowflake configuration for existing objects matching the ones below before proceeding.

For detailed information about Snowflake storage integrations and external stages, refer to:

• CREATE STORAGE INTEGRATION - Configure IAM-based authentication for S3 access
• CREATE STAGE - Create external stages using storage integrations

​

Permissions

To use Snowflake as a Chalk datasource, the user or role specified in your integration must have the following permissions:

• USAGE on the warehouse, database, schema, and external stage
• SELECT on all tables and views being queried
• CREATE TEMPORARY TABLE in the schema
• DROP on temporary tables in the schema

Chalk creates temporary tables during feature computation to push down filters to optimize your query loads. In addition, Chalk uses the stages to optimize data unloading.

​

RBAC Setup

Run against your Snowflake instance as a user with the ACCOUNTADMIN role (or a role with the privileges to create roles and grant the permissions below) to set up a role with the appropriate permissions:

-- Set variables (example values shown; customize for your environment)
SET ROLE_NAME='CHALK_ROLE';
SET WAREHOUSE_NAME='<warehouse_name>';
SET DB_NAME='<database_name>';
SET SCHEMA_NAME='<schema_name>';
SET USER_NAME='<chalk_user>';

-- Create a role for Chalk (CREATE supports IDENTIFIER($var))
CREATE ROLE IF NOT EXISTS IDENTIFIER($ROLE_NAME);

-- Grant warehouse permissions
GRANT USAGE ON WAREHOUSE <WAREHOUSE_NAME> TO ROLE <ROLE_NAME>;

-- Grant database/schema permissions
GRANT USAGE ON DATABASE <DB_NAME> TO ROLE <ROLE_NAME>;
GRANT USAGE ON SCHEMA <DB_NAME>.<SCHEMA_NAME> TO ROLE <ROLE_NAME>;
GRANT SELECT ON ALL TABLES IN SCHEMA <DB_NAME>.<SCHEMA_NAME> TO ROLE <ROLE_NAME>;

-- Grant temporary table permissions
GRANT CREATE TEMPORARY TABLE ON SCHEMA <DB_NAME>.<SCHEMA_NAME> TO ROLE <ROLE_NAME>;

-- Assign role to user
GRANT ROLE <ROLE_NAME> TO USER <USER_NAME>;

​

Data Unloading

When Chalk runs a large query against Snowflake, pulling the result set back over the standard query connection can be slow. Instead, Chalk can ask Snowflake to unload the result directly into your cloud storage bucket (S3 or GCS) and then read those files in parallel. This is significantly faster for large feature computations.

To enable this, you set up three things, all of which are located in your cloud account and Snowflake account:

1. A storage integration — a Snowflake object that holds the cloud identity Snowflake uses to write to your bucket. Using a storage integration with IAM authentication is the recommended approach because it avoids managing long-lived credentials.
2. An external stage — a named Snowflake object that points at a specific location in your bucket and writes through the storage integration.
3. A trust relationship on the cloud side (an AWS IAM role or a GCS service-account grant) that allows Snowflake’s identity to write objects into your bucket.

Once these exist, configure the unload destination from the Data unload tab on the Snowflake data source page. Set the Unload target, then provide the matching values. Chalk will use this configuration to transfer large result sets efficiently, improving performance for large feature computations.

Chalk connects with the credentials configured on the data source and offers what that connection can see, so the fields can be filled by selection rather than from memory:

• Named stage target: the Stage name dropdown lists the stages the role can see. Choosing one fills in Data unload location and Storage integration from the stage’s own definition, because Chalk unloads through the stage but reads the files back from the cloud location behind it — the two have to name the same objects. Stages outside the data source’s database and schema are offered fully qualified as DATABASE.SCHEMA.STAGE.
• URL target: Location takes a complete cloud URI (s3://, gcs://, azure://), and the dropdown offers the STORAGE_ALLOWED_LOCATIONS of each storage integration, since Snowflake refuses to write anywhere else.

Stage privileges and storage-integration privileges are separate grants, so either list can come back empty — most often because the role lacks USAGE on the integrations. Every field still accepts a hand-typed value, so you can configure a destination before the grants are in place. A stage is bound to a single storage location and integration, so one stage cannot serve both S3 and GCS. The examples below therefore use provider-specific stage names (CHALK_UNLOAD_STAGE_S3 and CHALK_UNLOAD_STAGE_GCS); if you only use one cloud, name the stage however you like. Choose the stage that matches the cloud storage location you want the Snowflake data source to unload to.

​

Unloaded Result Types

Snowflake unloads query results as Parquet files. The first column below lists the Snowflake values and expressions that your query may return directly. Chalk applies any transformations required for Parquet unloading, then casts each output column to the feature type declared in your resolver or feature class. The type mapping works as follows:

The mappings above reflect the type conversions Chalk configures to work around Snowflake’s Parquet unload restrictions where possible. For the underlying restrictions and additional limitations, see Snowflake’s data unloading considerations.

​

Session Parameters

When Chalk uses the native Snowflake driver, it configures the Snowflake session instead of depending on the account or user’s default session parameters. Chalk sets the following values on its persistent Snowflake session:

ALTER SESSION SET TIMESTAMP_TYPE_MAPPING = TIMESTAMP_NTZ;
ALTER SESSION SET QUOTED_IDENTIFIERS_IGNORE_CASE = FALSE;

Chalk sets QUOTED_IDENTIFIERS_IGNORE_CASE when it initializes the connection and reasserts both values immediately before an unloaded query’s COPY INTO statement. TIMESTAMP_TYPE_MAPPING = TIMESTAMP_NTZ keeps expressions that use the bare TIMESTAMP alias compatible with Snowflake’s Parquet unload restrictions. QUOTED_IDENTIFIERS_IGNORE_CASE = FALSE preserves the exact case of quoted output aliases so that unloaded columns continue to match the feature schema.

These session-level values take precedence over the account and user defaults inherited by the Chalk connection, regardless of the role selected for the session. They affect only Chalk’s Snowflake session; you do not need to change the defaults used by other Snowflake clients.

​

Cloud Storage Permissions

Chalk uses Snowflake external stages, which require GRANT USAGE ON STAGE. READ and WRITE stage privileges apply to internal stages, not external stages. To let Chalk read and clean up unloaded files, configure cloud IAM on the stage’s underlying S3 or GCS bucket or prefix for each identity that touches the unload files:

• The Snowflake storage integration identity writes the unloaded Parquet files into the stage location, and needs cloud permissions to create, list/read, and delete objects in that location.
• The Chalk engine service account reads the unloaded Parquet files from the same location and performs best-effort cleanup of per-query unload directories. This service account is visible on the Chalk Connections page. Grant it read and write permissions to the S3 or GCS location that backs the external stage.

​

Provider-specific unloading setup

Set up the storage integration and external stage for the cloud storage location your Snowflake data source should unload to:

• Setup unloading with AWS
• Setup unloading with GCS

​

Configure Chalk environment

You can configure the Snowflake-specific options using the SnowflakeSource.__init__ args. Alternately, you can configure the source through your dashboard.

​

Dashboard

Once the storage integration and stage exist (for either AWS or GCS), in Chalk dashboard proceed to Integrations > Data Sources > Add a data source, select Snowflake, add and configure a new Snowflake datasource.

​

Data Unloading

If you configure data unloading, open the Data unload tab on the Snowflake data source page and set the Unload target. For URL-based unloading, provide the configured Location, Integration name, and Data unload location values.

​

SDK Setup

If you configured your Snowflake integration in the dashboard, define your SnowflakeSource data sources in Python referencing the configured names:

from chalk.sql import SnowflakeSource

risk = SnowflakeSource(name="RISK")
marketing = SnowflakeSource(name="MARKETING")

Then reference them in SQL file resolvers using the name parameter. For example, to query from the RISK source:

-- type: online
-- resolves: User
-- source: RISK
SELECT id, credit_score FROM users

And to query from the MARKETING source:

-- type: online
-- resolves: User
-- source: MARKETING
SELECT id, email, campaign_status FROM users