Data Movement · Topic 10 of 17

The Snowflake SQL API and Openflow

Run SQL over REST with the Snowflake SQL API, including async handles and result partitions, and learn what Openflow is and how it is deployed.

  • 4 min
In a nutshell
  • SQL API endpoint: /api/v2/statements. REST, JSON, no driver needed.
  • Long statements return a statement handle. Results come in partitions.
  • Autocommit must be TRUE. Explicit transactions and USE need multi-statement requests.

What it is

The Snowflake SQL API is a REST API for running SQL statements without a driver. You send a statement to one endpoint, /api/v2/statements, and read the results back as JSON.

Openflow is Snowflake's integration service for moving data between sources and destinations, including unstructured content. It is built on Apache NiFi and ships with many ready made connectors.

Client appno driverPOST statementSQL APIapi v2 statementsruns onWarehousereturnsResultJSON partitionsSourcedatabase, SaaS, KafkaOpenflow connectorOpenflow runtimewritesSnowflake table

How it works

SQL API

The API can submit a statement, check its status, cancel it and fetch results concurrently. It runs standard queries and most DDL and DML.

StepWhat happens
SubmitPOST /api/v2/statements with the statement and context such as warehouse, database, schema and role
PollA statement that is still running returns a statement handle; the client checks its status with a GET using that handle
CancelA cancel request stops a running statement by its handle
FetchResults come back in partitions; the first is in the response and the client fetches the others, possibly in parallel

Authentication is by key pair (JWT), OAuth or a programmatic access token, and the request header names the token type. Network policies apply to API access in the same way as for other clients.

ClientPOST statementSQL APIstill runningStatement handleGET statusResultpartition 0GET partition nRemaining data

Design rules to know:

  • Autocommit must be TRUE for the statement. The API does not support explicit transactions in a single statement request.
  • Multi-statement requests allow BEGIN, COMMIT, ROLLBACK, USE, ALTER SESSION, session variables and temporary objects, because all statements share one session in that request.
  • PUT and GET are not supported, so files cannot be staged with the API. Use a driver, the Snowflake CLI or cloud storage for that.
  • Some stored procedures are not supported, for example Python and Java or Scala procedures that return a result set in Arrow format.
  • Fetching some results uses the cloud services layer, which can use cloud services credits.
Exam tip

The SQL API is the right choice for a lightweight service, serverless function or external orchestrator that needs to run SQL over HTTPS without installing a driver. It is not the way to bulk load local files.

Openflow

Openflow connects a source to Snowflake through a connector run in a runtime. It covers structured and unstructured data, including change data capture from databases, SaaS sources and event streams.

DeploymentWhere the data plane runsNotes
Snowflake DeploymentSnowpark Container Services in your Snowflake accountUses Snowflake's security model
Bring Your Own Cloud (BYOC)Your own cloud environment; Snowflake manages the control planeKeeps sensitive preprocessing inside your cloud

Typical uses are replicating database changes (for example from PostgreSQL, MySQL, SQL Server or Oracle), streaming from Kafka, loading SaaS data such as Salesforce or Google Ads, and ingesting documents from places such as Google Drive, Box or SharePoint for AI use cases.

At the time of writing, Snowflake Deployments are generally available in AWS, Azure and Google Cloud commercial regions, and BYOC is generally available in AWS commercial regions. New deployments are Gen 2, and configuring Gen 2 connectors is a public preview. Openflow is not enabled automatically in trial accounts. Check the current status in the documentation before relying on it.

Watch out

Openflow is a separate service from Snowpipe and the Kafka connector. It runs flows made of processors, while Snowpipe loads staged files with a COPY definition.

Common mistakes

  • Trying to run PUT through the SQL API. It is not supported.
  • Assuming a long running statement fails the HTTP call. The API returns a handle, and the client must poll or fetch by handle.
  • Leaving out partition fetches. Large results come in partitions, so the first response is not always the full result.
  • Sending transaction control in a normal single statement request. Use a multi-statement request for that.
  • Treating Openflow as a Snowflake connector for one source. It is a platform with many connectors, and the deployment choice (Snowflake Deployment or BYOC) decides where the data plane runs.

What to remember for the exam

  • SQL API endpoint: /api/v2/statements. REST, JSON, no driver needed.
  • Long statements return a statement handle. Results come in partitions.
  • Autocommit must be TRUE. Explicit transactions and USE need multi-statement requests.
  • PUT and GET are not supported in the SQL API.
  • Authentication: key pair JWT, OAuth or a programmatic access token.
  • Openflow is built on Apache NiFi, runs as a Snowflake Deployment (Snowpark Container Services) or BYOC, and moves structured and unstructured data.

Check yourself

  • An external scheduler must run a SQL statement over HTTPS and fetch the result later. Which Snowflake feature fits?

    The SQL API, which returns a statement handle for a running statement and serves results in partitions.

  • Can the SQL API stage a local file with PUT?

    No. PUT and GET are not supported, so use a driver or the Snowflake CLI.

  • A company must keep preprocessing of sensitive source data inside its own cloud. Which Openflow option fits?

    Bring Your Own Cloud (BYOC), where the data plane runs in the customer's cloud and Snowflake manages the control plane.

Topic 10 of 17 in Data Movement

Test yourself on Data Movement

The full Snowflake DEA-C02 question bank has 709 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.