Data Engineering · Topic 9 of 16

API Endpoints, SYSTEM$ALLOWLIST, SQL API, SnowSQL and Snowflake CLI

Use SYSTEM$ALLOWLIST for firewall rules, call Snowflake through the SQL API, and choose between SnowSQL and the Snowflake CLI for scripts and deployments.

  • 5 min
In a nutshell
  • SYSTEM$ALLOWLIST() returns JSON with type, host and port; SYSTEM$ALLOWLIST_PRIVATELINK is for private connectivity.
  • SQL API: POST /api/v2/statements, OAuth, key pair or other supported token authentication, async handles, result partitions, multi statement requests, no PUT or GET.
  • SnowSQL: older client with PUT and GET, config file, -f for scripts.

What it is

This page covers how clients reach Snowflake and which tool to use. API endpoints are the hostnames and ports a client must reach; SYSTEM$ALLOWLIST lists them so you can open the firewall correctly. The SQL API is a REST interface for running SQL. SnowSQL and the Snowflake CLI are command line clients for people and automation.

Firewall adminrunsSYSTEM$ALLOWLISThosts and portsrules forNetworkoutbound allow listApp or scriptHTTPS RESTSQL APIapi v2 statementsrunsSnowflakeEngineerterminalSnowSQL or snowSnowflake

How it works

API endpoints and SYSTEM$ALLOWLIST

Clients such as drivers, SnowSQL and Snowsight connect over HTTPS on port 443 to hostnames that depend on your account and cloud region. Many corporate networks block outbound traffic by default, so an administrator needs the exact list.

SELECT SYSTEM$ALLOWLIST(); takes no arguments and returns a JSON array of objects with type, host and port. The main types are:

TypePurpose
SNOWFLAKE_DEPLOYMENT, SNOWFLAKE_DEPLOYMENT_REGIONLESSThe account host and its regionless variant
STAGECloud storage hosts the client reads and writes files through
SNOWSQL_REPOWhere SnowSQL downloads and upgrades come from
OUT_OF_BAND_TELEMETRYDriver metrics and incident reporting
OCSP_CACHE, OCSP_RESPONDER, CRL_DISTRIBUTION_POINTCertificate revocation checking
CLIENT_FAILOVERHosts for Client Redirect connections
DUO_SECURITYDuo hosts for multi factor authentication
SNOWSIGHT_DEPLOYMENTHosts for the Snowsight web interface

For private connectivity (AWS PrivateLink, Azure Private Link or Google Cloud Private Service Connect), use SYSTEM$ALLOWLIST_PRIVATELINK instead, which returns the private endpoint hosts. Run the function in the account you are configuring, since the output is specific to it.

Exam tip

A question that says "the driver connects but PUT or large result downloads fail behind a firewall" points to the missing STAGE hosts from SYSTEM$ALLOWLIST.

SQL API

The SQL API lets any language that can send HTTPS requests run SQL without a driver. You send a POST request to /api/v2/statements with a JSON body holding the statement and context such as warehouse, database, schema and role. Statements can use bind variables.

Authentication uses OAuth, key pair authentication (a signed JWT) or workload identity federation, and the token type header also accepts programmatic access tokens. You name the token type in the X-Snowflake-Authorization-Token-Type header.

CapabilityHow it works
Asynchronous runSet async to true; the response returns a statement handle, and you poll GET /api/v2/statements/<handle>
CancelPOST /api/v2/statements/<handle>/cancel
Large resultsResults come in partitions; the first is in the response and you request others by partition number
Multiple statementsSend several statements in one request by setting the MULTI_STATEMENT_COUNT parameter
Stored procedures and transactionsSupported, including explicit transactions inside a multi statement request

The SQL API cannot run PUT and GET, because those move files through a client. To load files you stage them with a driver or tool and then call COPY INTO through the API.

ClientappPOST api v2 statementsSnowflakeruns SQLhandle and first partitionClientGET partitionsMore results

Use the SQL API for serverless functions, microservices or orchestrators that need to run SQL with no driver installed.

SnowSQL

SnowSQL is the original command line client, a Python based tool installed from a downloadable installer. It runs interactive queries and scripts (-f file.sql), supports variables, several output formats and PUT and GET. Connection settings sit in a config file (by default under ~/.snowsql/), where you can define named connections and select one with -c.

SnowSQL fits existing scripts and file transfer jobs. Its updates download from the host listed as SNOWSQL_REPO in the allowlist.

Snowflake CLI

The Snowflake CLI (the snow command) is the newer open source command line tool for developers. It does everything SnowSQL does for SQL (snow sql) and adds developer workflows.

AreaCommand group
Run SQLsnow sql
Connectionssnow connection (stored in a config.toml file)
Stage filessnow stage
Snowpark objects (UDFs, procedures)snow snowpark
Streamlit appssnow streamlit
Native Appssnow app
Git repositoriessnow git

Projects are described by a snowflake.yml definition file, so a deployment can be repeated in a CI pipeline.

AspectSnowSQLSnowflake CLI
Commandsnowsqlsnow
PurposeSQL scripts and file transferSQL plus app and developer deployment
Configconfig fileconfig.toml
Best forExisting scriptsNew automation and CI deployments

Common mistakes

Opening only port 443 to the account hostname. Add every STAGE and certificate host from SYSTEM$ALLOWLIST.

Trying PUT through the SQL API. The API runs SQL only; stage files with a driver or client tool first.

Assuming the SQL API is synchronous only. Use async for long statements and poll the handle.

Using SnowSQL for new deployment pipelines. The Snowflake CLI is built for Snowpark, Streamlit and Native App deployment.

What to remember for the exam

  • SYSTEM$ALLOWLIST() returns JSON with type, host and port; SYSTEM$ALLOWLIST_PRIVATELINK is for private connectivity.
  • SQL API: POST /api/v2/statements, OAuth, key pair or other supported token authentication, async handles, result partitions, multi statement requests, no PUT or GET.
  • SnowSQL: older client with PUT and GET, config file, -f for scripts.
  • Snowflake CLI: snow command, config.toml, snowflake.yml, deploys Snowpark, Streamlit and Native Apps.
  • Both command line tools use the same HTTPS endpoints.

Check yourself

  • Which function lists the hosts and ports a firewall must allow?

    SYSTEM$ALLOWLIST, which returns JSON with the type, host and port of each endpoint.

  • Why can PUT not run through the SQL API?

    The SQL API only executes SQL over REST and cannot move local files; use a driver or CLI for staging.

  • Which tool fits a CI pipeline that deploys a Streamlit app?

    The Snowflake CLI, with a snowflake.yml project and snow streamlit commands.

Topic 9 of 16 in Data Engineering

Test yourself on Data Engineering

The full Snowflake ARA-C01 question bank has 687 questions, with timed practice exams and an explanation for every answer.

This study guide is independent and is not affiliated with, endorsed by, or sponsored by Snowflake. Vendor names, certification names and exam codes are trademarks of their respective owners and are used for identification only.