Back to posts
Apr 21, 2026•21 min read

Free Datasets and APIs Every Data Team Should Know

A field guide to the open data sources that quietly enrich production analytics (Census, Open-Meteo, FRED, SEC EDGAR, Nominatim and more) with the licence terms, rate limits, and Fabric patterns that make them safe to depend on.

Most enterprise datasets describe what happened: a transaction, a click, a delivery, a sensor reading. They rarely describe the context in which it happened. The weather that day. The neighbourhood income. The federal interest rate. The competing business one block over. Free public datasets fill that gap, and the teams that learn to use them well consistently produce sharper analyses than teams that don't.

This is a field guide to the open sources we reach for over and over. Every entry includes the access pattern, the licence terms, the rate-limit reality, and a runnable Microsoft Fabric snippet. None of these require a credit card. All of them have shipped to production at organisations we've worked with.

Gov & Census
Weather APIs
Economic Data
Microsoft Fabric Lakehouse
Unified Enrichment Layer
  • $0 marginal cost per enrichment. All sources in this guide are free at the access tier we describe. The cost is your engineering time, not licence fees.
  • 7 open sources we keep coming back to. US Census TIGER + ACS, Nominatim, Open-Meteo, FRED, SEC EDGAR, Nager.Date, and the GTFS transit feed network.
  • 1 storage pattern that makes them safe. Pull once into the Lakehouse, refresh on a cadence, query from your Warehouse. Never call a public API at report render time.
-- The contract every enrichment table in the Lakehouse satisfies
CREATE TABLE enrichment.SourceRegistry (
SourceKey VARCHAR(64) PRIMARY KEY,
SourceName VARCHAR(200), -- "US Census ACS 5-Year"
LicenceUrl VARCHAR(500), -- attribution requirement link
RefreshCadence VARCHAR(50), -- "annual" | "daily" | "on-demand"
RateLimit VARCHAR(100), -- "1 req/sec" | "500/day" | "unlimited"
VintageDate DATE, -- when the source was last refreshed
RowCount BIGINT
);

01. Census TIGER: The Geometry Backbone

The US Census Bureau's TIGER/Line shapefiles are the canonical source of administrative boundary geometries in the United States: states, counties, tracts, block groups, ZIP code tabulation areas, school districts, congressional districts, and a dozen others. They are the foundation that every other US-based geographic enrichment depends on.

US
National / State Level
FIPS: 2-digit
County & Tract Boundaries
TIGER/Line Shapefiles (.shp)
Block Groups
Highly granular neighborhood polygons
Access

Bulk download from www2.census.gov/geo/tiger/ as ZIP'd shapefiles or geodatabases; if you want GeoJSON, use the TIGERweb REST services instead. No API key, no rate limit. Refresh annually. The boundaries change less than you'd think.

Licence

US Government work: public domain, no attribution required. Safe to redistribute, embed in client deliverables, or use as a base layer in a SaaS product.

The pattern: one Fabric notebook per geography level, scheduled annually, writing to a Delta table partitioned by state FIPS. Every downstream spatial join (in Spark, T-SQL, or KQL) reads from these tables instead of from the source files.

# PySpark in a Fabric notebook: load TIGER block groups into the Lakehouse
import geopandas as gpd
YEAR = 2024
STATES = ["06", "36", "48"] # CA, NY, TX FIPS codes
for fips in STATES:
url = f"https://www2.census.gov/geo/tiger/TIGER{YEAR}/BG/tl_{YEAR}_{fips}_bg.zip"
gdf = gpd.read_file(f"zip://{url}")
gdf["geometry_wkt"] = gdf.geometry.to_wkt()
spark.createDataFrame(gdf.drop(columns=["geometry"])) \
.write.mode("overwrite") \
.partitionBy("STATEFP") \
.saveAsTable("enrichment.census_block_groups")

02. Census ACS: The Demographic Layer

The American Community Survey is the demographic dataset most enrichment workflows actually want. Population, median household income, age distribution, educational attainment, commute times, household composition, published annually at every geography level TIGER defines, going back to 2009.

ACS 5-Year Estimates
Median HH Income $84,200
Total Population 4,120
Bachelor's Degree+ 42.5%
dim.Customers
Cust_ID C-9921
LTV $1,240
ACS_Income $84,200
Access

REST API at api.census.gov/data/{year}/acs/acs5. Free key required (instant signup). Rate limit: 500 calls per IP per day without a key, effectively unlimited with one.

Licence

Public domain US Government data. Suggested attribution: "Source: U.S. Census Bureau, American Community Survey." Always pair the data with the vintage year. ACS estimates are 5-year rolling averages.

Once both ACS and TIGER are in the Lakehouse, the join is a single query. Below: every customer enriched with the median household income of the block group they live in, computed once and stored as a column in the customer dimension.

-- T-SQL (SQL Server / Azure SQL): enrich customers with ACS demographics by block group. Fabric Warehouse has no spatial types; there, precompute the point-in-polygon match with a Spark spatial join.
UPDATE c -- update the alias, not the table name, or you self-join a second copy
SET MedianHHIncome = acs.B19013_001E,
PopulationDensity = acs.B01003_001E / NULLIF(bg.ALAND_SQMI, 0),
EnrichedAt = GETDATE()
FROM dim.Customers c
JOIN enrichment.census_block_groups bg
ON bg.geometry.STContains(c.HomePoint) = 1
JOIN enrichment.census_acs5_2023 acs
ON acs.GEOID = bg.GEOID
WHERE c.HomePoint IS NOT NULL;

03. Nominatim: Reverse Geocoding for Free

Nominatim is the OpenStreetMap-backed geocoder. Submit a coordinate, get back a street address with house number, road, suburb, city, postcode, and country. Submit an address, get back a coordinate. It's the open alternative to the Google Maps Geocoding API and good enough for the vast majority of batch enrichment use cases.

Lat/Lon
47.6062, -122.3321
Nominatim API
Structured Address
Street1201 3rd Ave
CitySeattle
StateWA
Zip98101
Access

Public endpoint at nominatim.openstreetmap.org/reverse. Maximum 1 request per second on the public instance, and they enforce it. For higher throughput, self-host the Nominatim Docker image on Azure, or use Photon, Komoot's separate open geocoder built on the same OSM data.

Licence

Open Database Licence (ODbL). Requires attribution: "© OpenStreetMap contributors". You may not use the public endpoint to bulk-build a competing dataset. For that, self-host.

The mistake we see most often: calling Nominatim from the application layer at request time. It will work for 30 minutes, then your IP gets rate-limited, and the feature breaks in production. Always batch in the Lakehouse with deliberate throttling and cache the result.

# PySpark notebook: batch reverse geocode new customer pins, respect 1 req/sec
import requests, time
from pyspark.sql.functions import col
HEADERS = {"User-Agent": "daly-bi-enrichment/1.0 (data@example.com)"}
def reverse_geocode(lat, lon):
r = requests.get("https://nominatim.openstreetmap.org/reverse",
params={"lat": lat, "lon": lon, "format": "json"},
headers=HEADERS, timeout=10)
time.sleep(1.1) # respect the rate limit
return r.json().get("address", {})
# Only geocode pins not already in the cache
new_pins = spark.table("silver.customer_pins") \
.join(spark.table("enrichment.geocode_cache"),
["lat", "lon"], "left_anti").collect()
results = [(p.lat, p.lon, reverse_geocode(p.lat, p.lon)) for p in new_pins]

04. Open-Meteo: Weather Without an API Key

Open-Meteo is the cleanest free weather API on the market. Historical hourly observations going back to 1940, 16-day forecasts, and ERA5 reanalysis data, all served from a single REST endpoint with no key, no signup, and a generous rate limit for non-commercial use.

Open-Meteo Historical

archive-api.open-meteo.com

Date
2026-04-18
Max Temp
24.5°C
Precipitation
12.0 mm
Wind Speed
18 km/h
Access

REST at archive-api.open-meteo.com/v1/archive (historical) or api.open-meteo.com/v1/forecast. No key required for non-commercial use under 10,000 daily requests. Commercial use requires the paid tier regardless of volume.

Licence

Creative Commons Attribution 4.0 (CC-BY 4.0). Attribute "Weather data by Open-Meteo.com" anywhere the data is shown to end users. The free tier is for non-commercial use only. Commercial use requires Open-Meteo's paid API subscription.

The natural pairing in Fabric is Real-Time Intelligence: store every observation in a KQL table partitioned by day, then join it to your event stream (sales transactions, deliveries, sensor readings) to ask whether weather actually moved the needle. The query below answers that for retail revenue.

// KQL: daily revenue versus weather, joined on store location and date
SalesEvents
| where EventTime between (ago(90d) .. now())
| summarize Revenue = sum(LineTotal) by StoreId, SalesDate = bin(EventTime, 1d)
| join kind=inner (
WeatherDaily
| project StoreId, SalesDate = ObservationDate,
TempMaxC, PrecipMm, WindKmh
) on StoreId, SalesDate
| extend WeatherBucket = case(PrecipMm > 10, "wet",
TempMaxC > 30, "hot",
TempMaxC < 5, "cold", "normal")
| summarize AvgRevenue = avg(Revenue) by StoreId, WeatherBucket

05. FRED: Economic Indicators at Series Granularity

The St. Louis Fed's FRED (Federal Reserve Economic Data) catalogue is over 800,000 economic time series: GDP, unemployment, inflation, exchange rates, regional labour market data, consumer sentiment, treasury yields, all behind one consistent API. If a question contains the phrase "controlling for the macro environment", FRED is where the macro environment lives.

FRED
Federal Reserve Economic Data
Series: UNRATE
Unemployment Rate
3.8% Monthly
Series: CPIAUCSL
Consumer Price Index
312.4 Idx 82-84=100
Series: FEDFUNDS
Effective Fed Funds
5.33% Daily
Access

REST at api.stlouisfed.org/fred/series/observations. Free key required. Rate limit: 120 requests per minute, which is easily enough for incremental refresh of a curated series list.

Licence

Most series are public domain (US Government source). Some are sourced from third parties (e.g., Haver Analytics, Moody's) and inherit those licences. Check the copyright field in each series response before redistributing.

The pattern: maintain a Lakehouse table called enrichment.fred_series_subscriptions listing every series ID your reports depend on. A daily notebook iterates through it, calls the FRED API with observation_start set to the last loaded date, and appends new observations.

# PySpark: incremental load of subscribed FRED series into the Lakehouse
import requests, os
from pyspark.sql import Row
FRED_KEY = os.environ["FRED_API_KEY"]
SERIES = ["UNRATE", "CPIAUCSL", "GDP", "FEDFUNDS", "DEXUSEU"]
# observation_start = last loaded date per series; first run backfills from 2010
last = {}
if spark.catalog.tableExists("enrichment.fred_observations"):
last = {r["SeriesId"]: r["LastDate"] for r in spark.sql(
"SELECT SeriesId, MAX(ObsDate) AS LastDate "
"FROM enrichment.fred_observations GROUP BY SeriesId").collect()}
rows = []
for sid in SERIES:
resp = requests.get("https://api.stlouisfed.org/fred/series/observations",
params={"series_id": sid, "api_key": FRED_KEY, "file_type": "json",
"observation_start": last.get(sid, "2010-01-01")}).json()
rows += [Row(SeriesId=sid, ObsDate=o["date"], Value=float(o["value"]))
for o in resp["observations"] if o["value"] != "."
and o["date"] > last.get(sid, "")] # skip already-loaded dates
spark.createDataFrame(rows).write.mode("append") \
.partitionBy("SeriesId").saveAsTable("enrichment.fred_observations")

06. SEC EDGAR + OpenCorporates: Company Reference Data

For US-listed companies, SEC EDGAR is the authoritative source: every 10-K, 10-Q, 8-K, and proxy statement, mapped to a CIK identifier you can use to join across systems. For private companies and international entities, OpenCorporates covers 200+ jurisdictions with company registration numbers, addresses, and officer data.

SEC EDGAR
Apple Inc.
CRM Account
Apple Computer
RESOLVED
enrichment.account_map
CIK 0000320193
Ticker AAPL
SEC EDGAR

JSON endpoints at data.sec.gov. No key. Rate limit: 10 requests per second per IP, strictly enforced. Identify yourself via a descriptive User-Agent or you will be blocked. All filings are public domain.

OpenCorporates

REST at api.opencorporates.com/v0.4. Free tier: 500 calls/month with attribution and a CC-BY-SA share-alike requirement. Commercial use requires a paid licence. Read the terms before embedding output in client deliverables.

The win for B2B teams: every account record in your CRM gets a stable external identifier (CIK or OpenCorporates ID) that survives company renames, mergers, and DUNS reassignments. The pattern below resolves every account name to a CIK once and stores the mapping.

# PySpark: fetch the EDGAR company tickers file and resolve account names to CIKs
import requests
from pyspark.sql.functions import col, lower, regexp_replace
HEADERS = {"User-Agent": "Daly BI Research data@example.com"}
raw = requests.get("https://www.sec.gov/files/company_tickers.json",
headers=HEADERS).json()
edgar = spark.createDataFrame(
[(v["cik_str"], v["ticker"], v["title"]) for v in raw.values()],
["CIK", "Ticker", "CompanyName"])
accounts = spark.table("crm.accounts") \
.withColumn("NameKey", lower(regexp_replace(col("AccountName"),
r"\s+(inc|corp|llc|ltd|plc)\.?$", "")))
edgar = edgar.withColumn("NameKey", lower(regexp_replace(col("CompanyName"),
r"\s+(inc|corp|llc|ltd|plc)\.?$", "")))
accounts.join(edgar, "NameKey", "left") \
.write.mode("overwrite").saveAsTable("enrichment.account_cik_map")

07. Calendars and Transit: The Quiet Force Multipliers

Two more sources to keep on the shelf. Nager.Date is a free public-holiday API covering 100+ countries, essential for any analysis that compares dates across geographies, since "Tuesday" doesn't mean the same thing in the US, India, and France. GTFS (General Transit Feed Specification) is the open standard that nearly every public transit agency in the world publishes their schedules and stops in.

Nager.Date

Holidays API
Thanksgiving Day US
2026-11-26

GTFS Transit

Open Data
Stops within 500m
Store #442
14
Nager.Date

REST at date.nager.at/api/v3/PublicHolidays/{year}/{country}. No key. No published rate limit, but cache the year-by-country results in your date dimension. MIT-licensed.

GTFS Transit Feeds

Catalogue at database.mobilitydata.org lists thousands of free agency feeds. Each is a ZIP of CSV files with stops, routes, and trip times. Licence varies by agency. Read each agency's terms.

Combined, these unlock comparisons your raw data can't support. The query below decomposes daily revenue into a like-for-like baseline by adjusting for both holidays and the count of nearby transit stops: two confounders that explain a surprising share of day-over-day variance in retail and hospitality.

-- T-SQL: daily revenue with holiday flag and transit-accessibility decile
SELECT
s.StoreId,
s.SalesDate,
s.Revenue,
CASE WHEN h.HolidayName IS NOT NULL THEN 1 ELSE 0 END AS IsHoliday,
h.HolidayName,
t.TransitStopCount_500m,
NTILE(10) OVER (ORDER BY t.TransitStopCount_500m) AS TransitDecile
FROM fact.DailyStoreSales s
JOIN dim.Stores st ON s.StoreId = st.StoreId
LEFT JOIN enrichment.nager_holidays h
ON h.HolidayDate = s.SalesDate AND h.CountryCode = st.CountryCode
LEFT JOIN enrichment.gtfs_stop_density t
ON t.StoreId = s.StoreId;

08. The Operating Discipline

Free data is only free at the moment of extraction. Treated carelessly, it generates production incidents, attribution lawsuits, and silent data drift that ruins models months after the fact. Six rules cover the failure modes we see most often.

1. Cache, don't call

No public API call from a Power BI dataset, a semantic model refresh, or a user-facing request. Pull into the Lakehouse on a schedule; serve from your Warehouse.

2. Identify yourself

Every outbound HTTP call carries a descriptive User-Agent with a contact email. Anonymous bulk traffic gets blocked first when an operator triages load.

3. Record the vintage

Every enrichment row gets a column for when the source was refreshed and which source version it came from. ACS 5-year 2022 is not ACS 5-year 2023.

4. Honour the licence

Display attribution where end users see the data. ODbL and CC-BY-SA require share-alike, so be careful before embedding those into a closed commercial product.

5. Throttle deliberately

Sleep between calls based on the published rate limit, not based on what feels reasonable. A burst that works once will get rate-limited once it's automated.

6. Pin a fallback

Keep yesterday's snapshot. When an upstream source has an outage (and they all eventually do), your reports keep loading from cache instead of going blank.

All six rules collapse into one operational artefact: a refresh-tracking table that records every successful pull with its vintage, row count, and licence URL. The query below surfaces sources that have drifted past their refresh cadence: the first alert that fires before a downstream report goes stale.

-- T-SQL: flag enrichment sources that are overdue for refresh
SELECT
SourceKey,
SourceName,
RefreshCadence,
VintageDate,
DATEDIFF(day, VintageDate, GETDATE()) AS DaysSinceRefresh,
RowCount,
LicenceUrl
FROM enrichment.SourceRegistry
WHERE (RefreshCadence = 'daily' AND DATEDIFF(day, VintageDate, GETDATE()) > 2)
OR (RefreshCadence = 'weekly' AND DATEDIFF(day, VintageDate, GETDATE()) > 10)
OR (RefreshCadence = 'monthly' AND DATEDIFF(day, VintageDate, GETDATE()) > 40)
OR (RefreshCadence = 'annual' AND DATEDIFF(day, VintageDate, GETDATE()) > 400)
ORDER BY DaysSinceRefresh DESC;

09. One Enriched View

The reward for doing this well is a single semantic model where every fact row carries the context an analyst would otherwise have to chase down by hand. Below is what that looks like once boundaries, demographics, weather, holidays, and transit accessibility are all sitting in the Warehouse: a single view ready to feed Power BI.

TIGER + ACS
Open-Meteo
FRED
Daily
Sales
Enriched Fact
SEC EDGAR
Nager.Date
GTFS Feeds
-- T-SQL (SQL Server / Azure SQL) view that joins every enrichment layer onto daily store sales. In Fabric Warehouse, swap the STContains join for a store-to-block-group key precomputed in Spark.
CREATE OR ALTER VIEW mart.DailyStoreSales_Enriched AS
SELECT
s.StoreId, s.SalesDate, s.Revenue, s.Transactions,
bg.GEOID AS BlockGroupGeoid,
acs.B19013_001E AS MedianHHIncome,
acs.B01003_001E AS Population,
w.TempMaxC, w.PrecipMm, w.WindKmh,
fred_un.Value AS NationalUnemploymentRate,
CASE WHEN h.HolidayName IS NOT NULL THEN 1 ELSE 0 END AS IsHoliday,
t.TransitStopCount_500m
FROM fact.DailyStoreSales s
JOIN dim.Stores st ON st.StoreId = s.StoreId
JOIN enrichment.census_block_groups bg ON bg.geometry.STContains(st.LocationPoint) = 1
JOIN enrichment.census_acs5_2023 acs ON acs.GEOID = bg.GEOID
LEFT JOIN enrichment.weather_daily w ON w.StoreId = s.StoreId AND w.ObservationDate = s.SalesDate
LEFT JOIN enrichment.fred_observations fred_un ON fred_un.SeriesId = 'UNRATE' AND fred_un.ObsDate = EOMONTH(s.SalesDate, -1)
LEFT JOIN enrichment.nager_holidays h ON h.HolidayDate = s.SalesDate AND h.CountryCode = st.CountryCode
LEFT JOIN enrichment.gtfs_stop_density t ON t.StoreId = s.StoreId;

Every column to the right of Transactions came from a free public source. None of it cost a licence fee. All of it makes the analyst's job dramatically easier, and the patterns above generalise to any country with comparable open data infrastructure.

Tags

Data EngineeringMicrosoft FabricData ArchitectureAPIsOpen DataGISLocation Intelligence