SightSpeak AI Blog

Hey community! Let’s learn how to connect to Snowflake from Python, run SQL queries with the Python connector, and write Snowpark code that executes inside Snowflake’s compute. Example code included.

Intro
Snowflake is a cloud data platform that separates storage and compute. Python developers typically interact with Snowflake using two primary approaches: the low-level Python Connector (PEP-249) for SQL execution, and Snowpark for Python, which provides DataFrame-style APIs executed inside Snowflake’s compute layer. This post demonstrates both methods and explains when each is best suited. Snowflake Documentation

Prerequisites

• Snowflake account and a role capable of creating tables.
• Python 3.9+ environment.
• pip install snowflake-connector-python snowflake-snowpark-python

1) Quick SQL with the Python Connector
Best for ad-hoc SQL execution, small ETL tasks, and scripted workloads.

import snowflake.connector
from pprint import pprint

conn = snowflake.connector.connect(
    user='YOUR_USER',
    password='YOUR_PASSWORD',
    account='YOUR_ACCOUNT',
    warehouse='MY_WH',
    database='MY_DB',
    schema='PUBLIC',
    role='MY_ROLE'
)

cur = conn.cursor()
try:
    cur.execute("CREATE OR REPLACE TABLE demo_py (id INT, name STRING)")
    cur.execute("INSERT INTO demo_py (id,name) VALUES (%s,%s),(%s,%s)", (1,'alice', 2,'bob'))
    cur.execute("SELECT * FROM demo_py")
    pprint(cur.fetchall())
finally:
    cur.close()
    conn.close()

Learn more here: Snowflake Documentation

2) Snowpark — DataFrame API executed inside Snowflake

from snowflake.snowpark import Session
from snowflake.snowpark.functions import col

session = Session.builder.configs({
    "account": "YOUR_ACCOUNT",
    "user": "YOUR_USER",
    "password": "YOUR_PASSWORD",
    "role": "MY_ROLE",
    "warehouse": "MY_WH",
    "database": "MY_DB",
    "schema": "PUBLIC",
}).create()

df = session.create_dataframe([(1,"alice"), (2,"bob")], schema=["id","name"])
df.write.save_as_table("snowpark_demo", mode="overwrite")

result = session.table("snowpark_demo") \
                .filter(col("id") > 1) \
                .collect()
print(result)

session.close()

More details: Snowpark for Python Documentation

Best practices & tips

• Use a secrets manager to avoid storing credentials in code.
• Use Snowpark for transformation-heavy pipelines.
• Monitor warehouse size and usage to manage cost.

Next steps

• Schedule Snowpark pipelines using Snowflake Tasks.
• Create a CI/CD workflow to deploy Snowpark code as packages.

References

• Snowflake Python Connector: Documentation
• Snowpark for Python: Documentation