How to build a Snowflake API

In this post, you’ll see exactly what it takes to create and query an API on top of your Snowflake data warehouse using Propel’s blazing-fast Serverless Analytics API Platform. In five easy steps, you’ll connect to Snowflake, define a Metric, and query it with our GraphQL API.

Photo: Propel

Looking for a few different ways to build an API on top of Snowflake? If so, take a look at our other blog post on the topic, Snowflake API: Comprehensive Guide to 3 Methods With Examples.

Are you looking for a way to build a fast, secure API on top of your Snowflake data warehouse? Propel's Serverless Analytics API Platform offers a fast and cost-effective way to create an API on top of your Snowflake data that is secure, performant, and low latency. In this post, we will walk you through the five easy steps to connecting your Snowflake Data Source, syncing your tables, defining Metrics, and querying your API with JavaScript, Python, Java, Go, Ruby, and C#/.NET. Let's get started!

Why do you need an API on top of Snowflake?

Snowflake typically has the most complete data organizations have. It centralizes data from all the different data silos, whether internal databases or SaaS applications. It is also the right platform to clean and transform your data, making it optimal for visualization and consumption.

When using the data internally for employees, you can either query the data directly with SQL or through a BI tool like Looker or Tableau; therefore, you don’t typically need an API.

You need an API on top of Snowflake when you are building customer-facing web or mobile applications that include:

User-facing dashboards
Product usage and metering
Analytics APIs
Email Reporting
Customer data sharing

These use cases have unique requirements and require a secure and performant data serving layer with low latency and high concurrency to provide a seamless user experience.

What are the requirements for an API on top of Snowflake?

When looking to build an API on top of Snowflake to power data applications and features powered by large-scale analytical data, you should consider the following requirements:

⚡️ Low latency → Customers expect fast, snappy product experiences. While internal employees may be okay waiting 45 seconds or even minutes for a query to run, this latency is a no-go for customer-facing products where the data analysis is part of the core product experience
🌊 High concurrency → Products need to support thousands or millions of users. Because data products ultimately serve customers rather than employees, they must support a dramatically higher number of concurrent requests than what internal data tools are designed to handle. And they must do so seamlessly and cost-effectively.
💰 Cost-effectiveness → Data has a fundamental tradeoff between flexibility, cost, and performance. The API should make it easy for users to understand and then make this tradeoff for different use cases.
🔐 Multi-tenant security layer → Each end-user can see only their own data. In SaaS and consumer products, end-users access their own data from web or mobile applications. This requires a multi-tenant security layer for business-to-business or consumer environments, not just employee-centric role-based access control.

Querying the warehouse directly from customer-facing applications can be slow, costs can get out of control, and it still requires substantial backend service logic to serve this data securely to the frontend.

Does Snowflake have an API?

Yes, Snowflake offers a pass-through SQL API. It is a REST API that takes SQL statements and executes them against the data warehouse. It is useful if you want to query SQL over REST, but it does not provide any additional abstraction, optimization, or security features to power secure, performant, low-latency customer-facing applications.

How do I build an API on top of Snowflake?

To build a performant Snowflake API to power your customer-facing web and mobile apps, you can use Propel, a serverless analytics API platform built on top of Snowflake.

Propel provides the cloud infrastructure, scalability, and security required to power data applications at a massive scale while giving you the flexibility to define the metrics you want to query. You can build your first Snowflake API in minutes by following these five easy steps:

1. Connect your Snowflake Data Source

To set up a Snowflake Data Source for Propel, follow our How to set up a Snowflake Data Source guide. If you need a Propel account, book a demo, and we'll onboard you as soon as possible. Then, enter your Snowflake connection details in Propel's Console to create the Data Source.

Propel will connect to Snowflake and run a series of checks to ensure the connection has the correct permissions.

*An animated screen capture of the Propel Console, showing how to create a Snowflake Data Source and the numerous checks that confirm the connection is working.*

2. Sync a table from Snowflake

Once connected, you can see your Snowflake tables in Propel. You can create a Data Pool for the table you want to serve data from. The Data Pool is a synced cache of your table that Propel optimizes for serving data. Propel’s built-in syncing eliminates the need for data pipelines, ETLs, or data engineering.

When creating the Data Pool, you must specify the table, its primary timestamp column (typically when the record was created), and a tenant ID if tenant-level access control is needed for your application.

*An animated screen capture of the Propel Console, showing how to create a Data Pool from a table within a Data Source.*

3. Define Metrics

With your data in a Propel Data Pool, you can define the Metrics you want to use in your web or mobile application. Metrics are the analytics measures you want to track, and they can be of different types: SUM, COUNT, COUNT_DISTINCT, MIN, MAX, and AVERAGE.

Once you configure your Metric settings in Propel, you can select the columns you want to expose as dimensions. Dimensions are different ways to filter Metric data to uncover insights. When defining a customer-facing Metric, you can control which columns are exposed to your customer via Dimensions.

‍

4. Query your Snowflake API

Propel gives you a GraphQL API on top of Snowflake that is always highly available and performant in just a few easy steps. Once you have your Metric defined, you can try Propel’s GraphQL Explorer to query the Metric data in the format you are going to use: a time series, a counter, or a leaderboard.

At the top-right of the GraphQL Explorer, you'll see the quick response times Propel's high-performance GraphQL API provides. In this example, querying hundreds of thousands of records takes about 500 milliseconds (end to end).

*An animated screen capture of Propel's GraphQL explorer, showing how to query. Metric using the GraphQL API with various time granularities and filters.*

5. Build your analytics product using Propel’s GraphQL API for Snowflake

The Metric you defined previously is now available via the Snowflake GraphQL API. Using the technology stack of your choice, you can build in-product analytics into your app. You didn’t have to do any data engineering beyond loading your data to Snowflake.

*An animated screen capture of Visual Studio Code, showing how to use Propel's GraphQL API, React, and ECharts to query and visualize Metric data from Snowflake.*

API Authentication

Once you have your Snowflake API up and running with Propel, you’ll need to authenticate your requests. Propel uses OAuth 2.0 client credentials flow to authenticate your requests to the API securely. This flow requires you to provide a client ID and a client secret to authenticate your requests. These credentials are generated in the Propel Console when you create an Application. To learn more, read our GraphQL Authentication guide.

‍

The examples below show how to query your new Snowflake API using different programming languages, frameworks, and tools.

Example 1: The GraphQL query to your Snowflake API

An example GraphQL query on top of Snowflake. The first code block contains the GraphQL query and the second code block contains the JSON variables to include. When issuing the GraphQL request, ensure you replace METXXXXXXXXXXXXXXXXXXXXXXXXXX with your Metric ID and include your access token in the “Authorization” header as a bearer token.

Example 2: Query your Snowflake API with curl

An example of a Snowflake API request using the command-line tool curl. Ensure you replace ACCESS TOKEN with your access token and METXXXXXXXXXXXXXXXXXXXXXXXXXX with your Metric ID.

Example 3: Query your Snowflake API with JavaScript (Node.js)

An example of a Snowflake API request using JavaScript. This example uses the graphql-request library to issue the actual GraphQL request. Install the dependency:

Then, copy the following sample to a file, index.js, replace ACCESS TOKEN with your access token, and replace METXXXXXXXXXXXXXXXXXXXXXXXXXX with your Metric ID.

Finally, run the example:

Example 4: Query your Snowflake API with React

An example of a Snowflake API request using JavaScript, React, and Chart.js. This example uses react-chartjs-2, a wrapper for Chart.js. Install the dependencies:

Then, copy the following sample to a file, TimeSeries.jsx, replace ACCESS TOKEN with your access token, and replace METXXXXXXXXXXXXXXXXXXXXXXXXXX with your Metric ID.

Finally, import your component and mount it in your React app:

Example 5: Query your Snowflake API with Python

An example of a Snowflake API request using Python. This example uses Python’s built-in urllib.request library for issuing the GraphQL request, so it doesn’t require additional dependencies; however, in production, you could use a library like gql for stronger typing.

Copy the following sample to a file, main.py, replace ACCESS TOKEN with your access token, and replace METXXXXXXXXXXXXXXXXXXXXXXXXXX with your Metric ID.

Finally, run the example:

Example 6: Query your Snowflake API with Java

An example of a Snowflake API request using Java. This example uses Java’s built-in HttpClient and constructs JSON manually, so it doesn’t require additional dependencies; however, in production, you should use a library like Jackson for constructing JSON. Additionally, for stronger typing, you could use Apollo’s Kotlin-based GraphQL client.

Copy the following sample to a file, Main.java, replace ACCESS TOKEN with your access token, and replace METXXXXXXXXXXXXXXXXXXXXXXXXXX with your Metric ID.

Finally, run the example, either from your IDE or from the command-line with java.

Example 7: Query your Snowflake API with Go

An example of a Snowflake API request using Go. This example uses the Go standard library, so it doesn’t require additional dependencies; however, in production, you could use a library like shurcooL/graphql or Khan/genqlient for stronger typing.

Copy the following sample to a file, main.go, replace ACCESS TOKEN with your access token, and replace METXXXXXXXXXXXXXXXXXXXXXXXXXX with your Metric ID.

Finally, run the example:

Example 8: Query your Snowflake API with Ruby

An example of a Snowflake API request using Ruby. This example uses the Ruby standard library, so it doesn’t require additional dependencies; however, in production, you could use a library like GitHub’s own graphql-client for stronger typing.

Copy the following sample to a file, main.rb, replace ACCESS TOKEN with your access token, and replace METXXXXXXXXXXXXXXXXXXXXXXXXXX with your Metric ID.

Finally, run the example:

Example 9: Query your Snowflake API with C#/.NET

An example of a Snowflake API request using C#/.NET. This example uses the C#/.NET standard library, so it doesn’t require additional dependencies; however, in production, you could use a library like graphql-client for stronger typing.

Copy the following sample to a file, Program.cs, replace ACCESS TOKEN with your access token, and replace METXXXXXXXXXXXXXXXXXXXXXXXXXX with your Metric ID.

Finally, run the example either from your IDE or from the command line.

How to create a Postman collection for your Snowflake API

Below is a step-by-step guide on how to create a Postman collection for your new Snowflake API:

Open Postman and create a new collection.
Create a new request in the collection and name it "Snowflake API".
In the “Authorization” section of your collection, go to “Configure new Token” and follow these steps:
a. Select Type as “OAuth 2.0”.
b. Select Add auth data to “Request headers”.
c. Header prefix "Bearer"
d. On “Configure New Token” add the following values:
   • Token name: “{{$isoTimestamp}}”
    • Grant Type: “Client Credentials”
    • Access Token Token URL: "https://auth.us-east-2.propeldata.com/oauth2/token"
    • Client ID: The Client ID from your Propel Application created in the Propel Console.
    • Client Secret: The Client secret from your Propel Application created in the Propel Console.
    • Scope: “metric:query”
    • Client Authentication: “Send as Basic Auth header”
e. Make sure you have “Auto-refresh access token” enabled.
In the request URL field, enter the endpoint https://api.us-east-2.propeldata.com/graphql.
Select the "POST" method in the dropdown menu.
In the body of the request, select the "GraphQL" option from the dropdown menu.
In the query field, enter the GraphQL query:

In the variables field, enter the variables:

Save the request and run it to make the GraphQL request.

Here is the resulting Postman collection in JSON:

Conclusion

In this post, we have explored the steps to build an API on top of your Snowflake data warehouse using Propel's Serverless Analytics API Platform. We have discussed why an API is necessary for customer-facing applications, the requirements for an API on top of Snowflake, and the five steps to build a Snowflake API: connect Snowflake, sync your tables, define Metrics, query the API, and build your data app.

Propel provides the infrastructure, scalability, and security required to power data applications while giving you the flexibility to define the metrics you want to query. Building an API on top of Snowflake with Propel is a fast, secure, and cost-effective way to power customer-facing web and mobile applications.

Nico Acosta

•

Co-founder and CEO at Propel

How-to

How to stream PostgreSQL CDC to Kafka and use Propel to get an instant API

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.