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
- 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
USEneed 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.
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.
| Step | What happens |
|---|---|
| Submit | POST /api/v2/statements with the statement and context such as warehouse, database, schema and role |
| Poll | A statement that is still running returns a statement handle; the client checks its status with a GET using that handle |
| Cancel | A cancel request stops a running statement by its handle |
| Fetch | Results 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.
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. PUTandGETare 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.
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.
| Deployment | Where the data plane runs | Notes |
|---|---|---|
| Snowflake Deployment | Snowpark Container Services in your Snowflake account | Uses Snowflake's security model |
| Bring Your Own Cloud (BYOC) | Your own cloud environment; Snowflake manages the control plane | Keeps 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.
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
PUTthrough 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
USEneed multi-statement requests. PUTandGETare 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.
PUTandGETare 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.