Snowflake

Local/Built-in Scheduler

Engine Adapter Type: snowflake

Installation

pip install "sqlmesh[snowflake]"

Connection options

Option	Description	Type	Required
type	Engine type name - must be snowflake	string	Y
user	The Snowflake username	string	N
password	The Snowflake password	string	N
authenticator	The Snowflake authenticator method	string	N
account	The Snowflake account name	string	Y
warehouse	The Snowflake warehouse name	string	N
database	The Snowflake database name	string	N
role	The Snowflake role name	string	N
private_key	Local path to the private key file	string	N

Snowflake SSO Authorization

SQLMesh supports Snowflake SSO authorization connections using the externalbrowser authenticator method. For example:

gateways:
    snowflake:
        connection:
            type: snowflake
            account: ************
            user: ************
            authenticator: externalbrowser
            warehouse: ************
            database: ************
            role: ************

Snowflake Private Key Authorization

SQLMesh supports Snowflake private key authorization connections by providing the private key bytes. Only config.py is supported when using private key authorization. account and user are required. For example:

from sqlmesh.core.config import (
    Config,
    GatewayConfig,
    ModelDefaultsConfig,
    SnowflakeConnectionConfig,
)

from cryptography.hazmat.primitives import serialization

key = """-----BEGIN PRIVATE KEY-----
...
-----END PRIVATE KEY-----""".encode()

p_key= serialization.load_pem_private_key(key, password=None)

pkb = p_key.private_bytes(
    encoding=serialization.Encoding.DER,
    format=serialization.PrivateFormat.PKCS8,
    encryption_algorithm=serialization.NoEncryption(),
)

config = Config(
    model_defaults=ModelDefaultsConfig(dialect="snowflake"),
    gateways={
       "my_gateway": GatewayConfig(
            connection=SnowflakeConnectionConfig(
                user="user",
                account="account",
                private_key=pkb,
            ),
        ),
    }
)

The authenticator method is assumed to be snowflake_jwt when private_key is provided, but it can also be explicitly provided in the connection configuration.

Airflow Scheduler

Engine Name: snowflake

The SQLMesh Snowflake Operator is similar to the SnowflakeOperator, and relies on the same SnowflakeHook implementation.

To enable support for this operator, the Airflow Snowflake provider package should be installed on the target Airflow cluster along with SQLMesh with the Snowflake extra:

pip install "apache-airflow-providers-snowflake[common.sql]"
pip install "sqlmesh[snowflake]"

The operator requires an Airflow connection to determine the target Snowflake account. Refer to Snowflake connection for more details.

By default, the connection ID is set to snowflake_default, but can be overridden using the engine_operator_args parameter to the SQLMeshAirflow instance as in the example below:

sqlmesh_airflow = SQLMeshAirflow(
    "snowflake",
    engine_operator_args={
        "snowflake_conn_id": "<Connection ID>"
    },
)