Snowflake API: Comprehensive Guide to 3 Methods With Examples

Learn how to use Snowflake REST API & Python language connector, as well as build a fast GraphQL API on Snowflake.

Photo: Propel

When to use a Snowflake API

You may want to use an API on top of your Snowflake data in a few different situations.

[In-product analytics] You have internal analytics data and you want your users to be able to view it
[Usage metering] You want to keep track of usage data and use that for billing or other purposes
[Using Snowflake as your application database] You want to use data in your Snowflake to power all or part of your customer-facing application

Let’s quickly dive into each of these use cases for further clarification.

1. [In-product analytics] You have internal analytics data and you want your users to be able to view it

Perhaps you’re managing a SaaS product. Or maybe a consumer app with millions of users.

Whatever it may be, having a world-class in-product analytics experience will help your product stand out from your competitors.

For example, you may want to let your users visualize their usage data.

Or perhaps you want to help your users notice new patterns in their sales data by visualizing it.

In each of these use cases, using an API on top of your Snowflake data, you can empower your users to be able to access data they need to make better decisions when using your product.

2. [Usage metering] You want to keep track of usage data and use that for billing or other purposes

Suppose you’re managing a product that uses an LLM API such as a ChatGPT API, or your company uses any other resource that costs you money every time you use it.

Then, you may want to charge customers based on their monthly usage.

If this data is already in Snowflake, you may want to use an API to access it for billing and usage-metering purposes.

3. [Using Snowflake as your application database] You want to use data in your Snowflake to power all or part of your customer-facing application

Suppose your users request features that require you to use some of the data that’s available in Snowflake.

Then, you’ll need a way to quickly and securely access the relevant user data in Snowflake from your product.

In such a case, being able to use a fast API on top of your Snowflake data may be crucial for achieving optimal product experiences.

How to use an API on top of Snowflake

Now that we’ve covered a few use cases where using an API on Snowflake may be useful, let’s go over a few different ways of implementing it.

Snowflake’s REST API
Snowflake’s language-specific connectors
An analytics platform that includes a semantic layer, such as Propel

Examples

To explain how a Snowflake API works in these three methods, we will use the following example throughout this post. If you prefer to get an overview of the three methods without following along, feel free to skip to the next section.

Example data setup

Suppose that you’re running a SaaS application that manages sales of taco restaurants.

Then, you may have data that looks like this:

As you can see, this data represents taco orders from various restaurants. Each row captures a unique order with details such as:

TIMESTAMP: The exact date and time the order was placed.
RESTAURANT_NAME: The name of the restaurant where the taco was ordered.
TACO_NAME: The type of taco ordered, like Carne Asada or Veggie.
TOPPINGS: A list of additional toppings added to the taco, like Guacamole or Radishes.
TACO_TOTAL_PRICE: The total price for the tacos of this type.

You can create data like this in your Snowflake account with two queries.

First, create a table by running this query:

CREATE TABLE taco_orders (
    TIMESTAMP TIMESTAMP_NTZ,
    ORDER_ID STRING,
    RESTAURANT_NAME STRING,
    TACO_NAME STRING,
    TORTILLA_NAME STRING,
    SAUCE_NAME STRING,
    TOPPINGS VARIANT,
    QUANTITY INTEGER,
    TACO_UNIT_PRICE FLOAT,
    TACO_TOTAL_PRICE FLOAT
);

Then, insert 20 rows that fit this format with this query:

INSERT INTO taco_orders 
(TIMESTAMP, ORDER_ID, RESTAURANT_NAME, TACO_NAME, TORTILLA_NAME, SAUCE_NAME, TOPPINGS, QUANTITY, TACO_UNIT_PRICE, TACO_TOTAL_PRICE) 
SELECT '2023-09-14T07:32:24.234208Z', 'a1234567-b8c9-0123-d456-e789f012a345', 'El Buen Sabor', 'Breakfast', 'Corn', 'Salsa Roja', ARRAY_CONSTRUCT('Lime', 'Guacamole'), 3, 3, 9 UNION ALL
SELECT '2023-09-14T07:33:25.234209Z', 'b2345678-c9d0-1234-e567-f890a123b456', 'La Taqueria', 'Barbacoa', 'Flour', 'Chimichurri', ARRAY_CONSTRUCT('Lime', 'Radishes'), 2, 3.5, 7 UNION ALL
SELECT '2023-09-14T07:34:26.234210Z', 'c3456789-d0e1-2345-f678-9012a345c678', 'Taqueria Cancun', 'Veggie', 'Whole Wheat', 'Chipotle Mayo', ARRAY_CONSTRUCT('Black beans', 'White beans'), 4, 2.75, 11 UNION ALL
SELECT '2023-09-14T07:35:27.234211Z', 'd4567890-e1f2-3456-a789-0123b456d789', 'Los Compadres', 'Grilled Fish', 'Corn', 'Queso Blanco', ARRAY_CONSTRUCT('Lime', 'Guacamole'), 1, 4, 4 UNION ALL
SELECT '2023-09-14T07:36:28.234212Z', 'e5678901-f234-4567-b890-1234c567e890', 'Farolito', 'Carne Asada', 'Flour', 'Habanero Hot Sauce', ARRAY_CONSTRUCT('Lime', 'Radishes', 'Onions'), 3, 3.5, 10.5 UNION ALL
SELECT '2023-09-14T07:37:29.234213Z', 'f6789012-a345-5678-c901-2345d678f901', 'Taqueria Vallarta', 'Chorizo', 'Whole Wheat', 'Cilantro Lime', ARRAY_CONSTRUCT('Guacamole', 'Cheese'), 2, 3.25, 6.5 UNION ALL
SELECT '2023-09-14T07:38:30.234214Z', '01234567-b8c9-6789-d012-e345f678a234', 'El Buen Sabor', 'Pollo', 'Corn', 'Mango Salsa', ARRAY_CONSTRUCT('Guacamole', 'Cheese'), 4, 3, 12 UNION ALL
SELECT '2023-09-14T07:39:31.234215Z', '12345678-c9d0-7890-e123-f456a789b012', 'La Taqueria', 'Al Pastor', 'Flour', 'Salsa Verde', ARRAY_CONSTRUCT('Lime', 'Radishes'), 1, 3.5, 3.5 UNION ALL
SELECT '2023-09-14T07:40:32.234216Z', '23456789-d0e1-8901-f234-a567b890c123', 'Taqueria Cancun', 'Veggie', 'Whole Wheat', 'Chipotle Mayo', ARRAY_CONSTRUCT('Black beans', 'White beans'), 3, 2.75, 8.25 UNION ALL
SELECT '2023-09-14T07:41:33.234217Z', '34567890-e1f2-9012-a345-b678c901d234', 'Los Compadres', 'Grilled Fish', 'Corn', 'Queso Blanco', ARRAY_CONSTRUCT('Lime', 'Guacamole'), 2, 4, 8 UNION ALL
SELECT '2023-09-14T07:26:18.234202Z', 'a1b2c3d4-e5f6-4789-a012-b345c678d901', 'El Buen Sabor', 'Pollo', 'Corn', 'Mango Salsa', ARRAY_CONSTRUCT('Guacamole', 'Cheese'), 2, 3, 6 UNION ALL
SELECT '2023-09-14T07:27:19.234203Z', 'b2c3d4e5-f6a7-4890-b123-c456d789e012', 'La Taqueria', 'Al Pastor', 'Flour', 'Salsa Verde', ARRAY_CONSTRUCT('Lime', 'Radishes'), 4, 3.5, 14 UNION ALL
SELECT '2023-09-14T07:28:20.234204Z', 'c3d4e5f6-a7b8-4901-c234-d567e890f123', 'Taqueria Cancun', 'Veggie', 'Whole Wheat', 'Chipotle Mayo', ARRAY_CONSTRUCT('Black beans', 'White beans'), 3, 2.75, 8.25 UNION ALL
SELECT '2023-09-14T07:29:21.234205Z', 'd4e5f6a7-b8c9-4012-d345-e678f901a234', 'Los Compadres', 'Grilled Fish', 'Corn', 'Queso Blanco', ARRAY_CONSTRUCT('Lime', 'Guacamole'), 1, 4, 4 UNION ALL
SELECT '2023-09-14T07:30:22.234206Z', 'e5f6a7b8-c9d0-4123-e456-f789a012b345', 'Farolito', 'Carne Asada', 'Flour', 'Habanero Hot Sauce', ARRAY_CONSTRUCT('Lime', 'Radishes', 'Onions'), 2, 3.5, 7 UNION ALL
SELECT '2023-09-14T07:31:23.234207Z', 'f6a7b8c9-d0e1-4234-f567-8901a234b567', 'Taqueria Vallarta', 'Chorizo', 'Whole Wheat', 'Cilantro Lime', ARRAY_CONSTRUCT('Guacamole', 'Cheese'), 4, 3.25, 13 UNION ALL
SELECT '2023-09-14T07:22:14.234198Z', '04861317-09f9-4dc8-a93d-f10e06d78ff0', 'Los Compadres', 'Carnitas', 'Whole Wheat', 'Chipotle Mayo', ARRAY_CONSTRUCT('Lime', 'Radishes'), 3, 3.25, 9.75 UNION ALL
SELECT '2023-09-14T07:23:15.234199Z', '0d8e653f-557b-4dab-a596-7fbee99703e7', 'La Taqueria', 'Grilled Fish', 'Whole Wheat', 'Queso Blanco', ARRAY_CONSTRUCT('Lime', 'Guacamole', 'White beans'), 4, 4, 16 UNION ALL
SELECT '2023-09-14T07:24:16.234200Z', 'ab45cd73-a7ad-4c07-98f8-7d4b98fb6c2d', 'La Taqueria', 'Veggie', 'Flour', 'Salsa Verde', ARRAY_CONSTRUCT('Black beans', 'Cheese'), 1, 2.75, 2.75 UNION ALL
SELECT '2023-09-14T07:25:17.234201Z', '4be5efdf-5ea6-409f-8814-26d9d03b8f85', 'Taqueria Cancun', 'Grilled Fish', 'Whole Wheat', 'Mango Salsa', ARRAY_CONSTRUCT('Radishes'), 4, 4, 16;

By running the following query:

SELECT * FROM taco_orders

You should be able to verify that you’ve successfully created this data:

Now, let’s dive into each of the three methods we mentioned earlier.

1. Snowflake’s REST API

Snowflake has its own REST API that allows you to execute queries and monitor the progress of those executions.

Below, we’ll go over a step-by-step introduction of how to get started and use Snowflake’s REST API, as well as a few examples.

However, let’s first cover the pros and cons of this approach:

Pros

It lets you execute any SQL query that’s available in Snowflake.
It is natively supported by Snowflake.

Cons

If you need to use this endpoint many times, for example when you need to serve data for many users, then this can get expensive fairly quickly.
Supporting high concurrency can be expensive as well.
It may not have the best query time particularly when dealing with complex queries.
When serving user data, you need to set up the logic to filter the data so users only have access to their own data.
Setting up authentication can be cumbersome as we’ll see below.

If your particular use case involves high concurrency and demands low latency, then an alternative solution is probably a better fit for you, as you’ll see later in this post.

1-A. Authentication

To get started with Snowflake’s REST API, you need to authenticate your requests to ensure secure access to your data.

To do this, there are two methods:

Key pair authentication
OAuth

Here, we’re going to cover key pair authentication.

To learn more about OAuth authentication with Snowflake, refer to the Snowflake documentation on OAuth.

Key pair authentication: a brief overview

Key pair authentication is a method used by Snowflake's REST API to verify your identity. It involves two main components:

Public key: Shared with Snowflake and stored in your account.
Private key: Kept secret by you and used to generate authentication tokens.

The process is as follows:

You generate a unique pair of public and private keys.
The public key is assigned to your Snowflake account.
For authentication, you create a token using your private key.
Snowflake validates the token using the associated public key, confirming your identity.

This method ensures secure access, as only the holder of the private key can generate a valid token for their account.

Setting up key pair authentication

1. Generating the Key Pair‍

To generate a public-private key pair, you can use tools like OpenSSL. Here's a basic command to generate an RSA key pair:

openssl genpkey -algorithm RSA -out rsa_key.p8openssl rsa -pubout -in rsa_key.p8 -out rsa_key.pub

This will create a private key (rsa_key.p8) and a public key (rsa_key.pub).

‍

2. Assigning the Public Key to your Snowflake user‍

To assign the public key to your Snowflake user, you can use the ALTER USER command:

ALTER USER <your_username> SET RSA_PUBLIC_KEY='<contents_of_rsa_key.pub>';

Replace your_username with your Snowflake username and contents_of_rsa_key.pub with the actual content of your public key file, excluding the delimiters (i.e., -----BEGIN PUBLIC KEY----- and -----END PUBLIC KEY-----).

After setting the public key, you can verify its assignment by running the DESCRIBE USER command:

DESCRIBE USER <your_username>;

The output should show the RSA_PUBLIC_KEY_FP property, indicating that the public key has been successfully assigned to the user.

Note:

Only security administrators (i.e., users with the SECURITYADMINrole) or higher can alter a user.

‍

3. Verifying the Connection with SnowSQL

‍To verify that the key pair is working as expected, you can use SnowSQL to connect to your Snowflake account:

snowsql -a <account_identifier> -u <user> --private-key-path <path>/rsa_key.p8

You can find your Snowflake account identifier as part of the URL you use to log in to Snowflake. The format is generally account_identifier.snowflakecomputing.com.

If your private key is encrypted, SnowSQL will prompt you for the passphrase you set during key generation.

Running this command should successfully connect you to your Snowflake account if the key pair is correctly set up.

‍

4. Generate Fingerprint and Create JWT in Python

After verifying the connection, you can obtain the fingerprint of your public key by running the DESCRIBE USER command in Snowflake. Look for the RSA_PUBLIC_KEY_FP property in the output.

Then, install the required Python package:

pip install pyjwt cryptography

Now, you can generate a JWT in Python using the following example:

import jwt
from datetime import datetime, timedelta, timezone
from cryptography.hazmat.primitives.serialization import load_pem_private_key
from cryptography.hazmat.backends import default_backend

# Set up Snowflake account and username in uppercase
account = "<account_identifier>".upper()
user = "<user_name>".upper()
qualified_username = account + "." + user

# Use the fingerprint obtained from the 'DESCRIBE USER' command in Snowflake
fingerprint_from_describe_user = "<RSA_PUBLIC_KEY_FP>"

# Get current time in UTC and set JWT lifetime to 59 minutes
now = datetime.now(timezone.utc)
lifetime = timedelta(minutes=59)

# Construct JWT payload with issuer, subject, issue time, and expiration time
payload = {
    "iss": qualified_username + '.' + fingerprint_from_describe_user,
    "sub": qualified_username,
    "iat": now,
    "exp": now + lifetime
}

# Specify the encoding algorithm for JWT
encoding_algorithm = "RS256"

# Read private key from file and encode payload into JWT
with open('<private_key_file_path>', 'rb') as pem_in:
    pemlines = pem_in.read()
    private_key = load_pem_private_key(pemlines, None, default_backend())
token = jwt.encode(payload, key=private_key, algorithm=encoding_algorithm)

# Convert token to string if it's in bytes
if isinstance(token, bytes):
    token = token.decode('utf-8')

# Print the generated JWT token (for testing and demonstration purposes)
print("Generated JWT Token:", token)

Replace account_identifier and user_name with your Snowflake account identifier and username, respectively. Replace RSA_PUBLIC_KEY_FP with the fingerprint obtained from DESCRIBE USER your_username;.

For Java and Node.js examples, please refer to the official Snowflake documentation.

‍

5. Verifying Key Pair Authentication with a cURL Request

To verify that your key pair authentication is set up correctly, you can make a test API request using curl. Here's how to execute a simple SQL query:

curl -i -X POST \
    -H "Content-Type: application/json" \
    -H "Authorization: Bearer <your_generated_jwt_token>" \
    -H "Accept: application/json" \
    -H "User-Agent: myApplicationName/1.0" \
    -H "X-Snowflake-Authorization-Token-Type: KEYPAIR_JWT" \
    -d '{"statement":"select current_version();"}' \
    "https://<account_identifier>.snowflakecomputing.com/api/v2/statements"

Replace your_generated_jwt_token with the JWT token you generated and account_identifier with your Snowflake account identifier.

Run this curl command in your terminal. If everything is set up correctly, you should receive a JSON response from Snowflake containing the results of the SQL query.

Interpreting a Successful Response

A successful response will look like:

HTTP/1.1 200 OK
... (HTTP Headers)
{
  "resultSetMetaData": { ... },
  "data": [["7.33.1"]],
  "code": "090001",
  ...
  "message": "Statement executed successfully.",
  ...
}

Key Points:

HTTP 200 OK: Request was successful.
data: [["7.33.1"]]: Current version of Snowflake, enclosed in a nested array.
message: "Statement executed successfully.": Confirms successful execution.
resultSetMetaData: {...}: Metadata related to the response, indicating additional information is available.

This verifies that your key pair authentication is set up correctly and functional.

1-B. Making API Requests with Snowflake's REST API

After successfully setting up authentication, you can start making API requests for various operations like querying, inserting, and updating data. All these operations use the /api/v2/statements endpoint.

Querying Data

Here's an example using curl to fetch the first 5 rows from the taco_orders table:

curl -i -X POST \
    -H "Content-Type: application/json" \
    -H "Authorization: Bearer <your_generated_jwt_token>" \
    -H "Accept: application/json" \
    -H "User-Agent: myApplicationName/1.0" \
    -H "X-Snowflake-Authorization-Token-Type: KEYPAIR_JWT" \
    -d '{"statement":"SELECT * FROM your_database.your_schema.TACO_ORDERS LIMIT 5;"}' \
    "https://<account_identifier>.snowflakecomputing.com/api/v2/statements"

Replace your_generated_jwt_token and account_identifier with your JWT token and Snowflake account identifier, respectively.

When executed successfully, you should expect an output similar to the following:

HTTP/1.1 200 OK
...
{
  "code": "090001",
  "message": "Statement executed successfully.",
  ...
  "resultSetMetaData": {
    "numRows": 5,
    "rowType": [
      {"name": "TIMESTAMP", "type": "timestamp_ntz"},
      {"name": "ORDER_ID", "type": "text"},
      ...
    ]
  },
  "data": [
    ["1694676744.234208000", "a1234567-b8c9-0123-d456-e789f012a345", ...],
    ...
  ]
}

Other operations

For inserting or updating data, you can use the same endpoint. Simply change the SQL statement in the request payload. For example, to insert a new row into taco_orders, you would change the "statement" value to an appropriate INSERT INTO SQL command:

curl -i -X POST \
    -H "Content-Type: application/json" \
    -H "Authorization: Bearer <your_generated_jwt_token>" \
    -H "Accept: application/json" \
    -H "User-Agent: myApplicationName/1.0" \
    -H "X-Snowflake-Authorization-Token-Type: KEYPAIR_JWT" \
    -d '{"statement":"INSERT INTO <your_database>.<your_schema>.TACO_ORDERS (TIMESTAMP, ORDER_ID, RESTAURANT_NAME, TACO_NAME, TORTILLA_NAME, SAUCE_NAME, TOPPINGS, QUANTITY, TACO_UNIT_PRICE, TACO_TOTAL_PRICE) SELECT CURRENT_TIMESTAMP(), '\''order123'\'', '\''El Buen Sabor'\'', '\''Breakfast'\'', '\''Corn'\'', '\''Salsa Roja'\'', ARRAY_CONSTRUCT('\''Lime'\'', '\''Guacamole'\''), 3, 3, 9;"}' \
    "https://<account_identifier>.snowflakecomputing.com/api/v2/statements"

By following this approach, you can perform a variety of data operations using Snowflake's REST API.

For more details on using Snowflake's SQL API, you can refer to the official Snowflake documentation.

2. Snowflake’s language-specific connectors

Snowflake offers SDKs and connectors for various programming languages such as Python, Java, .NET, and more. The full list of available languages for connectors and drivers can be found in the Snowflake documentation.

Pros

Ease of use: If you're already using one of the languages for which a connector or driver is available, integrating Snowflake into your existing stack becomes straightforward.

Cons

Language dependency: You're tied to the programming language for which you've set up the connector.
Code maintenance: As your queries get more complex, you may need to manage a larger codebase.

Let’s take a look at how this works with Python as an example.

Installation and establishing a connection (Python example)

First, install the Snowflake Python connector package:

pip install snowflake-connector-python

Then, establish a connection to Snowflake and execute a query:

import snowflake.connector

# Create a connection object
conn = snowflake.connector.connect(
    user='<your_user>',
    password='<your_password>',
    account='<your_account>',
    warehouse='<your_warehouse>',
    database='<your_database>',
    schema='<your_schema>'
)

# Create a cursor object
cur = conn.cursor()

# Execute a query
cur.execute("SELECT * FROM taco_orders LIMIT 5")

# Fetch results
for row in cur:
    print(row)

# Close the cursor
cur.close()

# Close the connection
conn.close()

You should see an output like this:

(datetime.datetime(2023, 9, 14, 7, 32, 24, 234208), 'a1234567-b8c9-0123-d456-e789f012a345', 'El Buen Sabor', 'Breakfast', 'Corn', 'Salsa Roja', '[\n  "Lime",\n  "Guacamole"\n]', 3, 3.0, 9.0)
(datetime.datetime(2023, 9, 14, 7, 33, 25, 234209), 'b2345678-c9d0-1234-e567-f890a123b456', 'La Taqueria', 'Barbacoa', 'Flour', 'Chimichurri', '[\n  "Lime",\n  "Radishes"\n]', 2, 3.5, 7.0)
(datetime.datetime(2023, 9, 14, 7, 34, 26, 234210), 'c3456789-d0e1-2345-f678-9012a345c678', 'Taqueria Cancun', 'Veggie', 'Whole Wheat', 'Chipotle Mayo', '[\n  "Black beans",\n  "White beans"\n]', 4, 2.75, 11.0)
(datetime.datetime(2023, 9, 14, 7, 35, 27, 234211), 'd4567890-e1f2-3456-a789-0123b456d789', 'Los Compadres', 'Grilled Fish', 'Corn', 'Queso Blanco', '[\n  "Lime",\n  "Guacamole"\n]', 1, 4.0, 4.0)
(datetime.datetime(2023, 9, 14, 7, 36, 28, 234212), 'e5678901-f234-4567-b890-1234c567e890', 'Farolito', 'Carne Asada', 'Flour', 'Habanero Hot Sauce', '[\n  "Lime",\n  "Radishes",\n  "Onions"\n]', 3, 3.5, 10.5)

Using cursors to navigate query results

Cursors are not just for executing queries; they also keep track of which records you've accessed so far. This is particularly useful when you're working with large datasets and you want to process records in chunks. Here's an example that demonstrates this:

import snowflake.connector

# Create a connection object
conn = snowflake.connector.connect(
    user='<your_user>',
    password='<your_password>',
    account='<your_account>',
    warehouse='<your_warehouse>',
    database='<your_database>',
    schema='<your_schema>'
)

# Create a cursor object
cur = conn.cursor()

# Execute a query to fetch the first 10 taco names and their total prices
cur.execute("SELECT TACO_NAME, TACO_TOTAL_PRICE FROM taco_orders LIMIT 10")

# Fetch and print the first 5 rows
print("First 5 rows:")
for _ in range(5):
    row = cur.fetchone()
    print(row[0], " | ", row[1])

print("\n")

# Fetch and print the next 5 rows
print("Next 5 rows:")
for _ in range(5):
    row = cur.fetchone()
    print(row[0], " | ", row[1])

# Close the cursor
cur.close()

# Close the connection
conn.close()

In this example, we first fetch and print the first 5 rows using fetchone(). The cursor keeps track of this, so when we fetch the next 5 rows, it continues from where it left off.

This way, you can navigate through your query results in a controlled manner, making cursors a useful tool for data retrieval.

Note on Snowpark

For more advanced data engineering tasks, Snowflake also offers Snowpark, a service designed for more native and idiomatic data transformation and analysis. For more information, check out Snowpark's official page.

3. An analytics platform that includes a semantic layer, such as Propel

In the previous sections, we’ve covered first-party methods to query data from Snowflake.

In this section, we’re going to cover the other option of using an analytics platform that includes a semantic layer such as Propel.

With Propel, you can create a blazingly fast GraphQL API on top of your Snowflake data. Let’s take a look at the pros and cons of this approach:

Pros

Low latency: when compared to querying data directly from Snowflake, having a separate caching & storage system makes it much faster to query analytics data, typically in milliseconds.
High concurrency: the serverless nature of this solution allows you to scale your service to a virtually unlimited number of concurrent users/requests.
Better timezone handling: if you have users in different timezones, you may want to query and serve the data in their specific timezones. Propel allows you to handle this with ease.
Better organization of your code: you won’t have to deal with dozens of individual SQL queries, and multi-tenant use cases can be handled with ease.

Cons

Not all Snowflake SQL queries are supported yet.

This is a great option especially if you’re dealing with a situation where low latency and high concurrency is important in your use case - for example when building a customer-facing analytics dashboard.

Let’s take a look at how to set this up with the example data we saw above.

Confirm that you have the example data

Once you set up the example data following the steps explained earlier in the article, you can try running

SELECT * FROM taco_orders

in the correct database and schema to ensure that our example data has been correctly created.

Enable CHANGE_TRACKING

Before you attempt to set up Propel on top of this data, it’s important to enable CHANGE_TRACKING on your table with the following query:

ALTER TABLE TACO_ORDERS SET CHANGE_TRACKING = TRUE;

Step 1: Sign up for Propel

Once you’ve confirmed that you have the example data (or any other data you want to use for this), you can go to propeldata.com and sign up for your Propel account there.

Step 2: Create the Snowflake user, role and warehouse for Propel

Once you sign up and sign in to Propel, we recommend that you create a dedicated Snowflake user, role, and warehouse for Propel. By creating a dedicated role and user, you can apply the principle of least privilege to give it only the privileges it needs. Additionally, by having a dedicated warehouse, you can monitor and control costs, as well as ensure you have the necessary compute to operate the integration.

Copy the script below to a new Snowflake worksheet and select the "All Queries" checkbox.

Note 1

Make sure you replace the values for the user_password, database_name, and schema_name variables with the following values before running the script.

user_password it must be at least 8 characters long, contain at least 1 digit, 1 uppercase letter and 1 lowercase letter
database_name with the name of the Snowflake database where your schema is located, for example ANALYTICS.
schema_name with the database and schema name where your tables are located, for example ANALYTICS.PUBLIC.

Note 2

You will need to run this script with a user that has SECURITYADMIN and ACCOUNTADMIN system-defined roles.

begin;

    use role accountadmin;
    /* Create variables for user, password, role, warehouse, database, and schema (needs to be uppercase for objects) */
    set role_name = 'PROPELLER';
    set user_name = 'PROPEL_USER';
    set warehouse_name = 'PROPELLING';

    /* Must be at least 8 characters long, contain at least 1 digit, 1 uppercase letter and 1 lowercase letter */
    set user_password = '';               /* Replace with a strong password */
    set database_name = 'ANALYTICS';      /* Replace with your Snowflake database name */
    set schema_name = 'ANALYTICS.PUBLIC'; /* Replace with your Snowflake schema name */

    /* Grant sysadmin role access to the database */
    grant usage,modify
    on database identifier($database_name)
    to role sysadmin;

    /* Grant sysadmin role access to the schema */
    grant usage,modify
    on schema identifier($schema_name)
    to role sysadmin;

    /* Change role to securityadmin for user / role steps */
    use role securityadmin;

    /* Create a role for Propel */
    create role if not exists identifier($role_name);
    grant role identifier($role_name) to role SYSADMIN;

     /* Change role to sysadmin for warehouse and database steps */
    use role sysadmin;

    /* Create a warehouse for Propel */
    create warehouse if not exists identifier($warehouse_name)
    warehouse_size = xsmall
    warehouse_type = standard
    auto_suspend = 60
    auto_resume = true
    initially_suspended = true;

    /* Change role to securityadmin for user / role steps */
    use role securityadmin;

    /* Create a user for Propel */
    create user if not exists identifier($user_name)
    password = $user_password
    default_role = $role_name
    default_warehouse = $warehouse_name;

    grant role identifier($role_name) to user identifier($user_name);

    /* Change role to accountadmin for warehouse and database steps */
    use role accountadmin;

    /* Grant Propel role access to the warehouse */
    grant usage, monitor
    on warehouse identifier($warehouse_name)
    to role identifier($role_name);

    /* Grant Propel role access to the database */
    grant usage, monitor
    on database identifier($database_name)
    to role identifier($role_name);

    /* Grant Propel role access to the schema */
    grant create procedure, create stage, create task, create stream, usage
    on schema identifier($schema_name)
    to role identifier($role_name);

    /* Grant Propel role select on all tables in the schema */
    grant select on all tables
    in schema identifier($schema_name)
    to role identifier($role_name);

    /* Grant Propel role select on all future tables in the schema */
    grant select on future tables
    in schema identifier($schema_name)
    to role identifier($role_name);

    grant execute task on account
    to role identifier($role_name);

commit;

Step 3: (Optional) Configure Snowflake Network Policy

If you have defined a Snowflake Network Policy, you need to update it to include the Propel IP address by following the instructions on modifying network policies.

Propel IP Addresses:

3.17.239.162
3.15.73.135
18.219.73.236

Step 4: Create a Data Pool in the Propel Console

Now that you have created the Snowflake user, role, and warehouse, you can create a Data Pool (Propel’s propriety storage system for fast analytical queries) in the Propel Console.

First, on the left hand side menu, click on Data Pools. Then click on the plus sign (+) to create a new Data Pool.

Click Snowflake in the next screen to select it as a data source.

Click Add new credentials:

Then enter a unique credentials name, as well as account, database, schema, warehouse, and role, user, and password.

Note:

Your Snowflake account should be in the following format "accountId.region.cloud", for example: "fn25463.us-east-2.aws". Do not include the snowflakecomputing.com domain.

When you’ve added all of those details, click Create and test Credentials.

Then you should see a “Connected” status message:

Continue with the setup flow, and then select the table as well as the columns you want to use.

You can use go with default (all columns selected):

Then select Primary timestamp to be TIMESTAMP and Unique ID to be ORDER_ID.

Click Next and make sure it’s set up correctly.

Then you can click Preview Data to make sure your data is loaded correctly.

‍

Step 5: Create Metrics

Finally, we’ll need to create Metrics for you to be able to set up a GraphQL you can use to query your data.

On the left hand side, click Metrics and then click Create New Metric.

Then fill out the required information like so:

As you can see, here, we’re giving it:

A unique name (Taco Soft Revenue)
A description (Metric to keep track of taco revenue)
Data Pool (Taco Test - or whatever name you chose earlier)
Metric type (SUM)
Measure (TACO_TOTAL_PRICE)

This Metric is designed to keep track of taco revenue.

Then select the columns you want to use as dimensions, which you can use to filter the Metric data.

Click Create, and you’re almost ready to start testing your GraphQL API!

Step 6: Test using the GraphQL API on top of your Snowflake data

Once you set up the Metric, click Playground. There, you can test using a GraphQL API that’s automatically set up on top of your Metric.

For example, you can click Leaderboard and create a leaderboard-like visualization with a single API call to determine, say, top-performing restaurants and their revenue.

You can see the associated query on the right-hand side.

You can learn more about how to use this GraphQL API, as well as check examples in a few different languages on the Query your data page on our documentation.

‍

Conclusion

In this post, we’ve covered three distinct methods for creating a Snowflake API on top of your own data:

Snowflake's REST API
Snowflake’s language-specific connectors
An analytic platform like Propel

Snowflake's REST API is natively supported but can be expensive for high concurrency, has complex authentication setup, and may not offer the best query times. Language-specific connectors are easy to integrate but tie you to a specific language and can complicate code maintenance. Propel excels in low latency and high concurrency, offers better timezone handling, and simplifies multi-tenant application development, making it particularly suited for customer-facing analytics.

We recommend choosing the method that best aligns with your project's specific needs.

‍

Any feedback?

If you have any feedback on this post or on Propel in general, please feel free to let us know at yksu@propeldata.com.

‍

YK Sugi

•

Senior Developer Advocate

Snowflake Concepts

Snowflake API: Comprehensive Guide to 3 Methods With Examples

This is some text inside of a div block.

Heading 1

Heading 2

Heading 3

Heading 4

Heading 5

Heading 6

Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur.