--- title: Snowflake External Functions description: "Snowflake is a cloud-based data warehousing company.  They specialize in\nprovisioning on-demand compute and elastic storage for data warehousing\napplications on a pay-per-use pricing model.In recent years\n[https://www.analytics.today/blog/oracle-vs-snowflake] they've dominating the \ncloud data-warehouse industry\n[https://www.prnewswire.com/news-releases/snowflake-more-than-triples-revenue-and-customer-base-doubles-post-money-valuation-all-in-just-one-year-300793857.html]\n.  Recently they announc" image: https://blog.ippon.tech/hubfs/Imported_Blog_Media/1280px-Snowflake_Logo_svg-3.png --- [Skip to main content](https://blog.ippon.tech/snowflake-external-functions#main) [![IpponLogoKleinBlue](https://blog.ippon.tech/hs-fs/hubfs/IpponLogoKleinBlue.png?width=219&height=64&name=IpponLogoKleinBlue.png) ![IpponLogoKleinBlue](https://blog.ippon.tech/hs-fs/hubfs/IpponLogoKleinBlue.png?width=219&height=64&name=IpponLogoKleinBlue.png) ![IpponLogoKleinBlue](https://blog.ippon.tech/hs-fs/hubfs/IpponLogoKleinBlue.png?width=168&height=49&name=IpponLogoKleinBlue.png) ![IpponLogoKleinBlue](https://blog.ippon.tech/hs-fs/hubfs/IpponLogoKleinBlue.png?width=168&height=49&name=IpponLogoKleinBlue.png)](https://ipponusa.com/) - [Home](https://ipponusa.com) - [Show submenu for About About](https://ipponusa.com/about-us) - [Who We Are](https://ipponusa.com/who-we-are) - [Careers](https://ipponusa.com/careers) - [Show submenu for Our Partners Our Partners](https://ipponusa.com/our-partners/) - [AWS](https://ipponusa.com/our-partners/aws/) - [Snowflake](https://ipponusa.com/our-partners/snowflake/) - [Databricks](https://ipponusa.com/our-partners/databricks/) - [Microsoft](https://ipponusa.com/our-partners/microsoft/) - [Show submenu for Services Services](https://ipponusa.com/services) - [Snowflake](https://ipponusa.com/service/snowflake-concierge/) - [Artificial Intelligence](https://ipponusa.com/service/artificial-intelligence/) - [Data & Analytics](https://ipponusa.com/service/data-analytics/) - [Cloud Strategy](https://ipponusa.com/service/cloud-strategy/) - [Platform Modernization](https://ipponusa.com/service/platform-moderization/) - [Product Innovation](https://ipponusa.com/service/product-innovation/) - [Operating Model](https://ipponusa.com/service/operating-model/) - [Blogs](https://blog.ippon.tech/) - [Success Stories](https://ipponusa.com/success-stories/) - [Show submenu for Resources Resources](https://ipponusa.com/resources/) - [eBooks](https://ipponusa.com/ebooks/) - [The Data Pour](https://info.ippon.tech/the-data-pour) - [Videos & Webinars](https://info.ippon.tech/videos-and-webinars) - [Media Center](https://ipponusa.com/media-center/) Search Open main navigation Close main navigation - [Home](https://ipponusa.com) - Show submenu for About About - About - [About](https://ipponusa.com/about-us) - [Who We Are](https://ipponusa.com/who-we-are) - [Careers](https://ipponusa.com/careers) - Show submenu for Our Partners Our Partners - Our Partners - [Our Partners](https://ipponusa.com/our-partners/) - [AWS](https://ipponusa.com/our-partners/aws/) - [Snowflake](https://ipponusa.com/our-partners/snowflake/) - [Databricks](https://ipponusa.com/our-partners/databricks/) - [Microsoft](https://ipponusa.com/our-partners/microsoft/) - Show submenu for Services Services - Services - [Services](https://ipponusa.com/services) - [Snowflake](https://ipponusa.com/service/snowflake-concierge/) - [Artificial Intelligence](https://ipponusa.com/service/artificial-intelligence/) - [Data & Analytics](https://ipponusa.com/service/data-analytics/) - [Cloud Strategy](https://ipponusa.com/service/cloud-strategy/) - [Platform Modernization](https://ipponusa.com/service/platform-moderization/) - [Product Innovation](https://ipponusa.com/service/product-innovation/) - [Operating Model](https://ipponusa.com/service/operating-model/) - [Blogs](https://blog.ippon.tech/) - [Success Stories](https://ipponusa.com/success-stories/) - Show submenu for Resources Resources - Resources - [Resources](https://ipponusa.com/resources/) - [eBooks](https://ipponusa.com/ebooks/) - [The Data Pour](https://info.ippon.tech/the-data-pour) - [Videos & Webinars](https://info.ippon.tech/videos-and-webinars) - [Media Center](https://ipponusa.com/media-center/) - Search - [Contact Us](https://ipponusa.com/contact/) [Contact Us](https://ipponusa.com/contact/) # Snowflake External Functions ![Dan Ferguson](https://app.hubspot.com/settings/avatar/d41d8cd98f00b204e9800998ecf8427e) by [Dan Ferguson](https://blog.ippon.tech/author/dan-ferguson) June 15, 2020 ![Snowflake External Functions](https://blog.ippon.tech/hubfs/Imported_Blog_Media/1280px-Snowflake_Logo_svg-3.png) [Cloud](https://blog.ippon.tech/tag/cloud/) [Data](https://blog.ippon.tech/tag/data/) [ETL](https://blog.ippon.tech/tag/etl/) [Data Warehouse](https://blog.ippon.tech/tag/data-warehouse/) [Snowflake](https://blog.ippon.tech/tag/snowflake/) [External Functions](https://blog.ippon.tech/tag/external-functions/)   June 15, 2020 Snowflake is a cloud-based data warehousing company.  They specialize in provisioning on-demand compute and elastic storage for data warehousing applications on a pay-per-use pricing model.  [In recent years](https://www.analytics.today/blog/oracle-vs-snowflake?ref=blog.ippon.tech) they've dominating the [cloud data-warehouse industry](https://www.prnewswire.com/news-releases/snowflake-more-than-triples-revenue-and-customer-base-doubles-post-money-valuation-all-in-just-one-year-300793857.html?ref=blog.ippon.tech).  Recently they announced a few new product features.  In this post, we're going to discuss one of those features, External Functions. ## External Functions [External Functions](https://docs.snowflake.com/en/sql-reference/external-functions-introduction.html?ref=blog.ippon.tech) in Snowflake are functions hosted on a different platform.  What does this mean?  Let's break it down. [Functions](https://docs.snowflake.com/en/sql-reference/user-defined-functions.html?ref=blog.ippon.tech) are pieces of Javascript or SQL code that execute in your data warehouse.  The code itself is written by developers and is intended to add features and functionality to your data warehouse, beyond the traditional SQL commands that Snowflake already offers.  External Functions are similar, except they are not hosted on Snowflake, and do not execute within the context of your data warehouse.  Instead, they are hosted elsewhere, accessed via an HTTP endpoint, and computed using third-party compute resources.  When making a call to a function, the value is computed using Snowflake's compute resources.  External Function values are computed using external resources, and the results are returned via HTTP. For example, if you had access to an external service that mapped zip codes to cities, the typical approach to leveraging this service without external functions would be to import the keys and values of cities and zip-codes to a table and then query that table.  Then you could select cities from the `zipcode_to_city` table where the zip-code is some value.  For example, 1. Create the table `zipcode_to_city`. 2. Create an ETL pipeline that batch loads `zipcode_to_city` with the zipcodes and cities from your source system. 3. Automate this ETL pipeline to update the table when/if changes happen. 4. Run `select city from zipcode_to_city where zip = '11102';`. 5. (Optional) Create a function that replaces this query for you in other queries. This is standard practice for integrating databases with external systems.  The problem here is you have to maintain an ETL pipeline to keep your downstream database up to date.  Additionally, you have another table to query which could impact the performance of queries downstream.  Now imagine having to do this for several external systems of record beyond just zip code - city mappings.  It's easy to see how this could get complicated quickly. Now, with external functions, you can create the `zipcode_to_city_external_function` and get the same data as you would have if you imported the table.  The only difference now is that instead of importing a table to Snowflake, you import only the data you need, when you need it, via HTTPS.  After configuring your function (which we'll see how to do later), you can call it in SQL like this: ``` select zipcode_to_city_external_function(zipcode); ``` The function `zipcode_to_city_external_function` replaces the typical call to your reference table and the value is imported and used in the rest of your SQL statement.  Now you don't have to maintain a reference table of zip-code – city mappings because you've offloaded that functionality to a third-party service.   ## Creating an External Function I'm not going to spend time breaking out syntax associated with creating an external function.  If you want to know the syntax options, check the Snowflake documentation associated with [External Functions](https://docs.snowflake.com/en/sql-reference/sql/create-external-function.html?ref=blog.ippon.tech#usage-notes).  Instead, I'll go through the typical workflow required to create and use an External Function on AWS. 1. Configure access to external function (IAM) 2. Create an API Integration object in Snowflake (API Gateway) 3. Create the External Function ### Configuring Access The first thing to do is to create the IAM assets used by Snowflake to make calls to your AWS hosted function.  Due to limitations we'll explore later in this post, Snowflake must call API Gateway.  As a result, we'll create this policy where you'll specify your own hosting API ARN: ``` { "Version": "2012-10-17", "Statement": [ { "Sid": "VisualEditor0", "Effect": "Allow", "Action": [ "apigateway:DELETE", "apigateway:PUT", "apigateway:PATCH", "apigateway:POST", "apigateway:GET" ], "Resource": "" } ] } ``` Now we create a role.  This role is a little different from your typical role as it requires access from a third party.  Note in the image below we select "Another AWS account" as the trusted entity, specifying a dummy ID in the external ID field.  When creating this role, specify your own account's Account ID in the Account ID field.  After you've done this, add the policy we created above to this role and complete the role creation. ![](https://blog.ippon.tech/hubfs/Imported_Blog_Media/Screen-Shot-2020-06-15-at-10_29_03-AM-2.png) ### Configure Snowflake-Side Access What we've done to this point is setup a templated access to AWS from Snowflake on the AWS side.  There's still some configuration we need to create on the Snowflake side before we can go back and finalize our AWS configuration.   External Functions require a Sn0wflake construct called an [API Integration](https://docs.snowflake.com/en/sql-reference/sql/create-api-integration.html?ref=blog.ippon.tech).  These constructs are logical endpoints within Snowflake that store references to external APIs.  See the below code snippet for more information. ``` CREATE API INTEGRATION IF NOT EXISTS external_integration API_PROVIDER = aws_api_gateway API_AWS_ROLE_ARN = '' ENABLED = TRUE API_ALLOWED_PREFIXES = ('" }, "Action": "sts:AssumeRole", "Condition": { "StringEquals": { "sts:ExternalId": "" } } } ] } ``` Replace the ARN containing your Account ID with the **API\_AWS\_IAM\_USER\_ARN** value from the previous query we ran in Snowflake.  Then replace the Dummy ID with the **API\_AWS\_EXTERNAL\_ID** from the previous query.  The results should look like the following: ``` { "Version": "2012-10-17", "Statement": [ { "Effect": "Allow", "Principal": { "AWS": "" }, "Action": "sts:AssumeRole", "Condition": { "StringEquals": { "sts:ExternalId": "" } } } ] } ``` What this does is allow the Snowflake asset you created with the **API\_AWS\_EXTERNAL\_ID** and **API\_AWS\_IAM\_USER\_ARN** specified to assume the role with the permissions we gave it.  Based on this blog post, that means access to call an API Gateway endpoint.  If you modify this role to have new policies though, that means you will grant this third party service all of those new policies.  For more details,  check out this [how-to guide](https://docs.snowflake.com/en/user-guide/data-load-s3-config.html?ref=blog.ippon.tech#option-2-configuring-an-aws-iam-role) published by Snowflake on this topic. ### Creating the External Function After all that back and forth, this is the easy part.  Following the [syntax for an External Function](https://docs.snowflake.com/en/sql-reference/sql/create-external-function.html?ref=blog.ippon.tech#usage-notes), we should be sending something like the following to Snowflake: ``` CREATE OR REPLACE SECURE EXTERNAL FUNCTION zipcode_to_city_external_function (zipcode VARCHAR) RETURNS VARCHAR NOT NULL RETURNS NULL ON NULL INPUT VOLATILE COMMENT = 'Returns a city for the given zip-code.' API_INTEGRATION = external_integration HEADERS = ('content-type' = 'application/json') AS '' ; ``` Notice how we specify an empty list of parameters, a return type, and the API Integration object we created earlier.  The API Integration object allows this external function to assume the role we specified, giving Snowflake the ability to query API Gateway as an external service. To use this external function, all we have to do is call our function like you would any User-Defined Function: ``` SELECT zipcode_to_city_external_function("11102"); ``` The results of this function call will vary based on the back-end implementation, but in my demo the result is: ``` ZIPCODE_TO_CITY_EXTERNAL_FUNCTION("11102") ASTORIA ``` The following is the backend implementation for this endpoint: ``` exports.handler = async (event) => { // MAGIC HAPPENS HERE city = CALL_TO_CITY_ZIP_MAPPER(event) const response = { data: [ [0, city] ] }; return response; }; ``` Obviously this is a hard-coded example.  I've hard-coded the response here to show the formatting of a response that Snowflake expects.  If you do not format your output like this you'll get an error like `external function top-level JSON object must contain "data" JSON array element`.   This may seem strange at first, but it makes a lot of sense when you break it down.  Snowflake is, at the end of the day, a database.  It stores data in a structured way; when it integrates with third parties it expects the third party to respect that data quality requirement.  As a result, we have this strange JSON-esque syntax being returned by the lambda function.  For more information on this, check out Snowflake's documentation about [creating External Functions for different platforms](https://docs.snowflake.com/en/sql-reference/external-functions-general.html?ref=blog.ippon.tech#data-format-sent-from-snowflake). ### Limitations & Gotcha's to External Functions - External Functions need to be called through an HTTP proxy service. This service is referenced in the API\_INTEGRATION field. Currently, the only supported API Integration objects are those hosted on API Gateway. - External Function calls cannot be used to define the default value of a column when creating a table. - External Functions must return scalar values that match the return type of the external function. Returned values must also match the number of rows which are being used. - External Functions always make POST requests. - The top-level response must be a series of name-value pairs sent as a JSON array labeled "data." - Lambda functions that integrate with API Gateway by proxy will have to include the expected API Gateway response object, as well as the expected Snowflake data object. - Backend integrations do not have to be Lambda functions, they just have to be hosted via API Gateway (at this time). - If you ever re-create your API Integration object in Snowflake, you will have to modify the IAM role. You just have to modify the API\_AWS\_EXTERNAL\_ID, the API\_AWS\_IAM\_USER\_ARN field should stay the same (unless your Snowflake user changes). For more details on the limitations of external functions, and the additional considerations you should take when creating external functions, check the [Snowflake documentation](https://docs.snowflake.com/en/sql-reference/external-functions-introduction.html?ref=blog.ippon.tech#limitations-of-external-functions) on the subject. ## Related Articles ##### [![Snowflake Introduces UBAC - What it Means For RBAC](https://blog.ippon.tech/hs-fs/hubfs/Ippon-1.png?width=520&height=294&name=Ippon-1.png) Snowflake • June 4, 2025 Snowflake Introduces UBAC - What it Means For RBAC 2 min read](https://blog.ippon.tech/snowflake-introduces-ubac-what-it-means-for-rbac) ##### [![Snowflake Summit 2025 - Platform Keynote Feature Announcements](https://blog.ippon.tech/hs-fs/hubfs/Ippon.png?width=520&height=294&name=Ippon.png) Snowflake • June 3, 2025 Snowflake Summit 2025 - Platform Keynote Feature Announcements 2 min read](https://blog.ippon.tech/snowflake-summit-2025-platform-keynote-feature-announcements) ##### [![Snowflake Notebooks QuickStart: Ingesting Data via External APIs](https://blog.ippon.tech/hs-fs/hubfs/3D%20student%20with%20a%20notebook%20-%20isolated%20over%20a%20white%20background.jpeg?width=520&height=294&name=3D%20student%20with%20a%20notebook%20-%20isolated%20over%20a%20white%20background.jpeg) Snowflake • March 20, 2025 Snowflake Notebooks QuickStart: Ingesting Data via External APIs 5 min read](https://blog.ippon.tech/snowflake-notebooks-quickstart-ingesting-data-via-external-apis) ### Comments ### Subscribe to Our Blog! Stay informed with the latest insights and updates by signing up for our weekly blog newsletter – delivered straight to your inbox! [Back to blog homepage »](https://blog.ippon.tech/) ![IpponLogoKleinBlue](https://blog.ippon.tech/hs-fs/hubfs/IpponLogoKleinBlue.png?width=247&height=73&name=IpponLogoKleinBlue.png "IpponLogoKleinBlue") Ippon is a consulting and expertise firm, who is convinced that technology is a source of progress for society. We help our clients leverage their digital assets to design an appropriate strategy and deploy their transformation roadmap at scale. #### Navigation - [Home](https://ipponusa.com/) - [About](https://ipponusa.com/about-us/) - [Services](https://ipponusa.com/services/) - [Blogs](https://blog.ippon.tech/?__hstc=223043268.1036d82247623b329dfefe4e92697801.1714729179243.1714729179243.1714729179243.1&__hssc=223043268.2.1714729179244&__hsfp=803678701) - [eBooks](https://ipponusa.com/ebooks/) - [The Data Pour](https://info.ippon.tech/the-data-pour?__hstc=223043268.1036d82247623b329dfefe4e92697801.1714729179243.1714729179243.1714729179243.1&__hssc=223043268.2.1714729179244&__hsfp=803678701) [Contact Us](https://ipponusa.com/contact/) [Join Us](https://ipponusa.com/careers/) #### Services - [Data & Analytics](https://ipponusa.com/service/data-analytics/) - [Cloud Strategy](https://ipponusa.com/service/cloud-strategy/) - [Artificial Intelligence](https://ipponusa.com/service/artificial-intelligence/) - [Operating Model](https://ipponusa.com/service/operating-model/) - [Platform Modernization](https://ipponusa.com/service/platform-moderization/) - [Product Innovation](https://ipponusa.com/service/product-innovation/) #### Ippon International - [France](https://fr.ippon.tech/?__hstc=223043268.1036d82247623b329dfefe4e92697801.1714729179243.1714729179243.1714729179243.1&__hssc=223043268.2.1714729179244&__hsfp=803678701) - [Australia](https://au.ippon.tech/?__hstc=223043268.1036d82247623b329dfefe4e92697801.1714729179243.1714729179243.1714729179243.1&__hssc=223043268.2.1714729179244&__hsfp=803678701) - #### Contact - Ippon Technologies [3431 West Leigh Street Richmond, VA 23230, USA](https://maps.app.goo.gl/RySm6SAwZnsxeiLz9) - [(844) 477- 6687](tel:8444776687) - [Sales@ipponusa.com](mailto:Sales@ipponusa.com) ©Copyright 2024 Ippon USA. All Rights Reserved.   |   [Terms and Conditions](https://ipponusa.com/privacy-policy/)   |   [Privacy Policy](https://ipponusa.com/privacy-policy/)   |   [Website by Skol Marketing](https://skolmarketing.com/) ```json { "@context" : "https://schema.org", "@type" : "BlogPosting", "author" : { "@type" : "Person", "name" : "Dan Ferguson", "url" : "https://blog.ippon.tech/author/dan-ferguson" }, "dateModified" : "2024-01-29T16:49:56.097Z", "datePublished" : "2020-06-15T12:42:00.000Z", "headline" : "Snowflake External Functions", "image" : [ "https://blog.ippon.tech/hubfs/Imported_Blog_Media/1280px-Snowflake_Logo_svg-3.png" ], "mainEntityOfPage" : { "@id" : "https://blog.ippon.tech/snowflake-external-functions", "@type" : "WebPage" }, "publisher" : { "@type" : "Organization", "logo" : { "@type" : "ImageObject", "url" : "https://blog.ippon.tech/hubfs/logo_SVG.svg" }, "name" : "Ippon Technologies" } } ```