Azure Machine learning

Data Science Best Practices with Snowflake and Azure- Part 2 Azure Machine Learning

October 9, 2023 | 3 mins read
Share post

Before dividing deep would recommend going through part 1 of the series of Best Practices for Data Science using Python Pandas Data Science Best Practices with Snowflake and Azure- Part 1 Pandas.

Using Snowflake and Azure Machine Learning together

External Function could be used for doing our Machine Learning Operations on Azure ML as described below. We would be focusing on creating the resources using the Azure portal.


To create an external function for Azure, you must have the following:

  • An Azure AD (Active Directory) tenant.
  • An account in that Azure AD tenant.
  • The account must have privileges.
  • Create an Azure Function app.
  • Create a service endpoint using Azure API Management service.
  • Register an Azure AD Application.
  • In a account in which you have the ACCOUNT ADMIN role or a role with the CREATE INTEGRATION privilege.

Step-1 : Create Azure Function as a remote service

There are multiple possible ways to create a remote service. This section shows how to create a remote service that is implemented as a JavaScript synchronous function.

Create the Azure Function App to serve as a container for the function(s) that you create later. Microsoft link for the same

  • When asked to choose how to Publish, choose Code.
  • Restrictions apply when creating multiple apps in the same resource group. For details, see the Microsoft documentation: Azure app service.
  • Snowflake provides a sample “echo” function in Node.js. To use this sample function to get started.
  • If asked for the Runtime stack, select Node.js.
  • And for the version of Node.js, select version 12.
  • When asked which OS to run the function on, choose “Windows” or “Linux”.
  • If you are only creating a demo function, Snowflake recommends selecting Windows.
  • Linux Function Apps cannot be edited in the Azure Portal. Users must publish the code through the Visual Studio Code interface.
  • If you want to run your Azure Function on Linux rather than Microsoft Windows, see the Microsoft documentation: Azure Functions.
  • Azure AD authentication is not available on Linux when using the “Consumption” pricing plan for Azure Functions. You must use an “App Service” pricing plan or “Premium” pricing plan in order to authenticate with Azure AD.

Step 2-Create an HTTP-Triggered Azure Function

After you create your Azure Function app (container), you need to create an Azure Function in the container. This function acts as the remote service. Microsoft allows Azure Functions to be called (“triggered”) different ways. A Snowflake external function invokes a remote service via an HTTP POST command, so the Azure Function you create must be an “HTTP-triggered function”.

Perform the tasks described in this section, you should be in the Function App screen in the Azure Portal. The name of your Azure Function app should be displayed, typically near the upper left corner of the screen.

To create the HTTP-triggered function:

  • In the left-hand side menu tree, look for the section titled Functions. In that section, click on the item labeled Functions to add a function.
  • Click on the + Add button.
  • Select HTTP trigger from the list of potential triggers on the right.
  • Enter the name to use for your HTTP-triggered function.
  • Record this name in the “HTTP-Triggered Function name” field in your tracking worksheet.
  • Enter the Authorization level.
  • Snowflake recommends choosing Function as the authorization level.
  • For more information about possible authorization levels, see the Microsoft documentation: HTTP-triggered functions.
  • Click on the button titled Add.
  • This takes you to a screen that shows the function name and, below that, the word Function.
  • In the tree menu on the left-hand side, click on Code + Test.
  • Replace the default code with your own code.
  • Sample code for a JavaScript “echo” function is provided below.
  • The function reads each row, then copies the row to the output (results). The row number is also included in the output. The output is returned as part of a multi-level dictionary.
  • This function accepts and returns data in the same format (JSON) that Snowflake sends and reads. For more details about data formats, see Remote Service Input and Output Data Formats .
  • Normally, the function returns HTTP code 200. If no rows are passed to the function (i.e. if the request body is empty), the function returns error code 400.
module.exports = async function(context, request) {
    context.log('JavaScript HTTP trigger function processed a request.');   if (request.body) {
        var rows =;
        var results = [];
        rows.forEach(row => {
            results.push([row[0], row]);

        results = {data: results}
        context.res = {
            status: 200,
            body: JSON.stringify(results)
   else {
       context.res = {
           status: 400,
           body: "Please pass data in the request body."

Authorization for Azure Function App

When an external function is called, Snowflake sends an HTTP POST command to the proxy service (e.g. the Azure API Management service), which relays the POST to the remote service (e.g. the Azure Function).

Each of these two steps should have authorization requirements, so you typically specify.

  • Authorization needed to call the API Management service.
  • The authorization needed to call functions in the Azure Function app that contains your Azure Function.

Step-3 : Create Proxy Service ( API Management Service )

Snowflake does not send data (HTTP POST requests) directly to a remote service. Instead, Snowflake sends the data to a proxy service that relays the data from Snowflake to the remote service (i.e. Azure Function) and back again.

Step-4 : Create API Integration for Azure in Snowflake

Use the CREATE API INTEGRATION command to create the API integration object:

  • Navigate to the Worksheets page within Snowflake UI
  • Execute the USE ROLE command to use the ACCOUNTADMIN role or a role with the CREATE INTEGRATION privilege.
use role has_accountadmin_privileges;
  • Enter a CREATE API INTEGRATION statement. The statement should look similar to the following:
create or replace api integration <integration_name>
    api_provider = azure_api_management
    azure_tenant_id = '<tenant_id>'
    azure_ad_application_id = '<azure_application_id>'
    api_allowed_prefixes = ('https://<api_management_service_name>')
    enabled = true;

Step-4 : Link API Integration for Azure to Proxy Service in Portal

Run the below command in the Snowflake worksheet


Record the app name and Record the consent URL. Paste the consent URL in your browser. When your browser resolves this URL.

Azure automatically creates a service principal that represents Snowflake in the tenant. You only need to create a service principal for Snowflake once per tenancy.

Step-5 : Create External Function for Azure in Snowflake

External functions in Snowflake are database objects, meaning they must be created in a schema in a database.

In the Snowflake Worksheet run statement below

create or replace external function <external_function_name>(<names,datatype> of parameters to function)
    returns variant
    api_integration = <api_integration_name>
    as '<invocation_url>';

Step-6 : Call the External Function for Azure

In the Snowflake Worksheet make sure the session uses the database and the schema that contains the function.

Using an appropriate role, call your external function as part of an SQL statement like below

select <function name>(<parameter>,<parameter>,...);

Leave A Comment

Your email address will not be published. Required fields are marked *