# Snowflake
source: https://docs.chalk.ai/docs/snowflake

## Integrate with 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.

Setting up data unloading is mandatory to enable the native Snowflake driver.

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

- 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.
- An external stage — a named Snowflake object that points at a specific location in
your bucket and writes through the storage integration.
- 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:

| Snowflake value or expression your query may return       | Chalk feature type                                                     | Notes                                                                                                                                                               |
| --------------------------------------------------------- | ---------------------------------------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| Numeric columns and integer literals                      | Declared numeric feature type, such as `int`, `float`, or `pa.int64()` | Snowflake `NUMBER` unloads as a decimal value, and Chalk casts it to the declared feature type. Add explicit SQL casts for precision- or overflow-sensitive values. |
| Boolean, string, binary, date, and time columns           | `bool`, `str`, `bytes`, `date`, and `time`                             | These simple scalar types round-trip to their expected feature equivalents.                                                                                         |
| `TIMESTAMP_LTZ` and `TIMESTAMP_TZ`                        | `datetime`                                                             | Chalk normalizes these timestamps to their UTC instant. Snowflake Parquet unload limits timestamp precision to milliseconds.                                        |
| `TIMESTAMP_NTZ`                                           | `datetime`                                                             | This type has no timezone, so Chalk treats the wall-clock timestamp as UTC. Snowflake Parquet unload limits timestamp precision to milliseconds.                    |
| Semi-structured values (for example, `ARRAY` or `OBJECT`) | `str`                                                                  | Chalk converts the value to JSON text such as `{"key":"value","num":42}` for unloading; you do not need to call `TO_JSON(...)` in your query.                       |
| Homogeneous arrays                                        | `List[T]`                                                              | Examples: `ARRAY` maps to `List[float]`; `ARRAY` maps to `List[str]`.                                                                                               |
| Object/map-like values with consistent value types        | `Dict[str, T]`                                                         | Values must have a consistent type.                                                                                                                                 |
| `VARIANT` scalar with an explicit SQL cast                | The scalar feature type from the cast                                  | Examples: `variant_col::DOUBLE AS score` or `variant_col::STRING AS label`.                                                                                         |
| `ST_ASTEXT(...)` over `GEOGRAPHY` or `GEOMETRY`           | `str`                                                                  | Convert geospatial values to text before returning them.                                                                                                            |

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.

Snowflake Data Dource form in the Chalk dashboard

### 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
```