# Snowflake {% hint style="warning" %} This is a comprehensive guide for [Snowflake developers](https://www.snowflake.com/en/developers/) who want to integrate [Coherent Spark](https://docs.coherent.global/) services into their Snowflake environment. {% endhint %} This article demonstrates how to bring your existing Excel logic into [Snowflake](https://docs.snowflake.com/) using [Coherent Spark](https://docs.coherent.global/) . 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 logging 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](https://github.com/Coherent-Partners/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) {% hint style="info" %} Most actions can be performed in the Snowflake UI, but some are better suited for the Snowflake CLI. {% endhint %}
### Getting started Let’s start by gathering the necessary credentials and information to work with both Snowflake and Coherent Spark. {% hint style="info" %} 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. {% endhint %} #### Log in to Coherent Spark Coherent Spark is a platform that allows you to *run* Excel models in a cloud environment, exposing them as API services. [Log in to your Coherent Spark account](https://docs.coherent.global/getting-started-in-5-minutes) to get the following: * Base URL (e.g., `https://excel.uat.us.coherent.global/my-tenant`) * User credentials: * [Authorization - API keys](/spark-apis/authorization-api-keys.md) or * [Authorization - Bearer token](/spark-apis/authorization-bearer-token.md) or * [Broken mention](broken://pages/Dx1VW4Wsqbw72hKR5spG) * Service [Execute API (v4)](/spark-apis/execute-api/execute-api-v4.md#uri) : * `folder` - the folder name containing the Spark model * `service` - the name of the Spark model * `version` - and optionally the semantic version (also known as *revision 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. Find more information about Spark services in the [How to: Create a Spark service](/build-spark-services/how-to-create-a-spark-service.md). #### Log in to Snowflake Using the right credentials, [log into your Snowflake account](https://docs.snowflake.com/en/user-guide/connecting), 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](https://docs.snowflake.com/en/user-guide/opencatalog/overview) that you are able to see the database, schema, and tables you intend to use. Visit the [Snowflake documentation](https://docs.snowflake.com/en/user-guide-getting-started) page to learn more. #### 2 Different ways of accessing Coherent within Snowflake Within Snowflake, you will utilize notebook to create access to Coherent Spark. There, we describe 2 ways to achieve this: 1. Using Python library: [request](https://pypi.org/project/requests/) 2. Or using Coherent Python SDK: [cspark](https://github.com/Coherent-Partners/spark-python-sdk). {% tabs %} {% tab title="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 **requests** library is installed (ideally, its latest version). \ ![](/files/eafXmNKTpbJfX61fKmW2) Once this is installed, your environment will restart, and you can proceed to the next phase. {% endtab %} {% tab title="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 workbook 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](https://github.com/snowflakedb/snowflake-cli) 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 by `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. ```toml # config.toml default_connection_name = "coherent_dev" [connections.coherent_dev] account = "" user = "" password = "" warehouse = "coherent_wh" database = "coherent_db" schema = "PUBLIC" [cli.logs] save_logs = true path = "" level = "info" ``` {% hint style="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"`). {% endhint %} 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`\ \ Let’s now use the CLI to push a version of the [Coherent Spark Python SDK](https://github.com/Coherent-Partners/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](https://github.com/Coherent-Partners/spark-python-sdk/tags). ``` 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. Please remember to add dependency to Anaconda Packages in order for cspark SDK to operate correctly. ```yaml name: coherent_app channels: - snowflake dependencies: - httpx=0.27.0 # 1 of 2 deps of cspark - dataclasses=* # 2 of 2 deps of cspark - requests=* ``` {% endtab %} {% endtabs %} ### 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](https://github.com/Snowflake-Labs/snowflake-demo-notebooks) has a great collection of notebooks that you can use to get started. The [Access External Endpoints](https://github.com/Snowflake-Labs/snowflake-demo-notebooks/tree/main/Access%20External%20Endpoints) one is a good starting point. #### Setting 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 -- SQL CREATE OR REPLACE NETWORK RULE cs_network_rule MODE = EGRESS TYPE = HOST_PORT VALUE_LIST = ('keycloak.uat.us.coherent.global', 'excel.uat.us.coherent.global'); ``` In the example above, the network rule allows the following services to be accessed: * `https://keycloak.{region}.coherent.global` for Keycloak (Coherent’s Identity Provider) * `https://excel.{region}.coherent.global` for Excel calculations (Coherent Spark’s main Excel engine) {% hint style="info" %} The `{region}` is the environment where Coherent Spark services are hosted. In the SQL example above, the region is `uat.us`, meaning UAT environment in the United States. {% endhint %} After creating the network rule, set up the external access integration: ```sql 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.

Enable external access integration

#### Calling a Spark service Depending on the method you have chosen above, we have 2 different code snippets: {% tabs %} {% tab title="Python with Requests" %} Within a Python-enabled cell, let’s call a Spark service using the Python `requests` library. [requests](https://pypi.org/project/requests/) is an elegant and simple HTTP library for Python, which is already installed in the **Snowpark** environment (see [Snowflake Anaconda Channel](https://repo.anaconda.com/pkgs/snowflake) 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. ```python 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) ```

Calling Spark using the requests library

To make things even easier, we’ve also included a **Snowpark-adapted Jupyter Notebook** (see attached file below) that developers can use to accelerate the integration process and quickly experiment with running Excel-based models in Snowflake. {% file src="/files/KNBfuyFZLUCY5EZAqqr7" %} {% endtab %} {% tab title="Coherent Python SDK" %} [cspark](https://github.com/Coherent-Partners/spark-python-sdk) 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](https://docs.snowflake.com/en/user-guide/ui-snowsight/notebooks-import-packages).

SDK importability test

Here’s the same example of calling the `volume-cylinder` service using the SDK this time. ```python 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) ```

Calling Spark using the SDK

To make things even easier, we’ve also included a **Snowpark-adapted Jupyter Notebook** (see attached file below) that developers can use to accelerate the integration process and quickly experiment with running Excel-based models in Snowflake. {% file src="/files/R9Lli7JHlgQuva3ydj7S" %} {% endtab %} {% endtabs %} {% hint style="warning" %} 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. Do 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. {% endhint %} ### 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](https://github.com/Coherent-Partners/spark-python-sdk/tree/main/examples/usecases). 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) --- # Agent Instructions: Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter: ``` GET https://docs.coherent.global/integrations/snowflake.md?ask= ``` The question should be specific, self-contained, and written in natural language. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.