Create hybrid table with Snowflake SQLAlchemy

Question

I want to add Snowflake hybrid tables to my database schema using SQLAlchemy.

Per this issue, support for hybrid tables is not implemented in the Snowflake SQLAlchemy official dialect.

Is there a way for me to customize the CREATE TABLE ... statement generated by SQLAlchemy so that it actually generates CREATE HYBRID TABLE ... for a given table?

Consider the example code below:

from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column


class Base(DeclarativeBase):
    pass


class MyTable(Base):
    id: Mapped[int] = mapped_column(primary_key=True)



Base.metadata.create_all(bind=...)

Running it will emit the following SQL statement:

CREATE TABLE mytable (
    id INTEGER NOT NULL AUTOINCREMENT,
    CONSTRAINT pk_mytable PRIMARY KEY (id)
)

and I would like a way to have the following instead:

CREATE HYBRID TABLE mytable (
    id INTEGER NOT NULL AUTOINCREMENT,
    CONSTRAINT pk_mytable PRIMARY KEY (id)
)

Answer 1

You can pass prefixes to a table.

from sqlalchemy import create_engine
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column

class Base(DeclarativeBase): ...

class SomeTable(Base):
    __tablename__ = "some_table"
    __table_args__ = {"prefixes": ["HYBRID"]}
    id: Mapped[int] = mapped_column(primary_key=True)

engine = create_engine("sqlite:///test.sqlite", echo=True)
Base.metadata.create_all(engine)

SQL Emitted

CREATE HYBRID TABLE some_table (
        id INTEGER NOT NULL, 
        PRIMARY KEY (id)
)

I used SQLite because I do not have access to Snowflake but it should work all the same in an actual Snowflake instance.