0
# Table Types
1

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

4
## Capabilities
5

6
### Standard Snowflake Table
7

8
Basic Snowflake table with clustering support and standard features.
9

10
```python { .api }
11
from snowflake.sqlalchemy import SnowflakeTable
12

13
class SnowflakeTable(Table):
14
    """Standard Snowflake table with clustering support."""
15
    
16
    def __init__(self, *args, **kwargs):
17
        """Create standard Snowflake table."""
18
```
19

20
### Hybrid Table
21

22
Snowflake hybrid table optimized for OLTP workloads with primary key enforcement.
23

24
```python { .api }
25
from snowflake.sqlalchemy import HybridTable
26

27
class HybridTable(SnowflakeTable):
28
    """Hybrid table for OLTP workloads with primary key enforcement."""
29
    
30
    _enforce_primary_keys = True
31
    _support_structured_types = True
32
    
33
    def __init__(self, *args, **kwargs):
34
        """Create hybrid table with OLTP optimizations."""
35
```
36

37
### Dynamic Table
38

39
Snowflake dynamic table for streaming and incremental data processing.
40

41
```python { .api }
42
from snowflake.sqlalchemy import DynamicTable
43
from snowflake.sqlalchemy.sql.custom_schema.options import (
44
    IdentifierOption, TargetLagOption
45
)
46

47
class DynamicTable(SnowflakeTable):
48
    """Dynamic table for streaming analytics and incremental processing."""
49
    
50
    def __init__(self, warehouse, target_lag, refresh_mode=None, *args, **kwargs):
51
        """
52
        Create dynamic table.
53
        
54
        Args:
55
            warehouse: Warehouse identifier for compute
56
            target_lag: Target lag for refresh frequency
57
            refresh_mode: Refresh mode (AUTO, FULL, INCREMENTAL)
58
        """
59
    
60
    @property
61
    def warehouse(self) -> IdentifierOption:
62
        """Get warehouse identifier option."""
63
    
64
    @property
65
    def target_lag(self) -> TargetLagOption:
66
        """Get target lag option."""
67
```
68

69
### Iceberg Table
70

71
Snowflake Iceberg table for data lake integration with external catalogs and storage.
72

73
```python { .api }
74
from snowflake.sqlalchemy import IcebergTable
75
from snowflake.sqlalchemy.sql.custom_schema.options import LiteralOption
76

77
class IcebergTable(SnowflakeTable):
78
    """Iceberg table for data lake integration."""
79
    
80
    _support_structured_types = True
81
    
82
    def __init__(self, *args, **kwargs):
83
        """Create Iceberg table with data lake capabilities."""
84
    
85
    @property
86
    def external_volume(self) -> LiteralOption:
87
        """Get external volume option."""
88
    
89
    @property
90
    def base_location(self) -> LiteralOption:
91
        """Get base location option."""
92
    
93
    @property
94
    def catalog(self) -> LiteralOption:
95
        """Get catalog option."""
96
```
97

98
## Table Options
99

100
Configuration options for customizing table behavior and properties.
101

102
### Basic Options
103

104
```python { .api }
105
from snowflake.sqlalchemy.sql.custom_schema.options import (
106
    AsQueryOption, ClusterByOption, IdentifierOption,
107
    KeywordOption, LiteralOption, TargetLagOption
108
)
109

110
class AsQueryOption:
111
    """AS query clause for CREATE TABLE AS SELECT."""
112
    
113
    def __init__(self, query: Union[str, Selectable]):
114
        """
115
        Create AS query option.
116
        
117
        Args:
118
            query: SQL query string or SQLAlchemy selectable
119
        """
120

121
class ClusterByOption:
122
    """CLUSTER BY clause for table clustering."""
123
    
124
    def __init__(self, *expressions: Union[str, TextClause]):
125
        """
126
        Create cluster by option.
127
        
128
        Args:
129
            *expressions: Column expressions for clustering
130
        """
131

132
class IdentifierOption:
133
    """Identifier option (unquoted)."""
134
    
135
    def __init__(self, value: str):
136
        """
137
        Create identifier option.
138
        
139
        Args:
140
            value: Identifier value
141
        """
142

143
class LiteralOption:
144
    """Literal option (quoted)."""
145
    
146
    def __init__(self, value: Union[int, str]):
147
        """
148
        Create literal option.
149
        
150
        Args:
151
            value: Literal value
152
        """
153

154
class TargetLagOption:
155
    """Target lag for dynamic tables."""
156
    
157
    def __init__(self, time: int, unit: TimeUnit):
158
        """
159
        Create target lag option.
160
        
161
        Args:
162
            time: Time value
163
            unit: Time unit (SECONDS, MINUTES, HOURS, DAYS)
164
        """
165
```
166

167
### Enums
168

169
```python { .api }
170
from snowflake.sqlalchemy.sql.custom_schema.options import (
171
    SnowflakeKeyword, TableOptionKey, TimeUnit
172
)
173

174
class SnowflakeKeyword(Enum):
175
    """Snowflake-specific keywords."""
176
    DOWNSTREAM = "DOWNSTREAM"
177
    AUTO = "AUTO"
178
    FULL = "FULL"
179
    INCREMENTAL = "INCREMENTAL"
180

181
class TableOptionKey(Enum):
182
    """Table option keys."""
183
    AS_QUERY = "AS_QUERY"
184
    BASE_LOCATION = "BASE_LOCATION"
185
    CATALOG = "CATALOG"
186
    CATALOG_SYNC = "CATALOG_SYNC"
187
    CLUSTER_BY = "CLUSTER_BY"
188
    DATA_RETENTION_TIME_IN_DAYS = "DATA_RETENTION_TIME_IN_DAYS"
189
    DEFAULT_DDL_COLLATION = "DEFAULT_DDL_COLLATION"
190
    EXTERNAL_VOLUME = "EXTERNAL_VOLUME"
191
    MAX_DATA_EXTENSION_TIME_IN_DAYS = "MAX_DATA_EXTENSION_TIME_IN_DAYS"
192
    REFRESH_MODE = "REFRESH_MODE"
193
    STORAGE_SERIALIZATION_POLICY = "STORAGE_SERIALIZATION_POLICY"
194
    TARGET_LAG = "TARGET_LAG"
195
    WAREHOUSE = "WAREHOUSE"
196

197
class TimeUnit(Enum):
198
    """Time units for target lag."""
199
    SECONDS = "SECONDS"
200
    MINUTES = "MINUTES"
201
    HOURS = "HOURS"
202
    DAYS = "DAYS"
203
```
204

205
## Usage Examples
206

207
### Basic Snowflake Table
208

209
```python
210
from sqlalchemy import Column, Integer, MetaData
211
from snowflake.sqlalchemy import SnowflakeTable, TEXT
212
from snowflake.sqlalchemy.sql.custom_schema.options import ClusterByOption
213

214
metadata = MetaData()
215
sales = SnowflakeTable(
216
    'sales',
217
    metadata,
218
    Column('id', Integer, primary_key=True),
219
    Column('customer_id', Integer),
220
    Column('product_name', TEXT()),
221
    Column('amount', DECIMAL(10, 2)),
222
    cluster_by=ClusterByOption('customer_id', 'product_name')
223
)
224
```
225

226
### Hybrid Table
227

228
```python
229
from snowflake.sqlalchemy import HybridTable, INTEGER, TEXT
230

231
users = HybridTable(
232
    'users',
233
    metadata,
234
    Column('user_id', INTEGER, primary_key=True),  # Required for hybrid tables
235
    Column('username', TEXT(), unique=True),
236
    Column('email', TEXT()),
237
    Column('profile', VARIANT)
238
)
239
```
240

241
### Dynamic Table
242

243
```python
244
from snowflake.sqlalchemy import DynamicTable
245
from snowflake.sqlalchemy.sql.custom_schema.options import (
246
    TargetLagOption, TimeUnit, SnowflakeKeyword
247
)
248

249
# Create dynamic table with auto-refresh
250
sales_summary = DynamicTable(
251
    'sales_summary',
252
    metadata,
253
    Column('customer_id', INTEGER),
254
    Column('total_sales', DECIMAL(12, 2)),
255
    Column('order_count', INTEGER),
256
    warehouse='COMPUTE_WH',
257
    target_lag=TargetLagOption(1, TimeUnit.HOURS),
258
    refresh_mode=SnowflakeKeyword.AUTO,
259
    as_query='SELECT customer_id, SUM(amount), COUNT(*) FROM sales GROUP BY customer_id'
260
)
261
```
262

263
### Iceberg Table
264

265
```python
266
from snowflake.sqlalchemy import IcebergTable
267

268
events = IcebergTable(
269
    'events',
270
    metadata,
271
    Column('event_id', INTEGER),
272
    Column('timestamp', TIMESTAMP_TZ),
273
    Column('event_data', VARIANT),
274
    external_volume='my_external_volume',
275
    base_location='s3://my-bucket/events/',
276
    catalog='my_catalog'
277
)
278
```
279

280
### Table with Multiple Options
281

282
```python
283
from snowflake.sqlalchemy.sql.custom_schema.options import (
284
    AsQueryOption, ClusterByOption, IdentifierOption, LiteralOption
285
)
286

287
complex_table = SnowflakeTable(
288
    'complex_table',
289
    metadata,
290
    Column('id', INTEGER),
291
    Column('data', VARIANT),
292
    cluster_by=ClusterByOption('id'),
293
    data_retention_time_in_days=LiteralOption(30),
294
    warehouse=IdentifierOption('ANALYTICS_WH'),
295
    as_query=AsQueryOption('SELECT * FROM source_table WHERE active = true')
296
)
297
```