2
0
Fetch Live Data in Snowflake with External Access — because modern data platforms no longer operate in isolation. They thrive on real-time external data that helps drive faster, smarter decisions. Whether it’s weather forecasts, market trends, or currency exchange rates, organizations today need the ability to pull live insights directly into Snowflake — securely and efficiently, without relying on external servers, middleware, or complex ETL pipelines.
This is where Snowflake External Access Integration comes in — enabling your Snowflake environment to securely call REST APIs
External Access Integration allows Snowflake to securely talk to the outside world. Network rules act as gatekeepers, defining exactly which external endpoints your Snowflake environment can reach. Eliminate the need for intermediate services, Lambda functions, or API gateways. Access Integration enables external data on-demand rather than waiting for scheduled batch jobs.
In this article, we’ll walk through a real-world example, Fetching live currency exchange rates from a public API and loading them directly into Snowflake .
Let’s say your organization operates in multiple countries.Each region tracks expenses in its local currency — INR, EUR, GBP, AUD, etc.But your finance team wants to view reports in a single base currency (USD). So, every day, you need latest exchange rates to convert your financial data into USD.
Instead of relying on manual uploads or third-party ETL, we’ll use Snowflake External Access to fetch live rates directly from a public API:
Technical Implementation:
Technical Implementation:
Step 1⃣. Create Network Rule
We establish a secure network rule that explicitly allows egress traffic only to our trusted API endpoint (open.er-api.com).
CREATE OR REPLACE NETWORK RULE FX_API_NETWORK_RULE
MODE = EGRESS
TYPE = HOST_PORT
VALUE_LIST = ('open.er-api.com');
Step 2️. Create External Access Integration
This is the gatekeeper — it allows Snowflake’s Python code to reach the above host. This component ties together our network rules and makes them available to our stored procedures.
CREATE OR REPLACE EXTERNAL ACCESS INTEGRATION FX_ACCESS_INTEGRATION
ALLOWED_NETWORK_RULES = (FX_API_NETWORK_RULE)
ENABLED = TRUE;
Step 3. Python Stored Procedure
The heart of our solution is a Python procedure that fetches exchange rates for multiple currencies, processes the data, and intelligently handles both successful responses and errors. It writes successful rate updates to our main exchange rate table while logging any failures to a separate error table for monitoring and troubleshooting.
The stored procedure below:
- Fetches live data from the external API
- Loops through a list of currencies
- Saves all valid exchange rates
- Logs invalid ones into an error table
Execute the Procedure:
Data Validation in Table:
Invalid Currency Exchange:
Instead of relying on yesterday’s exchange rates loaded through a nightly batch job, your finance team can now run reports with rates that are current as of minutes ago. Your pricing engine can recalculate product prices in different currencies based on live market conditions.
Conclusion: By combining Snowpark, Network Rules, and Access Integrations, you can securely consume APIs like Exchange Rates, Weather, or AI Predictions, and store the results directly inside Snowflake — without any ETL tool or external system.
