0
# Snowflake SQLAlchemy
1

2
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.
3

4
## Package Information
5

6
- **Package Name**: snowflake-sqlalchemy
7
- **Package Type**: pypi
8
- **Language**: Python
9
- **Installation**: `pip install snowflake-sqlalchemy`
10

11
## Core Imports
12

13
```python
14
import snowflake.sqlalchemy
15
from snowflake.sqlalchemy import VARIANT, ARRAY, OBJECT
16
from snowflake.sqlalchemy import HybridTable, DynamicTable, IcebergTable
17
from snowflake.sqlalchemy import MergeInto, CopyIntoStorage
18
```
19

20
## Basic Usage
21

22
```python
23
from sqlalchemy import create_engine, Column, Integer, String, MetaData, Table
24
from snowflake.sqlalchemy import VARIANT, TEXT
25

26
# Create connection to Snowflake
27
engine = create_engine(
28
    'snowflake://user:password@account_identifier/database/schema',
29
    echo=True
30
)
31

32
# Define table with Snowflake-specific types
33
metadata = MetaData()
34
user_table = Table(
35
    'users',
36
    metadata,
37
    Column('id', Integer, primary_key=True),
38
    Column('name', TEXT()),
39
    Column('profile', VARIANT)
40
)
41

42
# Create table
43
metadata.create_all(engine)
44

45
# Insert data with semi-structured content
46
with engine.connect() as conn:
47
    conn.execute(
48
        user_table.insert(),
49
        {'id': 1, 'name': 'John', 'profile': '{"age": 30, "city": "NYC"}'}
50
    )
51
```
52

53
## Architecture
54

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

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

63
## Capabilities
64

65
### Data Types
66

67
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.
68

69
```python { .api }
70
# Standard SQL types (re-exported from SQLAlchemy)
71
from snowflake.sqlalchemy import (
72
    BIGINT, BINARY, BOOLEAN, CHAR, DATE, DATETIME, DECIMAL, 
73
    FLOAT, INTEGER, REAL, SMALLINT, TIME, TIMESTAMP, VARCHAR
74
)
75

76
# Snowflake-specific types
77
from snowflake.sqlalchemy import (
78
    VARIANT, ARRAY, OBJECT, MAP,
79
    TIMESTAMP_TZ, TIMESTAMP_LTZ, TIMESTAMP_NTZ,
80
    GEOGRAPHY, GEOMETRY
81
)
82
```
83

84
[Data Types](./data-types.md)
85

86
### Table Types
87

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

90
```python { .api }
91
from snowflake.sqlalchemy import (
92
    SnowflakeTable, HybridTable, DynamicTable, IcebergTable
93
)
94
```
95

96
[Table Types](./table-types.md)
97

98
### Data Operations
99

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

102
```python { .api }
103
from snowflake.sqlalchemy import (
104
    MergeInto, CopyIntoStorage,
105
    CSVFormatter, JSONFormatter, PARQUETFormatter
106
)
107
```
108

109
[Data Operations](./data-operations.md)
110

111
### Cloud Storage Integration
112

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

115
```python { .api }
116
from snowflake.sqlalchemy import (
117
    AWSBucket, AzureContainer, ExternalStage,
118
    CreateStage, CreateFileFormat
119
)
120
```
121

122
[Cloud Storage](./cloud-storage.md)
123

124
### Connection and Configuration
125

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

128
```python { .api }
129
from snowflake.sqlalchemy import URL
130
from snowflake.sqlalchemy.util import (
131
    parse_url_boolean, parse_url_integer
132
)
133
```
134

135
[Connection and Configuration](./connection-config.md)