Snowflake Drivers, SQL API, CLI Clients and Snowpark

1.JDBC and ODBC drivers

When an application isn't written in Python, it usually connects to Snowflake through a standard driver. Snowflake ships two.

The JDBC driver is a type 4 driver that supports core JDBC functionality. It must run in a 64-bit environment on a Java LTS (Long-Term Support) version, 1.8 or higher. Most client tools that support JDBC can use it. Google Cloud Storage regional endpoints are supported from driver version 3.21.0 onward.

The ODBC driver serves applications built on ODBC. It now has two major lines. From version 4.0.0, it's built on the Universal Core, a thin C layer over a shared Rust library. The public ODBC interface hasn't changed, and application code never sees the Rust layer. Snowflake's advice on which line to install has a twist: choose based on the driver already on your machine, not on how old your Snowflake account is. If a vendor tool manages the driver, follow the vendor's upgrade process. File transfer is another place where the version matters, because the minimum version that supports the GET and PUT commands depends on your cloud:

Sources12

2.The Snowflake SQL API

Drivers keep a session open from inside the client application. The Snowflake SQL API takes a different approach. It's a REST API, so any program that can send HTTP requests can access and update data in Snowflake, whether to run queries or to manage the deployment, for example by provisioning users and roles or creating tables. It offers three core operations:

- submit SQL statements for execution - check the status of a statement's execution - cancel a statement's execution

It can run standard queries and most DDL and DML statements. Some statement types aren't supported, and Snowflake documents those limitations separately. A single request can carry multiple statements. A stored procedure can be created by specifying it in the request body. Explicit transactions are possible by specifying the start, end and statements in the transaction. Requests authenticate with OAuth or key pair authentication.

Sources3

3.Opening the firewall with SYSTEM$ALLOWLIST

Every client covered so far, whether a driver, a connector or a CLI, needs a network path to Snowflake. Firewalls often block that path by default. SYSTEM$ALLOWLIST is the system function that returns the host names and port numbers you need to allow, covering your account URL, your stages and the other hosts Snowflake uses. You can also pass its output to SnowCD.

The function takes no arguments. It returns a VARIANT holding an array of JSON structures, each with three fields: type, host and port. Some types can appear more than once in the output, STAGE and OCSP_RESPONDER for example. Here is part of the sample output:

{"type":"SNOWFLAKE_DEPLOYMENT", "host":"xy12345.snowflakecomputing.com", "port":443}, {"type":"STAGE", "host":"sfc-customer-stage.s3.us-west-2.amazonaws.com", "port":443},

Some of the types point straight back to the tools in this lesson. SNOWSQL_REPO is the endpoint SnowSQL uses for automatic downloads or upgrades. OUT_OF_BAND_TELEMETRY lists the hosts that drivers report metrics and incidents to. OCSP_CACHE and OCSP_RESPONDER handle certificate revocation checks, and OCSP_RESPONDER isn't needed when you use private connectivity. To get a table instead of JSON, combine the function with PARSE_JSON and FLATTEN:

SELECT t.VALUE:type::VARCHAR as type, t.VALUE:host::VARCHAR as host, t.VALUE:port as port FROM TABLE( ? (input => PARSE_JSON(SYSTEM$ALLOWLIST()))) AS t;

Sources4

4.SnowSQL and Snowflake CLI

Snowflake has two command-line clients. SnowSQL connects to Snowflake to run SQL queries and all DDL and DML operations, including loading data into tables and unloading it out of them. The snowsql executable runs as an interactive shell, or in batch mode through stdin or with the -f option. It was built with the Python connector, but you don't need to install the connector first, because the installers bundle everything SnowSQL needs. SnowSQL is now legacy, with support ending per minor release:

Snowflake CLI is the preferred client for new installs and new work. It's open source and was designed for developer workloads as well as SQL. Its snow command creates, manages and deploys apps across Streamlit in Snowflake, the Native App Framework, Snowpark Container Services and Snowpark. It can also handle user-defined functions, stored procedures, stages, Git repositories and SQL execution. You can install it as a Python tool:

pip install snowflake-cli

The pip method modifies the Python environment where you install Snowflake CLI, so the docs suggest considering uv or pipx instead to avoid dependency conflicts. pipx installs and runs Python packages in isolated virtual environments, so it doesn't modify your current Python environment:

pipx install snowflake-cli

Once installed, run snow --help to verify the installation, snow --version to show the CLI version, and snow --info to show information about it. The top-level commands include sql (executes Snowflake queries), stage, streamlit, snowpark, connection and object.

Sources567

5.Snowpark: Python, Scala and Java libraries

Every tool so far moves data or SQL between an outside system and Snowflake. Snowpark works the other way round: it brings the processing to the data. The Snowpark API is a library for querying and processing data at scale. Applications built with it process data inside Snowflake, on its elastic, serverless engine, so the data doesn't have to travel to wherever the application code runs.

Snowflake provides Snowpark libraries for exactly three languages: Python, Scala and Java. Each has its own developer guide and API reference, and the libraries are available from the Snowpark Client Download page. Snowflake explicitly compares developing with Snowpark against using the Snowflake Connector for Spark. The Spark connector brings Snowflake into the Apache Spark ecosystem, enabling Spark to read data from, and write data to, Snowflake; it runs as a Spark plugin provided as a Spark package (spark-snowflake). Snowflake lists these benefits of Snowpark over it:

- libraries and patterns purpose built for different languages, without compromising on performance or functionality - code can be authored with local tools such as Jupyter, VS Code or IntelliJ - pushdown for all operations, including Snowflake UDFs, so transformation and heavy lifting happen in Snowflake - no separate cluster outside Snowflake for computations; scale and compute management are handled by Snowflake

That comparison is the deciding question in architecture scenarios: a Spark cluster pulling data out through the connector, or the same logic running in Snowflake through Snowpark. Snowflake CLI ties back in here too, because its snow snowpark commands build, deploy and run Snowpark procedures and functions.

Sources89

Exam traps

Each one states something that sounds right. Open it to see what is actually true.

1. 1.Older Snowflake accounts should stay on ODBC driver 3.x, and newer accounts should use 4.x.Why is that wrong?

   Snowflake says to choose the ODBC path based on the driver installed on your machine, not on how old your account is.

   Covered in JDBC and ODBC drivers

2. 2.You have to install the Snowflake Connector for Python before you can install SnowSQL, because SnowSQL is built on it.Why is that wrong?

   SnowSQL was built with the Python connector, but the installers bundle all the software it needs, so the connector isn't a prerequisite.

   Covered in SnowSQL and Snowflake CLI

3. 3.The SQL API can run any SQL statement that works through a JDBC session.Why is that wrong?

   The SQL API runs standard queries and most DDL and DML statements. Some statement types are unsupported and are documented as limitations.

   Covered in The Snowflake SQL API

Practise it for real

Produce a firewall allowlist for your Snowflake account and read it as a table

1. 1.Run SELECT SYSTEM$ALLOWLIST(); in a worksheet or in Snowflake CLI

   Why: The function returns every host and port that your clients need to reach

   You should see: One VARIANT value containing a JSON array of objects with type, host and port fields

2. 2.Run the FLATTEN and PARSE_JSON version of the query shown in the allowlist section

   Why: Tabular output is easier to hand to a network team than raw JSON

   You should see: One row per entry, with TYPE, HOST and PORT columns, including SNOWFLAKE_DEPLOYMENT and STAGE rows

3. 3.Find the SNOWSQL_REPO, OCSP_CACHE and OCSP_RESPONDER rows, and note whether more than one STAGE row appears

   Why: These are the entries that client tools depend on for upgrades and certificate checks

   You should see: Hosts on port 443 for the deployment and stage entries, and port 80 for the OCSP entries