What you will be able to do
- Describe what the Snowflake Connector for Python provides and how it authenticates
- Install the pandas extra and move data between Snowflake and pandas with fetch_pandas_all, fetch_pandas_batches and write_pandas
- Predict how Snowflake data types map to pandas data types, including NULLs in integer columns
- Set up the Snowflake extension for Visual Studio Code and use it to sign in, run SQL and debug Snowpark Python
1.The Snowflake Connector for Python
Snowpark builds queries out of DataFrame objects. The Snowflake Connector for Python works at a lower level: it is a standard database driver. It is a native, pure-Python package with no JDBC or ODBC dependency, and you install it with pip on Linux, macOS and Windows. It follows the Python Database API v2 specification (PEP-249), so it works with two familiar objects:
- Connection objects connect to Snowflake. - Cursor objects run DDL/DML statements and queries.
SnowSQL, the Snowflake command-line client, is built on this connector.
After import snowflake.connector, you read login details from environment variables, the command line or a configuration file. The ACCOUNT parameter takes your account identifier without the snowflakecomputing.com suffix. Then you connect with the default authenticator or with federated authentication. Network administrators can require MFA for all connections, and if they do, the client has to be configured to use MFA.
Checkpoint 1 of 6· Check yourself
Which statement describes the Snowflake Connector for Python?
The connector is a native Python implementation of the Python DB API v2. It has no JDBC or ODBC dependency and installs with pip.
“The connector is a native, pure Python package that has no dependencies on JDBC or ODBC.”Source: docs.snowflake.com
2.Moving data between Snowflake and pandas
For pandas work, install the connector with its pandas extra. You must type the square brackets, and you should quote the whole package name so your shell doesn't treat the brackets as a wildcard. This install also brings in a compatible PyArrow, so you don't install PyArrow yourself. If a different PyArrow version is already installed, uninstall it before installing the connector. To combine extras, separate them with commas.
pip install "snowflake-connector-python[secure-local-storage,pandas]"Reading: run the query on a Cursor, then call fetch_pandas_all() to get a single DataFrame, or fetch_pandas_batches() to get the result in batches. Writing: call write_pandas(), or call pandas' DataFrame.to_sql() with pd_writer() as the insert method. Older code that looped over fetchmany(), or used SQLAlchemy's read_sql_query, can switch to these calls. SQLAlchemy is no longer required for this, though the connector still works with it.
Checkpoint 2 of 6· Match them up
Match each connector API to its job
Tap a term, then the definition that fits it.
The two fetch methods read data into pandas through a Cursor. write_pandas and to_sql with pd_writer write data from pandas to Snowflake.
“To read data into a pandas DataFrame, you use a Cursor to retrieve the data and then call one of these Cursor methods”Source: docs.snowflake.com
| Snowflake data type | pandas data type |
|---|---|
| FIXED NUMERIC (scale = 0) except DECIMAL | (u)int{8,16,32,64} or float64 (for NULL) |
| FIXED NUMERIC (scale > 0) except DECIMAL | float64 |
| FIXED NUMERIC type DECIMAL | decimal |
| FLOAT/DOUBLE | float64 |
| VARCHAR, BINARY, VARIANT | str |
| DATE | object (with datetime.date objects) |
| TIME, TIMESTAMP_NTZ, TIMESTAMP_LTZ, TIMESTAMP_TZ | pandas.Timestamp(np.datetime64[ns]) |
Two consequences follow from the table. A VARIANT column comes back as a string, so semi-structured data needs parsing on the client. And if a conversion overflows, the connector raises an exception; it doesn't silently truncate the value. Keep in mind that a fetched DataFrame lives in client memory. For very large tables, do the reduction in Snowflake first, for example with Snowpark, and fetch only the result.
Checkpoint 3 of 6· Exam question
A data scientist runs this Snowpark code from an external IDE and then checks QUERY_HISTORY, but no statement from the script appears. ```python from snowflake.snowpark.functions import col df = session.table("CUSTOMERS").filter(col("REGION") == "EMEA").select("ID", "SPEND") print("features ready") ``` What explains this?
Correct answer: D — Snowpark DataFrames are evaluated lazily, so no SQL is submitted until an action such as `collect()`, `show()` or `to_pandas()` is called on `df`.
- A. Snowpark keeps no local copy of the table. Every transformation, including `filter`, is part of the single plan that is sent as SQL later.
- B. Snowpark queries appear in query history like any other SQL. The missing entry here is because nothing has been executed yet.
- C. `session.table` returns a lazily evaluated DataFrame, not a placeholder, and transformations are never skipped; they just have not been triggered.
- D. Transformations only build a query plan on the client. Snowflake receives SQL when an action forces evaluation, and this script never calls one.
Checkpoint 4 of 6· Fill the gap
Which extra makes pip install the pandas-compatible connector?
pip install "snowflake-connector-python[ ? ]"The [pandas] extra installs the pandas-oriented API together with a compatible PyArrow version.
Source: docs.snowflake.comSources3
3.Working from Visual Studio Code
Snowpark code can be written in local tools such as Jupyter, VS Code or IntelliJ. For VS Code, Snowflake publishes the Snowflake extension. It lets you write and run Snowflake SQL inside the editor, and it works with Snowpark Python for debugging and for SQL highlighting and autocomplete inside Python strings.
Setup:
1. Install the extension from the Visual Studio Marketplace (search *Snowflake* and check for the Snowflake badge), or install a downloaded .vsix file.
2. Sign in from the Snowflake icon in the Activity Bar. Enter your account identifier or URL, then choose single sign-on, username/password or key pair. OAuth is configured in connections.toml.
3. Manage your connections. Choose Snowflake: Edit Connections File to edit connections.toml, or set Snowsql Config Path to load connections from a SnowSQL configuration file. Only the connection values from that file are used.
Once you are signed in, the sidebar shows your account, your default role, an Object Explorer and your query history.
Daily use: open or create a *Snowflake SQL File*, then run all statements or only the ones you select. Query history keeps past results, which you can sort, hide, or save as CSV or gzip. After each query, the extension runs DESC RESULT in the background, so LAST_QUERY_ID() returns the wrong ID in this environment.
For Snowpark Python: write a stored procedure as a Python function whose first parameter is a Snowpark Session. An inline Snowflake: Debug option then appears. It runs the procedure with your active extension session, and you can set breakpoints. With Auto Detect Sql in Python turned on, the extension highlights a Python string that starts with an uppercase SQL keyword. You can also mark SQL explicitly with start and end comment markers. While you are connected, the extension suggests table and column names as you type.
Checkpoint 5 of 6· Exam question
A job on a 16 GB VM must read an 80-million-row result set through the Snowflake Connector for Python and feed it to a pandas-based featurizer. `fetch_pandas_all()` runs out of memory. Which approach is MOST appropriate?
Correct answer: B — Iterate over `cursor.fetch_pandas_batches()` and process each DataFrame chunk in turn, so only one batch of rows sits in client memory at a time.
- A. Warehouse size changes query compute, not client memory. The full result would still have to fit inside the 16 GB VM.
- B. `fetch_pandas_batches()` yields the result as a sequence of smaller DataFrames, which bounds client memory use while still using Arrow-based transfer.
- C. Appending each row still accumulates all 80 million rows in memory, and per-row appends are very slow, so memory use does not stay flat.
- D. `fetchall()` loads every row into a Python list at once, which is even heavier than a DataFrame and does not stream.
Checkpoint 6 of 6· Check yourself
You want to step through a Snowpark Python stored procedure in VS Code with breakpoints. What does the Snowflake extension need?
The inline Snowflake: Debug option appears for a Python function whose first parameter is a Snowpark Session. It runs with your active extension session.
“Write a Snowflake stored procedure in a Python function where the first parameter is a Snowpark Session object.”Source: docs.snowflake.com
Sources4
Exam traps
Each one states something that sounds right. Open it to see what is actually true.
1.To get Snowflake query results into a pandas DataFrame, you need SQLAlchemy and pd.read_sql_query.Why is that wrong?
The connector's Cursor has fetch_pandas_all() and fetch_pandas_batches(). SQLAlchemy is optional and still compatible.
Covered in Moving data between Snowflake and pandas
2.Before installing the pandas connector, you should pin and install your own PyArrow version.Why is that wrong?
Installing the connector with the pandas extra installs the right PyArrow. Any other PyArrow version should be uninstalled first and not reinstalled afterwards.
Covered in Moving data between Snowflake and pandas
3.LAST_QUERY_ID() in the VS Code extension returns the ID of the query you just ran.Why is that wrong?
After every query, the extension runs DESC RESULT in the background, which changes what LAST_QUERY_ID() returns.
Covered in Working from Visual Studio Code
Sources
Every claim above is drawn from one of these pages, quoted as it was written on the date shown.
- 1.
“Cursor objects for executing DDL/DML statements and queries.”
↩︎ The Snowflake Connector for Python“The connector is a native, pure Python package that has no dependencies on JDBC or ODBC.”
↩︎ Checkpoint - 2.https://docs.snowflake.com/en/developer-guide/python-connector/python-connector-connectOfficial docs
“After reading the connection information, connect using either the default authenticator or federated authentication (if enabled).”
↩︎ The Snowflake Connector for Python - 3.
“You must enter the square brackets ([ and ]) as shown in the command.”
↩︎ Moving data between Snowflake and pandas“Call the write_pandas() function.”
↩︎ Moving data between Snowflake and pandas“If any conversion causes overflow, the Python connector throws an exception.”
↩︎ Moving data between Snowflake and pandas“With support for pandas in the Python connector, SQLAlchemy is no longer needed to convert data in a cursor into a DataFrame.”
↩︎ Exam trap 1“installing the Python Connector as documented below automatically installs the appropriate version of PyArrow.”
↩︎ Exam trap 2“To read data into a pandas DataFrame, you use a Cursor to retrieve the data and then call one of these Cursor methods”
↩︎ Checkpoint“and if the value is NULL, then the value is converted to float64, not an integer type.”
↩︎ Prediction - 4.https://docs.snowflake.com/en/user-guide/vscode-extOfficial docs
“The extension also integrates with Snowpark Python to provide debugging, syntax highlighting, and autocomplete features for SQL in Python code.”
↩︎ Working from Visual Studio Code“Only connection configuration values are used. Other SnowSQL configuration values are ignored.”
↩︎ Working from Visual Studio Code“The extension automatically detects SQL statements by looking for a SQL keyword in all capital letters as the first word in a Python string”
↩︎ Working from Visual Studio Code“This process makes LAST_QUERY_ID() inaccurate.”
↩︎ Exam trap 3“Write a Snowflake stored procedure in a Python function where the first parameter is a Snowpark Session object.”
↩︎ Checkpoint