SNOW-2221758: Temporary tables invisible to session after creation

[k-brooks]

Python version

3.12

Operating system and processor architecture

AWS Lambda - python:3.12.v75

Installed packages

asn1crypto==1.5.1
boto3==1.39.11
botocore==1.39.11
certifi==2025.7.14
cffi==1.17.1
charset-normalizer==3.4.2
cryptography==45.0.5
filelock==3.18.0
idna==3.10
jmespath==1.0.1
packaging==25.0
platformdirs==4.3.8
pycparser==2.22
PyJWT==2.10.1
pyOpenSSL==25.1.0
python-dateutil==2.9.0.post0
pytz==2025.2
requests==2.32.4
s3transfer==0.13.1
six==1.17.0
snowflake-connector-python==3.16.0
sortedcontainers==2.4.0
tomlkit==0.13.3
typing_extensions==4.14.1
urllib3==2.5.0

What did you do?

Role is created with usage against warehouse, database, and schema as well as create temporary table against the schema.

import snowflake.connector

conn = snowflake.connector.connect(
    user='your_user',
    password='your_pass',
    account='your_account',
    warehouse='your_wh',
    database='your_db',
    schema='your_schema',
    role='your_role',
    client_session_keep_alive=True
)

cursor = conn.cursor()

print("conn.get_session_id():", conn.get_session_id())
print("SQL session ID:", cursor.execute("SELECT CURRENT_SESSION()").fetchone()[0])

cursor.execute("CREATE TEMPORARY TABLE TEMP_DEBUG_TEST (id INT)")

cursor.execute("INSERT INTO TEMP_DEBUG_TEST (id) VALUES (1)")  # ❌ Fails here

What did you expect to see?

Insertion into the temp table succeeds - created temp table visible to the session

Can you set logging to DEBUG and collect the logs?

Upon request

[sfc-gh-dszmolka]

hello @k-brooks - thanks for the repro. I'm running exactly the same (+ DEBUG logs enabled)
the only difference being i'm using

print("conn.get_session_id():", conn.session_id)

as there's no get_session_id() method on SnowflakeConnection; so not sure where it is coming from.

Anyhow, using the vanilla Snowflake Python Driver 3.16.0; seems to be working. I'm testing on GCP in case that matters.
With also printing the results:

# python test.py 
conn.get_session_id(): 1123486135767054
SQL session ID: 1123486135767054
('Table TEMP_DEBUG_TEST successfully created.',)
(1,)

using the original repro:

# python test2.py 
conn.get_session_id(): 1123486135750678
SQL session ID: 1123486135750678

no error whatsoever.

packages (doing nothing just pip installing the driver)

# pip freeze
asn1crypto==1.5.1
boto3==1.39.11
botocore==1.39.11
certifi==2025.7.14
cffi==1.17.1
charset-normalizer==3.4.2
cryptography==45.0.5
filelock==3.18.0
idna==3.10
jmespath==1.0.1
packaging==25.0
platformdirs==4.3.8
pycparser==2.22
PyJWT==2.10.1
pyOpenSSL==25.1.0
python-dateutil==2.9.0.post0
pytz==2025.2
requests==2.32.4
s3transfer==0.13.1
six==1.17.0
snowflake-connector-python==3.16.0
sortedcontainers==2.4.0
tomlkit==0.13.3
typing_extensions==4.14.1
urllib3==2.5.0

and

# python -V
Python 3.12.9

maybe worth noting i'm doing this outside of Lambda; in a container, with role ACCOUNTADMIN

• can you please try running the exact same statements with the exact same role but in Snowsight GUI or other driver, to make sure it is not a permissions issue after all? if it doesn't work outside of the Python driver either, please stop here :)
• can you please check and see if it happens outside of AWS Lambda ?
• can you please let me know which cloud/region you use? Should not be relevant but best would be for me to try and reproduce on the same.
• would it change the situation and the error occurring if you try to use connection with with like this:

with snowflake.connector.connect(
      user="username"
..
      database="test_db"
) as conn:

[sfc-gh-dszmolka]

closing due to inactivity