> ## Documentation Index
> Fetch the complete documentation index at: https://docs.formal.ai/llms.txt
> Use this file to discover all available pages before exploring further.

# Snowflake

> How to connect to a Snowflake Resource using the Formal Connector

## Requirements

To use the Formal Connector with Snowflake, you must have access to your Snowflake account and the necessary permissions to manage resources within it.

## Configuration

* **Snowflake account URL**:  Your Snowflake account URL, typically in the format `X.Y.snowflakecomputing.com`. Refer to the displayed image for clarity.

<img src="https://mintcdn.com/formal/fJsHlx-X_b_ATHwb/assets/images/snowflake_hostname.png?fit=max&auto=format&n=fJsHlx-X_b_ATHwb&q=85&s=b2916f0f8e11f445771daef8fc6ea6c2" width="825" height="536" data-path="assets/images/snowflake_hostname.png" />

* **Snowflake username and password**: These credentials will be used by Formal users to connect to your Snowflake account.

## Native Users

Native users define the Snowflake credentials the connector uses to authenticate with the upstream Snowflake account on behalf of Formal users. Snowflake supports two authentication methods, and both are configured through the **Password** native user type in Formal. Formal automatically detects which method to use based on the format of the secret you provide.

### Password Authentication

To use password-based authentication, create a native user with the **Password** type and set the secret to the Snowflake user's password.

```hcl theme={null}
resource "formal_native_user" "snowflake_password" {
  resource_id        = formal_resource.snowflake.id
  native_user_id     = "SNOWFLAKE_USERNAME"
  native_user_secret = var.snowflake_password
  use_as_default     = true
}
```

### Key-Pair Authentication

To use key-pair authentication (`SNOWFLAKE_JWT`), create a native user with the **Password** type and set the secret to the PEM-encoded private key. Formal detects the PEM format and automatically uses JWT authentication when connecting to Snowflake.

```hcl theme={null}
resource "formal_native_user" "snowflake_keypair" {
  resource_id        = formal_resource.snowflake.id
  native_user_id     = "SNOWFLAKE_USERNAME"
  native_user_secret = var.snowflake_private_key_pem
  use_as_default     = true
}
```

<Note>
  The client authenticator method must match the native user method. If the native user is configured with a PEM key (key-pair auth), Formal clients must also use `SNOWFLAKE_JWT`. If the native user uses a password, clients should use password authentication.
</Note>

## TLS Configuration

TLS must be enabled for secure communication. You have two options:

1. **Formal-Managed TLS**: Formal will manage the TLS Certificate for you, providing you with a domain of the format `.YOUR_ORG_NAME.connectors.joinformal.com`.
2. **Self-Managed TLS**: If you prefer to manage the TLS certificate yourself, follow these steps:
   * Obtain a TLS wildcard certificate.
   * Add CNAME records for `account.hostname`, `s3.hostname`, `backend.hostname`, and `app.hostname` to your DNS.
   * Provide the TLS certificate through environment variables:
   ```
   CUSTOMER_TLS_CERT_PRIVATE_KEY: ""
   CUSTOMER_TLS_CERT_FULLCHAIN: ""
   ```

<Note> When Formal manages the TLS certificate, the Network Load Balancer (NLB) must be internet-facing. </Note>

## Connecting to Snowflake

With the above configuration complete, you can now connect to Snowflake using the specified hostname and Formal credentials.

### Connection Parameters

When configuring a Snowflake client to connect through the Formal Connector, note the following:

* **Host**: Must use the `account.` subdomain prefix on your Formal connector hostname. For example, if your connector hostname is `my-org.connectors.joinformal.com`, the host should be `account.my-org.connectors.joinformal.com`. Use the literal value "account" instead of passing the account identifier.
* **Account**: Set this to be your [Snowflake account identifier](https://docs.snowflake.com/en/user-guide/admin-account-identifier). This is the first segment of your Snowflake account URL (`<account id>.snowflakecomputing.com`).
* **Port**: The port your connector listener is configured on (e.g., `443` or `1443`).
* **User**: Your Formal identity (e.g., `idp:formal:human:you@yourcompany.com` or `idp:formal:machine:my-service`).
* **Authenticator**: Must match the authenticator method configured on the native user in Formal. If the native user uses key-pair authentication (`SNOWFLAKE_JWT`), your Formal client must also use `SNOWFLAKE_JWT`. If the native user uses password authentication, your client should use password authentication.

### Connecting via the Snowflake CLI (`snow`)

Add a connection to `~/.snowflake/config.toml`:

#### Password Authentication

```toml theme={null}
[connections.my-connection]
account = "<snowflake-account-id>"
host = "account.<connector-hostname>"
port = <connector-port>
user = "idp:formal:human:you@yourcompany.com"
password = "<your-formal-token>"
```

#### Key-Pair Authentication (SNOWFLAKE\_JWT)

```toml theme={null}
[connections.my-connection]
account = "<snowflake-account-id>"
host = "account.<connector-hostname>"
port = <connector-port>
user = "idp:formal:machine:my-service"
authenticator = "SNOWFLAKE_JWT"
private_key_file = "/path/to/private_key.pem"
```

When using `SNOWFLAKE_JWT`, the private key file should be the PEM file available via the **Download Private Key** button on the machine user details page in the Formal console.

Test the connection:

```bash theme={null}
snow connection test -c my-connection
```

Use `--debug` for verbose logging if troubleshooting:

```bash theme={null}
snow connection test -c my-connection --debug
```

## Connecting to Snowflake via Retool

1. **Configure the Connector for Snowflake**: Ensure your Formal Connector is correctly set up to proxy Snowflake connections.
2. **Update Retool Resource**: Retool requires an account identifier to connect to Snowflake. However, to use Formal with Snowflake, you need to bypass this limitation by ensuring Retool supports custom connection options similar to PostgreSQL resources. This allows for more flexible connections, including the ability to specify the full HTTP URL instead of just the account identifier.
3. **Custom Snowflake Hostname**: For a successful connection, it's crucial to set a custom Snowflake hostname that points to the Formal Connector. This is necessary because Retool's default configuration might not support the direct use of custom domains for Snowflake connections.
4. **Connection String**: Use the following JDBC connection string format for connecting to Snowflake through Formal:

```
jdbc:snowflake://account.test-snowflake-proxy.prod.com:443/?user=idp:formal:machine:prd-retool&password=[pwd]&db=analytics&options=formal_enduser={{ current_user.email }}
```

Replace \[pwd] with your actual password. This format ensures that the connection is routed through the Formal Connector, leveraging Formal's security and policy enforcement features.

## End-User Identity Propagation

When a machine user (e.g., a BI tool) connects to Snowflake through Formal, you can propagate the actual end-user's identity so that policies evaluate against the human user, not the shared machine credential. Formal supports two mechanisms for this, depending on the BI tool.

### Omni

Omni propagates end-user identity at **session login time** via the `QUERY_TAG` session parameter. When an Omni user runs a query, Omni includes a JSON-encoded `QUERY_TAG` in the Snowflake login request containing the `omniOrganizationUserId` field. The Formal Connector extracts this ID and resolves it to a Formal user via external ID mappings.

To set this up:

<Steps>
  <Step title="Create a BI Integration">
    In the Formal console, go to [BI Integrations](https://app.formal.ai/bi-integrations) and create a new **Omni** integration.
  </Step>

  <Step title="Map External IDs">
    For each Omni user, add an external ID to their corresponding Formal user. The external ID value should match the user's `omniOrganizationUserId` in Omni, and the app type should be set to the Omni integration you created.
  </Step>

  <Step title="Verify">
    Run a query through Omni and check [Logs](https://app.formal.ai/logs) to confirm the end-user is correctly attributed.
  </Step>
</Steps>

<Note>
  Omni resolves end-user identity once per session at login time. All queries within that session are attributed to the same end-user.
</Note>

### Other BI Tools (Metabase, Sigma, Hex, Custom)

Other BI tools propagate end-user identity **per query** via SQL comments. These tools append a comment to each SQL statement containing the end-user's external ID, which the Connector parses on every request.

For example, a query from Metabase might look like:

```sql theme={null}
SELECT * FROM orders /* {"formal_external_user_id": "12345"} */
```

The Connector extracts the external ID from the comment and resolves it to a Formal user. This means different queries within the same session can be attributed to different end-users.

For setup instructions, see the [BI Integrations guide](/docs/guides/integrations/bi).

### Looker (FORMAL\_ENDUSER)

Looker uses a different mechanism: the `FORMAL_ENDUSER` session parameter passed via JDBC connection options. This resolves the end-user by email address rather than external ID. See the [Looker setup instructions](/docs/guides/integrations/bi#looker) for details.

## Policy Evaluation

Formal supports the following policy evaluation stages for Snowflake:

* **Session**: Evaluate and enforce policies at connection time
* **Request**: Evaluate and enforce policies before query execution
* **Response**: Evaluate and enforce policies after data retrieval

## Troubleshooting

### Common Issues and Solutions

* **"403: Host forbidden"**: The connector uses subdomain-based routing. Ensure the `host` in your connection config uses the `account.` prefix (e.g., `account.my-org.connectors.joinformal.com`), not the bare connector hostname.
* **Error code `390102` (Incorrect username or password)**: The connector successfully proxied to Snowflake, but the native user credentials were rejected. Verify that the native user exists in Snowflake and that the password or private key is correct. If using `ENV:` for the native user secret, check the connector logs for `ENV variable ... not found` errors.
* **Authenticator mismatch**: If the native user in Formal is configured with key-pair auth, your client must also use `SNOWFLAKE_JWT` as the authenticator. A mismatch will cause authentication failures.
* Ensure TLS certificates are correctly set up, and DNS records are pointing to the NLB.
* Verify JDBC driver compatibility, especially if using self-signed certificates with Formal.