Snowflake SQLAlchemy

A comprehensive SQLAlchemy dialect that enables developers to connect and interact with Snowflake databases using SQLAlchemy's Object-Relational Mapping (ORM) and Core APIs. It serves as a bridge between SQLAlchemy applications and Snowflake's cloud data warehouse, offering native support for Snowflake-specific features like VARIANT/ARRAY/OBJECT data types, clustering, merge operations, key-pair authentication, and advanced table types.

Package Information

• Package Name: snowflake-sqlalchemy
• Package Type: pypi
• Language: Python
• Installation: pip install snowflake-sqlalchemy

Core Imports

import snowflake.sqlalchemy
from snowflake.sqlalchemy import VARIANT, ARRAY, OBJECT
from snowflake.sqlalchemy import HybridTable, DynamicTable, IcebergTable
from snowflake.sqlalchemy import MergeInto, CopyIntoStorage

Basic Usage

from sqlalchemy import create_engine, Column, Integer, String, MetaData, Table
from snowflake.sqlalchemy import VARIANT, TEXT

# Create connection to Snowflake
engine = create_engine(
    'snowflake://user:password@account_identifier/database/schema',
    echo=True
)

# Define table with Snowflake-specific types
metadata = MetaData()
user_table = Table(
    'users',
    metadata,
    Column('id', Integer, primary_key=True),
    Column('name', TEXT()),
    Column('profile', VARIANT)
)

# Create table
metadata.create_all(engine)

# Insert data with semi-structured content
with engine.connect() as conn:
    conn.execute(
        user_table.insert(),
        {'id': 1, 'name': 'John', 'profile': '{"age": 30, "city": "NYC"}'}
    )

Architecture

The Snowflake SQLAlchemy dialect extends SQLAlchemy's type system and operations to support Snowflake's unique features:

• Dialect Integration: Seamless SQLAlchemy dialect registration (snowflake:// connection strings)
• Type System: Complete support for standard SQL types plus Snowflake-specific semi-structured types
• Table Types: Advanced table types including Hybrid, Dynamic, and Iceberg tables
• Data Operations: Specialized commands for MERGE, COPY INTO, and cloud storage integration
• Cloud Storage: Native AWS S3 and Azure Blob Storage connectivity with encryption support

Capabilities

Data Types

Comprehensive type system supporting both standard SQL types and Snowflake's semi-structured data types including VARIANT, ARRAY, OBJECT, and MAP with full type safety.

# Standard SQL types (re-exported from SQLAlchemy)
from snowflake.sqlalchemy import (
    BIGINT, BINARY, BOOLEAN, CHAR, DATE, DATETIME, DECIMAL, 
    FLOAT, INTEGER, REAL, SMALLINT, TIME, TIMESTAMP, VARCHAR
)

# Snowflake-specific types
from snowflake.sqlalchemy import (
    VARIANT, ARRAY, OBJECT, MAP,
    TIMESTAMP_TZ, TIMESTAMP_LTZ, TIMESTAMP_NTZ,
    GEOGRAPHY, GEOMETRY
)

Data Types

Table Types

Advanced table types for modern data architectures including hybrid OLTP tables, dynamic tables for streaming analytics, and Iceberg tables for data lake integration.

from snowflake.sqlalchemy import (
    SnowflakeTable, HybridTable, DynamicTable, IcebergTable
)

Table Types

Data Operations

Specialized SQL operations including MERGE statements for upserts and COPY INTO commands for bulk data loading from cloud storage with comprehensive formatting options.

from snowflake.sqlalchemy import (
    MergeInto, CopyIntoStorage,
    CSVFormatter, JSONFormatter, PARQUETFormatter
)

Data Operations

Cloud Storage Integration

Native integration with AWS S3 and Azure Blob Storage including credential management, encryption support, and stage management for external data access.

from snowflake.sqlalchemy import (
    AWSBucket, AzureContainer, ExternalStage,
    CreateStage, CreateFileFormat
)

Cloud Storage

Connection and Configuration

Connection management, URL building utilities, and configuration options for optimizing Snowflake database connectivity within SQLAlchemy applications.

from snowflake.sqlalchemy import URL
from snowflake.sqlalchemy.util import (
    parse_url_boolean, parse_url_integer
)

Connection and Configuration