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
SYSTEM$ALLOWLIST()returns JSON withtype,hostandport;SYSTEM$ALLOWLIST_PRIVATELINKis for private connectivity.- SQL API:
POST /api/v2/statements, OAuth, key pair or other supported token authentication, async handles, result partitions, multi statement requests, noPUTorGET. - SnowSQL: older client with
PUTandGET, config file,-ffor 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.
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:
| Type | Purpose |
|---|---|
SNOWFLAKE_DEPLOYMENT, SNOWFLAKE_DEPLOYMENT_REGIONLESS | The account host and its regionless variant |
STAGE | Cloud storage hosts the client reads and writes files through |
SNOWSQL_REPO | Where SnowSQL downloads and upgrades come from |
OUT_OF_BAND_TELEMETRY | Driver metrics and incident reporting |
OCSP_CACHE, OCSP_RESPONDER, CRL_DISTRIBUTION_POINT | Certificate revocation checking |
CLIENT_FAILOVER | Hosts for Client Redirect connections |
DUO_SECURITY | Duo hosts for multi factor authentication |
SNOWSIGHT_DEPLOYMENT | Hosts 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.
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.
| Capability | How it works |
|---|---|
| Asynchronous run | Set async to true; the response returns a statement handle, and you poll GET /api/v2/statements/<handle> |
| Cancel | POST /api/v2/statements/<handle>/cancel |
| Large results | Results come in partitions; the first is in the response and you request others by partition number |
| Multiple statements | Send several statements in one request by setting the MULTI_STATEMENT_COUNT parameter |
| Stored procedures and transactions | Supported, 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.
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.
| Area | Command group |
|---|---|
| Run SQL | snow sql |
| Connections | snow connection (stored in a config.toml file) |
| Stage files | snow stage |
| Snowpark objects (UDFs, procedures) | snow snowpark |
| Streamlit apps | snow streamlit |
| Native Apps | snow app |
| Git repositories | snow git |
Projects are described by a snowflake.yml definition file, so a deployment can be repeated in a CI pipeline.
| Aspect | SnowSQL | Snowflake CLI |
|---|---|---|
| Command | snowsql | snow |
| Purpose | SQL scripts and file transfer | SQL plus app and developer deployment |
| Config | config file | config.toml |
| Best for | Existing scripts | New 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 withtype,hostandport;SYSTEM$ALLOWLIST_PRIVATELINKis for private connectivity.- SQL API:
POST /api/v2/statements, OAuth, key pair or other supported token authentication, async handles, result partitions, multi statement requests, noPUTorGET. - SnowSQL: older client with
PUTandGET, config file,-ffor scripts. - Snowflake CLI:
snowcommand,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
PUTnot 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.ymlproject andsnow streamlitcommands.
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.