Small updates act as game changer for calling API's!

If you work with analytics or data engineering, you have likely encountered the issue of getting data for analytics out of an API (yes/no?) Sometimes calling an API can be a hassle. Issues usually arises through scheduling, authenticating, parsing, transforming or loading the data. You will likely need an external service to handle the call, and the runtime environment for it. Grieve no more!

Recently Snowflake announced a wealth of fresh features... Some of the smallest announcements made on that day, was SECRETS and EXTERNAL ACCESS INTEGRATION. I did not think much of them at first, but these act as GAME CHANGERS, when used in combination with USER DEFINED FUNCTIONS (UDF) for accessing API endpoints.

Previously UDF's acted mainly on objects within Snowflake data cloud, but in combination with the secrets and external access, a Snowflake UDF can now be allowed to access external endpoints with a key (e.g. a REST API).

• The secrets can hold password, key, token or strings for authentication.
• The external access integration allows a specific address to be accessed, through a network rule.

Of course every API is a bit different, but I can now call an external public API and retrieve a auth token, and use the token to retrieve records back in a json object - all from within Snowflake, bypassing cloud functions, repositories, runtime environments, virtual network setup, key vaults from a cloud a provider.

This simplifies data load immensely for getting data out of API's, and maybe with a bit of tweaking, also direct database connections using UDF's.

The code for creating the objects getting a token from an API can be accessed here.