Snowflake

This is a comprehensive guide for Snowflake developers who want to integrate Coherent Spark services into their Snowflake environment.

This article demonstrates how to bring your existing Excel logic into Snowflake using Coherent Spark.

You’ll learn how Coherent Spark (or simply Spark) lets you run complex Excel models as API services inside Snowflake, so you can tap directly into your existing data. This guide assumes some familiarity with Snowflake and walks you through prerequisites, setup, and common practices. It covers signing into both Coherent Spark and Snowflake, using the Snowflake CLI, and configuring network rules for external access. You’ll also find examples of calling Spark services with the requests library and the Coherent Spark Python SDK. The article wraps up with advanced use cases and links to additional resources.

Why would you want to do this? In an ideal world, your modeling logic should live where your data lives. But while Snowflake is powerful for data storage and processing, building complex functions directly in Snowflake can be challenging. On the other hand, most business users prefer Excel for modeling because it’s intuitive and flexible. Coherent Spark bridges that gap by transforming your Excel models into scalable API services that integrate coherently with Snowflake.

After reading this guide, you’ll be able to run your externalized Excel functions directly from within Snowflake.

Prerequisites

Before you begin, make sure you have:

• Access to a Snowflake account with ACCOUNTADMIN or similar privileges.

• Access to a Coherent Spark account with read and execute permissions.

• Basic familiarity with Python and SQL.

• Snowflake CLI installed (optional but recommended). Most actions can be performed in the Snowflake UI, but some are better suited for the Snowflake CLI.

Getting started

We recommend working within a testing or development environment for both Snowflake (e.g., Snowflake warehouse, which should allow you to safely run queries and procedures without affecting your production environment) and Coherent Spark during this setup.

Let’s start by gathering the necessary credentials and information to work with both Snowflake and Coherent Spark.

Sign in to Spark

Coherent Spark is a platform that allows you to run Excel models in a cloud environment, exposing them as API services.

Sign into Spark and get the following:

• Base URL and tenant, for example https://excel.myenvironment.coherent.global/mytenant.

• User credentials:

  • Authorization - API keys or

  • Authorization - Bearer token

• Service URI:

  • folder - the folder name containing the Spark model

  • service - the name of the Spark model

  • version - and optionally the semantic version number.

Roughly speaking, a folder acts as a container that holds one or more services. Think of folders as a way to organize and group related services together. Each service represents an Excel model that has been converted into a Spark service. Services can exist in multiple versions, representing different iterations or updates of that service over time. When interacting with a Spark service, you are always working with a specific version – the latest one by default. You can explicitly specify an older version if you need to work with a previous iteration of the service.

You should also confirm your service is working in the API Tester. Find more information about Spark services in the How to: Create a Spark service.

Sign in to Snowflake

Using the right credentials, sign in to Snowflake, which will take you to the Snowflake Dashboard by default. If you are an ACCOUNTADMIN (or similar role), you will be able to see all the projects, including worksheets and notebooks, in the left sidebar.

Confirm in your Catalog that you are able to see the database, schema, and tables you intend to use. Visit the Snowflake documentation page to learn more.

Two methods to access Coherent Spark within Snowflake

Within Snowflake, you will utilize notebooks to create access to Spark. There, we describe two ways to achieve this:

• Python library: requests OR,

• Coherent Spark Python SDK: cspark.

Python with request

To start, click the top-right button to create a new notebook. You can also use the provided sample Jupyter notebook at the bottom of this page for a quick start guide. Once you are within the notebook environment, ensure the requests library is installed (ideally, its latest version). You can check this from the Packages menu.

Once this is installed, your environment will restart, and you can proceed to the next phase.

Coherent Python SDK

In order for Snowflake notebook to gain access to Coherent's Python SDK, we need to first use the Snowflake CLI tool to grab the SDK ZIP file and upload it to the Snowflake database of choice. Once the ZIP file is uploaded to the selected Snowflake database, you can then add the stage package to the current notebook environment.

Snowflake CLI

The Snowflake CLI is a command-line tool that allows you to interact with Snowflake from the terminal. It’s tailored for developers and can be used to perform most actions that are usually done in the Snowflake UI.

Follow the instructions on the Snowflake CLI GitHub repository to install it. Once installed, you can run snow to see the help message as shown below.

Snowflake CLI displaying help message

You will need to create a new connection with snow connection add. It will prompt a series of inputs that you need to provide. This will ultimately create a config.toml file with the appropriate credentials. In the example below, we are using a connection named coherent_dev to connect to the coherent_db database in the coherent_wh warehouse.

# config.toml
default_connection_name = "coherent_dev"

[connections.coherent_dev]
account = "<your-account>"
user = "<your-username>"
password = "<your-password>"
warehouse = "coherent_wh"
database = "coherent_db"
schema = "PUBLIC"

[cli.logs]
save_logs = true
path = "<your-logs-path>"
level = "info"

The snowflake folder is typically located in ~/Library/Application Support/snowflake on macOS. However, you may choose any other location and set it up accordingly (e.g., snow --config-file="/path/to/config.toml").

You may confirm your connection by running snow connection test or snow connection list. See example below:

$ snow connection test
+---------------------------------------------------------+
| key             | value                                 |
|-----------------+---------------------------------------|
| Connection name | coherent_dev                          |
| Status          | OK                                    |
| Host            | <your-account>.snowflakecomputing.com |
| Account         | <your-account>                        |
| User            | <your-username>                       |
| Role            | ACCOUNTADMIN                          |
| Database        | coherent_db                           |
| Warehouse       | coherent_wh                           |
+---------------------------------------------------------+

Keep in mind to set the default connection with snow connection set-default coherent_dev.

Now let’s use the CLI to push a version of the Coherent Spark Python SDK to Snowflake as a stage package. Make sure to create a stage first. In our example, we are using the stage coherent_packages.

1. Create a new Snowpark package from the Python SDK releases.

   snow snowpark package create cspark

2. Upload the ZIP file to the stage (i.e., coherent_packages).

   snow snowpark package upload --file="cspark.zip" --stage="coherent_packages"

3. Add an environment.yml for additional settings along with the dependencies in your notebook. Remember to add the dependency to Anaconda Packages in order for the cspark SDK to operate correctly.

   name: coherent_app
   channels:
     - snowflake
   dependencies:
     - httpx=0.27.0 # 1 of 2 deps of cspark
     - dataclasses=* # 2 of 2 deps of cspark
     - requests=*

Snowflake + Coherent Spark

Snowflake needs to be able to communicate with Coherent Spark. This is done by setting up network rules and external access integrations. By default, outbound traffic is blocked.

Let’s start a notebook so we can leverage Snowpark to execute Python and SQL code alongside your data. Snowflake Labs has a great collection of notebooks that you can use to get started. The Access External Endpoints notebook is a good starting point.

Set up external access integration

To enable communication between Snowflake and Coherent Spark, you need to set up network rules. Here’s an example of a network rule cs_network_rule for Coherent Spark:

-- SQL
CREATE OR REPLACE NETWORK RULE cs_network_rule
   MODE = EGRESS
   TYPE = HOST_PORT
   VALUE_LIST = ('keycloak.myenvironment.coherent.global', 'excel.myenvironment.coherent.global');

In the example above, the network rule allows the following services to be accessed:

• https://keycloak.{environment}.coherent.global for Keycloak (Coherent’s identity provider).

• https://excel.{environment}.coherent.global for Excel calculations (Spark’s main Excel engine).

The {environment} is the environment where Coherent Spark services are hosted.

After creating the network rule, set up the external access integration:

CREATE OR REPLACE EXTERNAL ACCESS INTEGRATION cs_network_rule
    ALLOWED_NETWORK_RULES = (cs_network_rule)
    ENABLED = true;

Finally, locate the notebook Settings and enable the external access integration for the rule you just created, as illustrated below.

Call a Spark service

Always store your Coherent Spark credentials in a secure secrets manager, such as Snowflake Secrets Manager. The sample code above is only meant for illustration purposes.

Avoid hardcoding any credentials in your code or configuration files. Instead, retrieve them securely at runtime to protect sensitive data and maintain compliance with best security practices.

Depending on the method you have chosen above, we have two different code snippets:

Python with requests

Within a Python-enabled cell, let’s call a Spark service using the Python requests library.

requests is an elegant and simple HTTP library for Python, which is already installed in the Snowpark environment (see Snowflake Anaconda Channel for more details).

Here’s a simple example of calling a Coherent Spark service using the requests library. In the example, we are calling the volume-cylinder service with some inputs.

import requests

base_url = 'https://excel.uat.us.coherent.global/my-tenant'
token = 'your-access-token-here'
folder, service = 'my-folder', 'volume-cylinder'
service_uri = f'folders/{folder}/services/{service}'

url = f'{base_url}/api/v3/{service_uri}/execute'
headers = {'Authorization': f'Bearer {token}', 'Content-Type': 'application/json'}
inputs = {'radius': 4, 'height': 2}
metadata = {'call_purpose': 'Snowflake Integration', 'source_system': 'Snowpark'}
payload = {'request_data': {'inputs': inputs}, 'request_meta': metadata}

response = requests.post(url, headers=headers, json=payload)
if response.ok:
    data = response.json()
    print(data['response_data']['outputs'])
else:
    print("Error:", response.status_code, response.text)

To make things even easier, we’ve also included a Snowpark-adapted Jupyter Notebook that developers can use to accelerate the integration process and quickly experiment with running Excel-based models in Snowflake.

Intro to Coherent Spark Python Request.ipynb

6KB

Open

Coherent Python SDK

cspark is Coherent Spark's Python SDK that provides convenient access to its APIs. Since the SDK is not included in the Snowflake Anaconda Channel, we added it manually earlier as a stage package, that is, by importing it as a pre-installed package.

Here’s the same example of calling the volume-cylinder service using the SDK this time.

import cspark.sdk as Spark

base_url = 'https://excel.uat.us.coherent.global/my-tenant'
token = 'your-access-token-here'
folder, service = 'my-folder', 'volume-cylinder'
service_uri = f'folders/{folder}/services/{service}'

inputs = {'radius': 4, 'height': 2}
metadata = {'call_purpose': 'Snowflake Integration', 'source_system': 'Snowpark'}

try:
    spark = Spark.Client(base_url=base_url, token=token, logger=False)
    with spark.services as svc:
        response = svc.execute(service_uri, inputs=inputs, response_format='original', **metadata)
        print(response.data['response_data']['outputs'])
except Exception as e:
    print('Error:', e.message)

To make things even easier, a Snowpark-adapted Jupyter Notebook (see attached file below) is included for developers to accelerate the integration process and quickly experiment with running Excel-based models in Snowflake.

Intro to Coherent Spark Coherent Spark SDK.ipynb

9KB

Open

What’s next?

Now that you have a basic understanding of how to call Coherent Spark services from Snowflake, you can start introducing more Snowflake native features (e.g., UDFs, stored procedures, etc.) into the picture. Remember, for your bulk processing needs, Spark supports both synchronous and asynchronous batch processing.

For more details on the advanced use cases, visit the SDK documentation. Some of the topics covered in the guide are:

• How-to: Execute records sequentially (1 record at a time).

• How-to: Execute batch of records synchronously (up to 100 records at a time).

• Asynchronous batch processing (high-throughput processing).