Snowflake table created with SQLAlchemy requires quotes (“”) to query

I am ingesting data into Snowflake tables using Python and SQLAlchemy. These tables that I have created all require quotations to query both the table name and the column names. For example, select * from "database"."schema"."table" where "column" = 2; Will run, while select * from database.schema.table where column = 2; will not run. The difference being the quotes.

I understand that if a table is created in Snowflake with quotes than quotes will be required to query it. However, I only put an Excel file in a Pandas data frame then used SQLAlchemy and pd.to_sql to create the table. An example of my code:

engine = create_engine(URL(
    account = 'my_account',
    user = 'my_username',
    password = 'my_password',
    database = 'My_Database',
    schema = 'My_Schema',
    warehouse = 'My_Wh',
    role='My Role',
))

connection = engine.connect()

df.to_sql('My_Table', con=engine, if_exists='replace', index=False, index_label=None, chunksize=16384)

Does SQLAlchemy automatically create the tables with quotes? Is this a problem with the schema? I did not set that up. Is there a way around this?

Answer

From the SQLAlchemy Snowflake Github documentation:

Object Name Case Handling
Snowflake stores all case-insensitive object names in uppercase text. In contrast, SQLAlchemy considers all lowercase object names to be case-insensitive. Snowflake SQLAlchemy converts the object name case during schema-level communication, i.e. during table and index reflection. If you use uppercase object names, SQLAlchemy assumes they are case-sensitive and encloses the names with quotes. This behavior will cause mismatches agaisnt data dictionary data received from Snowflake, so unless identifier names have been truly created as case sensitive using quotes, e.g., “TestDb”, all lowercase names should be used on the SQLAlchemy side.

What I think this is trying to say is SQLAlchemy treats any names containing capital letters as being case-sensitive and automatically encloses them in quotes, conversely any names in lower case are not quoted. It doesn’t look like this behaviour is configurable.

You probably don’t have any control over database and possibly schema names, but when creating your table if you want consistent behaviour whether quoted or unquoted then you should stick to using lower case naming. What you should find is that the table name will then work whether you use "my_table" or my_table.