Build completely private APIs in Snowflake

If you've stored data in Snowflake then chances are you've also got an enterprise application that needs to access it, and a common way to do that is through an HTTP API. This is your organization's private data though, so making it available through an API that is accessible on the public internet is not the best approach.

In this guide I'm going to walk you through how to build, deploy, and host a private custom API powered by Snowflake.

The API will not have an endpoint exposed to the Internet. Your application will, instead, access this API over private endpoints that are only available within your enterprise's VPC and other private environments.

The example will build a reporting endpoint (in Python) to return data from the TPC-H dataset already included in your Snowflake account.

Prerequisites

• Snowflake Account in an AWS commercial region.
  • Privileges necessary to create a user, database, warehouse, compute pool, repository, network rule, external access integration, and service in Snowflake.
  • Privileges necessary to access the tables in the SNOWFLAKE_SAMPLE_DATA.TPCH_SF10 database and schema.
  • Access to run SQL in the Snowflake console or SnowSQL
• GitHub Account with credits for Codespaces + basic experience using git.
• Ockam Account to securely expose your private API
• Intermediate knowledge of Python

Setup Snowflake

Setup development environment

The code in this guide comes from a lab that Brad Culberson and the team at Snowflake deliver to customers. We're going to use GitHub Codespaces as our development environment to make any changes we need to the code and to package it for deployment. Here's how to set it up:

• Visit the sfc-gh-bculberson/lab_data_api_python repo.
• Press .… or click the green <> Code button choose Codespaces and click Create codespace on main.

GitHub will then load a new Codespace environment with the code from this repository.

Python app code

In the src/connector.py file you'll find the code that defines the API and generates responses for a number of endpoints that are served under the /connector path. We're going to walk through just one of the endpoints as and example of how to retrieve data out of Snowflake and return it as JSON.

Build & publish app container

In Codespaces click the hamburger menu icon (☰) > Terminal > New Terminal. Choose Continue working in GitHub Codespaces and select the smallest instance possible (at the time of writing that's 2 cores).

Run in Snowflake

Switch back to your Snowflake console or SnowSQL and we will configure Snowpark to run our container.

Setup Ockam

It's now time to setup Ockam to allow you to securely connect your private systems. We're going to run the following commands in a terminal on your local workstation.