How to Get FRED Data into Snowflake (3 Ways)

​

The short answer

​

Option 1: Subscribe to a Snowflake Marketplace listing

select
    date,
    value as cpi,
    value / lag(value, 12) over (order by date) - 1 as cpi_yoy
from fred.fred_series_observations_vw
where series_id = 'CPIAUCSL'
order by date desc
limit 12;

​

Option 2: Call the FRED API from SQL with Snowflake Data Utilities

create or replace network rule my_db.my_schema.fred_network_rule
    mode = egress
    type = host_port
    value_list = ('api.stlouisfed.org');

create or replace secret my_db.my_schema.fred_api_key
    type = generic_string
    secret_string = '<your FRED API key>';

create or replace external access integration fred_external_access_integration
    allowed_network_rules = (my_db.my_schema.fred_network_rule)
    allowed_authentication_secrets = (my_db.my_schema.fred_api_key)
    enabled = true;

grant usage on database my_db to application SnowflakeDataUtilitiesApp;
grant usage on schema my_db.my_schema to application SnowflakeDataUtilitiesApp;
grant usage on integration fred_external_access_integration to application SnowflakeDataUtilitiesApp;
grant read on secret my_db.my_schema.fred_api_key to application SnowflakeDataUtilitiesApp;

call SnowflakeDataUtilitiesApp.utils.init_fred(
    api_key => 'my_db.my_schema.fred_api_key',
    external_integration => 'FRED_EXTERNAL_ACCESS_INTEGRATION');

call SnowflakeDataUtilitiesApp.utils.fred_download_and_generate_foundation_tables_from_series(
    series_ids_string => 'GDP,CPIAUCSL,UNRATE',
    output_timeseries_table => 'FRED_TS',
    output_attributes_table => 'FRED_ATT'
);

select * from SnowflakeDataUtilitiesApp.utils.fred_ts limit 10;

• You list every series. The app downloads the IDs you pass. Finding series and adding new ones is up to you.
• It’s a snapshot. The tables only change when you rerun the download. Schedule it with a Snowflake task to keep data current. Snowflake’s docs recommend regular full refreshes, because FRED data is revised.
• The rate limit still applies. Calls go to the FRED API under your key, so the limit of 120 requests a minute applies here too.

​

Option 3: Run your own FRED API pipeline

import os

import pandas as pd
import requests
import snowflake.connector
from snowflake.connector.pandas_tools import write_pandas

FRED_URL = "https://api.stlouisfed.org/fred/series/observations"


def fetch_series(series_id):
    resp = requests.get(
        FRED_URL,
        params={"series_id": series_id, "api_key": os.environ["FRED_API_KEY"], "file_type": "json"},
        timeout=30,
    )
    resp.raise_for_status()
    df = pd.DataFrame(resp.json()["observations"])[["date", "value"]]
    df["series_id"] = series_id
    # FRED marks missing observations with "." instead of a number.
    df["value"] = pd.to_numeric(df["value"], errors="coerce")
    return df


df = pd.concat(fetch_series(s) for s in ["GDP", "CPIAUCSL", "UNRATE"])
df.columns = [c.upper() for c in df.columns]

conn = snowflake.connector.connect(
    account=os.environ["SNOWFLAKE_ACCOUNT"],
    user=os.environ["SNOWFLAKE_USER"],
    password=os.environ["SNOWFLAKE_PASSWORD"],
    warehouse="LOAD_WH",
    database="ECON",
    schema="RAW",
)
write_pandas(conn, df, "FRED_OBSERVATIONS_STAGING", auto_create_table=True, overwrite=True)

merge into fred_observations t
using fred_observations_staging s
    on t.series_id = s.series_id and t.date = s.date
when matched then update set value = s.value
when not matched then insert (series_id, date, value)
    values (s.series_id, s.date, s.value);

• The rate limit. FRED allows up to 120 requests a minute and returns HTTP 429 beyond that. Repeated violations can get your key temporarily blocked. At one request per series, 250,000 series take at least 35 hours, before retries. FRED’s API version 2 can return every series on a release in bulk (fred/v2/release/observations), which cuts the request count for large backfills, but you have to work out which releases you need.
• Revisions. FRED values are revised after they’re first published, GDP being the best-known example. An append-only load keeps the first print forever. Refetch full history for updated series and MERGE it, as above. To keep the earlier values too, see vintage observations below.
• Incremental updates. The fred/series/updates endpoint lists the series updated recently. Use it to refetch only what changed instead of every series every day.
• Missing values. Missing observations come back as ".", not null. Convert them before loading, or numeric columns fail to load.
• Metadata. Units, frequency and seasonal adjustment come from a separate endpoint (fred/series). Without them, a column of numbers is hard to interpret.
• Licensing. Most FRED series are public domain, but some belong to third parties, and FRED’s terms say you need the owner’s permission to use those beyond personal use. Copyrighted series say so in their notes.

​

Vintage observations: keeping every revision

https://api.stlouisfed.org/fred/series/observations
    ?series_id=GDP
    &realtime_start=1776-07-04
    &realtime_end=9999-12-31
    &file_type=json
    &api_key=...

select date, value
from fred_observation_vintages
where series_id = 'GDP'
  and '2020-03-01' between realtime_start and realtime_end
order by date;

• Requests are capped by vintage count. FRED limits how many vintage dates one request can cover: 2,000 for JSON, 1,000 for CSV, and fewer for some daily series. Heavily revised series need several requests over consecutive real-time windows.
• Windows have to be stitched. Rows that span a window boundary come back clipped to the window. Merge adjacent rows with the same value, or one real-time period shows up as two.
• Volume grows fast. Weekly series that are revised often, such as some of the Federal Reserve’s H.8 bank balance sheet series, have 700+ vintages and hundreds of thousands of rows each. When we built the vintage tables for our listing, full vintage history came to about 550 million rows and took 1.2 million API calls. At 120 requests a minute, that’s about a week of calls for the initial load alone.
• Updates rewrite history. Every revision closes the realtime_end of the previous row and adds a new one. The reliable approach is to refetch each updated series’ full vintage history and replace it, rather than trying to patch rows in place.
• Not every series has vintages. Some licensed series return an error saying they don’t exist in ALFRED, so handle that per series instead of failing the whole run.

select date, value
from fred.fred_series_observations_vintages_vw
where series_id = 'GDP'
  and '2020-03-01' between realtime_start and realtime_end
order by date;

​

Check your load

select series_id, date, count(*) as rows_per_date
from fred_observations
group by series_id, date
having count(*) > 1;

select series_id, max(date) as latest_date
from fred_observations
group by series_id
order by latest_date;

​

Which should you choose?

• You want to analyze economic data, not maintain a pipeline: use a Marketplace listing.
• You need a few series and are fine refreshing them yourself: use the Snowflake Data Utilities app.
• You need custom transforms or a series no listing carries: build the API pipeline, and budget for the rate limit and revisions.

​

F.A.Q.