Ambient Weather Databricks Data Loader

A complete end-to-end dataflow pipeline that loads personal weather station data from the Ambient Weather API into Databricks Delta Lake tables and publishes live readings to a public JSON endpoint — built entirely on the Databricks free tier.

Dataflow Architecture

WS-2902 Weather Station
    → Ambient Weather Cloud (1-min intervals)
        → Socket.IO Realtime API
            → Databricks Delta Lake (workspace.ambient_weather)
                → GitHub Gist (live JSON, updates every minute)

Feature

Data Pipeline

Auto-detecting load mode — full historical load on first run, incremental on subsequent runs
Gap detection and recovery — automatically detects data gaps (WiFi outages, power loss) and jumps over them rather than stopping early
Multi-device support — handles multiple weather stations via a single API subscription
Explicit schema enforcement — READINGS_SCHEMA StructType prevents Arrow/Pandas type mismatch errors across API batches
Validation layer — flags new API columns, bad casts, suspicious timestamps, and missing required fields before writing
Null outdoor sensor handling — records where the outdoor sensor temporarily disconnects are written with null values rather than excluded, preserving timeline continuity

Device Management

SCD Type 2 device history — tracks station relocations, renames, and config changes over time with effective_from / effective_to timestamps
Multi-device upsert — devices table always reflects current state; devices_history maintains full audit trail
Geo context — lat, lon, elevation, timezone stored per device for dashboard local time display

Live Feed

Realtime Socket.IO fetch — connects to Ambient Weather's realtime API and grabs the latest reading instantly on connect via the subscribed event (no polling wait)
GitHub Gist publisher — pushes live JSON to a public GitHub Gist every minute via a scheduled Databricks Workflow
Compass conversion — wind direction degrees converted to compass bearing (N, NNE, NE, etc.)
Local time — all timestamps converted to station's local timezone in the published JSON

Infrastructure

Databricks Secrets vault — API keys never appear in notebook code
Scheduled Workflows — both the incremental loader and Gist updater run on configurable schedules
GitHub open source — fully documented, public repo

Prerequisites

Databricks Community Edition (free)
Ambient Weather account with at least one registered station
Ambient Weather API Key and Application Key (from My Account → API Keys)
GitHub account (free)
Databricks CLI installed locally

Setup

1. Install the Databricks CLI

pip install databricks-cli

2. Generate a Databricks Access Token

In your Databricks workspace click your profile icon → Settings
Go to Developer → Access Tokens → Generate new token
Name it databricks-cli, set expiration, copy the token

3. Configure the CLI

databricks configure --token
# Host: https://community.cloud.databricks.com
# Token: paste your token

Note: Use PowerShell on Windows for any CLI commands that require interactive input (secret values). Git Bash does not handle stdin prompts correctly.

4. Create a Secrets Scope

databricks secrets create-scope ambient_weather

5. Add Ambient Weather API Keys

databricks secrets put-secret ambient_weather api_key
# paste your Ambient Weather API key

databricks secrets put-secret ambient_weather app_key
# paste your Ambient Weather Application key

6. Create a GitHub Personal Access Token

Go to github.com/settings/tokens
Click Generate new token (classic)
Name it databricks-gist-updater
Check the gist scope only
Click Generate token and copy it

7. Create a Blank GitHub Gist

Go to gist.github.com
Filename: duquette_pines_weather.json (or your station name)
Content: {}
Click Create public gist
Copy the Gist ID from the URL:

https://gist.github.com/yourusername/THIS_IS_YOUR_GIST_ID

8. Add GitHub Secrets

databricks secrets put-secret ambient_weather github_token
# paste your GitHub Personal Access Token

databricks secrets put-secret ambient_weather gist_id
# paste your Gist ID

9. Verify All Secrets

databricks secrets list-secrets ambient_weather

Expected output:

Key           Last Updated Timestamp
api_key       ...
app_key       ...
github_token  ...
gist_id       ...

10. Import the Notebooks

In your Databricks workspace go to Workspace → Import
Upload notebooks/ambient_weather_loader.py
Upload notebooks/ambient_weather_gist_updater.py

11. Update Config (Cell 3 of each notebook)

Verify the table names match your workspace:

READINGS_TABLE = "workspace.ambient_weather.raw_readings"
DEVICES_TABLE  = "workspace.ambient_weather.devices"
HISTORY_TABLE  = "workspace.ambient_weather.devices_history"

Running the Data Loader

First Run — Full Historical Load

Run Cell 11 of ambient_weather_loader.py. It auto-detects that no table exists and performs a full historical load:

Pages backwards through all available station history
Detects and jumps over data gaps automatically
For a station with 3+ years of history expect ~40-45 minutes at the API rate limit
Logs progress every 10 pages with timestamp ranges and rate

Page     1 | Records:     288 | Range: 2026-06-10 → 2026-06-11 | Elapsed: 0.3s
Page    10 | Records:   2,787 | Range: 2026-06-02 → 2026-06-02 | Elapsed: 17.2s
⚠️  Empty batch — gap detected, jumping back 3 days...
Page    20 | Records:   5,379 | Range: 2026-05-21 → 2026-05-22 | Elapsed: 38.5s
...
Load complete: 350,337 records in 1,230 pages (2,516s)

Subsequent Runs — Incremental Load

Run Cell 11 again at any time. It auto-detects existing data and fetches only new records since the last loaded timestamp — typically completes in seconds.

On-Demand Latest Reading

Run Cell 13 at any time for a formatted live snapshot of current conditions across all devices.

Scheduling with Databricks Workflows

Incremental Loader (Daily or Hourly)

Go to Workflows → Create Job
Name: Ambient Weather Incremental Loader
Task: point at ambient_weather_loader notebook
Schedule: Daily at a convenient time (or hourly if you want tighter history)
Click Create

Gist Updater (Every Minute — Live Feed)

Go to Workflows → Create Job
Name: Ambient Weather Gist Updater
Task: point at ambient_weather_gist_updater notebook
Schedule: Every 1 minute using cron expression:

0/1 * * * ?

Click Create

Note: Each Gist updater run takes ~3 seconds (Socket.IO connect + Gist PATCH). Running every minute uses well within Databricks free tier compute and GitHub's API rate limits (1,440 requests/day vs 5,000/hour limit).

Live JSON Endpoint

Once the Gist updater is running, your live weather data is available at:

https://gist.githubusercontent.com/YOUR_GITHUB_USERNAME/YOUR_GIST_ID/raw/duquette_pines_weather.json

This URL always returns the latest reading without a commit hash — use this stable format for any downstream consumers (DAKboard, dashboards, etc.).

Sample Payload

{
  "station_name": "Duquette Pines",
  "mac_address": [REDACTED],
  "updated_utc": "2026-06-11 20:00 UTC",
  "updated_local": "2026-06-11 14:00 MDT",
  "timezone": "America/Denver",
  "tempf": 72.3,
  "tempinf": 71.1,
  "feelsLike": 72.3,
  "dewPoint": 32.5,
  "humidity": 21,
  "humidityin": 34,
  "windspeedmph": 8.5,
  "windgustmph": 11.4,
  "maxdailygust": 25.1,
  "winddir": 339,
  "winddir_compass": "NNW",
  "dailyrainin": 0,
  "weeklyrainin": 0.01,
  "monthlyrainin": 0.01,
  "totalrainin": 53.1,
  "baromrelin": 29.678,
  "baromabsin": 25.697,
  "solarradiation": 113.72,
  "uv": 1,
  "battout": 1,
  "lat": [REDACTED],
  "lon": [REDACTED],
  "elevation_m": 1337.4,
  "address": [REDACTED]
}

Tables

`ambient_weather.raw_readings`

One row per station reading. Partitioned by year and month. Primary key: dateutc + mac_address

See schema/readings_schema.md for full column documentation.

`ambient_weather.devices`

Current state of all registered stations. One row per device. Primary key: mac_address

`ambient_weather.devices_history`

SCD Type 2 history of device context changes (relocations, renames, etc.). A new row is inserted whenever tracked fields change (name, address, lat, lon, elevation, timezone). Current record identified by effective_to IS NULL.

See schema/devices_schema.md for full column documentation.

Data Quality Notes

Outdoor sensor dropouts — records where tempf and humidity are null represent periods when the outdoor sensor temporarily lost contact with the console. These are retained for timeline continuity.
Station offline periods — genuine gaps (WiFi outages, power loss) simply have no records. The loader detects and jumps over these automatically.
New API columns — the validation layer logs a warning if the API returns columns not in the schema. Add them to READINGS_SCHEMA in Cell 3 and run a one-time ALTER TABLE to capture them going forward.
Schema evolution — batt_co2 was added when Ambient Weather expanded CO2 sensor support. The explicit schema approach means adding new fields is a deliberate one-line change rather than a silent type inference surprise.

Project Structure

ambient-weather-databricks/
├── README.md
├── .gitignore
├── notebooks/
│   ├── ambient_weather_loader.py         # Historical + incremental Delta Lake loader
│   └── ambient_weather_gist_updater.py   # Live JSON feed publisher
└── schema/
    ├── readings_schema.md                # Full raw_readings column documentation
    └── devices_schema.md                 # devices + devices_history documentation

Known Limitations

Databricks Community Edition — secrets vault works despite some documentation suggesting otherwise; set up via CLI as described above
API rate limits — the loader sleeps 1 second between pages. Silent empty responses (rather than 429s) indicate throttling; the gap-jumping logic handles these gracefully
Sigma Public — does not support external data connections. Use Databricks Partner Connect to create a trial Sigma org with direct Databricks connectivity
GitHub Gist CORS — raw Gist URLs have CORS restrictions that prevent direct loading in some iframe contexts. Use https://api.allorigins.win/raw?url=YOUR_GIST_URL as a proxy workaround

License

MIT

Name		Name	Last commit message	Last commit date
notebooks		notebooks
schema		schema
.gitignore		.gitignore
LICENSE		LICENSE
README.md		README.md

Latest commit

History

Folders and files

Repository files navigation