Let’s take a look at External Network Access Integration—one of Snowflake’s newest features that’s designed to allow secure access to specific network locations external to Snowflake for data ingestion purposes.
This feature allows for the request and response process from Snowflake to an HTTP source and works in conjunction with API Integrations that use both an authentication method and API keys.
In this article, we’ll give a broad overview of a five-step process for leveraging External Network Access Integration to power the data ingestion process, using the Bulk FHIR export process as an example of how this feature can be harnessed.
Leveraging External Network Access Integration in Five Steps
External Network Access Integration can be used to enable the Bulk FHIR export process through a five-step process.
1. Setting Up External Network Access Integration
The setup process for using the External Network Access Integration begins with the creation of a network rule, followed by a security integration and an Oauth secret that references that integration.
Once the user starts the Oauth flow, Snowflake will validate the credentials and refresh process, at which point users can begin to create functions or procedures that leverage External Network Access Integration to support data ingestion processes.
2. Using the Secrets Property to Ensure Secure External Data Retrieval
Next, the user will need to create a function for verifying secure connectivity. This function uses the secrets property to ensure that credentials used for authentication aren’t stored in Snowflake in an insecure manner while enabling external access and allowing specified data retrieval functions.
3. Starting the Bulk FHIR Export Process and Tracking Progress
After the connection between the external network location and Snowflake has been verified, the next step is to create a set of functions that authenticate and start the export process and one that tracks the process’s status. The latter function can be used to estimate completion time throughout the process until a list of downloadable files is produced, at which point the function will report that the process has been completed.
4. Downloading Files with Safeguards in Place
The last step in the Bulk FHIR Process is to create a procedure that downloads the content and processes the response. The response can be downloaded to a stage or processed in memory so that it can be written to a table. It is encouraged that precautions be implemented to prevent incomplete downloads and account for network interruptions.
5. Orchestrating the Bulk FHIR Export Process using Tasks within Snowflake
With the functions and procedures created the final step is to run the Bulk FHIR Export Process on a regular cadence. To orchestrate this we use Snowflake Tasks to ensure the process is executed in order while providing execution insight and alerting capabilities. Once the task is completed, the data from the downloaded files will be available in a table, as well as the corresponding metadata that was recorded during the process execution.
Finding Compelling Applications For This High-Powered Feature
The External Network Access Integration has proven effective in supporting the Bulk FHIR Export Process. Taking a step back, we can see how it can also be used more broadly to securely ingest data from external sources into Snowflake, where sophisticated data functions can be applied.
This feature is especially useful when a custom loader is required, such as when a pre-built integration isn’t available. The feature can also be used to shift the loader cost onto Snowflake to consume credits or simplify the platform and reduce data movement by removing a cloud-hosted component.
External Network Access Integration can support multiple secrets per ingestion and responses greater than 16 megs, though columnar limits apply, and it is applicable for API integrations that use both an authentication method and API keys.
With capabilities like these, the feature is a great option for enabling secure access to resources that support HTTP protocol, allowing for a safe and efficient data ingestion process from external sources into Snowflake.
Ready to make use of cutting-edge features like External Network Access Integration to securely power your data ingestion process? Looking to leverage creative and innovative solutions and systems like the one described above? Let’s talk today about Hakkoda’s Snowflake certified experts can take your data to the next level.
